SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

How to return rows using procedure?

2985493Sep 26 2015 — edited Sep 26 2015

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

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

This post has been answered by Frank Kulash on Sep 26 2015

Jump to Answer

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

Answer

Hi,

2985493 wrote:

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

)

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.

Marked as Answer by 2985493 · Sep 27 2020

2985493

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

1 - 5

Locked Post

New comments cannot be posted to this locked post.

Locked on Oct 24 2015

Added on Sep 26 2015

#procedure

5 comments

380 views

SQL & PL/SQL

How to return rows using procedure?

Comments

Post Details