Description
CREATE PROCEDURE is used to create a stand-alone stored procedure in SPL
To create a stand-alone stored procedure. A procedure is a group
of SPL statements that you can call by name.
A procedure is introduced by the keywords CREATE PROCEDURE
followed by the procedure name and its parameters.
An option is to follow CREATE by OR REPLACE.
The advantage of doing so is that should you have already made the definition,
you will not get an error. On the other hand, should the previous definition be a
different procedure of the same name, you will not be warned, and the old procedure
will be lost.
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 essentially a SPL block.
Note: Unlike the type specifier in a SPL variable declaration,
the type specifier in a parameter declaration must be unconstrained.
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 in 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
