Oracle 第7章 子程序、程序包
1、技术目标
- 创建并使用子程序
- 创建并使用程序包
2、什么是子程序
子程序是已命名的PL/SQL块,存储在数据库中
命名的PL/SQL程序有两种:
- 存储过程
- 函数
注意:程序包是存储过程和函数的集合
与匿名PL/SQL块相同,子程序由3部分组成:
- 声明部分
- 可执行部分
- 异常处理部分(可选)
子程序的优点如下:
- 模块化,将程序分解为逻辑模块
- 可重用性,可以被任意数目的程序调用
- 可维护性,简化维护操作
- 安全性,通过设置权限,使数据更安全
3、存储过程
存储过程是用于完成特定任务的子程序,创建语法 为:
CREATE [OR REPLACE] PROCEDURE
procedure_name [(parameter_list)]
IS | AS
local_declaration
BEGIN
executable_statements
[EXCEPTION
exception_handlers]
END [procedure_name];
语法说明:
procedure_name,为存储过程名
parameter_list,为过程参数列表
local_declaration,为声明部分,定义在IS或AS关键字后(匿名块定义在DECLARE后)
executable_statements,为可执行语句
exception_handlers,为异常处理程序
注意:建议将过程名追加到END子句后
使用: 创建存储过程,接收员工编号,查询出该编号的员工并显示,未找到则
处理异常并显示"未找到该编号的雇员",先创建如下存储过程,
CREATE OR REPLACE PROCEDURE
findEmp(employeeNo NUMBER)
AS
empName VARCHAR2(20);
BEGIN
SELECT eName INTO empName
FROM EMP WHERE empNo = employeeNo;
DBMS_OUTPUT.PUT_LINE('雇员姓名是:'|| empName);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE ('未找到该编号的雇员');
END findEmp;
/
再调用该存储过程,语法 为:
EXECUTE procedure_name (parameters_list);
如,
--调用findEmp存储过程并传递参数9527(雇员编号)
set serveroutput on
EXECUTE findEmp(9527);
4、存储过程参数的三种模式
- IN(输入参数),用于接受调用程序的值,为默认的参数模式
- OUT(输出参数),用于向调用程序返回值
- IN OUT(输入输出参数),用于接受调用程序的值,并向返回更新的值
定义存储过程参数的语法 为:
parameter_name [IN | OUT | IN OUT] datatype
[{:= | DEFAULT} expression]
语法说明:
参数IN为默认模式,如果是OUT或者IN OUT参数,必须给其赋值才能返回,
可以为IN参数赋默认值,不能为 OUT、IN OUT参数赋默认值
使用1: 创建带IN参数的存储过程,显示指定项目编号的项目描述,
CREATE OR REPLACE PROCEDURE
itemdesc(itemCode IN VARCHAR2)
IS
vItemdesc VARCHAR2(5);
BEGIN
SELECT itemdesc INTO vItemdesc
FROM ItemFile
WHERE itemcode = itemCode;
DBMS_OUTPUT.PUT_LINE(item_code|| '项目的说明为'|| vItemdesc);
END;
/
SET SERVEROUTPUT ON
EXECUTE itemdesc('I188');--调用存储过程
使用2: 创建并使用带OUT参数的存储过程,根据输入
查询记录并通过输出参数返回值,
CREATE OR REPLACE PROCEDURE
test(value1 IN VARCHAR2,
value2 OUT NUMBER)
IS
identity NUMBER;
BEGIN
SELECT itemrate INTO identity
FROM ItemFile
WHERE itemcode = value1;
IF identity < 200 THEN
value2:=100;
END IF;
END;
--使用匿名块,调用存储过程
DECLARE
value1 VARCHAR2(5) := 'i202';
value2 NUMBER;
BEGIN
--调用存储过程test
test (value1, value2);
DBMS_OUTPUT.PUT_LINE('value2 的值为' || TO_CHAR(value2));
END;
/
使用3: 创建带IN OUT参数的存储过程,
CREATE OR REPLACE PROCEDURE
swap(p1 IN OUT NUMBER,
p2 IN OUT NUMBER)
IS
vTemp NUMBER;
BEGIN
--交换两个参数的值
vTemp := p1;
p1 := p2;
p2 := vTemp;
END;
/
--使用匿名块,调用存储过程
SET SERVEROUT ON
DECLARE
num1 NUMBER := 100;
num2 NUMBER := 200;
BEGIN
swap(num1, num2);
DBMS_OUTPUT.PUT_LINE('num1 = ' || num1);
DBMS_OUTPUT.PUT_LINE('num2 = ' || num2);
END;
/
5、为其他用户执行存储过程授权、删除
存储过程只有创建者可执行,其他用户需授权执行,授权语句如下:
--授权给指定用户
GRANT EXECUTE ON 存储过程名 TO 用户名;
--授权给所有用户
GRANT EXECUTE ON 存储过程名 TO PUBLIC;
删除存储过程的语句 如下:
DROP PROCEDURE 存储过程名;
6、函数
函数与存储过程相似,也是已命名程序块,其主要特征是必须返回一个值 ,
创建函数时需通过return子句指定函数返回值的类型,
创建函数的语法 为:
CREATE [OR REPLACE] FUNCTION
function_name [(param1,param2)]
RETURN datatype {IS|AS}
[local_declarations]
BEGIN
executable_statements;
RETURN result;
EXCEPTION
exception_handlers;
END ;
创建函数的限制 有:
- 函数只能接受IN参数,而不能接受IN OUT或OUT参数
- 形参只能使用数据库类型,不能是PL/SQL类型
- 函数的返回类型也必须是数据库类型
访问函数的两种方式:
- 使用PL/SQL块
- 使用SQL语句
使用1: 创建一个简单的函数funHello,
CREATE OR REPLACE FUNCTION funHello
RETURN VARCHAR2 IS
BEGIN
--返回指定类型的值
RETURN '您好,朋友!';
END;
/
--调用函数funHello,使用SQL
select funHello from dual;
使用2: 创建函数itemPriceRange,验证产品价格是否超出范围,
CREATE OR REPLACE FUNCTION
itemPriceRange (price NUMBER)
RETURN VARCHAR2 AS
minPrice NUMBER;
maxPrice NUMBER;
BEGIN
SELECT MAX(itemRate), MIN(itemRate)
INTO minPrice, maxPrice
FROM ItemFile;
IF price >= minPrice AND price <= maxPrice THEN
RETURN '输入的单价介于最低价与最高价之间';
ELSE
RETURN '超出范围';
END IF;
END;
/
--PL/SQL块中调用函数
DECLARE
p NUMBER := 300;
msg VARCHAR2(200);
BEGIN
msg := itemPriceRange(p);
DBMS_OUTPUT.PUT_LINE(msg);
END;
/
7、函数的授权、删除
其他用户调用函数需获得授权,授权语句 如下:
GRANT EXECUTE ON funHello TO 用户名;
删除函数:
DROP FUNCTION 函数名;
8、使用函数的限制
- 在select语句中调用的任何函数不得修改数据库表
- 远程执行或并行执行时,函数不得读取或写入程序包中变量的值
- 从select、values或set子句调用的函数可以写入程序包中的变量,其他子句中的函数不得写入程序包变量
- 如函数调用执行了update的存储过程,则该函数不能出现在SQL语句内
9、存储过程与函数的比较
过程 | 函 数 |
作为 PL/SQL 语句执行 | 作为表达式的一部分调用 |
在规格说明中不包含RETURN子句 | 必须在规格说明中包含RETURN子句 |
不返回任何值 | 必须返回单个值 |
可以包含 RETURN 语句, 但是与函数不同, 它不能用于返回值 | 必须包含至少一条 RETURN 语句 |
10、自主事务处理
自主事务处理是由另一个事务处理(主事务处理)启动的独立事务处理,
它可以暂停主事务处理并处理过程内的SQL操作,提交或回滚操作,然
后恢复主事务处理
当一个过程调用另一个过程时,在其中任一过程中进行的任何更改在这
两个过程中都是可见的,任何提交或回滚语句均会影响这两个过程中的
事务处理
问题: 创建两个存储过程P1、P2,P1调用P2
--创建P2
CREATE OR REPLACE PROCEDURE P2
AS
a varchar2(50);
BEGIN
--查询编号为V008的卖家地址
select venadd into a
from venderMaster where venCode = 'V008';
--输出显示卖家地址
DBMS_OUTPUT.PUT_LINE(a);
--回滚
ROLLBACK;
END;
/
--创建P1
CREATE OR REPLACE PROCEDURE P1
AS
b varchar2(50);
BEGIN
--修改编号为V008的卖家地址
update venderMaster set venadd = '科园1街'
where venCode = 'V008'
--调用存储过程P2显示卖家地址
P2();
--查询并显示编号为V008的卖家地址
select venadd into b
from venderMaster where venCode = 'V008';
--输出显示卖家地址
DBMS_OUTPUT.PUT_LINE(b);
END;
/
输出结果 (假设编号为V008的卖家原地址为'创业1路'):
科园1街(注:由P2输出)
创业1路(注:由P1输出)
问题描述: 当P2执行回滚操作时,P1也会受影响,说明事务处理可以
跨存储过程执行
解决方案:
为防止一个过程影响其他过程,可将存储过程设置为自主事务 ,确保
P1中进行的更改(包括提交或回滚)不影响P2,设置自主事务可使用编
译关键字AUTONOMOUS_TRANSACTION,该关键字通知PL/SQL编译
器将过程、函数或PL/SQL块标记为自主事务,修改P2,在声明中包含如
下代码:
PRAGMA AUTONOMOUS_TRANSACTION,
--创建P2
CREATE OR REPLACE PROCEDURE P2
AS
a varchar2(50);
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
--查询编号为V008的卖家地址
select venadd into a
from venderMaster where venCode = 'V008';
--输出显示卖家地址
DBMS_OUTPUT.PUT_LINE(a);
--回滚
ROLLBACK;
END;
/
再次执行P1,输出结果 为(假设卖家V008原地址为'创业1路'):
创业1路(注:由P2输出)
科园1街(注:由P1输出)
自主事务处理具有如下特征 :
- 与主事务处理的状态无关
- 提交或回滚操作不影响主事务处理
- 自主事务处理的结果对其他事务是可见的
- 能够启动其他自主事务处理
11、程序包
程序包是数据库对象的一种,是对相关过程、函数、变量、游标和异常
等对象的封装,程序包如下两部分组成:
- 规范,声明程序包中公共对象,包括类型、变量、常量、异常、游标规范和子程序规范等
- 主体,声明程序包私有对象和实现在包规范中声明的子程序和游标
12、程序包规范
规范包含一些应用程序可见的公共对象和类型的声明,是与应用程序
的接口,规范包含了应用程序所需的程序包资源,如果规范只声明了
类型、常量、变量和异常,则不需要主体。子程序、游标必须有程序
主体,创建规范的语法 为:
CREATE [OR REPLACE] PACKAGE package_name
IS|AS
[public type and item declarations]
[subprogram specifications]
END [package_name];
语法说明:
package_name,为包名
public type and item declarations,声明类型、常量、变量、异常和游标等
subprogram specifications,声明PL/SQL子程序
在规范中声明的项,在包之外也可使用,这种项称为"公共对象 ",
使用: 创建包packTemp,在此包中声明子程序orderProc和orderFun,
程序的实现将在包主体中定义,
CREATE OR REPLACE PACKAGE packTemp IS
PROCEDURE orderProc(orderNo varchar2);
FUNCTION orderFun(orderNos varchar2) RETURN varchar2;
END parcTemp;
13、包主体
包主体包含在规范中声明的每个游标和子程序的具体实现,私有
声明也可包括在主体中,创建主题的语法 为:
CREATE [OR REPLACE] PACKAGE BODY package_name
IS | AS
[public type and item declarations]
[subprogram bodies]
[BEGIN
initialization_statements]
END [package_name];
语法说明:
package_name,为包名称
public type and item declarations,为声明变量、常量、游标、异常或类型
subprogram bodies,为定义公共和私有PL/SQL子程序
使用1: 为packTemp包创建主体,
CREATE OR REPLACE PACKAGE BODY packTemp AS
--定义存储过程orderProc
PROCEDURE orderProc(orno VARCHAR2) IS
stat CHAR(1);
BEGIN
SELECT ostatus INTO stat FROM order_master
WHERE orderno = orno;
……
END order_proc;
--定义函数orderFun
FUNCTION orderFun(ornos VARCHAR2)
RETURN VARCHAR2
IS
icode VARCHAR2(5);
ocode VARCHAR2(5);
BEGIN
……
END order_fun;
END pack_me;
/
注意:在创建包规范或主体时发生编译错误,可输入SHOW ERRORS
命令查看错误报告
如需引用包规范中声明的类型、对象、子程序,使用如下语法:
package_name.type_name;
package_name.object_name;
package_name.subprogram_name;
使用2: 执行packTemp包中的存储过程orderProc,
EXECUTE packTemp.orderProc('参数');
14、使用包的优势
- 模块化,可封装类型、对象和子程序
- 更轻松的应用程序设计,可在没有主体情况下先编写规范
- 信息隐藏,利用私有对象和公有对象
- 新增功能,可在同一包中创建同名存储过程,过程的参数数量或数据类型可不同,允许创建在所有存储过程和函数中都能使用的全局变量和游标
- 性能更佳,首次调用包中的子程序,整个包均加载到内存,后续调用无需
- 磁盘I/O操作,另外,更改已打包函数的定义无需重新编译调用
15、包中的游标
包中可定义和使用游标,游标定义分为:
- 游标规范
- 游标主体
注意:包规范中声明游标规范时必须用RETURN指定游标返回类型
RETURN子句指示从游标获取并返回的数据元素,数据元素由游标
主体中的select语句确定,游标规范必须包含程序中所使用游标所
需的所有信息,因此需要返回数据类型,return子句可由两种数据
类型结构之一组成:
- 使用%ROWTYPE属性根据数据库表定义的记录
- 根据程序员定义的记录类型的记录
使用: 在curPack包中为orderMaster表创建和定义ordCur游标,
创建存储过程ordPro检索vcode,
--定义规范
CREATE OR REPLACE PACKAGE curPack IS
--游标规范
CURSOR ordCur(vcode VARCHAR2)
RETURN orderMaster%ROWTYPE; --返回类型
--存储过程ordPro
PROCEDURE ordPro(vcode VARCHAR2);
END cur_pack;
--定义主体
CREATE OR REPLACE PACKAGE BODY curPack AS
--游标主体
CURSOR ordCur(vcode VARCHAR2)
RETURN orderMaster%ROWTYPE --返回类型
IS SELECT * FROM orderMaster WHERE venCode = vcode;
--存储过程
PROCEDURE ordPro(vcode VARCHAR2) IS
orRec orderMaster%ROWTYPE;
BEGIN
--打开游标
OPEN ordCur(vcode);
LOOP
FETCH ordCur INTO orRec;
EXIT WHEN ordCur%NOTFOUND;
DBMS_OUTPUT.PUT_LIne(’ 返回的值为 ' || orRec.orderNo);
END LOOP;
END ordPro;
END curPack;
/
16、关于程序和程序包
子程序和程序包为数据库存储的对象,Oracle会在数据字典中存储对象的信息,
查询USER_OBJECTS视图,可获取子程序和包信息
使用1: 查询存储过程、函数、包的信息,
SELECT object_name, object_type
FROM USER_OBJECTS
WHERE object_type IN ('PROCEDURE', 'FUNCTION', 'PACKAGE', 'PACKAGE BODY');
USER_SOURCE视图存储子程序和程序包的源代码,
使用2: 查询存储过程TEST的源代码,
SELECT line, text FROM USER_SOURCE
WHERE NAME='TEST';
17、总结
- 子程序是命名的 PL/SQL 块,可带参数并可在需要时随时调用
- 有两种类型的PL/SQL子程序,即过程和函数
- 过程用户执行特定的任务,函数用于执行任务并返回值
- 程序包是对相关类型、变量、常量、游标、异常、过程和函数等对象的封装
- 程序包由两部分组成,即包规范和包主体
- 使用程序包的优点是:模块化、更轻松的程序设计、信息隐藏、新增功能以及性能更佳