Introduction PL/SQL

Basic PL/SQL

Advance PL/SQL


PL/SQL Packages


PL/SQL Packages is schema object and collection of related data type (variables, constants), cursors, procedures, functions are defining within a single context. Package are device into two part,

  1. Package Specification
  2. Package Body

Package specification block you can define variables, constants, exceptions and package body you can create procedure, function, subprogram.


PL/SQL Package Advantages

  1. You can create package to store all related functions and procedures are grouped together into single unit called packages.
  2. Package are reliable to granting a privileges.

  3. All function and procedure within a package can share variable among them.

  4. Package are support overloading to overload functions and procedures.

  5. Package are improve the performance to loading the multiple object into memory at once, therefore, subsequent calls to related program doesn't required to calling physically I/O.

  6. Package are reduce the traffic because all block execute all at once.

PL/SQL Packages


PL/SQL Package Syntax

PL/SQL Specification : This contain the list of variables, constants, functions, procedure names which are the part of the package. PL/SQL specification are public declaration and visible to a program.

Defining Package Specification Syntax

CREATE [OR REPLACE] PACKAGE package_name
    IS | AS
    [variable_declaration ...]
    [constant_declaration ...]
    [exception_declaration ...] 
    [cursor_specification ...]
	[PROCEDURE [Schema..] procedure_name
		[ (parameter {IN,OUT,IN OUT} datatype [,parameter]) ]
	]
	[FUNCTION [Schema..] function_name
		[ (parameter {IN,OUT,IN OUT} datatype [,parameter]) ]
		RETURN return_datatype
	]
END [package_name];

PL/SQL Body : This contains the actual PL/SQL statement code implementing the logics of functions, procedures which are you already before declare in "Package specification".

Creating Package Body Syntax

CREATE [OR REPLACE] PACKAGE BODY package_name
    IS | AS
    [private_variable_declaration ...]
    [private_constant_declaration ...]
	BEGIN
		[initialization_statement]
		[PROCEDURE [Schema..] procedure_name
			[ (parameter [,parameter]) ]
			IS | AS
				variable declarations;
				constant declarations;					
			BEGIN
				statement(s);
			EXCEPTION
				WHEN ...
			END
		]
		[FUNCTION  [Schema..] function_name
				[ (parameter [,parameter]) ]
				RETURN return_datatype
			IS | AS
				variable declarations;
				constant declarations;			
			BEGIN
				statement(s);
			EXCEPTION
				WHEN ...
			END
		]
	[EXCEPTION 
		WHEN built-in_exception_name_1 THEN
			User defined statement (action) will be taken;
	]	
END;
/

PL/SQL Package Example

PL/SQL Package example step by step explain to you, you are create your own package using this reference example. We have emp1 table having employee information,

EMP_NO EMP_NAME EMP_DEPT EMP_SALARY
1 Forbs ross Web Developer 45k
2 marks jems Program Developer 38k
3 Saulin Program Developer 34k
4 Zenia Sroll Web Developer 42k

Package Specification Code

Create Package specification code for defining procedure, function IN or OUT parameter and execute package specification program.

CREATE or REPLACE PACKAGE pkg1
	IS | AS
	PROCEDURE pro1
		(no in number, name out varchar2);
	FUNCTION fun1
		(no in number)
		RETURN varchar2;
	END;					
/

Package Body Code

Create Package body code for implementing procedure or function that are defined package specification. Once you implement execute this program.

CREATE or REPLACE PACKAGE BODY pkg1
IS
	PROCEDURE pro1(no in number,info our varchar2)
		IS
		BEGIN
			SELECT * INTO temp FROM emp1 WHERE eno = no;
		END;
	
	FUNCTION fun1(no in number) return varchar2
		IS
		name varchar2(20);
		BEGIN
			SELECT ename INTO name FROM emp1 WHERE eno = no;
			RETURN name;
		END;
END;
/

Pl/SQL Program calling Package

Now we have a one package pkg1, to call package defined function, procedures also pass the parameter and get the return result.

pkg_prg.sql
DECLARE
	no number := &no;
	name varchar2(20);
BEGIN
	pkg1.pro1(no,info);
	dbms_output.put_line('Procedure Result');
	dbms_output.put_line(info.eno||'     '||
	                     info.ename||'   '||
	                     info.edept||'   '||
	                     info.esalary||' '||);
	dbms_output.put_line('Function Result');	                     
	name := pkg1.fun1(no);
	dbms_output.put_line(name);
END;	
/

PL/SQL Program Result

Now execute the above created pkg_prg.sql program to asking which user information you want to get, you put user id and give information.

SQL>@pkg_prg
no number &n=2
Procedure Result
2    marks jems    Program Developer    38K
Function Result
marks jems

PL/SQL procedure successfully completed.

PL/SQL Package Alter

You can update package code you just recompile the package body,

Package Alter Syntax :

ALTER PACKAGE package_name COMPILE BODY; 

Recompile the already created/executed package code,

Package Alter Code :

SQL>ALTER PACKAGE pkg1 COMPILE BODY;

Package body Altered.

PL/SQL Package Drop

You can drop package using package DROP statement,

Package Drop Syntax :

DROP PACKAGE package_name; 

Drop the pkg1 program that was we created,

Package Drop Code :

SQL>DROP PACKAGE pkg1;

Package dropped.