|
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? |
