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 interface that 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 cursor queries and the subprograms’ code.
Note: All objects in the package body or definition are 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;
/
