ORACLE 游标详解

快来打我* 2022-04-02 10:22 734阅读 0赞

# ORACLE中一般游标和ref游标 #

[https://wenku.baidu.com/view/c37c0073852458fb770b56e3.html?pn=1][https_wenku.baidu.com_view_c37c0073852458fb770b56e3.html_pn_1]

游标标是构建在PL/SQL中，用来查询数据，获取记录集的指针。它让开发者 一次访问结果集中一行记录。 在oracle中提供了两种游标：

## 1 静态游标 ##

## 2 ref游标 ##

静态游标：静态游标是在编译的时候就被确定。然后把结果集复制到内存中 静态游标又分为两种：隐式游标和显示游标。

ref游标：ref游标是在运行的时候加载结果集

先来看看静态游标中的隐式游标 在PL/SQL中为所有的SQL数据操纵语句（包括返回一行的select）隐式声明游标 称为隐式游标。主要原因是用户不能直接命名和控制此类游标。当用户在PL/SQL 中使用数据操纵语句（DML）时，oracle预先定义一个名称为SQL的隐式游标，通过 检查隐式游标的属性获取与最近执行的SQL语句相关信息。 在执行DML语句之后，隐式游标属性返回信息。隐式游标属性包括：   %found   %notfound   %rowcount   %isopen

1 %found 只有DML语句影响一行或多行时，%found属性才返回true

返回值的类型为布尔型，值为TRUE代表插入 删除 更新或单行查询操作成功。

declare num number;

begin update emp set empno=123 where empno=111;

if sql%found then dbms\_output.put\_line('存在记录');

else

dbms\_output.put\_line('不存在记录'); end if; end;

2 %notfound %notfound属性作用正好跟%found属性相反。如果DML语句没有影响任何行数 ，则%notfound属性返回true.

返回值为布尔型,判断游标所在的行是否有效，如果有效，则%FOUNDD等于true，否则等于false,即与%FOUND属性返回值相反。

declare begin delete from emp where empno=111; if sql%notfound then dbms\_output.put\_line('删除失败'); end if; end;

3 %rowcount %rowcount属性返回DML语句影响的行数。如果DML语句没有影响任何行数 ，则%rowcount属性将返回0。

返回值类型为整型，返回当前位置为止游标读取的记录行数,即成功执行的数据行数

declare num number;

begin

update emp set empno=123 where empno=111;

if sql%rowcount=0 then

dbms\_output.put\_line('不存在记录');

else dbms\_output.put\_line('存在记录');

end if;

end;

4 %isopen %isopen属性判断SQL游标是否已经打开。在执行SQL语句之后，oracle自动关闭SQL 游标，所以隐式游标的%isopen属性始终为false.

返回的类型为布尔型,判断游标是否被打开，如果打开%ISOPEN等于true,否则等于false,即执行过程中为真,结束后为假。

在PL/SQL中向标准的select语句增加单独的into子句，就可以将从表或视图中查询 记录赋予变量或行变量。需要注意的是select ..into 语句结果必须有且只能有一行。

如果查询没有返回行，PL/SQL将抛出no\_data\_found异常。如果查询返回多行，则抛出 too\_many\_rows 异常。

如果抛出异常，则停止执行，控制权转移到异常处理部分（没有 异常处理，则程序中断）。

在引发异常时，将不使用属性%found, %notfound, %rowcount来查明DML语句是否 已影响了行数。

declare num number;

begin select empno into num from emp where empno=111;

if sql%rowcount=0 or sql%notfound then dbms\_output.put\_line('不存在记录'); else

dbms\_output.put\_line('存在记录');

end if;

end;

显示游标 显示游标是由用户显示声明的游标。根据在游标中定义的查询，查询返回的行集合可以 包含零行或多行，这些行称为活动集。游标将指向活动集中的当前行。

显示游标的操作过程。

使用显示游标的4个步骤：

（1）声明游标

（2）打开游标

（3）从游标中获取结果集

（4）关闭游标

cursor cursor\_name \[(parameter\[,parameter\])\] \[return return\_type\] is select\_statement; cursor\_name 指游标的名称。

parameter   为游标指定输入参数。

return\_type 定义游标提取行的行类型。

select\_statement 为游标定义查询语句。

open 游标名称 fetch 从游标中提取行 close 关闭游标

1 打开游标，执行游标中定义的查询语句，绑定输入参数，将游标指针指 向结果集的BOF位置。 open cursor\_name \[parameters\]

2   fetch 在打开游标之后，可以从游标中提取记录 fetch cursor\_name into variable\_name; fetch 是提取结果集中一行记录存储在变量中。每次提取之后，结果集指针 就向前移动一行。

3 close 在处理游标中的所有行之后，必须关闭游标，以释放分配给游标的所有资源。

close cursor\_name 用户可以通过检查游标属性来确定游标的当前状态。

显示游标的属性如下：

%found：如果执行最后一条fetch语句，成功返回行，则%found属性为true。

%notfound：如果执行最后一条fetch语句，未能提取行，则%notfound属性为true。

%isopen:如果游标已经打开，则返回true，否则返回false。

%rowcount：返回到目前为止游标提取的行数。%rowcount为数字类型属性。在第一 次获取之前,%rowcount为零。当fetch语句返回一行时，则该数加1。 declare info emp%rowtype;

cursor my\_cur is select \* from emp where empno=111;

begin open my\_cur; dbms\_output.put\_line(my\_cur%rowcount);

loop

if my\_cur%isopen then fetch my\_cur into info;

exit

when my\_cur%notfound;

dbms\_output.put\_line(info.empno);

dbms\_output.put\_line(my\_cur%rowcount);

end if;

end loop;

close my\_cur;

end;

使用显示游标删除或更新 使用游标时，如果处理过程中需要删除或更新。在定义游标查询语句时 必须使用select..for update语句，而在执行delete或update时使用 where current of 子句指定游标当前行。 cursor cursor\_name is select\_statement for update\[of column\] wait/nowait 在使用for update 子句声明游标之后，可以使用以下语法更新行 update table\_name set column\_name=column\_value where current of cursor\_name; update命令中使用的列必须出现在for update of 子句中 select 语句必须只包括一个表，而且delete和update语句只有在打开游标并且提取 特定行之后才能使用。

declare cursor cur\_emp is select \* from emp where sal<2000 for update of sal; num emp%rowtype;

begin open cur\_emp;

loop fetch cur\_emp into num;

exit when cur\_emp%notfound;

update emp set sal=2000 where current of cur\_emp;

end loop;

close cur\_emp;

end;

带参数的显示游标 PL/SQL中允许显示游标接受输入参数。用于声明带参数的显示游标语法

cursor cursor\_name\[<param\_name> data\_type\] \[return <return type>\] is          select\_statement declare dept\_num emp.deptno%type;

emp\_num   emp.empno%type;

emp\_nam emp.ename%type;

cursor emp\_cur(deptparam number) is select empno,ename from emp where deptno=deptparam;

begin dept\_num :=&部门编号; open emp\_cur(dept\_num);

loop fetch emp\_cur into emp\_num,emp\_nam;

exit when emp\_cur%notfound;

dbms\_output.put\_line(emp\_num||' '||emp\_nam); end loop;

close emp\_cur;

end;

可以使用循环游标来简化显示游标。循环游标隐式打开显示游标（不需要open） 自动从结果集提取记录，然后处理完所有记录自动关闭游标。循环游标自动创建 %rowtype类型的变量并将此变量用做记录的索引。 循环游标语法如下： for record\_index in cursor\_name record\_index是PL/SQL自动创建的变量，此变量的属性声明为%rowtype类型。作用 域for循环之内。 循环游标的特性有： 从游标中提取所有记录之后自动关闭游标。 提取和处理游标中每一条记录 提取记录之后%notfound属性为true则退出循环。如果未有结果集，则不进入循环。 declare cursor emp\_cur is select \* from emp; begin for temp in emp\_cur loop dbms\_output.put\_line(temp.ename); end loop; end; 循环游标自动打开，提取，关闭。只适用于静态游标

ref游标

隐式游标和显示游标都是静态定义的。它们在编译的时候结果集就已经被确定。 如果想在运行的时候动态确定结果集，就要使用ref游标和游标变量。

创建ref游标需要两个步骤：

1 声明ref cursor类型

2 声明 ref cursor类型变量。

语法如下： type ref\_cursor\_name is ref cursor \[return record\_type\] 其中，return 用于指定游标提取结果集的返回类型。

有return表示是强类型ref游标， 没有return表示是弱类型的游标。

弱类型游标可以提取任何类型的结果集。

定义游标变量之后，就可以在PL/SQL执行部门打开游标变量

open cursor\_name for select\_statement;

declare type emp\_cur is ref cursor;

my\_cur emp\_cur; num number;

selection varchar(2):='&请输入编号';

begin

if selection='1' then

dbms\_output.put\_line('员工信息');

open my\_cur for select deptno from emp;

elsif selection='2' then

dbms\_output.put\_line('部门信息');

open my\_cur for select deptno from dept;

else

dbms\_output.put\_line('请输入员工信息（1）或门部信息（2）');

end if;

fetch my\_cur into num;

while my\_cur%found    loop

dbms\_output.put\_line(num);

fetch my\_cur into num;

end loop;

close my\_cur;

end;

在PL/SQL中可以执行动态SQL语句，execute immediate 语句只能语句处理返回单行 或没有返回的SQL语句，ref游标则可以处理返回结果集的动态SQL。ref游标的声明 方法与普通ref游标相同，只是在open时指定了动态SQL字符串。

open cursor\_name for dynamic\_select\_string \[using bind\_argument\_list\] declare type sql\_cur is ref cursor; my\_cur sql\_cur;

emp\_info emp%rowtype; sql\_string varchar2(100):='&请输入查询字符串'; begin open my\_cur for sql\_string; loop fetch my\_cur into emp\_info;

exit when my\_cur%notfound;

dbms\_output.put\_line(emp\_info.ename);

end loop;

close my\_cur;

end;

游标变量的特点：

（1）游标变量可以从不同的结果集中提取记录

（2）游标变量可以做为存储过程参数进行传递

（3）游标变量可以引用游标的所有属性

（4）游标变量可以进行赋值运算 使用游标变量也有一定的限制

（1）for update 子句不能与游标变量一起使用

（2）不允许在程序包使用游标变量（可以声明游标类型）

（3）另一台服务器上的子过程不能接受游标变量参数

（4）不能将NULL值赋给游标变量。

（5）游标变量不能使用比较运算符。

（6）数据库中的列不能存储游标变量。

总结：

（1）游标是使用在PL/SQL中，是用来查询数据，获取结果集的指针

（2）游标类型包括隐式游标，显示游标和ref游标。

（3）游标属性包括%found，%notfound，%rowcount，%isopen

（4）PL/SQL自动定义隐式游标，以获取最近执行SQL语句信息。

（5）循环游标简化处理游标中所有行的查询。

（6）在声明ref游标时，不要将它与select 语句相关联。

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

关键字: oracle 隐式 游标 显示 循环 动态 select 语句 游标 异常 处理 自定义

游标的概念:

游标是SQL的一个内存工作区，由系统或用户以变量的形式定义。游标的作用就是用于临时存储从数据库中提取的数据块。在某些情况下，需要把数据从存放在磁盘的表中调到计算机内存中进行处理，最后将处理结果显示出来或最终写回数据库。这样数据处理的速度才会提高，否则频繁的磁盘数据交换会降低效率。

游标有两种类型：显式游标和隐式游标。在前述程序中用到的SELECT...INTO...查询语句，一次只能从数据库中提取一行数据，对于这种形式的查询和DML操作，系统都会使用一个隐式游标。但是如果要提取多行数据，就要由程序员定义一个显式游标，并通过与游标有关的语句进行处理。显式游标对应一个返回结果为多行多列的SELECT语句。

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

隐式游标

如前所述，DML操作和单行SELECT语句会使用隐式游标，它们是：

\* 插入操作：INSERT。

\* 更新操作：UPDATE。

\* 删除操作：DELETE。

\* 单行查询操作：SELECT ... INTO ...。

当系统使用一个隐式游标时，可以通过隐式游标的属性来了解操作的状态和结果，进而控制程序的流程。隐式游标可以使用名字SQL来访问，但要注意，通过SQL游标名总是只能访问前一个DML操作或单行SELECT操作的游标属性。所以通常在刚刚执行完操作之后，立即使用SQL游标名来访问属性。游标的属性有四种，如下所示。

Sql代码

1.隐式游标的属性 返回值类型   意    义

2.SQL%ROWCOUNT    整型  代表DML语句成功执行的数据行数

3.SQL%FOUND   布尔型 值为TRUE代表插入、删除、更新或单行查询操作成功

4.SQL%NOTFOUND    布尔型 与SQL%FOUND属性返回值相反

5.SQL%ISOPEN  布尔型 DML执行过程中为真，结束后为假

隐式游标的属性  返回值类型    意    义

SQL%ROWCOUNT 整型     代表DML语句成功执行的数据行数

SQL%FOUND   布尔型       值为TRUE代表插入、删除、更新或单行查询操作成功

SQL%NOTFOUND 布尔型       与SQL%FOUND属性返回值相反

SQL%ISOPEN   布尔型       DML执行过程中为真，结束后为假

【训练1】　使用隐式游标的属性，判断对雇员工资的修改是否成功。

步骤1：输入和运行以下程序：

Sql代码

1.SET SERVEROUTPUT ON

2.        BEGIN

3.        UPDATE emp SET sal=sal+100 WHERE empno=1234;

4.         IF SQL%FOUND THEN

5.        DBMS\_OUTPUT.PUT\_LINE('成功修改雇员工资！');

6.        COMMIT;

7.        ELSE

8.        DBMS\_OUTPUT.PUT\_LINE('修改雇员工资失败！');

9.         END IF;

10.        END;

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代码

1.修改雇员工资失败！

2.        PL/SQL 过程已成功完成。

修改雇员工资失败！

PL/SQL 过程已成功完成。

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

运行结果为：

Sql代码

1.成功修改雇员工资！

2.        PL/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代码

1.SET SERVEROUTPUT ON

2.        DECLARE

3.         v\_ename VARCHAR2(10);

4.         v\_job VARCHAR2(10);

5.         CURSOR emp\_cursor IS

6.         SELECT ename,job FROM emp WHERE empno=7788;

7.         BEGIN

8.     OPEN emp\_cursor;

9.    FETCH emp\_cursor INTO v\_ename,v\_job;

10.        DBMS\_OUTPUT.PUT\_LINE(v\_ename||','||v\_job);

11.        CLOSE emp\_cursor;

12.        END;

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代码

1.SCOTT,ANALYST

2.        PL/SQL 过程已成功完成。

SCOTT,ANALYST

PL/SQL 过程已成功完成。

说明：该程序通过定义游标emp\_cursor，提取并显示雇员7788的名称和职务。

作为对以上例子的改进，在以下训练中采用了记录变量。

【训练2】  用游标提取emp表中7788雇员的姓名、职务和工资。

Sql代码

1.SET SERVEROUTPUT ON

2.        DECLARE

3.         CURSOR emp\_cursor IS  SELECT ename,job,sal FROM emp WHERE empno=7788;

4.         emp\_record emp\_cursor%ROWTYPE;

5.        BEGIN

6.OPEN emp\_cursor;

7.        FETCH emp\_cursor INTO emp\_record;

8.           DBMS\_OUTPUT.PUT\_LINE(emp\_record.ename||','|| emp\_record.job||','|| emp\_record.sal);

9.         CLOSE emp\_cursor;

10.        END;

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代码

1.SCOTT,ANALYST,3000

2.        PL/SQL 过程已成功完成。

SCOTT,ANALYST,3000

PL/SQL 过程已成功完成。

说明：实例中使用记录变量来接收数据，记录变量由游标变量定义，需要出现在游标定义之后。

注意：可通过以下形式获得记录变量的内容：

记录变量名.字段名。

【训练3】  显示工资最高的前3名雇员的名称和工资。

Sql代码

1.SET SERVEROUTPUT ON

2.        DECLARE

3.         V\_ename VARCHAR2(10);

4.        V\_sal NUMBER(5);

5.        CURSOR emp\_cursor IS  SELECT ename,sal FROM emp ORDER BY sal DESC;

6.        BEGIN

7.         OPEN emp\_cursor;

8.         FOR I IN 1..3 LOOP

9.           FETCH emp\_cursor INTO v\_ename,v\_sal;

10.         DBMS\_OUTPUT.PUT\_LINE(v\_ename||','||v\_sal);

11.          END LOOP;

12.         CLOSE emp\_cursor;

13.         END;

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代码

1.KING,5000

2.     SCOTT,3000

3.     FORD,3000

4.     PL/SQL 过程已成功完成。

KING,5000

SCOTT,3000

FORD,3000

PL/SQL 过程已成功完成。

说明：该程序在游标定义中使用了ORDER BY子句进行排序，并使用循环语句来提取多行数据。

游标循环

【训练1】  使用特殊的FOR循环形式显示全部雇员的编号和名称。

Sql代码

1.SET SERVEROUTPUT ON

2.DECLARE

3.  CURSOR emp\_cursor IS

4.  SELECT empno, ename FROM emp;

5.BEGIN

6.FOR Emp\_record IN emp\_cursor LOOP

7.    DBMS\_OUTPUT.PUT\_LINE(Emp\_record.empno|| Emp\_record.ename);

8.    END LOOP;

9.    END;

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代码

1.7369SMITH

2.7499ALLEN

3.7521WARD

4.7566JONES

5.         PL/SQL 过程已成功完成。

7369SMITH

7499ALLEN

7521WARD

7566JONES

PL/SQL 过程已成功完成。

说明：可以看到该循环形式非常简单，隐含了记录变量的定义、游标的打开、提取和关闭过程。Emp\_record为隐含定义的记录变量，循环的执行次数与游标取得的数据的行数相一致。

【训练2】  另一种形式的游标循环。

Sql代码

1.SET SERVEROUTPUT ON

2.BEGIN

3. FOR re IN (SELECT ename FROM EMP)  LOOP

4.  DBMS\_OUTPUT.PUT\_LINE(re.ename)

5. END LOOP;

6.END;

SET SERVEROUTPUT ON

BEGIN

FOR re IN (SELECT ename FROM EMP)  LOOP

DBMS\_OUTPUT.PUT\_LINE(re.ename)

END LOOP;

END;

执行结果为：

Sql代码

1.SMITH

2.ALLEN

3.WARD

4.JONES

SMITH

ALLEN

WARD

JONES

说明：该种形式更为简单，省略了游标的定义，游标的SELECT查询语句在循环中直接出现。

显式游标属性

虽然可以使用前面的形式获得游标数据，但是在游标定义以后使用它的一些属性来进行结构控制是一种更为灵活的方法。显式游标的属性如下所示。

Sql代码

1.游标的属性   返回值类型   意    义

2.%ROWCOUNT   整型  获得FETCH语句返回的数据行数

3.%FOUND  布尔型 最近的FETCH语句返回一行数据则为真，否则为假

4.%NOTFOUND   布尔型 与%FOUND属性返回值相反

5.%ISOPEN 布尔型 游标已经打开时值为真，否则为假

游标的属性    返回值类型    意    义

%ROWCOUNT 整型     获得FETCH语句返回的数据行数

%FOUND   布尔型       最近的FETCH语句返回一行数据则为真，否则为假

%NOTFOUND  布尔型       与%FOUND属性返回值相反

%ISOPEN   布尔型       游标已经打开时值为真，否则为假

可按照以下形式取得游标的属性：

游标名%属性

要判断游标emp\_cursor是否处于打开状态，可以使用属性emp\_cursor%ISOPEN。如果游标已经打开，则返回值为“真”，否则为“假”。具体可参照以下的训练。

【训练1】  使用游标的属性练习。

Sql代码

1.SET SERVEROUTPUT ON

2.DECLARE

3.  V\_ename VARCHAR2(10);

4.  CURSOR emp\_cursor IS

5.  SELECT ename FROM emp;

6.BEGIN

7. OPEN emp\_cursor;

8. IF emp\_cursor%ISOPEN THEN

9.LOOP

10.   FETCH emp\_cursor INTO v\_ename;

11.   EXIT WHEN emp\_cursor%NOTFOUND;

12.   DBMS\_OUTPUT.PUT\_LINE(to\_char(emp\_cursor%ROWCOUNT)||'-'||v\_ename);

13.  END LOOP;

14. ELSE

15.  DBMS\_OUTPUT.PUT\_LINE('用户信息：游标没有打开！');

16. END IF;

17. CLOSE  emp\_cursor;

18.END;

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.1-SMITH

2.2-ALLEN

3.3-WARD

4. PL/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代码

1.SET SERVEROUTPUT ON

2.        DECLARE

3.            V\_empno NUMBER(5);

4.            V\_ename VARCHAR2(10);

5.            CURSOR  emp\_cursor(p\_deptno NUMBER,     p\_job VARCHAR2) IS

6.            SELECT  empno, ename FROM emp

7.            WHERE   deptno = p\_deptno AND job = p\_job;

8.BEGIN

9.     OPEN emp\_cursor(10, 'CLERK');

10.    LOOP

11.     FETCH emp\_cursor INTO v\_empno,v\_ename;

12.     EXIT WHEN emp\_cursor%NOTFOUND;

13.     DBMS\_OUTPUT.PUT\_LINE(v\_empno||','||v\_ename);

14.      END LOOP;

15.    END;

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代码

1.7934,MILLER

2.        PL/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代码

1.SET SERVEROUTPUT ON

2.        DECLARE

3.        v\_empno NUMBER(5);

4.        v\_ename VARCHAR2(10);

5.        v\_deptno NUMBER(5);

6.v\_job VARCHAR2(10);

7.         CURSOR emp\_cursor IS

8.            SELECT empno, ename FROM emp

9.            WHERE   deptno = v\_deptno AND job = v\_job;

10.        BEGIN

11.         v\_deptno:=10;

12.         v\_job:='CLERK';

13.         OPEN emp\_cursor;

14.        LOOP

15.         FETCH emp\_cursor INTO v\_empno,v\_ename;

16.           EXIT WHEN emp\_cursor%NOTFOUND;

17.DBMS\_OUTPUT.PUT\_LINE(v\_empno||','||v\_ename);

18.         END LOOP;

19.        END;

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代码

1.7934,MILLER

2.        PL/SQL 过程已成功完成。

7934,MILLER

PL/SQL 过程已成功完成。

说明：该程序与前一程序实现相同的功能。

动态SELECT语句和动态游标的用法

Oracle支持动态SELECT语句和动态游标，动态的方法大大扩展了程序设计的能力。

对于查询结果为一行的SELECT语句，可以用动态生成查询语句字符串的方法，在程序执行阶段临时地生成并执行，语法是：

execute immediate 查询语句字符串 into 变量1\[,变量2...\];

以下是一个动态生成SELECT语句的例子。

【训练1】  动态SELECT查询。

Sql代码

1.SET SERVEROUTPUT ON

2.        DECLARE

3.        str varchar2(100);

4.        v\_ename varchar2(10);

5.        begin

6.        str:='select ename from scott.emp where empno=7788';

7.        execute immediate str into v\_ename;

8.        dbms\_output.put\_line(v\_ename);

9.        END;

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代码

1.SCOTT

2.        PL/SQL 过程已成功完成。

SCOTT

PL/SQL 过程已成功完成。

说明：SELECT...INTO...语句存放在STR字符串中，通过EXECUTE语句执行。

在变量声明部分定义的游标是静态的，不能在程序运行过程中修改。虽然可以通过参数传递来取得不同的数据，但还是有很大的局限性。通过采用动态游标，可以在程序运行阶段随时生成一个查询语句作为游标。要使用动态游标需要先定义一个游标类型，然后声明一个游标变量，游标对应的查询语句可以在程序的执行过程中动态地说明。

定义游标类型的语句如下：

TYPE 游标类型名 REF CURSOR;

声明游标变量的语句如下：

游标变量名 游标类型名;

在可执行部分可以如下形式打开一个动态游标：

OPEN 游标变量名 FOR 查询语句字符串;

【训练2】  按名字中包含的字母顺序分组显示雇员信息。

输入并运行以下程序：

Sql代码

1.declare

2. type cur\_type is ref cursor;

3. cur cur\_type;

4. rec scott.emp%rowtype;

5. str varchar2(50);

6. letter char:= 'A';

7.begin

8.        loop

9.         str:= 'select ename from emp where ename like ''%'||letter||'%''';

10.         open cur for str;

11.         dbms\_output.put\_line('包含字母'||letter||'的名字：');

12.          loop

13.         fetch cur into rec.ename;

14.         exit when cur%notfound;

15.        dbms\_output.put\_line(rec.ename);

16.end loop;

17.  exit when letter='Z';

18.  letter:=chr(ascii(letter)+1);

19. end loop;

20.end;

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代码

1.包含字母A的名字：

2.ALLEN

3.WARD

4.MARTIN

5.BLAKE

6.CLARK

7.ADAMS

8.JAMES

9.包含字母B的名字：

10.BLAKE

11.包含字母C的名字：

12.CLARK

13.SCOTT

包含字母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代码

1.SET SERVEROUTPUT ON

2.DECLARE

3.v\_name VARCHAR2(10);

4.BEGIN

5.   SELECT   ename

6.   INTO     v\_name

7.   FROM     emp

8.   WHERE    empno = 1234;

9.DBMS\_OUTPUT.PUT\_LINE('该雇员名字为：'|| v\_name);

10.EXCEPTION

11.  WHEN NO\_DATA\_FOUND THEN

12.    DBMS\_OUTPUT.PUT\_LINE('编号错误，没有找到相应雇员！');

13.  WHEN OTHERS THEN

14.    DBMS\_OUTPUT.PUT\_LINE('发生其他错误！');

15.END;

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代码

1.编号错误，没有找到相应雇员！

2.        PL/SQL 过程已成功完成。

编号错误，没有找到相应雇员！

PL/SQL 过程已成功完成。

说明：在以上查询中，因为编号为1234的雇员不存在，所以将发生类型为“NO\_DATA\_

FOUND”的异常。“NO\_DATA\_FOUND”是系统预定义的错误类型，EXCEPTION部分下的WHEN语句将捕捉到该异常，并执行相应代码部分。在本例中，输出用户自定义的错误信息“编号错误，没有找到相应雇员!”。如果发生其他类型的错误，将执行OTHERS条件下的代码部分，显示“发生其他错误!”。

【训练2】  由程序代码显示系统错误。

Sql代码

1.SET SERVEROUTPUT ON

2.DECLARE

3.v\_temp NUMBER(5):=1;

4.BEGIN

5.v\_temp:=v\_temp/0;

6.EXCEPTION

7.  WHEN OTHERS THEN

8.DBMS\_OUTPUT.PUT\_LINE('发生系统错误！');

9.    DBMS\_OUTPUT.PUT\_LINE('错误代码：'|| SQLCODE( ));

10.    DBMS\_OUTPUT.PUT\_LINE('错误信息：' ||SQLERRM( ));

11.        END;

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代码

1.发生系统错误！

2.        错误代码：?1476

3.        错误信息：ORA-01476: 除数为 0

4.        PL/SQL 过程已成功完成。

发生系统错误！

错误代码：?1476

错误信息：ORA-01476: 除数为 0

PL/SQL 过程已成功完成。

说明：程序运行中发生除零错误，由WHEN OTHERS捕捉到，执行用户自己的输出语句显示错误信息，然后正常结束。在错误处理部分使用了预定义函数SQLCODE( )和SQLERRM( )来进一步获得错误的代码和种类信息。

预定义错误

Oracle的系统错误很多，但只有一部分常见错误在标准包中予以定义。定义的错误可以在EXCEPTION部分通过标准的错误名来进行判断，并进行异常处理。常见的系统预定义异常如下所示。

Sql代码

1.错 误 名 称 错误代码    错 误 含 义

2.CURSOR\_ALREADY\_OPEN ORA\_06511   试图打开已经打开的游标

3.INVALID\_CURSOR  ORA\_01001   试图使用没有打开的游标

4.DUP\_VAL\_ON\_INDEX    ORA\_00001   保存重复值到惟一索引约束的列中

5.ZERO\_DIVIDE ORA\_01476   发生除数为零的除法错误

6.INVALID\_NUMBER  ORA\_01722   试图对无效字符进行数值转换

7.ROWTYPE\_MISMATCH    ORA\_06504   主变量和游标的类型不兼容

8.VALUE\_ERROR ORA\_06502   转换、截断或算术运算发生错误

9.TOO\_MANY\_ROWS   ORA\_01422   SELECT…INTO…语句返回多于一行的数据

10.NO\_DATA\_FOUND   ORA\_01403   SELECT…INTO…语句没有数据返回

11.TIMEOUT\_ON\_RESOURCE ORA\_00051   等待资源时发生超时错误

12.TRANSACTION\_BACKED\_OUT  ORA\_00060   由于死锁，提交失败

13.STORAGE\_ERROR   ORA\_06500   发生内存错误

14.PROGRAM\_ERROR   ORA\_06501   发生PL/SQL内部错误

15.NOT\_LOGGED\_ON   ORA\_01012   试图操作未连接的数据库

16.LOGIN\_DENIED    ORA\_01017   在连接时提供了无效用户名或口令

错 误 名 称  错误代码  错 误 含 义

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     试图操作未连接的数据库

比如，如果程序向表的主键列插入重复值，则将发生DUP\_VAL\_ON\_INDEX错误。

如果一个系统错误没有在标准包中定义，则需要在说明部分定义，语法如下：

错误名 EXCEPTION;

定义后使用PRAGMA EXCEPTION\_INIT来将一个定义的错误同一个特别的Oracle错误代码相关联，就可以同系统预定义的错误一样使用了。语法如下：

PRAGMA EXCEPTION\_INIT(错误名，- 错误代码)；

【训练1】  定义新的系统错误类型。

Sql代码

1.SET SERVEROUTPUT ON

2.        DECLARE

3.        V\_ENAME VARCHAR2(10);

4.        NULL\_INSERT\_ERROR EXCEPTION;

5.        PRAGMA EXCEPTION\_INIT(NULL\_INSERT\_ERROR,-1400);

6.        BEGIN

7.        INSERT INTO EMP(EMPNO) VALUES(NULL);

8.EXCEPTION

9.WHEN NULL\_INSERT\_ERROR THEN

10.    DBMS\_OUTPUT.PUT\_LINE('无法插入NULL值！');

11.  WHEN OTHERS  THEN

12.    DBMS\_OUTPUT.PUT\_LINE('发生其他系统错误！');

13.END;

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代码

1.无法插入NULL值！

2.        PL/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代码

1.SET SERVEROUTPUT ON

2.DECLARE

3.new\_no NUMBER(10);

4.new\_excp1 EXCEPTION;

5.new\_excp2 EXCEPTION;

6.BEGIN

7.new\_no:=6789;

8.INSERT INTO emp(empno,ename)

9.  VALUES(new\_no, '小郑');

10.  IF new\_no<7000 THEN

11.    RAISE new\_excp1;

12.  END IF;

13.  IF new\_no>8000 THEN

14.    RAISE new\_excp2;

15.  END IF;

16.  COMMIT;

17.EXCEPTION

18.WHEN new\_excp1  THEN

19.    ROLLBACK;

20.    DBMS\_OUTPUT.PUT\_LINE('雇员编号小于7000的下限！');

21.    WHEN new\_excp2  THEN

22.    ROLLBACK;

23.    DBMS\_OUTPUT.PUT\_LINE('雇员编号超过8000的上限！');

24.    END;

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代码

1.SET SERVEROUTPUT ON

2.DECLARE

3.New\_no NUMBER(10);

4.BEGIN

5.  New\_no:=6789;

6. INSERT INTO    emp(empno,ename)

7.  VALUES(new\_no, 'JAMES');

8.IF new\_no<7000 THEN

9.    ROLLBACK;

10.    RAISE\_APPLICATION\_ERROR(-20001, '编号小于7000的下限！');

11.  END IF;

12.  IF new\_no>8000 THEN

13.    ROLLBACK;

14.    RAISE\_APPLICATION\_ERROR (-20002, '编号大于8000的下限！');

15.  END IF;

16.END;

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代码

1.DECLARE

2.        \*

3.        ERROR 位于第 1 行:

4.        ORA-20001: 编号小于7000的下限！

5.        ORA-06512: 在line 9

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代码

1.SET SERVEROUTPUT ON

2.DECLARE

3.v\_empno NUMBER(5):=7788;

4.emp\_rec emp%ROWTYPE;

5.BEGIN

6. SELECT \* INTO emp\_rec FROM emp WHERE empno=v\_empno;

7. DELETE FROM emp WHERE empno=v\_empno;

8.INSERT INTO emp1 VALUES emp\_rec;

9. IF SQL%FOUND THEN

10.  COMMIT;

11.  DBMS\_OUTPUT.PUT\_LINE('雇员复制成功！');

12. ELSE

13.  ROLLBACK;

14.  DBMS\_OUTPUT.PUT\_LINE('雇员复制失败！');

15. END IF;

16.END;

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代码

1.EMPNO ENAME      JOB

2.------------- -------------- ----------------

3.    7788  SCOTT      ANALYST

EMPNO ENAME      JOB

\-------------- -------------- ----------------

7788  SCOTT      ANALYST

说明：emp\_rec变量是根据emp表定义的记录变量，SELECT...INTO...语句将整个记录传给该变量。INSERT语句将整个记录变量插入emp1表，如果插入成功(SQL%FOUND为真)，则提交事务，否则回滚撤销事务。试修改雇员编号为7902，重新执行以上程序。

【训练2】  输出雇员工资，雇员工资用不同高度的\*表示。

输入并执行以下程序：

Sql代码

1.SET SERVEROUTPUT ON

2.BEGIN

3. FOR re IN (SELECT ename,sal FROM EMP)  LOOP

4.  DBMS\_OUTPUT.PUT\_LINE(rpad(re.ename,12,' ')||rpad('\*',re.sal/100,'\*'));

5. END LOOP;

6.END;

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代码

1.SMITH       \*\*\*\*\*\*\*\*

2.ALLEN           \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*

3.WARD        \*\*\*\*\*\*\*\*\*\*\*\*\*

4.JONES           \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*

5.MARTIN      \*\*\*\*\*\*\*\*\*\*\*\*\*

6.BLAKE       \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*

7.CLARK           \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*

8.SCOTT           \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*

9.KING            \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*

10.TURNER      \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*

11.ADAMS       \*\*\*\*\*\*\*\*\*\*\*

12.JAMES           \*\*\*\*\*\*\*\*\*\*

13.FORD            \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*

14.MILLER          \*\*\*\*\*\*\*\*\*\*\*\*\*

15.         执行结果为：

16.        PL/SQL 过程已成功完成。

SMITH         \*\*\*\*\*\*\*\*

ALLEN              \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*

WARD              \*\*\*\*\*\*\*\*\*\*\*\*\*

JONES              \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*

MARTIN     \*\*\*\*\*\*\*\*\*\*\*\*\*

BLAKE        \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*

CLARK              \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*

SCOTT              \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*

KING               \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*

TURNER     \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*

ADAMS             \*\*\*\*\*\*\*\*\*\*\*

JAMES             \*\*\*\*\*\*\*\*\*\*

FORD              \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*

MILLER      \*\*\*\*\*\*\*\*\*\*\*\*\*

执行结果为：

PL/SQL 过程已成功完成。

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

【训练3】  编写程序，格式化输出部门信息。

输入并执行如下程序：

Sql代码

1.SET SERVEROUTPUT ON

2.        DECLARE

3.         v\_count number:=0;

4.         CURSOR dept\_cursor IS SELECT \* FROM dept;

5.        BEGIN

6.          DBMS\_OUTPUT.PUT\_LINE('部门列表');

7.DBMS\_OUTPUT.PUT\_LINE('---------------------------------');

8.         FOR Dept\_record IN dept\_cursor LOOP

9.         DBMS\_OUTPUT.PUT\_LINE('部门编号：'|| Dept\_record.deptno);

10.         DBMS\_OUTPUT.PUT\_LINE('部门名称：'|| Dept\_record.dname);

11.            DBMS\_OUTPUT.PUT\_LINE('所在城市：'|| Dept\_record.loc);

12.DBMS\_OUTPUT.PUT\_LINE('---------------------------------');

13.      v\_count:= v\_count+1;

14.        END LOOP;

15.         DBMS\_OUTPUT.PUT\_LINE('共有'||to\_char(v\_count)||'个部门！');

16.        END;

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代码

1.部门列表

2.------------------------------------

3.部门编号：10

4.部门名称：ACCOUNTING

5.所在城市：NEW YORK

6.------------------------------------

7.部门编号：20

8.部门名称：RESEARCH

9.所在城市：DALLAS

10....

11.共有4个部门！

12.PL/SQL 过程已成功完成。

部门列表

\------------------------------------

部门编号：10

部门名称：ACCOUNTING

所在城市：NEW YORK

\------------------------------------

部门编号：20

部门名称：RESEARCH

所在城市：DALLAS

...

共有4个部门！

PL/SQL 过程已成功完成。

说明：该程序中将字段内容垂直排列。V\_count变量记录循环次数，即部门个数。

【训练4】  已知每个部门有一个经理，编写程序，统计输出部门名称、部门总人数、总工资和部门经理。

输入并执行如下程序：

Sql代码

1.SET SERVEROUTPUT ON

2.DECLARE

3. v\_deptno number(8);

4. v\_count number(3);

5. v\_sumsal number(6);

6. v\_dname  varchar2(15);

7.v\_manager  varchar2(15);

8. CURSOR list\_cursor IS

9.   SELECT deptno,count(\*),sum(sal) FROM emp group by deptno;

10.BEGIN

11.  OPEN list\_cursor;

12.  DBMS\_OUTPUT.PUT\_LINE('----------- 部 门 统 计 表 -----------');

13.DBMS\_OUTPUT.PUT\_LINE('部门名称   总人数  总工资   部门经理');

14.  FETCH list\_cursor INTO v\_deptno,v\_count,v\_sumsal;

15.  WHILE list\_cursor%found LOOP

16. SELECT dname INTO v\_dname FROM dept

17.    WHERE deptno=v\_deptno;

18.    SELECT ename INTO v\_manager FROM emp

19.    WHERE deptno=v\_deptno and job='MANAGER';

20.DBMS\_OUTPUT.PUT\_LINE(rpad(v\_dname,13)||rpad(to\_char(v\_count),8)

21.      ||rpad(to\_char(v\_sumsal),9)||v\_manager);

22.    FETCH list\_cursor INTO v\_deptno,v\_count,v\_sumsal;

23.    END LOOP;

24.        DBMS\_OUTPUT.PUT\_LINE('--------------------------------------');

25.        CLOSE list\_cursor;

26.        END;

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代码

1.-------------------- 部 门 统 计 表 -----------------

2.        部门名称     总人数  总工资     部门经理

3.        ACCOUNTING    3      8750       CLARK

4.        RESEARCH      5     10875       JONES

5.        SALES             6      9400       BLAKE

6.        -------------------------------------------------------------

7.        PL/SQL 过程已成功完成。

\-------------------- 部 门 统 计 表 -----------------

部门名称     总人数  总工资     部门经理

ACCOUNTING    3      8750        CLARK

RESEARCH     5     10875      JONES

SALES         6      9400            BLAKE

-------------------------------------------------------------

PL/SQL 过程已成功完成。

说明：游标中使用到了起分组功能的SELECT语句，统计出各部门的总人数和总工资。再根据部门编号和职务找到部门的经理。该程序假定每个部门有一个经理。

【训练5】  为雇员增加工资，从工资低的雇员开始，为每个人增加原工资的10%，限定所增加的工资总额为800元，显示增加工资的人数和余额。

输入并调试以下程序：

Sql代码

1.SET SERVEROUTPUT ON

2.DECLARE

3.  V\_NAME CHAR(10);

4.  V\_EMPNO NUMBER(5);

5.  V\_SAL NUMBER(8);

6.  V\_SAL1 NUMBER(8);

7.  V\_TOTAL NUMBER(8) := 800;     --增加工资的总额

8.V\_NUM NUMBER(5):=0;     --增加工资的人数

9.         CURSOR emp\_cursor IS

10.          SELECT EMPNO,ENAME,SAL FROM EMP ORDER BY SAL ASC;

11.        BEGIN

12.         OPEN emp\_cursor;

13.        DBMS\_OUTPUT.PUT\_LINE('姓名      原工资  新工资');

14.        DBMS\_OUTPUT.PUT\_LINE('---------------------------');

15.         LOOP

16.            FETCH emp\_cursor INTO V\_EMPNO,V\_NAME,V\_SAL;

17.EXIT WHEN emp\_cursor%NOTFOUND;

18.         V\_SAL1:= V\_SAL\*0.1;

19.            IF V\_TOTAL>V\_SAL1 THEN

20.            V\_TOTAL := V\_TOTAL - V\_SAL1;

21.            V\_NUM:=V\_NUM+1;

22.    DBMS\_OUTPUT.PUT\_LINE(V\_NAME||TO\_CHAR(V\_SAL,'99999')||

23.        TO\_CHAR(V\_SAL+V\_SAL1,'99999'));

24.             UPDATE EMP SET SAL=SAL+V\_SAL1

25.             WHERE EMPNO=V\_EMPNO;

26.         ELSE

27.DBMS\_OUTPUT.PUT\_LINE(V\_NAME||TO\_CHAR(V\_SAL,'99999')||TO\_CHAR(V\_SAL,'99999'));

28.         END IF;

29.        END LOOP;

30.        DBMS\_OUTPUT.PUT\_LINE('---------------------------');

31.        DBMS\_OUTPUT.PUT\_LINE('增加工资人数:'||V\_NUM||' 剩余工资：'||V\_TOTAL);

32.         CLOSE emp\_cursor;

33.         COMMIT;

34.         END;

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代码

1.姓名        原工资  新工资

2.        ---------------------------------------------

3.SMITH       1289   1418

4.JAMES       1531   1684

5.MARTIN      1664   1830

6.MILLER          1730   1903

7.ALLEN           1760   1936

8.ADAMS       1771   1771

9.TURNER      1815   1815

10.WARD        1830   1830

11.BLAKE       2850   2850

12.CLARK       2850   2850

13.JONES           2975   2975

14.FORD            3000   3000

15.KING            5000   5000

16.-----------------------------------------------

17.增加工资人数：5 剩余工资：3

18.PL/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 过程已成功完成。

【练习1】按部门编号从小到大的顺序输出雇员名字、工资以及工资与平均工资的差。

【练习2】为所有雇员增加工资，工资在1000以内的增加30%，工资在1000～2000之间的增加20%，2000以上的增加10%。

[https://wenku.baidu.com/view/c37c0073852458fb770b56e3.html?pn=1][https_wenku.baidu.com_view_c37c0073852458fb770b56e3.html_pn_1]

[https_wenku.baidu.com_view_c37c0073852458fb770b56e3.html_pn_1]: https://wenku.baidu.com/view/c37c0073852458fb770b56e3.html?pn=1

ORACLE 游标详解

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关 oracle 游标(oracle游标查询)

相关 Oracle游标

相关 Oracle游标使用详解

相关 Oracle-游标

相关 oracle 游标

相关 Oracle游标-提取记录、游标循环

相关 ORACLE 游标详解

相关 Oracle 游标

相关 oracle 游标杂耍

相关 Oracle--PL/SQL游标（光标）详解