DanBrother的部落格

The first example demonstrates a way to joining a PL/SQL table with a normal table using built-in data type.

create table student_table
(student_name varchar2(30) primary key,
score number(3)
);

SQL> desc student_table
名稱 空值? 類型
----------------------------------------------------------------- -------- ---------------
STUDENT_NAME NOT NULL VARCHAR2(30)
SCORE NUMBER(3)

-- GENERATE DATA
begin
for i in 1..10 loop
insert into student_table(student_name,score)
values('Name'||lpad(i,3,0),to_number(trunc(dbms_random.value('60','101'))));
end loop;
commit;
end;
/

col student_name format a20
select * from student_table order by score desc;
>>
STUDENT_NAME SCORE
-------------------- ----------
Name008 100
Name009 99
Name006 99
Name001 87
Name004 84
Name002 83
Name005 81
Name010 70
Name003 68
Name007 66

create type my_student_name_tab is table of varchar2(30);
/

create type my_student_score_tab is table of number(3);
/

col type_name format a20
col typecode format a20
col instantiable format a12
select type_name,typecode,attributes,instantiable from user_types;
>>
TYPE_NAME                          TYPECODE             ATTRIBUTES      INSTANTIABLE
-----------------------------    -------------------- ----------------- ------------------------
MY_STUDENT_NAME_TAB    COLLECTION         0                        YES
MY_STUDENT_SCORE_TAB  COLLECTION         0                        YES

declare
tmp_student_name_tab my_student_name_tab:=my_student_name_tab();
student_name_tab my_student_name_tab:=my_student_name_tab();
student_score_tab my_student_score_tab:=my_student_score_tab();
begin
tmp_student_name_tab.EXTEND(3);
tmp_student_name_tab(1):='Name008';
tmp_student_name_tab(2):='Name009';
tmp_student_name_tab(3):='Name006';

select a.column_value,b.score
bulk collect into student_name_tab,student_score_tab
from table(cast(tmp_student_name_tab as my_student_name_tab)) a, student_table b
where a.column_value = b.student_name;

for i in student_name_tab.FIRST .. student_name_tab.LAST loop
dbms_output.put_line(student_name_tab(i) || ' got score of '||student_score_tab(i)||'.');
end loop;
end;
/

Name008 got score of 100.
Name009 got score of 99.
Name006 got score of 99.

###################################################################
###################################################################
###################################################################

The second example demonstrates a way to joining a nested table with a normal table using built-in data type

create table student_table
(name varchar2(30) primary key,
score number(3),
gender char(1)
);

名稱 空值? 類型
----------------------------------------------------------------- -------- -------------
NAME NOT NULL VARCHAR2(30)
SCORE NUMBER(3)
GENDER CHAR(1)

-- GENERATE DATA
declare
v_gender char(1);
begin
for i in 1..10 loop
if i <=5 then
v_gender:='M';
else
v_gender:='F';
end if;
insert into student_table(name,score,gender)
values('Name'||lpad(i,3,0),to_number(trunc(dbms_random.value('60','101'))),v_gender);
end loop;
commit;
end;
/

col name format a14
col gender format a6
select * from student_table order by score desc;
>>
NAME SCORE GENDER
-------------- ---------- -------------
Name001 98 M
Name002 87 M
Name007 81 F
Name010 75 F
Name003 73 M
Name005 69 M
Name009 65 F
Name008 62 F
Name004 61 M
Name006 61 F

create type MY_STUDENT_OBJ is object
(name varchar2(30),
score number(3),
gender char(1)
);
/

create type MY_STUDENT_TAB is table of MY_STUDENT_OBJ;
/

SQL> desc MY_STUDENT_TAB
MY_STUDENT_TAB TABLE OF MY_STUDENT_OBJ
名稱                                   空值?     類型
-----------------------  -------- ------------------
NAME                                         VARCHAR2(30)
SCORE                                        NUMBER(3)
GENDER                                      CHAR(1)

col type_name format a20
col typecode format a20
col instantiable format a12
select type_name,typecode,attributes,instantiable from user_types;
>>
TYPE_NAME              TYPECODE              ATTRIBUTES   INSTANTIABLE
---------------------- -------------------- ----------------- -----------------
MY_STUDENT_OBJ  OBJECT                      3                     YES
MY_STUDENT_TAB  COLLECTION              0                    YES

declare
tmp_student_tab MY_STUDENT_TAB:=MY_STUDENT_TAB();
student_tab MY_STUDENT_TAB:=MY_STUDENT_TAB();

begin
tmp_student_tab.EXTEND(3);
tmp_student_tab(1):=MY_STUDENT_OBJ('Name001',0,'');
tmp_student_tab(2):=MY_STUDENT_OBJ('Name002',0,'');
tmp_student_tab(3):=MY_STUDENT_OBJ('Name007',0,'');


select MY_STUDENT_OBJ(b.name,b.score,b.gender)
bulk collect into student_tab
from table(cast(tmp_student_tab as MY_STUDENT_TAB)) a, student_table b
where a.name = b.name;

for i in student_tab.FIRST .. student_tab.LAST loop
dbms_output.put_line(student_tab(i).gender||':'||student_tab(i).name || ' got score of '||student_tab(i).score||'.');
end loop;
end;
/

M:Name001 got score of 98.
M:Name002 got score of 87.
F:Name007 got score of 81.