4 Developing and Using Stored Procedures

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 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 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 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 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 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

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.

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 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.
   

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 the illustration drop_procedure_1.gif
   
   

2. In the Drop window, click Apply.

   
   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.

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.

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 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 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.

The following example shows how to create a package directly in the SQL Worksheet.

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

PACKAGE eval Compiled.

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 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 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.
   

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 the illustration drop_package_1.gif
   
   

3. In the Drop Package dialog, click Apply.

   
   Description of the illustration drop_package_2.gif
   
   

4. In the Confirmation dialog, click OK.

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.

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.

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.

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;

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;

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;

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;

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.

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

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;

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

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;

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.

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

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;

WHILE condition LOOP
  ...
END LOOP;

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

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;

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;

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:

%NOTFOUND

%FOUND

%ROWCOUNT

%ISOPEN

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.

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

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;

EMP_EVAL Body Compiled

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 REPLAXCE 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
   

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.

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:

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:

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:

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:

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.

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.

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;

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.

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;

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.

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;