PL/SQL: Writing Initialization Code for a Package

Writing Initialization Code for a Package for PL/SQL packageProblem

You want to execute some code each time a particular PL/SQL package is instantiated in a session.

 

Solution

Create an initialization block for the package in question. By doing so, you will have the ability to execute code each time the package is initialized. The following example shows the same package that was constructed in this article. However, this time the package contains an initialization block.

 

CREATE OR REPLACE PACKAGE BODY process_employee_time IS

  PROCEDURE grant_raises (pct_increase IN NUMBER,
                          upper_bound IN NUMBER) as

  CURSOR emp_cur is 
     SELECT employee_id, first_name, last_name
     FROM employees;

  BEGIN

    -- loop through each record in the employees table
    FOR emp_rec IN emp_cur LOOP

         DBMS_OUTPUT.PUT_LINE(emp_rec.first_name || ' ' || emp_rec.last_name);
         increase_wage(emp_rec.employee_id, pct_increase, upper_bound);

    END LOOP;

  END grant_raises;   

  PROCEDURE increase_wage (empno_in IN NUMBER,
                          pct_increase IN NUMBER,
                          upper_bound IN NUMBER) AS

     emp_count    NUMBER := 0;
     emp_sal      employees.salary%TYPE;
     Results   VARCHAR2(50);

  BEGIN

     SELECT count(*)
     INTO emp_count
     FROM employees
     WHERE employee_id = empno_in;

     IF emp_count > 0 THEN
    
          -- IF EMPLOYEE FOUND, THEN OBTAIN RECORD
         SELECT salary
         INTO emp_sal
         FROM employees
         WHERE employee_id = empno_in;

         IF emp_sal < upper_bound AND 
             round(emp_sal + (emp_sal * pct_increase), 2) < upper_bound THEN

                 UPDATE employees
                 SET salary = round(salary + (salary * pct_increase),2)
                 WHERE employee_id = empno_in;
      
                 results := 'SUCCESSFUL INCREASE';

         ELSE
                 results := 'EMPLOYEE MAKES TOO MUCH, DECREASE RAISE PERCENTAGE';
    END IF;

  ELSE

    Results := 'NO EMPLOYEE FOUND';

  END IF;

  DBMS_OUTPUT.PUT_LINE(results);

  END increase_wage;

BEGIN

  DBMS_OUTPUT.PUT_LINE('EXECUTING THE INITIALIZATION BLOCK');

END;

 

The initialization block in this example is the last code block within the package body. In this case, that block lies in the final three lines.

 

How It Works

The initialization block for the package in the solution displays a line of text to indicate that the initialization block has been executed. The initialization block will execute once per session, the first time the package is used in that session. If you were to create this package in your session and invoke one of its members, you would see the message print. Although an initialization message is not very useful, there are several good reasons to use an initialization block. One such reason is to perform a query to obtain some data for the session.

Вас заинтересует / Intresting for you:

PL/SQL package: Collecting Rel...
PL/SQL package: Collecting Rel... 159 views sepia Sat, 01 Dec 2018, 10:54:57
Describe the Basic PL/SQL Prog...
Describe the Basic PL/SQL Prog... 390 views Андрей Волков Wed, 12 Sep 2018, 15:23:39
Why Oracle DBAs learn PL/SQL a...
Why Oracle DBAs learn PL/SQL a... 385 views Андрей Волков Wed, 12 Sep 2018, 14:43:12
Translating PL/SQL to JavaScri...
Translating PL/SQL to JavaScri... 392 views Гвен Sun, 03 Jun 2018, 11:54:40

Comments on PL/SQL: Writing Initialization Code for a Package

Be the first to comment
Please login to comment