PL/SQL Packages

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

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

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.

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

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

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

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

)

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)

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

END emp_pack; -- End of Package

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

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

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

--Private procedure

PROCEDURE emp_sal (v_eno IN emp.empno%TYPE)

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)

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

Packages: Forward Declarations

Identifiers must be declared before referencing them

CREATE OR REPLACE PACKAGE BODY forward_pack

PROCEDURE award_bonus (. . .)

BEGIN

calc_rating(. . .);--illegal reference

END;

FUNCTION calc_rating ( . . . )

RETURN NUMBER

BEGIN

RETURN …;

END;

END forward_pack;

CREATE OR REPLACE PACKAGE BODY forward_pack

-- Forward declaration

FUNCTION calc_rating(. . .) RETURN NUMBER;

PROCEDURE award_bonus (. . .)

BEGIN

calc_rating(. . .);-- Legal reference

END;

FUNCTION calc_rating ( . . . )

RETURN NUMBER

BEGIN

RETURN …;

END;

END forward_pack;

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

PROCEDURE increasesal (eno emp.empno%TYPE);

PROCEDURE increasesal (ename emp.ename%TYPE);

END overloadpack;

CREATE OR REPLACE PACKAGE BODY overloadpack

PROCEDURE increasesal (eno emp.empno%TYPE)

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)

cnt NUMBER;

CURSOR empcur

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;

Dropping and Recompiling a Package

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

USER_PROCEDURES Is used to display the list of all procedures and functions in that users schema

Oracle Applicatons

Pages

Thursday 7 April 2016

PL/SQL Packages

No comments:

Post a Comment