Package - pl/sql programming, PL-SQL Programming

What Is a Package?

The package is a schema object that group logically related PL/SQL items, types, and subprograms. The Packages usually have 2 parts, a specification & a body, though many times the body is needless. The specification is the interface to your applications; it declares the type, constants, variables, exceptions, cursors, and subprograms accessible for use. The body fully defines the cursors & subprograms, and so equipment the specification.

The figure shows, the specification as an operational interface and of the body as the "black box." You can enhance, debug, or replace the package body without changing the interface to the package.

1715_package.png

Figure: Package Interface

To build packages, use the CREATE PACKAGE statement that you can execute interactively from the SQL Plus. The syntax for the same is as shown:

CREATE [OR REPLACE] PACKAGE package_name

[AUTHID {CURRENT_USER | DEFINER}] {IS | AS}

[type_definition [type_definition] ...]

[cursor_spec [cursor_spec] ...]

[item_declaration [item_declaration] ...]

[{subprogram_spec | call_spec} [{subprogram_spec | call_spec}]...]

END [package_name];

[CREATE [OR REPLACE] PACKAGE BODY package_name {IS | AS}

[type_definition [type_definition] ...]

[cursor_body [cursor_body] ...]

[item_declaration [item_declaration] ...]

[{subprogram_spec | call_spec} [{subprogram_spec | call_spec}]...]

[BEGIN

sequence_of_statements]

END [package_name];]

The specifications hold the public declarations that are visible to your application. The body holds the implementation details and private declarations that are hidden from your application. The declarative section below of the package body is the optional initialization section that typically holds the statements that initialize the package variables. The AUTHID clause determine whether all the packaged subprograms execute with the privileges of their definer or invoker, and whether their unqualified references to schema objects are solved in the schema of the definer or invoker.

The call specification publishes a Java method or external C function in the Oracle data dictionary. The call specification publishes the routine by mapping its parameter types, name, and return type to their SQL counterparts.

In the illustration below, you package a cursor, a record type, and two employment procedures. Note that the procedure hire_employee uses the database series empno_seq and the function SYSDATE to insert a new employee number & hire date, correspondingly.

CREATE OR REPLACE PACKAGE emp_actions AS -- spec

TYPE EmpRecTyp IS RECORD (emp_id INTEGER, salary REAL);

CURSOR desc_salary RETURN EmpRecTyp;

PROCEDURE hire_employee (

ename VARCHAR2,

job VARCHAR2,

mgr NUMBER,

sal NUMBER,

comm NUMBER,

deptno NUMBER);

PROCEDURE fire_employee (emp_id NUMBER);

END emp_actions;

CREATE OR REPLACE PACKAGE BODY emp_actions AS -- body

CURSOR desc_salary RETURN EmpRecTyp IS

SELECT empno, sal FROM emp ORDER BY sal DESC;

PROCEDURE hire_employee (

ename VARCHAR2,

job VARCHAR2,

mgr NUMBER,

sal NUMBER,

comm NUMBER,

deptno NUMBER) IS

BEGIN

INSERT INTO emp VALUES (empno_seq.NEXTVAL, ename, job,

mgr, SYSDATE, sal, comm, deptno);

END hire_employee;

PROCEDURE fire_employee (emp_id NUMBER) IS

BEGIN

DELETE FROM emp WHERE empno = emp_id;

END fire_employee;

END emp_actions;

The declarations in the package specification are only visible & accessible to the applications.

The Implementation details in the package body are hidden and inaccessible. Therefore, you can change the body without having to recompile the calling programs.

Posted Date: 10/5/2012 5:58:18 AM | Location : United States







Related Discussions:- Package - pl/sql programming, Assignment Help, Ask Question on Package - pl/sql programming, Get Answer, Expert's Help, Package - pl/sql programming Discussions

Write discussion on Package - pl/sql programming
Your posts are moderated
Related Questions
Second Step at defining type SID in SQL CREATE TYPE SID AS VARCHAR(5) ; Explanation: TYPE SID announces that a type named SID is being defined to the system.

Parameter Aliasing   To optimize the subprogram call, the PL/SQL compiler can decide between the two techniques of the parameter passing. With the by-value techniques, the v

Example of Table Literal - SQL Example: A Table Literal (correct version) VALUES ('S1', 'C1', 'Anne'), ('S1', 'C2', 'Anne'), ('S2', 'C1', 'Boris'), ('S3', 'C3'

Predefined Exceptions The internal exception is raised implicitly whenever your PL/SQL program exceeds a system-dependent limit or violates an Oracle rule. Each & every Oracle

Set Operators The Set operators combine the results of the two queries into one result. The INTERSECT returns all the distinct rows selected by both queries. The MINUS returns

SQL Cursor   The Oracle implicitly opens a cursor to process each SQL statement not related with an explicit cursor. The PL/SQL refers to the most current implicit cursor as t

Use of COUNT in SQL It describes and discusses various general methods of expressing constraints, eventually noting that support for "=" with relation operands is sufficient f

Using Invoker Rights: By default, the stored procedure executes with the privileges of its definer, not its invoker. These procedures are bound to the schema in which they inh

What Is a Collection The collection is an ordered group of elements, all of similar type (for e.g. the grades for a class of students). Each element has a unique subscript whic

Recursive Subprograms The recursive subprogram is the one that calls itself. Think of a recursive call as a call to a few other subprograms that does the similar task as your