You are viewing a free preview of this lesson.
Subscribe to unlock all 10 lessons in this course and every other course on LearningBro.
A package is a schema object that groups related PL/SQL types, variables, procedures, and functions together. Packages are the primary way to organise and deploy PL/SQL code in production Oracle systems.
Every package has two separate components:
CREATE OR REPLACE PACKAGE package_name AS
-- Public declarations: visible to all callers
TYPE ...;
v_public_var datatype;
PROCEDURE public_proc(p1 IN datatype);
FUNCTION public_func(p1 IN datatype) RETURN datatype;
END package_name;
/
CREATE OR REPLACE PACKAGE BODY package_name AS
-- Private variables: only visible inside this body
v_private_var datatype;
PROCEDURE public_proc(p1 IN datatype) AS
BEGIN
-- implementation
END public_proc;
FUNCTION public_func(p1 IN datatype) RETURN datatype AS
BEGIN
-- implementation
END public_func;
END package_name;
/
-- Spec
CREATE OR REPLACE PACKAGE emp_mgmt AS
PROCEDURE hire (p_name IN VARCHAR2, p_salary IN NUMBER);
PROCEDURE fire (p_id IN NUMBER);
FUNCTION headcount RETURN NUMBER;
END emp_mgmt;
/
-- Body
CREATE OR REPLACE PACKAGE BODY emp_mgmt AS
PROCEDURE hire(p_name IN VARCHAR2, p_salary IN NUMBER) AS
BEGIN
INSERT INTO employees(name, salary) VALUES (p_name, p_salary);
COMMIT;
END hire;
PROCEDURE fire(p_id IN NUMBER) AS
BEGIN
DELETE FROM employees WHERE id = p_id;
COMMIT;
END fire;
FUNCTION headcount RETURN NUMBER AS
v_count NUMBER;
BEGIN
SELECT COUNT(*) INTO v_count FROM employees;
RETURN v_count;
END headcount;
END emp_mgmt;
/
BEGIN
emp_mgmt.hire('Bob', 60000);
END;
/
SELECT emp_mgmt.headcount FROM DUAL;
Use the package_name.subprogram_name dot notation.
Variables declared in the package spec or body persist for the lifetime of a session. Every call within the same session shares the same variable values:
CREATE OR REPLACE PACKAGE session_info AS
v_user VARCHAR2(100) := SYS_CONTEXT('USERENV', 'SESSION_USER');
END session_info;
/
| Benefit | Detail |
|---|---|
| Organisation | Group related code in one named unit |
| Encapsulation | Hide private helpers from callers |
| Performance | The entire package is loaded into memory on first use |
| Overloading | Multiple subprograms with the same name but different parameters |
| Global state | Package variables persist across calls in a session |
DROP PACKAGE package_name; -- drops spec and body
DROP PACKAGE BODY package_name; -- drops body only
Subscribe to continue reading
Get full access to this lesson and all 10 lessons in this course.