|
SQL>
SQL>
SQL> create table myTable1 as select * from all_objects where rownum < 50;
Table created.
SQL> create table myTable2 as select * from all_objects where rownum <= 50;
Table created.
SQL>
SQL> alter table myTable1 add constraint myTable1_pk primary key(object_id);
Table altered.
SQL> alter table myTable2 add constraint myTable2_pk primary key(object_id);
Table altered.
SQL>
SQL> analyze table myTable1 compute statistics
2 for table for all indexes for all indexed columns;
Table analyzed.
SQL>
SQL> analyze table myTable2 compute statistics
2 for table for all indexes for all indexed columns;
Table analyzed.
SQL>
SQL> create or replace function get_data( p_object_id in number ) return varchar2
2 is
3 l_object_name myTable2.object_name%type;
4 begin
5 select object_name into l_object_name
6 from myTable2
7 where object_id = p_object_id;
8 return l_object_name;
9 exception
10 when no_data_found then
11 return NULL;
12 end;
13 /
Function created.
SQL>
SQL> select a.object_id, a.object_name oname1, b.object_name oname2
2 from myTable1 a, myTable2 b
3 where a.object_id = b.object_id(+);
OBJECT_ID ONAME1 ONAME2
---------- ------------------------------ ------------------------------
20 ICOL$ ICOL$
44 I_USER1 I_USER1
28 CON$ CON$
15 UNDO$ UNDO$
29 C_COBJ# C_COBJ#
3 I_OBJ# I_OBJ#
25 PROXY_ROLE_DATA$ PROXY_ROLE_DATA$
39 I_IND1 I_IND1
51 I_CDEF2 I_CDEF2
26 I_PROXY_ROLE_DATA$_1 I_PROXY_ROLE_DATA$_1
17 FILE$ FILE$
OBJECT_ID ONAME1 ONAME2
---------- ------------------------------ ------------------------------
13 UET$ UET$
9 I_FILE#_BLOCK# I_FILE#_BLOCK#
41 I_FILE1 I_FILE1
48 I_CON1 I_CON1
38 I_OBJ3 I_OBJ3
7 I_TS# I_TS#
53 I_CDEF4 I_CDEF4
19 IND$ IND$
14 SEG$ SEG$
6 C_TS# C_TS#
42 I_FILE2 I_FILE2
OBJECT_ID ONAME1 ONAME2
---------- ------------------------------ ------------------------------
21 COL$ COL$
43 I_TS1 I_TS1
35 I_UNDO2 I_UNDO2
5 CLU$ CLU$
23 PROXY_DATA$ PROXY_DATA$
24 I_PROXY_DATA$ I_PROXY_DATA$
36 I_OBJ1 I_OBJ1
46 I_COL2 I_COL2
37 I_OBJ2 I_OBJ2
54 I_CCOL1 I_CCOL1
16 TS$ TS$
OBJECT_ID ONAME1 ONAME2
---------- ------------------------------ ------------------------------
8 C_FILE#_BLOCK# C_FILE#_BLOCK#
10 C_USER# C_USER#
34 I_UNDO1 I_UNDO1
56 BOOTSTRAP$ BOOTSTRAP$
12 FET$ FET$
33 I_TAB1 I_TAB1
32 CCOL$ CCOL$
22 USER$ USER$
49 I_CON2 I_CON2
30 I_COBJ# I_COBJ#
18 OBJ$ OBJ$
OBJECT_ID ONAME1 ONAME2
---------- ------------------------------ ------------------------------
47 I_COL3 I_COL3
2 C_OBJ# C_OBJ#
4 TAB$ TAB$
31 CDEF$ CDEF$
50 I_CDEF1 I_CDEF1
49 rows selected.
SQL>
SQL> select object_id, object_name oname1, get_data(object_id) oname2
2 from myTable1;
OBJECT_ID ONAME1
---------- ------------------------------
ONAME2
--------------------------------------------------------------------------------
20 ICOL$
ICOL$
44 I_USER1
I_USER1
28 CON$
CON$
OBJECT_ID ONAME1
---------- ------------------------------
ONAME2
--------------------------------------------------------------------------------
15 UNDO$
UNDO$
29 C_COBJ#
C_COBJ#
3 I_OBJ#
I_OBJ#
OBJECT_ID ONAME1
---------- ------------------------------
ONAME2
--------------------------------------------------------------------------------
25 PROXY_ROLE_DATA$
PROXY_ROLE_DATA$
39 I_IND1
I_IND1
51 I_CDEF2
I_CDEF2
OBJECT_ID ONAME1
---------- ------------------------------
ONAME2
--------------------------------------------------------------------------------
26 I_PROXY_ROLE_DATA$_1
I_PROXY_ROLE_DATA$_1
17 FILE$
FILE$
13 UET$
UET$
OBJECT_ID ONAME1
---------- ------------------------------
ONAME2
--------------------------------------------------------------------------------
9 I_FILE#_BLOCK#
I_FILE#_BLOCK#
41 I_FILE1
I_FILE1
48 I_CON1
I_CON1
OBJECT_ID ONAME1
---------- ------------------------------
ONAME2
--------------------------------------------------------------------------------
38 I_OBJ3
I_OBJ3
7 I_TS#
I_TS#
53 I_CDEF4
I_CDEF4
OBJECT_ID ONAME1
---------- ------------------------------
ONAME2
--------------------------------------------------------------------------------
19 IND$
IND$
14 SEG$
SEG$
6 C_TS#
C_TS#
OBJECT_ID ONAME1
---------- ------------------------------
ONAME2
--------------------------------------------------------------------------------
42 I_FILE2
I_FILE2
21 COL$
COL$
43 I_TS1
I_TS1
OBJECT_ID ONAME1
---------- ------------------------------
ONAME2
--------------------------------------------------------------------------------
35 I_UNDO2
I_UNDO2
5 CLU$
CLU$
23 PROXY_DATA$
PROXY_DATA$
OBJECT_ID ONAME1
---------- ------------------------------
ONAME2
--------------------------------------------------------------------------------
24 I_PROXY_DATA$
I_PROXY_DATA$
36 I_OBJ1
I_OBJ1
46 I_COL2
I_COL2
OBJECT_ID ONAME1
---------- ------------------------------
ONAME2
--------------------------------------------------------------------------------
37 I_OBJ2
I_OBJ2
54 I_CCOL1
I_CCOL1
16 TS$
TS$
OBJECT_ID ONAME1
---------- ------------------------------
ONAME2
--------------------------------------------------------------------------------
8 C_FILE#_BLOCK#
C_FILE#_BLOCK#
10 C_USER#
C_USER#
34 I_UNDO1
I_UNDO1
OBJECT_ID ONAME1
---------- ------------------------------
ONAME2
--------------------------------------------------------------------------------
56 BOOTSTRAP$
BOOTSTRAP$
12 FET$
FET$
33 I_TAB1
I_TAB1
OBJECT_ID ONAME1
---------- ------------------------------
ONAME2
--------------------------------------------------------------------------------
32 CCOL$
CCOL$
22 USER$
USER$
49 I_CON2
I_CON2
OBJECT_ID ONAME1
---------- ------------------------------
ONAME2
--------------------------------------------------------------------------------
30 I_COBJ#
I_COBJ#
18 OBJ$
OBJ$
47 I_COL3
I_COL3
OBJECT_ID ONAME1
---------- ------------------------------
ONAME2
--------------------------------------------------------------------------------
2 C_OBJ#
C_OBJ#
4 TAB$
TAB$
31 CDEF$
CDEF$
OBJECT_ID ONAME1
---------- ------------------------------
ONAME2
--------------------------------------------------------------------------------
50 I_CDEF1
I_CDEF1
49 rows selected.
SQL>
SQL> begin
2
3 for x in ( select a.object_id,
4 a.object_name oname1,
5 b.object_name oname2
6 from myTable1 a, myTable2 b
7 where a.object_id = b.object_id(+) )
8 loop
9 null;
10 end loop;
11
12 for x in ( select object_id,
13 object_name oname1,
14 get_data(object_id) oname2
15 from myTable1 )
16 loop
17 null;
18 end loop;
19
20 end;
21 /
PL/SQL procedure successfully completed.
SQL>
SQL> drop table myTable1;
Table dropped.
SQL> drop table myTable2;
Table dropped.
SQL>
|