ORACLE RETURNING INTOの使い方
DML文INSERT,UPDATE,DELETE RETURNING INTOのテスト結果について
1.前提準備
2.SQLの書き込み
(1)DMLの具体的なルールは変わらず、一番後ろにRETURNING COLUMN_を付けるだけNAME INTO:NAME NAMEカスタム.
(2)複数のフィールドを返す必要がある場合、一番後ろにRETURNING COLUMN_を付けるNAME1,COLUMN_NAME2… INTO :NAME1,:NAME2…
3.JAVAコードの作成
4.コードテンプレート
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 {
//
...
}
}