10.3 How do I…Create a stored procedure?Problem
I have one anonymous PL/SQL block that performs operations on database objects, but because this block is not stored in the database, it has to be compiled every time before I run it. I want to be able to create a procedure that can accept and return parameters. I know that a stored procedure is compiled and stored in the database and can be called from other PL/SQL blocks. How do I create a stored procedure?
Technique
A stored procedure is created with the CREATE PROCEDURE statement and consists of two parts. The procedure specification defines the name of the procedure and its parameters. The procedure body contains the PL/SQL block, which is executed when the procedure is run. The procedure body has the same structure as the PL/SQL blocks presented in How-To 10.1. It contains a declarative section, an executable section, and an exception section. Like anonymous blocks, the declarative and exception sections are optional. The syntax used to create a simple stored procedure that does not contain any parameters is shown here:
CREATE [OR REPLACE] PROCEDURE procedure_name IS
A stored procedure can contain parameters that can be passed into the procedure, back to the calling PL/SQL block, or in both directions. When a parameter is defined in the procedure specification, a name, the parameter mode, and the datatype have to be specified. The three possible parameter modes are IN (the default), which represents a parameter passed into the procedure; OUT, which represents a parameter passed back to the caller; or IN OUT, which represents a parameter passed in both directions. In the body of a subprogram, a parameter with an IN mode cannot be assigned a value, and a parameter with an OUT mode cannot be read from, or a compilation error will occur. A parameter can be defined with a default value to be used when the no value is specified for that parameter when calling the procedure. The AS keyword can be optionally used instead of the IS keyword. A sample procedure specification containing parameters is shown here:[ Optional declarative section contains all local types, variables, constants,
cursors and subprogram declarations. Note: There is no DECLARE statement.]
BEGIN
Executable section is run when the procedure is executed.
[EXCEPTION
Optional exception section to handle errors.]
END;
CREATE OR REPLACE PROCEDURE Test_Proc
(dept_no IN VARCHAR2,
mgr_id OUT NUMBER,
run_date IN DATE DEFAULT SYSDATE) IS
The third parameter shown in the example contains a default parameter. If a value for the run_date parameter is not specified, the current system date is used. The %TYPE attribute can be used to declare a parameter to have a datatype the same as a table column. The %ROWTYPE attribute can be used to declare a parameter to have a datatype the same as a table row or a cursor. Refer to How-To 10.8 for more information.
By default, the stored procedure is created in the current user account’s schema. Provided you have the necessary privileges, a different schema name can be provided as schema_name.procedure_name in the CREATE PROCEDURE statement.
Positional and Named Notation
Parameter names in a subprogram (function or procedure) declaration are formal parameters, whereas variables placed in the parameter list of a subprogram call are actual parameters. When calling a subprogram, the association between actual and formal parameters can be indicated by using positional notation or named notation.
When a procedure is called from a PL/SQL block, positional notation matches the parameter value with the order of the parameters in the procedure specification. A sample call using positional notation is shown here:
TEST_PROC(12, mgr_variable);
Named notation lets you specify a name for the parameter value, to ensure that the parameter is correct. A sample call using named notation is shown here:
TEST_PROC(mgr_id => mgr_variable, dept_no => 12);
The assignment operator => is used to relate the parameter name with its value. Named parameters ensure that the values are assigned correctly, irrespective of the order in which you specify parameters. Another way of calling using named notation is:
TEST_PROC(dept_no => 12, mgr_id => mgr_variable);
When using named notation, the order of parameters is not important. You can also mix positional and named notation within a single call:
TEST_PROC(12, mgr_id => mgr_variable);
With mixed notation, named notation cannot precede positional notation. Hence, the following procedure call is illegal:
TEST_PROC(mgr_id => mgr_variable, 12); -- this is illegal
Named notation is flexible because you can specify only a partial list of parameters and omit those parameters that have default values. When calling stored subprograms, greater readability is achieved by using named notation.
An ORA-6502 error occurs if there is a datatype mismatch between the formal and actual parameters, irrespective of the notation used.
Steps
1. Run SQL*Plus and connect as the WAITE user account and execute the CHP10_9.SQL file. It has a CREATE PROCEDURE statement contained in the file that creates a simple stored procedure, which receives a parameter and displays it. Figure 10.09 displays the results of the operation.
Lines 1 and 2 provide the procedure specification of the statement. The CREATE OR REPLACE clause instructs Oracle to create a new procedure if one does not exist or to replace the existing one. Line 2 specifies the name of the procedure and provides a parameter list. The parameter ORDER_NO is declared as an IN parameter of the NUMBER datatype. Lines 3 through 5 contain the procedure body. Line 4 displays the parameter passed to the procedure using the DBMS_OUTPUT.PUT_LINE procedure.
2. Run CHP10_10.SQL in SQL*Plus. The PL/SQL block contained in the file calls the stored procedure created in the previous steps. Figure 10.10 displays the results of the operation.
Line 2 calls the stored procedure created in the previous steps and passes it a parameter of 25. When the procedure is executed, the statements contained in the executable section of the procedure are run on the server.
The message displayed by the DBMS_OUTPUT.PUT_LINE procedure contained in the stored procedure is displayed in SQL*Plus. The number displayed in the output line is passed to the stored procedure as a parameter.
3. Run CHP10_11.SQL in SQL*Plus. The statement contained in the file creates a stored procedure demonstrating OUT parameters, default parameters, and the declaration of variables within a stored procedure. Figure 10.11 displays the results of the operation.
Lines 1 and 2 present the procedure specification. Lines 2 through 4 declare the name of the procedure and specify the parameter list.
Three parameters are passed to the procedure. The first parameter is an IN parameter of the VARCHAR2 datatype. Notice that no length is specified with the datatype. Be sure not to specify a length for a parameter, or an error will occur, but you can use the %TYPE and %ROWTYPE attributes to specify parameters with constrained datatypes.
A default value of NULL is specified for the first parameter. A NOT NULL constraint cannot be specified on a parameter. The second parameter is an OUT parameter, which is passed back to the caller. The third parameter is an IN parameter, with a default value specified using a syntax different than the first parameter. The default value is used whenever the procedure is called without a value for that parameter. Line 5 declares a local variable for the stored procedure and assigns a default value to the variable. Note that a NOT NULL constraint can be placed on a variable, but it must be assigned a default value in the declaration. The DECLARE clause is not used in named PL/SQL blocks. Lines 6 through 8 define the procedure body. The statement in line 7 sets the value of the OUT parameter to a value based on the parameters passed to the procedure. Note the implicit conversion of a number to a string when concatenating input parameters to form the return string. Whenever the procedure is called, the value of the OUT parameter is passed back to the caller.
4. Run CHP10_12.SQL in SQL*Plus. The PL/SQL block contained in the file runs the stored procedure created in the previous steps, demonstrating the use of parameters. The results of the operation are displayed in Figure 10.12.
Lines 1 and 2 contain the declarative section of the block. The variable declared in line 2 receives the value passed back through the OUT parameter from the procedure when it is called. Lines 4 and 5 pass two parameters to the stored procedure using positional notation. PL/SQL assigns the values to the parameters based on their location in the parameter list. Because the third parameter in the procedure is optional, the default value is used. Line 6 displays the results of the OUT parameter using the DBMS_OUTPUT.PUT_LINE procedure. Lines 7 through 9 call the same procedure using named notation. The name of the parameter is provided with the value that enables the parameters to be specified in any order. Line 10 displays the value returned by the called procedure.
Both calls to the stored procedure generate valid output. It does not matter whether position notation or named notation is used within your code, as long as the stored procedure can identify the values assigned to the parameters.
How It Works
Stored procedures are created with the CREATE PROCEDURE statement. Step 1 creates a simple procedure that receives a parameter and displays it using DBMS_OUTPUT.PUT_LINE. Step 2 runs the stored procedure created in the first step and displays its output. Step 3 creates a stored procedure with three parameters, of which two are assigned default values. A parameter can be optionally assigned a default value in the procedure specification. Step 4 presents the two methods used to pass parameters to the stored procedure. The first call to the procedure uses positional notation, which sets the parameter values based on their position in the list. The second call to the procedure uses the named notation.
The RETURN statement at the end of a procedure is implicit. A procedure can have explicit RETURN statements anywhere in the body to immediately return control back to the caller. The RETURN statement in a procedure cannot contain an expression.
Comments
Stored procedures enable your applications to perform business functions on the server, resulting in dramatic improvement in performance. Stored procedures promote code reuse and easier maintenance. Stored subprograms provide modularity and abstraction because they can be referenced from different applications without the application knowing the internal workings of the subprogram. They can improve performance because of reduced network traffic, better execution speed, and memory usage, as well as smaller application size.
NOTE - A stored procedure is marked with a valid or invalid state, depending on whether errors were encountered during compilation. Only valid stored procedures can be referenced. If any schema object (procedure, function, package specification, table, view, sequence, or synonym) referenced by a procedure has changed, the state of the procedure becomes invalid. All functions, procedures, and packages referencing an invalid procedure are also marked invalid.
Irrespective of state, all stored procedures are stored in the database. When an application calls a procedure, Oracle loads it into the shared pool (SGA) if it is not already in the shared pool. A valid procedure is executed immediately and, for an invalid procedure, Oracle automatically recompiles the procedure. If the recompilation succeeds, the procedure is marked as valid and execution continues. Otherwise, Oracle returns a runtime error.
In a complex system, you must ensure that all referenced objects are compiled before the referencing procedures to ensure that they are all marked valid. Object dependencies and state information can be queried from the USER_DEPENDENCIES and USER_OBJECTS data dictionary views. This is demonstrated in How-To 10.6.
To create stored procedural objects (procedures, functions, and packages), the user account must have the capability to create them with the CREATE PROCEDURE or the CREATE ANY PROCEDURE privilege or to alter them with the ALTER PROCEDURE or ALTER ANY PROCEDURE privilege. Privileges have to be explicitly granted and cannot be obtained through roles. To execute a stored procedural object, a different user (other than the object owner) must be granted EXECUTE privilege on that object or must have an EXECUTE ANY PROCEDURE system privilege. Users are not required to have privileges to the objects referenced by the subprogram. Previously granted EXECUTE privileges on an existing stored procedural object are preserved while replacing it with the OR REPLACE clause.