The PIPE ROW statement, which can appear only in the body of a pipelined table function, returns a table row (but not control) to the invoker of the function. A pipelined table function is declared with the option "PIPELINED".
CREATE OR REPLACE PACKAGE pkg1 AS TYPE numset_t IS TABLE OF NUMBER; FUNCTION f1(x NUMBER) RETURN numset_t PIPELINED; END pkg1; /
CREATE PACKAGE BODY pkg1 AS -- FUNCTION f1 returns a collection of elements (1,2,3,... x) FUNCTION f1(x NUMBER) RETURN numset_t PIPELINED IS BEGIN FOR i IN 1..x LOOP PIPE ROW(i); END LOOP; RETURN; END f1; END pkg1; /
Invoke pipelined table function in SELECT statement:
Have you done any similar study on migration from Sybase to other databases like Oracle, PostGres, MySQL, etc.? Or do you have any pointers / references that I can look for?
The PIPE ROW statement, which can appear only in the body of a pipelined table function, returns a table row (but not control) to the invoker of the function. A pipelined table function is declared with the option "PIPELINED".
ReplyDeleteCREATE OR REPLACE PACKAGE pkg1 AS
TYPE numset_t IS TABLE OF NUMBER;
FUNCTION f1(x NUMBER) RETURN numset_t PIPELINED;
END pkg1;
/
CREATE PACKAGE BODY pkg1 AS
-- FUNCTION f1 returns a collection of elements (1,2,3,... x)
FUNCTION f1(x NUMBER) RETURN numset_t PIPELINED IS
BEGIN
FOR i IN 1..x LOOP
PIPE ROW(i);
END LOOP;
RETURN;
END f1;
END pkg1;
/
Invoke pipelined table function in SELECT statement:
SELECT * FROM TABLE(pkg1.f1(5));
Result:
COLUMN_VALUE
------------
1
2
3
4
5
5 rows selected.
$---------System Parameter/System Variable
ReplyDelete$$-------Mapping Parameter/Mapping Variable
$$$------Global variable /Global Parameter
Have you done any similar study on migration from Sybase to other databases like Oracle, PostGres, MySQL, etc.? Or do you have any pointers / references that I can look for?
ReplyDelete