package plsql

Oracle pl sql package:

A package is a schema object that groups logically related PL/SQL types, variables and subprograms.

Parts of a package:

1. Package specification 2. Package body or definition

Package specification:

The package specification is the package interface which declares the types, variables, constants, exceptions, cursors and subprograms that can be referenced from outside the package. Note: All objects in the package specification are known as public objects.

Syntax of package specification:

CREATE PACKAGE package_name AS
   PROCEDURE procedure_name;
END cust_sal;
/

Example:

CREATE PACKAGE emp_sal AS
   PROCEDURE find_sal(e_id employees.id%type);
END emp_sal;
/

Package body or definition:

The package body or definition defines the queries for the cursors and the code for the subprograms. Note: All objects in the package body or definition are known as private objects.

Syntax of body or definition:

CREATE OR REPLACE PACKAGE BODY package_name AS
   PROCEDURE procedure_name IS
   	//procedure body
   END procedure_name;
END package_name;
/

Example:

CREATE OR REPLACE PACKAGE BODY emp_sal AS
   PROCEDURE find_sal(e_id employees.id%TYPE) IS
   e_sal employees.salary%TYPE;
   BEGIN
      SELECT salary INTO e_sal
      FROM employees
      WHERE id = e_id;
      dbms_output.put_line('Salary: '|| e_sal);
   END find_sal;
END emp_sal;
/

Please follow and like us: