Oracle 笔记（八）、PL/SQL 高级应用（游标、存储过程、函数、程序包）

灰太狼 2022-05-19 13:52 203阅读 0赞

[一、游标][Link 1]  
[二、存储过程][Link 2]  
[三、函数][Link 3]  
[过程与函数的异同][Link 4]  
[四、程序包][Link 5]  
  
  
  
**一、游标**  
游标是一种 PL/SQL 控制结构，可以对SQL语句的处理进行显式控制，便于对表的数据逐条进行处理。  
ps.当表中数据量大的时候，不建议使用游标（效率不高，耗费资源），但是它能逐条取数据方法灵活。  
游标是记录的指针，利用游标对活动集的更新或删除会反馈到表的记录上。  
  
游标属性：%FOUND、%NOTFOUND、%ROWCOUNT、%ISOPEN  
  
  
**1、显式游标**  
显式游标是由用户显式声明的游标。根据在游标中定义的查询，查询返回的行集可以包含零或多行，这些行称为活动集。游标将指向活动集中的当前行。  
  
显式游标操纵过程：声明、打开、从游标中获取记录、关闭。

> SET SERVEROUTPUT ON;  
> DECLARE  
> CURSOR cur IS  
> SELECT \* FROM books;  
> myrecord books%ROWTYPE;  
> BEGIN  
> OPEN cur;  
> LOOP  
> FETCH cur INTO books;  
> EXIT WHEN cur%NOTFOUND;  
> DBMS\_OUTPUT.PUT\_LINE(books.id || ' ' || books.name);  
> END LOOP;  
> CLOSE cur;  
> END;

1.1、带参数的显式游标：参数不需指定长度或者精度。  
1.2、FOR 循环游标：采用遍历方式，自动打开、提取和关闭游标。（能否利用 %ROWCOUNT 获得游标提取的行数？）

> DECLARE  
> /\* 定义带参数游标 \*/  
> CURSOR cur\_para(id varchar2) IS  
> SELECT books\_name FROM books WHERE books\_id = id;  
> BGEIN  
> /\* 调用带参数游标，并以 FOR 循环方式处理 \*/  
> FOR cur IN cur\_para('0001') LOOP  
> DBMS\_OUTPUT.PUT\_LINE(cur.books\_id || ' ' || cur.books\_id);  
> END LOOP;  
> END;

1.3、使用显示游标删除或更新记录  
定义时：需使用 SELECT ... FOR UPDATE 语句表示事物的锁定；  
执行时：需使用 WHERE CURRENT OF curXXX 子句指定游标的当前行。  
  
/\* 定义部分 \*/  
CURSOR cur IS  
SELECT name FROM deptment FOR UPDATE;  
....  
/\* 执行部分 \*/  
UPDATE deptment SET name=name || '\_tt' WHERE CURRENT OF cur;  
  
  
**2、隐式游标**  
不需声明，打开和关闭的游标。PL/SQL 为所有的 SQL 数据操纵语句隐式声明游标，它是不能直接命名和控制。

> BEGIN  
> FROM cur IN (SELECT name FROM deptment) LOOP  
> DBMS\_OUTPUT.PUT\_LINE(cur.books\_id || ' ' || cur.books\_id);  
> END LOOP;  
> END;

ps.  
匿名块：每次执行时都需要被编译，并且无法存储到数据库中，别的 PL/SQL 块也无法调用它。  
命名块：存储在数据库中，属于数据库对象。  
  
排错：SHOW ERRORS PROCEDURE/FUNCTION/PACKAGE obj\_name;  
  
  
  
**二、存储过程**

> CREATE OR REPLACE PROCEDURE test (value IN varchar2, value2 OUT NUMBER)  
> /\* 参数，不需指定长度或精度 \*/  
> IS  
> /\* 局部变量，省略 DECLARE 关键字，需有长度 \*/  
> identity NUMBER;  
> BEGIN  
> SELECT ITEMRATE INTO identity  
> FROM itemFile  
> WHERE itemcode = value;  
> IF identity < 200 THEN  
> value2 := 200;  
> ELSE  
> value2 :=50;  
> END IF;  
> END;

● 匿名块执行过程

> DECLARE  
> tvalue2 NUMBER;  
> BEGIN  
> test('i202', tvalue2);  
> DBMS\_OUTPUT.PUT\_LINE('value2的值为：' || TO\_CHAR(value2));  
> END;

● 单独执行  
EXECUTE myproc('0001');  
  
  
  
**三、函数**  
函数的主要特性是它必须返回一个值。创建函数时通过 RETURN 子句指定函数返回值的数据类型。  
函数的一些限制：  
● 函数只能带有 IN 参数，不能带有 IN OUT 或 OUT 参数。  
● 形式参数必须只使用数据库类型，不能使用 PL/SQL 类型。  
● 函数的返回类型必须是数据库类型。

> CREATE OR REPLACE FUNCTION item\_price\_rage (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(p);  
> DBMS\_OUTPUT.PUT\_LINE(MSG);  
> END;

● SELECT查询调用（因为函数必须有返回值）  
SELECT myfunction FROM dual;  
  
  
  
 **过程与函数的异同**  
**过程：**  
作为 PL/SQL 语句执行；  
在规范中不包含 RETURN 子句；  
不返回任何值（只有输入/输出参数，结果集）；  
可以包含 RETURN 语句，但是与函数不同，它不能用于返回值。  
**函数：**  
作为表达式的一部分调用；  
必须在规范中包含 RETURN 子句；  
必须返回单个值；  
必须包含至少一条 RETURN 语句。  
  
  
  
**四、程序包**  
程序包是一种数据库对象，它是对相关 PL/SQL 类型、子程序、游标、异常、变量和常量的封装。  
程序包规范：声明类型、变量、常量、异常、游标和子程序。  
程序包主体：用于实现在程序包规范中定义的游标、子程序。  
  
  
**4.1、程序包规范**  
包含应用程序所需的程序包资源，是与应用程序的接口。

> CREATE OR REPLACE PACKAGE pack\_me  
> IS  
> PROCEDURE order\_proc (orno varchar2);  
> FUNCTION order\_fun (ornos varchar2) RETURN varchar2;  
> END pack\_me;

\*创建 pack\_me 包，并声明了子程序 order\_proc 和 order\_fun，并交由程序包主体实现。  
  
  
**4.2、程序包主体**  
当程序包规范中指定了子程序和游标时，必须有程序包主体。

> CREATE OR REPLACE PACKAGE BODY pack\_me  
> AS  
> /\* 实现定义的存储过程 \*/  
> PROCEDURE order\_proc (orno varchar2)  
> IS  
> stst CHAR(1);  
> BEGIN  
> SELECT ostatus INTO stat FROM order\_master  
> WHERE orderno = orno;  
> IF stat = 'p' THEN  
> DBMS\_OUTPUT.PUT\_LINE('暂挂的订单');  
> ELSE  
> DBMS\_OUTPUT.PUT\_LINE('已完成的订单');  
> END IF;  
> END order\_proc;  
> /\* 实现定义的函数 \*/  
> FUNCTION order\_fun(ornos varchar2) RETURN varchar2  
> IS  
> icode varchar2(5);  
> ocode varchar2(5);  
> qtyord NUMBER;  
> qtydeld NUMBER;  
> BEGIN  
> SELECT qty\_ord, qty\_deld, itemcode, ordernc INTO qtyord, qtydeld, icode, ocode  
> FROM order\_detail  
> WHERE orderno = ornos;  
> IF qtyord < qtydeld THEN  
> RETURN ocode;  
> ELSE  
> RETURN icode;  
> END IF;  
> END order\_fun;  
> END pack\_me;

● 要执行 pack\_me包中的 order\_proc过程，输入  
EXECUTE pack\_me.order\_proc('o002');  
  
● 要执行包中预定义的函数

> DECLARE  
> msg varchar2(10);  
> BEGIN  
> msg := pack\_me.order\_fun('o002');  
> DBMS\_OUTPUT.PUT\_LINE('值是 ' || msg);  
> END;

**4.3、程序包的优点**  
程序包将相关的功能在逻辑上组织在一起，模块化，信息隐藏和更好的性能。  
ps.数据字典视图 USER\_SOURCE 包含存储过程的代码文本。  
  
  
**4.4、内置程序包**  
STANDARD 和 DBMS\_STANDARD：定义和扩展 PL/SQL 语言环境  
DBMS\_LOB：提供对 Oracle LOB 数据类型进行操作的功能  
DBMS\_LOCK：用户定义的锁  
DBMS\_OUTPUT：处理 PL/SQL 块和子程序输出调试信息  
DBMS\_SESSION：提供 ALTER SESSION 命令的 PL/SQL 等效功能  
DBMS\_ROWID：获得 ROWID 的详细信息  
DBMS\_RANDOM：提供随机数生成器  
DBMS\_SQL：允许用户使用动态 SQL，构造和执行任意 DML 或 DDL 语句  
DBMS\_JOB：提交和管理在数据库中执行的定时任务  
DBMS\_XMLDOM：用 DOM 模型读写 XML 类型的数据  
DBMS\_XMLPARSER：XML 解析，处理 XML 文档内容和结构  
DBMS\_XMLGEN：将 SQL 查询结果转换为规范的 XML 格式  
DBMS\_XMLQUERY：提供将数据转换为 XML 类型的功能  
DBMS\_XSLPROCESSOR：提供 XSLT 功能，转换 XML 文档  
UTL\_FILE：用 PL/SQL 程序来读写操作系统文本文件

转载：[http://www.blogjava.net/kiant/articles/236591.html][http_www.blogjava.net_kiant_articles_236591.html]

[Link 1]: http://www.blogjava.net/kiant/articles/236591.html#1
[Link 2]: http://www.blogjava.net/kiant/articles/236591.html#2
[Link 3]: http://www.blogjava.net/kiant/articles/236591.html#3
[Link 4]: http://www.blogjava.net/kiant/articles/236591.html#30
[Link 5]: http://www.blogjava.net/kiant/articles/236591.html#4
[http_www.blogjava.net_kiant_articles_236591.html]: http://www.blogjava.net/kiant/articles/236591.html