DB2 cursors in stored procedures with parameters?

[patrick.cc.choi]

Hi all,

In Oracle you can pass parameters to cursors i.e.
cursor myCursor(name_city varchar(50), population integer) is
select * from cities
where name = name_city and
num_people = population;
Can you do the same thing in DB2?

All I can see is:

DECLARE myCursor CURSOR WITH RETURN FOR

and this syntax doesn't allow for parameters...
Thanks!

[Mark A]

<pa*************@gmail.comwrote in message
news:11**********************@n67g2000cwd.googlegr oups.com...

Hi all,

In Oracle you can pass parameters to cursors i.e.

cursor myCursor(name_city varchar(50), population integer) is
select * from cities
where name = name_city and
num_people = population;

Can you do the same thing in DB2?

All I can see is:

DECLARE myCursor CURSOR WITH RETURN FOR

and this syntax doesn't allow for parameters...

Thanks!

I am not sure what you are trying to do. Here is an example SP in DB2 that
returns a cursor based on input variable.

CREATE PROCEDURE DB2INST1.DEPT_EMP
(IN IN_DEPT CHAR(3))

RESULT SETS 1
MODIFIES SQL DATA
LANGUAGE SQL

BEGIN

DECLARE C1 CURSOR WITH RETURN FOR
SELECT EMPNO
FROM DB2INST1.EMPLOYEE
WHERE WORKDEPT = IN_DEPT;

OPEN C1;

END
-----------------------------------------------------

CALL DB2INST1.DEPT_EMP ('A00')

Result set 1
--------------

EMPNO
------
000010
000110
000120

3 record(s) selected.

Return Status = 0

[patrick.cc.choi]

Thanks for your quick reply.

Actually what I'm trying to do is get the cursor declaration to take
the parameter...

i.e. in your example the parameter is in the CREATE PROCEDURE
declaration

CREATE PROCEDURE DB2INST1.DEPT_EMP (IN IN_DEPT CHAR(3))

I want to put it in the DECLARE stmt like so:

DECLARE C1 CURSOR(param1, param2) WITH RETURN FOR

Then call it like so:

OPEN c1(param1, param2...);


Mark A wrote:

<pa*************@gmail.comwrote in message
news:11**********************@n67g2000cwd.googlegr oups.com...

Hi all,

In Oracle you can pass parameters to cursors i.e.

cursor myCursor(name_city varchar(50), population integer) is
select * from cities
where name = name_city and
num_people = population;

Can you do the same thing in DB2?

All I can see is:

DECLARE myCursor CURSOR WITH RETURN FOR

and this syntax doesn't allow for parameters...

Thanks!

I am not sure what you are trying to do. Here is an example SP in DB2 that
returns a cursor based on input variable.

CREATE PROCEDURE DB2INST1.DEPT_EMP
(IN IN_DEPT CHAR(3))

RESULT SETS 1
MODIFIES SQL DATA
LANGUAGE SQL

BEGIN

DECLARE C1 CURSOR WITH RETURN FOR
SELECT EMPNO
FROM DB2INST1.EMPLOYEE
WHERE WORKDEPT = IN_DEPT;

OPEN C1;

END
-----------------------------------------------------

CALL DB2INST1.DEPT_EMP ('A00')

Result set 1
--------------

EMPNO
------
000010
000110
000120

3 record(s) selected.

Return Status = 0

[Mark A]

<pa*************@gmail.comwrote in message
news:11**********************@16g2000cwy.googlegro ups.com...

Thanks for your quick reply.

Actually what I'm trying to do is get the cursor declaration to take
the parameter...

i.e. in your example the parameter is in the CREATE PROCEDURE
declaration

CREATE PROCEDURE DB2INST1.DEPT_EMP (IN IN_DEPT CHAR(3))

I want to put it in the DECLARE stmt like so:

DECLARE C1 CURSOR(param1, param2) WITH RETURN FOR

Then call it like so:

OPEN c1(param1, param2...);

Why?

You can use a variable in the SQL statement of the cursor, and in situations
where you cannot use a variable for the SQL, then you can use a dynamically
prepared cursor with the entire definition as a parm or variable.

[patrick.cc.choi]

What you described is actually what I'm doing now (had to find a
workaround quickly =)). However it just seems that the Oracle
implementation is cleaner, and was wondering if there was an equivalent
to doing something similar.
Mark A wrote:

<pa*************@gmail.comwrote in message
news:11**********************@16g2000cwy.googlegro ups.com...

Thanks for your quick reply.

Actually what I'm trying to do is get the cursor declaration to take
the parameter...

i.e. in your example the parameter is in the CREATE PROCEDURE
declaration

CREATE PROCEDURE DB2INST1.DEPT_EMP (IN IN_DEPT CHAR(3))

I want to put it in the DECLARE stmt like so:

DECLARE C1 CURSOR(param1, param2) WITH RETURN FOR

Then call it like so:

OPEN c1(param1, param2...);

Why?

You can use a variable in the SQL statement of the cursor, and in situations
where you cannot use a variable for the SQL, then you can use a dynamically
prepared cursor with the entire definition as a parm or variable.

[Serge Rielau]

pa*************@gmail.com wrote:

What you described is actually what I'm doing now (had to find a
workaround quickly =)). However it just seems that the Oracle
implementation is cleaner, and was wondering if there was an equivalent
to doing something similar.

Correct - and I agree that parameterizing cursors is a nice-to-have.
It does, as Mark shows not add any function however.
You just use the variable directly within the SELECT.
If you don't know yet which variable to use when you DECLARE you can use
a temporary variable in the SELECT and assign to it before the OPEN.
All the Oracle syntax does is to father up the parameters in one place
(between the braces) and move the assignment into the OPEN syntax.

The closest I can think of that would give you at least the
encapsulation would be a table function (aka a parameterized view). But
these need to be statically created.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

WAIUG Conference
http://www.iiug.org/waiug/present/Fo...Forum2006.html

[Patrick]

Thanks again. As a developer it's always nice to have these functions.
They make life just a little easier day by day. But for now the
workaround is just as good.
Serge Rielau wrote:

pa*************@gmail.com wrote:

What you described is actually what I'm doing now (had to find a
workaround quickly =)). However it just seems that the Oracle
implementation is cleaner, and was wondering if there was an equivalent
to doing something similar.

Correct - and I agree that parameterizing cursors is a nice-to-have.
It does, as Mark shows not add any function however.
You just use the variable directly within the SELECT.
If you don't know yet which variable to use when you DECLARE you can use
a temporary variable in the SELECT and assign to it before the OPEN.
All the Oracle syntax does is to father up the parameters in one place
(between the braces) and move the assignment into the OPEN syntax.

The closest I can think of that would give you at least the
encapsulation would be a table function (aka a parameterized view). But
these need to be statically created.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

WAIUG Conference
http://www.iiug.org/waiug/present/Fo...Forum2006.html

[Serge Rielau]

Patrick wrote:

Thanks again. As a developer it's always nice to have these functions.
They make life just a little easier day by day. But for now the
workaround is just as good.

I agree. Rest assured that I recognize a good thing when I see it. Even
if it's coming from the competition :-)

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

WAIUG Conference
http://www.iiug.org/waiug/present/Fo...Forum2006.html