oracle 匿名存储过程

set serveroutput on;

declare l_count number(10,0) := 0;  

Cursor cur_badData is  with baddata as  (  select  distinct hs.hs_id from hunt_submittal hs, item it,hunt_choice hc  where hs.hs_application_it_id = it.it_id  and it.it_status_ind = 'A'  and hs.hs_status_ind = 'P'  and hs.hs_id = hc.hs_id  and hs.cu_state_customer_id = '99999999999'  and hc.hc_status_ind = 'A'  )  select hs.hs_id,cu.cu_state_customer_id from hunt_submittal hs, baddata bd,customer cu  where hs.hs_id = bd.hs_id  and hs.cu_id = cu.cu_id;  

begin  

For v_cur in cur_badData loop  l_count := l_count +1;

 update hunt_submittal  set hs_status_ind = 'A',  cu_state_customer_id =  v_cur.cu_state_customer_id  where hs_id  = v_cur.hs_id;

end loop;

DBMS_OUTPUT.PUT_LINE('total updated is - ' ||to_char(l_count));

EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Error in  pl sql SQLCODE ' || to_char(SQLCODE) || '. ' || 'SQLERRM - ' ||SQLERRM);

end;

/

 

转载于:https://www.cnblogs.com/manaatmoon/p/7261758.html