Databases Reference
In-Depth Information
8 dbms_lock.sleep (1);
9 pipe row (to_char (sysdate, 'hh24:mi:ss'));
10 end loop;
11 return;
12 end show_time;
13 /
Function created.
This function is returning a list of ive time information outputs, each with a second
inbetween. Line 8 takes care of the one-second pause, using
DBMS_LOCK.SLEEP
. In the
Return
clause of the function signature, is the
PIPELINED
keyword (line 3). On line 9,
the values are returned from the function.
PIPE ROW
is the key to return the values,
in our case the time component of
SYSDATE
.
Finally,
RETURN
on line 11 signals the end of the function and returns control to the
calling function.
Now we can see the power of the pipelined table function in action, used in the
from
clause of the following query:
SQL> select to_char (sysdate, 'hh24:mi:ss')
2 , column_value
3 from table (show_time)
4 /
TO_CHAR( COLUMN_VAL
-------- ----------
21:49:13 21:49:14
21:49:13 21:49:15
21:49:13 21:49:16
21:49:13 21:49:17
21:49:13 21:49:18
In this query, we have shown the current time, as well as the results from the
pipelined table function that we created earlier. On line 3, the pipelined table
function is called, inside the
table
operator, in the
from
clause. As you can see
from the output, the value for the current time is determined at the start of the
query (read consistency in action) and is ixed at
21:49:13
. The pipelined table
function returns its values with one second pause in between. So, the rows are
returned—not all at the same time, but one after the other—until the function is
completely inished.
