Description
PLSQL Package is a collection of functions and procedures. Once a package is defined, the function and procedure can be called by using the package name with Dot(.). And, dbms_output.put_line is a predefined package name.
A PLSQL Package will have 2 categories, they are:
- Package declaration
- Package body
Description
The PLSQL Package declaration is the interface to the package and declares constants, variables, cursors, subprograms and exceptions that can be referenced from outside the package.
The public objects are the objects that are placed inside the declaration. And, if any subprogram that doesn't exist in the declaration of package, but, if the code exist in the package, then the code is said to be private object.
Syntax
Create or replace package is
Function Declaration
Procedure Declaration
End package_name;
/
Function declaration => Declaration a function.
Procedure declaration => Declaration a procedure.
End package_name => End of the package.
Examples
The below example describes the creation of package declaration:
[c]SQL> create package cust_sal as
2 procedure find_sal(c_id customers.id%type);
3 end cust_sal;
4 /
PL/SQL package is successfully created.[/c]
The above example will create the package declaration with cust_sal and package identifier as customer_id(c_id).
Description
The package body actualizes the package declaration. It encloses the usage of each sub program and cursor declared inside the package declaration. The sub programs characterized in a package body are attainable over an extreme end of the package if and only if the declaration simultaneously emerges in the package declaration.
Syntax
Create or replace package body package_name is
Function Definition
Procedure Definition
End package_name;
/
Package_name => Name of the package
Function definition => Declaration of function
Procedure definition => Declaration of procedure
End package_name => End of the package body
Examples
The below example describes the package body program.
[c]SQL> create or replace package body pack2 is
2 Function calcsum(a number,b number)
3 return number is
4 s number(10);
5 begin
6 s:a+b;
7 returns;
8 end calcsum;
9 procedure calcmax(a IN number,b IN number,m OUT number)is
10 begin
11 if a>b then
12 m:=a;
13 else
14 m:=b;
15 end if;
16 end calcmax;
17 end pack1;
18 /
SQL>@Second.sql
Package body successfully created.
Select pack2.calcsum(10,20) from dual;
Sum
-------
30
SQL>variable x number
SQL>execute pack2.calcmax(10,20:x)
SQL>print x;
x
-----
20[/c]
In the above example, the package body is created with the name pack2 and calculates the sum value & maximum values.
Description
Package provides numerous advantages such as:
The entire package is stacked in the memory while calling the package sub program.
Information hiding describes the item type in the subprograms both visible and invisible.
Cursors and open variables enclosed for the continuation of a session are shared and executed in every subprogram.
Package will encapsulate consistently related sorts such as subprograms and items in the module named PL/SQL. Every package is seen effectively and the interfaces among the package are well defined, and clear.
While designing an application all the interface information should be characterized inside the package declaration. And, without package body, one can code and execute the PL/SQL package.
- Better execution
- Information hiding
- Included functionality
- Measured quality
- Easier application design
Description
The Alter package statement will explicitly recompile a package declaration and package body. Explicit recompilation wipes out the requirement for accurate standard run time recompilation and anticipates related run time arrangement errors & execution overhead.
Every single item inside the package will be stored in a different way in units. Alter package items will recompile the package statements together. It will change the work and adjust system articulation by performing recompilation announcements exclusively.
Syntax
ALTER PACKAGE[schema. ]package
COMPILE[DEBUG]
[PACKAGE |SPECIFICATION |BODY]
[compiler_parameters_clause];
Schema => Contains the schema object.
Package => Determines the term of the packages to be recompiled.
Compile => Is a key word for executing the program.
Package => Contains package specification and body.
Body => Specify to recompile the packages.
Specification => Analyse for compilation errors.
Description
The package drop articulation will drop a stored package from the database. The package drop explanation will drop the declaration and body of a package.
Syntax
DROP PACKAGE[BODY][schema. ]package;
Drop => Is used to drop a package.
Package =>Contains functions and procedures.
Body => Specify to drop the packages.
Schema => Indicates the schema involving packages.
Examples
The below example describes the drop package.
[c]
SQL>DROP Package customer1
dropped customer1 successfully
SQL>select * from customer1;
No such table exists
[/c]
The package customer1 is successfully dropped.
Key Points
- PLSQL Package - It is a collection of functions and procedures.
- Package declaration - Is the interface to the package.
- Package body - It actualizes the declaration packages.
- Package alter - Alters the declaration of package and package body.
- Package drop - Will drop a stored package from the database.