《数据库》第十四章 存储过程和函数

6
Inspur Education
过程 8-1
过程是用于完成特定任务的子程序 例如：
前往售票厅
在柜台购买车票
询问关于车票的信息
排队等候
7
Inspur Education
过程 8-2
创建过程的语法：
CREATE [OR REPLACE] PROCEDURE <procedure name> [(<parameter list>)]
包子程序（package subprogram） • 在包内创建的子程序是包子程序。用户在包规范中声明它并在 包体中定义它。它将存储在数据库中，直到用户删除包。
独立子程序（standalone subprogram） • 在方案级别创建的子程序是独立子程序。用户可以使用CREATE PROCEDURE或CREATE FUNCTION语句创建它。它将存储在数据库 中，直到用户使用DROP PROCEDURE或DROP FUNCTION语句删除 它。 包子程序或独立子程序属于存储的子程序。
BEGIN Executable Statements; RETURN result;
EXCEPTION Exception handlers;
END;
15
Inspur Education
函数 4-2
定义函数的限制：
函数只能接受 IN 参数，而不能接受 IN OUT 或 OUT 参数 形参不能是 PL/SQL 类型 函数的返回类型也必须是数据库类型
/
13
Inspur Education
过程 8-8
将过程的执行权限授予其他用户：
SQL> GRANT EXECUTE ON proc_stu TO SCOTT; SQL> GRANT EXECUTE ON proc_swap TO PUBLIC;
删除过程：
SQL> DROP PROCEDURE proc_swap;
DECLARE a number; b number; c number;
PROCEDURE findMin(x IN number, y IN number, z OUT number) IS BEGIN
IF x < y THEN z:= x;
ELSE z:= y;
END IF; END; BEGIN
可以包含 RETURN 语句，但是与函 必须包含至少一条 RETURN
数不同，它不能用于返回值
语句
19
Inspur Education
传参形式
实参（actual parameter）可以由三种方式传入：
• 位置表示法 • 命名表示法 • 混合表示法
20
Inspur Education
传参形式-位置表示法
vv__didecpht at_r(s3tu) d:=en'0t.0f_1d';epartment%type; vv__csltausinsfot_VsAtuRdCenHtA.f_Rc2la(1s0s0%);type; bBeEgGinIN sve_lsetcutinff_ona:=mfeu,nf_c_dsetpua(vrt_mide)n; t,f_class into v_name,v_dept,v_class frDomBMt_Ss_tuOdUeTntPwUhTe.PreUfT__idL=INv_Ei(dv;_stuinfo); ErNeDtu;rn '学号:'||v_name||'姓名:'||v_dept||'班级:'||v_class; e/ xception when no_data_found then
dbms_output.put_line('未找到相应学生'); end; /
9
Inspur Education
过程 8-4
执行过程的语法：
EXECUTE procedure_name(parameters_list);
SQL> SET SERVEROUTPUT ON SQL> EXECUTE proc_stu(‘007');
DECLARE a number; b number; c number;
PROCEDURE findMin(x IN number, y IN number, z OUT number) IS BEGIN
IF x < y THEN z:= x;
ELSE z:= y;
END IF; END; BEGIN
a:= 23; b:= 45; findMin(z=>c,x=>a, y=>b); dbms_output.put_line(' Minimum of (23, 45) : ' || c); END;
22
Inspur Education
传参形式-混合表示法
混合表示法,可以在过程调用中混合使用位置和命名表 示法，例如findMin(a,z=>c,y=>b)，但位置表示法必须 在命名表示法之前。
访问函数的两种方式：
使用 PL/SQL 块 使用 SQL 语句
16
Inspur Education
函数 4-3
创建函数：
CREATE OR REPLACE FUNCTION func_hello RETURN VARCHAR2
IS BEGIN
RETURN '朋友，您好'; END; /
从 SQL 语句调用函数：
IS|AS <local variable decl创a建ra过t程io，n可> 指定运行过程需传递的参数
BEGIN <executable statemen包t括s>在过程中要执行的语句
[EXCEPTION <exception handlers>]处理异常
END;
8
Biblioteka Baidu
Inspur Education
v_tenmupmn1unmubmebr;er := 100; beginnum2 number := 500;
v_tbeemgpin:= p1; p1 :=p_ps2w; ap(num1,num2); p2 :=dbvm_tse_mopu;tput.put_line('num1='||num1); end; dbms_output.put_line('num2='||num2); / end;
10
Inspur Education
过程 8-5
过程参数的三种模式：
IN 用于接受调用程序的值 默认的参数模式
OUT 用于向调用程序返回值
IN OUT 用于接受调用程序的值，并向调用程序返回更新的 值
11
Inspur Education
过程 8-6
SdeQcLla>recreate or replace procedure proc_avgscore(stuid In
SQL> SELECT func_hello FROM DUAL;
17
Inspur Education
函数 4-4
create or replace function func_stu(v_id varchar2) return varchar2 As DvE_CnLaAmReEt_student.f_name%type;
过程 8-3
create or replace 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 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
Inspur Education
第14章 存储过程和函数
Inspur Education
知识点回顾
掌握并使用游标管理技巧 隐式游标 显式游标 Ref游标
2
Inspur Education
本章目标
了解和使用子程序 存储过程 函数
了解和使用程序包
3
Inspur Education
子程序 3-1
命名的 PL/SQL 块，编译并存储在数据库中。 子程序的各个部分：
[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];
dbms_output.put_line('未找到相应学生'); end; /
18
Inspur Education
过程和函数的比较
过程 作为 PL/SQL 语句执行
函数 作为表达式的一部分调用
在规格说明中不包含 RETURN 子句 必须在规格说明中包含 RETURN 子句
不返回任何值
必须返回单个值
vaarvcghsacro2r,eavngusmcobreer;out number)
absegin
bpegroinc_avgscore('001',avgscore);
sdeblmecst_aovugtp(fu_tg.praudt_eli)nien(t'o学a号vg为sc0o0r1e的fro学m生t_的gr平ad均e成wh绩e为re：
声明部分 可执行部分 异常处理部分(可选)
4
Inspur Education
子程序 3-2
子程序的优点： 模块化
将程序分解为逻辑模块
可重用性
可以被任意数目的程序调用
可维护性
简化维护操作
安全性
通过设置权限，使数据更安全
5
Inspur Education
子程序分类 3-3
嵌套子程序（nested subprogram） • 在PL / SQL块内创建的子程序是嵌套子程序。用户可以同时声 明和定义它，也可以先声明它，然后在同一个块中定义它。嵌 套子程序只有在独立子程序或程序包子程序中时才存储在数据 库中。
f'|_|tsot_ucidh=asr(tuaivdg;score));
eenxcde; ption
/
when no_data_found then dbms_output.put_line('未找到相应记录');
end;
/
12
Inspur Education
过程 8-7
creaSteQoLr>reSpElaTcSeEpRroVcEeRduOreUTp_OsNwap(p1 In Out number,p2 In Out number) As SQL> declare
声明程序包中公共 对象。包括类型、 变量、常量、异常、 游标规范和子程序 规范等
程序包 规范 主体
声明程序包私有 对象和实现在包 规范中声明的子 程序和游标
24
Inspur Education
创建程序包 4-1
程序包规范
CREATE [OR REPLACE] PACKAGE package_name IS|AS
a:= 23; b:= 45; findMin(a,z=>c,y=>b); dbms_output.put_line(' Minimum of (23, 45) : ' || c); END;
23
Inspur Education
程序包
程序包是对相关过程、函数、变量、游标和异常等对象 的封装
程序包由规范和主体两部分组成
在位置表示法中，第一个实参代替第一个形参（ formal parameter）; 第二个实参代替第二个形参，依 此类推。(下例,调用过程findMin(a, b, c) 时： a代替x，b代替y，c代替z)
21
Inspur Education
传参形式-命名表示法
在命名表示法中，实参使用箭头符号（=>）与形参相关 联，例如findMin(z=>c,x=>a,y=>b)。
14
Inspur Education
函数 4-1
函数是可以返回值的命名的 PL/SQL 子程序。 创建函数的语法：
CREATE [OR REPLACE] FUNCTION <function name> [(param1,param2)]
RETURN <datatype> IS|AS [local declarations]