Package - pl/sql programming, PL-SQL Programming

Assignment Help:

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.


Related Discussions:- Package - pl/sql programming

Perform exception handling with user-defined errors, On occasion, some of B...

On occasion, some of Brewbean's customers mistakenly leave an item out of a basket already checked out, so they create a new basket containing the missing items. However, they requ

Assignments in pl/sql, Assignments in pl/sql The Variables and constants...

Assignments in pl/sql The Variables and constants are initialized every time a block or subprogram is entered. By default, the variables are initialized to NULL. Therefore, unle

Product-specific packages, Product-specific Packages The Oracle and dif...

Product-specific Packages The Oracle and different Oracle tools are supplied with the product-specific packages which help you to build the PL/SQL-based applications. For illus

Parameter default values, Parameter Default Values As the illustration ...

Parameter Default Values As the illustration below shows, you can initialize the IN parameters to the default values. In that way, you can pass various numbers of actual par

Case sensitivity-naming conventions, Case Sensitivity Similar to all the...

Case Sensitivity Similar to all the identifiers, the variables, the names of constants, and parameters are not case sensitive. For illustration, PL/SQL considers the following n

Example of not exists operator - sql, Example of NOT EXISTS Operator - SQL ...

Example of NOT EXISTS Operator - SQL Example is a translation into SQL of the corresponding example, which is included there merely to show that for any scalar comparison the

Rewrite rules - tautology, Equivalences & Rewrite Rules: If notice tha...

Equivalences & Rewrite Rules: If notice that as well as allowing us to prove trivial theorems, and tautologies enable us to establish that certain sentences are saying the sam

Bulk fetching - bulk bind performance improvement, Bulk Fetching The i...

Bulk Fetching The illustration below shows that you can bulk-fetch from a cursor into one or more collections: DECLARE TYPE NameTab IS TABLE OF emp.ename%TYPE; TYPE S

Using operator value in pl sql, Using Operator VALUE: As you may expec...

Using Operator VALUE: As you may expect, the operator VALUE returns the value of an object. The VALUE takes its argument a correlation variable.  For illustration, to return a

Multiset types - sql, Multiset types - SQL An SQL multiset is what in ...

Multiset types - SQL An SQL multiset is what in mathematics is also known as a bag-something like a set except that the same element can appear more than once. The body of an

Write Your Message!

Captcha
Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd