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 存储过程名;

过程参数的三种模式：

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) isBegin    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  VARCHAR2ISBEGIN  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][BEGINInitialization]END [package_name];

例

CREATE OR REPLACE PACKAGE pack_meIS  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';