Oralce 存储过程 cursor、type x_cur is ref cursor、sys_refcursor 静态游标、强类型弱类型动态游标、静态SQL 动态SQL 结合使用总结

「爱情、让人受尽委屈。」 2022-04-04 12:30 408阅读 0赞

直接上代码，使用的开发环境：

Oracle Database 11g Release 2(11.2.0.4.0) - Enterprise Edition，

PL/SQL Develop 12.0.7，Instantclient\_12\_2；

PL/SQL Develop -> New -> Test Windoow：

**游标 动态 遍历 cursor 匿名块**.tst：

-- Created on 2018/12/17 by TECH01 
    declare
    
        cursor cus_cur(in_id number) return pre_preorder%rowtype
        is
            select * from pre_preorder where id < in_id;
        cus_row pre_preorder%rowtype;
    
    begin
        
        open cus_cur(1200);
    
        dbms_output.put_line(cus_cur%ROWCOUNT);     --如果游标没有open cus_cur(1200); ORA-01001: invalid cursor
        
        if cus_cur%found = true then
            dbms_output.put_line('cus_cur%found = true');
        end if;
        
        if cus_cur%notfound = true then
            dbms_output.put_line('cus_cur%notfound = true');
        end if;
        
        loop
            fetch cus_cur into cus_row; --ORA-01001: invalid cursor
            
            if cus_cur%found = true then
                dbms_output.put_line('loop fetch cus_cur%found = true');
            end if;
                
            if cus_cur%notfound = true then
                dbms_output.put_line('loop fetch cus_cur%notfound = true');
            end if;
            
            exit when cus_cur%notfound;
            
        end loop;
    
        dbms_output.put_line(cus_cur%ROWCOUNT);
    
        close cus_cur;
    
        --dbms_output.put_line(cus_cur%ROWCOUNT );  --ORA-01001: invalid cursor
    
    end;
    
    
    
    
    
    DBMS Output:
    
    0
    loop fetch cus_cur%found = true
    loop fetch cus_cur%found = true
    loop fetch cus_cur%found = true
    loop fetch cus_cur%found = true
    loop fetch cus_cur%found = true
    loop fetch cus_cur%found = true
    loop fetch cus_cur%found = true
    loop fetch cus_cur%found = true
    loop fetch cus_cur%found = true
    loop fetch cus_cur%found = true
    loop fetch cus_cur%found = true
    loop fetch cus_cur%found = true
    loop fetch cus_cur%found = true
    loop fetch cus_cur%found = true
    loop fetch cus_cur%notfound = true
    14

**游标 动态 遍历 ref cursor 匿名块**.tst：

-- Created on 2018/12/17 by TECH01 
    declare 
      -- Local variables here
      --type cus_cur_type is ref cursor return pre_preorder%rowtype; 
      type cus_cur_type is ref cursor;
      cus_cur cus_cur_type;
      cus_row pre_preorder%rowtype;
      l_id integer := 1200;
    begin
      --open cus_cur for select * from pre_preorder where id < :v_id using l_id;  --PL/SQL: ORA-00933: SQL command not properly ended
      open cus_cur for 'select * from pre_preorder where id < :v_id' using l_id;  --ref cursor return pre_preorder%rowtype; 不可以用在动态SQL语句中PLS-00455: cursor 'CUS_CUR' cannot be used in dynamic SQL OPEN statement
      --open cus_cur for select * from pre_preorder where id < l_id;              --ref cursor return pre_preorder%rowtype; 可以用在静态SQL语句中
      
      dbms_output.put_line(cus_cur%ROWCOUNT );
      
      loop 
          fetch cus_cur into cus_row;
          exit when cus_cur%notfound;
      end loop;
      
      dbms_output.put_line(cus_cur%rowcount);
      
      
      open cus_cur for select * from pre_preorder where id < 1300;
      
      loop 
          fetch cus_cur into cus_row;
          exit when cus_cur%notfound;
      end loop;
      
      dbms_output.put_line(cus_cur%rowcount);
      
      
      close cus_cur;
      
      --dbms_output.put_line(cus_cur%rowcount);   --ORA-01001: invalid cursor
      
    end;

**游标 动态 遍历 sys\_refcursor 匿名块**.tst：

-- Created on 2018/12/17 by TECH01 
    declare
    
        cus_cur sys_refcursor;--return pre_preorder%rowtype;  --PLS-00103: Encountered the symbol "RETURN" when expecting one of the following: := . ( @ % ; not null range default character, The symbol ";" was substituted for "RETURN" to continue.
    
        cus_row pre_preorder%rowtype;
        l_id integer := 1200;
    begin
        --open cus_cur for select * from pre_preorder where id < :v_id using l_id;    --PL/SQL: ORA-00933: SQL command not properly ended
        --open cus_cur for 'select * from pre_preorder where id < :v_id' using l_id;  --PLS-00455: cursor 'CUS_CUR' cannot be used in dynamic SQL OPEN statement
        --open cus_cur for 'select * from pre_preorder where id < l_id';              --ORA-00904: "L_ID": invalid identifier
        open cus_cur for select * from pre_preorder where id < l_id;                  --sys_refcursor可以用在静态SQL语句中
        --open cus_cur for 'select * from pre_preorder where id < :v_id' using l_id;  --sys_refcursor可以用在动态SQL语句中
        
        
        
        dbms_output.put_line(cus_cur%ROWCOUNT);
    
        loop
            fetch cus_cur
                into cus_row;
            exit when cus_cur%notfound;
        end loop;
    
        dbms_output.put_line(cus_cur%ROWCOUNT);
    
        close cus_cur;
    
        --dbms_output.put_line(cus_cur%ROWCOUNT );  --ORA-01001: invalid cursor
    
    end;

综上总结：

1. 只有 sys\_refcursor return pre\_preorder%rowtype;

不能使用强类型的动态游标，汇报以下错误：

PLS-00103: Encountered the symbol "RETURN" when expecting one of the following: := . ( @ % ; not null range default character, The symbol ";" was substituted for "RETURN" to continue.

2. ref cursor return pre\_preorder%rowtype;

不可以用在动态SQL语句中PLS-00455: cursor 'CUS\_CUR' cannot be used in dynamic SQL OPEN statement；

可以用在静态SQL语句中

3. sys\_refcursor

可以用在静态SQL语句中；

可以用在动态SQL语句中；

3. x\_curcur%ROWCOUNT 可以理解为是一个计数器，初始值为0，每fetch游标x\_curcur一次，计数器 +1；

4. x\_cur%found 在fetch游标x\_curcur前为false，fetch到数据行时为true，直到fetch不到行数据时值为false；

5.x\_cur%notfound  在fetch游标x\_curcur前为false，fetch到数据行时为false，直到fetch不到行数据时值为true；

参考:

[https://blog.csdn.net/zx695370759/article/details/79200913][https_blog.csdn.net_zx695370759_article_details_79200913]

### 强类型的动态游标和弱类型的动态游标区别 ###

2018年01月29日 22:58:22 [穿毛衣的程序猿][Link 1] 阅读数：130

1、简单的来说：强类型的动态游标是指带有return返回语句的，而弱类型的动态游标是指不带return语句的（也即，弱类型的动态游标可以与任何查询语句匹配，但是强类型的动态游标只能与特定的查询语句匹配。）

2、个人理解：强类型的有点像java中使用了泛型一样对其进行了限制，而弱类型的就像object类型。

[https_blog.csdn.net_zx695370759_article_details_79200913]: https://blog.csdn.net/zx695370759/article/details/79200913
[Link 1]: https://me.csdn.net/zx695370759