Follow us: Connect on YouTube Connect on YouTube Connect on YouTube

Pages

Thursday, 7 April 2016

Oracle PL/SQL Packages


Oracle Package Features

A Package is a PL/SQL construct that allows related objects to be stored together
A Package is a stored database object
Groups together PROCEDURES, FUNCTIONS, CURSORS, types and variables
Other PL/SQL blocks can reference packages

Package Creation

Package has two components:
Package Specification
Package body        

Package Specification:

Declares types, variables, constants, exceptions, cursors and subprograms available for use Constructs declared in the package specifications are public

Package Specification Syntax:


          CREATE  OR REPLACE  PACKAGE  package_name IS
          procedure_specification
          function_specification
                    variable_declaration
          cursor_declaration etc.
           END  package_name;

Package Body:

It defines cursors and subprograms and so implements the specification
It can not be successfully compiled unless the package specification has already been successfully compiled

Package Body Syntax:


          CREATE  OR REPLACE  PACKAGE  BODY  package_name IS
           private_variable
          procedure_definition
          function_definition etc.
          private function or procedure definition
          END package_name;

Access Rules for Package Members


Data members and methods that are declared within the package specification become public members of the package
Data members and methods that are not declared within the package specification, but are implemented in the package body, become private to the package
Public package members can be accessed by the methods belonging to the package as well as by methods outside the package
Private package members can be accessed only by the methods defined in the package itself
Data members of a package (variables, constants, cursors etc.), both public and private, act like global data members and persist within a single Oracle session or connection.

When a packaged subprogram opens a cursor, that cursor remains open and is available to other packaged methods throughout the session. You do not have to explicitly define the cursor in each method. You can open it in one module and fetch it in another module.

Oracle Packages – Advantages

      Advantages of Packages
Modularity: encapsulates logically related programming structures in a named module
Easier Application Design: initially only the interface information in the package specification is required
Information hiding: constructs can be public or private
Better Performance: when a packaged subprogram is called the first time, the entire package is loaded into memory
Overloading: packages allow you to overload procedures and functions




Packages: Example


Creating a Package Specification

CREATE OR REPLACE PACKAGE emp_pack
IS
--Two procedures added to the specification become global objects
   PROCEDURE emp_add (
      p_empno    IN   emp.empno%TYPE,
      p_ename    IN   emp.ename%TYPE,
      p_esal     IN   emp.sal%TYPE,
      p_deptno   IN   emp.deptno%TYPE
   );

   PROCEDURE emp_del (p_empno IN emp.empno%TYPE);
END emp_pack;

 Creating a Package Body

CREATE OR REPLACE PACKAGE BODY emp_pack
IS
-- Private function with deptno as parameter to check the existence of deptno value in dept table
   FUNCTION valid_deptno (p_deptno dept.deptno%TYPE)
      RETURN BOOLEAN
   IS
      dummy   NUMBER (2);
   BEGIN
      SELECT deptno
        INTO dummy
        FROM dept
       WHERE deptno = p_deptno;

      RETURN TRUE;
--Exception Handling if the deptno value is not found
   EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
         RETURN FALSE;
   END valid_deptno;                            -- End of the private function

--Implementing the procedure defined in the specification
   PROCEDURE emp_add (
      p_empno    IN   emp.empno%TYPE,
      p_ename    IN   emp.ename%TYPE,
      p_esal     IN   emp.sal%TYPE,
      p_deptno   IN   emp.deptno%TYPE
   )
   IS
   BEGIN
--Function valid_deptno is called by procedure emp_add
      IF valid_deptno (p_deptno)
      THEN
         INSERT INTO emp
                     (empno, ename, sal, deptno
                     )
              VALUES (p_empno, p_ename, p_esal, p_deptno
                     );

         DBMS_OUTPUT.put_line
            (   'Emp# : '
             || p_empno
             || ' added to                                                                          table'
            );
         COMMIT;
      ELSE
         raise_application_error
                          (-20010,
                           'Invalid
                  department…Try again'
                          );
      END IF;
   EXCEPTION
      WHEN OTHERS
      THEN
         DBMS_OUTPUT.put_line(SQLERRM);
   END emp_add;                                 -- End of the public procedure

--Implementing the procedure defined in the specification
   PROCEDURE emp_del (p_empno IN emp.empno%TYPE)
   IS
   BEGIN
      DELETE FROM emp
            WHERE empno = p_empno;

      IF SQL%NOTFOUND
      THEN
         raise_application_error (-20120, 'Employee does not exist');
      ELSE
         DBMS_OUTPUT.put_line(SQL%ROWCOUNT || ' ROWS DELETED');
      END IF;
   END emp_del;                                 -- End of the public procedure
ENDemp_pack;                                   -- End of Package
    

Calling a packaged procedure or function from SQL*Plus
          EXECUTE  emp_pack.emp_del(1001)
   

Concept of Global Variable in a Package


PL/SQL packages offer the ability to implement global data in your application environment.
If you have access to the package, you can modify package variables in one method and then reference those changed variables in another method. The values of packaged data members persist for the duration of a user session (connection to the database).
Package variables can carry data across the boundaries of transactions, since they are tied to the session itself and not to a transaction.

Oracle Packages: Use of Global Variable


      Package Specification

          The variable v_sal is a packaged public variable that can act as a global variable for the package members as well as outside methods, that have right to execute the package


CREATE OR REPLACE PACKAGE increment_pack
IS
   -- Global Variable declared inside the specification
   v_sal   emp.sal%TYPE;

   PROCEDURE show_increment (p_empno emp.empno%TYPE);
END increment_pack;

Package Body

CREATE OR REPLACE PACKAGE BODY increment_pack
IS
   --Private procedure
   PROCEDURE emp_sal (v_eno IN emp.empno%TYPE)
   IS
   BEGIN
      SELECT sal
        INTO v_sal
        FROM emp
       WHERE empno = v_eno;
   END emp_sal;

   -- End of the private procedure within the package
   PROCEDURE show_increment (p_empno emp.empno%TYPE)
   IS
   BEGIN
      emp_sal (p_empno);                     -- Call to the private procedure

      -- Value of the variable v_sal is set by the procedure emp_sal and is used here
      IF v_sal > 2000
      THEN
         DBMS_OUTPUT.put_line('no salary increase');
      ELSE
         UPDATE emp
            SET sal = sal + sal * .1
          WHERE empno = p_empno;

         DBMS_OUTPUT.put_line('salary increased');
      END IF;
   EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
         DBMS_OUTPUT.put_line('Wrong employee number');
      WHEN OTHERS
      THEN
         DBMS_OUTPUT.put_line(SQLERRM);
   END show_increment;
END increment_pack;


-- Call 1
BEGIN
                   increment_pack.show_increment(5678);
END;
/-- Wrong employee number
-- call 2
 BEGIN
                   increment_pack.show_increment(7369);
 END;
/

-- ORA-06502: PL/SQL: numeric or value error: number precision too large
-- call 3

BEGIN
   increment_pack.show_increment (7521);
END;
-- salary increased

Oracle Packages: Forward Declarations


Identifiers must be declared before referencing them
CREATE OR REPLACE PACKAGE BODY forward_pack
IS
          PROCEDURE award_bonus (. . .)
          IS
          BEGIN
              calc_rating(. . .);--illegal reference
          END;
          FUNCTION calc_rating ( . . . )
          RETURN NUMBER
          IS
          BEGIN
                   RETURN …;
          END;
END forward_pack;

CREATE OR REPLACE PACKAGE BODY forward_pack
IS
          -- Forward declaration
          FUNCTION calc_rating(. . .) RETURN NUMBER;    
          PROCEDURE award_bonus (. . .)
          IS
          BEGIN
              calc_rating(. . .);-- Legal reference
          END;
          FUNCTION calc_rating ( . . . )
          RETURN NUMBER
          IS
          BEGIN
                   RETURN …;
          END;
END forward_pack;

Oracle Packages – Overloading


Allows you to use the same name for different subprograms inside a package
Requires formal parameters of the subprogram to differ in number, order or data type family
For functions if only the return type is different but parameters are same then overloading is not permitted
CREATE OR REPLACE PACKAGE PACK_MARKETING AS
PROCEDURE MKT_PROJECTION;
PROCEDURE MKT_PROJECTION(MK_ID NUMBER);
PROCEDURE MKT_PROJECTION (MK_NAME VARCHAR2);
PROCEDURE MKT_PROJECTION(MK_ID NUMBER,MK_NAME
                              VARCHAR2);
PROCEDURE MKT_PROJECTION(MK_NAME VARCHAR2,
                         MK_ID NUMBER);
END PACK_MARKETING;

Overloading: Example


CREATE OR REPLACE PACKAGE overloadpack
AS
   PROCEDURE increasesal (eno emp.empno%TYPE);

   PROCEDURE increasesal (ename emp.ename%TYPE);
ENDoverloadpack;
/

CREATE OR REPLACE PACKAGE BODY overloadpack
AS
   PROCEDURE increasesal (eno emp.empno%TYPE)
   AS
   BEGIN
      UPDATE emp
         SET sal = sal + 1000
       WHERE empno = eno;

      IF SQL%NOTFOUND
      THEN
         DBMS_OUTPUT.put_line('Wrong employee                    number');
      END IF;
   END increasesal;

   PROCEDURE increasesal (ename emp.ename%TYPE)
   AS
      cnt   NUMBER;

      CURSOR empcur
      IS
         SELECT empno
           FROM emp
          WHERE ename = increasesal.ename;
   BEGIN
      SELECT COUNT (empno)
        INTO cnt
        FROM emp
       WHERE ename = increasesal.ename;

      IF cnt = 0
      THEN
         DBMS_OUTPUT.put_line('Wrong employee name');
      ELSE
         FOR eno IN empcur
         LOOP
            UPDATE emp
               SET sal = sal + 1000
             WHERE empno = eno.empno;
         END LOOP;
      END IF;
   END increasesal;
END overloadpack;

Package Dropping and Recompiling


 Alter package
    ALTER PACKAGE package_name COMPILE;
 Dropping package specification
    DROP PACKAGE package_name;
 Dropping package body
    DROP PACKAGE BODY package_name;

Data Dictionary Views


           The data dictionary views used for stand-alone procedures and functions are also available for packaged procedures and functions         
              USER_SOURCE Is used to obtain the text of a stored object in the users schema
              USER_OBJECTS Is used to display the list of all objects in the users schema

Share this article :

0 comments:

Post a Comment