To the point guide to PL/SQL

Packages in PL/SQL are objects of schema and group of related data types such as variables, constants, procedures, cursors, functions.

Package parts are:

• Package Specification
• Package Body

Package Specification

The specification is the interface, where it contains the list of constants, variables, procedures, function, exceptions names are the part of the package. The specification objects are called public objects. Any function/subprograms not in the specification of package but coded in the body of package is called a private object.

Example:

CREATE OR REPLACE PACKAGE student AS
  -- get student's fullname
  FUNCTION get_fullname(n_stud_id NUMBER)
    RETURN VARCHAR2;
  END student;

Result:

Package created. 

Package Body

The package body of PL/SQL has all the code for procedures, functions declared in the specification of package and other declarations which are hidden from outside the package.

The package body is creating by using CREATE PACKAGE BODY Statement

Example:

CREATE or REPLACE PACKAGE BODY pack1
IS
	PROCEDURE procExp1(id in number, name our varchar2)
	IS
	BEGIN
	SELECT * INTO temp FROM Stud_Details WHERE Stud_id = id;
	END;
	
	FUNCTION funcExp(s_id in number) return varchar2
	IS
	Stud_name varchar2(20);
	BEGIN
	SELECT sname INTO Stud_name FROM Stud_Details  WHERE Stud_id = s_id;
		RETURN name;
		END;
END;
/

Result:

Package body created.

Using the Package Elements

By using dot notation user can reference to package elements:

Syntax:

package_name.package_element

Example:

DECLARE
  Stud_marks NUMBER;
  sname   VARCHAR2(30);

BEGIN
   s_name   := pack1.get_fullname(n_stud_id);
  s_marks := pack1.get_marks(n_stud_id);
 
  IF s_name  IS NOT NULL AND
    s_marks IS NOT NULL
  THEN
    dbms_output.put_line('Student: ' || s_name);
    dbms_output.put_line('Marks:' || s_marks);
  END IF;
END;
 /

Program calling Package:

From one package pack1, to call package defined procedure, function by passing parameters and get the return result.

Example:

DECLARE
	id number := &no;
	sname varchar2(20);
BEGIN
	pkg1.proc1(num,details);
	dbms_output.put_line('Result of Procedure');
	dbms_output.put_line(details.id||'     '||
	                     details.sname||'   '||
	                     details.marks||'   '||
	                     
	dbms_output.put_line('Result of Function');	                     
	fname := packg1.func1(no);
	dbms_output.put_line('Student name: 'fname);
END;	
/

Result:

id number &n=2
Result of Procedure
1    David     38K
Result of Function 
Student name: David

PL/SQL procedure successfully completed.

In next part of this guide, we will take a look on what Triggers are and how to use them with examples.