oracle 游标(cursor)

/* oracle 游标 */
/* 在pl/sql 快中 当执行查询语句(select) 和dml 语句是 ，oracle 会为其分配上下文区 ，游标是指上线文区的指针 */
/*
 <一> 不带参数的游标
 
 1.隐式游标：用于出来单行数据（select into 和 dml 语句）
 2.显示游标：用于处理多行数据
             显示游标的编写步骤 declare(定义)--》open(打开)--》fetch(提取)--》close(关闭)
             显示游标的属性 ： %isopen(用于确定游标是否已经打开),%found(是否从结果集中提取到了数据)
                               %notfound(与%found相反)，%rowcount 当前行为止已经提前到的行数
             eg:1. fetch .. collect into  处理多行
             declare
               cursor v_cursor is select username from tab_cursor ; --declare
               type t_tab is table of  varchar2(20);
               v_tab t_tab;
             begin
               open v_cursor ; -- open
               exit when v_cursor%notfound;
               fetch v_cursor bulk collect into v_tab; --fetch(处理多行)
                for i in 1..v_tab.count loop
                    dbms_output.put_line(v_tab(i));
                end loop ;
                close v_cursor; --close
             end;  
             ---------------------------------------------------------------------------------------------
             eg;2. 处理单行 fetch ... bulk  into
              declare
               cursor v_cursor2 is select username，id from tab_cursor where id='001'; --declare
               v_id varchar2(20);
               v_username varchar2(20);
             begin
               open v_cursor ; -- open
               loop
               fetch v_cursor2 bulk  into v_id,v_username ; --fetch(处理单行)
               exit when v_cursor%notfound;
                    dbms_output.put_line(v_id||'----'||v_username);
                end loop ;
                close v_cursor;
             end;  
             --------------------------------------------------------------------------------------------
             eg:3 fetch .. bulk collect into ..limit rows (提取部分数据)（类似eg 1）
             --------------------------------------------------------------------------------------------
             eg:4 基于游标定义记录变量（我认为使用记录变量来存取数据可以简化处理数据）
              declare
               cursor v_cursor is select username,id from tab_cursor ; --declare
               r_record v_cursor%rowtype;
             begin
               open v_cursor ; -- open
               exit when v_cursor%notfound;
               fetch v_cursor bulk collect into r_record; --fetch(处理多行)
                for i in 1..v_tab.count loop
                    dbms_output.put_line(r_record.id || r_record.username);
                end loop ;
                close v_cursor; --close
             end;  
             
     -- ======================================================================================================
     
  <二>   带参数的游标
 
       eg : cursor c_cursor(v_param) is select_statement;(根据指定的参数获取不同的值)
 
        cursor c_cursor(v_param varchar2) is select username,id from tab_cursor where id=v_param;(参数只指定类型而不指定长度)
   
        1.如果通过要操作update/delete 则需要 带有 'for update' 子句
        eg1.: upate tab_cursor set username = 'xxx' where current of cursor_name (使用current of 子句)
       
       
        2.如果游标子查询涉及到多张表，那么默认情况会在所以修改行上加共享锁，为了提高效率，可以在特定行上加共享锁，可以使用 for update ..of 子句
        eg; declare
               cursor v_cursor is select username,id from dept,emp where emp.deptno = dept.deptno for update of emp.deptno ; -- for update of
               r_record v_cursor%rowtype;
             begin
               open v_cursor ; -- open
               exit when v_cursor%notfound;
               fetch v_cursor bulk collect into r_record; --fetch(处理多行)
                  if r_record.deptno = 30 then
                   upate emp set sal = sal+1000 where current of cursor_name (使用current of 子句)
                  end if
                close v_cursor; --close
             end;  
       
       3.使用nowait ;for update 子句用于给行加锁，如果其它回话已经将该行加锁，那么默认情况下当前会话要一直等待其它会话释放锁，
         为了不等待可以加nowait 子句退出pl/sql 。
       
        eg; declare
               cursor v_cursor is select username,id from emp for update nowait  ; -- nowait
               r_record v_cursor%rowtype;
             begin
               open v_cursor ; -- open
               exit when v_cursor%notfound;
               fetch v_cursor bulk collect into r_record; --fetch(处理多行)
                  if r_record.deptno = 30 then
                   upate emp set sal = sal+1000 where current of cursor_name (使用current of 子句)
                  end if
                close v_cursor; --close
             end;  
       
       
       
       -----===================================================================================
       
       4.游标for 循环（简化对游标的处理）(个人推荐这种方法)
           使用游标for 循环时可以自动打开游标，提取游标数据并关闭游标
           语法：
             for record_name in cursor_name loop
                 statement;
             end loop
           
           eg 1;
            declare
              cursor emp_cursor is select name,sal from emp;
            begin
              for emp_record  in emp_cursor loop
                 dbms_output.put_line('第'||emp_cursor%rowcount||'是'||emp_record.name);
              end loop ;
            end;
       
          eg 2; 可以在for 中直接使用子查询
            declare
              --cursor emp_cursor is select username,id from test_complex;
            begin
              for emp_record  in (select username,id from test_complex) loop
                 dbms_output.put_line('第'||emp_record.id||'是'||emp_record.username);
              end loop ;
            end;
       
       
       
       --==============================================================================================
       
       5.使用游标变量；
          pl/sql 游标变量存放着指向内存地址的指针，当使用显示游标时需要在定义部分指定相应的静态sql（如上），
          但是使用游标变量时不需要指定，可以在使用游标变量时指定对应的select语句。
            游标变量使用 步骤；
               定义游标变量（ref cursor）-》打开游标-》提取游标变量数据-》关闭游标
              
         eg1;在定义 游标变量时不指定return 子句
            declare
               type t_cursor is ref cursor; --自定义类型
               v_cursor t_cursor ; --定义游标变量
               r_record emp%rowtype;

            begin
                open v_cursor for select name,id from emp;
                
               loop
                fetch v_cursor into r_record;
                exit when v_cursor%notfound;
                 dbms_output.put_line('第'||v_cursor%rowcount||'是'||r_record.username);
              end loop ;
              close v_cursor;
            end;
               
       eg1;在定义 游标变量时指定return 子句
            declare
               type t_record is record (name varchar2(20),sal varchar2(20));--定义记录类型
               type t_cursor is ref cursor return t_record; --自定义类型
               v_cursor t_cursor ; --定义游标变量
               r_record t_record; --定义记录变量

            begin
                open v_cursor for select name,id from emp;
                
               loop
                fetch v_cursor into r_record;
                exit when v_cursor%notfound;
                 dbms_output.put_line('第'||v_cursor%rowcount||'是'||r_record.username);
              end loop ;
              close v_cursor;
            end;
               
       
       --=======================================================================================
       
       6.使用cursor 表达式（嵌套游标）
       eg1;  
        declare
               type refcursor is cursor;
               cursor  t_cursor(deptno number) is select a.dname ,
                       (select b.name,b.sal from emp b where b.deptno=a.deptno) from dept a where a.deptno=&deptno;
               v_cursor refcursor ; --定义游标变量
               v_dname dept.name%type; --定义记录变量
               v_name emp.name%type;
               v_sal emp.sal%type;

            begin
                open refcursor(&deptno);
                
                
               loop
                fetch refcursor into r_record,v_cursor;
                exit when refcursor%notfound;
                dbms_output.put_line(v_dname);
                 
                loop
                  fetch v_cursor into v_name,v_sal;
                  exit when refcursor%notfound;
                   dbms_output.put_line(v_name||--||v_sal);
                end loop;
                
              end loop ;
              close refcursor;
            end;
       
       
  */