Oracle 第7章 子程序、程序包

 

Oracle 第6章 游标

Oracle 第8章 触发器、内置程序包

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子程序，即过程和函数
• 过程用户执行特定的任务，函数用于执行任务并返回值
• 程序包是对相关类型、变量、常量、游标、异常、过程和函数等对象的封装
• 程序包由两部分组成，即包规范和包主体
• 使用程序包的优点是：模块化、更轻松的程序设计、信息隐藏、新增功能以及性能更佳

 

Oracle 第6章 游标

Oracle 第8章 触发器、内置程序包