Oracle 預存程序 PROCEDURE

標籤：com   not   mit   else   using   資料庫系統   遊標   otf   when   

預存程序  

    一組用於完成特定資料庫功能的SQL語句集，該SQL語句集經過編譯後儲存在資料庫系統中。在使用時候，使用者通過指定已經定義的預存程序名字並給出相應的預存程序參數來調用並執行它，從而完成一個或一系列的資料庫操作。

    包含三部分：過程聲明，執行過程部分，預存程序異常（可選）。

樣本

create or replace procedure sp_(  p_sResult   out integer,--傳回值   p_ID       in   number --ID) as      v_id       number(16);      cursor  v_chac_cr  is                select t.money from T001 t   where t.sid=p_ID;--遊標begin   open v_chac_cr();   loop        fetch v_chac_cr into v_id;         exit when v_chac_cr%notfound;        insert into F002(cnt)  values (v_id);   end loop;   close v_chac_cr;   commit;   p_sResult:=1;exception--例外處理   when others then   p_sResult:=0;   rollback;end sp_;　　

 項目運用

  1.擷取表下一個ID值

CREATE OR REPLACE PROCEDURE PNEXTID( tablename   IN   VARCHAR2 , idno     OUT   NUMBER)IS sqlstring   VARCHAR2 ( 500 );BEGIN sqlstring := ‘SELECT nvl(max(id),0)+1 FROM ‘ ||tablename;  execute immediate sqlstring into idno;               -- 動態執行  merge into tSequence a using ( select idno as ID, tablename as Name from dual) b   --組合成表   merge into 確定表  on (upper(a.Name)=upper(b.Name))                       --表名大寫化  when matched then update set a.ID= case when b.ID>a.ID then b.ID else a.ID+ 1 end   -- update;  case when   then  else  end;  when not matched then insert (ID, Name ) values (b.ID,b.Name);                           --insert;  select ID into idno from tSEQUENCE WHERE upper( name ) = upper(tablename);EXCEPTION  WHEN OTHERS THEN   idno := - 1 ;END pNextID;

　2.返回列表資料(遊標)

CREATE OR REPLACE PROCEDURE SP_CONTENT (      o_result out types.cursorType, --結果集      ip_pwf  in varchar2) --關聯流程     as   /*  --型別宣告是遊標變數    create or replace package TestPackage is type outlist is ref cursor; */  v_sql varchar2(1000);  vn_count  numeric(12,0);  vn_num    numeric(12,0);  vn_id     numeric(12,0);  vs_table  varchar2(30);  vn_inst numeric(12,0);begin   vn_count:=length(ip_pwf);   vn_num:=instr(ip_pwf,‘.‘);   vn_id:=to_number(substr(ip_pwf,1,vn_num-1));   vs_table:=substr(ip_pwf,vn_num+1,vn_count-vn_num); begin v_sql:=‘select nvl(max(job_code),0)  from ‘||vs_table||‘ where company = ‘||vn_id||‘‘; execute immediate v_sql into vn_inst; exception   when others then   vn_inst:=0; end; open o_result for select caller,enddate,content from tuser t where class_code < vn_inst;end SP_CONTENT;

　　

　

 

Oracle 預存程序 PROCEDURE