子程序四(自治事務、Java呼叫子程序) (PL/SQL 十四)

※自治事務

DECLARE
    PROCEDURE XXX IS
    -- PRAGMA AUTONOMOUS_TRANSACTION;
    BEGIN
        INSERT INTO DEPT VALUES(60, 'c', 'd');
        COMMIT;
    END;
BEGIN
    INSERT INTO DEPT VALUES(50, 'a', 'b');
    XXX();
    ROLLBACK;
END;

※註解那一行沒打開時，50和60都會新增成功，因為COMMIT後，ROLLBACK沒用了

※註解打開後，60會新增成功，因為它偵測到自治事務後，將60commit，而外層的50被rollback了

※Java呼叫Procedure

CREATE OR REPLACE
    PROCEDURE xxx(p1 NUMBER, p2 IN OUT NUMBER, p3 OUT NUMBER) IS
BEGIN
    p2 := 5;
    p3 := 10;
END;

※回傳5和10給java接收

※測試類

public class JavaCallProcedure {
    private static final String DRIVER = "oracle.jdbc.driver.OracleDriver";
    private static final String URL = "jdbc:oracle:thin:@127.0.0.1:1521:orcl";
    private static final String USERNAME = "username";
    private static final String PASSWORD = "password";
    
    public static void main(String[] args) throws Exception {
        Class.forName(DRIVER);
        Connection conn = DriverManager.getConnection(URL, USERNAME, PASSWORD);
        CallableStatement cstmt = conn.prepareCall("{ CALL xxx(?, ?, ?)}");
        cstmt.setInt(1, 50);
        cstmt.setInt(2, 100);
        cstmt.registerOutParameter(2, Types.INTEGER);
        cstmt.registerOutParameter(3, Types.INTEGER);
        cstmt.execute();
    
        System.out.println("第二個參數返回值=" + cstmt.getInt(2));
        System.out.println("第三個參數返回值=" + cstmt.getInt(3));
        cstmt.close();
        conn.close();
    }
}

※只能用CALL，我用EXEC會出「java.sql.SQLException: 未支援 SQL92 符號的位置: 3」的錯

※花括號我不加也可以