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;
/