How to return rows using procedure?

2985493

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

Frank Kulash

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.

Frank Kulash

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

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

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

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

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