萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> Oracle 教學

Oracle 存儲過程開發指南:語法、參數模式、加密與重編譯選項

本文綜述 Oracle 存儲過程核心語法要素 (注意:示例中混雜 SQL Server 語法,下文已修正為 Oracle 標準)。

基本語法

CREATE [OR REPLACE] PROCEDURE procedure_name
  [ (parameter [IN|OUT|IN OUT] datatype [DEFAULT expr] [, ...]) ]
  [ AUTHID {DEFINER | CURRENT_USER} ]
  [ DETERMINISTIC | PARALLEL_ENABLE ]
IS | AS
  -- 宣告區
BEGIN
  -- 執行區
  NULL;
EXCEPTION
  -- 異常處理
END [procedure_name];
/

參數模式

  • IN (默認):只讀,可傳常量/運算式。
  • OUT:只寫,需傳變量,初始為 NULL。
  • IN OUT:讀寫,需傳變量,帶入初值並可修改返回。

編譯選項

  • DETERMINISTIC:相同輸入必得相同輸出,允許函數結果緩存。
  • PARALLEL_ENABLE:聲明可在並行查詢中安全調用。
  • AUTHID CURRENT_USER:調用者權限模式 (見 ID 147730)。

實戰範例

1. 簡單查詢

CREATE OR REPLACE PROCEDURE QUERY_BOOK IS
BEGIN
  FOR R IN (SELECT * FROM BOOK) LOOP
    DBMS_OUTPUT.PUT_LINE(R.書名 || ':' || R.價格);
  END LOOP;
END;
/
EXEC QUERY_BOOK;

2. 帶 OUT 參數的插入並返回統計

CREATE OR REPLACE PROCEDURE INSERT_BOOK (
  P_ID      IN  BOOK.編號%TYPE,
  P_NAME    IN  BOOK.書名%TYPE,
  P_PRICE   IN  BOOK.價格%TYPE,
  P_TOTAL   OUT NUMBER
) IS
BEGIN
  INSERT INTO BOOK VALUES (P_ID, P_NAME, P_PRICE);
  SELECT SUM(價格) INTO P_TOTAL FROM BOOK;
END;
/
-- 調用
VAR TOTAL NUMBER;
EXEC INSERT_BOOK('003','Delphi指南',100,:TOTAL);
PRINT TOTAL;

3. 多表連接生成臨時結果 (Oracle 用全局臨時表 GTT)

CREATE GLOBAL TEMPORARY TABLE TEMP_SALE ON COMMIT PRESERVE ROWS AS
SELECT A.產品編號, A.產品名稱, B.客戶名, B.客戶訂金,
       A.客戶訂數 * B.客戶訂金 AS 總金額
FROM   PRODUCT A JOIN ORDER_TAB B ON A.產品編號 = B.產品編號;
-- 會話私有,提交/斷開自動清理

注意:原文示例使用 SQL Server 語法 (#temp, TOP, IDENTITY),已修正為 Oracle 等效寫法。

copyright © 萬盛學電腦網 all rights reserved