oracle 子过程 块,Oracle学习笔记十二 子程序（存储过程、自定函数）跟程序包

Oracle学习笔记十二 子程序(存储过程、自定函数)和程序包

子程序

子程序：命名的 PL/SQL 块，编译并存储在数据库中。

子程序的各个部分：

1.声明部分

2.可执行部分

3.异常处理部分(可选)

子程序的分类：

1.过程 － 执行某些操作

2.函数 － 执行操作并返回值

子程序的优点：

模块化

将程序分解为逻辑模块

可重用性

可以被任意数目的程序调用

可维护性

简化维护操作

安全性

通过设置权限，使数据更安全

存储过程

过程是用于完成特定任务的子程序。

例如：

一个购票过程可以分为很多个子过程，分别完成。

创建存储过程

创建过程的语法：

CREATE [OR REPLACE] PROCEDURE

[()]

IS|AS

BEGIN

[EXCEPTION

]

END;

CREATE OR REPLACE PROCEDUREfind_emp

(emp_noNUMBER)ASempnameVARCHAR2(20);BEGIN

SELECT ename INTOempnameFROM EMP WHERE empno =emp_no;

DBMS_OUTPUT.PUT_LINE('雇员姓名是'||empname);

EXCEPTIONWHEN NO_DATA_FOUND THENDBMS_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) isVar_countint;Begin

Select count(*) into var_count from emp where sal >var_sal;

Dbms_output.put_line('符合要求的员工总数为：' ||var_count);

ExceptionWhen others thenDbms_output.put_line('未知错误');End;

--定义一个存储过程返回指定部门的员工总数:带返回值out的存储过程

Create or replace procedure p_get_emp(var_deptno int, var_total out int) asVar_nint;Begin

Select count(*) into var_n from emp where deptno =var_deptno;

Var_total := var_n; --总数由参数返回

End;

--使用

DeclareVar_sint;Beginp_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;

--调用：

DeclareVar_cur sys_refcursor;--接收参数

Row emp%rowtype;Beginp_get_datas(var_cur);--无需再次打开，因为在存储过程中已经打开过了

LoopFetch var_cur intorow;Exit when var_cur%notfound;

Dbms_output.put_line(row.ename|| ' ' ||row.job);Endloop;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;

--调用：

DeclareVar_sint;BeginVar_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 TOMARTIN;GRANT EXECUTE ON swap TO PUBLIC;(所有数据库用户)

函数

函数是可以返回值的命名的 PL/SQL 子程序。

创建函数的语法：

CREATE [OR REPLACE] FUNCTION

[(param1,param2)]

RETURN IS|AS

[local declarations]

BEGINExecutable Statements;RETURNresult;

EXCEPTION

Exception handlers;END;

定义函数的限制：

函数只能接受 IN 参数，而不能接受 IN OUT 或 OUT 参数

形参不能是 PL/SQL 类型

函数的返回类型也必须是数据库类型

访问函数的两种方式：

使用 PL/SQL 块

使用 SQL 语句

创建函数

CREATE OR REPLACE FUNCTIONfun_helloRETURN VARCHAR2

IS

BEGIN

RETURN '朋友，您好';END;

从 SQL 语句调用函数：

SELECT fun_hello FROM DUAL;

例

CREATE OR REPLACE FUNCTIONitem_price_range (priceNUMBER)RETURN VARCHAR2 ASmin_priceNUMBER;

max_priceNUMBER;BEGIN

SELECT MAX(ITEMRATE), MIN(ITEMRATE)INTOmax_price, min_priceFROMitemfile;IF price >= min_price AND price <=max_priceTHEN

RETURN '输入的单价介于最低价与最高价之间';ELSE

RETURN '超出范围';END IF;END;

DECLAREPNUMBER := 300;

MSGVARCHAR2(200);BEGINMSG := item_price_range(300);

DBMS_OUTPUT.PUT_LINE(MSG);END;

过程和函数的比较

过 程

函  数

作为 PL/SQL 语句执行

作为表达式的一部分调用

在规格说明中不包含  RETURN 子句

必须在规格说明中包含 RETURN 子句

不返回任何值

必须返回单个值

可以包含 RETURN 语句，但是与函数不同，它不能用于返回值

必须包含至少一条 RETURN

语句

程序包

程序包是对相关过程、函数、变量、游标和异常等对象的封装，程序包由规范和主体两部分组成。

程序包规范

CREATE [OR REPLACE]PACKAGE

package_nameIS|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 REPLACEPACKAGE 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) ISstatCHAR(1);BEGIN

SELECT ostatus INTO stat FROMorder_masterWHERE orderno =orno;

……ENDorder_proc;FUNCTION order_fun(ornos VARCHAR2)RETURN VARCHAR2

ISicodeVARCHAR2(5);

ocodeVARCHAR2(5);BEGIN……ENDorder_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) ISor_rec order_master%ROWTYPE;BEGIN

OPENord_cur(vcode);

LOOPFETCH ord_cur INTOor_rec;EXIT WHEN ord_cur%NOTFOUND;

DBMS_OUTPUT.PUT_LIne('返回的值为' ||or_rec.orderno);ENDLOOP;ENDord_pro;END cur_pack;

USER_OBJECTS 视图包含用户创建的子程序和程序包的信息

SELECT object_name, object_typeFROMUSER_OBJECTSWHERE object_type IN ('PROCEDURE', 'FUNCTION','PACKAGE', 'PACKAGE BODY');

USER_SOURCE 视图存储子程序和程序包的源代码

SELECT line, text FROM USER_SOURCE WHERE NAME='TEST';