Double aNumber = null;
long start = 0;
long end = 0;
String aString = "1234567.890";
CallableStatement cstmt = null;
try {
start = System.currentTimeMillis();
// *** SQL92 escape syntax ***
// Create the callable statement
cstmt = conn.prepareCall(
"{ ? = call scott.tonumberfun( ? ) }");
// Register the out parameter
cstmt.registerOutParameter(1, Types.DOUBLE, 2);
// Set the in parameter
cstmt.setString(2, aString);
// Execute the stored procedure
// Using executeUpdate() it returns a value of 1
int rows = cstmt.executeUpdate();
System.out.println("rows = " + rows);
// Get the returned value
aNumber = new Double(cstmt.getDouble(1));
// Check to see if the returned value was NULL
if (cstmt.wasNull()) aNumber = null;
// Display the returned value
System.out.println(aString + " converted to a number = " + aNumber);
cstmt = conn.prepareCall(
"{ call scott.tonumberprc( ?, ? ) }");
cstmt.registerOutParameter(2, Types.DOUBLE);
cstmt.setString(1, aString);
cstmt.execute();
aNumber = new Double(cstmt.getDouble(2));
if (cstmt.wasNull()) aNumber = null;
System.out.println(aString + " converted to a number = " + aNumber);
cstmt = conn.prepareCall(
"{ ? = call scott.chapter_13.tonumber( ? ) }");
cstmt.registerOutParameter(1, Types.DOUBLE);
cstmt.setString(2, aString);
cstmt.execute();
aNumber = new Double(cstmt.getDouble(1));
if (cstmt.wasNull()) aNumber = null;
System.out.println(aString + " converted to a number = " + aNumber);
cstmt = conn.prepareCall(
"{ call scott.chapter_13.tonumber( ?, ? ) }");
cstmt.registerOutParameter(2, Types.DOUBLE);
cstmt.setString(1, aString);
cstmt.execute();
aNumber = new Double(cstmt.getDouble(2));
if (cstmt.wasNull()) aNumber = null;
System.out.println(aString + " converted to a number = " + aNumber);
// *** Oracle PL/SQL syntax ***
cstmt = conn.prepareCall(
"begin ? := scott.tonumberfun( ? ); end;");
cstmt.registerOutParameter(1, Types.DOUBLE);
cstmt.setString(2, aString);
cstmt.execute();
aNumber = new Double(cstmt.getDouble(1));
if (cstmt.wasNull()) aNumber = null;
System.out.println(aString + " converted to a number = " + aNumber);
cstmt = conn.prepareCall(
"begin scott.tonumberprc( ?, ? ); end;");
cstmt.registerOutParameter(2, Types.DOUBLE);
cstmt.setString(1, aString);
cstmt.execute();
aNumber = new Double(cstmt.getDouble(2));
if (cstmt.wasNull()) aNumber = null;
System.out.println(aString + " converted to a number = " + aNumber);
cstmt = conn.prepareCall(
"begin ? := scott.chapter_13.tonumber( ? ); end;");
cstmt.registerOutParameter(1, Types.DOUBLE);
cstmt.setString(2, aString);
cstmt.execute();
aNumber = new Double(cstmt.getDouble(1));
if (cstmt.wasNull()) aNumber = null;
System.out.println(aString + " converted to a number = " + aNumber);
cstmt = conn.prepareCall(
"begin scott.chapter_13.tonumber( ?, ? ); end;");
cstmt.registerOutParameter(2, Types.DOUBLE);
cstmt.setString(1, aString);
cstmt.execute();
aNumber = new Double(cstmt.getDouble(2));
if (cstmt.wasNull()) aNumber = null;
System.out.println(aString + " converted to a number = " + aNumber);
end = System.currentTimeMillis();
System.out.println("Average elapsed time = " +
(end - start)/8 + " milliseconds");
}
catch (SQLException e) {
System.err.println("SQL Error: " + e.getMessage());
}
finally {
if (cstmt != null)
try { cstmt.close(); } catch (SQLException ignore) { }
}
