第十章：函数&包
来源：互联网 发布：php调用网页代码 编辑：程序博客网 时间：2024/05/30 21:58
--函数的创建和使用
--函数像过程一样，也存储在数据库中。
--两者之间的重要区别是，函数是能够返回单个值
--的PL/SQL语句块。
--创建函数的语法如下所示:
--CREATE [OR REPLACE]FUNCTION function_name(parameter list)
RETURN datatype
{ IS | AS }
[ declare_section ]
BEGIN
<body>
RETURN (return_value);
END;
--第一个函数
create or replace function show_description(i_course_no course.course_no%type)
return varchar2
is
v_description varchar2(30);
begin
--找到指定课程的名称，存到变量中
select description
into v_description
from course
where course_no=i_course_no;
return v_description;--返回课程名称
end show_description;
--函数调用：
--1）在plsql块或者存储过程中调用
--PLS-00221:'SHOW_DESCRIPTION' is not aprocedure or is undefined
begin
show_description(430);
end;
--在plsql中调用函数，要求一定要对函数的返回值做处理。
begin
dbms_output.put_line(show_description(430));
end;
--或者：
declare
v_description varchar2(30);
begin
v_description := show_description(430);
dbms_output.put_line(v_description);
end;
--2)使用select语句调用。有很多限制
select show_description(430)from dual;
--例2：检查给定的学生编号是否合法
create or replace function id_is_good(i_student_idnumber)
return boolean
is
v_id_cnt number(1);
begin
select count(*)
into v_id_cnt
from student
where student_id=i_student_id;
return (1=v_id_cnt);
exception
when others then
return false;
end id_is_good;
--使用select语句调用以上函数
--ORA-06553: PLS-382: expressionis of wrong type
select id_is_good(100)from dual;
--因为函数的返回类型是boolean型，sql不支持
begin
if id_is_good(100) then
dbms_output.put_line('good');
else
dbms_output.put_line('bad');
end if;
end;
--创建和使用包。非常重要。
--可以把包看做一个容器，里面存放过程、函数、变量、类型
--等东西。使用多个包，就可以将过程和函数进行分类存放。
--创建一个包分两个部分：
--1）包规范（包接口）：其中包含过程和函数的头部声明，
--但是不包含他们的具体实现。包规范中的所有对象都叫做
--公共对象，意味着他们可以被包之外的代码访问。
--2）包体：其中包含过程和函数的头部声明和具体实现。
--也可以包含私有对象（过程、函数、变量等）的声明。
--创建学生管理包
create or replace package manage_students is
-- Author : ADMINISTRATOR
-- Created :2014-06-309:57:43
-- Purpose : 学生管理包
procedure find_sname(i_student_idin number,
o_first_name out varchar2,
o_last_name out varchar2);
function id_is_good(i_student_idnumber) return boolean;
end manage_students;
--当包规范创建并编译完毕后，客户端已经可以访问了。
declare
o_first_name varchar2(20);
o_last_name varchar2(20);
begin
if manage_students.id_is_good(102) then
manage_students.find_sname(102,o_first_name,o_last_name );
end if;
end;
--执行以上代码，会抛出异常：
ORA-04067: not executed, package body "STUDENT.MANAGE_STUDENTS" doesnot exist
ORA-06508: PL/SQL: couldnot find program unit being called: "STUDENT.MANAGE_STUDENTS"
--这是正常的，因为过程和函数还没有具体实现，当然
--不能执行。
--实现包体
create or replace package body manage_students is
--过程实现
procedure find_sname(i_student_idin number,
o_first_name out varchar2,
o_last_name out varchar2)is
begin
select first_name, last_name
into o_first_name, o_last_name
from student
where student_id = i_student_id;
exception
when no_data_found then
DBMS_OUTPUT.PUT_LINE('Error in finding student_id: ' || i_student_id);
end find_sname;
--函数实现
function id_is_good(i_student_idnumber) return booleanis
v_id_cnt number(1);
begin
select count(*)
into v_id_cnt
from student
where student_id = i_student_id;
return(1= v_id_cnt);
exception
when others then
return false;
end id_is_good;
end manage_students;
--再执行以下代码，成功
declare
o_first_name varchar2(20);
o_last_name varchar2(20);
begin
if manage_students.id_is_good(102) then
manage_students.find_sname(102,o_first_name,o_last_name );
end if;
end;
--创建私有对象
--如果只在包体中定义某对象，则它是私有的。
--该包外部的任何程序不能直接访问私有元素
--在manage_students包规范定义中添加一个新的过程的声明（如下），并重新编译包规范
PROCEDURE display_student_count;
END manage_students;
--
create or replace package manage_students is
-- Author : ADMINISTRATOR
-- Created :2014-06-309:57:43
-- Purpose : 学生管理包
procedure find_sname(i_student_idin number,
o_first_name out varchar2,
o_last_name out varchar2);
function id_is_good(i_student_idnumber) return boolean;
procedure display_student_count;
end manage_students;
--包体：
create or replace package body manage_students is
--过程实现
procedure find_sname(i_student_idin number,
o_first_name out varchar2,
o_last_name out varchar2)is
begin
select first_name, last_name
into o_first_name, o_last_name
from student
where student_id = i_student_id;
exception
when no_data_found then
DBMS_OUTPUT.PUT_LINE('Error in finding student_id: ' || i_student_id);
end find_sname;
--函数实现
function id_is_good(i_student_idnumber) return booleanis
v_id_cnt number(1);
begin
select count(*)
into v_id_cnt
from student
where student_id = i_student_id;
return(1= v_id_cnt);
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;
return v_count;
end student_count_priv;
procedure display_student_count
is
begin
--调用私有函数
dbms_output.put_line(student_count_priv);
end display_student_count;
end manage_students;
--测试
begin
manage_students.display_student_count;
end;
--PLS-00302: component'STUDENT_COUNT_PRIV' must be declared
begin
dbms_output.put_line(manage_students.student_count_priv);
end;
--使用包变量
--将变量声明放在包规范中，那么该变量就是一个全局变量。
--可以被任何代码来访问。可以实现
--同一个会话的多个过程之间交换（共享）数据。
--包的初始化部分
--在包体的末尾。当一个用户会话第1次调用一个包时，
--该包的初始化部分的代码就会执行。只执行这一次
--给manage_students包添加包变量
create or replace package manage_students is
-- Author : ADMINISTRATOR
-- Created :2014-06-309:57:43
-- Purpose : 学生管理包
--包变量
v_current_date date;
procedure find_sname(i_student_idin number,
o_first_name out varchar2,
o_last_name out varchar2);
function id_is_good(i_student_idnumber) return boolean;
procedure display_student_count;
end manage_students;
--在包体的初始化部分给包变量赋值
create or replace package body manage_students is
--过程实现
procedure find_sname(i_student_idin number,
o_first_name out varchar2,
o_last_name out varchar2)is
begin
select first_name, last_name
into o_first_name, o_last_name
from student
where student_id = i_student_id;
exception
when no_data_found then
DBMS_OUTPUT.PUT_LINE('Error in finding student_id: ' || i_student_id);
end find_sname;
--函数实现
function id_is_good(i_student_idnumber) return booleanis
v_id_cnt number(1);
begin
select count(*)
into v_id_cnt
from student
where student_id = i_student_id;
return(1= v_id_cnt);
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;
return v_count;
end student_count_priv;
procedure display_student_count
is
begin
dbms_output.put_line(student_count_priv);
end display_student_count;
begin --这是包的初始化部分，对每个会话只执行一次
select sysdate
into v_current_date
from dual;
dbms_output.put_line('包初始化完毕');
end manage_students;
--测试：新开会话窗口，分别执行以下代码2次。观察输出。
begin
dbms_output.put_line(manage_students.v_current_date);
end;
--在包中使用游标变量。很重要
--游标变量就是一个refcursor类型的变量。
--定义refcursor类型的语法：
--TYPE ref_type_name IS REF CURSOR
[RETURN 记录类型];
--如果定义refcursor类型时带了return子句，那么
--该类型的游标变量就叫做强类型的游标变量。如果
--没有带return子句，那么该类型的游标变量就叫做
--弱类型的游标变量
--弱类型的游标变量可以指向任何一个select查询的
--结果集。而强类型的游标变量可以指向的查询，要求
--其select列表必须满足记录类型的内部结构。
--游标变量在包中主要用作过程的输出参数，用来从
--服务器向客户端返回一个结果集。
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 ISREF CURSOR RETURN course_rec_typ;
PROCEDURE get_course_list
(p_student_id NUMBER ,
p_instructor_id NUMBER ,
course_list_cv INOUT 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 ISNULL AND p_instructor_id ISNULL 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 ISNULL 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, coursec, 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 ISNULL 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, coursec, 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;
--在sqlplus中使用游标变量调用过程
--sqlplus中的variable命令专门用来定义绑定变量，
--其类型可以是游标变量。
variable course_cv refcursor;
--注意，以上语句命令窗口不支持
--调用过程
execute course_pkg.get_course_list(102,null,:course_cv);
--打印绑定变量的值。可以看到结果集输出
print :course_cv;
--注意，print命令会自动关闭游标变量。再次执行，出错：
exec course_pkg.get_course_list(NULL,102, :course_cv);
print :course_cv;
--如果过程的参数有一个弱类型的游标变量，那么
--在包中可以不写type ...isref cursor语句，而是
--直接使用系统预先定义的一个refcursor类型，其
--名字是sys_refcursor.
--函数像过程一样，也存储在数据库中。
--两者之间的重要区别是，函数是能够返回单个值
--的PL/SQL语句块。
--创建函数的语法如下所示:
--CREATE [OR REPLACE]FUNCTION function_name(parameter list)
RETURN datatype
{ IS | AS }
[ declare_section ]
BEGIN
<body>
RETURN (return_value);
END;
--第一个函数
create or replace function show_description(i_course_no course.course_no%type)
return varchar2
is
v_description varchar2(30);
begin
--找到指定课程的名称，存到变量中
select description
into v_description
from course
where course_no=i_course_no;
return v_description;--返回课程名称
end show_description;
--函数调用：
--1）在plsql块或者存储过程中调用
--PLS-00221:'SHOW_DESCRIPTION' is not aprocedure or is undefined
begin
show_description(430);
end;
--在plsql中调用函数，要求一定要对函数的返回值做处理。
begin
dbms_output.put_line(show_description(430));
end;
--或者：
declare
v_description varchar2(30);
begin
v_description := show_description(430);
dbms_output.put_line(v_description);
end;
--2)使用select语句调用。有很多限制
select show_description(430)from dual;
--例2：检查给定的学生编号是否合法
create or replace function id_is_good(i_student_idnumber)
return boolean
is
v_id_cnt number(1);
begin
select count(*)
into v_id_cnt
from student
where student_id=i_student_id;
return (1=v_id_cnt);
exception
when others then
return false;
end id_is_good;
--使用select语句调用以上函数
--ORA-06553: PLS-382: expressionis of wrong type
select id_is_good(100)from dual;
--因为函数的返回类型是boolean型，sql不支持
begin
if id_is_good(100) then
dbms_output.put_line('good');
else
dbms_output.put_line('bad');
end if;
end;
--创建和使用包。非常重要。
--可以把包看做一个容器，里面存放过程、函数、变量、类型
--等东西。使用多个包，就可以将过程和函数进行分类存放。
--创建一个包分两个部分：
--1）包规范（包接口）：其中包含过程和函数的头部声明，
--但是不包含他们的具体实现。包规范中的所有对象都叫做
--公共对象，意味着他们可以被包之外的代码访问。
--2）包体：其中包含过程和函数的头部声明和具体实现。
--也可以包含私有对象（过程、函数、变量等）的声明。
--创建学生管理包
create or replace package manage_students is
-- Author : ADMINISTRATOR
-- Created :2014-06-309:57:43
-- Purpose : 学生管理包
procedure find_sname(i_student_idin number,
o_first_name out varchar2,
o_last_name out varchar2);
function id_is_good(i_student_idnumber) return boolean;
end manage_students;
--当包规范创建并编译完毕后，客户端已经可以访问了。
declare
o_first_name varchar2(20);
o_last_name varchar2(20);
begin
if manage_students.id_is_good(102) then
manage_students.find_sname(102,o_first_name,o_last_name );
end if;
end;
--执行以上代码，会抛出异常：
ORA-04067: not executed, package body "STUDENT.MANAGE_STUDENTS" doesnot exist
ORA-06508: PL/SQL: couldnot find program unit being called: "STUDENT.MANAGE_STUDENTS"
--这是正常的，因为过程和函数还没有具体实现，当然
--不能执行。
--实现包体
create or replace package body manage_students is
--过程实现
procedure find_sname(i_student_idin number,
o_first_name out varchar2,
o_last_name out varchar2)is
begin
select first_name, last_name
into o_first_name, o_last_name
from student
where student_id = i_student_id;
exception
when no_data_found then
DBMS_OUTPUT.PUT_LINE('Error in finding student_id: ' || i_student_id);
end find_sname;
--函数实现
function id_is_good(i_student_idnumber) return booleanis
v_id_cnt number(1);
begin
select count(*)
into v_id_cnt
from student
where student_id = i_student_id;
return(1= v_id_cnt);
exception
when others then
return false;
end id_is_good;
end manage_students;
--再执行以下代码，成功
declare
o_first_name varchar2(20);
o_last_name varchar2(20);
begin
if manage_students.id_is_good(102) then
manage_students.find_sname(102,o_first_name,o_last_name );
end if;
end;
--创建私有对象
--如果只在包体中定义某对象，则它是私有的。
--该包外部的任何程序不能直接访问私有元素
--在manage_students包规范定义中添加一个新的过程的声明（如下），并重新编译包规范
PROCEDURE display_student_count;
END manage_students;
--
create or replace package manage_students is
-- Author : ADMINISTRATOR
-- Created :2014-06-309:57:43
-- Purpose : 学生管理包
procedure find_sname(i_student_idin number,
o_first_name out varchar2,
o_last_name out varchar2);
function id_is_good(i_student_idnumber) return boolean;
procedure display_student_count;
end manage_students;
--包体：
create or replace package body manage_students is
--过程实现
procedure find_sname(i_student_idin number,
o_first_name out varchar2,
o_last_name out varchar2)is
begin
select first_name, last_name
into o_first_name, o_last_name
from student
where student_id = i_student_id;
exception
when no_data_found then
DBMS_OUTPUT.PUT_LINE('Error in finding student_id: ' || i_student_id);
end find_sname;
--函数实现
function id_is_good(i_student_idnumber) return booleanis
v_id_cnt number(1);
begin
select count(*)
into v_id_cnt
from student
where student_id = i_student_id;
return(1= v_id_cnt);
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;
return v_count;
end student_count_priv;
procedure display_student_count
is
begin
--调用私有函数
dbms_output.put_line(student_count_priv);
end display_student_count;
end manage_students;
--测试
begin
manage_students.display_student_count;
end;
--PLS-00302: component'STUDENT_COUNT_PRIV' must be declared
begin
dbms_output.put_line(manage_students.student_count_priv);
end;
--使用包变量
--将变量声明放在包规范中，那么该变量就是一个全局变量。
--可以被任何代码来访问。可以实现
--同一个会话的多个过程之间交换（共享）数据。
--包的初始化部分
--在包体的末尾。当一个用户会话第1次调用一个包时，
--该包的初始化部分的代码就会执行。只执行这一次
--给manage_students包添加包变量
create or replace package manage_students is
-- Author : ADMINISTRATOR
-- Created :2014-06-309:57:43
-- Purpose : 学生管理包
--包变量
v_current_date date;
procedure find_sname(i_student_idin number,
o_first_name out varchar2,
o_last_name out varchar2);
function id_is_good(i_student_idnumber) return boolean;
procedure display_student_count;
end manage_students;
--在包体的初始化部分给包变量赋值
create or replace package body manage_students is
--过程实现
procedure find_sname(i_student_idin number,
o_first_name out varchar2,
o_last_name out varchar2)is
begin
select first_name, last_name
into o_first_name, o_last_name
from student
where student_id = i_student_id;
exception
when no_data_found then
DBMS_OUTPUT.PUT_LINE('Error in finding student_id: ' || i_student_id);
end find_sname;
--函数实现
function id_is_good(i_student_idnumber) return booleanis
v_id_cnt number(1);
begin
select count(*)
into v_id_cnt
from student
where student_id = i_student_id;
return(1= v_id_cnt);
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;
return v_count;
end student_count_priv;
procedure display_student_count
is
begin
dbms_output.put_line(student_count_priv);
end display_student_count;
begin --这是包的初始化部分，对每个会话只执行一次
select sysdate
into v_current_date
from dual;
dbms_output.put_line('包初始化完毕');
end manage_students;
--测试：新开会话窗口，分别执行以下代码2次。观察输出。
begin
dbms_output.put_line(manage_students.v_current_date);
end;
--在包中使用游标变量。很重要
--游标变量就是一个refcursor类型的变量。
--定义refcursor类型的语法：
--TYPE ref_type_name IS REF CURSOR
[RETURN 记录类型];
--如果定义refcursor类型时带了return子句，那么
--该类型的游标变量就叫做强类型的游标变量。如果
--没有带return子句，那么该类型的游标变量就叫做
--弱类型的游标变量
--弱类型的游标变量可以指向任何一个select查询的
--结果集。而强类型的游标变量可以指向的查询，要求
--其select列表必须满足记录类型的内部结构。
--游标变量在包中主要用作过程的输出参数，用来从
--服务器向客户端返回一个结果集。
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 ISREF CURSOR RETURN course_rec_typ;
PROCEDURE get_course_list
(p_student_id NUMBER ,
p_instructor_id NUMBER ,
course_list_cv INOUT 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 ISNULL AND p_instructor_id ISNULL 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 ISNULL 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, coursec, 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 ISNULL 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, coursec, 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;
--在sqlplus中使用游标变量调用过程
--sqlplus中的variable命令专门用来定义绑定变量，
--其类型可以是游标变量。
variable course_cv refcursor;
--注意，以上语句命令窗口不支持
--调用过程
execute course_pkg.get_course_list(102,null,:course_cv);
--打印绑定变量的值。可以看到结果集输出
print :course_cv;
--注意，print命令会自动关闭游标变量。再次执行，出错：
exec course_pkg.get_course_list(NULL,102, :course_cv);
print :course_cv;
--如果过程的参数有一个弱类型的游标变量，那么
--在包中可以不写type ...isref cursor语句，而是
--直接使用系统预先定义的一个refcursor类型，其
--名字是sys_refcursor.
0 0
- 第十章：函数&包
- 第十章：plsql编程基础(函数&包)
- 第十章包
- 第十章 简化函数调用
- java 第十天 包
- 拷贝构造函数第十章 Thinking inc++
- SQL技术第十章使用行函数
- p275第十章函数和指针
- 第十章 存储过程和函数
- matlab 图像函数以及运用（第十章）
- 第十章 函数和指针 p354 指针加法
- 第十章＊信号（十九）－－sleep函数
- 第十章
- 第十章
- 第十章
- 第十章
- 第十章
- 第十章
- c语言称重砝码
- 字典树模板（动态)
- 第九章:记录&触发器&存储过程
- UML 是什么？常用的几种图？
- java可变参数
- 第十章：函数&包
- 进程环境
- 第十一章：层次查询
- 初级算法整理，冒泡排序法
- NSArray之makeObjectsPerformSelector方法论
- ios aes加密
- 学生管理系统
- Java中的内部类详细总结
- 第二十四篇 自作的高仿画板（1）
