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.
1 | SQL> CREATE TABLE accounts_tbl ( |
2 | 2 account_id INT PRIMARY KEY, |
3 | 3 balance REAL NOT NULL ); |
|
The following stored procedure sp_interest is defined in the program below:
1 | CREATE OR REPLACE PROCEDURE sp_interest ( |
2 | id IN INTEGER, old OUT FLOAT, new OUT FLOAT ) IS |
4 | SELECT balance INTO old FROM accounts_tbl |
6 | new := old + old * args[0].trim( ); |
7 | UPDATE accounts_tbl SET balance = new |
|
|
|
|
|
|
|
|
01 | Statement stmt1 = conn.createStatement( ); |
03 | "CREATE OR REPLACE PROCEDURE sp_interest (" + |
04 | "id IN INTEGER, old OUT FLOAT, new OUT FLOAT ) IS " + |
06 | "SELECT balance INTO old FROM accounts_tbl " + |
07 | "WHERE account_id = id; " + |
08 | "new := old + old * args[0].trim( )" + ";" + |
09 | "UPDATE accounts_tbl SET balance = new " + |
10 | "WHERE account_id = id; " + |
15 | CallableStatement stmt2; |
16 | stmt2 = conn.prepareCall ( "BEGIN sp_interest( ?, ?, ? ); END;" ); |
17 | stmt2.registerOutParameter( 2, java.sql.Types.FLOAT ); |
18 | stmt2.registerOutParameter( 3, java.sql.Types.FLOAT ); |
19 | for ( int i = 1; i <= 5; i++ ) { |
20 | stmt2.setInt ( 1, i+1000 ); |
22 | System.out.print( "account_id: " + Integer.toString( i+1000 ) ); |
23 | System.out.print( "old balance: " + stmt2.getFloat( 2 ) ); |
24 | System.out.print( "new balance: " + stmt2.getFloat( 3 ) ); |
|
If your stored procedure has output parameters, you need to register their types using
registerOutParameter(int parameterIndex, int sqlType) before executing the stored procedure.
|
Your guess is as good as mine.
|