Oracle游标使用方法.docx

Oracle游标使用方法.docx

《Oracle游标使用方法.docx》由会员分享，可在线阅读，更多相关《Oracle游标使用方法.docx（75页珍藏版）》请在冰豆网上搜索。

Oracle游标使用方法

Oracle游标使用方法

当查询返回结果超过一行时，就需要一个显式游标，此时用户不能使用selectinto语句。

PL/SQL管理隐式游标，当查询开始时隐式游标打开，查询结束时隐式游标自动关闭。

显式游标在PL/SQL块的声明部分声明，在执行部分或异常处理部分打开，取出数据,关闭。

使用游标语法：

例：

SETSERVERIUTPUTON

DECLARER_empEMP%ROWTYPE;

CURSORc_empISselect*fromemp;

BEGIN

OPENc_emp;

Loop

FETCHc_empintor_emp;

EXITWHENC_EMP%NOTFOUND;

Dbms_output.put_line（\’SalaryofEmployee\’||r_emp.ename||\’is’\）

Endloop;

Closec_emp;

End;

%ROWTYPE也可以用游标名来定义，这样的话就必须要首先声明游标：

Forrecord_nameIN（corsor_name[（parameter[,parameter]…）]

|（query_difinition）loop

Statements

Endloop;

下面我们用for循环重写上面的例子：

在游标FOR循环中使用查询

在游标FOR循环中可以定义查询，由于没有显式声明所以游标没有名字，记录名通过游标查询来定义。

游标中的子查询

语法如下：

可以看出与SQL中的子查询有没什么区别。

游标中的更新和删除

在PL/SQL中依然可以使用UPDATE和DELETE语句更新或删除数据行。

显示游标只有在需要获得多行数据的情况下使用。

PL/SQL提供了仅仅使用游标就可以执行删除或更新记录的方法。

UPDATE或DELETE语句中的WHERECURRENTOF子串专门处理要执行UPDATE或DELETE操作的表中取出的最近的数据。

要使用这个方法，在声明游标时必须使用FORUPDATE子串，当对话使用FORUPDATE子串打开一个游标时，所有返回集中的数据行都将处于行级（ROW-LEVEL）独占式锁定，其他对象只能查询这些数据行，不能进行UPDATE、DELETE或SELECT...FORUPDATE操作。

语法：

在多表查询中，使用OF自居来锁定特定的表，如果忽略了OF子句，那么所有表中选择的数据行都将被锁定。

如果这些数据行已经被其他会话锁定，那么正常情况下oracle将等待，直到数据行解锁。

在UPDATE和DELETE中使用WHERECURRENTOF子串的语法如下：

WHERE{CURRENTOFcursor_name|search_condition}

实例:

DELCARE

CURSORc1IS

SELECTempno,salaryFROMemp

WHEREcommISNULL

FORUPDATEOFcomm;

v_commNUMBER（10,2）;

BEGIN

FORr1INc1LOOP

IFr1.salary<500THEN

v_comm:

=r1.salary*0.25;

ELSEIFr1.salary<1000THEN

v_comm:

=r1.salary*0.20;

ELSEIFr1.salary<3000THEN

v_comm:

=r1.salary*0.15;

ELSEv_comm:

=r1.salary*0.12;

ENDIF;

UPDATEemp

SETcomm=v_comm

WHERECURRENTOFc1;

ENDLOOP;

END

=====================================================

oracle隐式游标,显示游标,游标循环

动态SELECT语句和动态游标,异常处理,自定义异常

游标的概念:

   游标是SQL的一个内存工作区，由系统或用户以变量的形式定义。

游标的作用就是用于临时存储从数据库中提取的数据块。

在某些情况下，需要把数据从存放在磁盘的表中调到计算机内存中进行处理，最后将处理结果显示出来或最终写回数据库。

这样数据处理的速度才会提高，否则频繁的磁盘数据交换会降低效率。

游标有两种类型：

显式游标和隐式游标。

在前述程序中用到的SELECT...INTO...查询语句，一次只能从数据库中提取一行数据，对于这种形式的查询和DML操作，系统都会使用一个隐式游标。

但是如果要提取多行数据，就要由程序员定义一个显式游标，并通过与游标有关的语句进行处理。

显式游标对应一个返回结果为多行多列的SELECT语句。

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

隐式游标：

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

*插入操作：

INSERT。

*更新操作：

UPDATE。

*删除操作：

DELETE。

*单行查询操作：

SELECT...INTO...。

当系统使用一个隐式游标时，可以通过隐式游标的属性来了解操作的状态和结果，进而控制程序的流程。

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

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

游标的属性有四种，如下所示。

Sql代码：

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

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

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

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

4.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;  

SETSERVEROUTPUTON

BEGIN

UPDATEempSETsal=sal+100WHEREempno=1234;

IFSQL%FOUNDTHEN

DBMS_OUTPUT.PUT_LINE（'成功修改雇员工资！

'）;

COMMIT;

ELSE

DBMS_OUTPUT.PUT_LINE（'修改雇员工资失败！

'）;

ENDIF;

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数据类型...]）]

ISSELECT语句;

参数是可选部分，所定义的参数可以出现在SELECT语句的WHERE子句中。

如果定义了参数，则必须在打开游标时传递相应的实际参数。

SELECT语句是对表或视图的查询语句，甚至也可以是联合查询。

可以带WHERE条件、ORDERBY或GROUPBY等子句，但不能使用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;  

SETSERVEROUTPUTON

DECLARE

v_enameVARCHAR2（10）;

v_jobVARCHAR2（10）;

CURSORemp_cursorIS

SELECTename,jobFROMempWHEREempno=7788;

BEGIN

OPENemp_cursor;

FETCHemp_cursorINTOv_ename,v_job;

DBMS_OUTPUT.PUT_LINE（v_ename||','||v_job）;

CLOSEemp_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;  

SETSERVEROUTPUTON

DECLARE

CURSORemp_cursorISSELECTename,job,salFROMempWHEREempno=7788;

emp_recordemp_cursor%ROWTYPE;

BEGIN

OPENemp_cursor;

FETCHemp_cursorINTOemp_record;

DBMS_OUTPUT.PUT_LINE（emp_record.ename||','||emp_record.job||','||emp_record.sal）;

CLOSEemp_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;  

SETSERVEROUTPUTON

DECLARE

V_enameVARCHAR2（10）;

V_salNUMBER（5）;

CURSORemp_cursorISSELECTename,salFROMempORDERBYsalDESC;

BEGIN

OPENemp_cursor;

FORIIN1..3LOOP

FETCHemp_cursorINTOv_ename,v_sal;

DBMS_OUTPUT.PUT_LINE（v_ename||','||v_sal）;

ENDLOOP;

CLOSEemp_cursor;

END;

执行结果为:

Sql代码：

1.KING,5000  

2.     SCOTT,3000  

3.     FORD,3000  

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

KING,5000

SCOTT,3000

FORD,3000

PL/SQL过程已成功完成。

 说明：

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

游标循环

【训练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;  

SETSERVEROUTPUTON

DECLARE

CURSORemp_cursorIS

SELECTempno,enameFROMemp;

BEGIN

FOREmp_recordINemp_cursorLOOP

DBMS_OUTPUT.PUT_LINE（Emp_record.empno||Emp_record.ename）;

ENDLOOP;

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;  

SETSERVEROUTPUTON

BEGIN

FORreIN（SELECTenameFROMEMP）LOOP

DBMS_OUTPUT.PUT_LINE（re.ename）

ENDLOOP;

END;

执行结果为：

Sql代码

1.SMITH  

2.ALLEN  

3.WARD  

4.JONES  

SMITH

ALLEN

WARD

JONES

   说明：

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

显式游标属性

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

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

Sql代码：

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

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

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

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

4.%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;  

SETSERVEROUTPUTON

DECLARE

V_enameVARCHAR2（10）;

CURSORemp_cursorIS

SELECT