oracle动态游标

1，带参数的游标  
   
  　　与存储过程和函数相似，可以将参数传递给游标并在查询中使用。这对于处理在某种条件下打开游标的情况非常有用。它的语法如下：  
   
  CURSOR   cursor_name[(parameter[,parameter],...)]   IS   select_statement;    
   
  　　定义参数的语法如下：  
   
  Parameter_name   [IN]   data_type[{:=|DEFAULT}   value]    
   
  　　与存储过程不同的是，游标只能接受传递的值，而不能返回值。参数只定义数据类型，没有大小。    
   
  　　另外可以给参数设定一个缺省值，当没有参数值传递给游标时，就使用缺省值。游标中定义的参数只是一个占位符，在别处引用该参数不一定可靠。  
   
  　　在打开游标时给参数赋值，语法如下：  
   
  OPEN   cursor_name[value[,value]....];    
   
  　　参数值可以是文字或变量。  
   
  　　例：  
   
  DECALRE  
   
  CURSOR   c_dept   IS   SELECT   *   FROM   dept   ORDER   BY   deptno;  
  CURSOR   c_emp   (p_dept   VARACHAR2)   IS  
  SELECT   ename,salary  
  FROM   emp  
  WHERE   deptno=p_dept  
  ORDER   BY   ename  
  r_dept   DEPT%ROWTYPE;  
  v_ename   EMP.ENAME%TYPE;  
  v_salary   EMP.SALARY%TYPE;  
  v_tot_salary   EMP.SALARY%TYPE;  
   
  BEGIN  
   
  OPEN   c_dept;  
  LOOP  
  FETCH   c_dept   INTO   r_dept;  
  EXIT   WHEN   c_dept%NOTFOUND;  
  DBMS_OUTPUT.PUT_LINE('Department:'||   r_dept.deptno||'-'||r_dept.dname);  
  v_tot_salary:=0;  
  OPEN   c_emp(r_dept.deptno);  
  LOOP  
  FETCH   c_emp   INTO   v_ename,v_salary;  
  EXIT   WHEN   c_emp%NOTFOUND;  
  DBMS_OUTPUT.PUT_LINE('Name:'||   v_ename||'   salary:'||v_salary);  
  v_tot_salary:=v_tot_salary+v_salary;  
  END   LOOP;  
  CLOSE   c_emp;  
  DBMS_OUTPUT.PUT_LINE('Toltal   Salary   for   dept:'||   v_tot_salary);  
  END   LOOP;  
  CLOSE   c_dept;  
  END;    
2. 字符串变量
动态游标就是动态定义游标。  
  declare  
  type   t_sor   is   ref   cursor;  
  v_sor   t_sor;     --必需的，通过对象变量实现.  
  ...  
  begin    
  ..  
  end;  
  /
 
给一个完整的例子:  
  create   or   replace   procedure   SP_CLEAR(V_TABLE   IN   STRING)   IS  
   
      TYPE   cur_type   IS   REF   CURSOR;  
      c_tab   cur_type;  
      v_str   STRING(2000);  
      v_tab   t_clear.wlbmc%TYPE;  
      v_sql   STRING(3000);  
   
   
  begin  
      --Get   table   from   v_table  
      --Reorder   these   tables  
      v_str:=upper(v_table);  
      v_str:=REPLACE(v_str,',',''',''');  
      v_str:=''''||v_str||'''';  
      v_sql:='SELECT   WLBMC   FROM   T_CLEAR   WHERE   TRIM(WLBMC)   IN   ('||v_str||')   ORDER   BY   BZ   DESC';  
      OPEN   c_tab   FOR   v_sql;  
      LOOP  
          FETCH   c_tab   INTO   v_tab;  
          EXIT   WHEN   c_tab%NOTFOUND;  
          v_sql:='delete   from   '||v_tab||'';  
          EXECUTE   IMMEDIATE   v_sql;      
      END   LOOP;        
      COMMIT;      
      --deal   with   exception    
      EXCEPTION    
          WHEN   OTHERS   THEN  
                ROLLBACK;  
                RAISE;  
  end   SP_CLEAR;  
  /   
 
3.字符串变量例子2
给你一个完整的例子：  
  DECLARE  
        TYPE   EmpCurTyp   IS   REF   CURSOR;  
        emp_cv       EmpCurTyp;  
        emp_rec     emp%ROWTYPE;  
        sql_stmt   VARCHAR2(200);  
        my_job       VARCHAR2(15)   :=   'CLERK';  
  BEGIN  
        sql_stmt   :=   'SELECT   *   FROM   emp   WHERE   job   =   :j';  
        OPEN   emp_cv   FOR   sql_stmt   USING   my_job;  
        LOOP  
              FETCH   emp_cv   INTO   emp_rec;  
              EXIT   WHEN   emp_cv%NOTFOUND;  
              --   process   record  
        END   LOOP;  
        CLOSE   emp_cv;  
  END;   
 
 
带输入参数的动态游标的实例
 
 
from： http://war-martin.javaeye.com/blog/319608 

1、简单的来说：强类型的动态游标是指带有return返回语句的，而弱类型的动态游标是指不带return语句的（也即，弱类型的动态游标可以与任何查询语句匹配，但是强类型的动态游标只能与特定的查询语句匹配。）
2、个人理解：强类型的有点像java中使用了泛型一样对其进行了限制，而弱类型的就像object类型。
3、弱类型的动态游标使用示例：
--根据用户的不同输入来打印（custom、salerecord）信息
declare
type ruoleixing is ref cursor;              --定义一个动态游标类型(弱)
ruoref ruoleixing; --申明游标


e_custom custom%rowtype;
--声明变量接收查询出来的结果
e_salerecordsalerecord%rowtype;


receiver varchar2(1) :=upper(substr('&input',1,1));--定义变量接收用户的输入（其中的&input主要就要个&，input是随便取就行）


(其中的upper,substr分别表示将接收到的用户输入转换为大写和截取用户输入的第一个字母,注：在接收用户输入的时候如果是字符串信息得用单引号括起来即：'&input'否则会报错！)


begin


--判断用户的输入并进行相应的输出
if (receiver = 'C') then


open ruoref for --打开游标
select * from custom;
loop --循环提取游标中的数据
fetch ruoref into e_custom;
exit when ruoref%notfound;
dbms_output.put_line(e_custom.name || ''|| e_custom.levels); --注：别混了oracle中字符串的拼接是使用 concat 或 ||；e_custom.name || ' '||e_custom.levels中的name、levels均是custom表中的字段名
end loop;
elsif (receiver = 'S') then


open ruoref for
select * from salerecord;


loop
fetch ruoref intoe_salerecord;


exit when ruoref%notfound;


dbms_output.put_line(e_salerecord.totalmoney);--e_salerecord.money中的totalmoney是salerecord表中的一个字段名
end loop;


else
dbms_output.put_line('请输入C或S！');
return; --return表示退出此次操作


end if;
close ruoref; --注意要及时的关闭游标


end;

文章目录
游标
静态游标和动态游标
静态游标demo
动态游标demo
总结

游标
简述：存储过程代码中从表中检索出结果集进行操作交互。
静态游标和动态游标
静态游标：程序执行的时候不需要再去解析sql语言，对于sql语句的解析在编译的时候就可以完成

动态游标：含有参数，对于sql语句的解析必须要等到参数确定的时候才能完成

测试表的BONUS1数据:


静态游标demo
静态游标：查询BONUS1表中的所有数据

这里可以看到sql写在定义游标的时候，编译时完成查询。
create or replace procedure curor is
cursor v_check_names is select ENAME from BONUS1;
v_ENAME BONUS1.ENAME%type;
begin
    open v_check_names;
    loop
      fetch v_check_names into v_ENAME;
      exit when v_check_names%notfound;
      dbms_output.put_line(v_ENAME);
            
    end loop;
          
    close v_check_names;
end curor;


执行结果：


动态游标demo
动态游标：查询BONUS1表3行数据

这里可以看到动态游标会构造sql，带有参数。sql准备好以后再执行。
create or replace procedure curor is
type cur_get_index is ref cursor; --声明一个动态游标类型
v_cur_get_index cur_get_index;  --声明一个动态游标变量

v_count number:=3;
v_ENAME varchar2(60);
v_JOB varchar2(60);
v_SAL varchar2(60);
v_COMM varchar2(60);
v_sql varchar2(60);

begin
      v_sql := 'select * from BONUS1 where rownum <= '||v_count;
      dbms_output.put_line('ENAME----JOB----SAL----COMM');
      open v_cur_get_index for v_sql;    -- 打开游标，并且SQL执行结果存放到游标
      LOOP
          fetch v_cur_get_index into v_ENAME,v_JOB,v_SAL,v_COMM;
          exit when v_cur_get_index%notfound;  -- 退出循环
                    
          dbms_output.put_line(v_ENAME||'----'||v_JOB||'----'||v_SAL||'----'||v_COMM);

      END LOOP; -- 游标结束
end curor;

执行结果：


总结
静态游标：在定义的时候指明sql语句，编译时候执行。效率高。

动态游标：可以带有参数，编写sql处理各种复杂的业务场景。更灵活。
推荐博文：存储过程静态游标、隐式游标、显式游标、动态游标、游标遍历

今天写了个动态游标 使用传入参数
关于游标分类以及用法：http://hi.baidu.com/edeed/blog/item/725749540ee73651574e006a.html
思路就是先拼好SQL 然后开动态游标
ORALCE10G也支持正则表达式 呵呵 刚刚好可以实现动态传入参数
procedure TJ_PDA_TESTDATA(v_indicator in varchar) is
type rc is ref cursor;
l_cursor     rc;
l_sqlstr     varchar(2000);
l_sqlstr_new varchar(2000);
l_raw_use    raw_pda_testdata_tj%rowtype;
l_date       varchar(20);
begin
select to_char(sysdate, 'YYYY-MM-DD') into l_date FROM DUAL;
l_sqlstr := 'select a.region_id,
sum(count) totle,
''$v_date'' creatdate,
''$v_indicator'' indicator,
sum(case lev  when 1 then count else 0 end) lev1,
sum(case lev  when 2 then count else 0 end) lev2,
sum(case lev  when 3 then count else 0 end) lev3,
sum(case lev  when 4 then count else 0 end) lev4,
sum(case lev  when 5 then count else 0 end) lev5,
sum(case lev  when 6 then count else 0 end) lev6
from (select zhiju_id region_id, $v_indicator lev, count(1) count
from raw_pda_testdata_use t
where creatdate like ''$v_date%''
and zhiju_id != ''-1''
group by zhiju_id, $v_indicator) a
group by region_id';
SELECT REGEXP_REPLACE(l_sqlstr, '\$v_date', l_date) into l_sqlstr FROM dual;
SELECT REGEXP_REPLACE(l_sqlstr, '\$v_indicator', v_indicator) into l_sqlstr_new  FROM dual;
open l_cursor for l_sqlstr;
loop
fetch l_cursor
into l_raw_use;
exit when l_cursor%notfound;
begin
insert into raw_pda_testdata_tj
values
(l_raw_use.region_id,
l_raw_use.creatdate,
l_raw_use.indicator,
l_raw_use.totle,
l_raw_use.lev1,
l_raw_use.lev2,
l_raw_use.lev3,
l_raw_use.lev4,
l_raw_use.lev5,
l_raw_use.lev6);
commit;
EXCEPTION
WHEN others THEN
rollback;
end;
end loop;
close l_cursor;
EXCEPTION
WHEN others THEN
null;
END; -- Procedure