Stored Procedures
The java.sql.CallableStatement interface provides us to access a stored procedure which has several advantages over embedded SQL:
- Because the procedure is precompiled in the database, it executes much faster than dynamic SQL.
- Syntax errors in the stored procedure can be caught at compile time rather than at run time.
- Java developers need to know only the name of the procedure and its inputs and outputs.
The table accounts_tbl can be reset from the previous slide.
SQL> CREATE TABLE accounts_tbl (
2 account_id INT PRIMARY KEY,
3 balance REAL NOT NULL );
Table created.
|
The following stored procedure sp_interest is defined in the program below:
CREATE OR REPLACE PROCEDURE sp_interest (
id IN INTEGER, old OUT FLOAT, new OUT FLOAT ) IS
BEGIN
SELECT balance INTO old FROM accounts_tbl
WHERE account_id = id;
new := old + old * args[0].trim( );
UPDATE accounts_tbl SET balance = new
WHERE account_id = id;
END;
|
|
|
|
|
|
|
|
Statement stmt1 = conn.createStatement( );
String cmd =
"CREATE OR REPLACE PROCEDURE sp_interest (" +
"id IN INTEGER, old OUT FLOAT, new OUT FLOAT ) IS " +
"BEGIN " +
"SELECT balance INTO old FROM accounts_tbl " +
"WHERE account_id = id; " +
"new := old + old * args[0].trim( )" + ";" +
"UPDATE accounts_tbl SET balance = new " +
"WHERE account_id = id; " +
"END;";
stmt1.execute( cmd );
stmt1.close( );
CallableStatement stmt2;
stmt2 = conn.prepareCall ( "BEGIN sp_interest( ?, ?, ? ); END;" );
stmt2.registerOutParameter( 2, java.sql.Types.FLOAT );
stmt2.registerOutParameter( 3, java.sql.Types.FLOAT );
for ( int i = 1; i <= 5; i++ ) {
stmt2.setInt ( 1, i+1000 );
stmt2.execute ( );
System.out.print( "account_id: " + Integer.toString( i+1000 ) );
System.out.print( "old balance: " + stmt2.getFloat( 2 ) );
System.out.print( "new balance: " + stmt2.getFloat( 3 ) );
}
stmt2.close( );
|
If your stored procedure has output parameters, you need to register their types using
registerOutParameter(int parameterIndex, int sqlType) before executing the stored procedure.
