Oracle PL/SQL by Example, Fifth Edition (2015)
Chapter 19. Procedures
In this chapter, you will learn about
Passing Parameters IN and OUT of Procedures
All the PL/SQL that you have written up to this point has been anonymous blocks that were run as scripts and compiled by the database server at run time. Now you will begin to use modular code. Modular code is a methodology to build a program from distinct parts (modules), each of which performs a specific function or task toward the final objective of the program. Once modular code is stored on the database server, it becomes a database object, or subprogram, that is available to other program units for repeated execution. To save code into the database, the source code needs to be sent to the server so that it can be compiled into p-code and stored in the database. This process will be covered in the following three chapters. This chapter is short: It simply introduces stored procedures. Chapter 20 covers the basics of stored functions, and Chapter 21 is a lengthy chapter that pulls all the material together to cover packages.
In the first lab of this chapter, you will learn more about stored code and discover how to write one type of stored code known as procedures. In the second lab, you will learn about passing parameters into and out of procedures. Prior to covering the details of stored procedures, you will be introduced to the benefits of module code.
Benefits of Modular Code
A PL/SQL module is any complete logical unit of work. There are five types of PL/SQL modules: (1) anonymous blocks that are run with a text script (the type you have used until now), (2) procedures, (3) functions, (4) packages, and (5) triggers. There are two main benefits to using modular code: (1) It is more reusable and (2) it is more manageable.
You create a procedure either in SQL*Plus or in one of the many tools for creating and debugging stored PL/SQL code. If you are using SQL*Plus, you will need to write your code in a text editor and then run it at the SQL*Plus prompt.
The same block structure is used for all the module types. The block begins with a header (for named blocks only), which consists of (1) the name of the module and (2) a parameter list (if used).
The declaration section defines variables, cursors, and sub-blocks that will be needed in the next section.
The main part of the module is the execution section, where all of the calculations and processing are performed. This will contain executable code such as IF-THEN-ELSE statements, loops, calls to other PL/SQL modules, and so on.
The last section of the module is an optional exception handler, which contains the code to handle exceptions.
Until this chapter, you have written only anonymous blocks. Anonymous blocks are very much like modules, except that anonymous blocks do not have headers. There are important distinctions, though. As the name implies, anonymous blocks have no names and, therefore, cannot be called by another block. They are not stored in the database and must be compiled and then run each time the script is loaded.
The PL/SQL block in a subprogram is a named block that can accept parameters and can be invoked from an application that can communicate with the Oracle database server. A subprogram can be compiled and stored in the database. This allows the programmer to reuse the program. It also provides for easier maintenance of code. Subprograms may be either procedures or functions.
Lab 19.1: Creating Procedures
After this lab, you will be able to
Put Procedure Creation Syntax into Practice
Query the Data Dictionary for Information on Procedures
A procedure is a module performing one or more actions; it does not need to return any values. The syntax for creating a procedure is as follows:
CREATE OR REPLACE PROCEDURE name
[(parameter[, parameter, ...])]
A procedure may have zero to many parameters (this topic is covered in Lab 19.2). Every procedure has two parts: (1) the header portion, which comes before the AS (or sometimes IS—they are interchangeable) keyword and contains the procedure name and the parameter list, and (2) the body, which is everything after the AS (IS) keyword. The word REPLACE is optional. When this keyword is not included in the header of the procedure, to change the code in the procedure, you must first drop the procedure and then recreate it. Because it is very common to change the code of the procedure, especially when it is under development, it is strongly recommended that you use the OR REPLACE option.
Putting Procedure Creation Syntax into Practice
The following script demonstrates the syntax for creating a procedure. When this script is run, it creates a procedure named Discount that is compiled into p-code and stored in the database for later execution.
For Example ch19_1.sql
CREATE OR REPLACE PROCEDURE Discount
SELECT distinct s.course_no, c.description
FROM section s, enrollment e, course c
WHERE s.section_id = e.section_id
AND c.course_no = s.course_no
GROUP BY s.course_no, c.description,
HAVING COUNT(*) >=8;
FOR r_group_discount IN c_group_discount
SET cost = cost * .95
WHERE course_no = r_group_discount.course_no;
('A 5% discount has been given to '||
To execute the stored procedure Discount, the following syntax is used:
Executing the Discount procedure yields the following result:
5% discount has been given to 25 Adv. Word Perfect
.... (through each course with an enrollment over 8)
PL/SQL procedure successfully completed.
There is no COMMIT in this procedure, which means the procedure will not update the database. A COMMIT command needs to be issued after the procedure is run, if you want the changes to be made. Alternatively, you can enter a COMMIT command either before or after the end loop. If you put the COMMIT statement before the end loop, then you are committing changes after every loop. If you put the COMMIT statement after the end loop, then the changes will not be committed until after the procedure is near completion. It is wiser to follow the second option, as it leaves you better prepared for handling errors.
By the Way
If you receive an error in SQL*Plus, type the following command:
You can also add to the command,
L start_line_number end_line_number
to see a portion of the code and isolate errors.
Querying the Data Dictionary for Information on Procedures
Two main views in the data dictionary provide information on stored code: the USER_OBJECTS view, which gives information about the objects, and the USER_SOURCE view, which gives the text of the source code. The data dictionary also has ALL_ and DBA_ versions of these views.
The following SELECT statement gets pertinent information from the USER_OBJECTS view about the Discount procedure you just wrote:
SELECT object_name, object_type, status
WHERE object_name = 'DISCOUNT';
The result would be the following, assuming the only object in the database is the new Discount procedure:
OBJECT_NAME OBJECT_TYPE STATUS
---------------- ------------- ------
DISCOUNT PROCEDURE VALID
The status indicates where the procedure was compiled successfully. An invalid procedure cannot be executed.
The following SELECT statement displays the source code from the USER_SOURCE view for the Discount procedure:
SELECT TO_CHAR(line, 99)||'>', text
WHERE name = 'DISCOUNT'
Stored procedures in the database can also be seen in Oracle SQL Developer. If you expand the nodes under the appropriate database connection, you will see under the Procedure node all procedures in the database for the user specified in the database connection. The node will show both valid and invalid procedures. Figure 19.1, for instance, shows the valid Discount procedure. The default tab that opens shows the code; the code can be modified and recompiled in this tab. Additionally, tabs on grants and dependencies are available. If the procedure was invalid, it will have a red X next to it. There is also a tool in Oracle SQL Developer that can be utilized to write new procedures, which can be accessed by right-clicking on the Procedure node.
Figure 19.1 Discount Procedure Seen in Oracle SQL Developer
By the Way
A procedure can become invalid if the table it is based on is deleted or changed. You can recompile an invalid procedure with the following command:
alter procedure procedure_name compile
Lab 19.2: Passing Parameters IN and OUT of Procedures
After this lab, you will be able to
Use IN and OUT Parameters with Procedures
Using IN and OUT Parameters with Procedures
Parameters are the means to pass values from the calling environment to the server, and vice versa. These values are processed or returned via the execution of the procedure. There are three parameter modes: IN, OUT, and IN OUT.
Modes specify whether the parameter passed is read in or acts as a receptacle for what comes out. Figure 19.2 illustrates the relationship between the parameters when they are in the procedure header versus when the procedure is executed.
Figure 19.2 Matching a Procedure Call to a Procedure Header
Formal and Actual Parameters
Formal parameters are the names specified within parentheses as part of the header of a module. Actual parameters are the value expressions specified within parentheses as a parameter list when a call is made to the module. The formal parameter and the related actual parameter must be of the same or compatible data types. Table 19.1 explains the three types of parameters.
Table 19.1 Three Types of Parameters
Passing of Constraints (Data Types) with Parameter Values
Formal parameters do not require constraints on the data type. For example, instead of specifying a constraint such as VARCHAR2(60), you can just issue VARCHAR2 against the parameter name in the formal parameter list. The constraint is passed with the value when a call is made.
Matching Actual and Formal Parameters
Two methods can be used to match actual and formal parameters: positional notation and named notation. Positional notation is simply association by position; that is, the order of the parameters used when executing the procedure matches the order in the procedure’s header exactly. Named notation is explicit association using the symbol =>. It has the following syntax:
formal_parameter_name => argument_value
In named notation, the order does not matter. If you mix notation, however, you should list the positional notation before the named notation.
Default values can be used if a call to the program does not include a value in the parameter list. Note that it makes no difference which style is used; both work in similar fashion.
For Example ch19_2.sql
CREATE OR REPLACE PROCEDURE find_sname
(i_student_id IN NUMBER,
o_first_name OUT VARCHAR2,
o_last_name OUT VARCHAR2
SELECT first_name, last_name
INTO o_first_name, o_last_name
WHERE student_id = i_student_id;
DBMS_OUTPUT.PUT_LINE('Error in finding student_id:
This procedure takes in a student_id via the parameter named i_student_id. It passes out the parameters o_first_name and o_last_name. The procedure is a simple SELECT statement that retrieves the first_name and last_name from the STUDENT table when thestudent_id matches the value of i_student_id, which is the only IN parameter that exists in the procedure. To call the procedure, a value must be passed in for the i_student_id parameter.
For Example ch19_3.sql
(145, v_local_first_name, v_local_last_name);
('Student 145 is: '||v_local_first_name||
' '|| v_local_last_name||'.'
When calling the procedure find_sname, a valid student_id should be passed in for the i_student_id. If it is not a valid student_id, an exception will be raised. Two variables must also be listed when calling the procedure. These variables, v_local_first_name andv_local_last_name, are used to hold the values of the parameters that are being passed out. After the procedure has been executed, the local variables will have values and can then be displayed with a DBMS_OUTPUT.PUT_LINE statement.
In this chapter, you learned how to create procedures. First, you saw how to create a basic procedure that has no parameters. Then, in the second part of the chapter, you saw how to add parameters to the procedure to narrow the transaction process taking place within that procedure.
By the Way
The companion website provides additional exercises and suggested answers for this chapter, with discussion related to how those answers resulted. The main purpose of these exercises is to help you test the depth of your understanding by utilizing all of the skills that you have acquired throughout this chapter.