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.
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.
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, ...])]
AS
[local declarations]
BEGIN
executable statements
[EXCEPTION
exception handlers]
END [name];
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.
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
AS
CURSOR c_group_discount
IS
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,
e.section_id, s.section_id
HAVING COUNT(*) >=8;
BEGIN
FOR r_group_discount IN c_group_discount
LOOP
UPDATE course
SET cost = cost * .95
WHERE course_no = r_group_discount.course_no;
DBMS_OUTPUT.PUT_LINE
('A 5% discount has been given to '||
r_group_discount.course_no||' '||
r_group_discount.description
);
END LOOP;
END;
To execute the stored procedure Discount
, the following syntax is used:
EXECUTE Procedure_name
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.
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
FROM user_objects
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
FROM user_source
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.
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.
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.
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.
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
)
AS
BEGIN
SELECT first_name, last_name
INTO o_first_name, o_last_name
FROM student
WHERE student_id = i_student_id;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE('Error in finding student_id:
'||i_student_id);
END find_sname;
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 the student_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.
DECLARE
v_local_first_name student.first_name%TYPE;
v_local_last_name student.last_name%TYPE;
BEGIN
find_sname
(145, v_local_first_name, v_local_last_name);
DBMS_OUTPUT.PUT_LINE
('Student 145 is: '||v_local_first_name||
' '|| v_local_last_name||'.'
);
END;
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
and v_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.