Oracle存儲過程的調用

好久沒有寫部落格了，今天又來寫一篇，工作都好久沒有用到Oracle資料庫了，昨天做了一個Oracle資料庫存儲過程的例子，用JAVA APP去調用，現在把代碼傳上來：

首先：還是先把存儲過程寫好！（關于對一張表的分頁查詢）

--因為現在好多Web App，在JS端口都有封裝好的grid控件，是以這裡，隻傳開始記錄數，和結束記錄就可以了。簡單的練習而已
CREATE OR REPLACE PROCEDURE PROC_PAGINATION(
   START_NUM IN INT --輸入參數：分頁的開始記錄數
  ,END_NUM IN INT --輸入參數：分頁的結束記錄數
  ,TOTAL_COUNT OUT INT --輸出參數：總記錄數
  ,PAGE_RESULT_SET OUT SYS_REFCURSOR --Oracle的遊标查詢結果集
) AS
COUNT_SQL VARCHAR2(200); --定義變量：查詢總記錄數的SQL
V_SQL VARCHAR2(400); --定義變量：分頁查詢結果集的SQL
BEGIN
  COUNT_SQL := 'SELECT COUNT(*) FROM MYMIS.TB_MYMIS_RESOURCES_MENU';
  EXECUTE IMMEDIATE COUNT_SQL INTO TOTAL_COUNT; --把查詢的記錄數結果，給輸出參數
  --V_SQL := 'SELECT * FROM (SELECT TEMP.*,ROWNUM AS RN FROM (SELECT * FROM MYMIS.TB_MYMIS_RESOURCES_MENU ORDER BY RESOURCE_ID ASC) TEMP WHERE ROWNUM <= ' || END_NUM || ') WHERE RN >  ' || START_NUM;  
  V_SQL := 'SELECT * FROM (SELECT ROWNUM AS RN,A.* FROM MYMIS.TB_MYMIS_RESOURCES_MENU A WHERE ROWNUM <= ' || END_NUM || ' ORDER BY A.RESOURCE_ID ASC) WHERE RN > ' || START_NUM; 
 OPEN PAGE_RESULT_SET FOR V_SQL; --把遊标查詢的結果集給輸出參數END PROC_PAGINATION;
END PROC_PAGINATION;
           

存儲過程寫好了，下面是普通JDBC類的調用：(這裡就寫了一個main方法，做測試用)

public static void main(String[] args) {
//		Connection con = ConnectionFactory.getConnection();
//		if (con != null) {
//			System.out.println("連接配接正常...");
//		} else {
//			System.out.println("連接配接出錯...");
//		}
		Connection conn = ConnectionFactory.getConnection(); //擷取資料庫連接配接
	    CallableStatement cs; //定義JDBC調用存儲過程的類
		try {
			cs = conn.prepareCall("call MYMIS.PROC_PAGINATION(?,?,?,?)"); //調用存儲過程
			cs.setInt(1, 4);  //比對輸入，輸出參數
		    cs.setInt(2, 8);
		    cs.registerOutParameter(3, Types.INTEGER);
		    cs.registerOutParameter(4, oracle.jdbc.OracleTypes.CURSOR);
		    cs.execute(); //執行存儲過程
		    int totalPage = cs.getInt(3); //将輸出參數給一個變量
		    System.out.println("共有" + totalPage + "頁");
		    ResultSet rs = (ResultSet) cs.getObject(4); //将輸出結果集，給一個ResultSet
		    while (rs.next()) {
		        System.out.println(rs.getString("RESOURCE_ID")+","+rs.getString("RESOURCE_NAME") +"," + rs.getString("RESOURCE_TYPE"));
		    }
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
           

下面做了一個JdbcTemplate的調用，個人覺得資料庫持久層，spring的JdbcTemplate是個好東西，查詢效率也蠻快的，而且查詢方法也很多。同樣寫在main方法裡

public static void main(String[] args) {
    ClassPathXmlApplicationContext appctx = new ClassPathXmlApplicationContext("applicationContext.xml"); //讀取spring的全局配置檔案
    JdbcTemplate jdbcTemplate = (JdbcTemplate) appctx.getBean("jdbcTemplate"); //通過BeanId，從Bean工廠裡擷取到定義的jdbcTemplate對象類，如果不這樣new 出來JdbcTemplate為null
//    Object[] params = new Object[] { "111", "test", "99", "測試地市", "888",
//        "測試部門", "888888", "2" };
    jdbcTemplate.execute("call MYMIS.PROC_PAGINATION(?,?,?,?)");
    //jdbcTemplate.execute這樣去調用存儲過程，jdbcTemplate.execute(CallableStatementCreator arg0, CallableStatementCallback arg1) 兩個參數，一個是建立調用存儲過程對象，另一個是傳回參數
     List resultList = (List)jdbcTemplate.execute(
      new CallableStatementCreator(){
        public CallableStatement createCallableStatement(
            Connection con) throws SQLException {
          String storedProc = "{call MYMIS.PROC_PAGINATION(?,?,?,?)}";//調用存儲過程
          CallableStatement cs = con.prepareCall(storedProc);
          cs.setInt(1, 4); //比對輸入輸出參數
            cs.setInt(2, 8);
            cs.registerOutParameter(3, Types.INTEGER);
            cs.registerOutParameter(4, oracle.jdbc.OracleTypes.CURSOR);
          return cs; //傳回execute()方法的第一個參數，建立CallableStatement的對象
        }
      },new CallableStatementCallback() {
        public Object doInCallableStatement(CallableStatement cs)
        throws SQLException, DataAccessException {
          List resultsMap = new ArrayList();
          cs.execute();
          int count = cs.getInt(3); //輸出參數1
          System.out.println(count);
          ResultSet rs = (ResultSet) cs.getObject(4); //輸入出結果集
          while (rs.next()) {// 轉換每行的傳回值到Map中
            Map rowMap = new HashMap();
            //rowMap.put("total", count);
            rowMap.put("rs", rs);
            System.out.println(rs.getString("RESOURCE_NAME"));
            resultsMap.add(rowMap);//傳回execute()方法的第二個參數，CallableStatementCallback的對象
          }
          rs.close();
          return resultsMap; //将整個調用存儲過程得到的參數傳回。
        }
      }
    );
  }
           

大緻就是這樣的，代碼都經過本機測試過的！！感覺很多資料庫的存儲過程寫法有些差異，不過都大同小異。