Oracle学习笔记十二 子程序（存储过程、自定函数）和程序包
子程序
子程序：命名的 PL/SQL 块，编译并存储在数据库中。
子程序的各个部分：
1.声明部分
2.可执行部分
3.异常处理部分(可选)
子程序的分类：
1.过程 － 执行某些操作
2.函数 － 执行操作并返回值
子程序的优点：
模块化
将程序分解为逻辑模块
可重用性
可以被任意数目的程序调用
可维护性
简化维护操作
安全性
通过设置权限，使数据更安全
存储过程
过程是用于完成特定任务的子程序。
例如：
一个购票过程可以分为很多个子过程，分别完成。
创建存储过程
创建过程的语法：
CREATE [OR REPLACE] PROCEDURE <procedure name> [(<parameter list>)] IS|AS <local variable declaration> BEGIN <executable statements> [EXCEPTION <exception handlers>] END;
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;
我们还可以：
--查询用户所定义的存储过程 select distinct name from user_source where type = 'PROCEDURE'; --查看存储过程定义的源码内容（PL/SQL语句） select text from user_source where name = 'P_TEST'; --如果定义有错误，查看错误原因 Show error procedure 存储过程名 --删除存储过程 Drop procedure 存储过程名;
过程参数的三种模式：
IN
1.用于接受调用程序的值
2.默认的参数模式
OUT
1.用于向调用程序返回值
IN OUT
1.用于接受调用程序的值，并向调用程序返回更新的值
--统计满足指定工资数的员工的数量：带输入参数in的存储过程
Create or replace procedure p_total_sal(var_sal in int) is Var_count int; Begin Select count(*) into var_count from emp where sal > var_sal; Dbms_output.put_line(' 符合要求的员工总数为： ' || var_count); Exception When others then Dbms_output.put_line('未知错误'); End;
--定义一个存储过程返回指定部门的员工总数:带返回值out的存储过程
Create or replace procedure p_get_emp(var_deptno int, var_total out int) as Var_n int; Begin Select count(*) into var_n from emp where deptno = var_deptno; Var_total := var_n; --总数由参数返回 End;
--使用 Declare Var_s int; Begin p_get_emp(10, var_s); Dbms_output.put_line('返回的值为' || var_s); End;
--定义一个存储过程，通过该存储过程能返回一个结果集（游标）。
Create or replace procedure p_get_datas(mycur out sys_refcursor) is Begin Open mycur for select * from emp where deptno = 10; End;
--调用： Declare Var_cur sys_refcursor; --接收参数 Row emp%rowtype; Begin p_get_datas(var_cur); --无需再次打开，因为在存储过程中已经打开过了 Loop Fetch var_cur into row; Exit when var_cur%notfound; Dbms_output.put_line(row.ename || ' ' || row.job); End loop; End;
--输入输出参数
--根据员工编号返回他的工资的存储过程 Create or replace procedure p_get_sal(var_n in out int) is Begin Select sal into var_n from emp where empno = var_n); End;
--调用： Declare Var_s int; Begin Var_s := &n; p_get_sal(var_s); Dbms_output.put_line('他的工资为：' || var_s); End;
存储过程的使用
--存储过程的使用
1 命令方式：execute 存储过程名;
2 在PL/SQL中：直接使用 存储过程名 即可
调用存储过程时传递参数的方式。
1、按照位置方式传递。
Swap(num1,num2);
2、按名称方式传递。
swap(p2=>num2,p1=>num1);
(p1,p2是定义存储过程时参数名字)
将过程的执行权限授予其他用户：
GRANT EXECUTE ON find_emp TO MARTIN; GRANT EXECUTE ON swap TO PUBLIC;(所有数据库用户)
函数
函数是可以返回值的命名的 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;
定义函数的限制：
函数只能接受 IN 参数，而不能接受 IN OUT 或 OUT 参数
形参不能是 PL/SQL 类型
函数的返回类型也必须是数据库类型
访问函数的两种方式：
使用 PL/SQL 块
使用 SQL 语句
创建函数
CREATE OR REPLACE FUNCTION fun_hello RETURN VARCHAR2 IS BEGIN RETURN '朋友，您好'; END;
从 SQL 语句调用函数：
SELECT fun_hello FROM DUAL;
例
CREATE OR REPLACE FUNCTION item_price_range (price NUMBER) RETURN VARCHAR2 AS min_price NUMBER; max_price NUMBER; BEGIN SELECT MAX(ITEMRATE), MIN(ITEMRATE) INTO max_price, min_price FROM itemfile; IF price >= min_price AND price <= max_price THEN RETURN '输入的单价介于最低价与最高价之间'; ELSE RETURN '超出范围'; END IF; END;
DECLARE P NUMBER := 300; MSG VARCHAR2(200); BEGIN MSG := item_price_range(300); DBMS_OUTPUT.PUT_LINE(MSG); END;
过程和函数的比较
|
过 程 |
函 数 |
|
作为 PL/SQL 语句执行 |
作为表达式的一部分调用 |
|
在规格说明中不包含 RETURN 子句 |
必须在规格说明中包含 RETURN 子句 |
|
不返回任何值 |
必须返回单个值 |
|
可以包含 RETURN 语句，但是与函数不同，它不能用于返回值 |
必须包含至少一条 RETURN |
程序包
程序包是对相关过程、函数、变量、游标和异常等对象的封装，程序包由规范和主体两部分组成。
程序包规范
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];
例
CREATE OR REPLACE PACKAGE pack_me IS PROCEDURE order_proc (orno VARCHAR2); FUNCTION order_fun(ornos VARCHAR2) RETURN VARCHAR2; END pack_me;
CREATE OR REPLACE PACKAGE BODY pack_me AS PROCEDURE order_proc (orno VARCHAR2) IS stat CHAR(1); BEGIN 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;
程序包的优点
- 模块化
- 更轻松的应用程序设计
- 信息隐藏
- 新增功能(过程可以重载，可以定义公用变量或游标)
- 性能更佳
程序包中的游标
游标的定义分为游标规范和游标主体两部分。在包规范中声明游标规范时必须使用 RETURN 子句指定游标的返回类型。
RETURN子句指定的数据类型可以是：
1.用 %ROWTYPE 属性引用表定义的记录类型
2.程序员定义的记录类型
CREATE OR REPLACE PACKAGE cur_pack IS CURSOR ord_cur(vcode VARCHAR2) RETURN order_master%ROWTYPE; PROCEDURE ord_pro(vcode VARCHAR2); END cur_pack;
CREATE OR REPLACE PACKAGE BODY cur_pack AS CURSOR ord_cur(vcode VARCHAR2) RETURN order_master%ROWTYPE IS SELECT * FROM order_master WHERE VENCODE=vcode; PROCEDURE ord_pro(vcode VARCHAR2) IS or_rec order_master%ROWTYPE; BEGIN 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;
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='TEST';
