Home

	Expand Search

Chapter 2: The Embedded SQL Interface

Using stored procedures in Embedded SQL

Simple procedures

Simple procedures

Database procedures can be both created and called from Embedded SQL. A CREATE PROCEDURE statement can be embedded just like any other DDL statement. A CALL statement can also be embedded, or it can be prepared and executed. Here is a simple example of both creating and executing a stored procedure in Embedded SQL:

EXEC SQL CREATE PROCEDURE pettycash( IN amount DECIMAL(10,2) )
BEGIN


 UPDATE account
 SET balance = balance - amount
 WHERE name = 'bank';


 UPDATE account
 SET balance = balance + amount
 WHERE name = 'pettycash expense';


END;
EXEC SQL CALL pettycash( 10.72 );

If you wish to pass host variable values to a stored procedure, or retrieve the output variables, you prepare and execute a CALL statement. The example illustrates the use of host variables. Both the USING and INTO clauses are used on the EXECUTE statement.

EXEC SQL BEGIN DECLARE SECTION;
 double hv_expense;
 double hv_balance;
EXEC SQL END DECLARE SECTION;


EXEC SQL CREATE PROCEDURE pettycash( 
   IN expense  DECIMAL(10,2), 
   OUT endbalance DECIMAL(10,2) )
 BEGIN
  UPDATE account
  SET balance = balance - expense
  WHERE name = 'bank';


  UPDATE account
  SET balance = balance + expense
  WHERE name = 'pettycash expense';


  SET endbalance = ( SELECT balance FROM account
         WHERE name = 'bank' );
 END;


EXEC SQL PREPARE S1 FROM 'CALL pettycash( ?, ? )';


EXEC SQL EXECUTE S1 USING :hv_expense INTO :hv_balance;