Oracle存儲(chǔ)過程語法代碼示例詳解

更新時(shí)間：2025年04月16日 11:14:29 作者：實(shí)澤有之，無澤虛之

本文講解Oracle存儲(chǔ)過程語法,包括總體結(jié)構(gòu)、存儲(chǔ)過程聲明、參數(shù)定義、變量聲明、游標(biāo)聲明、行數(shù)據(jù)類型聲明、變量賦值、條件判斷、WHILE循環(huán)、FOR循環(huán)、游標(biāo)使用、異常捕捉、異常處理、存儲(chǔ)過程調(diào)用、存儲(chǔ)過程代碼示例調(diào)用代碼示例、調(diào)用結(jié)果展示,感興趣的朋友一起看看吧

簡(jiǎn)介

存儲(chǔ)過程是一系列SQL語句的集合，可以封裝復(fù)雜的邏輯，實(shí)現(xiàn)特定的功能，可以提高執(zhí)行速度和代碼的復(fù)用性，預(yù)先編譯后存儲(chǔ)在數(shù)據(jù)庫(kù)中，可以通過指定存儲(chǔ)過程的名稱對(duì)其進(jìn)行調(diào)用。

本文主要講解Oracle存儲(chǔ)過程語法，包括：總體結(jié)構(gòu)、存儲(chǔ)過程聲明、參數(shù)定義、變量聲明、游標(biāo)聲明、行數(shù)據(jù)類型聲明、變量賦值、條件判斷、WHILE循環(huán)、FOR循環(huán)、游標(biāo)使用、異常捕捉、異常處理、存儲(chǔ)過程調(diào)用、存儲(chǔ)過程代碼示例、調(diào)用代碼示例、調(diào)用結(jié)果展示。

總體結(jié)構(gòu)

一個(gè)完整的存儲(chǔ)過程結(jié)構(gòu)主要包括：過程聲明部分、過程執(zhí)行部分、異常處理部分，如下所示。

CREATE OR REPLACE PROCEDURE 存儲(chǔ)過程名稱(參數(shù)定義部分)
AS或IS
變量聲明部分
BEGIN
過程執(zhí)行部分
EXCEPTION
異常處理部分
END;

樣例準(zhǔn)備

先準(zhǔn)備樣例表TESTTABLE01，數(shù)據(jù)如下圖，后續(xù)所有代碼示例查詢的數(shù)據(jù)都來自這個(gè)表。

過程聲明部分

聲明格式

CREATE OR REPLACE PROCEDURE 存儲(chǔ)過程名稱(參數(shù)定義部分)
AS或IS

其中，【OR REPLACE】表示如果已存在同名的存儲(chǔ)過程，則直接替換，即將其覆蓋掉，這部分也可以省略，省略后，執(zhí)行編譯時(shí)，如果發(fā)現(xiàn)同名的存儲(chǔ)過程，則會(huì)報(bào)錯(cuò)提示ORA-00955，如下圖。AS或IS兩種寫法效果相同，任選其一。

參數(shù)定義

格式：【參數(shù)名輸入輸出類型數(shù)據(jù)類型】，可定義輸入或輸出的參數(shù)，可以不帶參數(shù)，也可以定義一個(gè)或多個(gè)參數(shù)，多個(gè)參數(shù)用英文逗號(hào)隔開，如下所示。

過程聲明和參數(shù)定義示例

CREATE OR REPLACE PROCEDURE TESTSP01(
para01 in VARCHAR2,
para02 in INT,
para03 out VARCHAR2
)
AS

para01、para02、para03為自定義的參數(shù)名；in或out表示輸入或輸出參數(shù)，輸入?yún)?shù)是在調(diào)用存儲(chǔ)過程時(shí)傳入的，輸出參數(shù)是在存儲(chǔ)過程內(nèi)部賦值的，可以輸出；VARCHAR2、INT表示參數(shù)的數(shù)據(jù)類型。

說明：【in out】表示該參數(shù)既是輸入?yún)?shù)也是輸出參數(shù)。

變量聲明

格式：【變量名數(shù)據(jù)類型】，變量聲明是在BEGIN前面。

可以定義存儲(chǔ)過程中需要用到的變量，每個(gè)變量用英文分號(hào)結(jié)尾，如下所示。

var01 VARCHAR2(10);
var02 VARCHAR2(20);
var03 INT;
var04 NUMBER(5);
var05 VARCHAR2(50);

游標(biāo)聲明

格式：【CURSOR 游標(biāo)名稱 IS 查詢語句】，游標(biāo)是內(nèi)存中用于存儲(chǔ)和檢索查詢結(jié)果集的一種數(shù)據(jù)結(jié)構(gòu)，可以用來遍歷的有多條數(shù)據(jù)的查詢結(jié)果。

示例：

CURSOR cursor01 IS
SELECT COL01,COL02,COL03
FROM TESTTABLE01
WHERE COL02 IS NOT NULL;

行數(shù)據(jù)類型

聲明游標(biāo)之后，需要再聲明一個(gè)行數(shù)據(jù)類型，用以存儲(chǔ)游標(biāo)中的某一行數(shù)據(jù)，也可以存儲(chǔ)表中的一行數(shù)據(jù)。

格式：

行變量名稱游標(biāo)名稱%ROWTYPE;

行變量名稱表名稱%ROWTYPE;

示例：

row01 cursor01%ROWTYPE;
row02 TESTTABLE01%ROWTYPE;

過程執(zhí)行部分

變量賦值

格式：【變量名:=變量值】，如下所示，可以將常量、變量等賦值給變量。

  var01:='hello';
  var02:=var01;
  var03:=8;
  var04:=1.8;
  var05:=var02||' world';

也可以在變量聲明的時(shí)候直接賦值，如下所示。

var03 INT:=8;
var04 NUMBER(5,2):=1.8;
var05 VARCHAR2(50):='hello world';

還可以把SQL語句的查詢結(jié)果賦值給變量，格式：【select 字段名或表達(dá)式 into 變量名 from ...】，如下所示。

  select count(1) into var03 from TESTTABLE01;
  select col02 into var01 from TESTTABLE01 where col01='id01';
  select replace(col02,'value','hello')||' world' into var05 from TESTTABLE01 where col01='id01';

條件判斷

格式如下：若條件判斷表達(dá)式01為真，則執(zhí)行代碼塊01，當(dāng)不滿足表達(dá)式01時(shí)，繼續(xù)判斷，若條件判斷表達(dá)式02為真，則執(zhí)行代碼塊02，若前面的條件都不滿足，則執(zhí)行代碼塊03。其中ELSIF（注意寫法不是ELSEIF）和ELSE部分都可以省略。

IF 條件判斷表達(dá)式01 THEN

代碼塊01

ELSIF 條件判斷表達(dá)式02 THEN

代碼塊02

ELSE

代碼塊03

END IF;

示例：

  IF var01 like 'value%' THEN
    var02:='ret01';
  ELSIF var03>1 THEN
    var02:='ret02';
  ELSE
    var02:='ret03';
  END IF;

WHILE循環(huán)

第一種寫法如下：如果條件表達(dá)式為真，再執(zhí)行里面的代碼塊，否則不執(zhí)行。

WHILE 條件表達(dá)式 LOOP
代碼塊
END LOOP ;

第二種寫法如下：先執(zhí)行一次LOOP中的代碼塊，再判斷條件表達(dá)式，如果為真，則退出循環(huán)，否則，繼續(xù)執(zhí)行循環(huán)，這種寫法與第一種寫法的區(qū)別在于，不論條件表達(dá)式結(jié)果如何，LOOP中的代碼塊會(huì)至少執(zhí)行一次。

LOOP

代碼塊

EXIT WHEN 條件表達(dá)式

END LOOP;

FOR循環(huán)

格式：每一次執(zhí)行循環(huán)時(shí)，會(huì)將索引自增一次，從索引范圍的最小值開始自增，伴隨著每一次循環(huán)，直到索引達(dá)到索引范圍的最大值，就退出循環(huán)。

FOR 索引 IN 索引范圍 LOOP

代碼塊；

END LOOP;

示例：

  -- for循環(huán)依次輸出1到8
  FOR i IN 1..8 LOOP
     var08:=var08||i;
  END LOOP;
  dbms_output.put_line('for循環(huán)輸出1到8:'||var08);

游標(biāo)使用

打開游標(biāo)：【OPEN 游標(biāo)名稱】；

獲取游標(biāo)數(shù)據(jù)放入行變量：【FETCH 游標(biāo)名稱 INTO 行變量名稱】，其中，F(xiàn)ETCH在獲取當(dāng)前行數(shù)據(jù)的同時(shí)，還會(huì)把游標(biāo)指針推進(jìn)到下一條紀(jì)錄，一般放在循環(huán)結(jié)構(gòu)中遍歷獲取每一條數(shù)據(jù)。

游標(biāo)的屬性：

【游標(biāo)名稱%ISOPEN】：表示游標(biāo)是否打開，正常情況返回布爾型；
【游標(biāo)名稱%FOUND】：表示游標(biāo)是否獲取到數(shù)據(jù)，正常情況返回布爾型；
【游標(biāo)名稱%NOTFOUND】：表示游標(biāo)是否沒有獲取到數(shù)據(jù)，正常情況返回布爾型；
【游標(biāo)名稱%ROWCOUNT】：返回游標(biāo)已經(jīng)遍歷獲取的記錄數(shù)，不是總數(shù)量，返回INT型。

示例：

  var061:=cursor01%ISOPEN; -- 游標(biāo)cursor01是否打開
  var062:=cursor01%FOUND; -- 游標(biāo)cursor01是否獲取到數(shù)據(jù)
  var063:=cursor01%NOTFOUND; -- 游標(biāo)cursor01是否沒有獲取到數(shù)據(jù)
  var064:=cursor01%ROWCOUNT; -- 返回游標(biāo)cursor01已經(jīng)遍歷獲取的記錄數(shù)，不是總數(shù)量

關(guān)閉游標(biāo)：【CLOSE 游標(biāo)名稱】。

示例1，使用while...loop循環(huán)方式讀取游標(biāo)數(shù)據(jù)：

  -- while...loop循環(huán)方式
  dbms_output.put_line('while...loop循環(huán)方式:');
  OPEN cursor01; -- 打開游標(biāo)cursor01
  var061:=cursor01%ISOPEN; -- 游標(biāo)是否打開
  var062:=cursor01%FOUND; -- 游標(biāo)是否獲取到數(shù)據(jù)
  var063:=cursor01%NOTFOUND; -- 游標(biāo)是否沒有獲取到數(shù)據(jù)
  FETCH cursor01 INTO row01; -- 從游標(biāo)cursor01獲取行數(shù)據(jù)賦給行變量row01，并將游標(biāo)推進(jìn)到下一行。
  WHILE cursor01%FOUND LOOP --如果cursor01%FOUND結(jié)果為真，則執(zhí)行while循環(huán)
    BEGIN
      var_col01 := row01.COL01; -- 獲取行變量中的具體字段值賦給左邊的變量
      var_col02 := row01.COL02; -- 獲取行變量中的具體字段值賦給左邊的變量
      dbms_output.put_line('獲取記錄數(shù):'||cursor01%ROWCOUNT); -- 輸出內(nèi)容
      dbms_output.put_line('當(dāng)前行數(shù)據(jù):'||var_col01||','||var_col02); -- 輸出內(nèi)容
      FETCH cursor01 INTO row01; -- 從游標(biāo)cursor01獲取行數(shù)據(jù)賦給行變量row01，并將游標(biāo)推進(jìn)到下一行。
    END; 
  END LOOP;
  CLOSE cursor01; -- 關(guān)閉游標(biāo)cursor01

示例2，使用loop... exit when...循環(huán)方式讀取游標(biāo)數(shù)據(jù)：

  -- loop... exit when...循環(huán)方式
  dbms_output.put_line('loop... exit when...循環(huán)方式:');
  OPEN cursor01; -- 打開游標(biāo)cursor01
  LOOP
     FETCH cursor01 INTO row01; -- 從游標(biāo)cursor01獲取行數(shù)據(jù)賦給行變量row01，并將游標(biāo)推進(jìn)到下一行。
     EXIT WHEN cursor01%NOTFOUND; -- 如果cursor01%NOTFOUND結(jié)果為真，則退出循環(huán)。
     var_col01 := row01.COL01; -- 獲取行變量中的具體字段值賦給左邊的變量
     var_col02 := row01.COL02; -- 獲取行變量中的具體字段值賦給左邊的變量
     dbms_output.put_line('獲取記錄數(shù):'||cursor01%ROWCOUNT); -- 輸出內(nèi)容
     dbms_output.put_line('當(dāng)前行數(shù)據(jù):'||var_col01||','||var_col02); -- 輸出內(nèi)容
  END LOOP ;
  CLOSE cursor01; -- 關(guān)閉游標(biāo)cursor01

示例3，使用for循環(huán)方式讀取游標(biāo)數(shù)據(jù)：

  -- for循環(huán)讀取游標(biāo)數(shù)據(jù)
  dbms_output.put_line('for循環(huán)讀取游標(biāo)數(shù)據(jù):');
  FOR row01 IN cursor01 LOOP -- 遍歷游標(biāo)cursor01獲取數(shù)據(jù)賦給行變量row01
     var_col01 := row01.COL01; -- 獲取行變量中的具體字段值賦給左邊的變量
     var_col02 := row01.COL02; -- 獲取行變量中的具體字段值賦給左邊的變量
     dbms_output.put_line('獲取記錄數(shù):'||cursor01%ROWCOUNT); -- 輸出內(nèi)容
     dbms_output.put_line('當(dāng)前行數(shù)據(jù):'||var_col01||','||var_col02); -- 輸出內(nèi)容
  END LOOP;

異常處理部分

異常捕捉

格式：EXCEPTION可以捕捉到存儲(chǔ)過程執(zhí)行中遇到的異常，WHEN后面是具體的異常名稱，捕捉到具體異常后，就會(huì)執(zhí)行對(duì)應(yīng)WHEN下面的異常處理代碼塊，如果捕捉到的異常和前面任何一個(gè)WHEN后面的異常名稱都不匹配，則直接執(zhí)行【W(wǎng)HEN OTHERS THEN】下面的異常處理代碼。

EXCEPTION
WHEN 異常01 THEN
異常處理代碼塊01
WHEN 異常02 THEN

異常處理代碼塊02

......
WHEN OTHERS THEN
異常處理代碼塊n

異常處理部分也可以只有OTHERS部分，格式如下，捕捉到任何異常都會(huì)跳轉(zhuǎn)到OTHERS對(duì)應(yīng)的異常處理代碼塊。

EXCEPTION
WHEN OTHERS THEN
異常處理代碼塊

異常處理

SQLCODE：獲取錯(cuò)誤代碼，

SQLERRM：獲取具體的錯(cuò)誤信息，

ROLLBACK：回滾事務(wù)。

異常處理示例：

EXCEPTION
  WHEN NO_DATA_FOUND THEN 
    dbms_output.put_line('未查詢到數(shù)據(jù)！');
  WHEN CURSOR_ALREADY_OPEN THEN 
    dbms_output.put_line('游標(biāo)已經(jīng)打開！');
  WHEN OTHERS THEN
    dbms_output.put_line(SQLCODE); -- 輸出錯(cuò)誤代碼
    dbms_output.put_line(SQLERRM); -- 輸出錯(cuò)誤信息

調(diào)用存儲(chǔ)過程調(diào)用命令格式

如果存儲(chǔ)過程不帶參數(shù)，調(diào)用格式如下。

call 存儲(chǔ)過程名稱();

如果存儲(chǔ)過程只帶輸入?yún)?shù)，參數(shù)值用英文逗號(hào)隔開，傳入的值要和存儲(chǔ)過程定義的參數(shù)的順序和類型保持一致，調(diào)用格式如下。

call 存儲(chǔ)過程名稱(參數(shù)值1,參數(shù)值2,...);

如果存儲(chǔ)過程帶輸出參數(shù)，比如某個(gè)存儲(chǔ)過程有三個(gè)參數(shù)，前兩個(gè)為輸入?yún)?shù)，第三個(gè)為輸出參數(shù)，調(diào)用格式如下，需要先定義一個(gè)變量用來接收輸出參數(shù)值，數(shù)據(jù)類型要和輸出參數(shù)相同。

BEGIN
DECLARE
變量名01 數(shù)據(jù)類型;
BEGIN
存儲(chǔ)過程名稱(傳入值01,傳入值02,變量名01);
END;
END;

完整存儲(chǔ)過程示例

-- 存儲(chǔ)過程聲明，可以帶參數(shù)，也可以不帶參數(shù)
CREATE OR REPLACE PROCEDURE TESTSP01(
para01 in VARCHAR2,
para02 in INT,
para03 out VARCHAR2
)
AS
-- 變量定義，可以在變量定義時(shí)賦值。
var01 VARCHAR2(10);
var02 VARCHAR2(20);
var03 INT:=0;
var04 NUMBER(5,2):=8.88;
var05 VARCHAR2(50):='initvalue';
var06 INT;
var061 BOOLEAN;
var062 BOOLEAN;
var063 BOOLEAN;
var07 VARCHAR2(30);
var08 VARCHAR2(20):='';
var_col01 VARCHAR2(20);
var_col02 VARCHAR2(20);
-- 游標(biāo)聲明
CURSOR cursor01 IS
SELECT COL01,COL02,COL03
FROM TESTTABLE01
WHERE COL02 IS NOT NULL;
-- 行變量聲明
row01 cursor01%ROWTYPE;
row02 TESTTABLE01%ROWTYPE;
BEGIN -- 過程執(zhí)行部分
  -- 變量賦值，可以將常量或變量賦值給變量
  dbms_output.put_line('變量賦值示例:');
  var01:='hello';
  var02:=var01;
  var03:=8;
  var04:=1.8;
  dbms_output.put_line('var01:'||var01||','||'var02:'||var02||','||'var03:'||var03||','||'var04:'||var04);
  -- 通過sql查詢給變量賦值
  select col02 into var05 from TESTTABLE01 where col01='id01';
  select count(1) into var06 from TESTTABLE01;
  select replace(col02,'value','hello')||' world' into var07 from TESTTABLE01 where col01='id01';
  dbms_output.put_line('var05:'||var05||','||'var06:'||var06||','||'var07:'||var07);
  -- IF判斷示例
  dbms_output.put_line('IF判斷示例:');
  IF para01 like 'value%' THEN
    dbms_output.put_line('IF判斷滿足第一個(gè)分支');
  ELSIF para02>1 THEN
    dbms_output.put_line('IF判斷滿足第二個(gè)分支');
  ELSE
    dbms_output.put_line('IF判斷滿足第三個(gè)分支');
  END IF;
  -- while...loop循環(huán)方式讀取游標(biāo)數(shù)據(jù)
  dbms_output.put_line('while...loop循環(huán)方式讀取游標(biāo)數(shù)據(jù):');
  OPEN cursor01; -- 打開游標(biāo)cursor01
  var061:=cursor01%ISOPEN; -- 游標(biāo)cursor01是否打開  
  var062:=cursor01%FOUND; -- 游標(biāo)cursor01是否獲取到數(shù)據(jù)
  var063:=cursor01%NOTFOUND; -- 游標(biāo)cursor01是否沒有獲取到數(shù)據(jù)
  FETCH cursor01 INTO row01; -- 從游標(biāo)cursor01獲取行數(shù)據(jù)賦給行變量row01，并將游標(biāo)推進(jìn)到下一行。
  WHILE cursor01%FOUND LOOP --如果cursor01%FOUND結(jié)果為真，則執(zhí)行while循環(huán)
    BEGIN
      var_col01 := row01.COL01; -- 獲取行變量中的具體字段值賦給左邊的變量
      var_col02 := row01.COL02; -- 獲取行變量中的具體字段值賦給左邊的變量
      dbms_output.put_line('獲取記錄數(shù):'||cursor01%ROWCOUNT); -- 輸出內(nèi)容
      dbms_output.put_line('當(dāng)前行數(shù)據(jù):'||var_col01||','||var_col02); -- 輸出內(nèi)容
      FETCH cursor01 INTO row01; -- 從游標(biāo)cursor01獲取行數(shù)據(jù)賦給行變量row01，并將游標(biāo)推進(jìn)到下一行。
    END; 
  END LOOP;
  CLOSE cursor01; -- 關(guān)閉游標(biāo)cursor01
  -- loop... exit when...循環(huán)方式讀取游標(biāo)數(shù)據(jù)
  dbms_output.put_line('loop... exit when...循環(huán)方式讀取游標(biāo)數(shù)據(jù):');
  OPEN cursor01; -- 打開游標(biāo)cursor01
  LOOP
     FETCH cursor01 INTO row01; -- 從游標(biāo)cursor01獲取行數(shù)據(jù)賦給行變量row01，并將游標(biāo)推進(jìn)到下一行。
     EXIT WHEN cursor01%NOTFOUND; -- 如果cursor01%NOTFOUND結(jié)果為真，則退出循環(huán)。
     var_col01 := row01.COL01; -- 獲取行變量中的具體字段值賦給左邊的變量
     var_col02 := row01.COL02; -- 獲取行變量中的具體字段值賦給左邊的變量
     dbms_output.put_line('獲取記錄數(shù):'||cursor01%ROWCOUNT); -- 輸出內(nèi)容
     dbms_output.put_line('當(dāng)前行數(shù)據(jù):'||var_col01||','||var_col02); -- 輸出內(nèi)容
  END LOOP ;
  CLOSE cursor01; -- 關(guān)閉游標(biāo)cursor01
  -- for循環(huán)依次輸出1到8
  FOR i IN 1..8 LOOP
     var08:=var08||i;
  END LOOP;
  dbms_output.put_line('for循環(huán)輸出1到8:'||var08);
  -- for循環(huán)讀取游標(biāo)數(shù)據(jù)
  dbms_output.put_line('for循環(huán)讀取游標(biāo)數(shù)據(jù):');
  FOR row01 IN cursor01 LOOP -- 遍歷游標(biāo)cursor01獲取數(shù)據(jù)賦給行變量row01
     var_col01 := row01.COL01; -- 獲取行變量中的具體字段值賦給左邊的變量
     var_col02 := row01.COL02; -- 獲取行變量中的具體字段值賦給左邊的變量
     dbms_output.put_line('獲取記錄數(shù):'||cursor01%ROWCOUNT); -- 輸出內(nèi)容
     dbms_output.put_line('當(dāng)前行數(shù)據(jù):'||var_col01||','||var_col02); -- 輸出內(nèi)容
  END LOOP;
  --給輸出參數(shù)賦值
  para03:='存儲(chǔ)過程執(zhí)行成功！';
EXCEPTION -- 異常處理部分
  WHEN NO_DATA_FOUND THEN 
    dbms_output.put_line('未查詢到數(shù)據(jù)！');
  WHEN CURSOR_ALREADY_OPEN THEN 
    dbms_output.put_line('游標(biāo)已經(jīng)打開！');
  WHEN OTHERS THEN
    dbms_output.put_line(SQLCODE); -- 輸出錯(cuò)誤代碼
    dbms_output.put_line(SQLERRM); -- 輸出錯(cuò)誤信息
END;

調(diào)用存儲(chǔ)過程示例

BEGIN
  DECLARE
    -- 定義變量接收輸出參數(shù)值，數(shù)據(jù)類型要和輸出參數(shù)相同
    outpara VARCHAR2(50); 
  BEGIN
    -- 調(diào)用存儲(chǔ)過程，給輸入?yún)?shù)賦值，將事先定義的變量傳給輸出參數(shù)。
    TESTSP01('value_in',8,outpara); 
    -- 打印輸出參數(shù)
    dbms_output.put_line('輸出參數(shù)值:'||outpara); 
  END;
END;

調(diào)用輸出結(jié)果展示