Использование выражения PIPELINED в PL/SQL.
Basically, when you would like a PLSQL (or java or c) routine to be the «source»
of data, instead of a table ,you would use a pipelined function.
Oracle Database then returns rows as they are produced by the function.
If you specify the keyword PIPELINED alone ( PIPELINED IS ...), then the PL/SQL function body should use the PIPE keyword. This keyword instructs the database to return single elements of the collection out of the function, instead of returning the whole collection as a single value.
- You can specify the
PIPELINED USING implementation_typeclause if you want to predefine an interface containing the start, fetch, and close operations. The implementation type must implement theODCITable interface and must exist at the time the table function is created. This clause is useful for table functions that will be implemented in external languages such as C++ and Java. - If the return type of the function is ANYDATASET, then you must also define a describe method(ODCITableDescribe)as part of the implementation type of the function.
Синтаксис:
CREATE OR REPLACE FUNCTION <pipelined_function_name>[(<function arguments>)]
RETURN <collection type> PIPELINED
RETURN <collection type> PIPELINED
IS
[(<variables declaration>)];
[(<variables declaration>)];
BEGIN
<.... LOOP>
[<some_actions>]
PIPE ROW (<cast to collection data_type>);
<.... END LOOP;>
RETURN;
END;
PIPELINED functions will operate like a table:
SELECT * FROM TABLE(<pipelined_function_name>[(<function arguments>)]);
Пример: - Generating Some Random employees from hr.employees table:
CREATE OR REPLACE TYPE o_EName AS OBJECT (
FirstName VARCHAR2(20),
LastName VARCHAR2(20)
);
/
FirstName VARCHAR2(20),
LastName VARCHAR2(20)
);
/
CREATE OR REPLACE TYPE t_ENames AS TABLE OF o_EName;
/
/
CREATE OR REPLACE FUNCTION GetRandEmployees (pEmpCount NUMBER)
RETURN t_ENames PIPELINED
IS
CURSOR c_Emps IS
RETURN t_ENames PIPELINED
IS
CURSOR c_Emps IS
SELECT First_Name,Last_Name FROM
( SELECT E.First_Name,E.Last_Name
FROM HR.Employees E
ORDER BY DBMS_RANDOM.VALUE)
WHERE ROWNUM <=pEmpCount;
BEGIN
( SELECT E.First_Name,E.Last_Name
FROM HR.Employees E
ORDER BY DBMS_RANDOM.VALUE)
WHERE ROWNUM <=pEmpCount;
BEGIN
FOR c IN c_Emps LOOP
PIPE ROW (o_EName(c.First_Name,c.Last_Name));
END LOOP;
RETURN;
END;
/
SELECT * FROM TABLE(GetRandEmployees(6));
/
SELECT * FROM TABLE(GetRandEmployees(6));
Результат:
| FIRSTNAME | LASTNAME |
|---|---|
| Alexis | Bull |
| Guy | Himuro |
| Tayler | Fox |
| Mozhe | Atkinson |
| Jean | Fleaur |
| Clara | Vishney |
