Description
CREATE PROCEDURE is used to create a new stored procedure in SPL.
A procedure is an SPL program that can be invoked or called as an individual SPL program statement.
A procedure is introduced by the keywords CREATE PROCEDURE
followed by the procedure name and its parameters.
The keywords, OR REPLACE, can optionally follow CREATE.
There can be any number of parameters, each followed by a mode and a type.
The possible modes are IN (read-only), OUT (write-only),
and INOUT (read and write).
Following the arguments is the keyword AS (IS is a synonym).
Then comes the body, which is an SPL block.
Note: Unlike the type specifier in an SPL variable declaration,
the type specifier in a parameter declaration must not specify a length.
For example, CHAR(10) and VARCHAR(20) are illegal;
CHAR or VARCHAR should be used instead.
The actual length of a parameter depends on the corresponding argument that is passed when the procedure is invoked.
Examples
The following is a simple stored procedure that takes no parameters:
CREATE OR REPLACE PROCEDURE simple_procedure
IS
BEGIN
DBMS_OUTPUT.PUT_LINE('That''s all folks!');
END;
To call the stored procedure above, use the EXEC command
followed by the name of the stored procedure from either Developer Studio
or EnterpriseDB PSQL as follows.
EXEC simple_procedure;
The second example is a more complicated stored procedure that takes
parameters.
In this example, p_deptno is an IN formal parameter,
p_empno and p_ename are IN OUT formal
parameters, and p_job, p_hiredate, and
p_sal are OUT formal parameters:
CREATE OR REPLACE PROCEDURE emp_query (
p_deptno IN NUMBER,
p_empno IN OUT NUMBER,
p_ename IN OUT VARCHAR2,
p_job OUT VARCHAR2,
p_hiredate OUT DATE,
p_sal OUT NUMBER
)
IS
BEGIN
SELECT empno, ename, job, hiredate, sal
INTO p_empno, p_ename, p_job, p_hiredate, p_sal
FROM emp
WHERE deptno = p_deptno
AND (empno = p_empno
OR ename = UPPER(p_ename));
END;
The emp_query procedure can be called by another program, passing it the actual parameters. The following is an example of another SPL program that calls emp_query.
DECLARE
v_deptno NUMBER(2);
v_empno NUMBER(4);
v_ename VARCHAR2(10);
v_job VARCHAR2(9);
v_hiredate DATE;
v_sal NUMBER;
BEGIN
v_deptno := 30;
v_empno := 7900;
v_ename := '';
emp_query(v_deptno, v_empno, v_ename, v_job, v_hiredate, v_sal);
DBMS_OUTPUT.PUT_LINE('Department : ' || v_deptno);
DBMS_OUTPUT.PUT_LINE('Employee No: ' || v_empno);
DBMS_OUTPUT.PUT_LINE('Name : ' || v_ename);
DBMS_OUTPUT.PUT_LINE('Job : ' || v_job);
DBMS_OUTPUT.PUT_LINE('Hire Date : ' || v_hiredate);
DBMS_OUTPUT.PUT_LINE('Salary : ' || v_sal);
END;
In this example, v_deptno, v_empno, v_ename,
v_job, v_hiredate, and v_sal are the actual parameters.
The output from the preceeding example is shown as follows:
Department : 30
Employee No: 7900
Name : JAMES
Job : CLERK
Hire Date : 03-DEC-81
Salary : 950
