Question: How do I create a PL/SQL package body in Oracle? I
understand that if I put my PL/SQL procedures into a package, then I can pin
them into RAM with
dbms_shared_pool.keep, so I need the create package
syntax please.
Answer: First, there are many benefits to
encapsulating PL/SQL functions and stored procedures into packages:
-
Better PL/SQL performance. Oracle stored procedures load once into
the shared pool and remain there unless they become paged out.
-
Coupling of data with methods. DBAs can use naming conventions with
Oracle packages to couple tables with the PL/SQL associated with a table,
essentially using Oracle stored procedures as "methods".
-
Isolation of PL/SQL code. Since all PL/SQL anonymous blocks are moved
out of the external programs and into the Oracle stored procedures, the
application programs become nothing more than calls to stored procedures. As
such, it becomes very simple to swap out one database and swap in another
one.
One of the foremost reasons PL/SQL packages function faster than traditional
code is related to caching in the Oracle SGA. Once loaded into the RAM memory of
the shared pool, PL/SQL packages will execute very quickly.
The package body includes the definition of procedures and functions declared
in the package spec and, like the package spec, can also include variables,
constants, user-defined exceptions, and user-defined datatypes.
Unlike objects created in a PL/SQL package specification, variables and other
constructs defined within a package body are private to the package body. These
constructs can only be referenced by procedures and functions within the package
body. Stored procedures are created inside a package without using the
CREATE PROCEDURE command. Instead, a procedure’s definition is defined as part
of the CREATE PACKAGE BODY command.
Here is an example of the Oracle create package body command from the docs:
CREATE OR REPLACE PACKAGE BODY emp_mgmt AS
tot_emps NUMBER;
tot_depts NUMBER;
FUNCTION hire
. .
RETURN(new_empno);
END;
FUNCTION
create_dept(department_id NUMBER, location_id NUMBER)
RETURN NUMBER IS
new_deptno NUMBER;
BEGIN
SELECT . . .
END;
PROCEDURE
remove_emp (employee_id NUMBER) IS
BEGIN
DELETE . . .
END;
PROCEDURE
remove_dept(department_id NUMBER) IS
BEGIN
DELETE FROM . .
END;
PROCEDURE
increase_sal(employee_id NUMBER, salary_incr NUMBER) IS
curr_sal NUMBER;
BEGIN
SELECT . .
END;
PROCEDURE
increase_comm(employee_id NUMBER, comm_incr NUMBER) IS
curr_comm NUMBER;
BEGIN
SELECT . .
END;
END
emp_mgmt;
/
The DBA will be required to alter a package when there are changes to tables,
views, sequences, and so on that the package procedures and functions reference.
This is accomplished through the use of the CREATE OR REPLACE PACKAGE [BODY]
form of the CREATE PACKAGE command.
