`

Cursor大全(oracle)

阅读更多

隐含游标
--------
又名SQL游标,用于处理单行select into 和 DML语句。
SQL%ISOPEN SQL%FOUND SQL%NOTFOUND SQL%ROWCOUNT

显示游标
--------
用户处理select语句返回的多行数据。
select语句返回多行数据处理方式:[1]显示游标;[2]select ... bulk collect into 集合变量...;

 

【1】显示游标属性

[1] %ISOPEN 检测游标是否打开。
[2] %FOUND 检测游标结果集是否存在数据。
[3] %NOTFOUND 是否不存在数据。
[4] %ROWCOUNT 游标已提取的实际行数。

【2】使用显示游标

[1] 定义游标

CURSOR cursor_name IS select_statement;

[2] 打开游标

OPEN cursor_name;

[3] 提取数据

FECTH cursor_name INTO variable,...;

[4] 关闭数据

CLOSE cursor_name;

 

使用标量变量:
-------------

DECLARE
Cursor emp_cur IS select ename,sal from emp order by empno;
v_ename emp.ename%TYPE;
v_sal   emp.sal%TYPE;
BEGIN
IF NOT emp_cur%ISOPEN THEN
   OPEN emp_cur; 
   DBMS_OUTPUT.PUT_LINE('打开游标');
END IF;
NULL;
LOOP
   FETCH emp_cur INTO v_ename,v_sal;
   EXIT WHEN emp_cur%NOTFOUND;
   DBMS_OUTPUT.PUT_LINE('用户名:'||v_ename||',工资:'||v_sal);
END LOOP;
NULL;
IF emp_cur%ISOPEN THEN
   CLOSE emp_cur;
   DBMS_OUTPUT.PUT_LINE('关闭游标');
END IF;
END;

 

使用PLSQL记录变量
-----------------

DECLARE
Cursor emp_cur IS select ename,sal from emp order by empno;
emp_record emp_cur%ROWTYPE;
BEGIN
IF NOT emp_cur%ISOPEN THEN
   OPEN emp_cur; 
   DBMS_OUTPUT.PUT_LINE('打开游标');
END IF;
NULL;
LOOP
   FETCH emp_cur INTO emp_record;
   EXIT WHEN emp_cur%NOTFOUND;
   DBMS_OUTPUT.PUT_LINE('用户名:'||emp_record.ename||',工资:'||emp_record.sal);
END LOOP;
NULL;
IF emp_cur%ISOPEN THEN
   CLOSE emp_cur;
   DBMS_OUTPUT.PUT_LINE('关闭游标');
END IF;
END;

 

使用PLSQL集合变量
-----------------

DECLARE
Cursor emp_cur IS select ename,sal from emp order by empno;
TYPE emp_table_type IS TABLE OF emp_cur%ROWTYPE INDEX BY BINARY_INTEGER;
emp_table emp_table_type;
i number;
BEGIN
i := 1;
IF NOT emp_cur%ISOPEN THEN
   OPEN emp_cur; 
   DBMS_OUTPUT.PUT_LINE('打开游标');
END IF;
NULL;
LOOP
   FETCH emp_cur INTO emp_table(i);
   EXIT WHEN emp_cur%NOTFOUND;
   DBMS_OUTPUT.PUT_LINE('用户名:'||emp_table(i).ename||',工资:'||emp_table(i).sal);
   i := i + 1;
END LOOP;
NULL;
IF emp_cur%ISOPEN THEN
   CLOSE emp_cur;
   DBMS_OUTPUT.PUT_LINE('关闭游标');
END IF;
END;

 

【3】循环游标

FOR record_name IN cursor_name|select_statement LOOP
   statement;
   ....
END LOOP;

使用循环游标
------------

declare 
cursor emp_cusor is select ename,sal from emp where deptno = &no order by empno;
begin
for emp_record in emp_cusor loop
      dbms_output.put_line('姓名:'||emp_record.ename||',工资:'||emp_record.sal);
end loop;
end;

begin
for emp_record in (select ename,sal from scott.emp where deptno = &no order by empno) loop
      dbms_output.put_line('姓名:'||emp_record.ename||',工资:'||emp_record.sal);
end loop;
end;

 

【4】参数游标

CURSOR cursor_name(parameter_name datatype) IS select_statement; --只能制定类型,不能指定具体大小
OPEN cursor_name(参数值);
FECTH cursor_name INTO variable,...;
CLOSE cursor_name;

使用游标参数
------------

示例1:
------

declare 
cursor emp_cursor(v_depnto number) is select ename,sal from scott.emp where deptno = v_depnto order by empno;
emp_record emp_cursor%rowtype;
v_dno number;
begin
v_dno := &no;
if not emp_cursor%isopen then
      open emp_cursor(v_dno);
end if; 
null;
loop
        fetch emp_cursor into emp_record; 
        exit when emp_cursor%notfound;
        dbms_output.put_line('姓名:'||emp_record.ename||',工资:'||emp_record.sal);
end loop;
null;
if emp_cursor%isopen then
       close emp_cursor;
end if;
end;

 示例2
-----

declare 
cursor emp_cursor(v_depnto number) is select ename,sal from scott.emp where deptno = v_depnto order by empno;
v_dno number;
begin
v_dno := &no;
for emp_record in emp_cursor(v_dno) loop
      dbms_output.put_line('姓名:'||emp_record.ename||',工资:'||emp_record.sal);
end loop;
end;

 

【5】更新、删除游标行

CURSOR cursor_name IS select_statement
FOR UPDATE [OF column_reference] [NOWAITE];   -- OF子句指定对特定表加锁。
UPDATE table_name SET column=.. WHERE CURRENT OF cursor_name;
DELETE table_name WHERE CURRENT OF cursor_name;


使用游标更新数据
----------------

declare
       cursor test_cursor is select empno,ename,sal,deptno from scott.test for update;
       test_record test_cursor%rowtype;
       v_deptno number;
       v_sal test.sal%type;
begin
       v_deptno := &no;
       if not test_cursor%isopen then
          open test_cursor;
       end if;
       loop
          fetch test_cursor into test_record;
          exit when test_cursor%notfound;
          dbms_output.put_line('姓名:'||test_record.ename||',旧工资:'||test_record.sal);
          if test_record.deptno = v_deptno then
             update scott.test set sal=2*sal where current of test_cursor;
          else
             update scott.test set sal=3*sal where current of test_cursor;
          end if;
       end loop;
       close test_cursor;
end;
       
declare
cursor test_cursor is select empno,ename,sal,deptno from scott.test for update;
      v_deptno NUMBER:=&dno;
begin
for test_record in test_cursor loop
        if test_record.deptno = v_deptno then
                 dbms_output.put_line('姓名:'||test_record.ename||',旧工资:'||test_record.sal);
                 update scott.test set sal = sal*1.5 where current of test_cursor;
           end if;
       end loop;
end;

 

使用游标删除数据
----------------

declare 
cursor test_cursor(v_deptno number) is select deptno,empno,ename,comm from scott.test where deptno = v_deptno for update;
v_dno test.deptno%type := &dno;
begin
     for test_record in test_cursor(v_dno) loop
         if test_record.comm is null then
            dbms_output.put_line('用户名:'||test_record.ename||'部门:'||test_record.deptno);
            delete from scott.test where current of test_cursor;
         end if;
     end loop;     
end;

 

【6】游标变量

指向内存地址的指针。可以在打开游标时指定其所对应的SELECT语句,实现动态游标。

[1]定义REF CURSOR类型和游标变量:
Type ref_type_name IS REF CURSOR [RETURN return_type --必须是PL/SQL记录类型];
说明:如果指定RETURN子句,那么在打开游标时SELECT语句的返回结果必须与RETURN子句所指定的记录类型匹配。
cursor_variable ref_type name;

SYS_REFCURSOR

[2]打开游标,指定对应的SELECT语句:
OPEN cursor_variable FOR select_statement;
[3]提取数据
FETCH cursor_variable INTO variable1,variable,...;
[4]关闭游标
CLOSE cursor_variable;

不使用RETURN子句
----------------

DECLARE
TYPE ref_type_table IS REF CURSOR;
v_cursor            ref_type_table;
emp_record          emp%rowtype;
BEGIN
     OPEN v_cursor FOR select * from emp where deptno=&no;
     LOOP
         FETCH v_cursor INTO emp_record;
         EXIT WHEN v_cursor%NOTFOUND;
         dbms_output.put_line('员工号:'||emp_record.ename||'部门号:'||emp_record.deptno);
     END LOOP;
     CLOSE v_cursor;
END;

 

使用RETURN子句
--------------

DECLARE
emp_record          emp%rowtype;
TYPE ref_type_table IS REF CURSOR RETURN emp%rowtype;
v_cursor            ref_type_table;
BEGIN
     OPEN v_cursor FOR select * from emp where deptno=&no;
     LOOP
         FETCH v_cursor INTO emp_record;
         EXIT WHEN v_cursor%NOTFOUND;
         dbms_output.put_line('员工号:'||emp_record.ename||'部门号:'||emp_record.deptno);
     END LOOP;
     CLOSE v_cursor;
END;

DECLARE
Type emp_record_type IS RECORD(
       ename emp.ename%TYPE,
       salary emp.sal%TYPE,
       deptno emp.deptno%TYPE);
emp_record emp_record_type;

TYPE ref_type_table IS REF CURSOR RETURN emp_record_type;
v_cursor            ref_type_table;
BEGIN
     OPEN v_cursor FOR select ename,sal,deptno from emp where deptno=&no;
     LOOP
         FETCH v_cursor INTO emp_record;
         EXIT WHEN v_cursor%NOTFOUND;
         dbms_output.put_line('员工号:'||emp_record.ename||',部门号:'||emp_record.deptno||',工资:'||emp_record.salary);
     END LOOP;
     CLOSE v_cursor;
END;

 

【7】使用游标批量获取

FETCH ... BULK COLLECT INTO ...[LIMIT row_number];

不限制行数
----------

DECLARE
     CURSOR emp_cursor(v_deptno number) IS SELECT * FROM EMP WHERE deptno = v_deptno;
     TYPE type_emp_table IS TABLE OF emp%ROWTYPE INDEX BY BINARY_INTEGER;
     emp_table type_emp_table;
     v_dno emp.deptno%TYPE;
BEGIN
     v_dno := &no;
     OPEN emp_cursor(v_dno);
     FETCH emp_cursor BULK COLLECT INTO emp_table;
     CLOSE emp_cursor;
     FOR i IN 1..emp_table.COUNT LOOP
         dbms_output.put_line('员工号:'||emp_table(i).ename||'工资:'||emp_table(i).sal);
     END LOOP;
     CLOSE emp_cursor;
END;

 

限制行数
--------

DECLARE
     CURSOR emp_cursor(v_deptno number) IS SELECT * FROM EMP WHERE deptno = v_deptno;
     TYPE type_emp_table IS TABLE OF emp%ROWTYPE INDEX BY BINARY_INTEGER;
     emp_table type_emp_table;
     v_dno emp.deptno%TYPE;
BEGIN
     v_dno := &no;
     OPEN emp_cursor(v_dno);
     FETCH emp_cursor BULK COLLECT INTO emp_table LIMIT &2;
     CLOSE emp_cursor;
     FOR i IN 1..emp_table.COUNT LOOP
         dbms_output.put_line('员工号:'||emp_table(i).ename||'工资:'||emp_table(i).sal);
     END LOOP;
     CLOSE emp_cursor;
END;

 

【8】使用CURSOR表达式

作用嵌套游标。

DECLARE
       CURSOR dept_emp_cursor(v_deptno number) IS 
   SELECT dname,cursor(SELECT * FROM emp e WHERE e.deptno = d.deptno) 
   FROM dept d WHERE deptno = v_deptno;
       TYPE emp_cursor_type IS REF CURSOR;
       emp_cursor emp_cursor_type;
       emp_record emp%ROWTYPE;
       v_name dept.dname%TYPE;
       v_dno emp.deptno%TYPE;
BEGIN
     v_dno := &no;
     OPEN dept_emp_cursor(v_dno);
     loop 
          FETCH dept_emp_cursor INTO v_name,emp_cursor;
          EXIT WHEN dept_emp_cursor%NOTFOUND;
          dbms_output.put_line('部门名称:'||v_name);
          LOOP
              FETCH emp_cursor INTO emp_record;            
              EXIT WHEN emp_cursor%NOTFOUND;
              dbms_output.put_line('员工名称:'||emp_record.ename||',工资:'||emp_record.sal);
          END LOOP;
     end loop;
     CLOSE dept_emp_cursor;
END;

 

分享到:
评论

相关推荐

    oracle-cursor.rar_cursor_oracle_oracle cursor

    Oracle游标是数据库管理系统中非常重要的一个概念,它在处理SQL查询时扮演着核心角色,尤其是在需要逐行处理结果集的场景下。Oracle游标允许我们动态地控制和操作查询的结果,使得我们可以按需处理每一行数据,而不...

    oracle cursor 实例

    oracle中游标的使用方法,便于初学者参考,精通人员欢迎指正在。

    在Oracle 9i下的display_cursor脚本

    在Oracle数据库中,游标(Cursor)是一种处理单条记录的数据对象,常用于循环执行SQL语句。当一个PL/SQL块或者存储过程执行一个SELECT语句时,Oracle会隐式或显式地打开一个游标来处理结果集。`DISPLAY_CURSOR`脚本...

    Java获取Oracle存储过程返回的Cursor

    在Java编程中,有时我们需要调用Oracle数据库的存储过程,特别是当存储过程返回一个游标(Cursor)时,这种情况在处理大量数据或者分页查询时很常见。游标允许我们逐行处理结果集,而无需一次性加载所有数据,这对于...

    Oracle中Cursor介绍[归类].pdf

    Oracle中的Cursor,也称为游标,是数据库管理系统中一个重要的概念,主要用于处理SQL查询返回的多行结果集。游标提供了一种方式,允许程序逐行处理查询结果,而不是一次性加载所有数据。以下是对游标分类及其特性的...

    oracle如何合并多个sys_refcursor详解

    在Oracle数据库环境中,合并多个`sys_refcursor`(也称为游标)的需求常常出现在复杂的业务逻辑中,尤其是在需要重复调用相同逻辑的存储过程时。本文将详细介绍如何通过序列化和XML处理来实现这一功能。 首先,让...

    java调用oracle存储过程返回结果集,Record,cursor参照.pdf

    Java调用Oracle存储过程返回结果集Record、Cursor参照 Java调用Oracle存储过程返回结果集(Record)是指在Java程序中通过调用Oracle存储过程来获取记录集的结果。下面将详细介绍相关知识点。 创建Type 在Oracle中...

    oracle 临时表使用例子并用CURSOR返回结果集的例子

    本篇文章将深入探讨Oracle临时表的使用,并通过一个使用游标(CURSOR)返回结果集的例子来进一步理解其用法。 首先,让我们了解如何创建Oracle临时表。临时表的创建语法与普通表类似,但我们需要使用`GLOBAL ...

    oracle-cursor.rar_oracle

    Oracle游标是数据库管理系统中一个重要的概念,它在处理复杂SQL操作时扮演着核心角色。游标允许我们按行处理查询结果集,而非一次性加载所有数据。这在处理大量数据或者进行迭代处理时非常有用,因为可以有效地管理...

    pkg_XXX.rar_oracle_oracle record cursor

    在Oracle数据库系统中,"pkg_XXX.rar_oracle_oracle record cursor" 提供了一个学习和参考的案例,涉及到了存储过程、游标(cursor)、记录(record)以及动态SQL和临时表的使用。这些概念是Oracle数据库编程的核心...

    Oracle JOB,procedure,cursor 的使用

    本篇文章将深入探讨Oracle中的JOB、PROCEDURE(存储过程)和CURSOR(游标)的使用,这些是Oracle数据库开发中的核心概念。 首先,我们来看Oracle中的JOB。JOB是一种调度工具,允许用户在特定时间或间隔执行数据库...

    cx_Oracle-7.3.0_oracle_cx_oracle_cx_Oracle7.3对应_python_jupyteror

    标题中的"cx_Oracle-7.3.0_oracle_cx_oracle_cx_Oracle7.3对应_python_jupyteror" 提到了一个关键的Python库——cx_Oracle,它是Python与Oracle数据库之间的一个接口,允许Python程序员使用Oracle的全部功能。...

    cx_Oracle-7.1.2-cp37-cp37m-win_amd64.zip

    使用python语言连接oracle数据库的连接工具,示例如下: import cx_Oracle connection = cx_Oracle.connect("scott", "tiger", "localhost/orcl") ...# close cursor and oracle cursor.close() connection.close()

    oracle CURSOR例子

    ### Oracle Cursor 示例详解 在Oracle数据库管理中,游标(Cursor)是一种强大的工具,用于处理SQL语句返回的结果集中的数据。游标允许程序逐一访问结果集中的每一行记录,并进行复杂的逻辑处理。本文将通过几个...

    Oracle数据库中的Cursor介绍

    ### Oracle数据库中的Cursor详解 #### 一、游标的基本概念 **游标(Cursor)**是Oracle数据库中的一个重要特性,主要用于处理SQL查询返回的结果集。它允许应用程序以更灵活的方式逐行处理查询结果,而非一次性处理...

    ORACLE技术文档\oracle cursor 游标.doc

    Oracle游标,或称为光标,是数据库管理系统中用于处理SQL查询的一种机制,它允许程序逐行处理查询结果。在Oracle中,游标是至关重要的,尤其在进行复杂的交互式数据操作时。当一个应用程序尝试打开过多的游标时,...

    oracle笔记游标的使用

    oracle笔记游标的使用,游标的详细代码案例,游标知识点笔记!

    Python连接oracle工具cx_Oracle官方文档

    文档还描述了各个对象的具体功能和用法,比如连接对象(Connection Object)、游标对象(Cursor Object)、变量对象(Variable Objects)、会话池对象(SessionPool Object)、订阅对象(Subscription Object)、大...

    cx_Oracle调用oracle所需驱动,

    在Python编程环境中,与Oracle数据库进行交互通常会使用cx_Oracle这个第三方库。cx_Oracle是Python的一个接口,它允许Python程序直接访问Oracle数据库,执行SQL查询和存储过程。为了能够顺利地使用cx_Oracle,我们...

    oracle 函数大全 参考函数 手册 速查 chm格式

    Oracle数据库是全球广泛使用的大型关系型数据库管理系统,其强大的功能和灵活性使得它在...无论你是初学者还是经验丰富的开发者,这份Oracle函数大全都是一个宝贵的参考资料,能帮助你解决日常工作中遇到的各种问题。

Global site tag (gtag.js) - Google Analytics