DB2 cursors in stored procedures with parameters?

Collapse
This topic is closed.
X
X
 
  • Time
  • Show
Clear All
new posts
  • patrick.cc.choi@gmail.com

    DB2 cursors in stored procedures with parameters?

    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

    #2
    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




    Comment

    • patrick.cc.choi@gmail.com

      #3
      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

      Comment

      • Mark A

        #4
        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.


        Comment

        • patrick.cc.choi@gmail.com

          #5
          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.

          Comment

          • Serge Rielau

            #6
            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

            Comment

            • Patrick

              #7
              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

              Comment

              • Serge Rielau

                #8
                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

                Comment

                Working...