6 Replies Latest reply: Nov 15, 2012 11:46 PM by Billy~Verreynne RSS

    Smarter way to make a cursor of record id's

    MrGibbage
      I need to run a query that runs a stored procedure. The stored procedure takes a cursor of record ids as a parameter. I've been making the cursor like this, but I was wondering if there was a smarter way. I can't change the stored procedure, so this is the only thing I have control over.
      CURSOR id_cur
      IS
       SELECT id
       FROM table t
       WHERE 
      t.id IN (
      id1,
      id2,
      ...
      id500
      );
      It just seems kind of strange to select only ids from records when the list of ids is given.
        • 1. Re: Smarter way to make a cursor of record id's
          BluShadow
          We need more information about what you are ultimately trying to achieve.

          Based on the small bit of information you've given, it would seem pointless to select the id's of a table, based on a list of known id's, just to get that same list of id's from the cursor to be able to pass them elsewhere.

          But without seeing more of what you are trying to do, it's difficult to know what would actually be the right answer.

          {message:id=9360002}
          • 2. Re: Smarter way to make a cursor of record id's
            Peter vd Zwan
            Hi,

            As said already you should give more info.

            But if I give it a try anyway:
            --Your sample table
            create table t as
             SELECT 1 id FROM dual union all
             SELECT 2 id FROM dual union all
             SELECT 3 id FROM dual union all
             SELECT 4 id FROM dual 
            ;
            select * from t;
            
            ID
            --
             1 
             2 
             3 
             4 
            
            -- A sample function with a cursor parameter
            CREATE OR REPLACE
              FUNCTION test_cursor(
                  in_cur sys_refcursor )
                RETURN NUMBER
              AS
                n_sum NUMBER := 0;
                cur_id t.id%type;
              BEGIN
                LOOP
                  FETCH
                    in_cur
                  INTO
                    cur_id;
                  EXIT WHEN in_cur%notfound;
                  n_sum := n_sum + cur_id;
                END LOOP;
                RETURN n_sum;
              END;
            /
            
            --Test function
            select
              test_cursor( cursor(select * from t)) test_sum
            
            from
              dual
            ;
            TEST_SUM
            --------
                  10
            
            select
              test_cursor( cursor(select * from t where id in (1,3))) test_sum
            
            from
              dual
            ;
            TEST_SUM
            --------
                   4
            
            -- test function without the use of table t
            select
              test_cursor( cursor(select * from table(sys.OdciNumberList(1,3)))) test_sum
            
            from
              dual
            ;
            TEST_SUM
            --------
                   4
            Regards,

            Peter
            • 3. Re: Smarter way to make a cursor of record id's
              MrGibbage
              Well, there's really not that much more to tell.
              OPEN id_cur;
              
              FETCH id_cur INTO r_id_cur;
              
              WHILE id_cur%FOUND
              LOOP
                 BEGIN
                 -- DO STUFF THAT TAKES A t.id AS A PARAMETER
                 END;
              END LOOP;
              The bottom line, all I need to do is create a cursor with a bunch of t.id's that I already know in advance of running the query. So my question is still, is there an easier way (by easier, I mean more elegant, or better performance)? I suppose that since the table is indexed on the id, it should be a pretty low-impact query on the server, right? I'm sure the stuff in the loop is much MUCH more intensive on the server.
              • 4. Re: Smarter way to make a cursor of record id's
                Peter vd Zwan
                Hi,

                >
                The bottom line, all I need to do is create a cursor with a bunch of t.id's that I already know in advance of running the query. So my question is still, is there an easier way (by easier, I mean more elegant, or better performance)? I suppose that since the table is indexed on the id, it should be a pretty low-impact query on the server, right? I'm sure the stuff in the loop is much MUCH more intensive on the server.
                >

                As I showed already you don't need to query the table if you know the id's already. Below is a way (again) how to do that:
                -- test function without the use of table t
                select
                  test_cursor( cursor(select * from table(sys.OdciNumberList(1,3)))) test_sum
                 
                from
                  dual
                ;
                TEST_SUM
                --------
                       4
                Regards

                Peter
                • 5. Re: Smarter way to make a cursor of record id's
                  Billy~Verreynne
                  MrGibbage wrote:

                  It just seems kind of strange to select only ids from records when the list of ids is given.
                  Strange? I think the term idiotic describes it better.

                  Such a cursor is all wrong and useless - is not how one approaches and solves SQL problems in a database.

                  500 id variables in PL/SQL? That is also idiotic. And not how one approaches and solves problems in ANY programming language. PL/SQL included.

                  I suggest having a strong word with the person who wrote this trash. Not only does the person lack SQL skills, but also basic programming skills are severely lacking.
                  • 6. Re: Smarter way to make a cursor of record id's
                    Billy~Verreynne
                    MrGibbage wrote:

                    The bottom line, all I need to do is create a cursor with a bunch of t.id's that I already know in advance of running the query. So my question is still, is there an easier way (by easier, I mean more elegant, or better performance)? I suppose that since the table is indexed on the id, it should be a pretty low-impact query on the server, right? I'm sure the stuff in the loop is much MUCH more intensive on the server.
                    The correct relational data model approach for this type of scenario is as follows.

                    The 500 id's required (from many 1000's I assume), are required for a specific business grouping purpose. This requirement needs to be addressed in the data model by a lookup/reference table. E.g.
                    // relation defining the groups
                    GROUPS = ( group_id(pk), group_name, group_description)
                    
                    // maps an id to a group
                    GROUP_CODES = ( group_id(pk), code_id(pk) )
                    So for example you will have group Foo defined as group id 123, and have 500 code identifiers mapped to this group.

                    To use these 500 ids of the Foo group now means that you will join (standard relational operation) the GROUP_CODES table with the relevant table from which you need to select and process rows that matches an identifier in the list of 500 ids.

                    E.g. (assuming the client/calling layer determined the relevant group id)
                    select
                      t.*
                    from table1 t
                         group_codes c
                    where t1.id = c.code_id
                    and   c.group_id = 123
                    and .. additional filter criteria as applicable..
                    Of course, bind variables will be used when this SQL is coded into production code.

                    Any deviation of the basic relational design principle behind this approach - and you need to ask some serious questions and demand justification for this deviation.