1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68
|
#1
SQL> REM instead of putting a type in a spec, do this:
SQL>
SQL> create or replace type myTableType as table of number;
2 /
Type created.
SQL>
SQL> REM here is an example of selecting from a local variable that is a pl/sql
SQL> REM table filled in at run time.
SQL>
SQL> declare
2 l_x myTableType := myTableType( 1, 2, 3, 4, 5, 6, 7, 8, 9 );
3
3 begin
4 for x in ( select *
5 from all_users
6 where user_id in
7 ( select *
8 from THE ( select cast( l_x as mytableType )
9 from dual ) a ) )
10 loop
11 dbms_output.put_line( x.username );
12 end loop;
13 end;
14 /
SYSTEM
PL/SQL procedure successfully completed.
SQL>
SQL> REM now, we want to "select * from PLSQL_FUNCTION()" not from a table:
SQL> REM this shows how to 'insert into VAR select' as well...
SQL>
SQL> create or replace function getMyTableType return myTableType
2 as
3 l_x myTableType;
4 begin
5 select cast( multiset( select user_id from all_users where rownum <
10 )
6 AS myTableType )
7 into l_x
8 from dual;
9
9 return l_x;
10 end;
11 /
Function created.
SQL>
SQL>
SQL> REM here we go... selecting from it:
SQL>
SQL> select * from all_users where user_id in
2 ( select *
3 from THE ( select cast( getMyTableType() as mytableType )
4 from dual ) a
5 )
6 / |
Partager