field-datatype is the datatype of the field, which can be a specific PL/SQL datatype (such as NUMBER or BOOLEAN) or can reference a column's datatype using the %TYPE designation.

initial-value is an initial value that must be assigned to field-name if it's declared as NOT NULL.

Listing 10.20 illustrates the declaration of a user-defined record type. In this case, we're declaring a record type named Patient_Rec_Type that is composed of three fields: Patient_ID, Body_Temp, and Bed_Number. The first two fields exist in the Patient table; however, Body_Temp has a different name—Body_Temp_Deg_F—in the table. The third field, Bed_Number, doesn't exist in the Patient table.

Listing 10.20. Using a user-defined record type.

SQL> declare
  2
  2  type Patient_Rec_Type is record
  3  (Patient_ID   Patient.Patient_ID%TYPE,
  4   Body_Temp    Patient.Body_Temp_Deg_F%TYPE,
  5   Bed_Number   varchar2(4));
  6
  6  Patient_Rec   Patient_Rec_Type;
  7
  7  begin
  8
  8  dbms_output.enable;
  9
  9  Patient_Rec.Patient_ID := `ZZ0123';
 10  Patient_Rec.Body_Temp  := 98.6;
 11  Patient_Rec.Bed_Number := `A123';
 12
 12  dbms_output.put_line(`Patient ID: ` || Patient_Rec.Patient_ID);
 13  dbms_output.put_line(`Body_Temp: ` || to_char(Patient_Rec.Body_Temp));
 14  dbms_output.put_line(`Bed Number: ` || Patient_Rec.Bed_Number);
 15
 15  insert into Patient
 16  (Patient_ID, Body_Temp_Deg_F)
 17  values
 18  (Patient_Rec.Patient_ID, Patient_Rec.Body_Temp);
 19
 19  end;
 20  /

                                               continues       

Listing 10.20. continued

Patient ID: ZZ0123
Body_Temp: 98.6
Bed Number: A123

Specifying Default Values for Variables

By default, all variables are initialized to NULL whenever you enter a procedure, function, or anonymous block. You can initialize a variable in the PL/SQL declare section in two ways:

variable-name   data-type  := initial-value;

or

variable-name   data-type  DEFAULT initial-value;

Here is an anonymous block that illustrates both methods of initializing a PL/SQL variable:

SQL> declare
  2
  2  i             natural := 33;
  3  my_string     varchar2(30) default `JACKSON';
  4
  4  begin
  5
  5  dbms_output.enable;
  6
  6  end;
  7  /
PL/SQL procedure successfully completed.

One reason to specify a default value for a variable is that the resulting code is often easier to understand and maintain. By specifying a default value, you are making fewer assumptions about how the code will behave.

Summary

This lesson focused on the following concepts:

• You can use the Oracle DDL statements CREATE PROCEDURE and CREATE FUNCTION to create stored procedures and functions, respectively.
• A stored procedure or function is composed of PL/SQL declarations and executable statements that are compiled and stored in the database.
• You can define arguments for a stored procedure or function that input only, output only, or input and output.

• Oracle supplies a pre-defined package, DBMS_OUTPUT, that contains routines for displaying information from within a PL/SQL subprogram.
• A stored function can be referenced in an Oracle SQL statement. The datatype of each value passed to the function must match its corresponding argument.
• A package is a group of related procedures and functions. A package has a specification and a body.
• A package specification is created via the CREATE PACKAGE statement. A package body is created with the CREATE PACKAGE BODY statement.
• PL/SQL supports several datatypes that aren't available for use in Oracle SQL statements: BOOLEAN, BINARY_INTEGER, NATURAL, and POSITIVE.
• A BOOLEAN variable can have a value of TRUE, FALSE, or NULL.
• By using %TYPE, you can declare a variable to have the same datatype as a particular column in a table.
• Use the designation %ROWTYPE to declare a record whose structure is equivalent to a specified table. You also can use %ROWTYPE to declare a record with the same structure as a cursor.
• A PL/SQL table is a user-declared datatype that you can think of as an unbounded array composed of one field. To declare a PL/SQL table, you must first declare a table datatype.
• A user-defined record consists of one or more fields. Each field's datatype may be explicitly declared using an Oracle SQL or PL/SQL datatype, or it uses the %TYPE designation to point to the datatype of a database column. A field may be declared as NOT NULL and initialized in its declaration.

What Comes Next?

On Day 11, you learn about several important aspects of PL/SQL—how to handle errors, how to retrieve multiple rows with a cursor, and how to write a trigger on a table.

Q&A

Q Is it better to create a package that contains procedures and functions rather than individual procedures and functions?

A From a software development perspective, it's better to create a package. By creating a package specification, the interface—the names of each procedure and function and its arguments—is separate from the PL/SQL code that implements the interface.

Workshop

The purpose of the Workshop is to allow you to test your knowledge of the material discussed in the lesson. See if you can correctly answer the questions in the quiz and complete the exercises before you continue with tomorrow's lesson.

Quiz

1. True or false: A stored procedure can call a stored function, but a stored function cannot call a stored procedure.
2. Name three reasons for using stored procedures, functions, and packages in an application.
3. What tools can be used to develop PL/SQL subprograms?
4. If "x > 32" is assigned to a PL/SQL variable, what is the datatype of the variable?

Exercises

1. Create an anonymous PL/SQL block that will call the Assign_Grade procedure in the Flugle package and assign a B to Anna Anastatia for Biology 101.
2. A student, Jackson Smythe, has created a stored function named Change_My_Grade with two arguments: Student ID and Class ID. The function changes the grade for any specified student and class to an A+. Write out the statements that will create this function.