table类型的一个小例子

--table类型的一个小例子

--表结构
create table STUDESC
(
ID VARCHAR2(10),
NAME VARCHAR2(20),
DESCR VARCHAR2(20)
)
create table STUDENT
(
ID VARCHAR2(10) not null,
NAME VARCHAR2(30),
LEADER VARCHAR2(10)
)

--创建记录类型

create or replace type t_test as object(
id VARCHAR(10),
DESCR varchar2(60)
)

--创建table类型

create or replace type t_test_table as table of t_test

--创建返回table类型的函数

create or replace function ZHSQ(IDS IN VARCHAR2, DESCRS VARCHAR2)
return t_test_table is
v_test t_test_table := t_test_table();
STR1 VARCHAR2(100);
STR2 VARCHAR2(100);
IDX1 INTEGER;
IDX2 INTEGER;
begin
STR1 := REPLACE(REPLACE(IDS, ')', ''), '(', '') || ',';
STR2 := REPLACE(REPLACE(DESCRS, ')', ''), '(', '') || ',';
LOOP
IDX1 := INSTR(STR1, ',', 1);
IDX2 := INSTR(STR2, ',', 1);
EXIT WHEN((IDX1 = 0) OR (IDX2 = 0) OR (IDX1 IS NULL) OR (IDX2 IS NULL));
v_test.extend();
V_TEST(v_test.COUNT) := t_test(SUBSTR(STR1, 1, IDX1 - 1),
SUBSTR(STR2, 1, IDX2 - 1));
STR1 := SUBSTR(STR1, IDX1 + 1, LENGTH(STR1) - IDX1);
STR2 := SUBSTR(STR2, IDX2 + 1, LENGTH(STR2) - IDX2);
END LOOP;
return v_test;
end ZHSQ;



--调用实例

insert into studesc
(id, name, descr)
SELECT T.ID,S.NAME,T.DESCR FROM TABLE(ZHSQ('(1,3,4)','(QQQ,WWW,EEE)')) T,STUDENT S WHERE T.ID=S.ID