函数里使用insert into……select xx from兼容

CREATE FUNCTION [dbo].[f_yonyou_bom_062_find_allchildren] (
@partID VARCHAR ( 100 ), @partVer VARCHAR ( 100 )) returns @t_allchild TABLE (
PARENTPARTID VARCHAR ( 100 ),
PARENTPARTVER VARCHAR ( 100 ),
PARTID VARCHAR ( 100 ),
PARTVER VARCHAR ( 100 ),
SEQNUM INT,
LAST_COUNT INT 
) AS BEGIN
DECLARE
	@COUNT INT 
	SET @COUNT = 0 INSERT INTO @t_allchild ( PARENTPARTID, PARENTPARTVER, PARTID, PARTVER, SEQNUM, LAST_COUNT )
VALUES
	( '', '', '' +@partID + '', '' +@partVer + '', 1, @COUNT )
WHILE
	@@ROWCOUNT > 0 BEGIN
	
	SET @COUNT =@COUNT + 1 INSERT INTO @t_allchild SELECT
	A.PARENTPARTID,
	A.PARENTPARTVAR,
	A.PARTID,
	A.PARTVAR,
	A.SEQNUM,@COUNT 
FROM
	BOM_062 A, @t_allchild B 
WHERE
	A.PARENTPARTID= B.PARTID 
	AND A.PARENTPARTVAR = B.PARTVER 
	AND B.LAST_COUNT = ( @COUNT - 1 ) 
ORDER BY
	B.SEQNUM 
END RETURN 
END

函数里面可以直接使用insert into … select 语句，不能在函数直接返回table类型数据，但是可以使用管道表函数代替，举个例子：
create table test(id int, name varchar(10));
insert into test values(1,‘a’);
commit;
create type row_type as object(id int, name varchar(10));
create type table_type as table of row_type;
create or replace
function func1
return table_type pipelined
as
v row_type;
begin
for myrow in
(
select id, name from test
)
loop
v := row_type(myrow.id, myrow.name);
pipe row (v);
end loop;
return;
end;
select * from table(func1);