7 Developing Stored Subprograms and Packages

About Subprogram Structure

A subprogram follows PL/SQL block structure; that is, it has:

• Declarative part (optional)

  The declarative part contains declarations of types, constants, variables, exceptions, explicit cursors, and nested subprograms. These items are local to the subprogram and cease to exist when the subprogram completes execution.

• Executable part (required)

  The executable part contains statements that assign values, control execution, and manipulate data.

• Exception-handling part (optional)

  The exception-handling part contains code that handles exceptions (run-time errors).

Comments can appear anywhere in PL/SQL code. The PL/SQL compiler ignores them. Adding comments to your program promotes readability and aids understanding. A single-line comment starts with a double hyphen (--) and extends to the end of the line. A multiline comment starts with a slash and asterisk (/*) and ends with an asterisk and a slash (*/).

The structure of a procedure is:

  PROCEDURE name [ ( parameter_list ) ]
  { IS | AS }
    [ declarative_part ]
  BEGIN  -- executable part begins
    statement; [ statement; ]...
  [ EXCEPTION -- executable part ends, exception-handling part begins]
    exception_handler; [ exception_handler; ]... ]
  END; /* exception-handling part ends if it exists;
          otherwise, executable part ends */

The structure of a function is like that of a procedure, except that it includes a RETURN clause and at least one RETURN statement (and some optional clauses that are beyond the scope of this document):

  FUNCTION name [ ( parameter_list ) ] RETURN data_type [ clauses ]
  { IS | AS }
    [ declarative_part ]
  BEGIN  -- executable part begins
    -- at least one statement must be a RETURN statement
    statement; [ statement; ]...
  [ EXCEPTION -- executable part ends, exception-handling part begins]
    exception_handler; [ exception_handler; ]... ]
  END; /* exception-handling part ends if it exists;
          otherwise, executable part ends */

The code that begins with PROCEDURE or FUNCTION and ends before IS or AS is the subprogram signature. The declarative, executable, and exception-handling parts comprise the subprogram body. The syntax of exception-handler is in "About Exceptions and Exception Handlers".

Tutorial: Creating a Standalone Stored Procedure

To create a standalone stored procedure, use either the SQL Developer tool Create PL/SQL Procedure or the DDL statement CREATE PROCEDURE.

This tutorial shows how to use the Create PL/SQL Procedure tool to create a standalone stored procedure named ADD_EVALUATION that adds a row to the EVALUATIONS table (created in "Creating Tables with the CREATE TABLE Statement").

To create a standalone stored procedure using Create PL/SQL Procedure tool:

1. On the Connections tab, expand hr_conn.

   Under the hr_conn icon, a list of schema object types appears.

2. Right-click Procedures.

   A list of choices appears.

3. Click New Procedure.

   The Create PL/SQL Procedure window opens.

4. For Schema, accept the default value, HR.

5. For Name, change PROCEDURE1 to ADD_EVALUATION.

6. Click the icon Add Column.

   A row appears under the column headings. Its fields have these default values: Name, param1; Type, VARCHAR2; Mode, IN; Default Value, empty.

7. For Name, change param1 to evaluation_id.

8. For Type, select NUMBER from the drop-down list.

9. For Mode, accept the default value, IN.

10. Leave Default Value empty.

11. Add a second parameter by repeating steps 6 through 10 with the Name employee_id and the Type NUMBER.

12. Add a third parameter by repeating steps 6 through 10 with the Name evaluation_date and the Type DATE.

13. Add a fourth parameter by repeating steps 6 through 10 with the Name job_id and the Type VARCHAR2.

14. Add a fifth parameter by repeating steps 6 through 10 with the Name manager_id and the Type NUMBER.

15. Add a sixth parameter by repeating steps 6 through 10 with the Name department_id and the Type NUMBER.

16. Add a seventh parameter by repeating steps 6 through 10 with the Name total_score and the Type NUMBER.

17. Click OK.

    The ADD_EVALUATION pane opens, showing the CREATE PROCEDURE statement that created the procedure:

    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
    , total_score IN NUMBER
    ) AS
    BEGIN
      NULL;
    END ADD_EVALUATION;
    

    Because the only statement in the execution part of the procedure is NULL, the procedure does nothing.

18. Replace NULL with this statement:

    INSERT INTO EVALUATIONS (
       evaluation_id,
       employee_id,
       evaluation_date,
       job_id,
       manager_id,
       department_id,
       total_score 
    )
    VALUES (
      ADD_EVALUATION.evaluation_id,
      ADD_EVALUATION.employee_id,
      ADD_EVALUATION.evaluation_date,
      ADD_EVALUATION.job_id,
      ADD_EVALUATION.manager_id,
      ADD_EVALUATION.department_id,
      ADD_EVALUATION.total_score
    );
    

    (Qualifying the parameter names with the procedure name ensures that they are not confused with the columns that have the same names.)

    The title of the ADD_EVALUATION pane is in italic font, indicating that the procedure is not yet saved in the database.

19. From the File menu, select Save.

    Oracle Database compiles the procedure and saves it. The title of the ADD_EVALUATION pane is no longer in italic font.

Tutorial: Creating a Standalone Stored Function

To create a standalone stored function, use either the SQL Developer tool Create PL/SQL Function or the DDL statement CREATE FUNCTION.

This tutorial shows how to use the Create PL/SQL Function tool to create a standalone stored function named calculate_score that has three parameters and returns a value of type NUMBER.

To create a standalone stored function using Create PL/SQL Function tool:

1. On the Connections tab, expand hr_conn.

   Under the hr_conn icon, a list of schema object types appears.

2. Right-click Functions.

   A list of choices appears.

3. Click New Function.

   The Create PL/SQL Function window opens. It looks like the Create PL/SQL Procedure window (see "Tutorial: Creating a Standalone Stored Procedure") except that its Parameters pane has a row for the value that the function returns. In that row, the value of Name is <Return> and the default value of Type is VARCHAR2.

4. For Schema, accept the default value, HR.

5. For Name, change FUNCTION1 to calculate_score.

6. In the Parameters pane, in the Type field of the only row, select NUMBER from the drop-down list.

7. Click the icon Add Column.

   A row appears under the column headings. Its fields have these default values: Name, param1; Type, VARCHAR2; Mode, IN; Default Value, empty.

8. For Name, change param1 to cat.

9. For Type, accept the default, VARCHAR2.

10. For Mode, accept the default value, IN.

11. Leave Default Value empty.

12. Add a second parameter by repeating steps 7 through 11 with the Name score and the Type NUMBER.

13. Add a third parameter by repeating steps 7 through 11 with the Name weight and the Type NUMBER.

14. Click OK.

    The calculate_score pane opens, showing the CREATE FUNCTION statement that created the function:

    CREATE OR REPLACE
    FUNCTION calculate_score
    ( cat IN VARCHAR2
    , score IN NUMBER
    , weight IN NUMBER
    ) RETURN NUMBER AS
    BEGIN
      RETURN NULL;
    END calculate_score;
    

    Because the only statement in the execution part of the function is RETURN NULL, the function does nothing.

15. Replace NULL with score * weight.

    The title of the calculate_score pane is in italic font, indicating that the function is not yet saved in the database.

16. Select Save from the File menu.

    Oracle Database compiles the function and saves it. The title of the calculate_score pane is no longer in italic font.

Changing Standalone Stored Subprograms

To change a standalone stored subprogram, use either the SQL Developer tool Edit or the DDL statement ALTER PROCEDURE or ALTER FUNCTION.

To change a standalone stored subprogram using the Edit tool:

1. On the Connections tab, expand hr_conn.

   Under the hr_conn icon, a list of schema object types appears.

2. Expand either Functions or Procedures.

   A list of functions or procedures appears.

3. Click the function or procedure to change.

   To the right of the Connections pane, a frame appears. Its top tab has the name of the subprogram to change. Under the top tab are subtabs.

4. Click the subtab Code.

   The Code pane appears, showing the code that created the subprogram to change.

5. Click the icon Edit.

   Another pane appears, also with the name of the subprogram to change.

6. In the pane, change the code.

   The title of the pane changes to italic font, indicating that the change is not yet saved in the database.

7. Select Save from the File menu.

   Oracle Database compiles the subprogram and saves it. The title of the pane is no longer in italic font.

Tutorial: Testing a Standalone Stored Function

This tutorial shows how to use the SQL Developer tool Run to test the standalone stored function calculate_score (created in "Tutorial: Creating a Standalone Stored Function").

To test the calculate_score function using the Run tool:

1. On the Connections tab, expand hr_conn.

   Under the hr_conn icon, a list of schema object types appears.

2. Expand Functions.

   A list of functions appears.

3. Right-click calculate_score.

   A list of choices appears.

4. Click Run.

   The Run PL/SQL window opens. Its PL/SQL Block frame includes this code:

   v_Return := calculate_score (
       CAT => CAT,
       SCORE => SCORE,
       WEIGHT => WEIGHT
     );
   

5. Change the values of SCORE and WEIGHT to 8 and 0.2, respectively:

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

6. Click OK.

   The Running - Log window opens, showing this result:

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

Dropping Standalone Stored Subprograms

To drop a standalone stored subprogram, use either the SQL Developer navigation frame and Drop tool, or the DDL statement DROP PROCEDURE or DROP FUNCTION.

To drop a standalone stored subprogram using the Drop tool:

1. On the Connections tab, expand hr_conn.

   Under the hr_conn icon, a list of schema object types appears.

2. Expand either Functions or Procedures.

   A list of functions or procedures appears.

3. Right-click the name of the function or procedure to drop.

   A list of choices appears.

4. Click Drop.

   The Drop window opens.

5. Click Apply.

   The Confirmation window opens.

6. Click OK.

About Package Structure

A package always has a specification, and it usually has a body.

The package specification defines the package, declaring the types, variables, constants, exceptions, explicit cursors, and subprograms that can be referenced from outside the package. A package specification is an application program interface (API): It has all the information that client programs need to invoke its subprograms, but no information about their implementation.

The package body defines the queries for the explicit cursors, and the code for the subprograms, that are declared in the package specification (therefore, a package with neither explicit cursors nor subprograms does not need a body). The package body can also define local subprograms, which are not declared in the specification and can be invoked only by other subprograms in the package. Package body contents are hidden from client programs. You can change the package body without invalidating the applications that call the package.

Tutorial: Creating a Package Specification

To create a package specification, use either the SQL Developer tool Create PL/SQL Package or the DDL statement CREATE PACKAGE.

This tutorial shows how to use the Create PL/SQL Package tool to create a specification for a package named EMP_EVAL.

This package specification is the API for the sample application that the tutorials and examples in this document show how to develop and deploy.

To create a package specification using Create PL/SQL Package tool:

1. On the Connections tab, expand hr_conn.

   Under the hr_conn icon, a list of schema object types appears.

2. Right-click Packages.

   A list of choices appears.

3. Click New Package.

   The Create PL/SQL Package window opens. The field Schema has the value HR, the field Name has the default value PACKAGE1, and the check box Add New Source In Lowercase is deselected.

4. For Schema, accept the default value, HR.

5. For Name, change PACKAGE1 to EMP_EVAL.

6. Click OK.

   The EMP_EVAL pane opens, showing the CREATE PACKAGE statement that created the package:

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

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

7. (Optional) In the CREATE PACKAGE statement, replace the comment with declarations.

   If you do not do this step now, you can do it later, as shown in "Tutorial: Changing a Package Specification".

8. From the File menu, select Save.

   Oracle Database compiles the package and saves it. The title of the EMP_EVAL pane is no longer in italic font.

Tutorial: Changing a Package Specification

To change a package specification, use either the SQL Developer tool Edit or the DDL statement CREATE PACKAGE with the OR REPLACE clause.

This tutorial shows how to use the Edit tool to change the specification for the EMP_EVAL package (created in "Tutorial: Creating a Package Specification"). Specifically, the tutorial shows how to add declarations for a procedure, eval_department, and a function, calculate_score.

To change EMP_EVAL package specification using the Edit tool:

1. On the Connections tab, expand hr_conn.

   Under the hr_conn icon, a list of schema object types appears.

2. Expand Packages.

   A list of packages appears.

3. Right-click EMP_EVAL.

   A list of choices appears.

4. Click Edit.

   The EMP_EVAL pane opens, showing the CREATE PACKAGE statement that created the package:

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

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

5. In the EMP_EVAL pane, replace the comment with this code:

   PROCEDURE eval_department ( dept_id IN NUMBER );
   
   FUNCTION calculate_score ( evaluation_id IN NUMBER
                            , performance_id IN NUMBER)
                            RETURN NUMBER;
   

   A new EMP_EVAL pane opens, showing the changed CREATE PACKAGE statement. The title of the pane is in italic font, which indicates that the changes have not been saved to the database.

6. Click the icon Compile.

   The changed package specification compiles and is saved to the database. The title of the EMP_EVAL pane is no longer in italic font.

Tutorial: Creating a Package Body

To create a package body, use either the SQL Developer tool Create Body or the DDL statement CREATE PACKAGE BODY.

This tutorial shows how to use the Create Body tool to create a body for the EMP_EVAL package (created in "Tutorial: Creating a Package Specification" and changed in "Tutorial: Changing a Package Specification").

This package body will contain the implementation details of the sample application that the tutorials and examples in this document show how to develop and deploy.

To create a body for the package EMP_EVAL using the Create Body tool:

1. On the Connections tab, expand hr_conn.

   Under the hr_conn icon, a list of schema object types appears.

2. Expand Packages.

   A list of packages appears.

3. Right-click EMP_EVAL.

   A list of choices appears.

4. Click Create Body.

   The EMP_EVAL Body pane appears, showing the automatically generated code for the package body:

   CREATE OR REPLACE
   PACKAGE BODY emp_eval AS
    
     PROCEDURE eval_department(dept_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;
   

   The title of the pane is in italic font, which indicates that the code is not saved in the database.

5. (Optional) In the CREATE PACKAGE BODY statement:

   • Replace the comments with executable statements.

   • (Optional) In the executable part of the procedure, either delete NULL or replace it with an executable statement.

   • (Optional) In the executable part of the function, either replace NULL with another expression.

   If you do not do this step now, you can do it later, as shown in "Tutorial: Declaring Variables and Constants in a Subprogram".

6. Click the icon Compile.

   The changed package body compiles and is saved to the database. The title of the EMP_EVAL Body pane is no longer in italic font.

Dropping a Package

To drop a package (specification and body), use either the SQL Developer navigation frame and Drop tool, or the DDL statement DROP PACKAGE.

To drop a package using the Drop tool:

1. On the Connections tab, expand the hr_conn information by clicking the plus sign (+) to the left of the hr_conn icon.

   Under the hr_conn icon, a list of schema object types appears.

2. Expand Packages.

   A list of packages appears.

3. Right-click the name of the package to drop.

   A list of choices appears.

4. Click Drop Package.

   The Drop window opens.

5. Click Apply.

   The Confirmation window opens.

6. Click OK.

Tutorial: Declaring Variables and Constants in a Subprogram

This tutorial shows how to use the SQL Developer tool Edit to declare variables and constants in the EMP_EVAL.calculate_score function (specified in "Tutorial: Creating a Package Specification"). (This tutorial is also an example of changing a package body.)

To declare variables and constants in calculate_score function:

1. On the Connections tab, expand the hr_conn information by clicking the plus sign (+) to the left of the hr_conn icon.

   Under the hr_conn icon, a list of schema object types appears.

2. Expand Packages.

   A list of packages appears.

3. Expand EMP_EVAL.

   A list appears.

4. Right-click EMP_EVAL Body.

   A list of choices appears.

5. Click Edit.

   The EMP_EVAL Body pane appears, showing the code for the package body:

   CREATE OR REPLACE
   PACKAGE BODY emp_eval AS
    
     PROCEDURE eval_department ( dept_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;
   

6. Between RETURN NUMBER AS and BEGIN, add these variable and constant declarations:

   n_score       NUMBER(1,0);                -- variable
   n_weight      NUMBER;                     -- variable
   max_score     CONSTANT NUMBER(1,0) := 9;  -- constant, initial value 9
   max_weight    CONSTANT NUMBER(8,8) := 1;  -- constant, initial value 1
   

7. From the File menu, select Save.

   Oracle Database saves the changed package body.

Ensuring that Variables, Constants, and Parameters Have Correct Data Types

After "Tutorial: Declaring Variables and Constants in a Subprogram", the code for the calculate_score function, in the body of the package EMP_EVAL, is:

FUNCTION calculate_score ( evaluation_id IN NUMBER
                          , performance_id IN NUMBER )
                          RETURN NUMBER AS
  n_score       NUMBER(1,0);                -- variable
  n_weight      NUMBER;                     -- variable
  max_score     CONSTANT NUMBER(1,0) := 9;  -- constant, initial value 9
  max_weight    CONSTANT NUMBER(8,8) := 1;  -- constant, initial value 1
  BEGIN
    /* TODO implementation required */
    RETURN NULL;
  END calculate_score;

The variables, constants, and parameters of the function represent values from the tables SCORES (created in "Creating Tables with the CREATE TABLE Statement") and PERFORMANCE_PARTS (created in "Tutorial: Creating a Table with the Create Table Tool"):

• Variable n_score will hold a value from the SCORE column of the SCORES table, and constant max_score will be compared to such values.

• Variable n_weight will hold a value from the WEIGHT column of the PERFORMANCE_PARTS table, and constant max_weight will be compared to such values.

• Parameter evaluation_id will hold a value from the EVALUATION_ID column of the SCORES table.

• Parameter performance_id will hold a value from the PERFORMANCE_ID column of the SCORES table.

Therefore, each variable, constant, and parameter has the same data type as its corresponding column.

If the data types of the columns change, you want the data types of the variables, constants, and parameters to change to the same data types; otherwise, the calculate_score function will be invalidated.

To ensure that the data types of the variables, constants, and parameters will always match those of the columns, declare them with the %TYPE attribute. The %TYPE attribute supplies the data type of a table column or another variable, ensuring the correct data type assignment.

Tutorial: Changing Declarations to Use the %TYPE Attribute

This tutorial shows how to use the SQL Developer tool Edit to change the declarations of the variables, constants, and formal parameters of the EMP_EVAL.calculate_score function (shown in "Tutorial: Declaring Variables and Constants in a Subprogram") to declarations that use the %TYPE attribute.

To change the declarations in calculate_score to use %TYPE:

1. On the Connections tab, expand the hr_conn information by clicking the plus sign (+) to the left of the hr_conn icon.

   Under the hr_conn icon, a list of schema object types appears.

2. Expand Packages.

   A list of packages appears.

3. Expand EMP_EVAL.

   A list appears.

4. Right-click EMP_EVAL Body.

   A list of choices appears.

5. Click Edit.

   The EMP_EVAL Body pane appears, showing the code for the package body:

   CREATE OR REPLACE
   PACKAGE BODY emp_eval AS
    
     PROCEDURE eval_department ( dept_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
     n_score       NUMBER(1,0);                -- variable
     n_weight      NUMBER;                     -- variable
     max_score     CONSTANT NUMBER(1,0) := 9;  -- constant, initial value 9
     max_weight    CONSTANT NUMBER(8,8) := 1;  -- constant, initial value 1
     BEGIN
       /* TODO implementation required */
       RETURN NULL;
     END calculate_score;
   
   END emp_eval;
   

6. In the code for the function, make the changes shown in 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;
     n_weight      PERFORMANCE_PARTS.WEIGHT%TYPE;
     max_score     CONSTANT SCORES.SCORE%TYPE := 9;
     max_weight    CONSTANT PERFORMANCE_PARTS.WEIGHT%TYPE := 1;
   

7. Right-click EMP_EVAL.

   A list of choices appears.

8. Click Edit.

   The EMP_EVAL pane opens, showing the CREATE PACKAGE statement that created the package:

   CREATE OR REPLACE PACKAGE emp_eval AS
    
   PROCEDURE eval_department(dept_id IN NUMBER);
   FUNCTION calculate_score(evaluation_id IN NUMBER
                           , performance_id IN NUMBER)
                             RETURN NUMBER;
    
   END emp_eval;
   

9. In the code for the function, make the changes shown in bold font:

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

10. Right-click EMP_EVAL.

    A list of choices appears.

11. Click Compile.

Assigning Values to Variables

You can assign a value to a variable in these ways:

• Use the assignment operator to assign it the value of an expression.

• Use the SELECT INTO or FETCH statement to assign it a value from a table.

• Pass it to a subprogram as an OUT or IN OUT parameter, and then assign the value inside the subprogram

• Bind the variable to a value.

Topics:

• Assigning Values to Variables with the Assignment Operator

• Assigning Values to Variables with the SELECT INTO Statement

variable_name data_type := expression;

variable_name := expression;

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;
  n_weight      PERFORMANCE_PARTS.WEIGHT%TYPE;
  running_total NUMBER := 0;
  max_score     CONSTANT SCORES.SCORE%TYPE := 9;
  max_weight    CONSTANT PERFORMANCE_PARTS.WEIGHT%TYPE:= 1;
BEGIN
  running_total := max_score * max_weight;
  RETURN running_total;
END calculate_score;

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;
  n_weight      performance_parts.weight%TYPE;
  running_total NUMBER := 0;
  max_score     CONSTANT scores.score%TYPE := 9;
  max_weight    CONSTANT performance_parts.weight%TYPE:= 1;
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;

PROCEDURE add_eval ( employee_id IN EMPLOYEES.EMPLOYEE_ID%TYPE
                   , today IN DATE )
AS
  job_id         EMPLOYEES.JOB_ID%TYPE;
  manager_id     EMPLOYEES.MANAGER_ID%TYPE;
  department_id  EMPLOYEES.DEPARTMENT_ID%TYPE;
BEGIN
  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;
 
  INSERT INTO EVALUATIONS (
    evaluation_id,
    employee_id,
    evaluation_date,
    job_id,
    manager_id,
    department_id,
    total_score
  )
  VALUES (
    evaluations_seq.NEXTVAL,   -- evaluation_id
    add_eval.employee_id,      -- employee_id
    add_eval.today,            -- evaluation_date
    add_eval.job_id,           -- job_id
    add_eval.manager_id,       -- manager_id
    add_eval.department_id,    -- department_id
    0                          -- total_score
  );
END add_eval;

About Control Statements

PL/SQL has three categories of control statements:

• Conditional selection statements, which let you execute different statements for different data values.

  The conditional selection statements are IF and and CASE.

• Loop statements, which let you repeat the same statements with a series of different data values.

  The loop statements are FOR LOOP, WHILE LOOP and basic LOOP.

  The EXIT statement transfers control to the end of a loop. The CONTINUE statement exits the current iteration of a loop and transfers control to the next iteration. Both EXIT and CONTINUE have an optional WHEN clause, in which you can specify a condition.

• Sequential control statements, which let you go to a specified, labeled statement, or to do nothing.

  The sequential control statements are GOTO and and NULL.

Using the IF Statement

The IF statement either executes or skips a sequence of statements, depending on the value of a Boolean expression.

The IF statement has this syntax:

IF boolean_expression THEN statement [, statement ]
[ ELSEIF boolean_expression THEN statement [, statement ] ]...
[ ELSE  statement [, statement ] ]
END IF;

Suppose that your company evaluates employees twice a year in the first 10 years of employment, but only once a year afterward. You want a function that returns the evaluation frequency for an employee. You can use an IF statement to determine the return value of the function, as in Example 7-4.

Add the eval_frequency function to the body of the EMP_EVAL package, but not to the specification. Because it is not in the specification, eval_frequency is local to the package—it can be invoked only by other subprograms in the package, not from outside the package.

FUNCTION eval_frequency (emp_id IN EMPLOYEES.EMPLOYEE_ID%TYPE)
  RETURN PLS_INTEGER
AS
  h_date     EMPLOYEES.HIRE_DATE%TYPE;
  today      EMPLOYEES.HIRE_DATE%TYPE;
  eval_freq  PLS_INTEGER;
BEGIN
  SELECT SYSDATE INTO today FROM DUAL;

  SELECT HIRE_DATE INTO h_date
  FROM EMPLOYEES
  WHERE EMPLOYEE_ID = emp_id;

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

  RETURN eval_freq;
END eval_frequency;

Using the CASE Statement

The CASE statement chooses from a sequence of conditions, and executes the corresponding statement.

The simple CASE statement evaluates a single expression and compares it to several potential values. It has this syntax:

CASE expression
WHEN value THEN statement
[ WHEN value THEN statement ]...
[ ELSE statement [, statement ]... ]
END CASE;

The searched CASE statement evaluates multiple Boolean expressions and chooses the first one whose value is TRUE. For information about the searched CASE statement, see Oracle Database PL/SQL Language Reference.

Suppose that, if an employee is evaluated only once a year, you want the eval_frequency function to suggest a salary increase, which depends on the JOB_ID.

Add the CASE statement shown in Example 7-5 to the eval_frequency function. (For information about the procedure that prints the strings, DBMS_OUTPUT.PUT_LINE, see Oracle Database PL/SQL Packages and Types Reference.)

FUNCTION eval_frequency (emp_id IN EMPLOYEES.EMPLOYEE_ID%TYPE)
  RETURN PLS_INTEGER
AS
  h_date     EMPLOYEES.HIRE_DATE%TYPE;
  today      EMPLOYEES.HIRE_DATE%TYPE;
  eval_freq  PLS_INTEGER;
  j_id       EMPLOYEES.JOB_ID%TYPE;

BEGIN
  SELECT SYSDATE INTO today FROM DUAL;

  SELECT HIRE_DATE INTO h_date
  FROM EMPLOYEES
  WHERE EMPLOYEE_ID = emp_id;

  IF ((h_date + (INTERVAL '120' MONTH)) < today) THEN
    eval_freq := 1;

    SELECT JOB_ID INTO j_id
    FROM EMPLOYEES
    WHERE EMPLOYEE_ID = emp_id;

    CASE j_id
       WHEN 'PU_CLERK' THEN DBMS_OUTPUT.PUT_LINE(
         'Consider 8% salary increase for employee # ' || emp_id);
       WHEN 'SH_CLERK' THEN DBMS_OUTPUT.PUT_LINE(
         'Consider 7% salary increase for employee # ' || emp_id);
       WHEN 'ST_CLERK' THEN DBMS_OUTPUT.PUT_LINE(
         'Consider 6% salary increase for employee # ' || emp_id);
       WHEN 'HR_REP' THEN DBMS_OUTPUT.PUT_LINE(
         'Consider 5% salary increase for employee # ' || emp_id);
       WHEN 'PR_REP' THEN DBMS_OUTPUT.PUT_LINE(
         'Consider 5% salary increase for employee # ' || emp_id);
       WHEN 'MK_REP' THEN DBMS_OUTPUT.PUT_LINE(
         'Consider 4% salary increase for employee # ' || emp_id);
       ELSE DBMS_OUTPUT.PUT_LINE(
         'Nothing to do for employee #' || emp_id);
    END CASE;
  ELSE
    eval_freq := 2;
  END IF;
 
  RETURN eval_freq;
END eval_frequency;

Using the FOR LOOP Statement

The FOR LOOP statement repeats a sequence of statements once for each integer in the range lower_bound through upper_bound. Its syntax is:

FOR counter IN lower_bound..upper_bound LOOP
  statement [, statement ]...
END LOOP;

The statements between LOOP and END LOOP can use counter, but cannot change its value.

Suppose that, instead of only suggesting a salary increase, you want the eval_frequency function to report what the salary would be if it increased by the suggested amount every year for five years.

Change the eval_frequency function as shown in bold font in Example 7-6. (For information about the procedures that prints the strings, DBMS_OUTPUT.PUT_LINE, see Oracle Database PL/SQL Packages and Types Reference.)

FUNCTION eval_frequency (emp_id IN EMPLOYEES.EMPLOYEE_ID%TYPE)
  RETURN PLS_INTEGER
AS
  h_date      EMPLOYEES.HIRE_DATE%TYPE;
  today       EMPLOYEES.HIRE_DATE%TYPE;
  eval_freq   PLS_INTEGER;
  j_id        EMPLOYEES.JOB_ID%TYPE;
  sal         EMPLOYEES.SALARY%TYPE;
  sal_raise   NUMBER(3,3) := 0;

BEGIN
  SELECT SYSDATE INTO today FROM DUAL;

  SELECT HIRE_DATE INTO h_date
  FROM EMPLOYEES
  WHERE EMPLOYEE_ID = emp_id;

  IF ((h_date + (INTERVAL '120' MONTH)) < today) THEN
    eval_freq := 1;

    SELECT JOB_ID INTO j_id
    FROM EMPLOYEES
    WHERE EMPLOYEE_ID = emp_id;

    SELECT SALARY INTO sal
    FROM EMPLOYEES
    WHERE EMPLOYEE_ID = emp_id;

    CASE j_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 (sal_raise != 0) THEN
      BEGIN
        DBMS_OUTPUT.PUT_LINE('If salary ' || sal || ' increases by ' ||
          ROUND((sal_raise * 100),0) ||
          '% each year for 5 years, it will be:');

        FOR i IN 1..5 LOOP
          sal := sal * (1 + sal_raise);
          DBMS_OUTPUT.PUT_LINE(ROUND(sal, 2) || ' after ' || i || ' year(s)');
        END LOOP;
      END;
    END IF;

  ELSE
    eval_freq := 2;
  END IF;

  RETURN eval_freq;
END eval_frequency;

Using the WHILE LOOP Statement

The WHILE LOOP statement repeats a sequence of statements while a condition is TRUE. Its syntax is:

WHILE condition LOOP
  statement [, statement ]...
END LOOP;

Suppose that the eval_frequency function uses the WHILE LOOP statement instead of the FOR LOOP statement, and ends after the proposed salary exceeds the maximum salary for the JOB_ID.

Change the eval_frequency function as shown in bold font in Example 7-7. (For information about the procedures that prints the strings, DBMS_OUTPUT.PUT_LINE, see Oracle Database PL/SQL Packages and Types Reference.)

FUNCTION eval_frequency (emp_id IN EMPLOYEES.EMPLOYEE_ID%TYPE)
  RETURN PLS_INTEGER
AS
  h_date      EMPLOYEES.HIRE_DATE%TYPE;
  today       EMPLOYEES.HIRE_DATE%TYPE;
  eval_freq   PLS_INTEGER;
  j_id        EMPLOYEES.JOB_ID%TYPE;
  sal         EMPLOYEES.SALARY%TYPE;
  sal_raise   NUMBER(3,3) := 0;
  sal_max     JOBS.MAX_SALARY%TYPE;

BEGIN
  SELECT SYSDATE INTO today FROM DUAL;

  SELECT HIRE_DATE INTO h_date
  FROM EMPLOYEES
  WHERE EMPLOYEE_ID = emp_id;

  IF ((h_date + (INTERVAL '120' MONTH)) < today) THEN
    eval_freq := 1;

    SELECT JOB_ID INTO j_id
    FROM EMPLOYEES
    WHERE EMPLOYEE_ID = emp_id;

    SELECT SALARY INTO sal
    FROM EMPLOYEES
    WHERE EMPLOYEE_ID = emp_id;

    SELECT MAX_SALARY INTO sal_max
    FROM JOBS
    WHERE JOB_ID = j_id;

    CASE j_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 (sal_raise != 0) THEN
      BEGIN
        DBMS_OUTPUT.PUT_LINE('If salary ' || sal || ' increases by ' ||
          ROUND((sal_raise * 100),0) ||
          '% each year, it will be:');

        WHILE sal <= sal_max LOOP
          sal := sal * (1 + sal_raise);
          DBMS_OUTPUT.PUT_LINE(ROUND(sal, 2));
        END LOOP;

        DBMS_OUTPUT.PUT_LINE('Maximum salary for this job is ' || sal_max);
      END;
    END IF;
  ELSE
    eval_freq := 2;
  END IF;
 
  RETURN eval_freq;
END eval_frequency;

Using the Basic LOOP and EXIT WHEN Statements

The basic LOOP statement repeats a sequence of statements. Its syntax is:

LOOP
  statement [, statement ]...
END LOOP;

At least one statement must be an EXIT statement; otherwise, the LOOP statement runs indefinitely.

The EXIT WHEN statement (the EXIT statement with its optional WHEN clause) exits a loop when a condition is TRUE and transfers control to the end of the loop.

In the eval_frequency function, in the last iteration of the WHILE LOOP statement, the last computed value usually exceeds the maximum salary.

Change the WHILE LOOP statement to a basic LOOP statement that includes an EXIT WHEN statement, as shown in Example 7-8.

FUNCTION eval_frequency (emp_id IN EMPLOYEES.EMPLOYEE_ID%TYPE)
  RETURN PLS_INTEGER
AS
  h_date      EMPLOYEES.HIRE_DATE%TYPE;
  today       EMPLOYEES.HIRE_DATE%TYPE;
  eval_freq   PLS_INTEGER;
  j_id        EMPLOYEES.JOB_ID%TYPE;
  sal         EMPLOYEES.SALARY%TYPE;
  sal_raise   NUMBER(3,3) := 0;
  sal_max     JOBS.MAX_SALARY%TYPE;

BEGIN
  SELECT SYSDATE INTO today FROM DUAL;

  SELECT HIRE_DATE INTO h_date
  FROM EMPLOYEES
  WHERE EMPLOYEE_ID = emp_id;

  IF ((h_date + (INTERVAL '120' MONTH)) < today) THEN
    eval_freq := 1;

    SELECT JOB_ID INTO j_id
    FROM EMPLOYEES
    WHERE EMPLOYEE_ID = emp_id;

    SELECT SALARY INTO sal
    FROM EMPLOYEES
    WHERE EMPLOYEE_ID = emp_id;

    SELECT MAX_SALARY INTO sal_max
    FROM JOBS
    WHERE JOB_ID = j_id;

    CASE j_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 (sal_raise != 0) THEN
      BEGIN
        DBMS_OUTPUT.PUT_LINE('If salary ' || sal || ' increases by ' ||
          ROUND((sal_raise * 100),0) ||
          '% each year, it will be:');

        LOOP
          sal := sal * (1 + sal_raise);
          EXIT WHEN sal > sal_max;
          DBMS_OUTPUT.PUT_LINE(ROUND(sal,2));
        END LOOP;

        DBMS_OUTPUT.PUT_LINE('Maximum salary for this job is ' || sal_max);
      END;
    END IF;
  ELSE
    eval_freq := 2;
  END IF;
 
  RETURN eval_freq;
END eval_frequency;

About Records

A record is a PL/SQL composite variable that can store data values of different types, similar to a struct type in C, C++, or Java. The internal components of a record are called fields. To access a record field, you use dot notation: record_name.field_name.

You can treat record fields like scalar variables. You can also pass entire records as subprogram parameters (if neither the sending nor receiving subprogram is a standalone stored subprogram).

Records are useful for holding data from table rows, or from certain columns of table rows. Each record field corresponds to a table column.

There are three ways to create a record:

• Declare a RECORD type, and then declare a variable of that type.

  The syntax is:

  TYPE record_name IS RECORD
    ( field_name data_type [:= initial_value]
   [, field_name data_type [:= initial_value ] ]... );
  
  variable_name record_name;
  

• Declare a variable of the type table_name%ROWTYPE.

  The fields of the record have the same names and data types as the columns of the table.

• Declare a variable of the type cursor_name%ROWTYPE.

  The fields of the record have the same names and data types as the columns of the table in the FROM clause of the cursor SELECT statement.

Tutorial: Declaring a RECORD Type

This tutorial shows how to use the SQL Developer tool Edit to declare a RECORD type, sal_info, whose fields can hold salary information for an employee—job ID, minimum and maximum salary for that job ID, current salary, and suggested raise.

To declare RECORD type sal_info:

1. On the Connections tab, expand the hr_conn information by clicking the plus sign (+) to the left of the hr_conn icon.

   Under the hr_conn icon, a list of schema object types appears.

2. Expand Packages.

   A list of packages appears.

3. Right-click EMP_EVAL.

   A list of choices appears.

4. Click Edit.

   The EMP_EVAL pane opens, showing the CREATE PACKAGE statement that created the package:

   CREATE OR REPLACE PACKAGE emp_eval AS
    
   PROCEDURE eval_department(dept_id IN NUMBER);
   FUNCTION calculate_score(evaluation_id IN NUMBER
                           , performance_id IN NUMBER)
                             RETURN NUMBER;
    
   END emp_eval;
   

5. In the EMP_EVAL pane, immediately before END emp_eval, add this code:

   TYPE sal_info IS RECORD
     ( j_id     jobs.job_id%type
     , sal_min  jobs.min_salary%type
     , sal_max  jobs.max_salary%type
     , sal      employees.salary%type
     , sal_raise NUMBER(3,3) );
   

   A new EMP_EVAL pane opens, showing the changed CREATE PACKAGE statement. The title of the pane is in italic font, which indicates that the changes have not been saved to the database.

6. Click the icon Compile.

   The changed package specification compiles and is saved to the database. The title of the EMP_EVAL pane is no longer in italic font.

   Now you can declare records of the type sal_info, as in "Tutorial: Creating and Invoking a Subprogram with a Record Parameter".

Tutorial: Creating and Invoking a Subprogram with a Record Parameter

If you declared the RECORD type sal_info in "Tutorial: Declaring a RECORD Type", this tutorial shows how to use the SQL Developer tool Edit to do the following:

• Create a procedure, salary_schedule, which has a parameter of type sal_info.

• Change the eval_frequency function so that it declares a record, emp_sal, of the type sal_info, populates its fields, and passes it to the salary_schedule procedure.

Because eval_frequency will invoke salary_schedule, the declaration of salary_schedule must precede the declaration of eval_frequency (otherwise, the package will not compile). However, the definition of salary_schedule can be anywhere in the package body.

To create salary_schedule and change eval_frequency:

1. On the Connections tab, expand the hr_conn information by clicking the plus sign (+) to the left of the hr_conn icon.

   Under the hr_conn icon, a list of schema object types appears.

2. Expand Packages.

   A list of packages appears.

3. Expand EMP_EVAL.

   A list appears.

4. Right-click EMP_EVAL Body.

   A list of choices appears.

5. Click Edit.

   The EMP_EVAL Body pane appears, showing the code for the package body.

6. In the EMP_EVAL Body pane, immediately before END emp_eval, add this definition of the salary_schedule procedure:

   PROCEDURE salary_schedule (emp IN sal_info) AS
     accumulating_sal  NUMBER;
   BEGIN
     DBMS_OUTPUT.PUT_LINE('If salary ' || emp.sal || 
       ' increases by ' || ROUND((emp.sal_raise * 100),0) || 
       '% each year, it will be:');
   
     accumulating_sal := emp.sal;
   
     WHILE accumulating_sal <= emp.sal_max LOOP
       accumulating_sal := accumulating_sal * (1 + emp.sal_raise);
       DBMS_OUTPUT.PUT_LINE(ROUND(accumulating_sal,2) ||', ');
     END LOOP;
   END salary_schedule;
   

   A new EMP_EVAL Body pane opens, showing the changed CREATE PACKAGE BODY statement. The title of the pane is in italic font, which indicates that the changes have not been saved to the database.

7. In the EMP_EVAL Body pane, enter the code shown in bold font, in this position:

   create or replace
   PACKAGE BODY EMP_EVAL AS
   
   FUNCTION eval_frequency (emp_id EMPLOYEES.EMPLOYEE_ID%TYPE)
     RETURN PLS_INTEGER;
   PROCEDURE salary_schedule(emp IN sal_info);
   PROCEDURE add_eval(employee_id IN employees.employee_id%type, today IN DATE);
    
   PROCEDURE eval_department (dept_id IN NUMBER) AS
   

8. Edit the eval_frequency function, making the changes shown in bold font:

   FUNCTION eval_frequency (emp_id EMPLOYEES.EMPLOYEE_ID%TYPE)
     RETURN PLS_INTEGER
   AS
     h_date     EMPLOYEES.HIRE_DATE%TYPE;
     today      EMPLOYEES.HIRE_DATE%TYPE;
     eval_freq  PLS_INTEGER;
     emp_sal    SAL_INFO;  -- replaces sal, sal_raise, and sal_max
    
   BEGIN
     SELECT SYSDATE INTO today FROM DUAL;
    
     SELECT HIRE_DATE INTO h_date
     FROM EMPLOYEES
     WHERE EMPLOYEE_ID = emp_id;
    
     IF ((h_date + (INTERVAL '120' MONTH)) < today) THEN
        eval_freq := 1;
    
        /* populate emp_sal */
    
        SELECT JOB_ID INTO emp_sal.j_id FROM EMPLOYEES
        WHERE EMPLOYEE_ID = emp_id;
    
        SELECT MIN_SALARY INTO emp_sal.sal_min FROM JOBS
        WHERE JOB_ID = emp_sal.j_id;
    
        SELECT MAX_SALARY INTO emp_sal.sal_max FROM JOBS
        WHERE JOB_ID = emp_sal.j_id;
    
        SELECT SALARY INTO emp_sal.sal FROM EMPLOYEES
        WHERE EMPLOYEE_ID = emp_id;
    
        emp_sal.sal_raise := 0;  -- default
    
        CASE emp_sal.j_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 (emp_sal.sal_raise != 0) THEN
          salary_schedule(emp_sal);
        END IF;
      ELSE
        eval_freq := 2;
      END IF;
    
      RETURN eval_freq;
    END eval_frequency;
   

9. Click Compile.

About Cursors

When Oracle Database executes a SQL statement, it stores the result set and processing information in an unnamed private SQL area. A pointer to this unnamed area, called a cursor, lets you retrieve the rows of the result set one at a time. Cursor attributes return information about the state of the cursor.

Every time you run either a SQL DML statement or a PL/SQL SELECT INTO statement, PL/SQL opens an implicit cursor. You can get information about this cursor from its attributes, but you cannot control it. After the statement runs, the database closes the cursor; however, its attribute values remain available until another DML or SELECT INTO statement runs.

PL/SQL also lets you declare explicit cursors. An explicit cursor has a name and is associated with a query (SQL SELECT statement)—usually one that returns multiple rows. After declaring an explicit cursor, you must open it (with the OPEN statement), fetch rows one at a time from the result set (with the FETCH statement), and close the cursor (with the CLOSE statement). After closing the cursor, you can neither fetch records from the result set nor see the cursor attribute values.

The syntax for the value of an implicit cursor attribute is SQLattribute (for example, SQL%FOUND). SQLattribute always refers to the most recently run DML or SELECT INTO statement.

The syntax for the value of an explicit cursor attribute is cursor_name immediately followed by attribute (for example, c1%FOUND).

Table 7-1 lists the cursor attributes and the values that they can return. (Implicit cursors have additional attributes that are beyond the scope of this book.)

Using an Explicit Cursor to Retrieve Result Set Rows One at a Time

The following procedure uses each necessary statement in its simplest form, but provides references to its complete syntax.

To use an explicit cursor to retrieve result set rows one at a time:

1. In the declarative part:

   1. Declare the cursor:

      CURSOR cursor_name IS query;
      

      For complete explicit cursor declaration syntax, see Oracle Database PL/SQL Language Reference.

   2. Declare a record to hold the row returned by the cursor:

      record_name cursor_name%ROWTYPE;
      

      For complete %ROWTYPE syntax, see Oracle Database PL/SQL Language Reference.

2. In the executable part:

   1. Open the cursor:

      OPEN cursor_name;
      

      For complete OPEN statement syntax, see Oracle Database PL/SQL Language Reference.

   2. Fetch rows from the cursor (rows from the result set) one at a time, using a LOOP statement that has syntax similar to this:

      LOOP
        FETCH cursor_name INTO record_name;
        EXIT WHEN cursor_name%NOTFOUND;
        -- Process row that is in record_name:
        statement;
        [ statement; ]...
      END LOOP;
      

      For complete FETCH statement syntax, see Oracle Database PL/SQL Language Reference.

   3. Close the cursor:

      CLOSE cursor_name;
      

      For complete CLOSE statement syntax, see Oracle Database PL/SQL Language Reference.

Tutorial: Using an Explicit Cursor to Retrieve Result Set Rows One at a Time

This tutorial shows how to implement the procedure EMP_EVAL.eval_department, which uses an explicit cursor, emp_cursor.

To implement the EMP_EVAL.eval_department procedure:

1. In the EMP_EVAL package specification, change the declaration of the eval_department procedure as shown in bold font:

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

   (For instructions for changing a package specification, see "Tutorial: Changing a Package Specification".)

2. In the EMP_EVAL package body, change the definition of the eval_department procedure as shown in bold font:

   PROCEDURE eval_department (dept_id IN employees.department_id%TYPE)
   AS
     CURSOR emp_cursor IS
       SELECT * FROM EMPLOYEES
       WHERE DEPARTMENT_ID = dept_id;
   
     emp_record  EMPLOYEES%ROWTYPE;  -- for row returned by cursor
     all_evals   BOOLEAN;  -- true if all employees in dept need evaluations
     today       DATE;
   
   BEGIN
     today := SYSDATE;
   
     IF (EXTRACT(MONTH FROM today) < 6) THEN
       all_evals := FALSE; -- only new employees need evaluations
     ELSE
       all_evals := TRUE;  -- all employees need evaluations
     END IF;
   
     OPEN emp_cursor;
   
     DBMS_OUTPUT.PUT_LINE (
       'Determining evaluations necessary in department # ' ||
       dept_id );
   
     LOOP
       FETCH emp_cursor INTO emp_record;
       EXIT WHEN emp_cursor%NOTFOUND;
   
       IF all_evals THEN
         add_eval(emp_record.employee_id, today);
       ELSIF (eval_frequency(emp_record.employee_id) = 2) THEN
         add_eval(emp_record.employee_id, today);
       END IF;
     END LOOP;
   
     DBMS_OUTPUT.PUT_LINE('Processed ' || emp_cursor%ROWCOUNT || ' records.');
   
     CLOSE emp_cursor;
   END eval_department;
   

   (For a step-by-step example of changing a package body, see "Tutorial: Declaring Variables and Constants in a Subprogram".)

3. Compile the EMP_EVAL package specification.

4. Compile the EMP_EVAL package body.

About Cursor Variables

A cursor variable is like a cursor (see "About Cursors"), except that it is not limited to one query. You can open a cursor variable for a query, process the result set, and then use the cursor variable for another query. Cursor variables are useful for passing query results between subprograms.

To declare a cursor variable, you declare a REF CURSOR type, and then declare a variable of that type (therefore, a cursor variable is often called a REF CURSOR). A REF CURSOR type can be either strong or weak.

A strong REF CURSOR type specifies a return type, which is the RECORD type of its cursor variables. The PL/SQL compiler does not allow you to use these strongly typed cursor variables for queries that return rows that are not of the return type. Strong REF CURSOR types are less error-prone than weak ones, but weak ones are more flexible.

A weak REF CURSOR type does not specify a return type. The PL/SQL compiler accepts weakly typed cursor variables in any queries. Weak REF CURSOR types are interchangeable; therefore, instead of creating weak REF CURSOR types, you can use the predefined type weak cursor type SYS_REFCURSOR.

After declaring a cursor variable, you must open it for a specific query (with the OPEN FOR statement), fetch rows one at a time from the result set (with the FETCH statement), and then either close the cursor (with the CLOSE statement) or open it for another specific query (with the OPEN FOR statement). Opening the cursor variable for another query closes it for the previous query. After closing a cursor variable for a specific query, you can neither fetch records from the result set of that query nor see the cursor attribute values for that query.

Using a Cursor Variable to Retrieve Result Set Rows One at a Time

The following procedure uses each of the necessary statements in its simplest form, but provides references to their complete syntax.

To use a cursor variable to retrieve result set rows one at a time:

1. In the declarative part:

   1. Declare the REF CURSOR type:

      TYPE cursor_type IS REF CURSOR [ RETURN return_type ];
      

      For complete REF CURSOR type declaration syntax, see Oracle Database PL/SQL Language Reference.

   2. Declare a cursor variable of that type:

      cursor_variable cursor_type;
      

      For complete cursor variable declaration syntax, see Oracle Database PL/SQL Language Reference.

   3. Declare a record to hold the row returned by the cursor:

      record_name return_type;
      

      For complete information about record declaration syntax, see Oracle Database PL/SQL Language Reference.

2. In the executable part:

   1. Open the cursor variable for a specific query:

      OPEN cursor_variable FOR query;
      

      For complete information about OPEN FOR statement syntax, see Oracle Database PL/SQL Language Reference.

   2. Fetch rows from the cursor variable (rows from the result set) one at a time, using a LOOP statement that has syntax similar to this:

      LOOP
        FETCH cursor_variable INTO record_name;
        EXIT WHEN cursor_variable%NOTFOUND;
        -- Process row that is in record_name:
        statement;
        [ statement; ]...
      END LOOP;
      

      For complete information about FETCH statement syntax, see Oracle Database PL/SQL Language Reference.

   3. Close the cursor variable:

      CLOSE cursor_variable;
      

      Alternatively, you can open the cursor variable for another query, which closes it for the current query.

      For complete information about CLOSE statement syntax, see Oracle Database PL/SQL Language Reference.

Tutorial: Using a Cursor Variable to Retrieve Result Set Rows One at a Time

This tutorial shows how to change the EMP_EVAL.eval_department procedure so that it uses a cursor variable instead of an explicit cursor, which lets it process multiple departments. The change includes adding a procedure that uses the cursor variable.

This tutorial also shows how to make EMP_EVAL.eval_department and EMP_EVAL.add_eval more efficient: Instead of passing one field of a record to add_eval and having add_eval use three queries to extract three other fields of the same record, eval_department passes the entire record to add_eval, and add_eval uses dot notation to access the values of the other three fields.

To change the EMP_EVAL.eval_department procedure to use a cursor variable:

1. In the EMP_EVAL package specification, add the procedure declaration and the REF CURSOR type definition, as shown in bold font:

   create or replace
   PACKAGE emp_eval AS
   
     PROCEDURE eval_department (dept_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
         ( j_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;
   END emp_eval;
   

   (For instructions for changing a package specification, see "Tutorial: Changing a Package Specification".)

2. In the EMP_EVAL package body, add a forward declaration for the procedure eval_loop_control and change the declaration of the procedure add_eval, as shown in bold font:

   create or replace
   PACKAGE BODY EMP_EVAL AS
   
     FUNCTION eval_frequency (emp_id IN EMPLOYEES.EMPLOYEE_ID%TYPE)
       RETURN PLS_INTEGER;
   
     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);
   ...
   

   (For a step-by-step example of changing a package body, see "Tutorial: Declaring Variables and Constants in a Subprogram".)

3. Change the eval_department procedure to retrieve three separate result sets based on the department, and to invoke the eval_loop_control procedure, as shown in bold font:

   PROCEDURE eval_department(dept_id IN employees.department_id%TYPE) AS
     emp_cursor    emp_refcursor_type;
     current_dept  departments.department_id%TYPE;
   
   BEGIN
     current_dept := dept_id;
   
     FOR loop_c IN 1..3 LOOP
       OPEN emp_cursor FOR
         SELECT * 
         FROM employees
         WHERE current_dept = eval_department.department_id;
   
       DBMS_OUTPUT.PUT_LINE
         ('Determining necessary evaluations in department #' ||
          current_dept);
   
       eval_loop_control(emp_cursor);
   
       DBMS_OUTPUT.PUT_LINE
         ('Processed ' || emp_cursor%ROWCOUNT || ' records.');
   
       CLOSE emp_cursor;
       current_dept := current_dept + 10; 
     END LOOP;
   END eval_department;
   

4. Change the add_eval as shown in bold font:

   PROCEDURE add_eval(emp_record IN employees%ROWTYPE, today IN DATE)
   AS
   -- (Delete local variables)
   BEGIN
     INSERT INTO EVALUATIONS (
       evaluation_id,
       employee_id,
       evaluation_date,
       job_id,
       manager_id,
       department_id,
       total_score
     )
     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. Before END EMP_EVAL, add the following procedure, which fetches the individual records from the result set and processes them:

   PROCEDURE eval_loop_control (emp_cursor IN emp_refcursor_type) AS
      emp_record      EMPLOYEES%ROWTYPE;
      all_evals       BOOLEAN;
      today           DATE;
   BEGIN
     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;
       EXIT WHEN emp_cursor%NOTFOUND;
   
       IF all_evals THEN
         add_eval(emp_record, today);
       ELSIF (eval_frequency(emp_record.employee_id) = 2) THEN
         add_eval(emp_record, today);
       END IF;
     END LOOP;
   END eval_loop_control;
   

6. Before END EMP_EVAL, add the following procedure, which retrieves a result set that contains all employees in the company:

   PROCEDURE eval_everyone AS
     emp_cursor emp_refcursor_type;
   BEGIN
     OPEN emp_cursor FOR SELECT * FROM employees;
     DBMS_OUTPUT.PUT_LINE('Determining number of necessary evaluations.');
     eval_loop_control(emp_cursor);
     DBMS_OUTPUT.PUT_LINE('Processed ' || emp_cursor%ROWCOUNT || ' records.');
     CLOSE emp_cursor;
   END eval_everyone;
   

7. Compile the EMP_EVAL package specification.

8. Compile the EMP_EVAL package body.

About Collections

A collection is a PL/SQL composite variable that stores elements of the same type in a specified order, similar to a one-dimensional array. The internal components of a collection are called elements. Each element has a unique subscript that identifies its position in the collection. To access a collection element, you use subscript notation: collection_name(element_subscript).

You can treat collection elements like scalar variables. You can also pass entire collections as subprogram parameters (if neither the sending nor receiving subprogram is a standalone stored subprogram).

A collection method is a built-in PL/SQL subprogram that either returns information about a collection or operates on a collection. To invoke a collection method, you use dot notation: collection_name.method_name. For example, collection_name.COUNT returns the number of elements in the collection.

PL/SQL has three types of collections:

• Associative arrays (formerly called "PL/SQL tables" or "index-by tables")

• Nested tables

• Variable arrays (varrays)

This document explains only associative arrays.

About Associative Arrays

An associative array is an unbounded set of key-value pairs. Each key is unique, and serves as the subscript of the element that holds the corresponding value. Therefore, you can access elements without knowing their positions in the array, and without traversing the array.

The data type of the key can be either PLS_INTEGER or VARCHAR2 (length).

If the data type of the key is PLS_INTEGER, and the associative array is indexed by integer, and it is dense (that is, it has no gaps between elements), then every element between the first and last element is defined and has a value (which can be NULL).

If the key type is VARCHAR2 (length), the associative array is indexed by string (of length characters), and it is sparse; that is, it might have gaps between elements.

When traversing a dense associative array, you do not need to beware of gaps between elements; when traversing a sparse associative array, you do.

To assign a value to an associative array element, you can use an assignment operator:

array_name(key) := value

If key is not in the array, the assignment statement adds the key-value pair to the array. Otherwise, the statement changes the value of array_name(key) to value.

Associative arrays are useful for storing data temporarily. They do not use the disk space or network operations that tables require. However, because associative arrays are intended for temporary storage, you cannot manipulate them with DML statements or use SELECT INTO statements to assign their values to variables.

If you declare an associative array in a package, and assign values to the variable in the package body, then the associative array exists for the life of the database session. Otherwise, it exists for the life of the subprogram in which you declare it.

Declaring Associative Arrays

To declare an associative array, you declare an associative array type, and then declare a variable of that type. The simplest syntax is:

TYPE array_type IS TABLE OF element_type INDEX BY key_type;

array_name  array_type;

An efficient way to declare an associative array is with a cursor, using the following procedure. The procedure uses each necessary statement in its simplest form, but provides references to its complete syntax.

To use a cursor to declare an associative array:

1. In the declarative part:

   1. Declare the cursor:

      CURSOR cursor_name IS query;
      

      For complete explicit cursor declaration syntax, see Oracle Database PL/SQL Language Reference.

   2. Declare the associative array type:

      TYPE array_type IS TABLE OF cursor_name%ROWTYPE
        INDEX BY { PLS_INTEGER | VARCHAR2 length }
      

      For complete associative array type declaration syntax, see Oracle Database PL/SQL Language Reference.

   3. Declare an associative array variable of that type:

      array_name  array_type;
      

      For complete variable declaration syntax, see Oracle Database PL/SQL Language Reference.

Example 7-9 uses the preceding procedure to declare two associative arrays, employees_jobs and jobs_, and then declares a third associative array, job_titles_type, without using a cursor. The first two arrays are indexed by integer; the third is indexed by string.

DECLARE
  -- Declare cursor:

  CURSOR employees_jobs_cursor IS
    SELECT FIRST_NAME, LAST_NAME, JOB_ID
    FROM EMPLOYEES
    ORDER BY JOB_ID, LAST_NAME, FIRST_NAME;

  -- Declare associative array type:

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

  -- Declare associative array:

  employees_jobs  employees_jobs_type;

  -- Use same procedure to declare another associative array:

  CURSOR jobs_cursor IS
    SELECT JOB_ID, JOB_TITLE
    FROM JOBS;

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

  jobs_  jobs_type;

-- Declare associative array without using cursor:

  TYPE job_titles_type IS TABLE OF JOBS.JOB_TITLE%TYPE
    INDEX BY JOBS.JOB_ID%TYPE;  -- jobs.job_id%type is varchar2(10)

  job_titles  job_titles_type;

BEGIN
  NULL;
END;
/

Populating Associative Arrays

The most efficient way to populate a dense associative array is with a cursor and the FETCH statement with the BULK COLLECT INTO clause, using the following procedure. The procedure uses each necessary statement in its simplest form, but provides references to its complete syntax.

You cannot use the following procedure to populate a sparse associative array. Instead, you must use an assignment statement inside a loop statement. For information about loop statements, see "Controlling Program Flow".

To use a cursor to populate an associative array indexed by integer:

1. If you have not done so, declare an associative array with a cursor, using the procedure in "Declaring Associative Arrays".

2. In the executable part of the PL/SQL unit in which you declared the associative array:

   1. Open the cursor:

      OPEN cursor_name;
      

      For complete OPEN statement syntax, see Oracle Database PL/SQL Language Reference.

   2. Fetch all rows from the cursor into the associative array variable at once, using a FETCH statement with the BULK COLLECT INTO clause:

      FETCH cursor_name BULK COLLECT INTO aa_variable;
      

      For complete FETCH statement syntax, see Oracle Database PL/SQL Language Reference.

   3. Close the cursor:

      CLOSE cursor_name;
      

      For complete CLOSE statement syntax, see Oracle Database PL/SQL Language Reference.

Example 7-10 uses the preceding procedure to populate the associative arrays employees_jobs and jobs_, which are indexed by integer. Then it uses an assignment statement inside a FOR LOOP statement to populate the associative array job_titles_type, which is indexed by string.

-- Declarative part from Example 7-9 goes here.
BEGIN
  -- Populate associative arrays indexed by integer:

  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;

  -- Populate associative array indexed by string:

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

Traversing Dense Associative Arrays

A dense associative array (indexed by integer) has no gaps between elements—every element between the first and last element is defined and has a value (which can be NULL). You can traverse a dense array with a FOR LOOP statement, as in Example 7-11.

When inserted in the executable part of Example 7-10, after the code that populates the employees_jobs array, the FOR LOOP statement in Example 7-11 prints the elements of the employees_jobs array in the order in which they were stored. Their storage order was determined by the ORDER BY clause in the declaration of employees_jobs_cursor, which was used to declare employees_jobs (see Example 7-9).

The upper bound of the FOR LOOP statement, employees_jobs. COUNT, invokes a collection method that returns the number of elements in the array. For more information about COUNT, see Oracle Database PL/SQL Language Reference.

-- Code that populates employees_jobs must precede this code:

FOR i IN 1..employees_jobs.COUNT LOOP
  DBMS_OUTPUT.PUT_LINE(
    RPAD(employees_jobs(i).first_name, 23) ||
    RPAD(employees_jobs(i).last_name,  28) ||     employees_jobs(i).job_id);
  END LOOP;

William                Gietz                       AC_ACCOUNT
Shelley                Higgins                     AC_MGR
Jennifer               Whalen                      AD_ASST
Steven                 King                        AD_PRES
Lex                    De Haan                     AD_VP
Neena                  Kochhar                     AD_VP
John                   Chen                        FI_ACCOUNT
...
Jose Manuel            Urman                       FI_ACCOUNT
Nancy                  Greenberg                   FI_MGR
Susan                  Mavris                      HR_REP
David                  Austin                      IT_PROG
...
Valli                  Pataballa                   IT_PROG
Michael                Hartstein                   MK_MAN
Pat                    Fay                         MK_REP
Hermann                Baer                        PR_REP
Shelli                 Baida                       PU_CLERK
...
Sigal                  Tobias                      PU_CLERK
Den                    Raphaely                    PU_MAN
Gerald                 Cambrault                   SA_MAN
...
Eleni                  Zlotkey                     SA_MAN
Ellen                  Abel                        SA_REP
...
Clara                  Vishney                     SA_REP
Sarah                  Bell                        SH_CLERK
...
Peter                  Vargas                      ST_CLERK
Adam                   Fripp                       ST_MAN
...
Matthew                Weiss                       ST_MAN

Traversing Sparse Associative Arrays

A sparse associative array (indexed by string) might have gaps between elements. You can traverse it with a WHILE LOOP statement, as in Example 7-12.

To run the code in Example 7-12, which prints the elements of the job_titles array:

1. At the end of the declarative part of Example 7-9, insert this variable declaration:

   i jobs_.job_id%TYPE;
   

2. In the executable part of Example 7-10, after the code that populates the job_titles array, insert the code from Example 7-12.

Example 7-12 includes two collection method invocations, job_titles.FIRST and job_titles.NEXT(i). job_titles.FIRST returns the first element of job_titles, and job_titles.NEXT(i) returns the subscript that succeeds i. For more information about FIRST, see Oracle Database PL/SQL Language Reference. For more information about NEXT, see Oracle Database PL/SQL Language Reference.

/* Declare this variable in declarative part:

   i jobs_.job_id%TYPE;

   Add this code to the executable part,
   after code that populates job_titles:
*/

i := job_titles.FIRST;

WHILE i IS NOT NULL LOOP
  DBMS_OUTPUT.PUT_LINE(RPAD(i, 12) || job_titles(i));
  i := job_titles.NEXT(i);
END LOOP;

AC_ACCOUNT  Public Accountant
AC_MGR      Accounting Manager
AD_ASST     Administration Assistant
AD_PRES     President
AD_VP       Administration Vice President
FI_ACCOUNT  Accountant
FI_MGR      Finance Manager
HR_REP      Human Resources Representative
IT_PROG     Programmer
MK_MAN      Marketing Manager
MK_REP      Marketing Representative
PR_REP      Public Relations Representative
PU_CLERK    Purchasing Clerk
PU_MAN      Purchasing Manager
SA_MAN      Sales Manager
SA_REP      Sales Representative
SH_CLERK    Shipping Clerk
ST_CLERK    Stock Clerk
ST_MAN      Stock Manager

About Exceptions and Exception Handlers

When a run-time error occurs in PL/SQL code, an exception is raised. If the subprogram (or block) in which the exception is raised has an exception-handling part, control transfers to it; otherwise, execution stops.

Run-time errors can arise from design faults, coding mistakes, hardware failures, and many other sources. Because you cannot anticipate all possible errors, Oracle recommends including exception-handling parts in your subprograms ("About Subprogram Structure" shows where to put the exception-handling part).

Oracle Database has many predefined exceptions, which it raises automatically when a program violates database rules or exceeds system-dependent limits. For example, if a SELECT INTO statement returns no rows, Oracle Database raises the predefined exception NO_DATA_FOUND. For a summary of predefined PL/SQL exceptions, see Oracle Database PL/SQL Language Reference.

PL/SQL lets you define (declare) your own exceptions. An exception declaration has this syntax:

exception_name EXCEPTION;

Unlike a predefined exception, a user-defined exception must be raised explicitly, using either the RAISE statement or the DBMS_STANDARD.RAISE_APPLICATION_ERROR procedure. For example:

IF condition THEN RAISE exception_name;

For information about the DBMS_STANDARD.RAISE_APPLICATION_ERROR procedure, see Oracle Database PL/SQL Language Reference.

The exception-handling part of a subprogram contains one or more exception handlers. An exception handler has this syntax:

WHEN { exception_name [ OR exception_name ]... | OTHERS } THEN
  statement; [ statement; ]...

A WHEN OTHERS exception handler handles unexpected run-time errors. If used, it must be last. For example:

EXCEPTION
  WHEN exception_1 THEN
    statement; [ statement; ]...
  WHEN exception_2 OR exception_3 THEN
    statement; [ statement; ]...
  WHEN OTHERS THEN
    statement; [ statement; ]...
END;

An alternative to the WHEN OTHERS exception handler is the EXCEPTION_INIT pragma, which associates a user-defined exception name with an Oracle Database error number.

Handling Predefined Exceptions

Example 7-13 shows, in bold font, how to change the EMP_EVAL.eval_department procedure to handle the predefined exception NO_DATA_FOUND. Make this change and compile the changed procedure. (For an example of how to change a package body, see "Tutorial: Declaring Variables and Constants in a Subprogram".)

PROCEDURE eval_department(dept_id IN employees.department_id%TYPE) AS
  emp_cursor    emp_refcursor_type;
  current_dept  departments.department_id%TYPE;

BEGIN
  current_dept := dept_id;

  FOR loop_c IN 1..3 LOOP
    OPEN emp_cursor FOR
      SELECT * 
      FROM employees
      WHERE current_dept = dept_id;

    DBMS_OUTPUT.PUT_LINE
      ('Determining necessary evaluations in department #' ||
       current_dept);

    eval_loop_control(emp_cursor);

    DBMS_OUTPUT.PUT_LINE
      ('Processed ' || emp_cursor%ROWCOUNT || ' records.');

    CLOSE emp_cursor;
    current_dept := current_dept + 10; 
  END LOOP;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE ('The query did not return a result set');
END eval_department;

Declaring and Handling User-Defined Exceptions

Example 7-14 shows, in bold font, how to change the EMP_EVAL.calculate_score function to declare and handle two user-defined exceptions, wrong_weight and wrong_score. Make this change and compile the changed function. (For an example of how to change a package body, see "Tutorial: Declaring Variables and Constants in a Subprogram".)

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;
  n_weight      performance_parts.weight%TYPE;
  running_total NUMBER := 0;
  max_score     CONSTANT scores.score%TYPE := 9;
  max_weight    CONSTANT performance_parts.weight%TYPE:= 1;
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
    IF (n_weight > max_weight) OR (n_weight < 0) THEN
      RAISE weight_wrong;
    END IF;
  END;

  BEGIN
    IF (n_score > max_score) OR (n_score < 0) THEN
      RAISE score_wrong;
    END IF;
  END;

  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;