PLSQL==>PACKAGE

来源：互联网发布：免费linux服务器试用编辑：程序博客网时间：2024/06/05 17:27

1.使用包的好处：首先，良好设计的包是对象（如函数，过程，全局变量和游标等）的逻辑分组。当一次调用包时，所有的代码（分析树和伪码）被加载到内存（Oracle服务器的共享全局区域【SGA,shared global area】）这意味着，一次调用包的成本是非常高的（会涉及服务器的大量处理活动），但是随后调用的处理性能会比较高。因此，在应用程序中使用包，能够比较经济地反复使用过程和函数。

包规范：包含有关包的内容的相关信息，但是不包括过程和函数的代码，它包含对全局/公有变量的声明。PLSQL语句块的声明的任何东西都可以在包规范中编码。

包体：游标和模块头部已经他们在包规范中的定义，必须准确匹配的；不要在包体中重复包规范中变量，异常，类型或者常量的声明；包规范中声明的任何元素都可以在包体中引用。

实例1：

CREATE OR REPLACE PACKAGE manage_students AS
PROCEDURE find_sname(i_student_id IN student.student_id%TYPE,
o_first_name OUT student.first_name%TYPE,
o_last_name OUT student.last_name%TYPE);
FUNCTION id_is_good(id_student_id IN student.student_id%TYPE)
RETURN BOOLEAN;
END manage_students;

实例2：

CREATE OR REPLACE PACKAGE school_api AS
PROCEDURE discount;
FUNCTION new_instructor_id RETURN instructor.instructor_id%TYPE;
END school_api;

CREATE OR REPLACE PACKAGE BODY school_api AS
PROCEDURE discount IS
CURSOR c_group_discount IS
SELECT DISTINCT COUNT(*), s.course_no, c.description
FROM section s, enrollment e, course c
WHERE s.section_id = e.student_id
GROUP BY s.course_no,
c.description,
e.section_id,
s.section_id
HAVING COUNT(*) >= 8;
BEGIN
FOR r_group_discount IN c_group_discount
LOOP
UPDATE course
SET cost = cost * 0.95
WHERE course_no = r_group_discount.course_no;
dbms_output.put_line('A 5% discount has been to' ||
r_group_discount.course_no || ' ' ||
r_group_discount.description);
END LOOP;
END discount;
FUNCTION new_instructor_id RETURN instructor.instructor_id%TYPE IS
v_new_instid instructor.instructor_id%TYPE;
BEGIN
SELECT instructor_id_seq.nextval INTO v_new_instid FROM dual;
RETURN v_new_instid;
EXCEPTION
WHEN OTHERS THEN
DECLARE
v_sqlerrm VARCHAR2(250) := substr(SQLERRM, 1, 250);
BEGIN
raise_application_error(-20003,
'Error in instructor: ' ||
v_sqlerrm);
END;
END new_instructor_id;
END school_api;

创建脚本调用以上包：

DECLARE
v_instructor_id instructor.instructor_id%TYPE;
BEGIN
school_api.discount;
v_instructor_id := school_api.new_instructor_id;
dbms_output.put_line('The new id is: ' || v_instructor_id);
END;

创建包的私有对象：

CREATE OR REPLACE PACKAGE manage_students AS
PROCEDURE find_sname(i_student_id student.student_id%TYPE,
o_first_name OUT student.first_name%TYPE,
o_last_name OUT student.last_name%TYPE);
FUNCTION id_is_good(i_student_id IN student.student_id%TYPE) RETURN BOOLEAN;
PROCEDURE display_student_count;
END manage_students;

CREATE OR REPLACE PACKAGE BODY manage_students AS
PROCEDURE find_sname(i_student_id IN student.student_id%TYPE,
o_first_name OUT student.first_name%TYPE,
o_last_name OUT student.last_name%TYPE) IS
v_student_id student.student_id%TYPE;
BEGIN
SELECT first_name, last_name
INTO o_first_name, o_last_name
FROM student
WHERE student_id = i_student_id;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Error is finding student_id: ' ||
v_student_id);
END find_sname;
FUNCTION id_is_good(i_student_id IN student.student_id%TYPE) RETURN BOOLEAN IS
v_id_cnt NUMBER;
BEGIN
SELECT COUNT(*)
INTO v_id_cnt
FROM student
WHERE student_id = i_student_id;
RETURN v_id_cnt = 1;
EXCEPTION
WHEN OTHERS THEN
RETURN FALSE;
END id_is_good;
FUNCTION student_count_priv RETURN NUMBER IS --**
v_count NUMBER;
BEGIN
SELECT COUNT(*) INTO v_count FROM student;
EXCEPTION
WHEN OTHERS THEN
RETURN(0);
END student_count_priv;
PROCEDURE display_student_count IS
v_count NUMBER;
BEGIN
v_count := student_count_priv;
dbms_output.put_line('There are ' || v_count || ' student.');
END display_student_count;
FUNCTION get_course_description_private(i_course_no course.course_no%TYPE) --***
RETURN course.description%TYPE IS
v_course_descript course.description%TYPE;
BEGIN
SELECT description
INTO v_course_descript
FROM course
WHERE course_no = i_course_no;
RETURN v_course_descript;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END get_course_description_private;
END manage_students;

游标变量：

CREATE OR REPLACE PACKAGE course_pkg AS
TYPE course_rec_typ IS RECORD(
first_name student.first_name%TYPE,
last_name student.last_name%TYPE,
course_no course.course_no%TYPE,
description course.description%TYPE,
section_no section.section_no%TYPE);
TYPE course_cur IS REF CURSOR RETURN course_rec_typ;
PROCEDURE get_course_list(p_student_id NUMBER,
p_instructor_id NUMBER,
course_list_cv IN OUT course_cur);
END course_pkg;

CREATE OR REPLACE PACKAGE BODY course_pkg AS
PROCEDURE get_course_list(p_student_id NUMBER,
p_instructor_id NUMBER,
course_list_cv IN OUT course_cur) IS
BEGIN
IF p_student_id IS NULL
AND p_instructor_id IS NULL
THEN
OPEN course_list_cv FOR
SELECT 'Please choose a student-' first_name,
'instructor combination' last_name,
NULL course_no,
NULL description,
NULL section_no
FROM dual;
ELSIF p_student_id IS NULL
THEN
OPEN course_list_cv FOR
SELECT s.first_name first_name,
s.last_name last_name,
c.course_no course_no,
c.description description,
se.section_no section_no
FROM instructor i,
student s,
section se,
course c,
enrollment e
WHERE i.instructor_id = p_instructor_id
AND i.instructor_id = se.instructor_id
AND se.course_no = c.course_no
AND e.student_id = s.student_id
AND e.section_id = se.section_id
ORDER BY c.course_no, se.section_no;
ELSIF p_instructor_id IS NULL
THEN

OPEN course_list_cv FOR
SELECT i.first_name first_name,
i.last_name last_name,
c.course_no course_no,
c.description description,
se.section_no section_no
FROM instructor i,
student s,
section se,
course c,
enrollment e
WHERE s.student_id = p_student_id
AND i.instructor_id = se.instructor_id
AND se.course_no = c.course_no
AND e.student_id = s.student_id
AND e.section_id = se.section_id
ORDER BY c.course_no, se.section_no;
END IF;
END get_course_list;
END course_pkg;

只传学生ID，不传教师ID：

SQL> exec course_pkg.get_course_list(102,null,:course_cv);

PL/SQL procedure successfully completed.

SQL> print course_cv;

FIRST_NAME LAST_NAME COURSE_NO DESCRIPTION SECTION_NO
---------- ---------- --------- -------------------- ----------
Charles Lowry 25 Intro to Programming 2
Nina Schorin 25 Intro to Programming 5

只传教师ID，不传学生ID：

SQL> exec course_pkg.get_course_list(null,102,:course_cv);
PL/SQL procedure successfully completed.
SQL> print course_cv;

FIRST_NAME LAST_NAME COURSE_NO DESCRIPTION SECTION_NO
---------- ---------- --------- ------------------------------ ----------
Jeff Runyan 10 Technology Concepts 2
Dawn Dennis 25 Intro to Programming 4
Alfred Hutheesing 25 Intro to Programming4
Arun Griffen 25 Intro to Programming4
Jim Joas 25 Intro to Programming4
May Jodoin 25 Intro to Programming 4
Lula Oates 100 Hands-On Windows 1
Regina Bose 100 Hands-On Windows 1
Roger Snow 100 Hands-On Windows1
Rommel Frost 100 Hands-On Windows1
Jenny Goldsmith 100 Hands-On Windows1
Debra Boyce 100 Hands-On Windows 1
Janet Jung 120 Intro to Java Programming4
Evan Fielding 124 Advanced Java Programming1
Ronald Tangaribua 124 Advanced Java Programming1
Charles Caro 124 Advanced Java Programming1
Sharon Thompson 124 Advanced Java Programming1
John Smith 124 Advanced Java Programming 1
N Kuehn 146 Java for C/C++ Programmers 2
Derrick Baltazar 146 Java for C/C++ Programmers2
Angela Torres 240 Intro to the BASIC Language2

21 rows selected.

实例4：

CREATE OR REPLACE PACKAGE student_info_pkg AS
TYPE student_details IS REF CURSOR;
PROCEDURE get_student_info(p_student_id NUMBER,
p_choice NUMBER,
details_cv IN OUT student_details);
END student_info_pkg;

CREATE OR REPLACE PACKAGE BODY student_info_pkg AS
PROCEDURE get_student_info(p_student_id NUMBER,
p_choice NUMBER,
details_cv IN OUT student_details) IS
BEGIN
IF p_choice = 1
THEN
OPEN details_cv FOR
SELECT s.first_name first_name,
s.last_name last_name,
s.street_address street_address,
z.city city,
z.state state,
z.zip zip
FROM student s, zipcode z
WHERE s.student_id = p_student_id
AND z.zip = s.zip;
ELSIF p_choice = 2
THEN
OPEN details_cv FOR
SELECT c.course_no course_no,
c.description description,
se.section_no section_no,
s.first_name first_name,
s.last_name last_name
FROM student s, section se, course c, enrollment e
WHERE se.course_no = c.course_no
AND e.student_id = s.student_id
AND e.section_id = se.section_id
AND se.section_id IN
(SELECT e.section_id
FROM student s, enrollment e
WHERE s.student_id = p_student_id
AND s.student_id = e.student_id)
ORDER BY c.course_no;
ELSIF p_choice = 3
THEN
OPEN details_cv FOR
SELECT i.first_name first_name,
i.last_name last_name,
c.course_no course_no,
c.description description,
se.section_no section_no
FROM instructor i,
student s,
section se,
course c,
enrollment e
WHERE s.student_id = p_student_id
AND i.instructor_id = se.instructor_id
AND se.course_no = c.course_no
AND e.student_id = s.student_id
AND e.section_id = se.section_id
ORDER BY c.course_no, se.section_no;
END IF;
END get_student_info;
END student_info_pkg;

测试以上过程：

SQL> execute student_info_pkg.get_student_info(102,1,:student_cv);

PL/SQL procedure successfully completed.

SQL> print student_cv;

FIRST_NAME LAST_NAME STREET_ADDRESS CITY ST ZIP
--------------- ------------------------- ---------------------------------------- ------------------------- -- -----
Fred Crocitto 101-09 120th St. Richmond Hill NY 11419

SQL> execute student_info_pkg.get_student_info(102,2,:student_cv);

PL/SQL procedure successfully completed.

SQL> print student_cv;

COURSE_NO DESCRIPTION SECTION_NO FIRST_NAME LAST_NAME
--------- ------------------------------ ---------- --------------- -------------------------
25 Intro to Programming 2 Fred Crocitto
25 Intro to Programming 2 Judy Sethi
25 Intro to Programming 2 Jenny Goldsmith
25 Intro to Programming 2 Barbara Robichaud
25 Intro to Programming 2 Jeffrey Citron
25 Intro to Programming 2 George Kocka
25 Intro to Programming 5 Fred Crocitto
25 Intro to Programming 5 Hazel Lasseter
25 Intro to Programming 5 James Miller
25 Intro to Programming 5 Regina Gates
25 Intro to Programming 5 Arlyne Sheppard
25 Intro to Programming 5 Thomas Edwards
25 Intro to Programming 5 Sylvia Perrin
25 Intro to Programming 5 M. Diokno
25 Intro to Programming 5 Edgar Moffat
25 Intro to Programming 5 Bessie Heedles
25 Intro to Programming 5 Walter Boremmann
25 Intro to Programming 5 Lorrane Velasco
18 rows selected.

SQL> execute student_info_pkg.get_student_info(214,3,:student_cv);

PL/SQL procedure successfully completed.

SQL> print student_cv;

FIRST_NAME LAST_NAME COURSE_NO DESCRIPTION SECTION_NO
--------------- ------------------------- --------- ------------------------------ ----------
Marilyn Frantzen120 Intro to Java Programming 1
Fernand Hanks 122 Intermediate Java Programming 5
Gary Pertez 130 Intro to Unix 2
Marilyn Frantzen145 Internet Protocols 1

0 0