- create or replace function fn_test
- (pid in number)
- return
- tabTEST
- is
- Result tabTEST =tabTEST();
- USERID number(12);
- VNAME varchar(200);
- orgid number(12);
- type tab is table of number;
- tab_test1 tab;
- /***
- create or replace type ctest as object
- (
- cUser number(12), -- 不可注释
- cName varchar(200),
- cOrg number(12)
- )
- create or replace type tabTEST is table of ctest
- 测试
- select from table(cast(fn_test({0})as tabTEST))
- ***/
- begin
- select ID bulk collect into tab_test1 from tuser where id=pid;
- Result =tabTEST();
- for i in 1..tab_test1.count loop
- select ID,name,belongorg into USERID,VNAME,orgid from tuser where ID=tab_test1(i);
- Result.extend;
- Result(Result.count)=ctest(NULL,NULL,NULL);
- Result(Result.count).cUser=USERID;
- Result(Result.count).cName=VNAME;
- Result(Result.count).cOrg =orgid;
- end loop;
- return(Result);
- end fn_test;
来源: http://www.linuxidc.com/Linux/2018-08/153591.htm