|
Create a Packages
Hands-On
introduction
In this
Hands-On, you create a package by copy and
pasting an existing PL/SQL stored procedure.
Then save it in the local library or the
database server.
A package is a
database construct that allows users to
collect many program units into one database
object. They consist of two components: a
package specification and a package body.
Create a
Package Specification
Introduction
A package
should have a PL/SQL package specification
and a PL/SQL package body. A ‘PL/SQL package
specification’ contains all your PL/SQL
functions header, procedures header, type,
variables, etc. A ‘PL/SQL package body’
contains a complete PL/SQL stored procedures
or functions. All the declared PL/SQL
functions, procedures, variables, etc in a
package specification are called public
procedures and functions. They can be
accessible to the users who have privilege
to execute them. In the PL/SQL package
specification, all the functions and
procedures must have a PL/SQL procedure in
its PL/SQL package body. It is not necessary
that all the PL/SQL procedures in a PL/SQL
package body have a specification entry in
its PL/SQL package specification. Those
PL/SQL procedures that have not have any
specification entry in the PL/SQL package
specification called private PL/SQL
procedures.
Select
"Program Units" and then click on the
“Create” icon to create a package that
contains all of department's functions and
procedures.
Name the
package (package department) and select
package specification. Then click "OK"
Write or “copy
and paste” all functions’ and procedures’
header for public use.
Open the
add_dept procedure and copy it header
specification into the package
specification. Do not forget to add the ";"
Repeat this
process for any other stored procedures, if
needed.
(Procedure
Builder)
PACKAGE
pkg_dept
IS
-- No
variables
-- This is the
add_dept specification…
PROCEDURE
add_dept
(p_dept_rec IN
dept%ROWTYPE,
p_status OUT
VARCHAR2);
-- This is the
remove_dept specification…
PROCEDURE
remove_dept
(p_deptno IN
dept.depno%TYPE,
p_status OUT
VARCHAR2);
-- Add more
and more…
END pkg_dept;
/
Add more
headers if needed. These two programs are
going to be enough to make the point.
Compile a
package specification
Compile the
package specification. No strike means the
compilation was successful.
Create a
package body
Create a
package body. Name the package body the same
as its package specification name. Select
“Package Body.” Then click “OK.”
Copy and paste
the entire stored procedures into the
package body. Repeat the copy process for
other stored procedures if needed.
(Procedure
Builder)
PACKAGE BODY
pkg_dept
IS
-- Add
department procedure…
PROCEDURE
add_dept
(p_deptno IN
dept.deptno%TYPE,
p_dname IN
dept.dname%TYPE,
p_loc IN
dept.loc%TYPE,
p_status OUT
VARCHAR2)
IS
-- No variable
BEGIN
/* This
program add dept. record. */
INSERT INTO
dept
VALUES
(p_deptno, p_dname, p_loc);
--- Save
record.
COMMIT;
-- Added
successfully if the get to this line.
p_status :=
‘OK’;
EXCEPTION
-- Check for
an Unique or Primary Key
WHEN
dup_val_on_index THEN
p_status :=
‘DUPLICATE RECORD’;
-- Check for
invalid input data
WHEN
invalid_number THEN
p_status :=
‘INVALID INPUT DATA’;
-- Check for
any other problems
WHEN others
THEN
p_status :=
‘CHECK THIS WE HAVE UNKNOWN PROBLEM.’;
END add_dept;
-- Remove
department procedure…
PROCEDURE
remove_dept
(p_deptno IN
dept.deptno%TYPE,
p_status OUT
VARCHAR2)
IS
-- Delete a
record
DELETE FROM
dept
WHERE deptno =
p_deptno;
-- Save the
transaction.
COMMIT;
-- Check the
status.
p_status :=
‘OK’;
EXCEPTION
WHEN
no_data_found THEN
p_status :=
‘NO DATA FOUND.’;
WHEN others
THEN
p_status :=
‘Other Problems.’;
END
remove_dept;
-- And more
internal procedures.
END pkg_dept;
/
Compile a
package body
Compile the
package body. No strike means: successfully
compiled.
Make a syntax
error and compile again. Read the error
messages. Then correct the error and compile
it again.
Then close the
window.
Run and test a
procedure or function in a package
Query the
department table.
PL/SQL> SELECT * FROM dept;
Write a PL/SQL
procedure block to use the remove procedure
to delete the department number 40.
PL/SQL>
DECLARE
v_status
VARCHAR2(40);
BEGIN
pkg_dept.remove_dept(40, v_status);
TEXT_IO.PUT_LINE(v_status);
END;
Then output
the status parameter to see that the
transaction was successfully deleted.
“OK” means: it
was successfully deleted.
Query the
department table again.
PL/SQL> SELECT * FROM dept;
Record was
deleted.
Save a package
specification and body into the database
server
Store the
package into the database server.
First store
the "package specification" then store the
"package body" unit.
Questions:
Q: What is the
PL/SQL package?
Q: What are
the components of a PL/SQL package?
Q: What is a
package body in the PL/SQL language?
Q: What is a
package specification in the PL/SQL
language?
Q: Where do
you save the package body and its package
specification?
Q: Can you
store a PL/SQL package in a client
environment?
Q: How do you
create a package specification and body?
Q: What are
the dependencies between a package body and
its package specification?
Q: Write a
PL/SQL package to have all your created
PL/SQL functions and procedures?
Q: What is a
public PL/SQL procedure or function in a
PL/SQL package?
Q: What is a
private PL/SQL procedure or function in a
PL/SQL package?
Q: What are
the differences between a public or private
PL/SQL procedure?
Q: How do you
run a PL/SQL procedure or function in a
PL/SQL package? |
