Oracle Database

A sub function is a function defined and used inside another procedure or function. You need to define a sub function in the declaration part of the enclosing procedure or function. Sub function definition starts with the FUNCTION key word. Here is a sample script showing you how to define and use a sub function:

SQL> CREATE OR REPLACE PROCEDURE SUM_TEST AS

2 FUNCTION MY_SUM(X NUMBER, Y NUMBER)

3 RETURN NUMBER AS

4 BEGIN

5 RETURN X + Y;

6 END;

7 BEGIN

8 DBMS_OUTPUT.PUT_LINE('3 + 5 = ' ||

9 TO_CHAR(MY_SUM(3,5)));

10 DBMS_OUTPUT.PUT_LINE('5 + 3 = ' ||

11 TO_CHAR(MY_SUM(5,3)));

12 END;

13 /

SQL> EXECUTE SUM_TEST;

3 + 5 = 8

5 + 3 = 8

PL/SQL allows sub procedures or functions to be called recursively. The tutorial example below shows you how to calculate factorial values with a recursive sub function:

SQL> CREATE OR REPLACE PROCEDURE FACTORIAL_TEST AS

2 FUNCTION FACTORIAL(N NUMBER)

3 RETURN NUMBER AS

4 BEGIN

5 IF N <= 1 THEN

6 RETURN 1;

7 ELSE

8 RETURN N*FACTORIAL(N-1);

9 END IF;

10 END;

11 BEGIN

12 DBMS_OUTPUT.PUT_LINE('3! = ' ||

13 TO_CHAR(FACTORIAL(3)));

14 DBMS_OUTPUT.PUT_LINE('10! = ' ||

15 TO_CHAR(FACTORIAL(10)));

16 DBMS_OUTPUT.PUT_LINE('64! = ' ||

17 TO_CHAR(FACTORIAL(64)));

18 END;

19 /

SQL> EXECUTE FACTORIAL_TEST;

3! = 6

10! = 3628800

64! = 126886932185884164103433389335161480802000000000000...

There must be something wrong with the FACTORIAL() definition that causes those many extra '0's in the '64!' result.

What happens if your code has bug on recursive procedure calls, which causes an infinite number nested procedure calls? The answer is so good. Oracle server seems to offer no protection calling stack limit. The script below shows you a badly coded recursive procedure. If you run it on an Oracle 10g XE server on Windows, your server will out of control and keep using virtual memory to satisfy the growing calling stack. You have to reboot your server to control back.

SQL> CREATE OR REPLACE PROCEDURE STACK_TEST AS

2 --Warning: do not run this procedure on your server

3 PROCEDURE STACK AS

4 BEGIN

5 STACK;

6 END;

7 BEGIN

8 STACK;

9 END;

10 /

SQL> EXECUTE STACK_TEST;

(your server keep running with 100% CPU and memory usage)

Here are the rules about IN parameters:

► A formal IN parameter acts like constant. It can not be assigned with new values.

► An actual IN parameter can take a value or a variable.

► An actual IN parameter is passed by reference to the specified value or the value of the specified variable.

► An actual IN parameter will not receive any value from the formal parameter.

Here is good example of a procedure with an IN parameter:

SQL> CREATE OR REPLACE PROCEDURE WELCOME AS

2 SITE CHAR(80) := 'rendc.org';

3 PROCEDURE WELCOME_PRINT(S IN CHAR) AS

4 BEGIN

5 DBMS_OUTPUT.PUT_LINE('Welcome to ' || S);

6 -- S := 'Google.com'; -- Not allowed

7 END;

8 BEGIN

9 WELCOME_PRINT('MySpace.com');

10 WELCOME_PRINT(SITE);

11 END;

12 /

SQL> EXECUTE WELCOME;

Welcome to MySpace.com

Welcome to rendc.org

Here are the rules about OUT parameters:

► A formal OUT parameter acts like an un-initialized variable. It must be assigned with new values before the end of the procedure or function.

► An actual OUT parameter must be a variable.

► An actual OUT parameter will not pass any value to the formal parameter.

► An actual OUT parameter will receive a copy of the value from the formal parameter at the end of the procedure or function.

Here is good example of a procedure with an OUT parameter:

SQL> CREATE OR REPLACE PROCEDURE WELCOME AS

2 SITE CHAR(40) := 'rendc.org';

3 MESSAGE CHAR(80);

4 PROCEDURE WELCOME_PRINT(S IN CHAR, M OUT CHAR) AS

5 BEGIN

6 M := 'Welcome to ' || S;

7 END;

8 BEGIN

9 WELCOME_PRINT('MySpace.com', MESSAGE);

10 DBMS_OUTPUT.PUT_LINE(MESSAGE);

11 WELCOME_PRINT(SITE, MESSAGE);

12 DBMS_OUTPUT.PUT_LINE(MESSAGE);

13 END;

14 /

SQL> EXECUTE WELCOME;

Welcome to MySpace.com

Welcome to rendc.org

Here are the rules about IN OUT parameters:

► A formal IN OUT parameter acts like an initialized variable.

► An actual IN OUT parameter must be a variable.

► An actual IN OUT parameter passes a copy of its value to the formal parameter when entering the procedure or function.

► An actual IN OUT parameter will receive a copy of the value from the formal parameter at the end of the procedure or function.

Here is good example of a procedure with IN OUT parameters:

SQL> CREATE OR REPLACE PROCEDURE SWAP_TEST AS

2 A NUMBER := 3;

3 B NUMBER := 8;

4 PROCEDURE MY_SWAP(X IN OUT NUMBER,Y IN OUT NUMBER) AS

5 T NUMBER;

6 BEGIN

7 T := X;

8 X := Y;

9 Y := T;

10 END MY_SWAP;

11 BEGIN

12 MY_SWAP(A,B);

13 DBMS_OUTPUT.PUT_LINE('A = ' || TO_CHAR(A));

14 DBMS_OUTPUT.PUT_LINE('B = ' || TO_CHAR(B));

15 END;

16 /

SQL> EXECUTE SWAP_TEST;

A = 8

B = 3

If you have an IN parameter, you can make it as an optional parameter for the calling statement by defining the formal parameter with the DEFAULT clause. This gives you the freedom of not providing the actual parameter when calling this procedure or function. See the following tutorial script shows you an example procedure with an optional parameter:

SQL> CREATE OR REPLACE PROCEDURE WELCOME AS

2 PROCEDURE GREETING(S IN CHAR DEFAULT 'globalguideline') AS

3 BEGIN

4 DBMS_OUTPUT.PUT_LINE('Welcome to ' || S);

5 END;

6 BEGIN

7 GREETING('MySpace.com');

8 GREETING;

9 END;

10 /

Procedure created.

SQL> EXECUTE WELCOME;

Welcome to MySpace.com

Welcome to globalguideline

Named parameters are actual parameters specified not by position but by providing formal parameter names when calling the procedure or function. The main advantage of named parameters is that the caller don't have to remember the position of each parameter. But the caller have to remember the formal parameter names. The script below illustrates how to use named parameters:

SQL> CREATE OR REPLACE PROCEDURE SWAP_TEST AS

2 A NUMBER := 3;

3 B NUMBER := 8;

4 PROCEDURE MY_SWAP(X IN OUT NUMBER,Y IN OUT NUMBER) AS

5 T NUMBER;

6 BEGIN

7 T := X;

8 X := Y;

9 Y := T;

10 END MY_SWAP;

11 BEGIN

12 MY_SWAP(Y=>B, X=>A); -- same as (X=>A, Y=B), OR (A,B)

13 DBMS_OUTPUT.PUT_LINE('A = ' || TO_CHAR(A));

14 DBMS_OUTPUT.PUT_LINE('B = ' || TO_CHAR(B));

15 END;

16 /

SQL> EXECUTE SWAP_TEST;

A = 8

B = 3

The scope of a variable can be described with these rules:

► A variable is valid within the procedure or function where it is defined.

► A variable is also valid inside a sub procedure or function defined.

► If a variable name is collided with another variable in a sub procedure or function, this variable becomes not visible in that sub procedure or function.

Here is a sample script to show you those rules:

The script below illustrates how to use named parameters:

SQL> CREATE OR REPLACE PROCEDURE PARENT AS

2 X CHAR(10) := 'ggl';

3 Y NUMBER := 999999.00;

4 PROCEDURE CHILD AS

5 Y CHAR(10) := 'CENTER';

6 Z NUMBER := -1;

7 BEGIN

8 DBMS_OUTPUT.PUT_LINE('X = ' || X); -- X from PARENT

9 DBMS_OUTPUT.PUT_LINE('Y = ' || Y); -- Y from CHILD

10 DBMS_OUTPUT.PUT_LINE('Z = ' || TO_CHAR(Z));

11 END;

12 BEGIN

13 DBMS_OUTPUT.PUT_LINE('X = ' || X); -- X from PARENT

14 DBMS_OUTPUT.PUT_LINE('Y = ' || TO_CHAR(Y));

15 -- DBMS_OUTPUT.PUT_LINE('Z = ' || TO_CHAR(Z));

16 CHILD;

17 END;

18 /

SQL> EXECUTE PARENT;

X = ggl

Y = 999999

X = ggl

Y = CENTER

Z = -1

Yes, you can run almost any DML statements in PL/SQL directly. To manipulate Oracle database data you can include INSERT, UPDATE, and DELETE statements, directly in PL/SQL programs, without any special notation, as shown in the following sample code:

(Connect to XE with SQL*Plus)

CREATE TABLE student (id NUMBER(5) PRIMARY KEY,

first_name VARCHAR(80) NOT NULL,

last_name VARCHAR(80) NOT NULL);

Table created.

SELECT COUNT(*) FROM student;

COUNT(*)

----------

0

CREATE OR REPLACE PROCEDURE HELLO AS

BEGIN

INSERT INTO student VALUES(29, 'Bob', 'Henry');

INSERT INTO student VALUES(30, 'Joe', 'Bush');

UPDATE student SET first_name = 'ggl' WHERE id = 30;

DELETE FROM student WHERE id = 29;

END;

/

SELECT * FROM student;

ID  FIRST_NAME LAST_NAME

-------- ----------- ----------

30  ggl  Bush

Yes, you can use variables in SQL statements as part of any expressions. The tutorial script provides you some good examples:

(Connect to XE with SQL*Plus)

CREATE TABLE student (id NUMBER(5) PRIMARY KEY,

first_name VARCHAR(80) NOT NULL,

last_name VARCHAR(80) NOT NULL);

Table created.

DECLARE

var_id NUMBER;

var_name CHAR(10);

BEGIN

var_id := 29;

var_name := 'Bob';

INSERT INTO student VALUES(var_id, var_name, 'Henry');

var_name := 'Joe';

INSERT INTO student VALUES(var_id+1, var_name, 'Bush');

var_name := 'ggl';

UPDATE student SET first_name = var_name

WHERE id = var_id+1;

DELETE FROM student WHERE id = var_id;

END;

/

SELECT * FROM student;

ID  FIRST_NAME LAST_NAME 

-------- ----------- ----------- 

30  ggl  Bush 

When a variable name collides with a column name, PL/SQL will use it as the variable if it is used where variable is allowed; It will be used as the column, if it is used where variable is not allowed but column is allowed. Here is a good example of variable names collide with column names:

CREATE TABLE student (id NUMBER(5) PRIMARY KEY,

first_name VARCHAR(80) NOT NULL,

last_name VARCHAR(80) NOT NULL);

Table created.

DECLARE

id NUMBER;

first_name CHAR(10);

BEGIN

id := 29;

first_name := 'Bob';

INSERT INTO student VALUES(id, first_name, 'Henry');

first_name := 'Joe';

INSERT INTO student VALUES(id+1, first_name, 'Bush');

first_name := 'ggl';

UPDATE student SET first_name = first_name WHERE id = 29;

-- 1 row updated

-- Both 'first_name's are treated as column names

UPDATE student SET first_name = first_name

WHERE id = id+1;

-- 0 rows updated

-- Both "id"s are treated as variable names

DELETE FROM student WHERE id = id;

-- 2 rows deleted

END;

/

If you want to assign results from SELECT statements to variables, you can use the INTO clause, which an extension of SELECT statements for PL/SQL. The sample code below shows some good example on INTO clause:

DECLARE

total NUMBER;

now DATE;

fname VARCHAR2(10);

lname VARCHAR2(10);

BEGIN

SELECT COUNT(*) INTO total FROM employees;

DBMS_OUTPUT.PUT_LINE('Count = ' || TO_CHAR(total));

SELECT SYSDATE INTO now FROM DUAL;

DBMS_OUTPUT.PUT_LINE('Now = ' || TO_CHAR(now, 'SSSSS'));

SELECT first_name, last_name INTO fname, lname

FROM employees

WHERE employee_id = 100;

DBMS_OUTPUT.PUT_LINE('Name = ' || fname || ' ' || lname);

END;

/

Count = 107

Now = 82375

Name = Steven King

You can use "SELECT ... INTO variable" to assign query results to variables. But what happens if the SELECT statements return multiple rows? The answer is that you will get a run time error. The following tutorial exercise shows this error condition:

DECLARE

fname VARCHAR2(10);

lname VARCHAR2(10);

BEGIN

SELECT first_name, last_name INTO fname, lname

FROM employees WHERE employee_id = 100;

DBMS_OUTPUT.PUT_LINE('Name = ' || fname || ' ' || lname);

SELECT first_name, last_name INTO fname, lname

FROM employees WHERE employee_id > 100;

DBMS_OUTPUT.PUT_LINE('Name = ' || fname || ' ' || lname);

END;

/

ORA-01422: exact fetch returns more than requested number

of rows

ORA-06512: at line 8

Name = Steven King