Previous | Table of Contents | Next

Page 204

Listing 9.10 Continued

 8  fetch a into old_lname;
 9  while (a%found) loop
 10   update employee set last_name = upper(last_name)
 11    where current of a;
 12   fetch a into old_lname;
 13  end loop;
 14  close a;
 15  commit;
 16 end;
 17 /

PL/SQL procedure successfully completed.

This PL/SQL block in this listing is a modified version of the one in Listing 9.9. The PL/SQL block in this listing does not have a need for the pay# local variable used for the primary key of the record. This variable was eliminated from the block along with the where clause of the update statement.

The Where Current Of clause has another advantage besides making your code simpler. It increases the speed of your record processing. Because it uses the last record fetched from the database, Oracle knows the record's exact location. Oracle does not have to perform any searches such as an index or tablespace scan to find the record. This increases the performance of the application. It especially enhances performance if the tables are large or the update statement where clause must perform tablespace scans to identify the row to be updated.

Creating and Using Named Procedures

Up to this point, the PL/SQL blocks discussed have been anonymous blocks. This means they did not have a Header section containing a name. Another block cannot call or execute them because they are nameless. This section, however, covers procedures. They are named PL/SQL blocks that are callable by other objects.

The Header section of the procedure block determines the way the block must be called. The block's name and a parameter list comprise the header's components. You include the parameter list when values are to be passed to or from the procedure. The procedure also has a Declare section, an Executable section, and an Exception section. Besides having the Header section, the procedure block is the same as an anonymous block. Figure 9.4 shows the structure of a procedure.

The procedure name listed in the header section is used to call the block. The parameters are used to bring values into and out of the procedure. The Declare section is placed after the keyword Is. Procedures do not have to contain the keyword Declare. If the procedure has a nested block, this block can have a Declare section using the Declare keyword. Listing 9.11 illustrates the creation and execution of a procedure that lists the employees in a particular department. The department number is passed into the procedure through the parameters listed in the header.

Page 205

FIG. 9.4
Procedure structure.



Listing 9.11 L_09_11.SQL—Using a Named Procedure to List Employees

SQL> set serveroutput on;
SQL> create procedure dept_employees (dept in char)
  2  is
  3    cursor a is select last_name from employee
  4      where fk_department = dept
  5      order by 1;
  6  begin
  7    for deptlist in a
  8       loop
  9       dbms_output.put_line (deptlist.last_name);
 10       end loop;
 11  end dept_employees;
 12  /

Procedure created.

SQL>
SQL> begin
  2    dept_employees (`WEL');
  3  end;
  4  /
ANTHONY
CARTER
HOOVER
REAGAN
ROOSEVELT
TAFT

Page 206

Listing 9.11 Continued

PL/SQL procedure successfully completed.

SQL> drop procedure dept_employees;

Procedure dropped.

Procedures are not like anonymous blocks. They cannot be executed directly; they must be called from another application. Procedures must also be defined in the database using the Create Procedure command before they can be called. The first block of the preceding Listing illustrates the creation of the procedure called "dept_employees". The second PL/SQL block illustrates calling the stored procedure from an anonymous block. The purpose of the procedure is to list the employees of a particular department.

The procedure is passed the name of the department using the variable in the Header section. This section has a variable called "dept". It is followed by the keyword In and the data type of the variable. The In keyword means the variable is used as an input variable. It cannot be used to pass values out of the procedure. To pass values out, the Out parameter is used. When the variable is used for input and output, the In Out parameter is used. When defining the variable data type, you do not have to specify its size.

The procedure contains a cursor that is used in a Cursor For loop to display the employee last names. The cursor select statement uses the input variable "dept" in its where clause to select only the employees of the "WEL" or Welfare department.

The second block is an anonymous PL/SQL block that calls the "dept_employees" procedure. It calls the procedure by listing the name of the procedure as if it were a statement. In fact, the name of a procedure is a statement and should be treated as one. Because this procedure is expecting an input variable, the calling statement must supply it. This is accomplished by the literal "WEL" that follows the procedure name. "WEL" is the code for the Welfare department. It is used in the cursor where clause. The procedure is then executed.

You can eliminate procedures by using the Drop Procedure command.

NOTE
Procedures are a good technique to modularize your code. I have managed projects that developed multiple applications that perform the same calculation. It is surprising that the same calculation in different applications sometimes produces different results. A single stored procedure that is callable from the various applications would solve this problem.n

Creating and Using Functions

A function is a block of PL/SQL code called from another application. A function differs from a procedure in that a function always returns a value. It also differs in that it cannot be called as its own statement. It must always be called as part of an executable statement. In fact, a

Page 207

function is used as an expression in a statement because the function returns a value and the value is used in the expression.

Figure 9.5 illustrates the structure of a function. It begins with the keyword Function followed by the pass into and out of parameters. The second line is used to define the Return data type parameter. The remainder of the block contains the Declaration, Executable, and Exception sections. Like a procedure, the function must be stored in the database for it to be called by another application.

FIG. 9.5
The structure of a
function.



Return data type is used to define the value that will be supplied to the calling expression. It can be any of the approved types such as number or varchar2. It cannot be an exception data type or a cursor name. The data type of the return parameter must agree with the data type the expression is expecting. In the expression a = 3, for example, if you replace the value 3 with a function, the return type of the function must be number. Specifying a different data type causes an error.

In Listing 9.12, a PL/SQL block calculates the average age of the employees hired for each department. This value is used in a calling expression to compare the hiring age of each employee to the department average. The application computes the difference in the employee's age at hire and the average for his or her department.

Previous | Table of Contents | Next