Popular Posts
ListSelectionListener & ItemListener import java.awt.Dimension; import java.awt.FlowLayout; import java.awt.Toolkit; import java.awt.event.ItemEvent; import java.awt.event.ItemL... netbean shortcut Ctrl + F:尋找 F3:尋找下一個字串 Ctrl + G:跳到第 N 行 Ctrl + H:取代 Tab:增加縮排 Shift + Tab:減少縮排 Ctrl + E:刪除一行 Ctrl + Shift + I:修正 import 項目 Alt + Ent... Capture response output stream using HttpModule using System; using System.Collections.Generic; using System.IO; using System.Linq; using System.Text; using System.Web; namespace TestWebA...
Blog Archive
Stats
Java: ResultSet on store procedure
Oracle
CREATE OR REPLACE PROCEDURE get_result (
    start_pattern IN VARCHAR2,
    end_pattern IN VARCHAR2,
    result_set OUT SYS_REFCURSOR
) IS
BEGIN
    OPEN result_set FOR
        SELECT
            sheet_id
        FROM checksheet_with_process
        WHERE
            sheet_id >= start_pattern
            AND sheet_id <= end_pattern
        ORDER BY sheet_id DESC;

END;

-- test 
SET SERVEROUTPUT ON;
DECLARE
result_set SYS_REFCURSOR;
sheet_id VARCHAR2(50);
BEGIN
    DBMS_OUTPUT.ENABLE;
    get_sheet_print_list('20100923','20101023',result_set);
    
    LOOP
        FETCH result_set INTO
            sheet_id
        ;
        EXIT WHEN result_set%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(sheet_id);
    END LOOP;
END;
ProcedureResult.java
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;

public class ProcedureResult {

    /**
     * @param args
     */
    public static void main(String[] args) {
        // TODO Auto-generated method stub
        Connection conn = null;
        CallableStatement stmt = null;
        ResultSet rs = null;

        try {
            conn = DriverManager.getConnection("connection string");
            stmt = conn.prepareCall("CALL get_result(?,?,?)");
            stmt.setString(1, "20101001");
            stmt.setString(2, "20101031");
            stmt.registerOutParameter(3, oracle.jdbc.driver.OracleTypes.CURSOR);
            stmt.execute();
            rs = (ResultSet) stmt.getObject(3);

            while (rs != null && rs.next()) {
                System.out.println(rs.getString("sheet_id"));
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            if (rs != null)
                try {
                    rs.close();
                } catch (SQLException e2) {
                    // TODO Auto-generated catch block
                    e2.printStackTrace();
                }
            if (stmt != null)
                try {
                    stmt.close();
                } catch (SQLException e1) {
                    // TODO Auto-generated catch block
                    e1.printStackTrace();
                }
            if (conn != null)
                try {
                    conn.close();
                } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
        }
    }

}