|
"A person reveals
his character by nothing so clearly as the joke he resents."
Georg Christoph Lichtenberg (1742 - 1799) |
Read
first then play the video:
PLS-VIDEO -Creating
PL/SQL Package
Section 4: Package
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.
|
"Without friends no
one would choose to live, though he had all other goods."
Aristotle (384 BC - 322 BC), Nichomachean Ethics |
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?
|