Oracle技術_5分鐘會用預存程序_預存程序簡單一實例(包含迴圈、條件、增改查、參數傳入、變數賦值、java調用等)，oracle_5

Oracle技術_5分鐘會用預存程序_預存程序簡單一實例(包含迴圈、條件、增改查、參數傳入、變數賦值、java調用等)，oracle_5

例子業務功能：

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_ACREATE 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_SUBCREATE 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', 'popkidorc@mail.com', 'T01');INSERT INTO PROCEDURE_TEST_A  (A_ID, A_USER, A_EMAIL, A_TYPE)VALUES  ('AID00002', 'csdn', 'csdn@mail.com', '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', 'kid@mail.com', 'T01');INSERT INTO PROCEDURE_TEST_A  (A_ID, A_USER, A_EMAIL, A_TYPE)VALUES  ('AID00005', 'orc', 'orc@mail.com', '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) := 'default@mail.com'; --聲明變數，並賦值；若第二個輸入參數不為空白，則取該值作為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', 'test@mail.com');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, "test@mail.com");proc.execute();

執行結果如，DBMS控制台列印的：

兩個被更新的表：

點擊進入ooppookid的部落格