APEX

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.