例子業務功能:
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表中。
裡面包含了存儲過程常用的大部分操作,包含循環、條件、增改查、參數傳入、變量賦值等,話不多說直接上例子:
--數據來源表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);
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;
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();