日期:2014-05-16  浏览次数:20431 次

新手存储过程碰到的小细节
SQL Window
create or replace procedure Pro_SPL_Upsno is
  v_sql varchar2(4000);
  cursor c1 is                  --游标的使用
    select spli_sid v_supplierid,
           lpad(spli_sno, 10, '0') v_supplierno,
           spli_sname v_suppliername
      from spl_supplierinfo
     where spil_nvalid = 1
       and length(spli_sno) = 8
       and rownum < 100
     order by spli_sno;

begin                           --有begin无end
  v_sql := '';                     --动态sql
  for v_c1 in c1 loop                --循环的使用
    v_sql := 'update spl_supplierinfo set SPLI_SNO = ''' || v_c1.v_supplierno ||
             ''' where spli_sid = ''' || v_c1.v_supplierid || '''';  --注意''的使用
    DBMS_OUTPUT.PUT_LINE('V_SQL:' || v_sql);
    Execute immediate v_sql;             --immediate 的使用
    commit;
  end loop;

EXCEPTION                           --exception的使用
  WHEN OTHERS THEN
    ROLLBACK;
    RAISE;
end Pro_SPL_Upsno;


Command Window


SQL> exec pro_spl_upsno;                

PL/SQL procedure successfully completed    

 

SQL> set serveroutput on;                  --如果需要打印

SQL> exec pro_spl_upsno;   

V_SQL:update spl_supplierinfo set SPLI_SNO = '0031400026' where spli_sid = '88372'
.
.
.
.
.
.
PL/SQL procedure successfully completed  


ORA-20000: ORU-10027: buffer overflow解决2种方法
1.SQL>exec DBMS_OUTPUT.ENABLE(1000000);           --调用了dbms_output包
2.SQL>set serveroutput on size 1000000         --设置成更大。