DB2 cursors in stored procedures with parameters?

[patrick.cc.choi@gmail.com]

Hi all,

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


cursor myCursor(name_c ity 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]

Re: DB2 cursors in stored procedures with parameters?

<patrick.cc.cho i@gmail.comwrot e in message
news:1165549360 .271100.199970@ n67g2000cwd.goo glegroups.com.. .

Hi all,
>
In Oracle you can pass parameters to cursors i.e.
>
cursor myCursor(name_c ity 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_E MP
(IN IN_DEPT CHAR(3))

RESULT SETS 1
MODIFIES SQL DATA
LANGUAGE SQL

BEGIN

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

OPEN C1;

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

CALL DB2INST1.DEPT_E MP ('A00')

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

EMPNO
------
000010
000110
000120

3 record(s) selected.

Return Status = 0

[patrick.cc.choi@gmail.com]

Re: DB2 cursors in stored procedures with parameters?

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_E MP (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:

<patrick.cc.cho i@gmail.comwrot e in message
news:1165549360 .271100.199970@ n67g2000cwd.goo glegroups.com.. .

Hi all,

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

cursor myCursor(name_c ity 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_E MP
(IN IN_DEPT CHAR(3))
>
RESULT SETS 1
MODIFIES SQL DATA
LANGUAGE SQL
>
BEGIN
>
DECLARE C1 CURSOR WITH RETURN FOR
SELECT EMPNO
FROM DB2INST1.EMPLOY EE
WHERE WORKDEPT = IN_DEPT;
>
OPEN C1;
>
END
-----------------------------------------------------
>
CALL DB2INST1.DEPT_E MP ('A00')
>
Result set 1
--------------
>
EMPNO
------
000010
000110
000120
>
3 record(s) selected.
>
Return Status = 0

[Mark A]

Re: DB2 cursors in stored procedures with parameters?

<patrick.cc.cho i@gmail.comwrot e in message
news:1165551129 .497657.114600@ 16g2000cwy.goog legroups.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_E MP (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@gmail.com]

Re: DB2 cursors in stored procedures with parameters?

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:

<patrick.cc.cho i@gmail.comwrot e in message
news:1165551129 .497657.114600@ 16g2000cwy.goog legroups.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_E MP (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]

Re: DB2 cursors in stored procedures with parameters?

patrick.cc.choi @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/Forum2006/Forum2006.html

[Patrick]

Re: DB2 cursors in stored procedures with parameters?

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:

patrick.cc.choi @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]

Re: DB2 cursors in stored procedures with parameters?

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/Forum2006/Forum2006.html