程式師世界 >> 數據庫知識 >> Oracle數據庫 >> Oracle教程 >> Oracle技術_5分鐘會用存儲過程_存儲過程簡單實例(包含循環、條件、增改查、參數傳入、變量賦值、java調用等)

Oracle技術_5分鐘會用存儲過程_存儲過程簡單實例(包含循環、條件、增改查、參數傳入、變量賦值、java調用等)

編輯：Oracle教程

Oracle技術_5分鐘會用存儲過程_存儲過程簡單實例(包含循環、條件、增改查、參數傳入、變量賦值、java調用等)

例子業務功能：

1.根據傳入的類型A_TYPE聯合查詢PROCEDURE_TEST_A表、PROCEDURE_TEST_A_SUB表中的數據，並顯示主要內容。

2.根據傳入的類型A_TYPE聯合查詢PROCEDURE_TEST_A表、PROCEDURE_TEST_A_SUB表，並將結果插入PROCEDURE_TEST_B表中。

這裡若B_EMAIL字段為空則取傳入的默認值。

3.若PROCEDURE_TEST_B表進行了插入操作，則分組統計ASUB_NUMBER字段更新或插入PROCEDURE_TEST_C表中。

裡面包含了存儲過程常用的大部分操作，包含循環、條件、增改查、參數傳入、變量賦值等，話不多說直接上例子：

1.創建所需要的表，並初始化數據

創建4張表，其中3個表需要初始數據，代碼如下：

--數據來源表PROCEDURE_TEST_A
CREATE TABLE PROCEDURE_TEST_A
(
  A_ID     VARCHAR2(255) NOT NULL,
  A_USER   VARCHAR2(255),
  A_EMAIL  VARCHAR2(255),
  A_TYPE   VARCHAR2(5),
  CONSTRAINT PROCEDURE_TEST_A PRIMARY KEY (A_ID)  
);
--數據來源表的子表PROCEDURE_TEST_A_SUB
CREATE TABLE PROCEDURE_TEST_A_SUB
(
  ASUB_ID      VARCHAR2(255) NOT NULL,
  ASUB_NAME    VARCHAR2(255),
  ASUB_NUMBER  NUMBER(18,2),
  ASUB_COMMENT VARCHAR2(2000),
  A_ID         VARCHAR2(255),
  CONSTRAINT PROCEDURE_TEST_A_SUB PRIMARY KEY (ASUB_ID),
  CONSTRAINT PROCEDURE_TEST FOREIGN KEY (A_ID) REFERENCES PROCEDURE_TEST_A (A_ID)
);
--數據整合後插入表
CREATE TABLE PROCEDURE_TEST_B
(
  B_ID       VARCHAR2(255) NOT NULL,
  B_USER     VARCHAR2(255),
  B_EMAIL    VARCHAR2(255),
  B_NAME     VARCHAR2(255),
  B_NUMBER   NUMBER(18,2),
  B_COMMENT  VARCHAR2(2000),
  CONSTRAINT PROCEDURE_TEST_B PRIMARY KEY (B_ID)  
);
--數據整合後更新表
CREATE TABLE PROCEDURE_TEST_C
(
  C_USER    VARCHAR2(255) NOT NULL,
  C_NUMBER   NUMBER(18,2),
  CONSTRAINT PROCEDURE_TEST_C PRIMARY KEY (C_USER)  
);
--PROCEDURE_TEST_A初始化
INSERT INTO PROCEDURE_TEST_A
  (A_ID, A_USER, A_EMAIL, A_TYPE)
VALUES
  ('AID00006', 'system', '', 'T00');
INSERT INTO PROCEDURE_TEST_A
  (A_ID, A_USER, A_EMAIL, A_TYPE)
VALUES
  ('AID00001', 'popkidorc', '[email protected]', 'T01');
INSERT INTO PROCEDURE_TEST_A
  (A_ID, A_USER, A_EMAIL, A_TYPE)
VALUES
  ('AID00002', 'csdn', '[email protected]', 'T01');
INSERT INTO PROCEDURE_TEST_A
  (A_ID, A_USER, A_EMAIL, A_TYPE)
VALUES
  ('AID00003', 'pop', '', 'T01');
INSERT INTO PROCEDURE_TEST_A
  (A_ID, A_USER, A_EMAIL, A_TYPE)
VALUES
  ('AID00004', 'kid', '[email protected]', 'T01');
INSERT INTO PROCEDURE_TEST_A
  (A_ID, A_USER, A_EMAIL, A_TYPE)
VALUES
  ('AID00005', 'orc', '[email protected]', 'T01');
--PROCEDURE_TEST_A_SUB初始化
INSERT INTO PROCEDURE_TEST_A_SUB
  (ASUB_ID, ASUB_NAME, ASUB_NUMBER, ASUB_COMMENT, A_ID)
VALUES
  ('ASUBID00001', 'oralce_blog', 9.90, 'oralce博客', 'AID00001');
INSERT INTO PROCEDURE_TEST_A_SUB
  (ASUB_ID, ASUB_NAME, ASUB_NUMBER, ASUB_COMMENT, A_ID)
VALUES
  ('ASUBID00002', 'sql_blog', 1.50, 'sql博客', 'AID00001');
INSERT INTO PROCEDURE_TEST_A_SUB
  (ASUB_ID, ASUB_NAME, ASUB_NUMBER, ASUB_COMMENT, A_ID)
VALUES
  ('ASUBID00003', 'swift_blog', 1.00, 'swift博客', 'AID00001');
INSERT INTO PROCEDURE_TEST_A_SUB
  (ASUB_ID, ASUB_NAME, ASUB_NUMBER, ASUB_COMMENT, A_ID)
VALUES
  ('ASUBID00004', 'game_blog', 6.00, 'game博客', 'AID00003');
INSERT INTO PROCEDURE_TEST_A_SUB
  (ASUB_ID, ASUB_NAME, ASUB_NUMBER, ASUB_COMMENT, A_ID)
VALUES
  ('ASUBID00005', 'sport_blog', 5.55, 'sport博客', 'AID00003');
INSERT INTO PROCEDURE_TEST_A_SUB
  (ASUB_ID, ASUB_NAME, ASUB_NUMBER, ASUB_COMMENT, A_ID)
VALUES
  ('ASUBID00006', 'kid_blog', 99.00, 'kid博客', 'AID00004');
INSERT INTO PROCEDURE_TEST_A_SUB
  (ASUB_ID, ASUB_NAME, ASUB_NUMBER, ASUB_COMMENT, A_ID)
VALUES
  ('ASUBID00007', 'zero_blog', 0.00, 'zero博客', 'AID00005');
INSERT INTO PROCEDURE_TEST_A_SUB
  (ASUB_ID, ASUB_NAME, ASUB_NUMBER, ASUB_COMMENT, A_ID)
VALUES
  ('ASUBID00008', 'large_blog', 100000.00, 'large博客', 'AID00005');
--PROCEDURE_TEST_C初始化
INSERT INTO PROCEDURE_TEST_C
  (C_USER, C_NUMBER)
VALUES
  ('popkidorc', 9.90);

執行後，表結構及數據結果如圖：

2.創建存儲過程

代碼如下，注釋非常詳細，直接copy就可慢慢看：

CREATE OR REPLACE PROCEDURE PROCEDURE_TEST(I_A_TYPE       VARCHAR2,
                                           I_DEFAULT_MAIL VARCHAR2) IS
  --聲明變量 start--
  L_DEFAULT_MAIL VARCHAR2(255) := '[email protected]'; --聲明變量，並賦值；若第二個輸入參數不為空，則取該值作為MAIL字段的默認值
  L_TEST_B_COUNT INTEGER; --B表更新後影響記錄數
  L_TEST_C_COUNT INTEGER; --C表更新後影響記錄數
  CURSOR A_CURSOR IS
    SELECT A.A_USER, S.ASUB_NUMBER
      FROM PROCEDURE_TEST_A_SUB S
      LEFT JOIN PROCEDURE_TEST_A A
        ON A.A_ID = S.A_ID
     WHERE A.A_TYPE = I_A_TYPE; --游標對象，用來儲存結果集
  --聲明變量 end--
BEGIN
  --循環顯示A表中數據 start--
  FOR A_C IN A_CURSOR LOOP
    DBMS_OUTPUT.PUT_LINE('===LOOP PROCEDURE_TEST_A===' || A_C.A_USER ||
                         '===' || A_C.ASUB_NUMBER);
  END LOOP; --這裡用的是FOR IN循環，WHILE循環也比較常用，可以去查一下
  --循環顯示A表中數據 end--  

  --查詢A、A_SUB表，並插入B表 start--
  INSERT INTO PROCEDURE_TEST_B
    (B_ID, B_USER, B_EMAIL, B_NAME, B_NUMBER, B_COMMENT)
    SELECT SYS_GUID(),
           A.A_USER,
           DECODE(A.A_EMAIL,
                  NULL,
                  DECODE(I_DEFAULT_MAIL,
                         NULL,
                         L_DEFAULT_MAIL,
                         I_DEFAULT_MAIL),
                  A.A_EMAIL),
           S.ASUB_NAME,
           S.ASUB_NUMBER,
           S.ASUB_COMMENT
      FROM PROCEDURE_TEST_A_SUB S
      LEFT JOIN PROCEDURE_TEST_A A
        ON A.A_ID = S.A_ID
     WHERE A.A_TYPE = I_A_TYPE;

  L_TEST_B_COUNT := SQL%ROWCOUNT;
  DBMS_OUTPUT.PUT_LINE('===INSERT PROCEDURE_TEST_B ROWCOUNT===' ||
                       L_TEST_B_COUNT); --影響的記錄數，SQL%ROWCOUNT
  --查詢A、A_SUB表，並插入B表 end--

  --更新C表 start--
  IF L_TEST_B_COUNT > 0 THEN
    --先判斷若B表有更改才來更新C表
    MERGE INTO PROCEDURE_TEST_C C
    USING (SELECT A.A_USER, SUM(S.ASUB_NUMBER) AS SUM_NUMBER
             FROM PROCEDURE_TEST_A_SUB S
             LEFT JOIN PROCEDURE_TEST_A A
               ON A.A_ID = S.A_ID
            GROUP BY A.A_USER) A
    ON (A.A_USER = C.C_USER)
    WHEN MATCHED THEN
      UPDATE SET C.C_NUMBER = A.SUM_NUMBER
    WHEN NOT MATCHED THEN
      INSERT VALUES (A.A_USER, A.SUM_NUMBER);
    L_TEST_C_COUNT := SQL%ROWCOUNT;
    DBMS_OUTPUT.PUT_LINE('===UPDATE OR INSERT PROCEDURE_TEST_C ROWCOUNT===' ||
                         L_TEST_C_COUNT); --影響的記錄數，SQL%ROWCOUNT
  END IF;
  --更新C表 end--

  --提交事務 start--
  COMMIT; --這裡慎用，最好不要直接在存儲過程中提交，而是使用服務端代碼手動提交。
  --提交事務 end--
  --異常處理 start--
EXCEPTION
  --很多異常我就不一一寫出來了，常見的寫兩個，其他的用OTHER了
  WHEN DUP_VAL_ON_INDEX THEN
    --違反了唯一性限制。
    DBMS_OUTPUT.PUT_LINE('===DUP_VAL_ON_INDEX EXCEPTION===');
    RAISE;
  WHEN NO_DATA_FOUND THEN
    --SELECT時候未找到數據
    DBMS_OUTPUT.PUT_LINE('===NO_DATA_FOUND EXCEPTION===');
    RAISE;
    --終止進程
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('===OTHERS EXCEPTION===');
    RAISE;
    --異常處理 end--
END PROCEDURE_TEST;

3.執行存儲過程

直接執行，代碼如下：

BEGIN
  --執行存儲過程
  PROCEDURE_TEST('T01', '[email protected]');
END;

java通過thin調用，代碼如下(關鍵代碼)：

Class.forName("數據庫驅動包");
Connection conn =  DriverManager.getConnection("連接字符串", "用戶名", "密碼");
CallableStatement proc = null;
proc = conn.prepareCall("{ call PROCEDURE_TEST(?,?) }");
proc.setString(1, "T01");
proc.setString(2, "[email protected]");
proc.execute();

執行結果如下圖，DBMS控制台打印的：