Oracle游标使用方法及语法大全

下面对这两种格式进行说明:

第一种格式中的变量名是用来从游标中接收数据的变量,需要事先定义。变量的个数和类型应与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;

执行结果为: Sql代码:

1. SCOTT,ANALYST

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

执行结果为: Sql代码:

1. SCOTT,ANALYST,3000 2. 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 DES

C;

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;

执行结果为: Sql代码:

1. KING,5000

2. SCOTT,3000 3. FORD,3000

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

执行结果为: Sql代码

1. 7369SMITH 2. 7499ALLEN 3. 7521WARD

4. 7566JONES

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

执行结果为: Sql代码

1. SMITH 2. ALLEN 3. WARD 4. JONES

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

显式游标属性

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

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

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

2. %FOUND 布尔型 最近的FETCH语句返回一行数据则为真,否则为假 3. %NOTFOUND 布尔型 与%FOUND属性返回值相反 4. %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);

联系客服:779662525#qq.com(#替换为@)