Home » SQL & PL/SQL » SQL & PL/SQL » Filling a table of object in function loop (11.0.2.10)
Filling a table of object in function loop [message #657523] |
Sun, 13 November 2016 03:49 |
|
OraFerro
Messages: 433 Registered: July 2011
|
Senior Member |
|
|
Hi all,
I have a function that returns a table of object, this function takes a parameter (id)
and returns a table of a single row.
I need to use this same function to return the same table for more than one row.
I created another function that calls the sigle-row function in a loop to fill the table of object,
still the return is one row!
( I tried reading more about FORALL and different BULK COLLECT techniques but it seems that either
I am missing the hole concept or I am doing a mistake... or both!
I appreciate your usual help or advice regarding the approach in general (re-using the function in a loop)
My example:
CREATE TABLE TEST_CASE
(
ID NUMBER,
NAME1 VARCHAR(10),
VALUE1 NUMBER
);
INSERT ALL
INTO TEST_CASE VALUES (1, 'TTT', 77)
INTO TEST_CASE VALUES (2, 'RRR', 737)
INTO TEST_CASE VALUES (3, 'TT', 477)
INTO TEST_CASE VALUES (4, 'HHH', 775)
INTO TEST_CASE VALUES (5, 'TFFFTT', 66)
INTO TEST_CASE VALUES (6, 'III', 18)
SELECT * FROM DUAL;
CREATE OR REPLACE TYPE OBJ_TEST_CASE AS OBJECT
(
ID NUMBER,
NAME1 VARCHAR(10),
VALUE1 NUMBER
);
CREATE TYPE TBL_TEST_CASE AS
TABLE OF OBJ_TEST_CASE;
-- A function that get the result for a single id as a table
CREATE OR REPLACE FUNCTION F_TEST_CASE
(
I_ID IN NUMBER
)
RETURN TBL_TEST_CASE AS
AGR_ACCOUNT_STATEMENT TBL_TEST_CASE;
BEGIN
SELECT OBJ_TEST_CASE(
ID ,
NAME1,
VALUE1
)
BULK COLLECT INTO AGR_ACCOUNT_STATEMENT
FROM TEST_CASE WHERE ID = I_ID;
RETURN (AGR_ACCOUNT_STATEMENT);
END ;
-- an array to hold a list of rows
CREATE OR REPLACE TYPE ARY_AGREEMENT AS VARRAY(200) OF NUMBER;
-- Using the single value function to return a table for many rows
CREATE OR REPLACE FUNCTION F_TEST_CASE_ALL(MYMAXVALUE IN NUMBER)
RETURN TBL_TEST_CASE AS
AGR_ACCOUNT_STATEMENT TBL_TEST_CASE;
VARY_AGREEMENT ARY_AGREEMENT:= ARY_AGREEMENT();
BEGIN
SELECT ID BULK COLLECT INTO VARY_AGREEMENT FROM TEST_CASE
WHERE VALUE1 > MYMAXVALUE
;
FOR I IN 1..VARY_AGREEMENT.COUNT LOOP
SELECT OBJ_TEST_CASE(
ID ,
NAME1,
VALUE1
)
BULK COLLECT INTO AGR_ACCOUNT_STATEMENT
FROM TABLE(F_TEST_CASE(VARY_AGREEMENT(I)));
END LOOP;
RETURN AGR_ACCOUNT_STATEMENT;
END;
SELECT * FROM TABLE(F_TEST_CASE_ALL(18));
--still a single row (the last row) is returned not many! what am I doing wrong?
Many Thanks,
Ferro
|
|
|
Re: Filling a table of object in function loop [message #657524 is a reply to message #657523] |
Sun, 13 November 2016 04:10 |
|
Michel Cadot
Messages: 68693 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
You have a single row because you return a single object.
Your approach is wrong, you should return a REF CURSOR not a collection.
SQL> CREATE OR REPLACE FUNCTION F_TEST_CASE_ALL(MYMAXVALUE IN NUMBER)
2 RETURN sys_refcursor AS
3 c sys_refcursor;
4 BEGIN
5 open c for select id, name1, value1 from TEST_CASE where VALUE1 > MYMAXVALUE;
6 return c;
7 END;
8 /
Function created.
SQL> var c refcursor
SQL> exec :c := F_TEST_CASE_ALL(18);
PL/SQL procedure successfully completed.
SQL> print c
ID NAME1 VALUE1
---------- ---------- ----------
1 TTT 77
2 RRR 737
3 TT 477
4 HHH 775
5 TFFFTT 66
5 rows selected.
or (more difficult to handle):
SQL> select F_TEST_CASE_ALL(18) from dual;
F_TEST_CASE_ALL(18)
--------------------
CURSOR STATEMENT : 1
CURSOR STATEMENT : 1
ID NAME1 VALUE1
---------- ---------- ----------
1 TTT 77
2 RRR 737
3 TT 477
4 HHH 775
5 TFFFTT 66
5 rows selected.
1 row selected.
The alternative is to use a pipelined function.
SQL> CREATE OR REPLACE TYPE OBJ_TEST_CASE AS OBJECT
2 (
3 ID NUMBER,
4 NAME1 VARCHAR(10),
5 VALUE1 NUMBER
6 );
7 /
Type created.
SQL> CREATE TYPE TBL_TEST_CASE AS
2 TABLE OF OBJ_TEST_CASE;
3 /
Type created.
SQL> CREATE OR REPLACE FUNCTION F_TEST_CASE_ALL(MYMAXVALUE IN NUMBER)
2 RETURN TBL_TEST_CASE pipelined AS
3 BEGIN
4 for rec in (
5 select id, name1, value1 from TEST_CASE where VALUE1 > MYMAXVALUE
6 ) loop
7 pipe row (OBJ_TEST_CASE(rec.id, rec.name1, rec.value1));
8 end loop;
9 END;
10 /
Function created.
SQL> select * from TABLE(F_TEST_CASE_ALL(18));
ID NAME1 VALUE1
---------- ---------- ----------
1 TTT 77
2 RRR 737
3 TT 477
4 HHH 775
5 TFFFTT 66
5 rows selected.
|
|
|
|
|
|
Re: Filling a table of object in function loop [message #657548 is a reply to message #657528] |
Sun, 13 November 2016 23:11 |
|
OraFerro
Messages: 433 Registered: July 2011
|
Senior Member |
|
|
Hi Michel,
I would like to hear your technical advice regarding why I chose to call the single-row function.
I have an accounting statement that has 3 main parts (header row, body rows, and virtual balance rows) and a final query that combines all rows and does some summary and calculations.
I developed a single function to cover each of the 3 parts. For example the function that covers the header row, depends on several queries, each of them can take either an id of a single account or use IN CLAUSE for a list of accounts. In order not to repeat this clause more than once and affect query readability, I chose to write the function for a single row, and then call it in a loop to get rows for all needed accounts.
I hope I was clear, and appreciate you technical advice as always.
Thanks,
Ferro
|
|
|
|
|
Goto Forum:
Current Time: Sat Sep 28 14:05:40 CDT 2024
|