Discussions
Categories
- 196.7K All Categories
- 2.2K Data
- 235 Big Data Appliance
- 1.9K Data Science
- 449.8K Databases
- 221.5K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 549 MySQL Community Space
- 477 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3K ORDS, SODA & JSON in the Database
- 532 SQLcl
- 4K SQL Developer Data Modeler
- 186.8K SQL & PL/SQL
- 21.3K SQL Developer
- 295.4K Development
- 17 Developer Projects
- 138 Programming Languages
- 292.1K Development Tools
- 104 DevOps
- 3.1K QA/Testing
- 645.9K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 153 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.1K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 17 Java Essentials
- 158 Java 8 Questions
- 85.9K Java Programming
- 79 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.2K Java SE
- 13.8K Java Security
- 203 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 394 LiveLabs
- 37 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.6K Other Languages
- 2.3K Chinese
- 170 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 230 Portuguese
How to return rows using procedure?

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!!
Best 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
-
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.
-
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.
-
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!
-
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.
-
Thank you, I will learn this and have a try.