CREATE [OR REPLACE] PROCEDURE [schema.]procedure
[ (argument [IN | OUT | IN OUT] datatype
[, argument [IN | OUT | IN OUT] datatype] ...)]
{IS | AS} spl_subprogram_body 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.
recreates the procedure if it already exists. You can use this option to change the definition of an existing procedure without dropping, recreating, and regranting object privileges previously granted on it. If you redefine a procedure, Oracle recompiles it.
is the schema to contain the procedure. If you omit schema, Oracle creates the procedure in your current schema.
is the name of the procedure to be created.
is the name of an argument to the procedure. If the procedure does not accept arguments, you can omit the parentheses following the procedure name.
specifies that you must specify a value for the argument when calling the procedure.
specifies that the procedure passes a value for this argument back to its calling environment after execution.
specifies that you must specify a value for the argument when calling the procedure and that the procedure passes a value back to its calling environment after execution. If you omit IN, OUT, and IN OUT, the argument defaults to IN.
is the datatype of an argument. An argument can have any datatype supported by SPL.
The datatype cannot specify a length, precision, or scale. Oracle derives the length, precision, or scale of an argument from the environment from which the procedure is called.
is the definition of the procedure. Procedure definitions are written in SPL.
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 EDB-Studio or EDB-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 EDB-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