Oracle存儲過程語法

創建基本的存儲過程

CREATE OR REPLACE PROCEDURE MyProName IS
BEGIN
  NULL;
END;

行1:CREATE?OR?REPLACE?PROCEDURE?是一個SQL語句通知Oracle數據庫去創建一個叫做skeleton存儲過程,?如果存在就覆蓋它;

行2:IS關鍵詞表明后面將跟隨一個PL/SQL體。

行3:BEGIN關鍵詞表明PL/SQL體的開始。

行4:NULL?PL/SQL語句表明什么事都不做，這句不能刪去，因為PL/SQL體中至少需要有一句;

行5:END關鍵詞表明PL/SQL體的結束

創建帶參數存儲過程

現在想給存儲過程加上參數，定義變量怎么處理呢？如下

CREATE OR REPLACE PROCEDURE MyPro(param1 in varchar2,param2 out varchar2) as
  v_cnt      number; --定義變量1
  tablename1 varchar2(80); --定義變量2
  sqlTxt     varchar2(2000); --定義變量2
BEGIN
  --處理邏輯
  NULL;
END;

?(1)存儲過程參數不帶取值范圍，in表示傳入，out表示輸出；類型可以使用任意Oracle中的合法類型。

?(2) 變量帶取值范圍，后面接分號

創建帶事務的存儲過程

CREATE OR REPLACE PROCEDURE MyPro(param1 in varchar2,param2 out varchar2) asv_cnt      number; --定義變量1tablename1 varchar2(80); --定義變量2sqlTxt     varchar2(2000); --定義變量2
BEGIN--處理邏輯NULL;commit;--提交事務
ExceptionWhen others thenDbms_output.Put_line(sqlerrm);--打印輸出錯誤Rollback;--回滾事務
END;

?提交事務，存在異常則回滾事務；

使用游標

游標遍歷

CREATE OR REPLACE PROCEDURE MyPro(param1 in varchar2, param2 out varchar2) asv_cnt      number; --定義變量1tablename1 varchar2(80); --定義變量2sqlTxt     varchar2(2000); --定義變量2
BEGIN--定義游標cursor c_tab_temp1 isselect t.modelnumber, t.tablename from d_modelmap t;c_bom_row1 c_tab_temp1%rowtype;--處理邏輯--循環游標for c_bom_row1 in c_tab_temp1 looptablename1 := c_bom_row1.tablename;  --取游標中的值END LOOP;commit; --提交事務
ExceptionWhen others thenDbms_output.Put_line(sqlerrm); --打印輸出錯誤Rollback; --回滾事務
END;

返回游標

create or replace procedure test(
res out varchar2,
p_cur out sys_refcursor)   as
Begin
open p_cur forselect * from tableName
res:='';
end;

其他使用技巧

分支條件判斷

--分支判斷if tablename1 <> '' then--處理邏輯null;else--處理邏輯null;end if;

動態sql

 --拼接動態sqlsqltxt := 'update ' || tablename1 || '  tset t.partid=(select t3.f_id from i_partlist t3 where t3.part_no=t.part_noand nvl(t3.part_techstate,'' '')=nvl(t.part_techstate,'' ''))where not  exists(select t2.f_id from i_Partlist t2 where t2.f_id=t.partid)';--執行動態sqlexecute immediate sqlTxt;

給變量賦值

(1)用select XX into xx給變量賦值

 select count(1)  into v_count  from A t where t.A='aaa';

(2)直接賦值

V_TEST := 123;

while 循環

  WHILE V_TEST=1 LOOPBEGINXXXXEND;END LOOP;

判斷是否存在

在判斷語句前最好先用count（*）函數判斷是否存在該條操作記錄

  --判斷是否存在，v_count是定義的數值變量select count(1)into v_countfrom A twhere t.A='aaa';if v_count = 0 thenelseend if;