Oracle複合資料型別樣本

--Recode資料類型declarev_deptinfo scott.dept%rowtype;type dept_record is RECORD(  v1 scott.dept.deptno%type,  v2 scott.dept.dname%type,  v3 scott.dept%rowtype--可以聲明ROWTYPE類型);v_deptrecord dept_record;begin--一下的賦值方式錯誤：因為V3是ROWTYPE類型，而查詢的一行記錄有五列，給v3賦值時會發生類型不符select deptno,dname,t.* into v_deptrecord from dept t where deptno=10;--解決方案：可以對v1,v2賦值後，再寫另外一條語句對v3賦值。dbms_output.put_line(v_deptrecord.v3.dname||' '||v_deptrecord.v3.deptno);end;
--索引表1declaretype my_index_table1 is table of scott.dept.dname%type--可以使任意資料類型，代表此索引表所儲存資料的類型。index by binary_integer;my1 my_index_table1;c number(2);beginselect count(*) into c from dept;for i in 1..c loop  select dname into my1(i) from    (select rownum rn,t.* from dept t) x     where x.rn=i;end loop;--每個集合都有COUNT屬性，代表此集合儲存的有效元素總個數。for i in 1..my1.count loop  dbms_output.put_line(my1(i));end loop;end;
--索引表2declaretype my_index_table1 is table of scott.dept.dname%typeindex by varchar2(20);--Oracle 9i以上的版本，索引表的下表可以為3中資料類型（BINARY_INTEGER、PLS_INTEGER、VVARCHAR2(length)）;my1 my_index_table1;beginselect loc into my1('南昌') from dept where deptno=10;dbms_output.put_line(my1('南昌'));end;
--巢狀表格1declaretype my_index_table1 is table of scott.dept.dname%type;my1 my_index_table1:=my_index_table1(null,null,null,null);--初始化可以使用null值beginselect dname into my1(1) from dept where deptno=10;select dname into my1(2) from dept where deptno=20;select dname into my1(3) from dept where deptno=30;select dname into my1(4) from dept where deptno=40;--巢狀表格刪除元素後，下標依然存在，依然可以重新進行賦值.my1.delete(3);dbms_output.put_line(my1.count);select dname into my1(3) from dept where deptno=30;dbms_output.put_line(my1.count);for i in 1..my1.count loop  dbms_output.put_line(my1(i));end loop;end;
--巢狀表格2create type phone_type is table of varchar2(20);create table employee (   eid number(4),   ename varchar2(10),   phone phone_type) nested table phone store as phone_table;
insert into employeevalues(1,'xx',phone_type('0791-111','123454545'));
insert into employeevalues(2,'xx',phone_type('0791-111','123454545','saaasf'));--變長數組declaretype my_index_table1 is varray(3) of scott.dept.dname%type;my1 my_index_table1:=my_index_table1('a','b','c');--初始化beginselect dname into my1(1) from dept where deptno=10;select dname into my1(3) from dept where deptno=20;for i in 1..my1.count loop  dbms_output.put_line(my1(i));end loop;end;
--記錄表2declare--自訂RECORD可以存放自己想要的列，脫離了ＲＯＷＴＹＰＥ的死板，可以靈活的自訂存放哪些列。type dept_record is RECORD(  v1 scott.dept.deptno%type,  v2 scott.dept.dname%type,  v3 scott.dept.loc%type);type my_index_table1 is table of dept_recordindex by binary_integer;my1 my_index_table1;c number(2);--查詢出dept表中的所有資料並放進自訂的資料類型begin-先查詢出表中的記錄總數，以記錄總數作為迴圈條件對dept表、以rownum作為WHERE條件對dept表進行逐條查詢並存貯進自訂資料類型select count(*) into c from dept;for i in 1..c loop  select x.deptno,x.dname,x.loc into my1(i) from    (select rownum rn,t.* from dept t) x     where x.rn=i;end loop;--迴圈輸出my1類型中的v2欄位在DEPT表中代表的資料；for i in 1..my1.count loop  dbms_output.put_line(my1(i).v2);end loop;end;

更多Oracle相關資訊見Oracle 專題頁面 http://www.bkjia.com/topicnews.aspx?tid=12