The variables are defined as follows:

procedure-name is the procedure name and 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 is the PL/SQL statements that compose the procedure.

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

To illustrate this syntax, Listing 9.13 contains an example of an anonymous block that declares a procedure named Record_Patient_Temp_Deg_C. This procedure has two arguments: the patient ID and the patient's body temperature as measured in degrees Centigrade (see line 4). On line 3, the variable High_Fever is initialized to 42. On line 17, the procedure is invoked with two arguments: New_Patient_ID (which is GG9999) and High_Fever. The SELECT statement that follows the anonymous block demonstrates that the procedure did what it was supposed to—it converted 42 degrees Centigrade to 107.6 degrees Fahrenheit.

Listing 9.13. Example of a procedure.

SQL> declare
  2
  2  New_Patient_ID  Patient.Patient_ID%type;
  3  High_Fever      constant real := 42.0;
  4
  4  procedure Record_Patient_Temp_Deg_C (Patient_ID varchar2,
  5                                       Body_Temp_Deg_C real) is
  6

                                                       continues

Listing 9.13. continued

  6  Temp_Deg_F real;
  7
  7  begin
  8
  8    Temp_Deg_F := (9.0/5.0)*Body_Temp_Deg_C + 32.0;
  9
  9    insert into Patient
 10    (Patient_ID, Body_Temp_Deg_F)
 11    values
 12    (Patient_ID, Temp_Deg_F);
 13
 13    commit;
 14  end;
 15
 15  begin
 16
 16  New_Patient_ID := `GG9999';
 17
 17  Record_Patient_Temp_Deg_C (New_Patient_ID, High_Fever);
 18
 18  end;
 19  /
PL/SQL procedure successfully completed.

SQL> select Patient_ID, Body_Temp_Deg_F
  2  from Patient
  3  where
  4  Patient_ID = `GG9999';
PATIEN BODY_TEMP_DEG_F
------ ---------------
GG9999           107.6

Listing 9.14 demonstrates that the variables declared within a procedure are not accessible outside of the procedure.

Listing 9.14. Example of variable scope in PL/SQL.

SQL> declare
  2
    2  procedure Delete_Patients is
  3
  3  Temp_Deg_F real;
  4
  4  begin
  5
   5    delete from Patient
  6    where
  7    Patient_ID = `GG3333';

  8
  8    commit;
  9
  9  end;
 10
 10  begin
 11
 11  Temp_Deg_F := 100.0;
 12
 12  end;
 13  /
Temp_Deg_F := 100.0;
*
ERROR at line 11:
ORA-06550: line 11, column 1:
PLS-00201: identifier `TEMP_DEG_F' must be declared
ORA-06550: line 11, column 1:
PL/SQL: Statement ignored

ANALYSIS
Listing 9.14 begins with the declaration section of an anonymous block. At line 2, a procedure, Delete_Patients, is declared; within the procedure, the variable Temp_Deg_F is declared as a REAL. However, at line 11, the anonymous block references Temp_Deg_F, which results in the PL/SQL error message PLS-00201. This listing illustrates that the scope of a local variable in a procedure or function doesn't extend to another subprogram that invokes that procedure or function.

For further information on the use of stored procedures, see the lesson on Day 11.

Declaring a Function

A PL/SQL function declaration is similar to a procedure declaration—except that the function returns a value of a predefined datatype.

The syntax for declaring a function is

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

The variables are defined as follows:

function-name is the function name and 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 returned by function-name.

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

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

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

For instance, Listing 9.15 provides an example of the declaration and use of a function. The function, Max_Additional_Fees, has a single argument, Dept_ID (see line 3). The function returns the Course_ID that has the highest additional fees of all courses in the specified department (the SELECT statement begins on line 9). The anonymous block calls the function with Dept_ID equal to ECON (see line 24) and the function returns Course_ID 189. The query at the end of Listing 9.15 shows that 189 does indeed have the highest additional fees.

Listing 9.15. Example of a function.

SQL> declare
  2
  2  Course_ID  Course.Course_ID%type;
  3
  3  function Max_Additional_Fees (Dept_ID IN varchar2)
  4           return varchar2 is
  5
  5  Additional_Fees Course.Additional_Fees%type;
  6  Units           Course.Units%type;
  7  Course_ID       Course.Course_ID%type;
  8
  8  begin
  9
  9  select Course_ID
 10  into   Course_ID
 11  from Course
 12  where
 13  Department_ID = Dept_ID and
 14  Additional_Fees =
 15  (select max(Additional_Fees)
 16   from Course
 17   where
 18   Department_ID = Dept_ID);
 19
 19  return Course_ID;
 20
 20  end;
 21
 21  --  Beginning of executable section of anonymous block.
 22
 22  begin

 23
 23  dbms_output.enable;
 24
 24  Course_ID := Max_Additional_Fees (`ECON');
 25
 25  dbms_output.put_line(`Course_ID: ` || Course_ID);
 26
 26  end;
 27  /
Course_ID: 189

PL/SQL procedure successfully completed.

SQL> select Course_ID, Additional_Fees
  2  from Course
  3  where
  4  Department_ID = `ECON'
  5  order by Course_ID;

COURS ADDITIONAL_FEES
----- ---------------
101                25
189               750
199                 0

Procedure and Function Arguments

Each procedure and function argument can optionally be defined as one of the following:

Listing 9.16 presents an example of how all three types of arguments are used.