Oracle存储过程，带游标

[sql] view plain copy

print ?

1. create or replace procedure aa is  
2.  bb number;  
3. cursor cur is    
4. select t.id,v.description from t_user t ,v_userdep v where t.removed=0 and t.login_name = substr(v.login_name,1,12);  
5. begin  
6. for c in cur loop  
7.       bb := 0;  
8.       select count(*) into bb from t_user_temp s where s.id = c.id;  
9.       dbms_output.put_line(bb);  
10.       if(bb<=0) then  
11.           insert into t_user_temp s (s.id,s.name) values(c.id,c.description);  
12.           commit;  
13.        else   
14.           update t_user_temp s set s.name = s.name || ',' || c.description where s.id=c.id;  
15.           end if;  
16.         end loop;  
17. end aa;  

create or replace procedure aa is
 bb number;
cursor cur is  
select t.id,v.description from t_user t ,v_userdep v where t.removed=0 and t.login_name = substr(v.login_name,1,12);
begin
for c in cur loop
      bb := 0;
      select count(*) into bb from t_user_temp s where s.id = c.id;
      dbms_output.put_line(bb);
      if(bb<=0) then
          insert into t_user_temp s (s.id,s.name) values(c.id,c.description);
          commit;
       else 
          update t_user_temp s set s.name = s.name || ',' || c.description where s.id=c.id;
          end if;
        end loop;
end aa;

[sql] view plain copy

print ?

1. CREATE OR REPLACE PROCEDURE F_holiday is  
2.   
3.   cursor mycursor is select t.hol_days,t.hol_days_left,t.hol_days_wait,t.hol_id,t.remark from t_hol_holiday t where t.removed=0 ;  
4.    t_mycursor mycursor%ROWTYPE;  
5.   
6. begin  
7.  -- strSql:='select t.hol_days,t.hol_days_left,t.hol_days_wait,t.hol_id,t.remark from t_hol_holiday t where t.removed=0 ';  
8.   
9.   open mycursor;  
10.      --FETCH v_mysursor INTO holDays,holDaysLeft,holDaysWait,holId,remark;  
11.   Loop  
12.      FETCH mycursor INTO t_mycursor;  
13.      exit when mycursor%NOTFOUND;  
14.     -- WHILE v_mysursor%FOUND LOOP  
15.         insert into t_hol_holiday (ID,hol_year,hol_days,hol_days_left,hol_days_wait,removed,operator,operate_time,hol_id,hol_state,remark) values (sys_guid(),'2011',t_mycursor.hol_days,t_mycursor.hol_days,t_mycursor.hol_days,'0','1156',to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),t_mycursor.hol_id,'725',t_mycursor.remark);  
16.   End LOOP;  
17.   close mycursor; --关闭游标  
18.    EXCEPTION  
19.      WHEN NO_DATA_FOUND THEN  
20.       null;  
21.      WHEN OTHERS THEN  
22.        -- Consider logging the error and then re-raise  
23.        RAISE;  
24. end F_holiday;  

CREATE OR REPLACE PROCEDURE F_holiday is

  cursor mycursor is select t.hol_days,t.hol_days_left,t.hol_days_wait,t.hol_id,t.remark from t_hol_holiday t where t.removed=0 ;
   t_mycursor mycursor%ROWTYPE;

begin
 -- strSql:='select t.hol_days,t.hol_days_left,t.hol_days_wait,t.hol_id,t.remark from t_hol_holiday t where t.removed=0 ';

  open mycursor;
     --FETCH v_mysursor INTO holDays,holDaysLeft,holDaysWait,holId,remark;
  Loop
     FETCH mycursor INTO t_mycursor;
     exit when mycursor%NOTFOUND;
    -- WHILE v_mysursor%FOUND LOOP
        insert into t_hol_holiday (ID,hol_year,hol_days,hol_days_left,hol_days_wait,removed,operator,operate_time,hol_id,hol_state,remark) values (sys_guid(),'2011',t_mycursor.hol_days,t_mycursor.hol_days,t_mycursor.hol_days,'0','1156',to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),t_mycursor.hol_id,'725',t_mycursor.remark);
  End LOOP;
  close mycursor; --关闭游标
   EXCEPTION
     WHEN NO_DATA_FOUND THEN
      null;
     WHEN OTHERS THEN
       -- Consider logging the error and then re-raise
       RAISE;
end F_holiday;

[sql] view plain copy

print ?

1. CREATE OR REPLACE PROCEDURE rrrr is  
2.   
3.   cursor mycursor is select t.mc_dx from t_func_xt_ywdxsm t ;  
4.   
5.    t_mycursor mycursor%ROWTYPE;  
6.   
7. begin  
8.  -- strSql:='select t.hol_days,t.hol_days_left,t.hol_days_wait,t.hol_id,t.remark from t_hol_holiday t where t.removed=0 ';  
9.   
10.   open mycursor;  
11.      --FETCH v_mysursor INTO holDays,holDaysLeft,holDaysWait,holId,remark;  
12.   Loop  
13.      FETCH mycursor INTO t_mycursor;  
14.      exit when mycursor%NOTFOUND;  
15.     -- WHILE v_mysursor%FOUND LOOP  
16.         insert into t_rectification_user_conf (reserve1,removed) values(t_mycursor.mc_dx,0);  
17.   End LOOP;  
18.   close mycursor; --关闭游标  
19.    EXCEPTION  
20.      WHEN NO_DATA_FOUND THEN  
21.       null;  
22.      WHEN OTHERS THEN  
23.        -- Consider logging the error and then re-raise  
24.        RAISE;  
25. end rrrr;  

CREATE OR REPLACE PROCEDURE rrrr is

  cursor mycursor is select t.mc_dx from t_func_xt_ywdxsm t ;

   t_mycursor mycursor%ROWTYPE;

begin
 -- strSql:='select t.hol_days,t.hol_days_left,t.hol_days_wait,t.hol_id,t.remark from t_hol_holiday t where t.removed=0 ';

  open mycursor;
     --FETCH v_mysursor INTO holDays,holDaysLeft,holDaysWait,holId,remark;
  Loop
     FETCH mycursor INTO t_mycursor;
     exit when mycursor%NOTFOUND;
    -- WHILE v_mysursor%FOUND LOOP
        insert into t_rectification_user_conf (reserve1,removed) values(t_mycursor.mc_dx,0);
  End LOOP;
  close mycursor; --关闭游标
   EXCEPTION
     WHEN NO_DATA_FOUND THEN
      null;
     WHEN OTHERS THEN
       -- Consider logging the error and then re-raise
       RAISE;
end rrrr;

test 窗口 在下方输入 测试数据 执行后即可

command 窗口中 如下执行

[sql] view plain copy

print ?

1. set serveroutput on  
2. SQL> declare  
3.   2  i number;  
4.   3  begin  
5.   4  pro_test(1,i);  
6.   5  dbms_output.put_line(i);  
7.   6  end;  
8.   7  /  

set serveroutput on
SQL> declare
  2  i number;
  3  begin
  4  pro_test(1,i);
  5  dbms_output.put_line(i);
  6  end;
  7  /

[sql] view plain copy

print ?

1. create or replace procedure testt is  
2.   
3. cursor cur is    
4.   select *  from c_contract c where c.removed = 0 and c.contract_price is not null;  
5.    n number;  
6. begin  
7.   
8.   
9.  begin  
10.  n:=1;  
11.    dbms_output.put_line (n);  
12.        for c in cur loop  
13.           dbms_output.put_line (c.id);  
14.            n := to_number (c.contract_price);  
15.             
16.           end loop;  
17.   end;  
18.   
19.   
20. end testt;