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

一、游标

    游标是一种 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 获得游标提取的行数？）

  /* 定义带参数游标 */   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);

1.3、使用显示游标删除或更新记录

定义时：需使用 SELECT ... FOR UPDATE 语句表示事物的锁定；

执行时：需使用 WHERE CURRENT OF curXXX 子句指定游标的当前行。

  /* 定义部分 */

  SELECT name FROM deptment FOR UPDATE;

....

  /* 执行部分 */

  UPDATE deptment SET name=name || '_tt' WHERE CURRENT OF cur;

2、隐式游标

不需声明，打开和关闭的游标。PL/SQL 为所有的 SQL 数据操纵语句隐式声明游标，它是不能直接命名和控制。

  FROM cur IN (SELECT name FROM deptment) LOOP

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;   SELECT ITEMRATE INTO identity     FROM itemFile     WHERE itemcode = value;   IF identity < 200 THEN     value2 := 200;   ELSE     value2 :=50;   END IF;

● 匿名块执行过程

  tvalue2 NUMBER;   test('i202', tvalue2);   DBMS_OUTPUT.PUT_LINE('value2的值为：' || TO_CHAR(value2));

● 单独执行

    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;   SELECT MAX(ITEMRATE), MIN(ITEMRATE) INTO max_price, min_price     FROM itemfile;   IF price >= min_price AND price <= max_price THEN     RETURN '输入的单价介于最低价与最高价之间';     RETURN '超出范围';

● 匿名块执行函数

  p NUMBER := 300;   MSG varchar2(200);   MSG := item_price_range(p);   DBMS_OUTPUT.PUT_LINE(MSG);

● SELECT查询调用（因为函数必须有返回值）

    SELECT myfunction FROM dual;

过程：

    作为 PL/SQL 语句执行；

    在规范中不包含 RETURN 子句；

    不返回任何值（只有输入/输出参数，结果集）；

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

函数：

    作为表达式的一部分调用；

    必须在规范中包含 RETURN 子句；

    必须返回单个值；

    必须包含至少一条 RETURN 语句。

    程序包是一种数据库对象，它是对相关 PL/SQL 类型、子程序、游标、异常、变量和常量的封装。

程序包规范：声明类型、变量、常量、异常、游标和子程序。

程序包主体：用于实现在程序包规范中定义的游标、子程序。

4.1、程序包规范

包含应用程序所需的程序包资源，是与应用程序的接口。

CREATE OR REPLACE PACKAGE pack_me   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   /* 实现定义的存储过程 */   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     icode varchar2(5);     ocode varchar2(5);     qtyord NUMBER;     qtydeld NUMBER;     SELECT qty_ord, qty_deld, itemcode, ordernc INTO qtyord, qtydeld, icode, ocode       FROM order_detail       WHERE orderno = ornos;     IF qtyord < qtydeld THEN       RETURN ocode;       RETURN icode;   END order_fun;

● 要执行 pack_me包中的 order_proc过程，输入

    EXECUTE pack_me.order_proc('o002');

● 要执行包中预定义的函数

  msg varchar2(10);   msg := pack_me.order_fun('o002');   DBMS_OUTPUT.PUT_LINE('值是 ' || msg);

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 程序来读写操作系统文本文件

本文转自 qvodnet 51CTO博客，原文链接:http://blog.51cto.com/bks2015/1982991