Stored Procedures


The java.sql.CallableStatement interface provides us to access a stored procedure which has several advantages over embedded SQL: The table accounts_tbl can be reset from the previous slide.

1SQL> CREATE TABLE accounts_tbl (
2  2    account_id  INT  PRIMARY KEY,
3  3    balance     REAL NOT NULL );
4Table created.

The following stored procedure sp_interest is defined in the program below:

1CREATE OR REPLACE PROCEDURE  sp_interest (
2  id IN INTEGERold OUT FLOATnew OUT FLOAT ) IS
3BEGIN
4  SELECT  balance  INTO  old  FROM  accounts_tbl
5    WHERE  account_id = id;
6  new := old + old * args[0].trim( );
7  UPDATE  accounts_tbl  SET  balance = new
8    WHERE  account_id = id;
9END;



Pct (args[0]) =

           
01Statement  stmt1 = conn.createStatement( );
02String     cmd   =
03  "CREATE OR REPLACE PROCEDURE  sp_interest (" +
04    "id IN INTEGER,  old OUT FLOAT,  new OUT FLOAT ) IS " +
05  "BEGIN " +
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; " +
11  "END;";
12stmt1.execute( cmd );
13stmt1.close( );
14 
15CallableStatement  stmt2;
16stmt2 = conn.prepareCall  ( "BEGIN  sp_interest( ?, ?, ? );  END;" );
17stmt2.registerOutParameter( 2, java.sql.Types.FLOAT );
18stmt2.registerOutParameter( 3, java.sql.Types.FLOAT );
19for ( int i = 1;  i <= 5;  i++ ) {
20  stmt2.setInt    ( 1, i+1000 );
21  stmt2.execute   ( );
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 ) );
25}
26stmt2.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.




      Your guess is as good as mine.