Skip Headers
Oracle® Database 2 Day Developer's Guide
11g Release 1 (11.1)

B28843-04
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

4 Developing and Using Stored Procedures

This chapter introduces the use of PL/SQL, the imperative language of Oracle Database.

This chapter contains the following sections:

Overview of Stored Procedures

You already know how to interact with the database using SQL, but it is not sufficient for building enterprise applications. PL/SQL is a third generation language that has the expected procedural and namespace constructs, and its tight integration with SQL makes it possible to build complex and powerful applications. Because PL/SQL is executed in the database, you can include SQL statements in your code without having to establish a separate connection.

The main types of program units you can create with PL/SQL and store in the database are standalone procedures and functions, and packages. Once stored in the database, these PL/SQL components, collectively known as stored procedures, can be used as building blocks for several different applications.

While standalone procedures and functions are invaluable for testing pieces of program logic, Oracle recommends that you place all your code inside a package. Packages are easier to port to another system, and have the additional benefit of qualifying the names of your program units with the package name. For example, if you developed a schema-level procedure called continue in a previous version of Oracle Database, your code would not compile when you port it to a newer Oracle Database installation. This is because Oracle recently introduced the statement CONTINUE that exits the current iteration of a loop and transfers control to the next iteration. If you developed your procedure inside a package, the procedure package_name.continue would have been protected from such name capture.

This next section of this chapter is "Creating and Using Standalone Procedures and Functions", shows you how to create and use standalone procedures and functions. You may wish to skip it and move directly to "Creating and Using Packages".

Creating and Using Standalone Procedures and Functions

With Oracle Database, you can store programs in the database, so commonly used code can be written and tested once and then accessed by any application that requires it. Program units that reside in the database also ensure that when the code is invoked the data is processed consistently, which leads to ease and consistency of the application development process.

Schema-level, or standalone subprograms such as functions (which return a value) and procedures (which do not return a value) are compiled and stored in an Oracle Database. Once compiled, they become stored procedure or stored function schema objects, and can be referenced or called by any applications connected to Oracle Database. At invocation, both stored procedures and functions can accept parameters.

Procedures and functions follow the basic PL/SQL block structure, which consists of the following elements:

The general form of a PL/SQL block follows. Note also that each stored program unit has a header that names the unit and identifies it as either a function, procedure, or a package.

Header AS
[declaration statements
  ...]
BEGIN
  ...
[EXCEPTION
  ...]
END;

See Also:

Creating Procedures and Functions

The SQL statements for creating procedures and functions are CREATE PROCEDURE and CREATE FUNCTION, respectively. In practice, it is best to use a CREATE OR REPLACE statement. The general form of these statements follows.

CREATE OR REPLACE procedure_name(arg1 data_type, ...) AS
BEGIN
  ....
END procedure_name;

CREATE OR REPLACE procedure_name(arg1 data_type, ...) AS
BEGIN
  ....
END procedure_name;

To create a procedure:

You will create a procedure add_evaluation that creates a new row in the evaluations table.

  1. In the Connections navigation hierarchy, right-click Procedures.

  2. Select New Procedure.

    Description of create_procedure_1.gif follows
    Description of the illustration create_procedure_1.gif

  3. In the New Procedure window, set the following parameters:

    • Ensure that Schema is set to HR.

    • Set Name to ADD_EVALUATION.

    In the Parameters tab, click the Add Column icon ('plus' sign) and specify the first parameter of the procedure. Set Name to eval_id, set Type to NUMBER, set Mode to IN, and leave Default Value empty.

    Similarly, add the following parameters, in this order:

    • employee_id: set Type to NUMBER, set Mode to IN, and leave Default Value empty.

    • evaluation_date: set Type to DATE, set Mode to IN, and leave Default Value empty.

    • job_id: set Type to VARCHAR2, set Mode to IN, and leave Default Value empty.

    • manager_id: set Type to NUMBER, set Mode to IN, and leave Default Value empty

    • department_id: set Type to NUMBER, set Mode to IN, and leave Default Value empty

    Click OK.

    Description of create_procedure_2.gif follows
    Description of the illustration create_procedure_2.gif

  4. The ADD_EVALUATION pane opens with the following code.

    Note that the tile of the pane is in italic font, which indicates that the procedure is not saved in the database.

    CREATE OR REPLACE
    PROCEDURE ADD_EVALUATION
    ( evaluation_id IN NUMBER
    , employee_id IN NUMBER
    , evaluation_date IN DATE
    , job_id IN VARCHAR2
    , manager_id IN NUMBER
    , department_id IN NUMBER
    ) AS
    BEGIN
      NULL;
    END ADD_EVALUATION;
    
  5. From the File menu, select Save to save the new procedures. Alternatively, use the CTRL + S key combination.

    Note that Oracle Database automatically compiles procedures prior to saving them.

    Description of create_procedure_3.gif follows
    Description of the illustration create_procedure_3.gif

    Note that the tile of the add_evaluation pane is in regular font, not italic; this indicates that the procedure is saved to the database

To create a function:

You will create a new function calculate_score, which calculates the weighted score based on the performance in a particular category.

  1. In the Connections navigation hierarchy, right-click Functions.

  2. Select New Function.

    Description of create_function_1.gif follows
    Description of the illustration create_function_1.gif

  3. In the New Function window, set the following parameters:

    • Ensure that Schema is set to HR.

    • Set Name to CALCULATE_SCORE.

    In the Parameters pane, set the <return> Type to NUMBER.

    Similarly, add the following parameters, in this order:

    • cat: set Type to VARCHAR2, set Mode to IN, and leave Default Value empty.

    • score: set Type to NUMBER, set Mode to IN, and leave Default Value empty

    • weight: set Type to NUMBER, set Mode to IN, and leave Default Value empty

    Click OK.

    Description of create_function_2.gif follows
    Description of the illustration create_function_2.gif

  4. The calculate_score pane opens with the following code.

    Note that the tile of the pane is in italic font, which indicates that the procedure is not saved in the database.

    CREATE OR REPLACE
    FUNCTION calculate_score
    ( cat IN VARCHAR2
    , score IN NUMBER
    , weight IN NUMBER
    ) RETURN NUMBER AS
    BEGIN
      RETURN NULL;
    END calculate_score;
    
  5. From the File menu, select Save to save the new function. Alternatively, use the CTRL + S key combination.

    Note that Oracle Database automatically compiles functions prior to saving them.

    Note that the tile of the calculate_score pane is in regular font, not italic; this indicates that the procedure is saved to the database

See Also:

Modifying Procedures and Functions

You already created a new procedure and a new function. However, they both consist of only the subprogram signature. In this section, you will edit a subprogram body.

To modify a function:

You will edit the function calculate_score to determine the weighted value of an evaluation for a particular category.

  1. In the calculate_score pane, replace the body of the function with the following code. The new code is in bold font.

    BEGIN
      RETURN score * weight;
    END calculate_score;
    
  2. Compile and save the function; you may use the CTRL + S key combination.

See Also:

Testing Procedures and Functions

Next, you will test the function that you just modified.

To test a function:

You will test the function calculate_score.

  1. In the Connections navigator hierarchy, right-click the calculate_score function. Select Run.

    Description of run_function_1.gif follows
    Description of the illustration run_function_1.gif

  2. In the Run PL/SQL window, click inside the PL/SQL Block pane, and edit the assignments for the score and weight variables. The new code is in bold font.

    v_Return := CALCULATE_SCORE(
        CAT => CAT,
        SCORE => 8,
        WEIGHT => 0.2
      );
    

    Click OK.

  3. In the Running - Log pane, note the following results:

    Connecting to the database hr_conn.
    v_Return = 1.6
    Process exited.
    Disconnecting from the database hr_conn.
    

See Also:

Dropping Procedures and Functions

You can delete a procedure or function from the database using either the Connection Navigator, or the SQL DROP statement.

To drop a procedure:

You will drop the procedure ADD_EVALUATION.

  1. In the Connections navigator hierarchy, right-click the ADD_EVALUATION function.

    Select Drop.

    Description of drop_procedure_1.gif follows
    Description of the illustration drop_procedure_1.gif

  2. In the Drop window, click Apply.

    Description of drop_procedure_2.gif follows
    Description of the illustration drop_procedure_2.gif

  3. In the Confirmation dialog box, click OK.

You dropped the ADD_EVALUATION procedure from the database.

See Also:

Creating and Using Packages

In the preceding section, you created and tested procedures and functions that were schema objects. This approach can be useful for testing subsets or small features of your application.

Enterprise level applications have much greater complexity: some of the interfaces and types are directly available to user, while others are used only by ohter functions and procedures and are never called by the user. PL/SQL enables you to formally state the relationship between these subprograms by placing them in the same package, which is a schema object that groups and name-qualifies logically related elements such as PL/SQL types, variables, functions and procedures. Encapsulating these elements inside a package also prevents, over the life time of the applications, unintended consequences such as name capture that is discussed in "Overview of Stored Procedures".

Procedures and functions that are defined within a package are known as packaged subprograms. Procedures and functions that are nested within other subprograms or within a PL/SQL block are called local subprograms; they exist only inside the enclosing block and cannot be referenced externally.

Another reason that standalone procedures and functions, like the ones in "Creating and Using Standalone Procedures and Functions", are limited to large-scale development is that they can only send and receive scalar parameters (NUMBER, VARCHAR2, and DATE), but cannot use a composite structure, RECORD, unless it is defined in a package specification.

Packages usually have two parts: a specification and a body.

The package is defined by the package specification, which declares the types, variables, constants, exceptions, cursors, functions and procedures that can be referenced from outside of the package. The specification is the interface to the package. Applications that call the subprograms in a package only need to know the names and parameters from the package specification.

The standard package specification has this form:

CREATE OR REPLACE PACKAGE package_name AS
  type definitions for records, index-by tables
  constants
  exceptions
  global variable declarations
  procedure procedure_1(arg1, ...);
  ...
  function function_1(arg1,...) return datat_ype;
  ...
END package_name;

The package body contains the code that implements these subprograms, the code for all private subprograms that can only be invoked from within the package, and the queries for the cursors. You can change the implementation details inside the package body without invalidating the calling applications.

The package body has this form:

CREATE OR REPLACE PACKAGE BODY package_name AS
  PROCEDURE procedure_1(arg1,...) IS
    BEGIN
      ...
    EXCEPTION 
      ...
    END procedure_1;
  ...
  FUNCTION function_1(arg1,...)  RETURN data_type IS result_variable data_type
    BEGIN
      ...
      RETURN result_variable;
    EXCEPTION
      ...
  END function_1;
  ...
END package_name;

See Also:

Guidelines for Packages

You should become familiar with the packages supplied with Oracle Database and avoid writing code that duplicates existing features.

You should design and define the package specification before writing the implementation in the package body. In the specification, include only those parts that must be publicly visible to calling programs, and hide private declarations within the package body. This prevents unsafe dependencies of other programs on your implementation details.

Because PL/SQL has a single-pass compiler, you may find that the dependencies between correct and valid subprograms within the package body prevent you from successfully compiling your package. You then need to declare these unknown subprograms near the top of the package body, and specify them later. For this reason, Oracle recommends that you add new elements at the end of the package specification or body to minimize possible invalidation of dependents.

See Also:

Creating a Package

You will create a package that encapsulates all the functionality necessary to perform employee evaluations. After you create the package, "Modifying a Package" explains how you modify the package and to create the package body.

To create a package in SQL Developer navigation hierarchy:

  1. In the Connections navigation hierarchy, right-click Packages.

  2. Select New Package.

    Description of create_package_1.gif follows
    Description of the illustration create_package_1.gif

  3. In the Create PL/SQL Package dialog, set the following parameters:

    • Ensure that Schema is set to HR.

    • Set Name to EMP_EVAL.

      Click OK.

    Description of create_package_2.gif follows
    Description of the illustration create_package_2.gif

  4. The emp_eval pane opens with the following code:

    CREATE OR REPLACE PACKAGE emp_eval AS
     
      /* TODO enter package declarations (types, exceptions, methods etc) here */
     
    END emp_eval;
    

    Note that the title of the pane is in italic font, which indicates that the package is not saved to the database.

  5. From the File menu, select Save to compile and save the new package. Alternatively, use the CTRL + S key combination.

    In the Messages - Log pane, the system confirms that the package was created:

    EMP_EVAL Compiled.
    

    Note that the title of the emp_eval pane is in regular font, not italic; this indicates that the procedure is saved to the database.

Example 4-1 shows how to create a package directly in the SQL Worksheet.

Example 4-1 Creating a PL/SQL Package

CREATE OR REPLACE PACKAGE eval AS
  /* package */
END eval;

The results of the script follow.

PACKAGE eval Compiled.

See Also:

Modifying a Package

In this section, you will modify package emp_eval.

To change the package specification:

You will change the package specification of emp_eval by specifying some functions and procedures.

  1. In the Connections navigation hierarchy, select Packages, and then right-click emp_eval.

  2. Select Edit.

    Description of mod_package_1.gif follows
    Description of the illustration mod_package_1.gif

  3. In the EMP_EVAL pane, edit the package. The new code is in bold font.

    create or replace
    PACKAGE emp_eval AS
      PROCEDURE eval_department(department_id IN NUMBER);
      FUNCTION calculate_score(evaluation_id IN NUMBER
                             , performance_id IN NUMBER) 
                               RETURN NUMBER;
    END emp_eval;
    
  4. Compile the package specification.

    You will see the following message that confirms that the package compiled correctly.

    EMP_EVAL Compiled.
    

To create a package body:

You will create a package body for emp_eval by specifying some functions and procedures.

  1. In the Connections navigation hierarchy, right-click emp_eval.

  2. Select Create Body.

    Description of create_package_4.gif follows
    Description of the illustration create_package_4.gif

  3. In the emp_eval Body pane, you can see the automatically generated code for the package body.

    CREATE OR REPLACE
    PACKAGE BODY emp_eval AS
     
      PROCEDURE eval_department(department_id IN NUMBER) AS
      BEGIN
        /* TODO implementation required */
        NULL;
      END eval_department;
     
      FUNCTION calculate_score(evaluation_id IN NUMBER
                             , performance_id IN NUMBER)
                               RETURN NUMBER AS
      BEGIN
        /* TODO implementation required */
        RETURN NULL;
      END calculate_score;
    
    END emp_eval;
    
  4. Compile and save the package body.

    You will see the following message that confirms that the package body compiled correctly.

    EMP_EVAL Body Compiled.
    

See Also:

Dropping a Package

You can delete a package from the database either by using the Connections navigator hierarchy or the SQL DROP statement. When you drop a package, you remove from the database both the package specification and its package body.

To drop a package:

  1. In the Connections navigator hierarchy, select Packages, and then right-click the EVAL package.

  2. Select Drop Package. Description of drop_package_1.gif follows
    Description of the illustration drop_package_1.gif

  3. In the Drop Package dialog, click Apply.

    Description of drop_package_2.gif follows
    Description of the illustration drop_package_2.gif

  4. In the Confirmation dialog, click OK.

See Also:

Using Variables and Constants

One of the significant advantages that PL/SQL offers over SQL is its ability to use variables and constants in programming constructs.

A variable is defined by the user to hold a specified value of a particular data type. This value is mutable; it can change at runtime.

A constant holds a value that cannot be changed; the compiler ensures that this value is immutable and does not compile any code that could change it. You should use constants in your code instead of direct values because they will make it simpler to maintenance of your code base over time. When you declare all values that do not change as constants, this optimizes your compiled code.

See Also:

PL/SQL Data Types

In addition to the SQL data types such as VARCHAR2, DATE, NUMBER, and so on, Oracle Database supports data types that you can use only through PL/SQL. These data types include BOOLEAN, composite data types such as RECORD, reference types such as REF CURSOR and INDEX BY TABLE, and numerous specialized types that represent numbers, characters, and date elements. One numeric type, PLS_INTEGER, is especially useful because it performs binary integer arithmetic and has significant performance benefits. Note that these PL/SQL types cannot be used at the level of the schema (and therefore, in tables), but only for types and processes that are defined within a package.

See Also:

Using Variables and Constants

Variables and constants can have any SQL or PL/SQL data type, and are declared in the declaration block of a subprogram. By default, any variable that is declared has a value of NULL. When defining a constant, you must use the CONSTANT clause, and assign a value immediately.

Using Comments

In PL/SQL, in-line comments start with a double hyphen, --, and extend to the end of the line. Multi-line comments must start with a slash and asterisk, /*, and terminate with an asterisk and a slash, */.

Using Identifiers

Identifiers name PL/SQL program units such as constants, variables, and subprograms. All identifiers must have at most 30 characters, and must start with a letter that is followed by any combination of letters, numerals, and the signs '$', '_', and '#'. Other characters cannot be used in identifiers.

Note that because PL/SQL is not case-sensitive except in managing string and character literals, you can use uppercase and lowercase letters interchangeably. This means that an identifier last_name is equivalent to LAST_NAME. Declaring the second identifier generates an error.

You should use meaningful names for your variables and constants, and use a good naming convention. For example, you could start each constant name with 'cons_'. Also, remember not to use reserved words as identifiers.

See Also:

Declaring Variables and Constants

You will update the new function of the emp_eval package, calculate_score, which calculates the final score for the employee evaluation by combing all weighted scores in different categories.

To declare variables and constants:

  1. In the Connections navigation hierarchy, click the plus sign (+) beside Packages to expand the group.

  2. Click the 'plus' beside emp_eval to expand the package.

  3. Right-click EMP_EVAL Body.

  4. Select Edit.

    emp_eval Body pane appears.

    Description of edit_package_body_1.gif follows
    Description of the illustration edit_package_body_1.gif

  5. In the emp_eval Body pane, modify function calculate_score by adding variables and constants, as shown by the following code. New code is bold font.

      FUNCTION calculate_score(evaluation_id IN NUMBER
                             , performance_id IN NUMBER) 
                               RETURN NUMBER AS
        n_score       NUMBER(1,0);                     -- a variable
        n_weight      NUMBER;                          -- a variable
        max_score     CONSTANT NUMBER(1,0) := 9;       -- a constant limit check
        max_weight    CONSTANT NUMBER(8,8) := 1;       -- a constant limit check
      BEGIN
        RETURN NULL;
      END calculate_score;
    
  6. Use the key combination 'CTRL'+'S' to save the updated package body.

    The following message appears in the Messages-Log pane:

    EMP_EVAL Body Compiled
    

See Also:

Declaring Variables with Structure Identical to Database Columns

In "Declaring Variables and Constants", you modified function calculate_score by adding two variables, n_score and n_weight. These variables will represent values from tables in the database: n_score is stored in the scores table, and n_weight is stored in the performance_parts table. The data types you used for these variables match the column data type definitions in the tables.

Over time, applications evolve and the column definitions may change; this may invalidate the calculate_score function. For easier code maintenance, you should use special qualifiers that declare variables with data types that match the definitions of the appropriate columns and rows. These qualifiers are %TYPE and %ROWTYPE.

  • The %TYPE attribute supplies the data type of a table column or another variable. This has the advantages of guaranteeing the correct data type assignment, and the correct implementation of the function at runtime if the data type of the table column changes.

  • The %ROWTYPE attribute supplies the definition of a row in a table to a RECORD variable. Columns in a table row and the corresponding fields in a RECORD have the same names and data types. The advantages of using %ROWTYPE are the same as for %TYPE. See "Using Composite Data Structures; Records" for a demonstration.

The following task shows how to use the %TYPE attribute in a function. You will edit the function calculate_score to assign to variables n_score and n_weight the data types that match the columns of the source tables. Note that the constants max_score and max_weight will be used to check equivalence to table values, so they too must match the table types.

To use the %TYPE attribute:

  1. In the emp_eval Body pane, modify function calculate_score by changing the definition of the variables, as shown by the following code. New code is bold font.

    FUNCTION calculate_score(evaluation_id IN scores.evaluation_id%TYPE
                           , performance_id IN scores.performance_id%TYPE) 
                             RETURN NUMBER AS
      n_score       scores.score%TYPE;               -- from SCORES
      n_weight      performance_parts.weight%TYPE;   -- from PERFORMANCE_PARTS
      max_score     CONSTANT scores.score%TYPE := 9; -- a constant limit check
      max_weight    CONSTANT performance_parts.weight%TYPE := 1;      
                                                     -- a constant limit check
    BEGIN
      RETURN NULL;
    END calculate_score;
    
  2. In the emp_eval package specification, change the declaration of the function calculate_score.

    FUNCTION calculate_score(evaluation_id IN scores.evaluation_id%TYPE
                           , performance_id IN scores.performance_id%TYPE)
                             RETURN NUMBER;
    
  3. In the Connections navigation hierarchy, right-click the emp_eval package, and select Compile. Alternatively, use the Ctrl+Shift+F9 keyboard shortcut.

    Description of compile.gif follows
    Description of the illustration compile.gif

    The following message appears in the Messages-Log pane:

    EMP_EVAL Body Compiled
    

To use the %ROWTYPE attribute:

Look at the code used in the eval_department procedure in"Using Explicit Cursors" .

Assigning Values to Variables

You can assign values to a variable in three general ways: through the assignment operator, by selecting a value into the variable, or by binding a variable. This section covers the first two methods. Variable binding is described in 2 Day + guides for Application Express, Java, .NET, and PHP.

Assigning Values with the Assignment Operator

You can assign values to a variable both in the declaration and the body of a subprogram.

The following code shows the standard declaration of variables and constants. In procedures and functions, the declaration block does not use the DECLARE keyword; instead, it follows the AS keyword of the subprogram definition.

Example 4-2 Assigning variable values in a declaration

In the emp_eval Body pane, modify function calculate_score by adding a new variable running_total. The value of running_total is also the new return value of the function. You will set the initial value of the return variable to 0. Note that running_total is declared as a general NUMBER because it will hold a product of two NUMBERs with different precision and scale. New code is bold font.

FUNCTION calculate_score(evaluation_id IN scores.evaluation_id%TYPE
                       , performance_id IN scores.performance_id%TYPE)
                         RETURN NUMBER AS
  n_score       scores.score%TYPE;               -- from SCORES
  n_weight      performance_parts.weight%TYPE;   -- from PERFORMANCE_PARTS
  running_total NUMBER               := 0;       -- used in calculations
  max_score     CONSTANT scores.score%TYPE := 9; -- a constant limit check
  max_weight    CONSTANT performance_parts.weight%TYPE:= 1;      
                                                 -- a constant limit check
BEGIN
  RETURN running_total;
END calculate_score;

Compile the emp_eval Body.

You can also assign values to variables within the body of a subprogram. You will edit the function calculate_score by using the running_total variable inside the body of the function to hold a value of an expression.

Example 4-3 Assigning variable values in the body of a function

In the emp_eval Body pane, modify function calculate_score by assigning to the running_total variable the value of an expression, as shown by the following code. New code is bold font.

FUNCTION calculate_score(evaluation_id IN scores.evaluation_id%TYPE
                       , performance_id IN scores.performance_id%TYPE)
                         RETURN NUMBER AS

  n_score       scores.score%TYPE;               -- from SCORES
  n_weight      performance_parts.weight%TYPE;   -- from PERFORMANCE_PARTS
  running_total NUMBER               :=0;        -- used in calculations
  max_score     CONSTANT scores.score%TYPE := 9; -- a constant limit check
  max_weight    CONSTANT performance_parts.weight%TYPE:= 1;       
                                                 -- a constant limit check
BEGIN
  running_total := max_score * max_weight;
  RETURN running_total;
END calculate_score;

Compile and save emp_eval Body.

See Also:

Assigning Values from the Database

The simplest possible assignment of a value is to use the assignment operator (:=) as you did for the variable running_total in "Assigning Values with the Assignment Operator".

However, the purpose of function calculate_score is to perform a calculation based on values stored in database tables. To use existing database values in a procedure, function, or package, you must assign these values to a variable by using a SELECT INTO statement. You can then use the variable in subsequent computations.

Example 4-4 Assigning to a variable a values from the database

In the emp_eval Body pane, modify function calculate_score by assigning the table values to the variables n_score and n_weight, and then assigning their product to the running_total variable, as shown by the following code. New code is bold font.

FUNCTION calculate_score(evaluation_id IN scores.evaluation_id%TYPE
                       , performance_id IN scores.performance_id%TYPE)
                         RETURN NUMBER AS

  n_score       scores.score%TYPE;               -- from SCORES
  n_weight      performance_parts.weight%TYPE;   -- from PERFORMANCE_PARTS
  running_total NUMBER               := 0;       -- used in calculations
  max_score     CONSTANT scores.score%TYPE := 9; -- a constant limit check
  max_weight    CONSTANT performance_parts.weight%TYPE:= 1;       
                                                 -- a constant limit check
BEGIN
  SELECT s.score INTO n_score FROM scores s 
    WHERE evaluation_id = s.evaluation_id 
      AND performance_id = s.performance_id;
  SELECT p.weight INTO n_weight FROM performance_parts p
    WHERE performance_id = p.performance_id;
    running_total := n_score * n_weight;
  RETURN running_total;
END calculate_score;

Compile and save emp_eval Body.

Similarly, add a new add_eval procedure for inserting new records into the evaluations table, based on the content of the corresponding row in the employees table. Note that add_eval is using the sequence evaluations_seq.

Example 4-5 Creating a new table row with values from another table

In the emp_eval Body pane, above the line END emp_eval, add procedure add_eval, which uses some columns from the employees table to insert rows into the evaluations table. Note also that you will create the local function add_eval in the body of the emp_eval package, but not declare it in the package specification. This means that add_eval may be invoked only within the emp_eval package, by another subprogram.

PROCEDURE add_eval(employee_id IN employees.employee_id%TYPE, today IN DATE) AS
  -- placeholders for variables
  job_id         employees.job_id%TYPE;
  manager_id     employees.manager_id%TYPE;
  department_id  employees.department_id%TYPE;
BEGIN

  -- extracting values from employees for later insertion into evaluations
  SELECT e.job_id INTO job_id FROM employees e 
    WHERE employee_id = e.employee_id;
  SELECT e.manager_id INTO manager_id FROM employees e 
    WHERE employee_id = e.employee_id;
  SELECT e.department_id INTO department_id FROM employees e 
    WHERE employee_id = e.employee_id;
 
  -- inserting a new row of values into evaluations table
  INSERT INTO evaluations VALUES (
    evaluations_seq.NEXTVAL,   -- evaluation_id
    employee_id,               -- employee_id
    today,                     -- evaluation_date
    job_id,                    -- job_id
    manager_id,                -- manager_id
    department_id,             -- department_id
    0);                        -- total_score
  
END add_eval;

Compile and save emp_eval Body.

See Also:

Controlling Program Flow

Control structures are the most powerful feature of the PL/SQL extension to SQL. They let you manipulate data and process it using conditional selection, iterative control, and sequential statements. Conditional selection is a situation where you may have different types of data values, and may need to perform different processing steps. Iterative control is a situation where you need to perform repetitive process steps on similar data. In general, all the lines of code in your programs run sequentially; sequential control means that you are choosing to execute an alternate labeled programming branch (GOTO statement).

Description of control_structures.gif follows
Description of the illustration control_structures.gif

This section will only cover conditional selection and iterative program flow structures, such as IF...THEN...ELSE, CASE, FOR...LOOP, WHILE...LOOP, and LOOP...EXIT WHEN.

See Also:

Using Conditional Selection Control

Conditional selection structures test an expression that evaluates to a BOOLEAN value TRUE or FALSE. Depending on the value, control structures execute the assigned sequence of statements. There are two general selection control mechanisms: IF...THEN...ELSE and its variations, and the CASE statement.

See Also:

Using IF...THEN...ELSE Selection Control

The IF...THEN...ELSE statement runs a sequence of statements conditionally. If the test condition evaluates to TRUE, the program runs statements in the THEN clause. If the condition evaluates to FALSE, the program runs the statements in the ELSE clause. You can also use this structure for testing multiple conditions if you include the ELSIF keyword. The general form of the IF...THEN...[ELSIF]...ELSE statement follows:

IF condition_1 THEN
  ...;
ELSIF condition_2 THEN  -- optional
  ...;
ELSE                     -- optional
  ...;
END IF;

For example, the sample company could have a rule that an employee evaluation should be done twice a year (December 31 and June 30) in the first ten years of employment, but only once a year (December 31) subsequently. You could implement this rule in an eval_frequency function that determines how many times in each year an evaluation should be performed by using the IF...THEN...ELSE clause on the value of the hire_date column.

The function eval_frequency uses the employees.hire_date value to determine if evaluations should be performed once each year (over 10 years employment) or twice each year.

Note also that you will create the function eval_frequency in the body of the emp_eval package, but not declare it in the package specification. This means that eval_frequency may be invoked only within the emp_eval package, by another subprogram.

Example 4-6 Using the IF... THEN...ELSE Selection Control

In the emp_eval Body pane, add eval_frequency function immediately before the END emp_eval; statement, as shown by the following code. The control structures are in bold font.

FUNCTION eval_frequency (employee_id IN employees.employee_id%TYPE) 
  RETURN PLS_INTEGER AS
  hire_date   employees.hire_date%TYPE;     -- start of employment
  today       employees.hire_date%TYPE;     -- today's date
  eval_freq   PLS_INTEGER;                  -- frequency of evaluations
BEGIN
  SELECT SYSDATE INTO today FROM DUAL;      -- set today's date
  SELECT e.hire_date INTO hire_date         -- determine when employee started
      FROM employees e
      WHERE employee_id = e.employee_id;

   IF((hire_date + (INTERVAL '120' MONTH)) < today) THEN
     eval_freq := 1;
   ELSE
     eval_freq := 2;
   END IF;

   RETURN eval_freq;
 END eval_frequency;

Compile and save emp_eval Body.

See Also:

Using CASE...WHEN Selection Control

The CASE...WHEN construct is a good alternative to nested IF...THEN statements if the variable that determines the course of action has several possible values. The CASE evaluates a condition, and performs a different action for each possible value. Whenever possible, use the CASE...WHEN statement instead of IF...THEN, both for readability and efficiency. The general form of the CASE...WHEN construct follows:

CASE condition
  WHEN value_1 THEN expression_1;
  WHEN value_2 THEN expression_2;
  ...
  ELSE expression_default;
END CASE;

Suppose that in the make_evaluation function from "Using IF...THEN...ELSE Selection Control", you wanted to notify the hr user if a long-time employee who holds one of a select positions should be considered for a salary raise. Depending on the value of employees.job_id, the program logic should notify the user of the suggested salary raise.

Note that you will use the DBMS_OUTPUT.PUT_LINE procedure, described in Oracle Database PL/SQL Packages and Types Reference.

Example 4-7 Using CASE...WHEN Conditional Control

In the emp_eval Body pane, edit eval_frequency function to add a job_id variable and a CASE statement that is based on the value of the job_id, as shown by the following code. New code is in bold font.

FUNCTION eval_frequency (employee_id IN employees.employee_id%TYPE) 
  RETURN PLS_INTEGER AS
  hire_date   employees.hire_date%TYPE;     -- start of employment
  today       employees.hire_date%TYPE;     -- today's date
  eval_freq   PLS_INTEGER;                  -- frequency of evaluations
  job_id      employees.job_id%TYPE;        -- category of the job
  
BEGIN
  SELECT SYSDATE INTO today FROM DUAL;    -- set today's date
  SELECT e.hire_date INTO hire_date          -- determine when employee started
      FROM employees e
      WHERE employee_id = e.employee_id;
 
  IF((hire_date + (INTERVAL '120' MONTH)) < today) THEN
     eval_freq := 1;
     
     /* Suggesting salary increase based on position */
     SELECT e.job_id INTO job_id FROM employees e
       WHERE employee_id = e.employee_id;  
      CASE job_id
        WHEN 'PU_CLERK' THEN DBMS_OUTPUT.PUT_LINE(
         'Consider 8% salary increase for employee number ' || employee_id);
        WHEN 'SH_CLERK' THEN DBMS_OUTPUT.PUT_LINE(
         'Consider 7% salary increase for employee number ' || employee_id);
        WHEN 'ST_CLERK' THEN DBMS_OUTPUT.PUT_LINE(
         'Consider 6% salary increase for employee number ' || employee_id);
        WHEN 'HR_REP' THEN DBMS_OUTPUT.PUT_LINE(
         'Consider 5% salary increase for employee number ' || employee_id);
        WHEN 'PR_REP' THEN DBMS_OUTPUT.PUT_LINE(
         'Consider 5% salary increase for employee number ' || employee_id);
        WHEN 'MK_REP' THEN DBMS_OUTPUT.PUT_LINE(
         'Consider 4% salary increase for employee number ' || employee_id);
        ELSE DBMS_OUTPUT.PUT_LINE( 
         'Nothing to do for employee #' || employee_id);
      END CASE;

   ELSE
     eval_freq := 2;
   END IF;
 
   RETURN eval_freq;
 END eval_frequency;

Compile and save emp_eval Body.

See Also:

Using Iterative Control

Iteration structures, or loops, execute a sequence of statements repeatedly. There are three basic types of loops, the FOR...LOOP, the WHILE...LOOP, and the LOOP...EXIT WHEN.

See Also:

Using the FOR...LOOP

The FOR...LOOP repeats a sequence of steps a defined number of times and uses a counter variable that must be in the defined range of integers to run the loop. The loop counter is implicitly declared in the FOR...LOOP statement, and implicitly incremented every time the loop runs. Note that the value of the loop counter can be used within the body of the loop, but it cannot be changed programmatically. The FOR...LOOP statement has the following form:

FOR counter IN integer_1..integer_2 LOOP
  ...
END LOOP;

Suppose that in addition to recommending that some employees receive a raise, as described in "Using CASE...WHEN Selection Control", function eval_frequency prints how the salary for the employee would change over a set number of years if this increase in salary continued.

Note that you will use the DBMS_OUTPUT.PUT procedure, described in Oracle Database PL/SQL Packages and Types Reference.

Example 4-8 Using FOR...LOOP iterative control

In the emp_eval Body pane, edit eval_frequency function so that it uses the proposed salary increase (sal_raise) that is assigned in the CASE block to print the proposed salary over a number of years, starting with the current salary, salary. The new code is in bold font.

FUNCTION eval_frequency (employee_id IN employees.employee_id%TYPE) 
  RETURN PLS_INTEGER AS
  hire_date   employees.hire_date%TYPE;     -- start of employment
  today       employees.hire_date%TYPE;     -- today's date
  eval_freq   PLS_INTEGER;                  -- frequency of evaluations
  job_id      employees.job_id%TYPE;        -- category of the job
  salary      employees.salary%TYPE;        -- current salary
  sal_raise   NUMBER(3,3) := 0;             -- proposed % salary increase
  
BEGIN
  SELECT SYSDATE INTO today FROM DUAL;    -- set today's date
  SELECT e.hire_date INTO hire_date          -- determine when employee started
      FROM employees e
      WHERE employee_id = e.employee_id;
 
  IF((hire_date + (INTERVAL '120' MONTH)) < today) THEN
     eval_freq := 1;
     
     /* Suggesting salary increase based on position */
     SELECT e.job_id INTO job_id FROM employees e
       WHERE employee_id = e.employee_id;  
     SELECT e.salary INTO salary FROM employees e
       WHERE employee_id = e.employee_id;
      CASE job_id
        WHEN 'PU_CLERK' THEN sal_raise := 0.08;
        WHEN 'SH_CLERK' THEN sal_raise := 0.07;
        WHEN 'ST_CLERK' THEN sal_raise := 0.06;
        WHEN 'HR_REP' THEN sal_raise := 0.05;
        WHEN 'PR_REP' THEN sal_raise := 0.05;
        WHEN 'MK_REP' THEN sal_raise := 0.04;
        ELSE NULL; -- job type does not match ones that should consider increases
      END CASE;
      
      /* If a salary raise is not zero, print the salary schedule */
      IF (sal_raise != 0) THEN -- start code for salary schedule printout 
        BEGIN
          DBMS_OUTPUT.PUT_LINE('If the salary ' || salary || ' increases by ' ||
            ROUND((sal_raise * 100),0) || 
           '% each year over 5 years, it would be ');
    
          FOR loop_c IN 1..5 LOOP
            salary := salary * (1 + sal_raise);
            DBMS_OUTPUT.PUT (ROUND(salary,2) ||', ');
          END LOOP;
   
        DBMS_OUTPUT.PUT_LINE('in successive years.');

      END;
    END IF;
  
   ELSE
     eval_freq := 2;
   END IF;
 
   RETURN eval_freq;
 END eval_frequency;

Compile the emp_eval Body.

See Also:

Using the WHILE...LOOP

The WHILE...LOOP repeats as long as a condition holds TRUE. The condition evaluates at the top of each loop and if TRUE, the statements in the body of the loop run. If the condition is FALSE or NULL, the control passes to the next statement after the loop. The general form of the WHILE...LOOP control structure follows.

WHILE condition LOOP
  ...
END LOOP;

Note that the WHILE...LOOP may run indefinatelly, so use it with care.

Suppose that the EVAL_FREQUENCY function in "Using the FOR...LOOP" uses the WHILE...LOOP instead of the FOR...LOOP, and terminates after the proposed salary reaches the upper salary limit for the job_id.

Example 4-9 Using WHILE...LOOP Iterative Control

In the emp_eval Body pane, edit eval_frequency function so that it uses the proposed salary increase (sal_raise) that is assigned in the CASE block to print the proposed salary over a number of years and stops when it reaches the maximum level possible for the job_id. The new code is in bold font.

FUNCTION eval_frequency (employee_id IN employees.employee_id%TYPE) 
  RETURN PLS_INTEGER AS
  hire_date   employees.hire_date%TYPE;     -- start of employment
  today       employees.hire_date%TYPE;     -- today's date
  eval_freq   PLS_INTEGER;                  -- frequency of evaluations
  job_id      employees.job_id%TYPE;        -- category of the job
  salary      employees.salary%TYPE;        -- current salary
  sal_raise   NUMBER(3,3) := 0;             -- proposed % salary increase
  sal_max     jobs.max_salary%TYPE;         -- maximum salary for a job
  
BEGIN
  SELECT SYSDATE INTO today FROM DUAL;    -- set today's date
  SELECT e.hire_date INTO hire_date          -- determine when employee started
      FROM employees e
      WHERE employee_id = e.employee_id;
 
  IF((hire_date + (INTERVAL '120' MONTH)) < today) THEN
     eval_freq := 1;
     
     /* Suggesting salary increase based on position */
     SELECT e.job_id INTO job_id FROM employees e
       WHERE employee_id = e.employee_id;  
     SELECT e.salary INTO salary FROM employees e
       WHERE employee_id = e.employee_id;
     SELECT j.max_salary INTO sal_max FROM jobs j
       WHERE job_id = j.job_id;
      CASE job_id
        WHEN 'PU_CLERK' THEN sal_raise := 0.08;
        WHEN 'SH_CLERK' THEN sal_raise := 0.07;
        WHEN 'ST_CLERK' THEN sal_raise := 0.06;
        WHEN 'HR_REP' THEN sal_raise := 0.05;
        WHEN 'PR_REP' THEN sal_raise := 0.05;
        WHEN 'MK_REP' THEN sal_raise := 0.04;
        ELSE NULL;
      END CASE;
      
        /* If a salary raise is not zero, print the salary schedule */
    IF (sal_raise != 0) THEN -- start code for salary schedule printout 
      BEGIN
        DBMS_OUTPUT.PUT_LINE('If the salary ' || salary || ' increases by ' ||
          ROUND((sal_raise * 100),0) || 
          '% each year, it would be ');
    
        WHILE salary <= sal_max LOOP
          salary := salary * (1 + sal_raise);
          DBMS_OUTPUT.PUT (ROUND(salary,2) ||', ');
        END LOOP;
   
        DBMS_OUTPUT.PUT_LINE('in successive years.');
      END;
    END IF;
  
   ELSE
     eval_freq := 2;
   END IF;
 
   RETURN eval_freq;
 END eval_frequency;

See Also:

Using the LOOP...EXIT WHEN

The LOOP...EXIT WHEN structure enables you to exit the loop if further processing is undesirable. If the EXIT WHEN condition evaluates to TRUE, the loop exits and control passes to the next statement.

The eval_frequency function in "Using the WHILE...LOOP" uses the WHILE...LOOP. Note that the last computed value may (and typically does) exceed the maximum possible value for a salary in the last iteration of the loop. If you use the LOOP_EXIT WHEN construct instead of the WHILE...LOOP, you can have finer control for terminating the loop.

Example 4-10 Using LOOP...EXIT WHEN Iterative Control

In the emp_eval Body pane, edit eval_frequency function so that it uses the proposed salary increase (sal_raise) that is assigned in the CASE block to print the proposed salary over a number of years and stops when it reaches the maximum level possible for the job_id. The new code is in bold font.

FUNCTION eval_frequency (employee_id IN employees.employee_id%TYPE) 
  RETURN PLS_INTEGER AS
  hire_date   employees.hire_date%TYPE;     -- start of employment
  today       employees.hire_date%TYPE;     -- today's date
  eval_freq   PLS_INTEGER;                  -- frequency of evaluations
  job_id      employees.job_id%TYPE;        -- category of the job
  salary      employees.salary%TYPE;        -- current salary
  sal_raise   NUMBER(3,3) := 0;             -- proposed % salary increase
  sal_max     jobs.max_salary%TYPE;         -- maximum salary for a job
  
BEGIN
  SELECT SYSDATE INTO today FROM DUAL;    -- set today's date
  SELECT e.hire_date INTO hire_date          -- determine when employee started
      FROM employees e
      WHERE employee_id = e.employee_id;
 
  IF((hire_date + (INTERVAL '120' MONTH)) < today) THEN
     eval_freq := 1;
     
     /* Suggesting salary increase based on position */
     SELECT e.job_id INTO job_id FROM employees e
       WHERE employee_id = e.employee_id;  
     SELECT e.salary INTO salary FROM employees e
       WHERE employee_id = e.employee_id;
     SELECT j.max_salary INTO sal_max FROM jobs j
       WHERE job_id = j.job_id;
      CASE job_id
        WHEN 'PU_CLERK' THEN sal_raise := 0.08;
        WHEN 'SH_CLERK' THEN sal_raise := 0.07;
        WHEN 'ST_CLERK' THEN sal_raise := 0.06;
        WHEN 'HR_REP' THEN sal_raise := 0.05;
        WHEN 'PR_REP' THEN sal_raise := 0.05;
        WHEN 'MK_REP' THEN sal_raise := 0.04;
        ELSE NULL;
      END CASE;
      
        /* If a salary raise is not zero, print the salary schedule */
    IF (sal_raise != 0) THEN -- start code for salary schedule printout 
      BEGIN
        DBMS_OUTPUT.PUT_LINE('If the salary ' || salary || ' increases by ' ||
          ROUND((sal_raise * 100),0) || 
          '% each year, it would be ');
    
        LOOP
          salary := salary * (1 + sal_raise);
          EXIT WHEN salary > sal_max;
          DBMS_OUTPUT.PUT (ROUND(salary,2) ||', ');
        END LOOP;
   
        DBMS_OUTPUT.PUT_LINE('in successive years.');
      END;
    END IF;
  
   ELSE
     eval_freq := 2;
   END IF;
 
   RETURN eval_freq;
 END eval_frequency;

See Also:

Using Composite Data Structures; Records

A composite data structure, or a record, is a group of related data items stored in fields, each with its own name and data type. You can think of a record as a variable that can hold a table row, or some columns from a table row. The fields correspond to table columns. The record structure is very efficient for passing related items to a subprogram as a single parameter, and for effectively using related fields from different tables during run time.

You must define a RECORD as a type, and access its fields through the point notation. The general form for defining and using a record follows:

TYPE record_name IS RECORD(                        -- define record type
 field_1 data_type,                                -- define fields in record
 ...
 field_n data_type);
...
variable_name record_name;                         -- define variable of new type
...
BEGIN
 ...
   ...variable_name.field1...;                     -- use fields of new variable
   ...variable_name.fieldn...;
 ...
END...;

In the eval_frequency function from "Using the LOOP...EXIT WHEN", you used several related parameters. You can use the RECORD construct to combine some of these items into a single parameter.

You will create a type that will contain the upper and lower limits for a job specification.

To create a RECORD type:

  1. In the Connections navigation hierarchy, click the plus sign (+) beside Packages to expand the group.

  2. Right-click EMP_EVAL.

  3. Select Edit.

    The emp_eval pane appears. It shows the specification of the emp_eval package.

  4. In the emp_eval package specification, immediately before the closing line of the package specification, END emp_eval, enter the definition of a record type sal_info, which contains the fields necessary for evaluating salary levels.

    TYPE sal_info IS RECORD  -- type for salary, limits, raises, and adjustments
      ( job_id jobs.job_id%type               
      , sal_min jobs.min_salary%type           
      , sal_max jobs.max_salary%type
      , salary employees.salary%type
      , sal_raise NUMBER(3,3) );
    
  5. Compile and save emp_eval.

    The following message appears in the Messages-Log pane:

    EMP_EVAL Compiled
    

Once you declare a new RECORD type in the package specification, you can use it inside the package body to declare variables of that type. You will create a new procedure, salary_schedule, and invoke it from the eval_frequency function using a variable of type sal_info.

Note that PL/SQL compilation is a single path process; if a subprogram is declared after its client subprogram, PL/SQL compiler throws an error. To work around this situation, you could declare all the subprograms that are not already declared in the package specification at the top of the package body. The definition of the subprogram can be anywhere within the package body. See step 2 in the following task on instructions for declaring function eval_frequency and procedures salary_schedule and add_eval.

To use a RECORD type:

  1. In the emp_eval Body pane, add the definition of the salary_schedule procedure immediately before the END emp_eval statement, as shown by the following code. Note that this code is similar to the content of the BEGIN...END block in eval_frequency that executes if the salary raise is nonzero.

    PROCEDURE salary_schedule(emp IN sal_info) AS
      accumulating_sal NUMBER;          -- accumulator 
      BEGIN
        DBMS_OUTPUT.PUT_LINE('If the salary of ' || emp.salary || 
             ' increases by ' || ROUND((emp.sal_raise * 100),0) || 
             '% each year, it would be ');
        accumulating_sal := emp.salary;  -- assign value of sal to accumulator
        WHILE  accumulating_sal <= emp.sal_max LOOP
           accumulating_sal :=  accumulating_sal * (1 + emp.sal_raise);
           DBMS_OUTPUT.PUT (ROUND( accumulating_sal,2) ||', ');
        END LOOP;
          DBMS_OUTPUT.PUT_LINE('in successive years.');   
      END salary_schedule; 
    
  2. In the emp_eval Body pane, near the top of the emp_eval body definition, enter declarations for eval_frequency and salary_schedule. New code is in bold font.

    create or replace
    PACKAGE BODY emp_eval AS
    
    /* local subprogram declarations */
    FUNCTION eval_frequency (employee_id employees.employee_id%TYPE) RETURN NUMBER;
    PROCEDURE salary_schedule(emp IN sal_info);
    PROCEDURE add_eval(employee_id IN NUMBER, today IN DATE);
     
    /* subprogram definition */ 
      PROCEDURE eval_department (dept_id IN NUMBER) AS
    ...
    
  3. In the emp_eval Body pane, edit eval_frequency function so that it uses the new sal_info type as variable emp_sal, populates its fields, and invokes salary_schedule. Note that the code that was previously executed if the salary raise was nonzero is no longer part of this function; it has been incorporated into the salary_schedule procedure. Note also that the declarations at the top of the functions changed. New code is in bold font.

    FUNCTION eval_frequency (employee_id employees.employee_id%TYPE) 
      RETURN PLS_INTEGER AS
      hire_date   employees.hire_date%TYPE;     -- start of employment
      today       employees.hire_date%TYPE;     -- today's date
      eval_freq   PLS_INTEGER;                  -- frequency of evaluations
      emp_sal     SAL_INFO;                     -- record for fields associated
                                                -- with salary review
    BEGIN
      SELECT SYSDATE INTO today FROM DUAL;    -- set today's date
      SELECT e.hire_date INTO hire_date          -- determine when employee started
          FROM employees e
          WHERE employee_id = e.employee_id;
     
      IF((hire_date + (INTERVAL '120' MONTH)) < today) THEN
         eval_freq := 1;
         
         /* populate emp_sal */
         SELECT e.job_id INTO emp_sal.job_id FROM employees e
           WHERE employee_id = e.employee_id;  
         SELECT j.min_salary INTO emp_sal.sal_min FROM jobs j
           WHERE emp_sal.job_id = j.job_id;
         SELECT j.max_salary INTO emp_sal.sal_max FROM jobs j
           WHERE emp_sal.job_id = j.job_id;
         SELECT e.salary INTO emp_sal.salary FROM employees e
           WHERE employee_id = e.employee_id;
         emp_sal.sal_raise := 0;  -- default 
         
          CASE emp_sal.job_id
            WHEN 'PU_CLERK' THEN emp_sal.sal_raise := 0.08;
            WHEN 'SH_CLERK' THEN emp_sal.sal_raise := 0.07;
            WHEN 'ST_CLERK' THEN emp_sal.sal_raise := 0.06;
            WHEN 'HR_REP' THEN emp_sal.sal_raise := 0.05;
            WHEN 'PR_REP' THEN emp_sal.sal_raise := 0.05;
            WHEN 'MK_REP' THEN emp_sal.sal_raise := 0.04;
            ELSE NULL;
          END CASE;
          
            /* If a salary raise is not zero, print the salary schedule */
        IF (emp_sal.sal_raise != 0) THEN salary_schedule(emp_sal);
        END IF;
      
       ELSE
         eval_freq := 2;
       END IF;
     
       RETURN eval_freq;
     END eval_frequency;
    
  4. Compile and save emp_eval Body.

    The following message appears in the Messages - Log pane:

    EMP_EVAL Body Compiled
    

See Also:

Retrieving Data from a Set Using Cursors and Cursor Variables

A cursor is a type of pointer that is built into PL/SQL for querying the database, retrieving a set of records (a result set), and enabling the developer to access these records one row at a time. A cursor is a handle or a name for a private in-memory SQL area that holds a parsed statement and related information. Oracle Database implicitly manages cursors. However, there are a few interfaces that enable you to use cursors explicitly, as a named resource within a program to more effectively parse embedded SQL statements. The two main types of cursors are therefore defined as:

Each user session may have many open cursors, up to the limit set by the initialization parameter OPEN_CURSORS, which is 50 by default. You should ensure that your applications close cursors to conserve system memory. If a cursor cannot be opened because the OPEN_CURSORS limit is reached, contact the database administrator to alter the OPEN_CURSORS initialization parameter.

See Also:

Using Explicit Cursors

The implicit cursor, such as in a FOR...LOOP, are generally more efficient than an explicit cursor. However, explicit cursors may be more appropriate for your program, and they also allow you to manage specific in-memory areas as a named resource.

An explicit cursor has the attributes described in the following table:

Cursor Attribute Description
%NOTFOUND
Returns TRUE or FALSE, based on the results of the last fetch.
%FOUND
Returns TRUE or FALSE, based on the results of the last fetch; negation of the %NOTFOUND results.
%ROWCOUNT
Returns the number of rows fetched. Can be called at any time after the first fetch. Also returns the number of rows affected from UPDATE and DELETE statements.
%ISOPEN
Returns TRUE if a cursor is still open.

An explicit cursor must be defined as a variable of the same type as the columns it fetches; the data type of the record is derived from the cursor definition. Explicit cursors must be opened and may then retrieve rows within a LOOP...EXIT WHEN structure and then closed. The general form for using cursors follows:

DECLARE
  CURSOR cursor_name type IS query_definition;
OPEN cursor_name
  LOOP
    FETCH record;
    EXIT WHEN cursor_name%NOTFOUND;
    ...;             -- process fetched row
  END LOOP;
CLOSE cursor_name;

This is what happens during the life time of a cursor:

  • The OPEN statement parses the query identified by the cursor, binds the inputs, and ensures that you can successfully fetch records from the result set.

  • The FETCH statement runs the query, and then finds and retrieves the matching rows. You will need to define and use local variables as buffers for the data returned by the cursor, and then process the specific record.

  • The CLOSE statement completes cursor processing and closes the cursor. Note that once a cursor is closed you cannot retrieve additional records from the result set.

You can implement procedure eval_department, which you declared in "Creating a Package", using a cursor for each employee record that matches the query.

Example 4-11 Using a cursor to retrieve rows form a result set

The cursor emp_cursor fetches individual rows from the result set. Depending on the value of the eval_frequency function for each row and the time of the year that the eval_department procedure runs, a new evaluation record is created for the employee by invoking the add_eval procedure. Note that the buffer variable, emp_record, is defined as a %ROWTYPE.

In the emp_eval package specification, edit the declaration of procedure eval_department:

PROCEDURE eval_department(department_id IN employees.department_id%TYPE);

In the emp_eval Body pane, edit eval_department procedure.

  PROCEDURE eval_department(department_id IN employees.department_id%TYPE) AS
   -- declaring buffer variables for cursor data
   emp_record      employees%ROWTYPE;
   -- declaring variable to monitor if all employees need evaluations
   all_evals       BOOLEAN;
   -- today's date
   today           DATE;
   -- declaring the cursor
   CURSOR emp_cursor IS SELECT * FROM employees e
           WHERE department_id = e.department_id;
BEGIN
-- determine if all evaluations must be done or just for newer employees;
-- this depends on time of the year
  today := SYSDATE;
  IF (EXTRACT(MONTH FROM today) < 6) THEN all_evals := FALSE;
  ELSE  all_evals := TRUE;
  END IF;
 
  OPEN emp_cursor;
 
  -- start creating employee evaluations in a specific department
  DBMS_OUTPUT.PUT_LINE('Determining evaluations necessary in department # ' ||
                        department_id);
  LOOP
    FETCH emp_cursor INTO emp_record;   -- getting specific record
    EXIT WHEN emp_cursor%NOTFOUND;      -- all records are been processed
    IF all_evals THEN                   
      add_eval(emp_record.employee_id, today); -- create evals for all
    ELSIF (eval_frequency(emp_record.employee_id) = 2) THEN
      add_eval(emp_record.employee_id, today); -- create evals; newer employees
    END IF;
  END LOOP;
 
  DBMS_OUTPUT.PUT_LINE('Processed ' || emp_cursor%ROWCOUNT || ' records.');
 
  CLOSE emp_cursor;  
 
END eval_department;

Compile the emp_eval package specification, and then the emp_eval Body.

The following message appears in the Messages-Log panes:

EMP_EVAL Body Compiled

See Also:

Using Cursor Variables: REF Cursors

Cursors are static, as they are defined by the queries that create them. In some cases, the queries themselves are created at runtime. A cursor variable, known as a REF CURSOR, is more flexible than a cursor because it is independent of a specific query. It can be opened for a query, can process the result set, and can be re-used for a query that returns the same set of columns. This also makes REF CURSORs ideal for passing results of a query between subprograms.

REF CURSORS can be declared with a return type that specifies the form of the result set (strongly typed), or without a return type to retrieve any result set (weakly-typed). Oracle recommends that you declare a REF CURSOR with a return type as it is less prone to error because of its strong association with correctly formulated queries. If you need a more flexible cursor that may be associated with several interchangeable types, use the predefined type SYS_REFCURSOR.

The general form for using a REF CURSORs follows.

DECLARE
  TYPE cursor_type IS REF CURSOR RETURN return_type;
  cursor_variable cursor_type;
  single_record return_type;
OPEN cursor_variable FOR query_definition;
  LOOP
    FETCH record;
    EXIT WHEN cursor_name%NOTFOUND;
    ...;             -- process fetched row
  END LOOP;
CLOSE cursor_name;

This is what happens during the life time of a REF CURSOR and a cursor variable:

  • The REF CURSOR type [with a return type] is declared.

  • The cursor variable that matches the cursor type is declared.

  • The variable for processing individual rows of the result set is declared; its type must be the same as the return type of the REF CURSOR type definition.

  • The OPEN statement parses the query to the cursor variable.

  • The FETCH statement inside the loop runs the query, and retrieves the matching rows into the local variable of the same type as the return type of the REF CURSOR for further processing.

  • The CLOSE statement completes cursor processing and closes the REF CURSOR.

In "Using Explicit Cursors", the procedure eval_department retrieves a result set, processes it using a cursor, closes the cursor, and ends. If you declare the cursor as a REF CURSOR type, you could modify it to process more departments (for example, three consecutive departments) by re-using the cursor.

Note that the fetching loop is part of the new eval_fetch_control procedure, that uses the cursor variable as input. This has an additional benefit of separating the processing of the result set from the definition of the query. You could write a procedure (eval_everyone) that initiates evaluations for all employees in the company, not just on a department basis.

Note also that eval_department uses a single field of a record to call procedure add_eval, which runs three separate queries on the same record. This is very inefficient; you will re-write the add_eval to use the entire record buffer of the REF CURSOR.

To use a REF CURSOR:

  1. In the emp_eval specification, add the REF CURSOR type definition, emp_refcursor_type. The type is defined at package level for visibility for all subprograms. Also add a declaration for procedure eval_everyone. The new code is in bold font.

    create or replace
    PACKAGE emp_eval AS
      PROCEDURE eval_department (department_id IN employees.department_id%TYPE);
      PROCEDURE eval_everyone;
      FUNCTION calculate_score(eval_id IN scores.evaluation_id%TYPE 
                             , perf_id IN scores.performance_id%TYPE) 
                               RETURN NUMBER;
      TYPE SAL_INFO IS RECORD  -- type for salary, limits, raises, and adjustments
          ( job_id jobs.job_id%type               
          , sal_min jobs.min_salary%type           
          , sal_max jobs.max_salary%type
          , salary employees.salary%type
          , sal_raise NUMBER(3,3));
          
      TYPE emp_refcursor_type IS REF CURSOR RETURN employees%ROWTYPE;
                               -- the REF CURSOR type for result set fetches
    END emp_eval;
    
  2. In the emp_eval Body pane, add a forward declaration for procedure eval_loop_control and edit the declaration of procedure add_eval. New code is in bold font.

    CREATE OR REPLACE PACKAGE BODY emp_eval AS
      /* local subprogram declarations */
      FUNCTION eval_frequency (employee_id IN employees.employee_id%TYPE) 
        RETURN NUMBER;
      PROCEDURE salary_schedule(emp IN sal_info);
      PROCEDURE add_eval(emp_record IN employees%ROWTYPE, today IN DATE);
      PROCEDURE eval_loop_control(emp_cursor IN emp_refcursor_type);
    ...
    
  3. In the emp_eval Body pane, edit eval_department procedure to retrieve three separate result sets based on the department, and to call the eval_loop_control procedure.

    PROCEDURE eval_department(department_id IN employees.department_id%TYPE) AS
      -- declaring the REF CURSOR
      emp_cursor      emp_refcursor_type;
     department_curr departments.department_id%TYPE;
       
    BEGIN
      department_curr := department_id;   -- starting with the first department
      FOR loop_c IN 1..3 LOOP
        OPEN emp_cursor FOR
          SELECT * 
          FROM employees e
          WHERE department_curr = e.department_id;
        -- create employee evaluations is specific departments
        DBMS_OUTPUT.PUT_LINE('Determining necessary evaluations in department #' ||
              department_curr);
        eval_loop_control(emp_cursor);  -- call to process the result set
        DBMS_OUTPUT.PUT_LINE('Processed ' || emp_cursor%ROWCOUNT || ' records.');
        CLOSE emp_cursor;
        department_curr := department_curr + 10; 
      END LOOP;
    END eval_department;
    
  4. In the emp_eval Body pane, edit add_eval procedure to use the entire retrieved record of employee%ROWTYPE, instead of an employee_id. Note that you no longer need any declarations at the beginning of the procedure.

    PROCEDURE add_eval(emp_record IN employees%ROWTYPE, today IN DATE) AS
    BEGIN
    -- inserting a new row of values into evaluations table
      INSERT INTO evaluations VALUES (
        evaluations_seq.NEXTVAL,   -- evaluation_id
        emp_record.employee_id,    -- employee_id
        today,                     -- evaluation_date
        emp_record.job_id,         -- job_id
        emp_record.manager_id,     -- manager_id
        emp_record.department_id,  -- department_id
        0);                        -- total_score
        
    END add_eval;
    
  5. Towards the end of code in the emp_eval Body pane, add eval_loop_control procedure to fetch the individual records from the result set and to process them. Note that much of this code is from an earlier definition of the eval_department procedure in "Using Explicit Cursors". New structures are in bold font.

    PROCEDURE eval_loop_control(emp_cursor IN emp_refcursor_type) AS
        -- declaring buffer variable for cursor data
       emp_record      employees%ROWTYPE;
       -- declaring variable to monitor if all employees need evaluations
       all_evals       BOOLEAN;
       -- today's date
       today           DATE;
    BEGIN
      -- determine if all evaluations must be done or just for newer employees;
     -- this depends on time of the year
      today := SYSDATE;
      
      IF (EXTRACT(MONTH FROM today) < 6) THEN 
       all_evals := FALSE;
      ELSE  all_evals := TRUE;
      END IF;
      LOOP
        FETCH emp_cursor INTO emp_record;   -- getting specific record
        EXIT WHEN emp_cursor%NOTFOUND;      -- all records are been processed
        IF all_evals THEN                   
          add_eval(emp_record, today); -- create evaluations for all
        ELSIF (eval_frequency(emp_record.employee_id) = 2) THEN
          add_eval(emp_record, today); -- create evaluations for newer employees
        END IF;
      END LOOP;
     END eval_loop_control;
    
  6. In the emp_eval Body pane, add eval_everyone procedure, which retrieves a result set that contains all employees in the company. Note that its code is similar to that of procedure eval_department in Step 3.

    PROCEDURE eval_everyone AS
       -- declaring the REF CURSOR type
      emp_cursor emp_refcursor_type;
       BEGIN
        OPEN emp_cursor FOR SELECT * FROM employees;
        -- start creating employee evaluations in a specific department
        DBMS_OUTPUT.PUT_LINE('Determining the number of necessary evaluations');
        eval_loop_control(emp_cursor); -- call to process the result set
      DBMS_OUTPUT.PUT_LINE('Processed ' || emp_cursor%ROWCOUNT || ' records.');
      CLOSE emp_cursor;  
     END eval_everyone;
    
  7. In the emp_eval pane, compile and save emp_eval specification.

    The following message appears in the Messages-Log pane:

    EMP_EVAL Compiled
    
  8. In the emp_eval body pane, compile and save emp_eval body.

    The following message appears in the Messages-Log pane:

    EMP_EVAL Body Compiled
    

See Also:

Using Collections; Index-By Tables

Another group of user-defined datatypes available in PL/SQL is a collection, which is Oracle's version of one-dimensional arrays. A collection is a data structure that can hold a number of rows of data in a single variable. In contrast to a record, which holds only one row of data of different types, the data in a collection must be of the same type. In other programming languages, the types of structures represented by collections are called arrays.

Collections are used to maintain lists of information and can significantly improve your application's performance because they allow direct access to their elements. There are three types of collection structures: index-by tables, nested tables, and variable arrays.

In this discussion, we will limit ourselves to index-by tables.

Index-by tables are also known as associative arrays, or sets of key-value pairs where each key is unique and is used to locate a corresponding value in the array. This key, or index, can be either an integer or a string.

Associative arrays represent data sets of arbitrary size that allow access to individual elements without knowledge of its relative position within the array, and without having to loop through all array elements.

For simple temporary storage of lookup data, associative arrays allow you to store data in memory, without using the disk space and network operations required for SQL tables. Because associative arrays are intended for temporary rather than persistent data storage, you cannot use them with SQL statements such as INSERT and SELECT INTO. You can, however, make them persistent for the life of a database session by declaring the type in a package and assigning the values in a package body.

Assigning a value using a key for the first time adds that key to the associative array. Subsequent assignments using the same key update the same entry. It is important to choose a key that is unique, such as a primary key of a database table, a result of a good numeric hash function, or a concatenation of strings that forms a unique string value.

Before declaring an index-by table, you must define its type. In the rest of this section, we will show you how to use an index-by table as part of our application.

We will show an efficient implementation of two types of associative arrays (indexed by PLS_INTEGER and VARCHAR2) using the following steps:

Creating Cursors for Index-by Tables

It is very convenient to define a cursor that would fetch the data into the index-by table, and then use its element type to create the index-by table. Example 4-12 shows how to create two cursors, employees_jobs_cursor for fetching data from the hr.employees table, and jobs_cursor for fetching data from the hr.jobs table. Notice that we are not using an ORDER BY clause for the second cursor.

Example 4-12 Declaring cursors for index-by tables

CURSOR employees_jobs_cursor IS
  SELECT e.first_name, e.last_name, e.job_id
  FROM hr.employees e
  ORDER BY e.job_id, e.last_name, e.first_name;

CURSOR jobs_cursor IS
  SELECT j.job_id, j.job_title
  FROM hr.jobs j;

Defining Index-by Tables

Now that you have declared your cursors, you can use the %ROWTYPE attribute to create the index-by PLS_INTEGER tables employees_jobs and jobs, as shown in Example 4-13:

Example 4-13 Creating index-by PLS_INTEGER tables based on the cursor structure

TYPE employees_jobs_type IS TABLE OF employees_jobs_cursor%ROWTYPE
  INDEX BY PLS_INTEGER;
employees_jobs employees_jobs_type;

TYPE jobs_type IS TABLE OF jobs_cursor%ROWTYPE
  INDEX BY PLS_INTEGER;
jobs jobs_type;

To create a table that is indexed by a VARCHAR2, such as the job_titles index-by table of job_id, use the definition of these types from the original table, hr.jobs, as shown in Example 4-14:

Example 4-14 Creating index-by VARCHAR2 tables

TYPE job_titles_type IS TABLE OF hr.jobs.job_title%TYPE
  INDEX BY hr.jobs.job_id%TYPE;
job_titles job_titles_type;

Populating Index-by PLS_INTEGER Tables; BULK COLLECT

If your work requires referencing a large quantity of data as local PL/SQL variables, the BULK COLLECT clause is much more efficient than looping through a result set one row at a time. When you query only some columns, you can store all the results for each column in a separate collection variable. When you query all the columns of a table, you can store the entire result set in a collection of records.

With the index-by PLS_INTEGER employees_jobs and jobs tables, you can now open the cursor and use BULK COLLECT to retrieve data, as shown in Example 4-15:

Example 4-15 Populating index-by PLS_INTEGER tables through BULK COLLECT

OPEN employees_jobs_cursor;
FETCH employees_jobs_cursor BULK COLLECT INTO employees_jobs;
CLOSE employees_jobs_cursor;

OPEN jobs_cursor;
FETCH jobs_cursor BULK COLLECT INTO jobs;
CLOSE jobs_cursor;

Populating Index-by VARCHAR2 Tables

Once the jobs table contains data, use the FOR ... LOOP, as shown in Example 4-16, to build the index-by VARCHAR2 table, job_titles:

Example 4-16 Populating index-by VARCHAR2 tables

FOR i IN 1..jobs.COUNT() LOOP
  job_titles(jobs(i).job_id) := jobs(i).job_title;
END LOOP;

Iterating Through an Index-by Table

The structure employees_jobs is a dense index-by table, because it is indexed by a PLS_INTEGER. You can iterate through it simply by placing your operations within a FOR ... LOOP that counts from 1 through the COUNT() value of the table, as demonstrated in Example 4-17. Note that the line in bold represents a direct look-up of a value in the job_titles table.

Example 4-17 Iterating through an index-by PLS_INTEGER table

FOR i IN 1..employees_jobs.count() LOOP
  DBMS_OUTPUT.PUT_LINE(
    RPAD(employees_jobs(i).employee_id, 10)||
    RPAD(employees_jobs(i).first_name, 15)||
    RPAD(employees_jobs(i).last_name,  15)||
    job_titles(employees(i).job_id));
  END LOOP;

The structure job_titles is a sparse index-by table, indexed by a VARCHAR2. As Example 4-18 demonstrates, you can iterate through it within a WHILE ... END LOOP using a pre-defined counter that is equal to the first key value, and the NEXT() value of the table. You will notice that the elements are naturally sorted in lexical order of the index.

Example 4-18 Iterating through an index-by VARCHAR2 table

DECLARE i hr.jobs.job_id%TYPE := job_titles.FIRST();
WHILE i IS NOT NULL LOOP
  DBMS_OUTPUT.PUT_LINE(
    RPAD(job_titles(i).job_id, 10)||
    job_titles(i).job_title);
  i := job_titles.NEXT(i);
END LOOP;

Handling Errors and Exceptions

Error conditions, known as exceptions, are easy to detect and process within your PL/SQL code. When an error occurs, it raises an exception by stopping normal processing and transferring control to exception-handling code. This code is located at the end of the PL/SQL block. In PL/SQL, the checks and calls to error routines are performed automatically, with each exception having its own exception handler.

Predefined exceptions are raised automatically for certain common error conditions that involve variables or database operations. You can also declare custom exceptions for conditions that are errors with respect to your program, or as wrappers to existing Oracle messages.

See Also:

Existing PL/SQL and SQL Exceptions

Oracle Database will automatically raise an exception if a PL/SQL program violates a known database rule, such as the predefined exception NO_DATA_FOUND if a SELECT INTO statement returns no rows. The following table shows some of the common exceptions.

Exception Description
ACCESS_INTO_NULL A program attempts to assign values to the attributes of an uninitialized object
CASE_NOT_FOUND None of the choices in the WHEN clauses of a CASE statement is selected, and there is no ELSE clause.
COLLECTION_IS_NULL A program attempts to apply collection methods other than EXISTS to an uninitialized nested table or varray, or the program attempts to assign values to the elements of an uninitialized nested table or varray.
CURSOR_ALREADY_OPEN A program attempts to open a cursor that is already open. A cursor must be closed before it can be reopened. A cursor FOR loop automatically opens the cursor to which it refers, so your program cannot open that cursor inside the loop.
DUP_VAL_ON_INDEX A program attempts to store duplicate values in a column that is constrained by a unique index.
INVALID_CURSOR A program attempts a cursor operation that is not allowed, such as closing an unopened cursor.
INVALID_NUMBER In a SQL statement, the conversion of a character string into a number fails because the string does not represent a valid number. (In procedural statements, VALUE_ERROR is raised.) This exception is also raised when the LIMIT clause expression in a bulk FETCH statement does not evaluate to a positive number.
LOGIN_DENIED A program attempts to logon to Oracle database with a user name or password that is not valid.
NO_DATA_FOUND A SELECT INTO statement returns no rows, or your program references a deleted element in a nested table or an uninitialized element in an index-by table.

Because this exception is used internally by some SQL functions to signal completion, do not rely on this exception being propagated if you raise it within a function that is called as part of a query.

NOT_LOGGED_ON A program issues a database call without being connected to Oracle database.
ROWTYPE_MISMATCH The host cursor variable and PL/SQL cursor variable involved in an assignment have incompatible return types. When an open host cursor variable is passed to a stored subprogram, the return types of the actual and formal parameters must be compatible.
SUBSCRIPT_BEYOND_COUNT A program references a nested table or varray element using an index number larger than the number of elements in the collection.
SUBSCRIPT_OUTSIDE_LIMIT A program references a nested table or varray element using an index number (-1 for example) that is outside the legal range.
TOO_MANY_ROWS A SELECT INTO statement returns more than one row.
VALUE_ERROR An arithmetic, conversion, truncation, or size-constraint error occurs. For example, when your program selects a column value into a character variable, if the value is longer than the declared length of the variable, PL/SQL cancels the assignment and raises VALUE_ERROR. In procedural statements, VALUE_ERROR is raised if the conversion of a character string into a number fails. (In SQL statements, INVALID_NUMBER is raised.)
ZERO_DIVIDE A program attempts to divide a number by zero.

Example 4-19 Handling exceptions

In the emp_eval Body pane, edit eval_department procedure to handle cases where the query does not return a result set. New code is in bold font.

  PROCEDURE eval_department(department_id IN employees.department_id%TYPE) AS
   -- declaring the REF CURSOR
   emp_cursor      emp_refcursor_type;
   department_curr departments.department_id%TYPE;
   
  BEGIN
    department_curr := department_id;   -- starting with the first department
    FOR loop_c IN 1..3 LOOP
      OPEN emp_cursor FOR
        SELECT * 
          FROM employees e
          WHERE department_curr = e.department_id;
     -- create employee evaluations is specific departments
        DBMS_OUTPUT.PUT_LINE('Determining necessary evaluations in department #' ||
          department_curr);
        eval_loop_control(emp_cursor);  -- call to process the result set
        DBMS_OUTPUT.PUT_LINE('Processed ' || emp_cursor%ROWCOUNT || ' records.');
      CLOSE emp_cursor;
      department_curr := department_curr + 10;
    END LOOP;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      DBMS_OUTPUT.PUT_LINE ('The query did not return a result set');
END eval_department;

Compile and save emp_eval Body.

Custom Exceptions

A package may contain custom exceptions for handling errors. Exceptions are declared in the program, in any declarative region, depending on how it is used: a subprogram, a package body, or a package specification.

An exception declaration has the following form:

exception_name EXCEPTION;

To raise custom exceptions programmatically, based on incorrect values, you need to use the following form:

IF condition THEN
  RAISE exception_name;

To trap unexpected Oracle errors, you must include the exception handling instructions in your code, typically as the last block within the body of your subprogram or package. You should name the specific exceptions you are handling (both standard and custom), and use the OTHERS handler to trap unexpected errors. An exception body has the following form:

EXCEPTION
  WHEN exception_name_1 THEN
    ...;
    DBMS_OUTPUT.PUT_LINE(message_1);
  ...
  WHEN OTHERS THEN
    ...
    DBMS_OUTPUT.PUT_LINE(message_others);

Alternatively, you may design your program to continue running after an exception is raised. You must then enclose the code that may generate an exception in a BEGIN...END block with its own exception handler. For example, code that traps the exception within a loop structure can handle the exception for an element that raises an error, and then continue with the next iteration of the loop.

In the following task, you will redesign the function calculate_score to declare, raise and trap two possible exceptions, weight_wrong and score_wrong.

Example 4-20 Handling custom exceptions

In the emp_eval Body pane, edit calculate_score function. New code is in bold font.

FUNCTION calculate_score(evaluation_id IN scores.evaluation_id%TYPE
                       , performance_id IN scores.performance_id%TYPE)
                         RETURN NUMBER AS
    weight_wrong  EXCEPTION;
    score_wrong   EXCEPTION;                       
    n_score       scores.score%TYPE;               -- from SCORES
    n_weight      performance_parts.weight%TYPE;   -- from PERFORMANCE_PARTS
    running_total NUMBER               := 0;       -- used in calculations
    max_score     CONSTANT scores.score%TYPE := 9; -- a constant limit check
    max_weight    CONSTANT performance_parts.weight%TYPE:= 1;
                                                   -- a constant limit check
  BEGIN
    SELECT s.score INTO n_score FROM scores s
      WHERE evaluation_id = s.evaluation_id 
      AND performance_id = s.performance_id;
    SELECT p.weight INTO n_weight FROM performance_parts p
      WHERE performance_id = p.performance_id;
    BEGIN                             -- check that weight is valid
      IF n_weight > max_weight OR n_weight < 0 THEN
        RAISE weight_wrong;
      END IF;
    END;
    BEGIN                              -- check that score is valid
      IF n_score > max_score OR n_score < 0 THEN
        RAISE score_wrong;
      END IF;
    END;
    -- calculate the score
    running_total := n_score * n_weight;
    RETURN running_total;
    EXCEPTION
      WHEN weight_wrong THEN
        DBMS_OUTPUT.PUT_LINE(
          'The weight of a score must be between 0 and ' || max_weight);
        RETURN -1;
      WHEN score_wrong THEN
        DBMS_OUTPUT.PUT_LINE(
          'The score must be between 0 and ' || max_score);
        RETURN -1;
    END calculate_score;

Compile and save emp_eval Body

See Also: