Oracle7 子程序和程序包
回顾
游标用于处理查询结果集中的数据
游标类型有:隐式游标、显式游标和 REF 游标
隐式游标由 PL/SQL 自动定义、打开和关闭
显式游标用于处理返回多行的查询
显式游标可以删除和更新活动集中的行
要处理结果集中所有记录时,可使用循环游标
在声明 REF 游标时,不需要将 SELECT 语句与其关联
目标
了解和使用子程序
了解和使用程序包
子程序 2-1
命名的 PL/SQL 块,编译并存储在数据库中。
子程序的各个部分:
声明部分
可执行部分
异常处理部分(可选)
子程序的分类:
过程 - 执行某些操作
函数 - 执行操作并返回值
子程序 2-2
子程序的优点:
模块化
将程序分解为逻辑模块
可重用性
可以被任意数目的程序调用
可维护性
简化维护操作
安全性
通过设置权限,使数据更安全
过程 8-1
过程是用于完成特定任务的子程序
例如:
前往售票厅 询问关于车票的信息 排队等候 在柜台购买车票
与存储过程对比学习:定义、执行
过程 8-2
创建过程的语法:
CREATE [OR REPLACE] PROCEDURE
<procedure name> [(<parameter list>)]
IS|AS
<local variable declaration>
BEGIN
<executable statements>
[EXCEPTION
<exception handlers>]
END;
[说明]与普通PL/SQL语句块的区别
Is和As没有区别,其他地方亦然
[注]过程体内不能使用查询语句,只能用于赋值(SQL语句块都如此)
如果过程体语句有错误也能创建成功
没有参数就不写,不用()
过程 8-3
create or replace procedure test4(v_id varchar2)
is
v_ename emp.ename%type;
v_sal emp.sal%type;
begin
select ename, sal into v_ename,v_sal from emp where empno=v_id;
dbms_output.put_line(v_ename||' '||v_sal);
end;
过程 8-4
执行过程的语法:
EXECUTE procedure_name(parameters_list);
SQL> SET SERVEROUTPUT ON
SQL> EXEC proc_stu(‘007');
[说明]此例为根据输入项目编号,显示项目说明
execute可简写为exec
[注]调用带有输出参数的过程时,无需再写exec
过程 8-5
过程参数的三种模式:
IN
用于接受调用程序的值
默认的参数模式
OUT
用于向调用程序返回值
IN OUT
用于接受调用程序的值,并向调用程序返回更新的值
[说明]没有返回值,但可以有Out、In Out参数,In参数为默认
在过程执行过程中,如需要返回可直接输入return
过程创建后,可在OEM中查看相应信息
过程 8-6
create or replace procedure test5(
x in number,
y in number,
z out number
)
as
begin
z:=x+y;
end;
[例]In、Out参数的使用,输入学号,输出学生平均成绩;并写PL/SQL块该过程
过程 8-7
create or replace procedure test6(
a in out number,
b in out number
)
is
begin
dbms_output.put_line(a);
dbms_output.put_line(b);
a:=100;
b:=100;
end;
SQL> SET SERVEROUT ON
SQL> declare
num1 number := 100;
num2 number := 500;
begin
p_swap(num1,num2);
dbms_output.put_line('num1='||num1);
dbms_output.put_line('num2='||num2);
end;
/
[例]输入两个数,交换位置
create or replace procedure p_swap(p1 In Out number,p2 In Out number)
As
v_temp number;
begin
v_temp := p1;
p1 := p2;
p2 := v_temp;
end;
/
--调用
过程 8-8
将过程的执行权限授予其他用户:
SQL> GRANT EXECUTE ON proc_stu TO SCTOO;
SQL> GRANT EXECUTE ON proc_swap TO PUBLIC;
删除过程:
SQL> DROP PROCEDURE proc_swap;
函数 4-1
函数是可以返回值的命名的 PL/SQL 子程序。
创建函数的语法:
CREATE [OR REPLACE] FUNCTION
<function name> [(param1,param2)]
RETURN <datatype> IS|AS
[local declarations]
BEGIN
Executable Statements;
RETURN result;
EXCEPTION
Exception handlers;
END;
[说明]与过程对比:必须声名返回值类型,并在函数体里显式返回
函数 4-2
定义函数的限制:
函数只能接受 IN 参数,而不能接受 IN OUT 或 OUT 参数
形参不能是 PL/SQL 类型
函数的返回类型也必须是数据库类型
访问函数的两种方式:
使用 PL/SQL 块
使用 SQL 语句
函数 4-3
创建函数:
CREATE OR REPLACE FUNCTION func_hello
RETURN VARCHAR2
IS
BEGIN
RETURN '朋友,您好';
END;
/
从 SQL 语句调用函数:
SQL> SELECT func_hello FROM DUAL;
【说明】执行不需要exec
函数 4-4
create or replace function get_sal(v_ename in varchar2)
return number
is
v_sal number;
begin
select sal into v_sal from emp where upper(ename)=upper(v_ename);
return v_sal;
exception
when no_data_found then
raise_application_error(-20000,'员工不存在');
end;
过程和函数的比较
过 程 函 数
作为 PL/SQL 语句执行 作为表达式的一部分调用
在规格说明中不包含 RETURN 子句 必须在规格说明中包含 RETURN 子句
不返回任何值 必须返回单个值
可以包含 RETURN 语句,但是与函数不同,它不能用于返回值 必须包含至少一条 RETURN 语句
程序包
程序包是对相关过程、函数、变量、游标和异常等对象的封装
程序包由规范和主体两部分组成
规范 声明程序包中公共对象。包括类型、变量、常量、异常、游标规范和子程序规范等
主体 声明程序包私有对象和实现在包规范中声明的子程序和游标
[说明]与Java对比,package、interface
(1)规范:与应用程序的接口
(2)只有在规范中声名游标或子程序时才需要主体
[说明]由于我们已经在前面说明了一些常用的内置包:dbms_output、dbms_lob、dbms_random,因此我们不再单独介绍内置包了
创建程序包 2-1
程序包规范
CREATE [OR REPLACE]
PACKAGE
package_name IS|AS
[Public item declarations]
[Subprogram specification]
END [package_name];
程序包主体
CREATE [OR REPLACE] PACKAGE BODY package_name IS|AS
[Private item declarations]
[Subprogram bodies]
[BEGIN
Initialization]
END [package_name];
创建程序包 2-2
create or replace package body pack_stu As
procedure proc_stu(v_id varchar2)
Is
v_name t_student.f_name%type;
v_dept t_student.f_department%type;
v_class t_student.f_class%type;
begin
v_globalid := v_id;
select f_name,f_department,f_class into v_name,v_dept,v_class from t_student where f_id=v_id;
...
end proc_stu;
function func_stu(v_id varchar2)
return varchar2 As
...
end func_stu;
end pack_stu;
/
[例]程序包:包含共有变量v_globalid;过程proc_stu:根据学号显示学生姓名、系别、班级;函数func_stu:根据学号显示学生姓名、系别、班级;
--程序包规范
create or replace package pack_stu As
v_globalid char(8);
procedure proc_stu(v_id varchar2);
function func_stu(v_id varchar2) return varchar2;
end pack_stu;
/
--程序包主体
create or replace package body pack_stu As
procedure proc_stu(v_id varchar2)
Is
v_name t_student.f_name%type;
v_dept t_student.f_department%type;
v_class t_student.f_class%type;
begin
v_globalid := v_id;
select f_name,f_department,f_class into v_name,v_dept,v_class from t_student where f_id=v_id;
dbms_output.put_line('学号:'||v_name||'姓名:'||v_dept||'班级:'||v_class);
exception
when no_data_found then
dbms_output.put_line('未找到相应学生');
end proc_stu;
function func_stu(v_id varchar2)
return varchar2 As
v_name t_student.f_name%type;
v_dept t_student.f_department%type;
v_class t_student.f_class%type;
begin
v_globalid := v_id;
select f_name,f_department,f_class into v_name,v_dept,v_class from t_student where f_id=v_id;
return '学号:'||v_name||'姓名:'||v_dept||'班级:'||v_class;
exception
when no_data_found then
dbms_output.put_line('未找到相应学生');
end func_stu;
end pack_stu;
/
--使用程序包
select pack_stu.func_stu('001') from dual;
begin
dbms_output.put_line(pack_stu.v_globalid);
end;
/
程序包的优点
模块化
更轻松的应用程序设计
信息隐藏
新增功能
性能更佳
新增功能:“重载”、全局变量和游标
性能更佳:程序包加载到内存中
程序包中的游标 2-1
游标的定义分为游标规范和游标主体两部分
在包规范中声明游标规范时必须使用 RETURN 子句指定游标的返回类型
RETURN子句指定的数据类型可以是:
用 %ROWTYPE 属性引用表定义的记录类型
程序员定义的记录类型
[说明]用户自定义类型可参考OEM中操作;注意程序包中的游标的特殊点,说明理由
程序包中的游标 2-2
SQL> CREATE OR REPLACE PACKAGE pack_cur As
Cursor cur_stu(stuclass number) return t_student%rowtype;
procedure proc_stu(stuclass number);
end;
/
SQL> CREATE OR REPLACE PACKAGE BODY pack_cur AS
Cursor cur_stu(stuclass number) return t_student%rowtype Is
select * from t_student where f_class = stuclass;
procedure proc_stu(stuclass number)
As
rec_stu t_student%rowtype;
begin
Open cur_stu(stuclass);
loop
Fetch cur_stu Into rec_stu;
Exit when cur_stu%NotFound;
dbms_output.put_line('学生姓名:'||rec_stu.f_name);
end loop;
end proc_stu;
end;
/
[例]定义一游标:接收班级为参数,返回行属性;定义一过程:利用游标显示指定班级学生的姓名。
--调用程序包
exec pack_cur.proc_stu(1);
有关子程序和程序包的信息
USER_OBJECTS 视图包含用户创建的子程序和程序包的信息
SELECT object_name, object_type
FROM USER_OBJECTS
WHERE object_type IN ('PROCEDURE', 'FUNCTION',
'PACKAGE', 'PACKAGE BODY');
USER_SOURCE 视图存储子程序和程序包的源代码
SELECT line, text FROM USER_SOURCE
WHERE NAME = 'PACK_CUR';
总结
子程序是命名的 PL/SQL 块,可带参数并可在需要时随时调用
有两种类型的PL/SQL子程序,即过程和函数
过程用户执行特定的任务,函数用于执行任务并返回值
程序包是对相关类型、变量、常量、游标、异常、过程和函数等对象的封装
程序包由两部分组成,即包规范和包主体
使用程序包的优点是:模块化、更轻松的程序设计、信息隐藏、新增功能以及性能更佳
回顾
游标用于处理查询结果集中的数据
游标类型有:隐式游标、显式游标和 REF 游标
隐式游标由 PL/SQL 自动定义、打开和关闭
显式游标用于处理返回多行的查询
显式游标可以删除和更新活动集中的行
要处理结果集中所有记录时,可使用循环游标
在声明 REF 游标时,不需要将 SELECT 语句与其关联
目标
了解和使用子程序
了解和使用程序包
子程序 2-1
命名的 PL/SQL 块,编译并存储在数据库中。
子程序的各个部分:
声明部分
可执行部分
异常处理部分(可选)
子程序的分类:
过程 - 执行某些操作
函数 - 执行操作并返回值
子程序 2-2
子程序的优点:
模块化
将程序分解为逻辑模块
可重用性
可以被任意数目的程序调用
可维护性
简化维护操作
安全性
通过设置权限,使数据更安全
过程 8-1
过程是用于完成特定任务的子程序
例如:
前往售票厅 询问关于车票的信息 排队等候 在柜台购买车票
与存储过程对比学习:定义、执行
过程 8-2
创建过程的语法:
CREATE [OR REPLACE] PROCEDURE
<procedure name> [(<parameter list>)]
IS|AS
<local variable declaration>
BEGIN
<executable statements>
[EXCEPTION
<exception handlers>]
END;
[说明]与普通PL/SQL语句块的区别
Is和As没有区别,其他地方亦然
[注]过程体内不能使用查询语句,只能用于赋值(SQL语句块都如此)
如果过程体语句有错误也能创建成功
没有参数就不写,不用()
过程 8-3
create or replace procedure test4(v_id varchar2)
is
v_ename emp.ename%type;
v_sal emp.sal%type;
begin
select ename, sal into v_ename,v_sal from emp where empno=v_id;
dbms_output.put_line(v_ename||' '||v_sal);
end;
过程 8-4
执行过程的语法:
EXECUTE procedure_name(parameters_list);
SQL> SET SERVEROUTPUT ON
SQL> EXEC proc_stu(‘007');
[说明]此例为根据输入项目编号,显示项目说明
execute可简写为exec
[注]调用带有输出参数的过程时,无需再写exec
过程 8-5
过程参数的三种模式:
IN
用于接受调用程序的值
默认的参数模式
OUT
用于向调用程序返回值
IN OUT
用于接受调用程序的值,并向调用程序返回更新的值
[说明]没有返回值,但可以有Out、In Out参数,In参数为默认
在过程执行过程中,如需要返回可直接输入return
过程创建后,可在OEM中查看相应信息
过程 8-6
create or replace procedure test5(
x in number,
y in number,
z out number
)
as
begin
z:=x+y;
end;
[例]In、Out参数的使用,输入学号,输出学生平均成绩;并写PL/SQL块该过程
过程 8-7
create or replace procedure test6(
a in out number,
b in out number
)
is
begin
dbms_output.put_line(a);
dbms_output.put_line(b);
a:=100;
b:=100;
end;
SQL> SET SERVEROUT ON
SQL> declare
num1 number := 100;
num2 number := 500;
begin
p_swap(num1,num2);
dbms_output.put_line('num1='||num1);
dbms_output.put_line('num2='||num2);
end;
/
[例]输入两个数,交换位置
create or replace procedure p_swap(p1 In Out number,p2 In Out number)
As
v_temp number;
begin
v_temp := p1;
p1 := p2;
p2 := v_temp;
end;
/
--调用
过程 8-8
将过程的执行权限授予其他用户:
SQL> GRANT EXECUTE ON proc_stu TO SCTOO;
SQL> GRANT EXECUTE ON proc_swap TO PUBLIC;
删除过程:
SQL> DROP PROCEDURE proc_swap;
函数 4-1
函数是可以返回值的命名的 PL/SQL 子程序。
创建函数的语法:
CREATE [OR REPLACE] FUNCTION
<function name> [(param1,param2)]
RETURN <datatype> IS|AS
[local declarations]
BEGIN
Executable Statements;
RETURN result;
EXCEPTION
Exception handlers;
END;
[说明]与过程对比:必须声名返回值类型,并在函数体里显式返回
函数 4-2
定义函数的限制:
函数只能接受 IN 参数,而不能接受 IN OUT 或 OUT 参数
形参不能是 PL/SQL 类型
函数的返回类型也必须是数据库类型
访问函数的两种方式:
使用 PL/SQL 块
使用 SQL 语句
函数 4-3
创建函数:
CREATE OR REPLACE FUNCTION func_hello
RETURN VARCHAR2
IS
BEGIN
RETURN '朋友,您好';
END;
/
从 SQL 语句调用函数:
SQL> SELECT func_hello FROM DUAL;
【说明】执行不需要exec
函数 4-4
create or replace function get_sal(v_ename in varchar2)
return number
is
v_sal number;
begin
select sal into v_sal from emp where upper(ename)=upper(v_ename);
return v_sal;
exception
when no_data_found then
raise_application_error(-20000,'员工不存在');
end;
过程和函数的比较
过 程 函 数
作为 PL/SQL 语句执行 作为表达式的一部分调用
在规格说明中不包含 RETURN 子句 必须在规格说明中包含 RETURN 子句
不返回任何值 必须返回单个值
可以包含 RETURN 语句,但是与函数不同,它不能用于返回值 必须包含至少一条 RETURN 语句
程序包
程序包是对相关过程、函数、变量、游标和异常等对象的封装
程序包由规范和主体两部分组成
规范 声明程序包中公共对象。包括类型、变量、常量、异常、游标规范和子程序规范等
主体 声明程序包私有对象和实现在包规范中声明的子程序和游标
[说明]与Java对比,package、interface
(1)规范:与应用程序的接口
(2)只有在规范中声名游标或子程序时才需要主体
[说明]由于我们已经在前面说明了一些常用的内置包:dbms_output、dbms_lob、dbms_random,因此我们不再单独介绍内置包了
创建程序包 2-1
程序包规范
CREATE [OR REPLACE]
PACKAGE
package_name IS|AS
[Public item declarations]
[Subprogram specification]
END [package_name];
程序包主体
CREATE [OR REPLACE] PACKAGE BODY package_name IS|AS
[Private item declarations]
[Subprogram bodies]
[BEGIN
Initialization]
END [package_name];
创建程序包 2-2
create or replace package body pack_stu As
procedure proc_stu(v_id varchar2)
Is
v_name t_student.f_name%type;
v_dept t_student.f_department%type;
v_class t_student.f_class%type;
begin
v_globalid := v_id;
select f_name,f_department,f_class into v_name,v_dept,v_class from t_student where f_id=v_id;
...
end proc_stu;
function func_stu(v_id varchar2)
return varchar2 As
...
end func_stu;
end pack_stu;
/
[例]程序包:包含共有变量v_globalid;过程proc_stu:根据学号显示学生姓名、系别、班级;函数func_stu:根据学号显示学生姓名、系别、班级;
--程序包规范
create or replace package pack_stu As
v_globalid char(8);
procedure proc_stu(v_id varchar2);
function func_stu(v_id varchar2) return varchar2;
end pack_stu;
/
--程序包主体
create or replace package body pack_stu As
procedure proc_stu(v_id varchar2)
Is
v_name t_student.f_name%type;
v_dept t_student.f_department%type;
v_class t_student.f_class%type;
begin
v_globalid := v_id;
select f_name,f_department,f_class into v_name,v_dept,v_class from t_student where f_id=v_id;
dbms_output.put_line('学号:'||v_name||'姓名:'||v_dept||'班级:'||v_class);
exception
when no_data_found then
dbms_output.put_line('未找到相应学生');
end proc_stu;
function func_stu(v_id varchar2)
return varchar2 As
v_name t_student.f_name%type;
v_dept t_student.f_department%type;
v_class t_student.f_class%type;
begin
v_globalid := v_id;
select f_name,f_department,f_class into v_name,v_dept,v_class from t_student where f_id=v_id;
return '学号:'||v_name||'姓名:'||v_dept||'班级:'||v_class;
exception
when no_data_found then
dbms_output.put_line('未找到相应学生');
end func_stu;
end pack_stu;
/
--使用程序包
select pack_stu.func_stu('001') from dual;
begin
dbms_output.put_line(pack_stu.v_globalid);
end;
/
程序包的优点
模块化
更轻松的应用程序设计
信息隐藏
新增功能
性能更佳
新增功能:“重载”、全局变量和游标
性能更佳:程序包加载到内存中
程序包中的游标 2-1
游标的定义分为游标规范和游标主体两部分
在包规范中声明游标规范时必须使用 RETURN 子句指定游标的返回类型
RETURN子句指定的数据类型可以是:
用 %ROWTYPE 属性引用表定义的记录类型
程序员定义的记录类型
[说明]用户自定义类型可参考OEM中操作;注意程序包中的游标的特殊点,说明理由
程序包中的游标 2-2
SQL> CREATE OR REPLACE PACKAGE pack_cur As
Cursor cur_stu(stuclass number) return t_student%rowtype;
procedure proc_stu(stuclass number);
end;
/
SQL> CREATE OR REPLACE PACKAGE BODY pack_cur AS
Cursor cur_stu(stuclass number) return t_student%rowtype Is
select * from t_student where f_class = stuclass;
procedure proc_stu(stuclass number)
As
rec_stu t_student%rowtype;
begin
Open cur_stu(stuclass);
loop
Fetch cur_stu Into rec_stu;
Exit when cur_stu%NotFound;
dbms_output.put_line('学生姓名:'||rec_stu.f_name);
end loop;
end proc_stu;
end;
/
[例]定义一游标:接收班级为参数,返回行属性;定义一过程:利用游标显示指定班级学生的姓名。
--调用程序包
exec pack_cur.proc_stu(1);
有关子程序和程序包的信息
USER_OBJECTS 视图包含用户创建的子程序和程序包的信息
SELECT object_name, object_type
FROM USER_OBJECTS
WHERE object_type IN ('PROCEDURE', 'FUNCTION',
'PACKAGE', 'PACKAGE BODY');
USER_SOURCE 视图存储子程序和程序包的源代码
SELECT line, text FROM USER_SOURCE
WHERE NAME = 'PACK_CUR';
总结
子程序是命名的 PL/SQL 块,可带参数并可在需要时随时调用
有两种类型的PL/SQL子程序,即过程和函数
过程用户执行特定的任务,函数用于执行任务并返回值
程序包是对相关类型、变量、常量、游标、异常、过程和函数等对象的封装
程序包由两部分组成,即包规范和包主体
使用程序包的优点是:模块化、更轻松的程序设计、信息隐藏、新增功能以及性能更佳