Oracle存储过程，带游标

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);beginfor 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;

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;

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 窗口中 如下执行

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

create or replace procedure testt iscursor cur is    select *  from c_contract c where c.removed = 0 and c.contract_price is not null;   n number;begin begin n:=1;   dbms_output.put_line (n);       for c in cur loop          dbms_output.put_line (c.id);           n := to_number (c.contract_price);                    end loop;  end;end testt;