PLSQL

PL/SQL Packages

PL/SQL Packages

These are the schema objects that groups are logically related PL/SQL types, variables, and subprograms. They have two mandatory parts:

  1. Package specification
  2. Package body or definition
     

Package Specification

This is the interface to the package. This only declares the types, variables, constants, exceptions, cursors, and subprograms that are to be referenced from outside the package. We can also say that, it is said to contain all the information about the content of the package, but excludes the code for the subprograms. All the objects are placed in the specification are said to be called public objects. Any of the subprogram not in the package specification but then they are coded in the package body is called a private object.

CREATE PACKAGE teachers_sal AS
                 PROCEDURE find_sal(t_id teachers.id%type);
END teachers_sal;
/

When we execute the above code is executed at the SQL prompt.

Package created.

PACKAGE BODY

This code as for various methods that are declared in the package specification and also other private declarations, that are hidden from the code outside the package. The statement CREATE PACKAGE BODY is used for creating the package body. 

Example:
CREATE OR REPLACE PACKAGE BODY teachers_sal AS  
   
   PROCEDURE find_sal(t_id teachers.id%TYPE) IS 
   t_sal teachers.salary%TYPE; 
   BEGIN 
      SELECT salary INTO t_sal 
      FROM teachers 
      WHERE id = t_id; 
      dbms_output.put_line('Salary: '|| c_sal); 
   END find_sal; 
END teachers_sal; 
/
Output:
Package body created.

Using the package elements

The package elements like variables, procedures or functions are accessed by the syntax that is given below:

package_name.element_name;
Example:
DECLARE 
   code teachers.id%type := &tt_id; 
BEGIN 
   teachers_sal.find_sal(code); 
END; 
/
Output:
Enter value for tt_id: 2 
Salary: 3000 

PL/SQL procedure successfully completed.