What is a Stored Procedure?

What is a Stored Procedure?

A stored procedure is a group of SQL statements that form a logical unit and perform a particular task, and they are used to encapsulate a set of operations or queries to execute on a database server. For example, operations on an employee database (hire, fire, promote, lookup) could be coded as stored procedures executed by application code. Stored procedures can be compiled and executed with different parameters and results, and they may have any combination of input, output, and input/output parameters.

Note: Stored procedures are supported by most DBMSs, but there is a fair amount of variation in their syntax and capabilities. For this reason, this simple example of what a stored procedure looks like and how it is invoked from JDBC is not intended to be run.

This simple stored procedure has no parameters. Even though most stored procedures do something more complex than this example, it serves to illustrate some basic points about them. As previously stated, the syntax for defining a stored procedure is different for each DBMS. For example, some use begin . . . end , or other keywords to indicate the beginning and ending of the procedure definition. In some DBMSs, the following SQL statement creates a stored procedure:

create procedure SHOW_SUPPLIERS

as

select SUPPLIERS.SUP_NAME, COFFEES.COF_NAME

from SUPPLIERS, COFFEES

where SUPPLIERS.SUP_ID = COFFEES.SUP_ID

order by SUP_NAME

The following code puts the SQL statement into a string and assigns it to the variable createProcedure, which we will use later:

String createProcedure = "create procedure SHOW_SUPPLIERS " +

                           "as " +

                           "select SUPPLIERS.SUP_NAME, COFFEES.COF_NAME " +

                           "from SUPPLIERS, COFFEES " +

                           "where SUPPLIERS.SUP_ID = COFFEES.SUP_ID " +

                           "order by SUP_NAME";

The following code fragment uses the Connection object con to create a Statement object, which is used to send the SQL statement creating the stored procedure to the database:

Statement stmt = con.createStatement();

stmt.executeUpdate(createProcedure);

The procedure SHOW_SUPPLIERS is compiled and stored in the database as a database object that can be called, similar to the way you would call a method.

Calling a Stored Procedure from JDBC

JDBC allows you to call a database stored procedure from an application written in the Java programming language. The first step is to create a CallableStatement object. As with Statement and PreparedStatement objects, this is done with an open Connection object. A callableStatement object contains a call to a stored procedure; it does not contain the stored procedure itself. The first line of code below creates a call to the stored procedure SHOW_SUPPLIERS using the connection con. The part that is enclosed in curly braces is the escape syntax for stored procedures. When the driver encounters "{call SHOW_SUPPLIERS}", it will translate this escape syntax into the native SQL used by the database to call the stored procedure named SHOW_SUPPLIERS.

CallableStatement cs = con.prepareCall("{call SHOW_SUPPLIERS}");

ResultSet rs = cs.executeQuery();

Note that the method used to execute cs is executeQuery because cs calls a stored procedure that contains one query and thus produces one result set. If the procedure had contained one update or one DDL statement, the method executeUpdate would have been the one to use. It is sometimes the case, however, that a stored procedure contains more than one SQL statement, in which case it will produce more than one result set, more than one update count, or some combination of result sets and update counts. In this case, where there are multiple results, the method execute should be used to execute the CallableStatement .

The class CallableStatement is a subclass of PreparedStatement, so a CallableStatement object can take input parameters just as a PreparedStatement object can. In addition, a CallableStatement object can take output parameters, or parameters that are for both input and output. INOUT parameters and the method execute are used rarely.

Stored procedure and Cursers:

If you are used to Sybase or SQLServer, returning a result set from a stored procedure is easy. Just finish the procedure with a "select x,y,z from my_table", selecting whatever columns you wish to return.

Not so for Oracle. You need to use cursors.

What is a REF CURSOR?

Cursors, as you know, help return recordsets/ resultsets. A cursor is a work area for a SQL statement that returns one or more rows. It allows you to fetch rows from the result set one by one. Cursors aren't particularly difficult to use, but to return a result set from a PL/SQL stored procedure, you must use a cursor variable. Cursor variables are basically pointers to cursors, and you use them to pass around references to cursors, such as a parameter to a stored procedure. The PL/SQL type of a cursor variable is REF CURSOR.

To use cursor variables, you must define a REF CURSOR type. Treated just like a data type, your stored procedure takes REF CURSORS as OUT parameters, and you can return a full recordset in each REF CURSOR parameter back to the caller. So you can include as many REF CURSOR parameters as you want - your stored procedure will have the ability to return that many recordsets.

Cursor type can be strong or weak. Strong types are tied to a specific set of a table's columns. This means a strong REF CURSOR type can only be used with queries (i.e. cursors) returning those columns. Weak types can refer to any cursor, and so are much more flexible. They do, however, sacrifice type safety.

The easiest way forward is to define a weakly typed REF CURSOR in a PL/SQL package. Packages are used in PL/SQL for partitioning functionality. Below, a weak REF CURSOR type called REF_CURSOR is defined in a PL/SQL package called types.

CREATE OR REPLACE PACKAGE types
AS
TYPE ref_cursor IS REF CURSOR;
END;

This definition can now be used in all stored procedures to declare a variable of type REF CURSOR.

We can now write a stored procedure that returns a REF CURSOR, that in JDBC we can process as a result set.

Examples on Stored Procedure

Example 1:

Assume we start from a table defined as below.

CREATE TABLE STOCK_PRICES(
RIC VARCHAR(6) PRIMARY KEY,
PRICE NUMBER(7,2),
UPDATED DATE )

Here we have a table of stock prices, with the RIC (Reuters Instrument Code) as the primary key. We define a PL/SQL function that simply declares a cursor that returns all columns for stocks below a certain price.

CREATE OR REPLACE FUNCTION sp_get_stocks(v_price IN NUMBER)
RETURN types.ref_cursor
AS
stock_cursor types.ref_cursor;
BEGIN
OPEN stock_cursor FOR
SELECT ric,price,updated FROM stock_prices
WHERE price < v_price;

RETURN stock_cursor;
END;

Example 2:

This stored procedure takes an input parameter for lookup, and has two OUT REF CURSORS. For simplicity of this example, both the REF CURSORS return a column (a single column). That is not a requirement in real life of course.

CREATE OR REPLACE

PROCEDURE GetEmpRS1 (p_recordset1 OUT SYS_REFCURSOR, 

              p_recordset2 OUT SYS_REFCURSOR,

              PARAM IN STRING) AS

BEGIN

  OPEN p_recordset1 FOR

  SELECT RET1 

    FROM MYTABLE

    WHERE LOOKUPVALUE > PARAM;

  OPEN p_recordset2 FOR

  SELECT RET2

   FROM MYTABLE

   WHERE LOOKUPVALUE >= PARAM;

END GetEmpRS1;

Calling the stored procedure from Java

There are some Oracle-specific tricks to calling the above stored procedure from Java.

The query string can be in the Oracle format or the standard JDBC callable statement syntax. If you are using the Oracle syntax, the query string is:

begin ? := sp_get_stocks(?); end;

If you are using the JDBC syntax, the query string is:

{ call ? := sp_get_stocks(?) }

In each case the spacing is important - the query will not work otherwise.

Secondly, the out parameter must be registered to be of type OracleTypes.CURSOR.

And finally, use getObject() to retrieve the result set.

The code (without error handling) is illustrated below.

Example 1:

String query = "begin ? := sp_get_stocks(?); end;";

CallableStatement stmt = conn.prepareCall(query);

// register the type of the out param - an Oracle specific type
stmt.registerOutParameter(1, OracleTypes.CURSOR);

// set the in param
stmt.setFloat(2, price);

// execute and retrieve the result set
stmt.execute();
ResultSet rs = (ResultSet)stmt.getObject(1);

// print the results
while (rs.next()) {
System.out.println(rs.getString(1) + "\t" +
rs.getFloat(2) + "\t" +
rs.getDate(3).toString());
}

Example 2:

create or replace function sp_ListEmp return types.cursortype
as
l_cursor types.cursorType;
begin
open l_cursor for select ename, empno from emp order by ename;
return l_cursor;
end;

Or like this for a procedure:

create or replace procedure getemps( p_cursor in out types.cursorType )

as

begin

open p_cursor for select ename, empno from emp order by ename;

end;

Here is an example:

create or replace package types
as
type cursorType is ref cursor;
end;
/

create or replace function sp_ListEmp return types.cursortype
as
l_cursor types.cursorType;
begin
open l_cursor for select ename, empno from emp order by ename;

return l_cursor;
end;
/
create or replace procedure getemps( p_cursor in out types.cursorType )

as

begin

open p_cursor for select ename, empno from emp order by ename;

end;

/

And the java to use this could be:

import java.sql.*;
import java.io.*;
import oracle.jdbc.driver.*;

class curvar
{
public static void main (String args [])
throws SQLException, ClassNotFoundException
{
String driver_class = "oracle.jdbc.driver.OracleDriver";
String connect_string = "jdbc:oracle:thin:@slackdog:1521:oracle8";

String query = "begin :1 := sp_listEmp; end;";
Connection conn;

Class.forName(driver_class);
conn = DriverManager.getConnection(connect_string, "scott", "tiger");

CallableStatement cstmt = conn.prepareCall(query);
cstmt.registerOutParameter(1,OracleTypes.CURSOR);
cstmt.execute();
ResultSet rset = (ResultSet)cstmt.getObject(1);

while (rset.next ())
System.out.println( rset.getString (1) );
cstmt.close();
}
}