Forum Stats

  • 3,838,309 Users
  • 2,262,351 Discussions
  • 7,900,578 Comments

Discussions

How to return rows using procedure?

2985493
2985493 Member Posts: 10
edited Sep 26, 2015 11:10AM in SQL & PL/SQL

Example: There has 3 areas : A, B ,C , and each area has some persons.

create table Person (name varchar2(2), area varchar2(2));

insert into Person values('A1','A');  --row 1

insert into Person values('A2','A');  --row 2

insert into Person values('A3','A');  --row 3

insert into Person values('A4','A');  --row 4

insert into Person values('B1','B');  --row 5

insert into Person values('C1','C');  --row 6

insert into Person values('C2','C');  --row 7

result as following:

A   B   C

--------------

A1 B1 C1

A2      C2

A3

A4

If we "select * from Person", then we got all rows.

Now want to choose volunteers from every area, the rule is to choose a person from each area orderly, until N persons are chose. If N = 6, the sequence is

A1, B1, C1, A2, C2, A3

and want to return rows 1,5,6,2,7,3 (the rows' order is not important) from Person.

How to create a procedure to do this? And I wish the returned rows just like returned from a select sentence. (can be easily filled into a DataTable and then be used in a program.)

Thank you!!

Tagged:

Best Answer

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,230 Red Diamond
    edited Sep 26, 2015 11:04AM Answer ✓

    Hi,

    2985493 wrote:
    
    Thank you. Could you write a procedure to do the work? This is just an example, the actual chosing rule is complicated enough that has to use procedure and cursor. I wonder how to write a procedure returns result just like a select sentence.
    
    

    Here's one way:

    VARIABLE  c  REFCURSOR
    
    
    CREATE OR REPLACE PROCEDURE  top_by_area
    (    csr          OUT  SYS_REFCURSOR
    ,    n_to_return  IN   PLS_INTEGER   := 6
    )
    IS
    BEGIN
        OPEN  csr  FOR
            WITH    got_area_num    AS
            (
                SELECT    name, area
                FROM      person
                ORDER BY  ROW_NUMBER () OVER ( PARTITION BY  area
                                               ORDER BY       name
                                             )  
                , area
            )
            SELECT    name, area
            ,         ROWNUM     AS r_num
            FROM      got_area_num
            WHERE     ROWNUM  <= n_to_return
            ORDER BY  r_num
            ;
    END  top_by_area;
    /
    SHOW ERRORS
    
    
    EXEC  top_by_area (:c);
    
    
    PRINT  :c
    
    

    The output from the PRINT command given your sample data is:

    NAME AREA    R_NUM
    ---- ---- --------
    A1   A           1
    B1   B           2
    C1   C           3
    A2   A           4
    C2   C           5
    A3   A           6
    
    

    This is just one way of creating and testing the procedure.  I don't know how you plan to use it.

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,230 Red Diamond
    edited Sep 26, 2015 10:26AM

    Hi,

    This query gets the results you want:

    WITH    got_area_num    AS
    (
        SELECT    name, area
        ,         ROW_NUMBER () OVER ( PARTITION BY  area
                                       ORDER BY       name
                                     )   AS area_num
        FROM      person
        ORDER BY  area_num, area
    )
    SELECT    name, area
    ,         ROWNUM     AS r_num
    FROM      got_area_num
    WHERE     ROWNUM  <= 6
    ORDER BY  r_num
    ;
    
    
    

    It's unclear how you plan to use this in a procedure.  Maybe you just need a procedure (or a function) that opens a cursor like this.

  • 2985493
    2985493 Member Posts: 10
    edited Sep 26, 2015 10:41AM

    Thank you. Could you write a procedure to do the work? This is just an example, the actual chosing rule is complicated enough that has to use procedure and cursor. I wonder how to write a procedure returns result just like a select sentence.

  • 2985493
    2985493 Member Posts: 10
    edited Sep 26, 2015 10:53AM

    I am a new beginner from China. It is some difficult to find learning material of oracle procedure here.So could you just write a sample procedure for me to do this work? I can expand a simple procedure to a more complex one by myself if you give me the sample. Thank you!

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 42,230 Red Diamond
    edited Sep 26, 2015 11:04AM Answer ✓

    Hi,

    2985493 wrote:
    
    Thank you. Could you write a procedure to do the work? This is just an example, the actual chosing rule is complicated enough that has to use procedure and cursor. I wonder how to write a procedure returns result just like a select sentence.
    
    

    Here's one way:

    VARIABLE  c  REFCURSOR
    
    
    CREATE OR REPLACE PROCEDURE  top_by_area
    (    csr          OUT  SYS_REFCURSOR
    ,    n_to_return  IN   PLS_INTEGER   := 6
    )
    IS
    BEGIN
        OPEN  csr  FOR
            WITH    got_area_num    AS
            (
                SELECT    name, area
                FROM      person
                ORDER BY  ROW_NUMBER () OVER ( PARTITION BY  area
                                               ORDER BY       name
                                             )  
                , area
            )
            SELECT    name, area
            ,         ROWNUM     AS r_num
            FROM      got_area_num
            WHERE     ROWNUM  <= n_to_return
            ORDER BY  r_num
            ;
    END  top_by_area;
    /
    SHOW ERRORS
    
    
    EXEC  top_by_area (:c);
    
    
    PRINT  :c
    
    

    The output from the PRINT command given your sample data is:

    NAME AREA    R_NUM
    ---- ---- --------
    A1   A           1
    B1   B           2
    C1   C           3
    A2   A           4
    C2   C           5
    A3   A           6
    
    

    This is just one way of creating and testing the procedure.  I don't know how you plan to use it.

  • 2985493
    2985493 Member Posts: 10
    edited Sep 26, 2015 11:10AM

    Thank you, I will learn this and have a try.

This discussion has been closed.