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.
create or replace package body
test_pack
as
function
plus_tax(v_msrp in number) return number
as
v_total number;
begin
v_total := v_msrp + (v_msrp * .07);
return v_total;
end;
procedure
new_job
(v_job_id
in number,
v_job_desc in character,
v_min_lvl in number,
v_max_lvl in number)
as
BEGIN
insert into jobs values (v_job_id, v_job_desc,v_min_lvl,v_max_lvl);
EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20001,'An error -
'||SQLCODE||' -ERROR- '||SQLERRM);
END;
end;
/
 |
|
Get the Complete
Oracle SQL Tuning Information
The landmark book
"Advanced Oracle
SQL Tuning The Definitive Reference" is
filled with valuable information on Oracle SQL Tuning.
This book includes scripts and tools to hypercharge Oracle 11g
performance and you can
buy it
for 30% off directly from the publisher.
|
and include the URL for the page.
