Stored Procedures and Java

Stored Procedures

                                            (MySQL example)

Stored procedure is an already written SQL statement that is saved in the database.

Simple syntax:

CREATE PROCEDURE productpricing()

BEGIN

   SELECT Avg(prod_price) AS priceaverage

   FROM products;

END;

Incase of running from MySQL command line:

-We set the delimiter ‘;’ to something else like //

Syntax:

DELIMITER //

CREATE PROCEDURE productpricing()

BEGIN

   SELECT Avg(prod_price) AS priceaverage

   FROM products;

END //

//again we change the delimiter to default

DELIMITER ;

Working with parameters:

productpricing() is a really simple stored procedure; it simply displays the results of a SELECT statement. Typically stored procedures do not display results; rather, they return them into variables that you specify.

CREATE PROCEDURE productpricing(

   OUT pl DECIMAL(8,2), //p1,ph,pa are variables

   OUT ph DECIMAL(8,2),

   OUT pa DECIMAL(8,2)

)

BEGIN

   SELECT Min(prod_price)

   INTO pl             //putting first result into variable

   FROM products;

   SELECT Max(prod_price)

   INTO ph

   FROM products;

   SELECT Avg(prod_price)

   INTO pa

   FROM products;

END;

Analysis of the above procedure:

MySQL supports parameters of types IN (those passed to stored procedures), OUT (those passed from stored procedures, as we've used here), and INOUT (those used to pass parameters to and from stored procedures).

Calling the above procedure:

CALL productpricing(@pricelow,

                    @pricehigh,

                    @priceaverage);

The parameters passed in the above procedure will hold the results returned by the stored procedure.

Displaying the results stored in procedure:

SELECT @pricehigh, @pricelow, @priceaverage;

An example using the IN and OUT parameters:

CREATE PROCEDURE ordertotal(

   IN onumber INT,

   OUT ototal DECIMAL(8,2)

)

BEGIN

   SELECT Sum(item_price*quantity)

   FROM orderitems

   WHERE order_num = onumber

   INTO ototal;

END;

Analysis:

Here ‘onumber’ will be considered as input parameter to the procedure and ‘ototal’ as the output parameter.

We can see the the WHERE clause uses  onumber to get to specific row and putting the output into the ‘ototal’.

Calling the above procedure:

CALL ordertotal(20005, @total);

Dropping a Stored Procedure:

DROP PROCEDURE IF EXISTS `sp_students_DELETE_byPK`

Building Intelligent Stored Procedures(the read power of stored procedure):

The real power of stored procedures is realized when business rules and intelligent processing are included within them.

Lets consider a senerio,

1. Suppose we need to obtain the total as before.

2. Check whether tax applies for that person(yes/no).

3. Add tax to total if applicable.

4. Return the current total.

Ex:

-- Name: ordertotal

-- Parameters: onumber = order number

--             taxable = 0 if not taxable, 1 if taxable

--             ototal = order total variable

CREATE PROCEDURE ordertotal(

   IN onumber INT,

   IN taxable BOOLEAN,

   OUT ototal DECIMAL(8,2)

) COMMENT 'Obtain order total, optionally adding tax'

BEGIN

   -- Declare variable for total

   DECLARE total DECIMAL(8,2);

   -- Declare tax percentage

   DECLARE taxrate INT DEFAULT 6;

   -- Get the order total

   SELECT Sum(item_price*quantity)

   FROM orderitems

   WHERE order_num = onumber

   INTO total;

   -- Is this taxable?

   IF taxable THEN

      -- Yes, so add taxrate to the total

      SELECT total+(total/100*taxrate) INTO total;

   END IF;

   -- And finally, save to out variable

   SELECT total INTO ototal;

END;

Calling the procedure:

CALL ordertotal(20005, 0, @total);

SELECT @total;

CALL ordertotal(20005, 1, @total);

SELECT @total;

Note: IF also supports ELSEIF and ELSE clauses (the former also uses a THEN clause, the latter does not)

Inspecting Stored Procedures

To display the CREATE statement used to create a stored procedure, use the SHOW CREATE PROCEDURE statement:

• Input

SHOW CREATE PROCEDURE ordertotal;

To obtain a list of stored procedures including details on when and who created them, use SHOW PROCEDURE STATUS.

Note:

Limiting Procedure Status Results SHOW PROCEDURE STATUS lists all stored procedures. To restrict the output you can use LIKE to specify a filter pattern, for example:

SHOW PROCEDURE STATUS LIKE 'ordertotal';

More Example:

CREATE PROCEDURE demoSp(IN inputParam VARCHAR(255), \

                                        INOUT inOutParam INT)

BEGIN

    DECLARE z INT;

    SET z = inOutParam + 1;

    SET inOutParam = z;

    SELECT inputParam;

    SELECT CONCAT('zyxw', inputParam);

END

My Own Procedure:

CREATE PROCEDURE `sunayandb`.`demoSP` (in inParam varchar(20), out outParam varchar(50))

BEGIN

        declare strng varchar(18);

        set strng='thisIsAdded';

        select concat(strng,inParam) into outParam;

END

    Java syntax:

  CallableStatement cStmt = conn.prepareCall("{call demoSp(?, ?)}");

  cStmt.setString(1, "abcdefg");

Using CallableStatement methods to call stored procedures

To call stored procedures, you invoke methods in the CallableStatement class. The basic steps are:

1. Invoke the Connection.prepareCall method to create a CallableStatement object.
2. Invoke the CallableStatement.setXXX methods to pass values to the input (IN) parameters.
3. Invoke the CallableStatement.registerOutParameter method to indicate which parameters are output-only (OUT) parameters, or input and output (INOUT) parameters.
4. Invoke one of the following methods to call the stored procedure:

CallableStatement.executeUpdate

Invoke this method if the stored procedure does not return result sets.

CallableStatement.executeQuery

Invoke this method if the stored procedure returns one result set.

CallableStatement.execute

Invoke this method if the stored procedure returns multiple result sets.

5. If the stored procedure returns result sets, retrieve the result sets. See Retrieve multiple result sets from a stored procedure in a JDBC application.
6. Invoke the CallableStatement.getXXX methods to retrieve values from the OUT parameters or INOUT parameters.
7. Invoke the CallableStatement.close method to close the CallableStatement object when you have finished using that object.

The following code illustrates calling a stored procedure that has one input parameter, four output parameters, and no returned ResultSets. The numbers to the right of selected statements correspond to the previously-described steps.

Figure 33. Using CallableStatement methods for a stored procedure call with parameter markers

int ifcaret;

int ifcareas;

int xsbytes;

String errbuff;

Connection con;

CallableStatement cstmt;

ResultSet rs;

...

cstmt = con.prepareCall("CALL DSN8.DSN8ED2(?,?,?,?,?)");                1 

                                  // Create a CallableStatement object

cstmt.setString (1, "DISPLAY THREAD(*)");                               2 

                                  // Set input parameter (DB2 command) 

cstmt.registerOutParameter (2, Types.INTEGER);                          3 

                                  // Register output parameters

cstmt.registerOutParameter (3, Types.INTEGER);

cstmt.registerOutParameter (4, Types.INTEGER);

cstmt.registerOutParameter (5, Types.VARCHAR);

cstmt.executeUpdate();            // Call the stored procedure          4 

ifcaret = cstmt.getInt(2);        // Get the output parameter values    6 

ifcareas = cstmt.getInt(3);

xsbytes = cstmt.getInt(4);

errbuff = cstmt.getString(5);

cstmt.close();                                                          7  

Related reference

• JDBC differences between the DB2 Universal JDBC Driver and other DB2 JDBC drivers
• Comparison of driver support for JDBC APIs