ORACLE RETURNING INTOの使い方

10676 ワード

DML文INSERT,UPDATE,DELETE RETURNING INTOのテスト結果について
1.前提準備
            PreparedStatement  ,   OraclePreparedStatement           ,          

    wPs1.unwrap(OraclePreparedStatement.class)      OraclePreparedStatement  

2.SQLの書き込み
(1)DMLの具体的なルールは変わらず、一番後ろにRETURNING COLUMN_を付けるだけNAME INTO:NAME NAMEカスタム.
  : INSERT INTO A_TABLE(ID,NAME) VALUES ('1', '2') RETURNING ID INTO :R_ID
      UPDATE A_TABLE SET ID = '2' WHERE NAME = '2' RETURNING ID INTO :R_ID
      DELETE FROM A_TABLE WHERE NAME = '2' RETURNING ID INTO :R_ID

(2)複数のフィールドを返す必要がある場合、一番後ろにRETURNING COLUMN_を付けるNAME1,COLUMN_NAME2… INTO :NAME1,:NAME2…
  : INSERT INTO A_TABLE(ID,NAME) VALUES ('1', '2') RETURNING ID,NAME INTO :R_ID,:R_NAME
     UPDATE A_TABLE SET ID = '2' WHERE NAME = '2' RETURNING ID,NAME INTO :R_ID,:R_NAME
     DELETE FROM A_TABLE WHERE NAME = '2' RETURNING ID,NAME INTO :R_ID,:R_NAME   

3.JAVAコードの作成
 (1) sql  '?'              wPs.setString(1, "123");
 (2)   INTO      '?'           wPs.registerReturnParameter(2, Types.INTEGER);          	      ,              (        ,       )
 (3)        
     resultSet = wPs.getReturnResultSet();
     while (resultSet.next()) {
         System.out.println(resultSet.getString(1)); //           ,      
         System.out.println(resultSet.getString(2));
     }

4.コードテンプレート
public static void main(String[] args){
		String inSQL = "DELETE FROM A_TABLE WHERE ID = ? RETURNING ID,NAME INTO :R_ID,:R_NAME";
		Connection conn = null;
		String Driver = "oracle.jdbc.OracleDriver";
		String URL = "jdbc:oracle:thin:@127.0.0.1:1521:orcl";
		String USER = "scott";
		String Password = "tiger";
		OraclePreparedStatement wPs = null;
		PreparedStatement wPs1 = null;
		ResultSet resultSet = null;
 		try {
				Class.forName(Driver);
				conn =  DriverManager.getConnection(URL, USER, Password);
  			wPs1 = pHdl.prepareSqlStatement(inSQL);
  			wPs = wPs1.unwrap(OraclePreparedStatement.class);
  			wPs.setString(1, "123");
  			wPs.registerReturnParameter(2, Types.CHAR);
  			wPs.registerReturnParameter(3, Types.CHAR);
  			wPs.executeUpdate();
  			resultSet = wPs.getReturnResultSet();
  			while (resultSet.next()) {
  					System.out.println(resultSet.getString(1));
  					System.out.println(resultSet.getString(2));
  			}
 		} catch (SQLException e) {
  				return;
 		} finally {
  				//     
  				...
    }
}