CREATE PROCEDURE

Name

CREATE PROCEDURE -- create a new procedure

Synopsis

CREATE [OR REPLACE] PROCEDURE [schema.]procedure 
    [ (argument [IN | OUT | IN OUT] datatype 
    [, argument [IN | OUT | IN OUT] datatype] ...)] 
    {IS | AS} spl_subprogram_body 

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.

Parameters

OR REPLACE

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.

schema

is the schema to contain the procedure. If you omit schema, Oracle creates the procedure in your current schema.

procedure

is the name of the procedure to be created.

argument

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.

IN

specifies that you must specify a value for the argument when calling the procedure.

OUT

specifies that the procedure passes a value for this argument back to its calling environment after execution.

IN OUT

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.

datatype

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.

spl_subprogram_body

is the definition of the procedure. Procedure definitions are written in SPL.

Notes

To drop a stored procedure use the DROP PROCEDURE command

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 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