Week 2

Day 10

Program Development with PL/SQL

During our last lesson, you learned about some of the programming constructs used in PL/SQL. In this lesson, you learn how to construct PL/SQL modules—procedures, functions, and packages—that are stored in an Oracle database. You'll also learn about the use of PL/SQL datatypes that aren't available in SQL.

A stored procedure or function is a PL/SQL program stored in an Oracle database and invoked by a user, either directly or indirectly. The benefits of using stored procedures and functions are:

• Efficiency: In a client/server architecture, a client application issues SQL requests to a database server. As the number of users increases, so does the number of SQL requests, and the network can quickly become the performance bottleneck. The use of stored procedures offers a significant performance improvement because a single call to a stored procedure can invoke multiple SQL statements that are executed on the server, thereby reducing network traffic.

• Reusability: A PL/SQL program is written once and can be used in a variety of situations—in SQL scripts, in database triggers, and in client application logic.
• Portability: You can use a stored procedure in any Oracle8 database, regardless of platform. Consequently, you don't have to deal with compatibility issues such as the operating system and compiler releases. If Oracle8 is supported by the platform, the stored procedure can be ported to it without any additional changes. Of course, if the stored procedure contains references to file and directory names, you might need to make additional changes.
• Maintainability: A stored procedure is designed to perform a specific task. Such a task might be required by a database trigger, an SQL*Plus script, an application program, or another stored procedure. By calling the same stored procedure from all of these sources, you reduce the cost of software maintenance.

Creating a Stored Procedure or Function

Using a text processor, such as Notepad or WordPad, to construct a stored procedure is a good idea. A better idea is to use one of the components in Oracle Developer/2000— Procedure Builder—which is discussed during the lesson on Day 16, "Developer/2000: Using Oracle Graphics and Procedure Builder." With Notepad or WordPad open, you can copy the stored procedure and paste it into SQL*Plus or SQL Worksheet for development and testing.

The syntax for creating a stored procedure is as follows:

CREATE [OR REPLACE] PROCEDURE procedure-name
[(argument1 ... [, argumentN) ] IS
[local-variable-declarations]
BEGIN
executable-section

[exception-section]
END [procedure-name];

The variables are defined as follows:

procedure-name is the procedure name subject to Oracle database object-naming restrictions.

argument1 through argumentN are optional argument declarations that consist of

   argument-name [IN | OUT] datatype [ {:= | DEFAULT} value]

local-variable-declarations are optional declarations of variables, constants, and other procedures and functions local to procedure-name.

executable-section are the PL/SQL statements that compose the procedure.

exception-section is the optional exception handling section of the procedure.

For example, Listing 10.1 contains a stored procedure that has a single argument used by the DELETE statement to determine which classes to remove from the Course table.

Listing 10.1. Creating a stored procedure with a single argument.

SQL> create or replace procedure Delete_Specified_Course
  2         (Description_Phrase varchar2) is
  3
  3  begin
  4
  4  delete from Course
  5  where
  6  upper(Description) like Description_Phrase;
  7
  7  end;
  8  /
Procedure created.

The syntax for creating a stored function is very similar to the syntax for creating a stored procedure. Of course, a stored function also must return a value.

CREATE [OR REPLACE] FUNCTION function-name
[(argument1 ... [, argumentN) ]
RETURN function-datatype IS
[local-variable-declarations]
BEGIN
executable-section
[exception-section]
RETURN function-value
END [function-name];

The variables are defined as follows:

function-name is the function name subject to Oracle database object-naming
restrictions.

argument1 through argumentN are optional argument declarations that consist of

argument-name [IN | OUT] datatype [ {:= | DEFAULT} value]

function-datatype is the datatype of the value returned by the function.

local-variable-declarations are optional declarations of variables, constants, and other procedures and functions local to function-name.

executable-section are the PL/SQL statements that compose the function.

exception-section is the optional exception-handling section of the function.

function-value is the value that the function returns to the caller.

Listing 10.2 illustrates how to create a stored function that obtains the grade point average of a student.

Listing 10.2. Creating a stored function with a single argument.

create or replace function student_GPA (arg_student_ID IN varchar2)
   return number is

GPA number;

begin

select avg(decode(grade, `A+', 4.25, `A', 4, `A-', 3.75,
                         `B+', 3.25, `B', 3, `B-', 2.75,
                         `C+', 2.25, `C', 2, `C-', 1.75,
                         `D+', 1.25, `D', 1, `D-', 0.75, `F', 0))
into GPA
from student_schedule
where