-
-函数的创建和使用
-
-函数像过程一样,也存储在数据库中。
-
-两者之间的重要区别是,函数是能够返回单个值
-
-的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 a
procedure
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_id
number)
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: expression
is
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
-
30
9:
57:
43
-
- Purpose : 学生管理包
procedure find_sname(i_student_id
in
number,
o_first_name
out varchar2,
o_last_name
out varchar2);
function id_is_good(i_student_id
number)
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" does
not exist
ORA
-
06508: PL
/
SQL: could
not find program unit being
called:
"STUDENT.MANAGE_STUDENTS"
-
-这是正常的,因为过程和函数还没有具体实现,当然
-
-不能执行。
-
-实现包体
create
or replace package body manage_students
is
-
-过程实现
procedure find_sname(i_student_id
in
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_id
number)
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;
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
-
30
9:
57:
43
-
- Purpose : 学生管理包
procedure find_sname(i_student_id
in
number,
o_first_name
out varchar2,
o_last_name
out varchar2);
function id_is_good(i_student_id
number)
return
boolean;
procedure display_student_count;
end manage_students;
-
-包体:
create
or replace package body manage_students
is
-
-过程实现
procedure find_sname(i_student_id
in
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_id
number)
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;
-
-定义私有函数。该函数只能被本包中的
-
-其它过程或函数调用
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
-
30
9:
57:
43
-
- Purpose : 学生管理包
-
-包变量
v_current_date
date;
procedure find_sname(i_student_id
in
number,
o_first_name
out varchar2,
o_last_name
out varchar2);
function id_is_good(i_student_id
number)
return
boolean;
procedure display_student_count;
end manage_students;
-
-在包体的初始化部分给包变量赋值
create
or replace package body manage_students
is
-
-过程实现
procedure find_sname(i_student_id
in
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_id
number)
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;
-
-定义私有函数。该函数只能被本包中的
-
-其它过程或函数调用
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;
-
-在包中使用游标变量。很重要
-
-游标变量就是一个
ref
cursor类型的变量。
-
-定义
ref
cursor类型的语法:
-
-
TYPE ref_type_name
IS
REF
CURSOR
[
RETURN 记录类型];
-
-如果定义
ref
cursor类型时带了
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
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;
-
-在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 ...
is
ref
cursor语句,而是
-
-直接使用系统预先定义的一个
ref
cursor类型,其
-
-名字是sys_refcursor.