Cursors in MySQL Stored Procedures

Cursors in MySQL Stored Procedures,cursorsstored

 

After my previous article on Stored Procedures was published on
SitePoint, I received quite a number of comments. One of them suggested
further elaboration on CURSOR, an important feature in Stored
Procedures.

As cursors are a part of a Stored Procedure, we will elaborate a bit
more on SP in this article as well. In particular, we will see how to
return a dataset from an SP.  

 

 

What is a CURSOR?

A cursor can’t be used by itself in MySQL. It is an essential component
in stored procedures. I would be inclined to treat a cursor as a
“pointer” in C/C++, or an iterator in PHP’s foreach statement.

With cursors, we can traverse a dataset and manipulate each record to
accomplish certain tasks. When such an operation on a record can also be
done in the PHP layer, it saves data transfer amounts as we can just
return the processed aggregation/statistical result back to the PHP
layer (thus eliminating the selectforeach – manipulation process
at the client side).

Since a cursor is implemented in a stored procedure, it has all the
benefits (and limitations) of an SP (access control, pre-compiled, hard
to debug, etc).

The official documentation on cursors is located here. It contains only
four commands that are related to cursor declaration, opening, closing,
and fetching. As mentioned above, we will also touch on some other
stored procedure statements. Let’s get started. 

 Enhancing the Application: Advanced JDBC Features

After my previous article on Stored
Procedures was
published on SitePoint, I received quite a number of comments. One of
them suggested further elaboration on CURSOR, an important feature in
Stored Procedures.

A real world question

My personal website has a page showing the scores of my favorite NBA
team: LA Lakers. The table structure behind it is straightforward:

997755.com澳门葡京 1

Fig 1. The Lakers matches status table structure

I have been updating this table since 2008. Some of the latest records
showing Lakers’ 2013-14 season are shown below:

997755.com澳门葡京 2

Fig 2. The Lakers matches status table data (partial) for 2013-2014
season

(I am using MySQL Workbench as the GUI tool to manage my MySQL
databases. You can use your favorite tool.)

Well, I have to admit Lakers are not playing very well these days. 6
consecutive losses up to Jan 15th. I get this “6 consecutive losses” by
manually counting from the last played match all the way up (towards
earlier games) and see how long an “L” (meaning a loss) in winlose
column can appear.  This is certainly doable but if the requirement
becomes more complicated in a larger table, it takes more time and is
more error prone.

Can we do this with a single SQL statement? I am not an SQL expert and I
haven’t been able to figure out how to achieve the desired result (“6
consecutive losses”) from one SQL statement. The input of gurus will be
highly appreciated – leave it in the comments below.

Can we do this in PHP? Yes, of course. We can retrieve the game data
(particularly, the winlose column) for current season and do a
traverse on the records to calculate the current longest win/lose
streak. But to do that, we will have to grab all data for that year and
most of the data will be wasted (as it is not likely for a team to have
a win/lose streak for more than 20+ games in a 82-game regular season).
However, we don’t know how many records should be retrieved into PHP to
determine the streak, so this waste is a must. And finally, if the
current win/lose streak is the only thing we want to know from that
table, why pull all the raw data?

Can we do this via other means? Yes, it is possible. For example, we can
create a redundant table specifically designed to store the current
win/lose streak. Every insertion of the record will update that table
too. But this is way too cumbersome and too error prone.

So, what is a better way to achieve this result?

This chapter describes additional functionality that you can use in your
Java application. Some of these features have not been implemented in
the sample application, while some features are enhancements you can use
in your code to improve performance.

As cursors are a part of a Stored Procedure, we will elaborate a bit
more on SP in this article as well. In particular, we will see how to
return a dataset from an SP.  

Using Cursor in a Stored Procedure

As the name of this article suggests, we will see a better alternative
(in my view) to solve this problem: using cursor in a Stored Procedure.

Let’s create the first SP in MySQL Workbench as follows:

DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `streak`(in cur_year int, out longeststreak int, out status char(1))
BEGIN
    declare current_win char(1);
    declare current_streak int;
    declare current_status char (1);

    declare cur cursor for select winlose from lakers where year=cur_year and winlose<>'' order by id desc;

    set current_streak=0;

    open cur;

    fetch cur into current_win;
    set current_streak = current_streak +1;

    start_loop: loop
        fetch cur into current_status;
            if current_status <> current_win then 
                leave start_loop;
            else
                set current_streak=current_streak+1;
            end if;

    end loop;

    close cur;

    select current_streak into longeststreak;
    select current_win into `status`;
END

In this SP, we have one input parameter and two output parameters. This
defines the signature of the SP.

In the SP body, we also declared a few local variables to hold the
streak status (win or lose, current_win), current streak and current
win/lose status for a particular match.

declare cur cursor for select winlose from lakers where year=cur_year and winlose<>'' order by id desc;

The above line is the cursor declaration. We declared a cursor named
cur and the dataset bind to that cursor is the win/lose status for
those matches played (thus its winlose column is either “W” or “L”
instead of nothing) in a particular year ordered by id (the latest
played games will have the highest ID) descending.

Though not displayed explicitly, we can imagine that this dataset will
contain a series of “L”s and “W”s. Based on the data shown in Figure 2
above, it should be: “LLLLLLWLL…” (6 Ls, 1 Ws, etc).

To calculate the win/lose streak, we begin with the latest (and the
first in the dataset) match data. When a cursor is opened, it always
starts at the first record in the associated dataset.

After the first data is grabbed, the cursor will move to the next
record. In this way, a cursor behaves very much like a queue, traversing
the dataset in a FIFO (First In First Out) manner. This is exactly what
we wanted. 

After getting the current win/lose status and set the streak number, we
continue to loop through (traverse) the remainder of the dataset. With
each loop iteration, the cursor will “point” to the next record until we
break the loop or all the records are consumed. 

If the next win/lose status is the same as the current win/lose status,
it means the streak goes on and we increase the streak number by 1 and
continue the traversing; otherwise, it means the streak discontinues and
we can leave the loop earlier.

Finally, we close the cursor and release the resources. Then we return
the desired output.

Next, we can enhance the access control of the SP as described in my
previous article.

To test the output of this SP, we will write a short PHP script:

<?php
$dbms = 'mysql';

$host = 'localhost';
$db = 'sitepoint';
$user = 'root';
$pass = 'your_pass_here';
$dsn = "$dbms:host=$host;dbname=$db";

$cn=new PDO($dsn, $user, $pass);

$cn->exec('call streak(2013, @longeststreak, @status)');
$res=$cn->query('select @longeststreak, @status')->fetchAll();

var_dump($res); //Dump the output here to get a raw view of the output

$win=$res[0]['@status']='L'?'Loss':'Win';
$streak=$res[0]['@longeststreak'];

echo "Lakers is now $streak consecutive $win.\n";

This will output something like the following figure:

997755.com澳门葡京 3

(This output is based on Lakers’ match up to Jan 15th, 2014.)

This chapter includes the following sections:

 

997755.com澳门葡京,Return a dataset from a Stored Procedure

A few discussions went along on how to return a dataset from an SP,
which constructs the dataset out of the results from a few repeated
calls to another SP.

A user may want to know more from our previously created SP that only
returns a win/lose streak for one year; thus we can get a table showing
the win/lose streaks for all the years in a form like:

YEAR Win/Lose Streak
2013 L 6
2012 L 4
2011 L 2

(Well, a more useful result can be to return the longest win streak and
loss streak in a particular season. This requirement can be easily
expanded from the previous SP so I will leave it to interested parties
to implement. For the purpose of this article, we will stick to the
current win/loss streak.)

MySQL SP can only return scalar results (an integer, a string, etc),
unless the result is returned by a select ... from ... statement (and
it becomes a dataset). The issue here is that the table-form data we
want to see does not exist in our current db structure and is
constructed from another SP.

To tackle this, we need the help of a temporary table, or if situation
allows and requires, a redundant table. Let’s see how we can achieve our
target via a temporary table.

First, we will create a second SP as shown below:

DELIMITER $$

CREATE DEFINER=`root`@`%` PROCEDURE `yearly_streak`()
begin
    declare cur_year, max_year, min_year int;

    select max(year), min(year) from lakers into max_year, min_year;

    DROP TEMPORARY TABLE IF EXISTS yearly_streak;
    CREATE TEMPORARY TABLE yearly_streak (season int, streak int, win char(1));

    set cur_year=max_year;

    year_loop: loop
        if cur_year<min_year then
            leave year_loop;
        end if;

        call streak(cur_year, @l, @s);
        insert into yearly_streak values (cur_year, @l, @s);

        set cur_year=cur_year-1;
    end loop;

    select * from yearly_streak;
    DROP TEMPORARY TABLE IF EXISTS yearly_streak;

END

A few key things to notice here:

To get the results, we create another short PHP script as shown below:

<?php
... // Here goes the db connection parameters

$cn=new PDO($dsn, $user, $pass);

$res=$cn->query('call yearly_streak')->fetchAll();

foreach ($res as $r)
{
    echo sprintf("In year %d, the longest W/L streaks is %d %s\n", $r['season'], $r['streak'], $r['win']);
}

And the display will be like:

997755.com澳门葡京 4

Please note that the above is a bit different from calling our first SP.

The first SP does not return a dataset but only two parameters. In that
case, we use PDO exec then query to fetch the output; while in the
second SP, we returned a dataset from the SP, so we use PDO query
directly to invoke the call to the SP.

Voila! We did it!

  • Using Dynamic SQL

  • Calling Stored Procedures

  • Using Cursor Variables

What is a CURSOR?

A cursor can’t be used by itself in MySQL. It is an essential component
in stored procedures. I would be inclined to treat a cursor as a
“pointer” in C/C++, or an iterator in PHP’s foreach statement.

With cursors, we can traverse a dataset and manipulate each record to
accomplish certain tasks. When such an operation on a record can also be
done in the PHP layer, it saves data transfer amounts as we can just
return the processed aggregation/statistical result back to the PHP
layer (thus eliminating the selectforeach – manipulation process
at the client side).

Since a cursor is implemented in a stored procedure, it has all the
benefits (and limitations) of an SP (access control, pre-compiled, hard
to debug, etc).

The official documentation on cursors is located
here. It contains
only four commands that are related to cursor declaration, opening,
closing, and fetching. As mentioned above, we will also touch on some
other stored procedure statements. Let’s get started. 

 

Conclusion

In this article, we dug further into MySQL stored procedures and took a
look at the cursor functionality. We have demonstrated how to fetch
scalar data by output parameters (defined as out var_name vartype in
the SP declaration) and also fetch a calculated dataset via a temp
table. During this process, a few statements otherwise used in stored
procedures also surfaced.

The official documentation on the syntax of stored procedure and various
statements can be found on the MySQL website. To create a stored
procedure, please refer to these documents and to understand the
statements, please see here.

Feel free to comment and let us know your thoughts!

in MySQL Stored Procedures,cursorsstored
After my
previous article on Stored Procedures was published on SitePo…

6.1 Using Dynamic SQL

Dynamic SQL, or generating SQL statements on the fly, is a constant need
in a production environment. Very often, and especially in the matter of
updates to be performed on a database, the final query is not known
until run time.

For scenarios where many similar queries with differing update values
must be run on the database, you can use the OraclePreparedStatement
object, which extends the Statement object. This is done by
substituting the literal update values with bind variables. You can also
use stored PL/SQL functions on the database by calling stored procedures
through the OracleCallableStatement object.

This section discusses the following topics:

  • Using OraclePreparedStatement

  • Using OracleCallableStatement

  • Using Bind Variables

A real world question

My personal website has a page showing the scores of my favorite NBA
team: LA Lakers. The table structure behind it is straightforward:

997755.com澳门葡京 5

Fig 1. The Lakers matches status table structure

I have been updating this table since 2008. Some of the latest records
showing Lakers’ 2013-14 season are shown below:

997755.com澳门葡京 6

Fig 2. The Lakers matches status table data (partial) for 2013-2014
season

(I am using MySQL Workbench as the GUI tool to manage my MySQL
databases. You can use your favorite tool.)

Well, I have to admit Lakers are not playing very well these days. 6
consecutive losses up to Jan 15th. I get this “6 consecutive losses” by
manually counting from the last played match all the way up (towards
earlier games) and see how long an “L” (meaning a loss) in winloseCursors in MySQL Stored Procedures。
column can appear.  This is certainly doable but if the requirement
becomes more complicated in a larger table, it takes more time and is
more error prone.

Can we do this with a single SQL statement? I am not an SQL expert and I
haven’t been able to figure out how to achieve the desired result (“6
consecutive losses”) from one SQL statement. The input of gurus will be
highly appreciated – leave it in the comments below.

Can we do this in PHP? Yes, of course. We can retrieve the game data
(particularly, the winlose column) for current season and do a
traverse on the records to calculate the current longest win/lose
streak. But to do that, we will have to grab all data for that year and
most of the data will be wasted (as it is not likely for a team to have
a win/lose streak for more than 20+ games in a 82-game regular season).
However, we don’t know how many records should be retrieved into PHP to
determine the streak, so this waste is a must. And finally, if the
current win/lose streak is the only thing we want to know from that
table, why pull all the raw data?

Can we do this via other means? Yes, it is possible. For example, we can
create a redundant table specifically designed to store the current
win/lose streak. Every insertion of the record will update that table
too. But this is way too cumbersome and too error prone.

So, what is a better way to achieve this result?

 

6.1.1 Using OraclePreparedStatement

To run static SQL queries on the database, you use the Statement
object. However, to run multiple similar queries or perform multiple
updates that affect many columns in the database, it is not feasible to
hard-code each query in your application.

You can use OraclePreparedStatement when you run the same SQL
statement multiple times. Consider a query like the following:

SELECT * FROM Employees WHERE ID=xyz;

Every time the value of xyz in this query changes, the SQL statement
needs to be compiled again.

If you use OraclePreparedStatement functionality, the SQL statement
you want to run is precompiled and stored in a PreparedStatement
object, and you can run it as many times as required without compiling
it every time it is run. If the data in the statement changes, you can
use bind variables as placeholders for the data and then provide literal
values at run time.

Consider the following example of using OraclePreparedStatement:

Example 6-1 Creating a PreparedStatement

OraclePreparedStatement pstmt = conn.prepareStatement("UPDATE Employees 
                                SET salary = ? WHERE ID = ?");
   pstmt.setBigDecimal(1, 153833.00)
   pstmt.setInt(2, 110592)   

The advantages of using the OraclePreparedStatement interface include:

  • You can batch updates by using the same PreparedStatement object

  • You can improve performance because the SQL statement that is run
    many times is compiled only the first time it is run.

  • You can use bind variables to make the code simpler and reusable.

Using Cursor in a Stored Procedure

As the name of this article suggests, we will see a better alternative
(in my view) to solve this problem: using cursor in a Stored Procedure.

Let’s create the first SP in MySQL Workbench as follows:

 

DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `streak`(in cur_year int, out longeststreak int, out status char(1))
BEGIN
    declare current_win char(1);
    declare current_streak int;
    declare current_status char (1);

    declare cur cursor for select winlose from lakers where year=cur_year and winlose<>'' order by id desc;

    set current_streak=0;

    open cur;

    fetch cur into current_win;
    set current_streak = current_streak +1;

    start_loop: loop
        fetch cur into current_status;
            if current_status <> current_win then 
                leave start_loop;
            else
                set current_streak=current_streak+1;
            end if;

    end loop;

    close cur;

    select current_streak into longeststreak;
    select current_win into `status`;
END

In this SP, we have one input parameter and two output parameters. This
defines the signature of the SP.

In the SP body, we also declared a few local variables to hold the
streak status (win or lose, current_win), current streak and current
win/lose status for a particular match.

 

declare cur cursor for select winlose from lakers where year=cur_year and winlose<>'' order by id desc;

The above line is the cursor declaration. We declared a cursor named
cur and the dataset bind to that cursor is the win/lose status for
those matches played (thus its winlose column is either “W” or “L”
instead of nothing) in a particular year ordered by id (the latest
played games will have the highest ID) descending.

Though not displayed explicitly, we can imagine that this dataset will
contain a series of “L”s and “W”s. Based on the data shown in Figure 2
above, it should be: “LLLLLLWLL…” (6 Ls, 1 Ws, etc).

To calculate the win/lose streak, we begin with the latest (and the
first in the dataset) match data. When a cursor is opened, it always
starts at the first record in the associated dataset.

After the first data is grabbed, the cursor will move to the next
record. In this way, a cursor behaves very much like a queue, traversing
the dataset in a FIFO (First In First Out) manner. This is exactly what
we wanted. 

After getting the current win/lose status and set the streak number, we
continue to loop through (traverse) the remainder of the dataset. With
each loop iteration, the cursor will “point” to the next record until we
break the loop or all the records are consumed. 

If the next win/lose status is the same as the current win/lose status,
it means the streak goes on and we increase the streak number by 1 and
continue the traversing; otherwise, it means the streak discontinues and
we can leave the loop earlier.

Finally, we close the cursor and release the resources. Then we return
the desired output.

Next, we can enhance the access control of the SP as described in my
previous
article.

To test the output of this SP, we will write a short PHP script:

 

<?php
$dbms = 'mysql';

$host = 'localhost';
$db = 'sitepoint';
$user = 'root';
$pass = 'your_pass_here';
$dsn = "$dbms:host=$host;dbname=$db";

$cn=new PDO($dsn, $user, $pass);

$cn->exec('call streak(2013, @longeststreak, @status)');
$res=$cn->query('select @longeststreak, @status')->fetchAll();

var_dump($res); //Dump the output here to get a raw view of the output

$win=$res[0]['@status']='L'?'Loss':'Win';
$streak=$res[0]['@longeststreak'];

echo "Lakers is now $streak consecutive $win.\n";

This will output something like the following figure:

997755.com澳门葡京 7

(This output is based on Lakers’ match up to Jan 15th, 2014.)

 

6.1.2 Using OracleCallableStatement

You can access stored procedures on databases using the
OracleCallableStatement interface. This interface extends the
OraclePreparedStatement interface. The OracleCallableStatement
interface consists of standard JDBC escape syntax to call stored
procedures. You may use this with or without a result parameter.
However, if you do use a result parameter, it must be registered as an
OUT parameter. Other parameters that you use with this interface can
be either IN, OUT, or both.

These parameters are set by using accessor methods inherited from the
OraclePreparedStatement interface. IN parameters are set by using
the set``XXX methods and OUT parameters are retrieved by using the
get``XXX methods, XXX being the Java data type of the parameter.

A CallableStatement can also return multiple ResultSet objects.

As an example, you can create an OracleCallableStatement to call the
stored procedure called foo, as follows:

Example 6-2 Creating a CallableStatement

OracleCallableStatement cs = (OracleCallableStatement)
conn.prepareCall("{call foo(?)}");

You can pass the string bar to this procedure in one of the following
two ways:

cs.setString(1,"bar"); // JDBC standard
// or...
cs.setString("myparameter","bar"); // Oracle extension

Return a dataset from a Stored Procedure

A few discussions went along on how to return a dataset from an SP,
which constructs the dataset out of the results from a few repeated
calls to another SP.

A user may want to know more from our previously created SP that only
returns a win/lose streak for one year; thus we can get a table showing
the win/lose streaks for all the years in a form like:

YEAR Win/Lose Streak
2013 L 6
2012 L 4
2011 L 2

(Well, a more useful result can be to return the longest win streak and
loss streak in a particular season. This requirement can be easily
expanded from the previous SP so I will leave it to interested parties
to implement. For the purpose of this article, we will stick to the
current win/loss streak.)

MySQL SP can only return scalar results (an integer, a string, etc),
unless the result is returned by a select ... from ... statement (and
it becomes a dataset). The issue here is that the table-form data we
want to see does not exist in our current db structure and is
constructed from another SP.

To tackle this, we need the help of a temporary table, or if situation
allows and requires, a redundant table. Let’s see how we can achieve our
target via a temporary table.

First, we will create a second SP as shown below:

 

DELIMITER $$

CREATE DEFINER=`root`@`%` PROCEDURE `yearly_streak`()
begin
    declare cur_year, max_year, min_year int;

    select max(year), min(year) from lakers into max_year, min_year;

    DROP TEMPORARY TABLE IF EXISTS yearly_streak;
    CREATE TEMPORARY TABLE yearly_streak (season int, streak int, win char(1));

    set cur_year=max_year;

    year_loop: loop
        if cur_year<min_year then
            leave year_loop;
        end if;

        call streak(cur_year, @l, @s);
        insert into yearly_streak values (cur_year, @l, @s);

        set cur_year=cur_year-1;
    end loop;

    select * from yearly_streak;
    DROP TEMPORARY TABLE IF EXISTS yearly_streak;

END

A few key things to notice here:

  1. We determine the max year and min year by selecting from the table
    lakers;
  2. We created a temp table to hold the output, with the structure
    requested by the output (season, streak, win);
  3. In the loop, we first execute our previously created SP, with
    necessary parameters (call streak(cur_year, @l, @s);), then grab
    the data returned and insert into the temp table
    (insert into yearly_streak values (cur_year, @l, @s);).
  4. Finally, we select from the temp table and return the dataset, then
    do some cleaning (DROP TEMPORARY TABLE IF EXISTS yearly_streak;).

To get the results, we create another short PHP script as shown below:

 

<?php
... // Here goes the db connection parameters

$cn=new PDO($dsn, $user, $pass);

$res=$cn->query('call yearly_streak')->fetchAll();

foreach ($res as $r)
{
    echo sprintf("In year %d, the longest W/L streaks is %d %s\n", $r['season'], $r['streak'], $r['win']);
}

And the display will be like:

997755.com澳门葡京 8

Please note that the above is a bit different from calling our first SP.

The first SP does not return a dataset but only two parameters. In that
case, we use PDO exec then query to fetch the output; while in the
second SP, we returned a dataset from the SP, so we use PDO query
directly to invoke the call to the SP.

Voila! We did it!

 

6.1.3 Using Bind Variables

Bind variables are variable substitutes for literals in a SQL statement.
They are used in conjunction with OraclePreparedStatement and
OracleCallableStatement to specify parameter values that are used to
build the SQL statement. Using bind variables has remarkable performance
advantages in a production environment.

For PL/SQL blocks or stored procedure calls, you can use the following
qualifiers to differentiate between input and output variables: IN,
OUT, and IN OUT. Input variable values are set by using set``XXX
methods and OUT variable values can be retrieved by using get``XXX
methods, where XXX is the Java data type of the values. This depends
on the SQL data types of the columns that you are accessing in the
database.

Conclusion

In this article, we dug further into MySQL stored procedures and took a
look at the cursor functionality. We have demonstrated how to fetch
scalar data by output parameters (defined as out var_name vartype in
the SP declaration) and also fetch a calculated dataset via a temp
table. During this process, a few statements otherwise used in stored
procedures also surfaced.

The official documentation on the syntax of stored procedure and various
statements can be found on the MySQL website. To create a stored
procedure, please refer to these
documents
and to understand the statements, please see
here.

Feel free to comment and let us know your thoughts!

6.2 Calling Stored Procedures

Oracle Java Database Connectivity (JDBC) drivers support the processing
of PL/SQL stored procedures and anonymous blocks. They support Oracle
PL/SQL block syntax and most of SQL92 escape syntax. The following
PL/SQL calls would work with any Oracle JDBC driver:

Example 6-3 Calling Stored Procedures

// SQL92 syntaxCallableStatement cs1 = conn.prepareCall
                        ( "{call proc (?,?)}" ) ; // stored proc
CallableStatement cs2 = conn.prepareCall
                        ( "{? = call func (?,?)}" ) ; // stored func

// Oracle PL/SQL block syntax
CallableStatement cs3 = conn.prepareCall
                        ( "begin proc (?,?); end;" ) ; // stored proc
CallableStatement cs4 = conn.prepareCall
                        ( "begin ? := func(?,?); end;" ) ; // stored func

As an example of using the Oracle syntax, here is a PL/SQL code snippet
that creates a stored function. The PL/SQL function gets a character
sequence and concatenates a suffix to it:

Example 6-4 Creating a Stored Function

create or replace function foo (val1 char)
return char as
begin
return val1 || 'suffix';
end;

You can call this stored function in a Java program as follows:

Example 6-5 Calling a Stored Function in Java

OracleDataSource ods = new OracleDataSource();
ods.setURL("jdbc:oracle:thin:@<hoststring>");
ods.setUser("hr");
ods.setPassword("hr");
Connection conn = ods.getConnection();
CallableStatement cs = conn.prepareCall ("begin ? := foo(?); end;");
cs.registerOutParameter(1,Types.CHAR);
cs.setString(2, "aa");
cs.executeUpdate();
String result = cs.getString(1);

The following sections describe how you can use stored procedures in the
sample application in this guide:

  • Creating a PL/SQL Stored Procedure in
    JDeveloper

  • Creating a Method to Use the Stored
    Procedure

  • Allowing Users to Choose the Stored
    Procedure

  • Calling the Stored Procedure from the
    Application

6.2.1 Creating a PL/SQL Stored Procedure in JDeveloper

JDeveloper allows you to create stored procedures in the database
through the Connection Navigator. In these steps, you create a stored
procedure that can be used as an alternative way of inserting an
employee record in the sample application.

  1. Select the Connections tab to view the Connection
    Navigator.

  2. Expand the database connection node (by default called
    DBConnection1), and the HR node to see the
    objects in the HR database.

  3. Right-click Procedures, and select New PL/SQL Procedure.

  4. In the Create PL/SQL Procedure dialog, enter insert_employee as
    the object name. Click OK.

    The skeleton code for the procedure is displayed in the Source
    Editor.

  5. After the procedure name, enter the following lines of code:

    PROCEDURE    "INSERT_EMPLOYEE" (p_first_name  employees.first_name%type, 
      p_last_name    employees.last_name%type,
      p_email        employees.email%type,
      p_phone_number employees.phone_number%type,
      p_job_id       employees.job_id%type,
      p_salary       employees.salary%type
    ) 
    
  6. After the BEGIN statement, replace the line that reads NULL with
    the following:

      INSERT INTO Employees VALUES (EMPLOYEES_SEQ.nextval, p_first_name , 
        p_last_name , p_email , p_phone_number, SYSDATE, p_job_id, 
        p_salary,.30,100,80);
    

    You can see that the statement uses the same hard-coded values that
    are used for the last three columns in the addEmployee method in
    the DataHandler.java class.

  7. Add the procedure name in the END statement:

    END insert_employee;
    
  8. Save the file, and check whether there are any compilation errors.

The complete code for the stored procedure is shown in Example
6-6.

Example 6-6 Creating a PL/SQL Stored Procedure to Insert Employee Data

PROCEDURE    "INSERT_EMPLOYEE" (p_first_name  employees.first_name%type, 
  p_last_name    employees.last_name%type,
  p_email        employees.email%type,
  p_phone_number employees.phone_number%type,
  p_job_id       employees.job_id%type,
  p_salary       employees.salary%type
) 
AS
BEGIN
  INSERT INTO Employees VALUES (EMPLOYEES_SEQ.nextval, p_first_name , 
    p_last_name , p_email , p_phone_number, SYSDATE, p_job_id, 
    p_salary,.30,100,80);
END insert_employee;

6.2.2 Creating a Method to Use the Stored Procedure

In these steps, you add a method to the DataHandler.java class that
can be used as an alternative to the addEmployee method. The new
method you add here makes use of the insert_employee stored procedure.

  1. Select the Applications tab to display the
    Application Navigator.

  2. If the DataHandler.java file is not already open in the Java
    Source Editor, double-click it to open it.

  3. Import the CallableStatement interface as follows:

    import java.sql.CallableStatement;
    
  4. After the addEmployee method, add the declaration for the
    addEmployeeSP method.

    public String addEmployeeSP(String first_name, String last_name, 
      String email, String phone_number, String job_id,
      int salary) throws SQLException {
    }
    

    The method signature is the same as that for addEmployee.

  5. Inside the method, add a try block, and inside that, connect to
    the database.

    try {
      getDBConnection(); 
    }
    
  6. In addition, inside the try block, create the SQL string:

    sqlString = "begin hr.insert_employee(?,?,?,?,?,?); end;";
    

    The question marks (?) in the statement are bind variables, acting
    as placeholders for the values of first_name, last_name, and so
    on expected by the stored procedure.

  7. Create the CallableStatement:

    CallableStatement callstmt = conn.prepareCall(sqlString);
    
  8. Set the IN parameters:

      callstmt.setString(1, first_name);
      callstmt.setString(2, last_name);
      callstmt.setString(3, email);
      callstmt.setString(4, phone_number);
      callstmt.setString(5, job_id);
      callstmt.setInt(6, salary);
    
  9. Add a trace message, and run the callable statement.

      System.out.println("\nInserting with stored procedure: " + 
                          sqlString);
      callstmt.execute();
    
  10. Add a return message:

      return "success"; 
    
  11. After the try block, add a catch block to trap any errors. Call
    the logException created in Example
    5-5.

    catch ( SQLException ex ) {
      System.out.println("Possible source of error: Make sure you have created the stored procedure"); 
      logException( ex ); 
      return "failure";
    }
    
  12. Save DataHandler.java.

The complete method is shown in Example
6-7.

Note:

If you have not added the logException() method (see Example
5-5), JDeveloper will
indicate an error by showing a red curly line under logException(ex).
This method must be present in the DataHandler.java class before you
proceed with compiling the file.

Example 6-7 Using PL/SQL Stored Procedures in Java

public String addEmployeeSP(String first_name, String last_name, 
  String email, String phone_number, String job_id,
  int salary) throws SQLException {

  try {
    getDBConnection(); 
    sqlString = "begin hr.insert_employee(?,?,?,?,?,?); end;";
    CallableStatement callstmt = conn.prepareCall(sqlString);
    callstmt.setString(1, first_name);
    callstmt.setString(2, last_name);
    callstmt.setString(3, email);
    callstmt.setString(4, phone_number);
    callstmt.setString(5, job_id);
    callstmt.setInt(6, salary);
    System.out.println("\nInserting with stored procedure: " + 
                       sqlString);

    callstmt.execute();
    return "success"; 
  }
  catch ( SQLException ex ) {
    System.out.println("Possible source of error: Make sure you have created the stored procedure"); 
    logException( ex ); 
    return "failure";
  }
}

6.2.3 Allowing Users to Choose the Stored Procedure

The steps in this section add a radio button group to the insert.jsp
page, which allows a user to choose between inserting an employee record
using the stored procedure, or by using a SQL query in Java code.

  1. Open insert.jsp in the Visual Editor, if it is not already open.

  2. Create a new line after the Insert Employee Record heading. With the
    cursor on this new line, drag UseBean from the
    JSP page of the Component Palette to add a jsp:useBean tag to the
    page. Enter empsbean as the ID, browse to select hr.DataHandler
    as the Class, and set the Scope to session. With the UseBean still selected on
    the page, set the style of this line to None instead of
    Heading 3.

  3. Drag a Radio Button component from the HTML Forms
    page of the Component Palette onto the page inside the form above
    the table. In the Insert Radio Button dialog, enter useSP as the
    Name, false as the Value,
    and select Checked. Click OK.

  4. In the Visual Editor, position the cursor to the right of the
    button, and enter text to describe the purpose of the button, for
    example, ‘Use only JDBC to insert a new record’.

  5. Press Enter at the end of the current line to create a new line.

  6. Drag a second Radio Button below the first one.
    In the Insert Radio Button dialog, use useSP as the Name, true as the Value, and
    ensure that the Checked checkbox is not
    selected.

  7. In the Visual Editor, position the cursor directly to the right of
    the button, and enter text to describe the purpose of the button,
    for example, ‘Use stored procedure called via JDBC to insert a
    record’.

  8. Save the page.

Figure 6-1 shows insert.jsp with
the radio button that provides the option to use a stored procedure.

Figure 6-1 Adding a Link to Provide the Stored Procedure Option

997755.com澳门葡京 9
Description of “Figure 6-1 Adding a Link to Provide the Stored
Procedure Option”

6.2.4 Calling the Stored Procedure from the Application

The steps in this section modify the insert_action.jsp file, which
processes the form on the insert.jsp page, to use the radio button
selection and select the appropriate method for inserting a new employee
record.

  1. Open insert_action.jsp in the Visual Editor, if it is not already
    open.

  2. Double-click the scriptlet to invoke the Scriptlet Properties dialog
    box and add a new variable after the salary variable, as follows:

    String useSPFlag = request.getParameter("useSP");
    
  3. Below that, still in the Scriptlet Properties dialog box, replace
    the existing empsbean.addEmployee line with the following lines of
    code to select the addEmployeeSP method or the pure JDBC
    addEmployee method to insert the record.

    if ( useSPFlag.equalsIgnoreCase("true"))
      empsbean.addEmployeeSP(first_name, last_name, email, 
      phone_number, job_id, salary.intValue()); 
    // otherwise use pure JDBC insert
    else
      empsbean.addEmployee(first_name, last_name, email, 
      phone_number, job_id, salary.intValue()); 
    
  4. Save insert_action.jsp.

You can now run the application and use the radio buttons on the insert
page to choose how you want to insert the new employee record. In a
browser, the page will appear as shown in Figure
6-2.

Figure 6-2 Using Stored Procedures to Enter Records

997755.com澳门葡京 10
Description of “Figure 6-2 Using Stored Procedures to Enter
Records”

6.3 Using Cursor Variables

Oracle JDBC drivers support cursor variables with the REF CURSOR
types, which are not a part of the JDBC standard. REF CURSOR types
are supported as JDBC result sets.

A cursor variable holds the memory location of a query work area, rather
than the contents of the area. Declaring a cursor variable creates a
pointer. In SQL, a pointer has the data type REF x, where REF is
short for REFERENCE and x represents the entity being referenced. A
REF CURSOR, then, identifies a reference to a cursor variable. Because
many cursor variables might exist to point to many work areas, REF
CURSOR can be thought of as a category or data type specifier that
identifies many different types of cursor variables. A REF CURSOR
essentially encapsulates the results of a query.

Oracle does not return ResultSets. To access data returned by a query,
you use CURSORS and REF CURSORS. CURSORS contain query results and
metadata. A REF CURSOR (or CURSOR variable) data type contains a
reference to a cursor. It can be passed between the RDBMS and the
client, or between PL/SQL and Java in the database. It can also be
returned from a query or a stored procedure.

Note:

REF CURSOR instances are not scrollable.

This section contains the following subsections:

  • Oracle REF CURSOR Type Category

  • Accessing REF CURSOR Data

  • Using REF CURSOR in the Sample
    Application

6.3.1 Oracle REF CURSOR Type Category

To create a cursor variable, begin by identifying a type that belongs to
the REF CURSOR category. For example:

dept_cv DeptCursorTyp 
...

Then, create the cursor variable by declaring it to be of the type
DeptCursorTyp:

Example 6-8 Declaring a REF CURSOR Type

DECLARE TYPE DeptCursorTyp IS REF CURSOR

REF CURSOR, then, is a category of data types, rather than a
particular data type. Stored procedures can return cursor variables of
the REF CURSOR category. This output is equivalent to a database
cursor or a JDBC result set.

6.3.2 Accessing REF CURSOR Data

In Java, `aREF CURSORis materialized as aResultSet` object and
can be accessed as follows:

Example 6-9 Accessing REF Cursor Data in Java

import oracle.jdbc.*;
...
CallableStatement cstmt;
ResultSet cursor;

// Use a PL/SQL block to open the cursor
cstmt = conn.prepareCall
         ("begin open ? for select ename from emp; end;");

cstmt.registerOutParameter(1, OracleTypes.CURSOR);
cstmt.execute();
cursor = ((OracleCallableStatement)cstmt).getCursor(1);

// Use the cursor like a normal ResultSet
while (cursor.next ())
    {System.out.println (cursor.getString(1));} 

In the preceding example:

  1. A CallableStatement object is created by using the prepareCall
    method of the connection class.

  2. The callable statement implements a PL/SQL procedure that returns a
    REF CURSOR.

  3. As always, the output parameter of the callable statement must be
    registered to define its type. Use the type code
    OracleTypes.CURSOR for a REF CURSOR.

  4. The callable statement is run, returning the REF CURSOR.

  5. The CallableStatement object is cast to OracleCallableStatement
    to use the getCursor method, which is an Oracle extension to the
    standard JDBC application programming interface (API), and returns
    the REF CURSOR into a ResultSet object.

6.3.3 Using REF CURSOR in the Sample Application

In the following sections, you enhance the sample application to display
a dynamically-generated list of job IDs and job titles in the Job field
when they are inserting a new employee record.

  • Creating a Package in the
    Database

  • Creating a Database Function

  • Calling the REF CURSOR from a
    Method

  • Displaying a Dynamically Generated
    List

To do this, you create a database function, GET_JOBS, that uses a
REF CURSOR to retrieve a result set of jobs from the Jobs table. A
new Java method, getJobs, calls this database function to retrieve the
result set.

6.3.3.1 Creating a Package in the Database

The following steps create a new package in the database to hold a
REF CURSOR declaration.

  1. Select the Connections tab to view it in the
    Navigator.

  2. Expand the Database node, the DBConnection1 node, and the HR node,
    to view the list of database objects. Scroll down to Packages.
    Right-click Packages and select New PL/SQL Package.

  3. In the Create PL/SQL Package dialog, enter JOBSPKG as the name.
    Click OK. The package definition is displayed in
    the Source Editor.

  4. Position the cursor at the end of the first line and press Enter to
    create a new line. In the new line, declare a REF CURSOR as
    follows:

        TYPE ref_cursor IS REF CURSOR;
    
  5. Save the package.

The code for the package is shown in Example
6-10:

Example 6-10 Creating a Package in the Database

PACKAGE "JOBSPKG" AS
    TYPE ref_cursor IS REF CURSOR;
END;

6.3.3.2 Creating a Database Function

These steps create a database function GET_JOBS that uses a
REF CURSOR to retrieve a result set of jobs from the Jobs table.

  1. In the Connections Navigator, again expand the necessary nodes to
    view the objects in the HR database. Right-click Functions and select New PL/SQL
    Function from the shortcut menu.

  2. In the Create PL/SQL Function dialog, enter GET_JOBS as the name.
    Click OK. The definition for the GET_JOBS
    function displays in the Source Editor

  3. In the first line of the function definition, substitute
    JobsPkg.ref_cursor as the return value, in place of VARCHAR2.

  4. After the AS keyword, enter the following:

     jobs_cursor JobsPkg.ref_cursor;
    
  5. In the BEGIN block enter the following code to replace the current
    content:

      OPEN jobs_cursor FOR
      SELECT job_id, job_title FROM jobs;
      RETURN jobs_cursor;
    
  6. Save the function

The code for the function is shown in Example
6-11.

Example 6-11 Creating a Stored Function

FUNCTION    "GET_JOBS"
RETURN JobsPkg.ref_cursor
AS jobs_cursor JobsPkg.ref_cursor;
BEGIN
  OPEN jobs_cursor FOR
  SELECT job_id, job_title FROM jobs;
  RETURN jobs_cursor;
END;

6.3.3.3 Calling the REF CURSOR from a Method

These steps create a Java method, getJobs, in the DataHandler class
that calls the GET_JOBS function to retrieve the result set.

  1. Double-click DataHandler.java to open it in the Source Editor if
    it is not already open.

  2. Enter the method declaration.

    public ResultSet getJobs() throws SQLException {
    
    }
    
  3. Within the method body, connect to the database.

      getDBConnection();
    
  4. Following the connection, declare a new variable, jobquery:

      String jobquery = "begin ? := get_jobs; end;";
    
  5. Create a CallableStatement using the prepareCall method:

      CallableStatement callStmt = conn.prepareCall(jobquery);
    
  6. Register the type of the OUT parameter, using an Oracle-specific
    type.

      callStmt.registerOutParameter(1, OracleTypes.CURSOR);
    
  7. When you specify that you want to use an Oracle-specific type,
    JDeveloper displays a message asking you to use Alt+Enter to import
    oracle.jdbc.OracleTypes. Press Alt+Enter, and then select OracleTypes (oracle.jdbc) from the list that
    appears.

  8. Run the statement and return the result set.

      callStmt.execute();
      rset = (ResultSet)callStmt.getObject(1);
    
  9. Enclose the code entered so far in a try block.

  10. Add a catch block to catch any exceptions, and call your
    logException method as well.

    catch ( SQLException ex ) {
      logException( ex );
    }
    
  11. After the close of the catch block, return the result set.

    return rset;
    
  12. Make the file to check for syntax errors.

The code for the getJobs method is as follows:

  public ResultSet getJobs() throws SQLException {
  try {
    getDBConnection();
    String jobquery = "begin ? := get_jobs; end;";
    CallableStatement callStmt = conn.prepareCall(jobquery);
    callStmt.registerOutParameter(1, OracleTypes.CURSOR);
    callStmt.execute();
    rset = (ResultSet)callStmt.getObject(1);
  } catch ( SQLException ex ) {
  logException( ex );
  }
  return rset;
  }

6.3.3.4 Displaying a Dynamically Generated List

To create the drop down list displaying the list of job IDs and job
titles in the Insert page, you hard-coded the job IDs and job titles. In
the following steps, you replace this with a dynamically-generated list
provided by the REF CURSOR created in the previous section.

  1. Double-click insert.jsp in the Application Navigator to open it in
    the Visual Editor, if it is not already open.

  2. Drag a Page Directive onto the page to the right
    of the useBean tag. In the Insert Page Directive dialog box, enter
    Java as the Language, and in the Import field, browse to select java.sql.ResultSet. Click OK.

  3. Drag a scriptlet onto the page next to the Page Directive. In the
    Insert Scriptlet dialog box, add the following code to execute the
    getJobs method and return a result set containing a list of jobs.

    ResultSet rset = empsbean.getJobs();
    
  4. Select the ListBox component in the page, and
    click Scriptlet in the JSP Component Palette.
    (You need not drag and drop the scriptlet onto the page in this
    case.) The Insert Scriptlet dialog box appears.

  5. Enter the following code into the Insert Scriptlet dialog box. Click
    OK.

      while (rset.next ())
       {
        out.println("<option value=" + rset.getString("job_id") + ">" + 
        rset.getString("job_title") + "</option> "  );
       }
    
  6. Remove the hard-coded values as follows.

    With the ListBox component still selected, in the
    Structure window scroll to Job field. Examine the
    list of hard-coded options below the select keyword. Delete each of
    the options, ensuring that you retain the scriptlet.

    Figure 6-3 Structure View of Dropdown ListBox Options

    997755.com澳门葡京 11
    Description of “Figure 6-3 Structure View of Dropdown ListBox
    Options”

  1. Save the page.

Now run the application, click to insert a new employee and use the list
to display a list of available jobs. Figure
6-4 shows the dynamic jobs list in
the browser.

 

相关文章

发表评论

电子邮件地址不会被公开。 必填项已用*标注

*
*
Website