csdn游标，存储过程

最新推荐文章于 2024-09-12 21:47:45 发布

lifei2199

最新推荐文章于 2024-09-12 21:47:45 发布

阅读量912

点赞数

文章标签：存储 exception sql insert 数据库 oracle

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 获得游标提取的行

数？）

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

oracle 隐式游标,显示游标,游标循环,动态SELECT语句和动态游标,异常处理,自定义异常

oracle 隐式游标,显示游标,游标循环,动态SELECT语句和动态游标,异常处理,自定义异常【转载】
游标的概念:
游标是SQL的一个内存工作区，由系统或用户以变量的形式定义。游标的作用就是用于临时存储从数据库中

提取的数据块。在某些情况下，需要把数据从存放在磁盘的表中调到计算机内存中进行处理，最后将处理结果

显示出来或最终写回数据库。这样数据处理的速度才会提高，否则频繁的磁盘数据交换会降低效率。
游标有两种类型：显式游标和隐式游标。在前述程序中用到的SELECT...INTO...查询语句，一次只能从数据库

中提取一行数据，对于这种形式的查询和DML操作，系统都会使用一个隐式游标。但是如果要提取多行数据，

就要由程序员定义一个显式游标，并通过与游标有关的语句进行处理。显式游标对应一个返回结果为多行多列

的SELECT语句。
游标一旦打开，数据就从数据库中传送到游标变量中，然后应用程序再从游标变量中分解出需要的数据，并进

行处理。
隐式游标
如前所述，DML操作和单行SELECT语句会使用隐式游标，它们是：
* 插入操作：INSERT。
* 更新操作：UPDATE。
* 删除操作：DELETE。
* 单行查询操作：SELECT ... INTO ...。
当系统使用一个隐式游标时，可以通过隐式游标的属性来了解操作的状态和结果，进而控制程序的流程。隐式

游标可以使用名字SQL来访问，但要注意，通过SQL游标名总是只能访问前一个DML操作或单行SELECT操作的游

标属性。所以通常在刚刚执行完操作之后，立即使用SQL游标名来访问属性。游标的属性有四种，如下所示。

Sql代码
隐式游标的属性返回值类型   意    义
SQL%ROWCOUNT    整型代表DML语句成功执行的数据行数
SQL%FOUND   布尔型值为TRUE代表插入、删除、更新或单行查询操作成功
SQL%NOTFOUND    布尔型与SQL%FOUND属性返回值相反
SQL%ISOPEN 布尔型 DML执行过程中为真，结束后为假

【训练1】　使用隐式游标的属性，判断对雇员工资的修改是否成功。
步骤1：输入和运行以下程序：

Sql代码
SET SERVEROUTPUT ON
        BEGIN
        UPDATE emp SET sal=sal+100 WHERE empno=1234;
         IF SQL%FOUND THEN
        DBMS_OUTPUT.PUT_LINE('成功修改雇员工资！');
        COMMIT;
        ELSE
        DBMS_OUTPUT.PUT_LINE('修改雇员工资失败！');
         END IF;
        END;

运行结果为：

Sql代码
修改雇员工资失败！
PL/SQL 过程已成功完成。

步骤2：将雇员编号1234改为7788，重新执行以上程序：
运行结果为：

Sql代码
成功修改雇员工资！
PL/SQL 过程已成功完成。

说明：本例中，通过SQL%FOUND属性判断修改是否成功，并给出相应信息。
显式游标
游标的定义和操作
游标的使用分成以下4个步骤。
1．声明游标
在DECLEAR部分按以下格式声明游标：
CURSOR 游标名[(参数1 数据类型[，参数2 数据类型...])]
IS SELECT语句;
参数是可选部分，所定义的参数可以出现在SELECT语句的WHERE子句中。如果定义了参数，则必须在打开游标时

传递相应的实际参数。
SELECT语句是对表或视图的查询语句，甚至也可以是联合查询。可以带WHERE条件、ORDER BY或GROUP BY等子句

，但不能使用INTO子句。在SELECT语句中可以使用在定义游标之前定义的变量。
2．打开游标
在可执行部分，按以下格式打开游标：
OPEN 游标名[(实际参数1[，实际参数2...])];
打开游标时，SELECT语句的查询结果就被传送到了游标工作区。
3．提取数据
在可执行部分，按以下格式将游标工作区中的数据取到变量中。提取操作必须在打开游标之后进行。
FETCH 游标名 INTO 变量名1[，变量名2...];
或
FETCH 游标名 INTO 记录变量;
游标打开后有一个指针指向数据区，FETCH语句一次返回指针所指的一行数据，要返回多行需重复执行，可以使

用循环语句来实现。控制循环可以通过判断游标的属性来进行。
下面对这两种格式进行说明：
第一种格式中的变量名是用来从游标中接收数据的变量，需要事先定义。变量的个数和类型应与SELECT语句中

的字段变量的个数和类型一致。
第二种格式一次将一行数据取到记录变量中，需要使用%ROWTYPE事先定义记录变量，这种形式使用起来比较方

便，不必分别定义和使用多个变量。
定义记录变量的方法如下：
变量名表名|游标名%ROWTYPE；
其中的表必须存在，游标名也必须先定义。
4．关闭游标
CLOSE 游标名;
显式游标打开后，必须显式地关闭。游标一旦关闭，游标占用的资源就被释放，游标变成无效，必须重新打开

才能使用。
以下是使用显式游标的一个简单练习。
【训练1】用游标提取emp表中7788雇员的名称和职务。

Sql代码
SET SERVEROUTPUT ON
        DECLARE
         v_ename VARCHAR2(10);
         v_job VARCHAR2(10);
         CURSOR emp_cursor IS
         SELECT ename,job FROM emp WHERE empno=7788;
         BEGIN
     OPEN emp_cursor;
    FETCH emp_cursor INTO v_ename,v_job;
        DBMS_OUTPUT.PUT_LINE(v_ename||','||v_job);
        CLOSE emp_cursor;
        END;

执行结果为:

Sql代码
SCOTT,ANALYST
PL/SQL 过程已成功完成。

说明：该程序通过定义游标emp_cursor，提取并显示雇员7788的名称和职务。
作为对以上例子的改进，在以下训练中采用了记录变量。
【训练2】用游标提取emp表中7788雇员的姓名、职务和工资。

Sql代码
SET SERVEROUTPUT ON
        DECLARE
         CURSOR emp_cursor IS SELECT ename,job,sal FROM emp WHERE empno=7788;
         emp_record emp_cursor%ROWTYPE;
        BEGIN
OPEN emp_cursor;
        FETCH emp_cursor INTO emp_record;
           DBMS_OUTPUT.PUT_LINE(emp_record.ename||','|| emp_record.job||','|| emp_record.sal);
         CLOSE emp_cursor;
        END;

执行结果为:

Sql代码
SCOTT,ANALYST,3000
PL/SQL 过程已成功完成。

说明：实例中使用记录变量来接收数据，记录变量由游标变量定义，需要出现在游标定义之后。
注意：可通过以下形式获得记录变量的内容：
记录变量名.字段名。
【训练3】显示工资最高的前3名雇员的名称和工资。

Sql代码
SET SERVEROUTPUT ON
        DECLARE
         V_ename VARCHAR2(10);
        V_sal NUMBER(5);
        CURSOR emp_cursor IS SELECT ename,sal FROM emp ORDER BY sal DESC;
        BEGIN
         OPEN emp_cursor;
         FOR I IN 1..3 LOOP
           FETCH emp_cursor INTO v_ename,v_sal;
         DBMS_OUTPUT.PUT_LINE(v_ename||','||v_sal);
          END LOOP;
         CLOSE emp_cursor;
         END;

执行结果为:

Sql代码
KING,5000
     SCOTT,3000
     FORD,3000
     PL/SQL 过程已成功完成。

说明：该程序在游标定义中使用了ORDER BY子句进行排序，并使用循环语句来提取多行数据。
游标循环
【训练1】使用特殊的FOR循环形式显示全部雇员的编号和名称。

Sql代码
SET SERVEROUTPUT ON
DECLARE
CURSOR emp_cursor IS
SELECT empno, ename FROM emp;
BEGIN
FOR Emp_record IN emp_cursor LOOP
    DBMS_OUTPUT.PUT_LINE(Emp_record.empno|| Emp_record.ename);
    END LOOP;
    END;

执行结果为：

Sql代码
7369SMITH
7499ALLEN
7521WARD
7566JONES
PL/SQL 过程已成功完成。

说明：可以看到该循环形式非常简单，隐含了记录变量的定义、游标的打开、提取和关闭过程。Emp_record

为隐含定义的记录变量，循环的执行次数与游标取得的数据的行数相一致。
【训练2】另一种形式的游标循环。

Sql代码
SET SERVEROUTPUT ON
BEGIN
FOR re IN (SELECT ename FROM EMP) LOOP
DBMS_OUTPUT.PUT_LINE(re.ename)
END LOOP;
END;

执行结果为：

Sql代码
SMITH
ALLEN
WARD
JONES

说明：该种形式更为简单，省略了游标的定义，游标的SELECT查询语句在循环中直接出现。
显式游标属性
虽然可以使用前面的形式获得游标数据，但是在游标定义以后使用它的一些属性来进行结构控制是一种更为灵

活的方法。显式游标的属性如下所示。

Sql代码
游标的属性   返回值类型   意    义
%ROWCOUNT   整型获得FETCH语句返回的数据行数
%FOUND 布尔型最近的FETCH语句返回一行数据则为真，否则为假
%NOTFOUND   布尔型与%FOUND属性返回值相反
%ISOPEN 布尔型游标已经打开时值为真，否则为假

可按照以下形式取得游标的属性：
游标名%属性
要判断游标emp_cursor是否处于打开状态，可以使用属性emp_cursor%ISOPEN。如果游标已经打开，则返回值为

“真”，否则为“假”。具体可参照以下的训练。
【训练1】使用游标的属性练习。

Sql代码
SET SERVEROUTPUT ON
DECLARE
V_ename VARCHAR2(10);
CURSOR emp_cursor IS
SELECT ename FROM emp;
BEGIN
OPEN emp_cursor;
IF emp_cursor%ISOPEN THEN
LOOP
   FETCH emp_cursor INTO v_ename;
   EXIT WHEN emp_cursor%NOTFOUND;
   DBMS_OUTPUT.PUT_LINE(to_char(emp_cursor%ROWCOUNT)||'-'||v_ename);
END LOOP;
ELSE
DBMS_OUTPUT.PUT_LINE('用户信息：游标没有打开！');
END IF;
CLOSE emp_cursor;
END;

执行结果为：

Sql代码
1-SMITH
2-ALLEN
3-WARD
PL/SQL 过程已成功完成。

说明：本例使用emp_cursor%ISOPEN判断游标是否打开；使用emp_cursor%ROWCOUNT获得到目前为止FETCH语

句返回的数据行数并输出；使用循环来获取数据，在循环体中使用FETCH语句；使用emp_cursor%NOTFOUND判断

FETCH语句是否成功执行，当 FETCH语句失败时说明数据已经取完，退出循环。
【练习1】去掉OPEN emp_cursor;语句，重新执行以上程序。
游标参数的传递
【训练1】带参数的游标。

Sql代码
SET SERVEROUTPUT ON
        DECLARE
            V_empno NUMBER(5);
            V_ename VARCHAR2(10);
            CURSOR emp_cursor(p_deptno NUMBER,     p_job VARCHAR2) IS
            SELECT empno, ename FROM emp
            WHERE   deptno = p_deptno AND job = p_job;
BEGIN
     OPEN emp_cursor(10, 'CLERK');
    LOOP
     FETCH emp_cursor INTO v_empno,v_ename;
     EXIT WHEN emp_cursor%NOTFOUND;
     DBMS_OUTPUT.PUT_LINE(v_empno||','||v_ename);
      END LOOP;
    END;

执行结果为：

Sql代码
7934,MILLER
PL/SQL 过程已成功完成。

说明：游标emp_cursor定义了两个参数：p_deptno代表部门编号，p_job代表职务。语句OPEN emp_cursor(10,

'CLERK')传递了两个参数值给游标，即部门为10、职务为CLERK，所以游标查询的内容是部门10的职务为CLERK

的雇员。循环部分用于显示查询的内容。
【练习1】修改Open语句的参数：部门号为20、职务为ANALYST，并重新执行。
也可以通过变量向游标传递参数，但变量需要先于游标定义，并在游标打开之前赋值。对以上例子重新改动如

下：
【训练2】通过变量传递参数给游标。

Sql代码
SET SERVEROUTPUT ON
        DECLARE
        v_empno NUMBER(5);
        v_ename VARCHAR2(10);
        v_deptno NUMBER(5);
v_job VARCHAR2(10);
         CURSOR emp_cursor IS
            SELECT empno, ename FROM emp
            WHERE   deptno = v_deptno AND job = v_job;
        BEGIN
         v_deptno:=10;
         v_job:='CLERK';
         OPEN emp_cursor;
        LOOP
         FETCH emp_cursor INTO v_empno,v_ename;
           EXIT WHEN emp_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_empno||','||v_ename);
         END LOOP;
        END;

执行结果为：

Sql代码
7934,MILLER
PL/SQL 过程已成功完成。

说明：该程序与前一程序实现相同的功能。
动态SELECT语句和动态游标的用法
Oracle支持动态SELECT语句和动态游标，动态的方法大大扩展了程序设计的能力。
对于查询结果为一行的SELECT语句，可以用动态生成查询语句字符串的方法，在程序执行阶段临时地生成并执

行，语法是：
execute immediate 查询语句字符串 into 变量1[,变量2...];
以下是一个动态生成SELECT语句的例子。
【训练1】动态SELECT查询。

Sql代码
SET SERVEROUTPUT ON
        DECLARE
        str varchar2(100);
        v_ename varchar2(10);
        begin
        str:='select ename from scott.emp where empno=7788';
        execute immediate str into v_ename;
        dbms_output.put_line(v_ename);
        END;

执行结果为：

Sql代码
SCOTT
PL/SQL 过程已成功完成。

说明：SELECT...INTO...语句存放在STR字符串中，通过EXECUTE语句执行。
在变量声明部分定义的游标是静态的，不能在程序运行过程中修改。虽然可以通过参数传递来取得不同的数据

，但还是有很大的局限性。通过采用动态游标，可以在程序运行阶段随时生成一个查询语句作为游标。要使用

动态游标需要先定义一个游标类型，然后声明一个游标变量，游标对应的查询语句可以在程序的执行过程中动

态地说明。
定义游标类型的语句如下：
TYPE 游标类型名 REF CURSOR;
声明游标变量的语句如下：
游标变量名游标类型名;
在可执行部分可以如下形式打开一个动态游标：
OPEN 游标变量名 FOR 查询语句字符串;
【训练2】按名字中包含的字母顺序分组显示雇员信息。
输入并运行以下程序：

Sql代码
declare
type cur_type is ref cursor;
cur cur_type;
rec scott.emp%rowtype;
str varchar2(50);
letter char:= 'A';
begin
        loop
         str:= 'select ename from emp where ename like ''%'||letter||'%''';
         open cur for str;
         dbms_output.put_line('包含字母'||letter||'的名字：');
          loop
         fetch cur into rec.ename;
         exit when cur%notfound;
        dbms_output.put_line(rec.ename);
end loop;
exit when letter='Z';
letter:=chr(ascii(letter)+1);
end loop;
end;

运行结果为：

Sql代码
包含字母A的名字：
ALLEN
WARD
MARTIN
BLAKE
CLARK
ADAMS
JAMES
包含字母B的名字：
BLAKE
包含字母C的名字：
CLARK
SCOTT

说明：使用了二重循环，在外循环体中，动态生成游标的SELECT语句，然后打开。通过语句letter:=chr

(ascii(letter)+1)可获得字母表中的下一个字母。

异常处理
错误处理
错误处理部分位于程序的可执行部分之后，是由WHEN语句引导的多个分支构成的。错误处理的语法如下：
EXCEPTION
WHEN 错误1[OR 错误2] THEN
语句序列1；
WHEN 错误3[OR 错误4] THEN
语句序列2；
WHEN OTHERS
语句序列n；
END;
其中：
错误是在标准包中由系统预定义的标准错误，或是由用户在程序的说明部分自定义的错误，参见下一节系统预

定义的错误类型。
语句序列就是不同分支的错误处理部分。
凡是出现在WHEN后面的错误都是可以捕捉到的错误，其他未被捕捉到的错误，将在WHEN OTHERS部分进行统一处

理，OTHENS必须是EXCEPTION部分的最后一个错误处理分支。如要在该分支中进一步判断错误种类，可以通过使

用预定义函数SQLCODE( )和SQLERRM( )来获得系统错误号和错误信息。
如果在程序的子块中发生了错误，但子块没有错误处理部分，则错误会传递到主程序中。
下面是由于查询编号错误而引起系统预定义异常的例子。
【训练1】查询编号为1234的雇员名字。

Sql代码
SET SERVEROUTPUT ON
DECLARE
v_name VARCHAR2(10);
BEGIN
   SELECT   ename
   INTO     v_name
   FROM     emp
   WHERE    empno = 1234;
DBMS_OUTPUT.PUT_LINE('该雇员名字为：'|| v_name);
EXCEPTION
WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('编号错误，没有找到相应雇员！');
WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('发生其他错误！');
END;

执行结果为：

Sql代码
编号错误，没有找到相应雇员！
PL/SQL 过程已成功完成。

说明：在以上查询中，因为编号为1234的雇员不存在，所以将发生类型为“NO_DATA_
FOUND”的异常。“NO_DATA_FOUND”是系统预定义的错误类型，EXCEPTION部分下的WHEN语句将捕捉到该异常，

并执行相应代码部分。在本例中，输出用户自定义的错误信息“编号错误，没有找到相应雇员!”。如果发生

其他类型的错误，将执行OTHERS条件下的代码部分，显示“发生其他错误!”。
【训练2】由程序代码显示系统错误。

Sql代码
SET SERVEROUTPUT ON
DECLARE
v_temp NUMBER(5):=1;
BEGIN
v_temp:=v_temp/0;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('发生系统错误！');
    DBMS_OUTPUT.PUT_LINE('错误代码：'|| SQLCODE( ));
    DBMS_OUTPUT.PUT_LINE('错误信息：' ||SQLERRM( ));
        END;

执行结果为：

Sql代码
发生系统错误！
        错误代码：?1476
        错误信息：ORA-01476: 除数为 0
        PL/SQL 过程已成功完成。

说明：程序运行中发生除零错误，由WHEN OTHERS捕捉到，执行用户自己的输出语句显示错误信息，然后正常结

束。在错误处理部分使用了预定义函数SQLCODE( )和SQLERRM( )来进一步获得错误的代码和种类信息。
预定义错误
Oracle的系统错误很多，但只有一部分常见错误在标准包中予以定义。定义的错误可以在EXCEPTION部分通过标

准的错误名来进行判断，并进行异常处理。常见的系统预定义异常如下所示。

Sql代码
错误名称错误代码    错误含义
CURSOR_ALREADY_OPEN ORA_06511   试图打开已经打开的游标
INVALID_CURSOR ORA_01001   试图使用没有打开的游标
DUP_VAL_ON_INDEX    ORA_00001   保存重复值到惟一索引约束的列中
ZERO_DIVIDE ORA_01476   发生除数为零的除法错误
INVALID_NUMBER ORA_01722   试图对无效字符进行数值转换
ROWTYPE_MISMATCH    ORA_06504   主变量和游标的类型不兼容
VALUE_ERROR ORA_06502   转换、截断或算术运算发生错误
TOO_MANY_ROWS   ORA_01422   SELECT…INTO…语句返回多于一行的数据
NO_DATA_FOUND   ORA_01403   SELECT…INTO…语句没有数据返回
TIMEOUT_ON_RESOURCE ORA_00051   等待资源时发生超时错误
TRANSACTION_BACKED_OUT ORA_00060   由于死锁，提交失败
STORAGE_ERROR   ORA_06500   发生内存错误
PROGRAM_ERROR   ORA_06501   发生PL/SQL内部错误
NOT_LOGGED_ON   ORA_01012   试图操作未连接的数据库
LOGIN_DENIED    ORA_01017   在连接时提供了无效用户名或口令

比如，如果程序向表的主键列插入重复值，则将发生DUP_VAL_ON_INDEX错误。
如果一个系统错误没有在标准包中定义，则需要在说明部分定义，语法如下：
错误名 EXCEPTION;
定义后使用PRAGMA EXCEPTION_INIT来将一个定义的错误同一个特别的Oracle错误代码相关联，就可以同系统预

定义的错误一样使用了。语法如下：
PRAGMA EXCEPTION_INIT(错误名，- 错误代码)；
【训练1】定义新的系统错误类型。

Sql代码
SET SERVEROUTPUT ON
        DECLARE
        V_ENAME VARCHAR2(10);
        NULL_INSERT_ERROR EXCEPTION;
        PRAGMA EXCEPTION_INIT(NULL_INSERT_ERROR,-1400);
        BEGIN
        INSERT INTO EMP(EMPNO) VALUES(NULL);
EXCEPTION
WHEN NULL_INSERT_ERROR THEN
    DBMS_OUTPUT.PUT_LINE('无法插入NULL值！');
WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('发生其他系统错误！');
END;

执行结果为：

Sql代码
无法插入NULL值！
PL/SQL 过程已成功完成。

说明：NULL_INSERT_ERROR是自定义异常，同系统错误1400相关联。
自定义异常
程序设计者可以利用引发异常的机制来进行程序设计，自己定义异常类型。可以在声明部分定义新的异常类型

，定义的语法是：
错误名 EXCEPTION;
用户定义的错误不能由系统来触发，必须由程序显式地触发，触发的语法是：
RAISE 错误名；
RAISE也可以用来引发模拟系统错误，比如，RAISE ZERO_DIVIDE将引发模拟的除零错误。
使用RAISE_APPLICATION_ERROR函数也可以引发异常。该函数要传递两个参数，第一个是用户自定义的错误编号

，第二个参数是用户自定义的错误信息。使用该函数引发的异常的编号应该在20 000和20 999之间选择。
自定义异常处理错误的方式同前。
【训练1】插入新雇员，限定插入雇员的编号在7000～8000之间。

Java代码
SET SERVEROUTPUT ON
DECLARE
new_no NUMBER(10);
new_excp1 EXCEPTION;
new_excp2 EXCEPTION;
BEGIN
new_no:=6789;
INSERT INTO emp(empno,ename)
VALUES(new_no, '小郑');
IF new_no<7000 THEN
    RAISE new_excp1;
END IF;
IF new_no>8000 THEN
    RAISE new_excp2;
END IF;
COMMIT;
EXCEPTION
WHEN new_excp1 THEN
    ROLLBACK;
    DBMS_OUTPUT.PUT_LINE('雇员编号小于7000的下限！');
    WHEN new_excp2 THEN
    ROLLBACK;
    DBMS_OUTPUT.PUT_LINE('雇员编号超过8000的上限！');
    END;

执行结果为：
雇员编号小于7000的下限！
PL/SQL 过程已成功完成。
说明：在此例中，自定义了两个异常：new_excp1和new_excp2，分别代表编号小于7000和编号大于8000的错误

。在程序中通过判断编号大小，产生对应的异常，并在异常处理部分回退插入操作，然后显示相应的错误信息

。
【训练2】使用RAISE_APPLICATION_ERROR函数引发系统异常。

Sql代码
SET SERVEROUTPUT ON
DECLARE
New_no NUMBER(10);
BEGIN
New_no:=6789;
INSERT INTO    emp(empno,ename)
VALUES(new_no, 'JAMES');
IF new_no<7000 THEN
    ROLLBACK;
    RAISE_APPLICATION_ERROR(-20001, '编号小于7000的下限！');
END IF;
IF new_no>8000 THEN
    ROLLBACK;
    RAISE_APPLICATION_ERROR (-20002, '编号大于8000的下限！');
END IF;
END;

执行结果为：

Sql代码
DECLARE
        *
        ERROR 位于第 1 行:
        ORA-20001: 编号小于7000的下限！
        ORA-06512: 在line 9

说明：在本训练中，使用RAISE_APPLICATION_ERROR引发自定义异常，并以系统错误的方式进行显示。错误编

号为20001和20002。
注意：同上一个训练比较，此种方法不需要事先定义异常，可直接引发。
可以参考下面的程序片断将出错信息记录到表中，其中，errors为记录错误信息的表，SQLCODE为发生异常的错

误编号，SQLERRM为发生异常的错误信息。
DECLARE
v_error_code      NUMBER;
v_error_message   VARCHAR2(255);
BEGIN
...
EXCEPTION
...
WHEN OTHERS THEN
    v_error_code := SQLCODE ;
    v_error_message := SQLERRM ;
    INSERT INTO errors
    VALUES(v_error_code, v_error_message);
END;
【练习1】修改雇员的工资，通过引发异常控制修改范围在600～6000之间。
阶段训练
【训练1】将雇员从一个表复制到另一个表。
步骤1：创建一个结构同EMP表一样的新表EMP1：
CREATE TABLE emp1 AS SELECT * FROM SCOTT.EMP WHERE 1=2;
步骤2：通过指定雇员编号，将雇员由EMP表移动到EMP1表：

Sql代码
SET SERVEROUTPUT ON
DECLARE
v_empno NUMBER(5):=7788;
emp_rec emp%ROWTYPE;
BEGIN
SELECT * INTO emp_rec FROM emp WHERE empno=v_empno;
DELETE FROM emp WHERE empno=v_empno;
INSERT INTO emp1 VALUES emp_rec;
IF SQL%FOUND THEN
COMMIT;
DBMS_OUTPUT.PUT_LINE('雇员复制成功！');
ELSE
ROLLBACK;
DBMS_OUTPUT.PUT_LINE('雇员复制失败！');
END IF;
END;

执行结果为：
雇员复制成功！
PL/SQL 过程已成功完成。
步骤2：显示复制结果：
SELECT empno,ename,job FROM emp1;
执行结果为：

Sql代码
EMPNO ENAME JOB
------------- -------------- ----------------
7788 SCOTT ANALYST

说明：emp_rec变量是根据emp表定义的记录变量，SELECT...INTO...语句将整个记录传给该变量。INSERT语句

将整个记录变量插入emp1表，如果插入成功(SQL%FOUND为真)，则提交事务，否则回滚撤销事务。试修改雇员

编号为7902，重新执行以上程序。
【训练2】输出雇员工资，雇员工资用不同高度的*表示。
输入并执行以下程序：

Sql代码
SET SERVEROUTPUT ON
BEGIN
FOR re IN (SELECT ename,sal FROM EMP) LOOP
DBMS_OUTPUT.PUT_LINE(rpad(re.ename,12,' ')||rpad('*',re.sal/100,'*'));
END LOOP;
END;

输出结果为：

Sql代码
SMITH       ********
ALLEN           ****************
WARD        *************
JONES           ******************************
MARTIN      *************
BLAKE       *****************************
CLARK           *****************************
SCOTT           ******************************
KING            **************************************************
TURNER      ***************
ADAMS       ***********
JAMES           **********
FORD            ******************************
MILLER          *************
         执行结果为：
        PL/SQL 过程已成功完成。

说明：第一个rpad函数产生对齐效果，第二个rpad函数根据工资额产生不同数目的*。该程序采用了隐式的简

略游标循环形式。
【训练3】编写程序，格式化输出部门信息。
输入并执行如下程序：

Sql代码
SET SERVEROUTPUT ON
        DECLARE
         v_count number:=0;
         CURSOR dept_cursor IS SELECT * FROM dept;
        BEGIN
          DBMS_OUTPUT.PUT_LINE('部门列表');
DBMS_OUTPUT.PUT_LINE('---------------------------------');
         FOR Dept_record IN dept_cursor LOOP
         DBMS_OUTPUT.PUT_LINE('部门编号：'|| Dept_record.deptno);
         DBMS_OUTPUT.PUT_LINE('部门名称：'|| Dept_record.dname);
            DBMS_OUTPUT.PUT_LINE('所在城市：'|| Dept_record.loc);
DBMS_OUTPUT.PUT_LINE('---------------------------------');
      v_count:= v_count+1;
        END LOOP;
         DBMS_OUTPUT.PUT_LINE('共有'||to_char(v_count)||'个部门！');
        END;

输出结果为：

Sql代码
部门列表
------------------------------------
部门编号：10
部门名称：ACCOUNTING
所在城市：NEW YORK
------------------------------------
部门编号：20
部门名称：RESEARCH
所在城市：DALLAS
...
共有4个部门！
PL/SQL 过程已成功完成。

说明：该程序中将字段内容垂直排列。V_count变量记录循环次数，即部门个数。
【训练4】已知每个部门有一个经理，编写程序，统计输出部门名称、部门总人数、总工资和部门经理。
输入并执行如下程序：

Sql代码
SET SERVEROUTPUT ON
DECLARE
v_deptno number(8);
v_count number(3);
v_sumsal number(6);
v_dname varchar2(15);
v_manager varchar2(15);
CURSOR list_cursor IS
   SELECT deptno,count(*),sum(sal) FROM emp group by deptno;
BEGIN
OPEN list_cursor;
DBMS_OUTPUT.PUT_LINE('----------- 部门统计表 -----------');
DBMS_OUTPUT.PUT_LINE('部门名称   总人数总工资   部门经理');
FETCH list_cursor INTO v_deptno,v_count,v_sumsal;
WHILE list_cursor%found LOOP
SELECT dname INTO v_dname FROM dept
    WHERE deptno=v_deptno;
    SELECT ename INTO v_manager FROM emp
    WHERE deptno=v_deptno and job='MANAGER';
DBMS_OUTPUT.PUT_LINE(rpad(v_dname,13)||rpad(to_char(v_count),8)
      ||rpad(to_char(v_sumsal),9)||v_manager);
    FETCH list_cursor INTO v_deptno,v_count,v_sumsal;
    END LOOP;
        DBMS_OUTPUT.PUT_LINE('--------------------------------------');
        CLOSE list_cursor;
        END;

输出结果为：

Sql代码
-------------------- 部门统计表 -----------------
        部门名称     总人数总工资     部门经理
        ACCOUNTING    3      8750       CLARK
        RESEARCH      5     10875       JONES
        SALES             6      9400       BLAKE
        -------------------------------------------------------------
        PL/SQL 过程已成功完成。

说明：游标中使用到了起分组功能的SELECT语句，统计出各部门的总人数和总工资。再根据部门编号和职务找

到部门的经理。该程序假定每个部门有一个经理。
【训练5】为雇员增加工资，从工资低的雇员开始，为每个人增加原工资的10%，限定所增加的工资总额为800

元，显示增加工资的人数和余额。
输入并调试以下程序：

Sql代码
SET SERVEROUTPUT ON
DECLARE
V_NAME CHAR(10);
V_EMPNO NUMBER(5);
V_SAL NUMBER(8);
V_SAL1 NUMBER(8);
V_TOTAL NUMBER(8) := 800;     --增加工资的总额
V_NUM NUMBER(5):=0;     --增加工资的人数
         CURSOR emp_cursor IS
          SELECT EMPNO,ENAME,SAL FROM EMP ORDER BY SAL ASC;
        BEGIN
         OPEN emp_cursor;
        DBMS_OUTPUT.PUT_LINE('姓名      原工资新工资');
        DBMS_OUTPUT.PUT_LINE('---------------------------');
         LOOP
            FETCH emp_cursor INTO V_EMPNO,V_NAME,V_SAL;
EXIT WHEN emp_cursor%NOTFOUND;
         V_SAL1:= V_SAL*0.1;
            IF V_TOTAL>V_SAL1 THEN
            V_TOTAL := V_TOTAL - V_SAL1;
            V_NUM:=V_NUM+1;
    DBMS_OUTPUT.PUT_LINE(V_NAME||TO_CHAR(V_SAL,'99999')||
        TO_CHAR(V_SAL+V_SAL1,'99999'));
             UPDATE EMP SET SAL=SAL+V_SAL1
             WHERE EMPNO=V_EMPNO;
         ELSE
DBMS_OUTPUT.PUT_LINE(V_NAME||TO_CHAR(V_SAL,'99999')||TO_CHAR(V_SAL,'99999'));
         END IF;
        END LOOP;
        DBMS_OUTPUT.PUT_LINE('---------------------------');
        DBMS_OUTPUT.PUT_LINE('增加工资人数:'||V_NUM||' 剩余工资：'||V_TOTAL);
         CLOSE emp_cursor;
         COMMIT;
         END;

输出结果为：

Sql代码
姓名        原工资新工资
        ---------------------------------------------
SMITH       1289   1418
JAMES       1531   1684
MARTIN      1664   1830
MILLER          1730   1903
ALLEN           1760   1936
ADAMS       1771   1771
TURNER      1815   1815
WARD        1830   1830
BLAKE       2850   2850
CLARK       2850   2850
JONES           2975   2975
FORD            3000   3000
KING            5000   5000
-----------------------------------------------
增加工资人数：5 剩余工资：3
PL/SQL 过程已成功完成。

oracle 存储过程的基本语法及注意事项
分类：数据库 2011-12-26 12:52 10人阅读评论(0) 收藏举报
oracle 存储过程的基本语法及注意事项
oracle 存储过程的基本语法

1.基本结构
CREATE OR REPLACE PROCEDURE 存储过程名字
(
参数1 IN NUMBER,
参数2 IN NUMBER
) IS
变量1 INTEGER :=0;
变量2 DATE;
BEGIN

END 存储过程名字

2.SELECT INTO STATEMENT
将select查询的结果存入到变量中，可以同时将多个列存储多个变量中，必须有一条
记录，否则抛出异常(如果没有记录抛出NO_DATA_FOUND)
例子：
BEGIN
SELECT col1,col2 into 变量1,变量2 FROM typestruct where xxx;
EXCEPTION
WHEN NO_DATA_FOUND THEN
xxxx;
END;
...

3.IF 判断
IF V_TEST=1 THEN
    BEGIN
       do something
    END;
END IF;

4.while 循环
WHILE V_TEST=1 LOOP
BEGIN
XXXX
END;
END LOOP;

5.变量赋值
V_TEST := 123;

6.用for in 使用cursor
...
IS
CURSOR cur IS SELECT * FROM xxx;
BEGIN
FOR cur_result in cur LOOP
BEGIN
V_SUM :=cur_result.列名1+cur_result.列名2
END;
END LOOP;
END;

7.带参数的cursor
CURSOR C_USER(C_ID NUMBER) IS SELECT NAME FROM USER WHERE TYPEID=C_ID;
OPEN C_USER(变量值);
LOOP
FETCH C_USER INTO V_NAME;
EXIT FETCH C_USER%NOTFOUND;
do something
END LOOP;
CLOSE C_USER;

8.用pl/sql developer debug
连接数据库后建立一个Test WINDOW
在窗口输入调用SP的代码,F9开始debug,CTRL+N单步调试

关于oracle存储过程的若干问题备忘
1.在oracle中，数据表别名不能加as，如：

select a.appname from appinfo a;-- 正确
select a.appname from appinfo as a;-- 错误
也许，是怕和oracle中的存储过程中的关键字as冲突的问题吧

2.在存储过程中，select某一字段时，后面必须紧跟into，如果select整个记录，利用游标的话就另当别论了

。

select af.keynode into kn from APPFOUNDATION af where af.appid=aid and af.foundationid=fid;--

有into，正确编译
select af.keynode from APPFOUNDATION af where af.appid=aid and af.foundationid=fid;-- 没有into

，编译报错，提示：Compilation
Error: PLS-00428: an INTO clause is expected in this SELECT statement

3.在利用select...into...语法时，必须先确保数据库中有该条记录，否则会报出"no data found"异常。

可以在该语法之前，先利用select count(*) from 查看数据库中是否存在该记录，如果存在，再利用

select...into...

4.在存储过程中，别名不能和字段名称相同，否则虽然编译可以通过，但在运行阶段会报错

select keynode into kn from APPFOUNDATION where appid=aid and foundationid=fid;-- 正确运行
select af.keynode into kn from APPFOUNDATION af where af.appid=appid and

af.foundationid=foundationid;-- 运行阶段报错，提示
ORA-01422:exact fetch returns more than requested number of rows
5.在存储过程中，关于出现null的问题

假设有一个表A，定义如下：
create table A(
id varchar2(50) primary key not null,
vcount number(8) not null,
bid varchar2(50) not null -- 外键
);如果在存储过程中，使用如下语句：
select sum(vcount) into fcount from A where bid='xxxxxx';如果A表中不存在bid="xxxxxx"的记录，则

fcount=null(即使fcount定义时设置了默认值，如：fcount number(8):=0依然无效，fcount还是会变成null)

，这样以后使用fcount时就可能有问题，所以在这里最好先判断一下：
if fcount is null then
fcount:=0;
end if;这样就一切ok了。

6.Hibernate调用oracle存储过程

        this.pnumberManager.getHibernateTemplate().execute(
                new HibernateCallback() {
                    public Object doInHibernate(Session session)
                            throws HibernateException, SQLException {
                        CallableStatement cs = session
                                .connection()
                                .prepareCall("{call modifyapppnumber_remain(?)}");
                        cs.setString(1, foundationid);
                        cs.execute();
                        return null;
                    }
                })

PL/SQL中读取返回游标的存储过程
分类：数据库 2011-12-26 13:39 19人阅读评论(0) 收藏举报
[sql] view plaincopyprint?create table userTabel(
userid number(10),
username varchar2(100),
constraint PK_USERID PRIMARY KEY(userid)
);
commit;

insert into userTabel values(1,'Albert');
insert into userTabel values(2,'reboot') ;
insert into userTabel values(3,'Jeff');

create or replace package pkg_BB is
-- Author : ADMINISTRATOR
-- Created : 2008-07-17 8:35:52
-- Purpose :

-- Public type declarations
type mycur is ref cursor;
type myrecord is record(
                          myid usertabel.userid%type,
                          myname usertabel.username%type);
procedure pro_GetCur(cur_return out mycur);

end pkg_BB;

create or replace package body pkg_BB is
-- Function and procedure implementations
procedure pro_GetCur(cur_return out mycur)
is
begin
   open cur_return for select * from Usertabel;
end pro_GetCur;
end pkg_BB;
create table userTabel(
userid number(10),
username varchar2(100),
constraint PK_USERID PRIMARY KEY(userid)
);
commit;

insert into userTabel values(1,'Albert');
insert into userTabel values(2,'reboot') ;
insert into userTabel values(3,'Jeff');

create or replace package pkg_BB is
-- Author : ADMINISTRATOR
-- Created : 2008-07-17 8:35:52
-- Purpose :

-- Public type declarations
type mycur is ref cursor;
type myrecord is record(
myid usertabel.userid%type,
myname usertabel.username%type);
procedure pro_GetCur(cur_return out mycur);

end pkg_BB;

create or replace package body pkg_BB is
-- Function and procedure implementations
procedure pro_GetCur(cur_return out mycur)
is
begin
   open cur_return for select * from Usertabel;
end pro_GetCur;
end pkg_BB;
[sql] view plaincopyprint?测试代码：

declare
rec pkg_bb.myrecord;
cur pkg_bb.mycur;
begin
pkg_bb.pro_GetCur(cur);

/*;
close cur;
dbms_output.put_line(rec.myid);
dbms_output.put_line(rec.myname);*/
loop
fetch cur into rec;
exit when cur%notfound;
dbms_output.put_line(rec.myid);
dbms_output.put_line(rec.myname);
end loop;
close cur;
end;

输出结果：
1
Albert
2
reboot
3
Jeff
测试代码：

declare
rec pkg_bb.myrecord;
cur pkg_bb.mycur;
begin
pkg_bb.pro_GetCur(cur);

/*;
close cur;
dbms_output.put_line(rec.myid);
dbms_output.put_line(rec.myname);*/
loop
fetch cur into rec;
exit when cur%notfound;
dbms_output.put_line(rec.myid);
dbms_output.put_line(rec.myname);
end loop;
close cur;
end;

输出结果：
1
Albert
2
reboot
3
Jeff

[sql] view plaincopyprint?fetch strn into rec;
while strn%found loop
         dbms_output.put_line(rec.cpname);
          fetch strn into rec;
   end loop;