oracle基礎學習(2)

oracle迴圈的幾種寫法:declarex number :=5;begin--x:=0;loopx:=x+1;/*if (x=10) then exit; end if;*/exit when x=10;dbms_output.put_line('x='||x);end loop;dbms_output.put_line('outer');end;/------------------------------declarex number :=5;beginwhile x<10 loopx:=x+1;dbms_output.put_line('x='||x);end loop;dbms_output.put_line('outer');end;/-----------------------------beginfor x in 2..10 loopdbms_output.put_line('x='||x);end loop;end;/-------------------------------declarex number:=5;begin<>--標記x:=x+1;dbms_output.put_line('x='||x);if x<10 then goto label1;end if;end;/---------------異常處理Exceptionwhen .. then...常見系統異常DUP_VAL_ON_INDEXNO_DATA_FOUNDTOO_MANY_ROWSVALUE_ERRORZERO_DIVIDEe.g.:declareva varchar2(20);beginselect A into va from abc where F_NVAR='abc';dbms_output.put_line(va);exceptionwhen NO_DATA_FOUND THENDBMS_OUTPUT.PUT_LINE('未找到資料');end;-----------------------declareva varchar2(20);vi integer;beginselect A into va from abc where F_NVAR='中國人民萬';dbms_output.put_line(va);vi:=1/0;exceptionwhen NO_DATA_FOUND THENDBMS_OUTPUT.PUT_LINE('未找到資料');when OTHERS THENDBMS_OUTPUT.PUT_LINE('其它問題');end;---------------------declareva varchar2(20);vi integer;e exception;beginselect A into va from abc where F_NVAR='中國人民萬';dbms_output.put_line(va);--vi:=1/0;raise e;exceptionwhen NO_DATA_FOUND THENDBMS_OUTPUT.PUT_LINE('未找到資料');when e THENDBMS_OUTPUT.PUT_LINE('發生問題A');when OTHERS THENDBMS_OUTPUT.PUT_LINE('其它問題');end;-----------------------複合變數:記錄TYPE type_name IS RECORD(Variable_name datatype,Variable_name datatype,...);-----------declare myrec abc%rowtype;beginselect * into myrec from abc where a='aaa';dbms_output.put_line(myrec.a||myrec.b||myrec.c);end;-------------------ROW_NUMBER() 函數 與 ROWNUM偽列select row_number() over (order by a) sa,a,b,c,f_nvar from abcselect * from abc where rownum<10declare myrec abc%rowtype;beginselect * into myrec from abc where a='AAA1111123' and ROWNUM<2;dbms_output.put_line(myrec.a||myrec.b||myrec.c||myrec.f_nvar);end;-------------------------------------一個簡單的預存程序:create or replace procedure proc_show_abc_by_a(pa varchar2)asfa varchar2(20);beginselect a into fa from spark.abc where a=pa;dbms_output.put_line(fa);end;/-------------------------------------同義字 create or replace public synonym myabc for spark.abc;-------------------------------------序列create sequence myseqstart with 1increment by 1ordernocycle;