Is it possible to 'concatenate' tables in PL/SQL
916833Feb 26 2012 — edited Mar 3 2012Hello,
I am traversing a table recursively in a pl/sql function returning a table. I created the function which works but returning a VARCHAR2 now and I want to transform it returning a table.
My question is if that variable STR was a table how could that be implemented.
create or replace
FUNCTION TRAVERSE_STR(PART IN NUMBER)
RETURN
VARCHAR2
IS
STR VARCHAR2(4000);
TYPE PART_HAS_SUBPART_ROW IS TABLE OF PART_HAS_SUBPART%ROWTYPE INDEX BY BINARY_INTEGER;
PART_SUB_PART PART_HAS_SUBPART_ROW;
STATE NUMBER;
CURSOR CUR IS SELECT * FROM PART_HAS_SUBPART WHERE PART_ID=PART;
I BINARY_INTEGER := 0;
BEGIN
OPEN CUR;
LOOP
I := I+1;
FETCH CUR INTO PART_SUB_PART(I);
EXIT WHEN CUR%NOTFOUND;
SELECT IS_HAS_PART INTO STATE FROM PART WHERE ID = PART_SUB_PART(I).SUB_PART_ID;
IF STATE=0 THEN
STR := STR || PART_SUB_PART(I).PART_ID || ' ' ||PART_SUB_PART(I).SUB_PART_ID || ' ';
ELSE IF STATE=1 THEN
STR := str || TRAVERSE_STR(PART_SUB_PART(I).SUB_PART_ID) || ' ';
END IF;
END IF;
END LOOP;
CLOSE CUR;
RETURN STR;
END TRAVERSE_STR;
======================================
I don't know if it is clear but that is my variant with table:
create or replace
FUNCTION TRAVERSE_TBL(PART IN NUMBER)
RETURN
PART_HAS_SUBPART_TYPE
IS
TYPE PART_HAS_SUBPART_ROW IS TABLE OF PART_HAS_SUBPART%ROWTYPE INDEX BY BINARY_INTEGER;
PART_SUB_PART PART_HAS_SUBPART_ROW;
RETURN_TABLE PART_HAS_SUBPART_TYPE;
STATE NUMBER;
CURSOR CUR IS SELECT * FROM PART_HAS_SUBPART WHERE PART_ID=PART;
I BINARY_INTEGER := 0;
BEGIN
OPEN CUR;
LOOP
I := I+1;
FETCH CUR INTO PART_SUB_PART(I);
EXIT WHEN CUR%NOTFOUND;
SELECT IS_HAS_PART INTO STATE FROM PART WHERE ID = PART_SUB_PART(I).SUB_PART_ID;
IF STATE=0 THEN
RETURN_TABLE.EXTEND;
RETURN_TABLE(RETURN_TABLE.COUNT) := PART_ROW(PART_SUB_PART(I).PART_ID, PART_SUB_PART(I).SUB_PART_ID, PART_SUB_PART(I).AMOUNT);
ELSE IF STATE=1 THEN
RETURN_TABLE.EXTEND;
RETURNTABLE(RETURN_TABLE.COUNT) := SELECT TRAVERSE_TBL(PART_SUB_PART(I).SUB_PART_ID) FROM DUAL;_
END IF;
END IF;
END LOOP;
CLOSE CUR;
RETURN RETURN_TABLE;
END TRAVERSE_TBL;
Thank you in advance!
Edited by: Todor Kolev on Feb 26, 2012 7:46 PM