Question

谁有oracle环境下调试存储过程的经验，帮我调试下自定义函数，主要是解决在oracle写返回临时表的问题？？

CREATE FUNCTION jinduquxian(sid int,sign varchar2,yyear int,mmonth int)
return t table(sch float,act float,resch float)
AS
sqlexec varchar(400);
field1 number;
schedl varchar(100);
BEGIN
/*创建临时表*/
sqlexec:='create global temporary table t(sch,act)';
Execute Immediate sqlexec
/*获取权重值和挣值 本月*/
select field1:=complete_pct from PLN_Project where Project_ID=@sid and project_type=0;
select
msch=case when field1<>0 then (sum(case date<a.target_end_date then (schedl:=(date-a.target_start_date)/(a.target_end_date-a.target_start_date)) else schedl=:100/field1)) else -1 end,
act=case when field1<>0 then sum(a.complete_pct*a.sch_percent)/(field1) else -1 end
from PLN_task a where a.proj_id=sid ;
/*获取权重值和挣值小于本月累加*/
select
@msch=case when field1<>0 then sum(b.FWeightAbsolute*c.sch_Percent)/(@field1) else -1 end,
@mact=case when field1<>0 then sum(b.FWeightAbsolute*c.act_Percent)/(@field1) else -1 end
from PLN_task a inner join PLN_projwbs b on a.wbs_id=b.wbs_id inner join PLN_project c on a.proj_id=c.project_id
where a.proj_id=sid and a.TASK_type='TT_Task'and to_char(a.early_start_date,'yyyy')<yyear or (to_char(a.early_start_date,'yyyy')<yyear and to_char(a.early_start_date,'mm')<mmonth)
/*将获取的计划值、实际值、变更值插入临时表*/
insert into t values (sch,act)
return
END;

Answer 1

Oralce存储过程不能返回表对象吧，如果是希望返回一个数据集，建议你使用Oracle管道函数，功能类似一个表，可以存储结果集。

Answer 2

你需要创建表对象类型，然后返回该类型请参考
http://www.cnblogs.com/liguiqing/archive/2009/05/26/1489243.html

Answer 3

sqlserver拿过来的吧?语法没改完.

追问

是oracle数据库，和sqlserver有很大区别的。我的难点是在oralcle中怎样在CREATE FUNCTION jinduquxian(sid int,sign varchar2,yyear int,mmonth int)
return t table(sch float,act float,resch float) 返回临时表t

追答

我奇怪的是,你编译能通过?