《Oracle数据库应用与开发》第07章[子程序与程序包]理论课

• 从 SQL 语句调用函数： SQL> SELECT fun_hello FROM DUAL;
函数 4-4
CREATE OR REPLACE FUNCTION item_price_range (price NUMBER) RETURN VARCHAR2 AS min_price NUMBER; DECLARE NUMBER; max_price P NUMBER := 300; BEGIN MSG VARCHAR2(200); SELECT MAX(ITEMRATE), MIN(ITEMRATE) BEGIN max_price, min_price INTO MSG :=itemfile; FROM item_price_range(300); DBMS_OUTPUT.PUT_LINE(MSG); max_price IF price >= min_price AND price <= END; THEN / RETURN '输入的单价介于最低价与最高价之间'; ELSE RETURN '超出范围'; END IF; END; /
• 函数是可以返回值的命名的 PL/SQL 子程 序。 • 创建函数的语法：
CREATE [OR REPLACE] FUNCTION <function name> [(param1,param2)] RETURN <datatype> IS|AS [local declarations] BEGIN Executable Statements; RETURN result; EXCEPTION Exception handlers;
子程序和程序包
《Oracle数据库应用与开发》第07章
回顾
• 游标用于处理查询结果集中的数据 • 游标类型有：隐式游标、显式游标和 REF 游标 • 隐式游标由 PL/SQL 自动定义、打开和关 闭 • 显式游标用于处理返回多行的查询 • 显式游标可以删除和更新活动集中的行 • 要处理结果集中所有记录时，可使用循环 游标 • 在声明 REF 游标时，不需要将 SELECT
创建程序包 2-2
CREATE OR REPLACE PACKAGE pack_me CREATE OR REPLACE PACKAGE BODY pack_me AS IS PROCEDURE order_proc (orno VARCHAR2) IS PROCEDURE order_proc (orno VARCHAR2); stat CHAR(1); FUNCTION order_fun(ornos VARCHAR2) RETURN VARCHAR2; BEGIN END pack_me; SELECT ostatus INTO stat FROM order_master / WHERE orderno = orno; …… END order_proc; FUNCTION order_fun(ornos VARCHAR2) RETURN VARCHAR2 IS icode VARCHAR2(5); ocode VARCHAR2(5); BEGIN …… END order_fun; END pack_me; /
过程和函数的比较
过程 作为 PL/SQL 语句执行 在规格说明中不包含 RETURN 子句 不返回任何值 可以包含 RETURN 语句，但 是与函数不同，它不能用于返 回值 函 数 作为表达式的一部分调用 必须在规格说明中包含 RETURN 子句 必须返回单个值 必须包含至少一条 RETURN 语句
过程 8-3
CREATE OR REPLACE PROCEDURE find_emp (emp_no NUMBER) AS empname VARCHAR2(20); BEGIN SELECT ename INTO empname FROM EMP WHERE empno = emp_no; DBMS_OUTPUT.PUT_LINE('雇员姓名是 '|| empname); EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE ('雇员编号未找到'); END find_emp; /
过程 8-6
SQL> CREATE OR REPLACE PROCEDURE test( value1 IN VARCHAR2, DECLARE OUT NUMBER ) value2 value1 VARCHAR2(5) := 'i202'; 在PL/SQL语句块中不能 语句块中不能 IS value2 NUMBER; EXEC关键字来调用过程 关键字来调用过程 identity NUMBER; BEGIN BEGIN test (value1, value2); INTO identity SELECT ITEMRATE DBMS_OUTPUT.PUT_LINE('value2 的值为' FROM itemFile || TO_CHAR(value2)); WHERE itemcode = value1; END; IF identity < 200 THEN / value2:=100; END IF; END;
主体
创建程序包 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]
过程 8-8
• 将过程的执行权限授予其他用户：
SQL> GRANT EXECUTE ON find_emp TO MARTIN; SQL> GRANT EXECUTE ON swap TO PUBLIC;(所有数据库用户)
• 删除过程：
SQL> DROP PROCEDURE find_emp;
函数 4-1
– 将程序分解为逻辑模块
• 可重用性
– 可以被任意数目的程序调用
• 可维护性
– 简化维护操作
• 安全性
– 通过设置权限，使数据更安全
过程 8-1
• 过程是用于完成特定任务的子程序 • 例如：
在柜台购买车票 前往售票厅
询问关于车票的信息
排队等候
过程 8-2
创建过程的语法：
CREATE [OR REPLACE] PROCEDURE <procedure name> [(<parameter list>)] IS|AS 创建过程，可指定运行过程需传递的参数 <local variable declaration> BEGIN 包括在过程中要执行的语句 <executable statements> [EXCEPTION 处理异常 <exception handlers>] END;
自主事务处理 2-1
• 自主事务处理
– 主事务处理启动独立事务处理 – 然后主事务处理被暂停 – 自主事务处理子程序内的 SQL 操作 – 然后终止自主事务处理 – 恢复主事务处理
• PRAGMA AUTONOMOUS_TRANSACTION 用于标记子程序为自主事务处理
自主事务处理 2-2
• 自主事务处理的特征：
– 用 %ROWTYPE 属性引用表定义的记录类型 – 程序员定义的记录类型
程序包中的游标 2-2
SQL> CREATE OR REPLACE PACKAGE BODY cur_pack AS CURSOR ord_cur(vcode VARCHAR2) SQL> CREATE OR REPLACE PACKAGE cur_pack IS RETURN order_master%ROWTYPE IS CURSOR ord_cur(vcode VARCHAR2) SELECT * FROM order_master WHERE VENCODE=vcode; RETURN order_master%ROWTYPE; PROCEDURE ord_pro(vcode VARCHAR2) IS PROCEDURE ord_pro(vcode VARCHAR2); or_rec order_master%ROWTYPE; BEGIN END cur_pack; / OPEN ord_cur(vcode); LOOP FETCH ord_cur INTO or_rec; EXIT WHEN ord_cur%NOTFOUND; DBMS_OUTPUT.PUT_LIne(’返回的值为 || or_rec.orderno); 返回的值为' 返回的值为 END LOOP; END ord_pro; END cur_pack; /
– 与主事务处理的状态无关 – 提交或回滚操作不影响主事务处理 – 自主事务处理的结果对其他事务是可见的 – 能够启动其他自主事务处理
程序包
• 程序包是对相关过程、函数、变量、游标 和异常等对象的封装 • 程序包由规范和主体两部分组成
声明程序包中公共 对象。包括类型、 变量、常量、异常、 游标规范和子程序 规范等 程序包 规范 声明程序包私有 对象和实现在包 规范中声明的子 程序和游标
过程 8-4
过程参数的三种模式： • IN
– 用于接受调用程序的值 – 默认的参数模式
• OUT
– 用于向调用程序返回值
• IN OUT
– 用于接受调用程序的值，并向调用程序返回更 新的值