Name
CREATE PROCEDURE -- define a new stored procedure
Synopsis
CREATE [ OR REPLACE ] PROCEDURE name [ (argname [ IN | IN OUT | OUT ] argtype [ DEFAULT value ] [, ...]) ] [ AUTHID { DEFINER | CURRENT_USER } ] { IS | AS } [ declaration; ] [, ...] BEGIN statement; [...] [ EXCEPTION { WHEN exception [ OR exception ] [...] THEN statement; [, ...] } [, ...] ] END [ name ]
Description
CREATE PROCEDURE defines a new stored procedure. CREATE OR REPLACE PROCEDURE will either create a new procedure, or replace an existing definition.
If a schema name is included, then the procedure is created in the specified schema. Otherwise it is created in the current schema. The name of the new procedure must not match any existing procedure in the same schema unless the intent is to update the definition of an existing procedure, in which case use CREATE OR REPLACE PROCEDURE.
The user that creates the procedure becomes the owner of the procedure.
See Section 4.2.3 for more information on procedures.
Parameters
name
The name (optionally schema-qualified) of the procedure to create.
argname
The name of an argument. The argument is referenced by this name within the procedure body.
IN | IN OUT | OUT
The argument mode. IN declares the argument for input only. This is the default. IN OUT allows the argument to receive a value as well as return a value. OUT specifies the argument is for output only.
argtype
The data type(s) of the procedure’s arguments. The argument types may be a base data type or it may copy the type of an existing column. A length must not be specified for any base type - for example, specify VARCHAR2, not VARCHAR2(10).
The type of a column is referenced by writing tablename.columnname%TYPE; using this can sometimes help make a procedure independent from changes to the definition of a table.
DEFAULT value
Supplies a default value for an input argument if one is not supplied in the procedure call. DEFAULT may not be specified for arguments with modes IN OUT or OUT.
DEFINER | CURRENT_USER
Specifies whether the privileges of the procedure owner (DEFINER) or the privileges of the current user executing the procedure (CURRENT_USER) are to be used to determine whether or not access is allowed to database objects referenced in the procedure. Also, under DEFINER, the search path of the procedure owner is used to resolve references to unqualified database objects while under CURRENT_USER, the search path of the current user executing the procedure is used to resolve references to unqualified database objects. DEFINER is the default.
declaration
A variable, type, or REF CURSOR declaration.
statement
An SPL program statement. Note that a DECLARE - BEGIN - END block is considered an SPL statement unto itself. Thus, the function body may contain nested blocks.
exception
An exception condition name such as NO_DATA_FOUND, OTHERS, etc.
Examples
The following procedure lists the employees in the emp table:
CREATE OR REPLACE PROCEDURE list_emp IS v_empno NUMBER(4); v_ename VARCHAR2(10); CURSOR emp_cur IS SELECT empno, ename FROM emp ORDER BY empno; BEGIN OPEN emp_cur; DBMS_OUTPUT.PUT_LINE('EMPNO ENAME'); DBMS_OUTPUT.PUT_LINE('----- -------'); LOOP FETCH emp_cur INTO v_empno, v_ename; EXIT WHEN emp_cur%NOTFOUND; DBMS_OUTPUT.PUT_LINE(v_empno || ' ' || v_ename); END LOOP; CLOSE emp_cur; END; EXEC list_emp; EMPNO ENAME ----- ------- 7369 SMITH 7499 ALLEN 7521 WARD 7566 JONES 7654 MARTIN 7698 BLAKE 7782 CLARK 7788 SCOTT 7839 KING 7844 TURNER 7876 ADAMS 7900 JAMES 7902 FORD 7934 MILLER
The following procedure uses IN OUT and OUT arguments to return an employee’s number, name, and job based upon a search using first, the given employee number, and if that is not found, then using the given name. An anonymous block calls the procedure.
CREATE OR REPLACE PROCEDURE emp_job ( p_empno IN OUT emp.empno%TYPE, p_ename IN OUT emp.ename%TYPE, p_job OUT emp.job%TYPE ) IS v_empno emp.empno%TYPE; v_ename emp.ename%TYPE; v_job emp.job%TYPE; BEGIN SELECT ename, job INTO v_ename, v_job FROM emp WHERE empno = p_empno; p_ename := v_ename; p_job := v_job; DBMS_OUTPUT.PUT_LINE('Found employee # ' || p_empno); EXCEPTION WHEN NO_DATA_FOUND THEN BEGIN SELECT empno, job INTO v_empno, v_job FROM emp WHERE ename = p_ename; p_empno := v_empno; p_job := v_job; DBMS_OUTPUT.PUT_LINE('Found employee ' || p_ename); EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('Could not find an employee with ' || 'number, ' || p_empno || ' nor name, ' || p_ename); p_empno := NULL; p_ename := NULL; p_job := NULL; END; END; DECLARE v_empno emp.empno%TYPE; v_ename emp.ename%TYPE; v_job emp.job%TYPE; BEGIN v_empno := 0; v_ename := 'CLARK'; emp_job(v_empno, v_ename, v_job); DBMS_OUTPUT.PUT_LINE('Employee No: ' || v_empno); DBMS_OUTPUT.PUT_LINE('Name : ' || v_ename); DBMS_OUTPUT.PUT_LINE('Job : ' || v_job); END; Found employee CLARK Employee No: 7782 Name : CLARK Job : MANAGER
See Also
