CREATE OR REPLACE FUNCTION GETFROSTMONEY(YEAR in VARCHAR2,TAB in VARCHAR2,MONTH in VARCHAR2) RETURN VARCHAR2 AS
RESULT CLOB;
BEGIN
RESULT := 'with t as(select (case when (actualmoney-actualpay) > 0 then (actualmoney-actualpay) else 0 end) val from ' TAB '
where substr(period,0,4) = p.year and to_number(substr(period,6,2)) = 1 )
select decode(paystate,1,0,(select val from t)) frostmoney1 from uf_plant_wboutrepair';
RETURN RESULT;
END GETFROSTMONEY;我想自定义一个函数,让它返回一个超大文本,然后在其他地方,我只需要调用这个函数就能得到这个文本,现在问题是,如何才能将我传过来的TAB传进这个字符串当中去
RESULT CLOB;
BEGIN
RESULT := 'with t as(select (case when (actualmoney-actualpay) > 0 then (actualmoney-actualpay) else 0 end) val from ' TAB '
where substr(period,0,4) = p.year and to_number(substr(period,6,2)) = 1 )
select decode(paystate,1,0,(select val from t)) frostmoney1 from uf_plant_wboutrepair';
RETURN RESULT;
END GETFROSTMONEY;我想自定义一个函数,让它返回一个超大文本,然后在其他地方,我只需要调用这个函数就能得到这个文本,现在问题是,如何才能将我传过来的TAB传进这个字符串当中去
解决方案 »
- jboss部署问题
- 寒假碰到的难题,望大家帮忙下哈!!
- JSTL标签使用错误,大侠帮忙看下
- java webservices例子
- spring问题:No request handling method with name 'list' in class .......
- 我使用websphere的application server 5.0 版本 ,它自带的jdk怎么是1。3版本的??? 有谁知道如何使was5.0环境下使用jdk1.4的啊?
- 方案征集,均有分!!!
- sun的j2ee sdk 1.3.1不能在2000server上运行??
- 如何使用eclipse,请各位大哥指教
- 要做一个出色的 JAVA 程序员,除了JAVA外,还应该懂些什么(比如 XML、Linux等),推荐书籍
- 菜鸟问题
- 有没有这样的集合?
UPDATETEXT tempTable.content @ptrval NULL 0 ']}'
UPDATETEXT tempTable.content @ptrval @insertPoint 0 @result
where substr(period,0,4) = p.year and to_number(substr(period,6,2)) = 1 )
select decode(paystate,1,0,(select val from t)) frostmoney1 from uf_admin_buylabour
union all
with t as(select (case when (actualmoney-actualpay) > 0 then (actualmoney-actualpay) else 0 end) val from uf_admin_gift
where substr(period,0,4) = p.year and to_number(substr(period,6,2)) = 1 )
select decode(paystate,1,0,(select val from t)) frostmoney1 from uf_admin_gift
union all
with t as(select (case when (actualmoney-actualpay) > 0 then (actualmoney-actualpay) else 0 end) val from uf_admin_itpurchase
where substr(period,0,4) = p.year and to_number(substr(period,6,2)) = 1 )
select decode(paystate,1,0,(select val from t)) frostmoney1 from uf_admin_itpurchase
union all
with t as(select (case when (actualmoney-actualpay) > 0 then (actualmoney-actualpay) else 0 end) val from uf_admin_itmaintain
where substr(period,0,4) = p.year and to_number(substr(period,6,2)) = 1 )
select decode(paystate,1,0,(select val from t)) frostmoney1 from uf_admin_itmaintain
union all
with t as(select (case when (actualmoney-actualpay) > 0 then (actualmoney-actualpay) else 0 end) val from uf_plant_po
where substr(period,0,4) = p.year and to_number(substr(period,6,2)) = 1 )
select decode(paystate,1,0,(select val from t)) frostmoney1 from uf_plant_po
union all
with t as(select (case when (actualmoney-actualpay) > 0 then (actualmoney-actualpay) else 0 end) val from uf_plant_ctdemand
where substr(period,0,4) = p.year and to_number(substr(period,6,2)) = 1 )
select decode(paystate,1,0,(select val from t)) frostmoney1 from uf_plant_ctdemand
union all
with t as(select (case when (actualmoney-actualpay) > 0 then (actualmoney-actualpay) else 0 end) val from uf_plant_outrepair
where substr(period,0,4) = p.year and to_number(substr(period,6,2)) = 1 )
select decode(paystate,1,0,(select val from t)) frostmoney1 from uf_plant_outrepair
union all
with t as(select (case when (actualmoney-actualpay) > 0 then (actualmoney-actualpay) else 0 end) val from uf_plant_rent
where substr(period,0,4) = p.year and to_number(substr(period,6,2)) = 1 )
select decode(paystate,1,0,(select val from t)) frostmoney1 from uf_plant_rent
union all
with t as(select (case when (actualmoney-actualpay) > 0 then (actualmoney-actualpay) else 0 end) val from uf_plant_epiboly
where substr(period,0,4) = p.year and to_number(substr(period,6,2)) = 1 )
select decode(paystate,1,0,(select val from t)) frostmoney1 from uf_plant_epiboly
union all
with t as(select (case when (actualmoney-actualpay) > 0 then (actualmoney-actualpay) else 0 end) val from uf_plant_wboutrepair
where substr(period,0,4) = p.year and to_number(substr(period,6,2)) = 1 )
select decode(paystate,1,0,(select val from t)) frostmoney1 from uf_plant_wboutrepair
union all
with t as(select (case when (actualmoney-actualpay) > 0 then (actualmoney-actualpay) else 0 end) val from uf_research_trialrun
where substr(period,0,4) = p.year and to_number(substr(period,6,2)) = 1 )
select decode(paystate,1,0,(select val from t)) frostmoney1 from uf_research_trialrun
union all
with t as(select (case when (actualmoney-actualpay) > 0 then (actualmoney-actualpay) else 0 end) val from uf_admin_facility
where substr(period,0,4) = p.year and to_number(substr(period,6,2)) = 1 )
select decode(paystate,1,0,(select val from t)) frostmoney1 from uf_admin_facility....
我其实就是想像java一样,把相同的语句重构出来,然后在这里只需要调用函数,传入参数返回语句就行了