oracle游标杂耍

/*
set serveroutput on
declare 
    v_code test_user.t_test1.code%type;
    v_name  test_user.t_test1.name%type;
   
    cursor c_test is
    select code,name from test_user.t_test1;
    begin
        open c_test;
        loop
            fetch c_test into v_code,v_name;
            dbms_output.put_line(v_code ||'---------'|| v_name);
            exit when c_test%notfound;
        end loop;
                    commit;
        close c_test;
     end;
*/     
     --set serveroutput on
  /*
     declare vv_code  TEST_USER.T_TEST1.CODE%type;
                vv_name test_user.t_test1.name%type;
      v_rowcount int;
                
        cursor c_tt is select code,name from test_user.t_test1;
        begin
            if  c_tt%isopen then
                NULL;
              ELSE
            open c_tt;
            END IF;
            fetch c_tt into vv_code,vv_name;

            while c_tt%found
            loop
                if vv_code is not null then
                    if vv_name is not null then
                        insert into test_user.ta values (1,'test1111111',vv_code,vv_name,v_rowcount);
                    end if;
                end if;
                           v_rowcount:=c_tt%rowcount;-----c_tt%rowcount 游标检索出的总数据行数
            if c_tt%rowcount>3 then
                dbms_output.put_line(v_rowcount);
            end if;
                fetch c_tt into vv_code,vv_name;
     
            end loop;
                        commit;
            IF c_tt%isopen then
            close c_tt;
            else
            null;
            end if;
            
        end;
  */

  /* 
   ----游标的FOR循环  不用显示打开游标，关闭游标，测试数据的存在和定义存放数据的变量

     declare    
   Cursor c_t is select code,name from test_user.t_test1;
   begin
        for row_count in c_t
        loop
            if row_count.code is not null then
                if row_count.name is not null then
                    insert into test_user.ta values(23,'萨法款到即发',row_count.code,row_count.name,12345);
                end if;
             end if;
        end loop;
           commit;
   end;
  
 -- 替换方案
  
  begin
    for row_count in (select code,name from test_user.t_test1) loop
        insert into test_user.ta values(55,'阿司法局离开',row_count.code,row_count.name,98);
    end loop;
    commit;
  end;
  */
  /*
  ---游标FOR循环中，可以传递参数给游标
  set serveroutput on
  declare
    cursor c_ref_vai (v_id integer) is select id,code,name from test_user.ta where id=v_id;
    begin
        for row_foreach in c_ref_vai(55) loop
            dbms_output.put_line(row_foreach.id||'----'||row_foreach.code ||'----'||row_foreach.name);
        end loop;
    end;
    
    */

  ---游标变量和游标的是不同的，它们类似 变量和常量之间的关系。游标是静态的，游标变量是动态的，
  --游标变量并不与特定的查询绑定在一起。游标变量类似指针，保存某个项目的内存位置，而不是项目本身。
  --游标变量具有REF CURSOR数据类型。游标总是指向相同的查询工作区，而游标变量 能够指向不同的工作区，
  --因此游标和游标变量不能互操作。
  -----定义 REF CURSOR类型。创建游标变量：
  TYPE  ref_type_name is  REF CURSOR [RETURN  return_type];
  参数：
  return_type:必须表示一个记录或者是数据库表的一行。
  如果 显示声明： RETURN return_type 则表示 REF CURSOR 是一个强类型，否则就是一个弱类型。
  --声明游标变量
  DECLARE 
    TYPE ref_type_name is  REF CURSOR  return t_goods%rowtype;
    v_ref_type_name         ref_type_name;
  或：用%type 提供记录变量店数据类型
  DECLARE
        one_record  t_goods%rowtype;
        TYPE ref_type_name is ref cursor return one_record%type;
        v_ref_type_name         ref_type_name;
        
        
   --用 用户自定义的记录类型作为返回值
   DECLARE
            TYPE myrecord is record(code varchar2(32),name varchar2(32),address varchar2(64));
            TYPE ref_type_name is ref cursor return myrecord;
            v_ref_type_name         ref_type_name;
            
    ---声明游标变量作为函数或存储过程的参数
    declare
        type ref_type_name is ref cursor return t_goods%rowtype;
        procedure open_t_goods_data(row_record in out ref_type_name)  ----必须用 IN OUT 类型
        as
        begin
        .....
        end open_t_goods_data;
                
  案例：
    create package t_goods_cv 
    as
    type ref_cur_strong is ref cursor return t_goods%rowtype;
    type ref_cur_weak  is ref cursor;
    procedure my_prc(v_ref1 in out ref_cur_strong,v_ref2 in out ref_cur_weak,v_id in integer);
    end t_goods_cv;
    create package body t_goods_cv
    as
    begin
        procedure my_prc(v_ref1 in out ref_cur_strong,v_ref2 in out ref_cur_weak v_id in integer)
        is 
        begin
            if v_id=1 then
                open v_ref1 for select * from test_user.t_goods;
                fetch v_ref1 into row_foreach
                loop
                    dbms_output.put_line(row_foreach.name||row_foreach.address);
                    exit when v_ref1%notfound;
                end loop;
                close v_ref1;
                else v_id<0 then
                open v_ref2 for select * from test_user.t_test1;
                fetch v_ref2 into row_foreach2
                while row_foreach2.name is not null
                loop
                    ......;
                end loop;
            end if;
        end;
    end t_goods_cv;
    
    
    ---使用 BULK COLLECT子句，从游标变量中提取多行，放入一个或多个集合中。
    DECLARE
        TYPE c_cur is ref cursor return t_goods%rowtype
        type t_name is table of t_goods.name%type;
        type t_code is table of t_goods.code%type;
        v_ref_cur   c_cur;
        v_name      t_name;
        v_code        t_code;
        begin
            open v_ref_cur for select code,name from test_user.t_goods;
            fetch v_ref_cur bulk collect into v_code,v_name;
            ...........
            close v_ref_cur;
        end;
        
    ---主变量或游标变量 格式是：变量或游标变量前加：这是在pl/sql中用法
    即 :v_i---代表主变量
        :ref_cur----代表主游标变量
        
        
  -----游标表达式
    一个游标表达式返回一个嵌套游标，结果集中的每行都包含值加上子查询生成的游标。
    游标表达式的查询，是作为游标声明的一部分，可以在动态SQL查询中使用游标表达式。语法：
    CURSOR (subquery);
    
    案例
    create or replace procedure my_test(id in integer)
    as
    type refcursor is ref cursor;
    cursor c1 is
    select a.code,
    cursor(select b.name,
    cursor(select c.name from tc c where c.id=b.ref_id)as cname
    from tb b where b.ref_ta_id=a.id)as bname 
    from ta a where a.id=id;
    v_1 refcursor;
    v_2 refcursor;
    v_3 refcursor;
    v_1name ta.code%type;
    v_2name tb.name%type;
    v_3name tc.name%type;
    begin
        open c1;
        loop
        fetch c1 into v_1name,v_1;
        ....;
        exit when c1%notfound;
        loop
        fetch v_1 into v_2name,v2;
        ...;
        exit when v_1%notfound;
        loop
        fetch v2 into v_3name;
        ...;
        exit when v2%notfound;
        ....;
        end loop;
        end loop;
        end loop;
        close c1;
        
    end;
    end my_test;