Create PROCEDURE zzszpxqcx
@time1 String;
@time2 String
@time3 String
AS
select a.xq as xq,a.by1,b.sn,c.sy, ( case when b.sn=0 then 0 else cast((a.by1-b.sn)/b.sn*100 as decimal(10,2)) end) as zj1, ( case when c.sy=0 then 0 else cast((a.by1-c.sy)/c.sy*100 as decimal(10,2)) end) as zj2 from (select xq,sum(zse) as by1 from(select sum (distinct b.se) as zse,case when d.swjg_mc like '%1%' then '1when d.swjg_mc like '%2%' then '2'when d.swjg_mc like '%3%' then '3' when d.swjg_mc like '%4%' then '4' when d.swjg_mc like '%5%' then '5' when d.swjg_mc like '%6%' then '6' else '出错' end xq from sb_zzs_2003_ybnsr a, sb_zzs_2003_fb2 b,dj_nsrxx c,dm_swjg d where a.pzxh=b.pzxh and to_char(a.sssq_q,'yyyy-mm')=@time1 and b.yxbz='Y' and b.fblc='1' and a.nsr_swjg_dm=d.swjg_dm group by d.swjg_mc) group by xq) a,(select xq,sum(zse) as sn from(select sum (distinct b.se) as zse,case when d.swjg_mc like '%1%' then '1' when d.swjg_mc like '%2%' then '2' when d.swjg_mc like '%3%' then '3' when d.swjg_mc like '%4%' then '4' when d.swjg_mc like '%5%' then '5' when d.swjg_mc like '%6%' then '6' else '出错' end xq from sb_zzs_2003_ybnsr a, sb_zzs_2003_fb2 b,dj_nsrxx c,dm_swjg d where a.pzxh=b.pzxh and to_char(a.sssq_q,'yyyy-mm')=@time2 and b.yxbz='Y' and b.fblc='1' and a.nsr_swjg_dm=d.swjg_dm group by d.swjg_mc) group by xq) b,(select xq,sum(zse) as sy from(select sum (distinct b.se) as zse,case when d.swjg_mc like '%1%' then '1' when d.swjg_mc like '%2%' then '2' when d.swjg_mc like '%3%' then '3' when d.swjg_mc like '%4%' then '4' when d.swjg_mc like '%5%' then '5' when d.swjg_mc like '%6%' then '6' else '出错' end xq from sb_zzs_2003_ybnsr a, sb_zzs_2003_fb2 b,dj_nsrxx c,dm_swjg d where a.pzxh=b.pzxh and to_char(a.sssq_q,'yyyy-mm')=@time3 and b.yxbz='Y' and b.fblc='1' and a.nsr_swjg_dm=d.swjg_dm group by d.swjg_mc) group by xq) c where a.xq=b.xq and a.xq=c.xq;
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GOsql在PL/SQL下运行正常,但是这个存储过程编译不过,提示无效的存储过程,顺便问一下,JSP下调多参数存储过程怎样传值
@time1 String;
@time2 String
@time3 String
AS
select a.xq as xq,a.by1,b.sn,c.sy, ( case when b.sn=0 then 0 else cast((a.by1-b.sn)/b.sn*100 as decimal(10,2)) end) as zj1, ( case when c.sy=0 then 0 else cast((a.by1-c.sy)/c.sy*100 as decimal(10,2)) end) as zj2 from (select xq,sum(zse) as by1 from(select sum (distinct b.se) as zse,case when d.swjg_mc like '%1%' then '1when d.swjg_mc like '%2%' then '2'when d.swjg_mc like '%3%' then '3' when d.swjg_mc like '%4%' then '4' when d.swjg_mc like '%5%' then '5' when d.swjg_mc like '%6%' then '6' else '出错' end xq from sb_zzs_2003_ybnsr a, sb_zzs_2003_fb2 b,dj_nsrxx c,dm_swjg d where a.pzxh=b.pzxh and to_char(a.sssq_q,'yyyy-mm')=@time1 and b.yxbz='Y' and b.fblc='1' and a.nsr_swjg_dm=d.swjg_dm group by d.swjg_mc) group by xq) a,(select xq,sum(zse) as sn from(select sum (distinct b.se) as zse,case when d.swjg_mc like '%1%' then '1' when d.swjg_mc like '%2%' then '2' when d.swjg_mc like '%3%' then '3' when d.swjg_mc like '%4%' then '4' when d.swjg_mc like '%5%' then '5' when d.swjg_mc like '%6%' then '6' else '出错' end xq from sb_zzs_2003_ybnsr a, sb_zzs_2003_fb2 b,dj_nsrxx c,dm_swjg d where a.pzxh=b.pzxh and to_char(a.sssq_q,'yyyy-mm')=@time2 and b.yxbz='Y' and b.fblc='1' and a.nsr_swjg_dm=d.swjg_dm group by d.swjg_mc) group by xq) b,(select xq,sum(zse) as sy from(select sum (distinct b.se) as zse,case when d.swjg_mc like '%1%' then '1' when d.swjg_mc like '%2%' then '2' when d.swjg_mc like '%3%' then '3' when d.swjg_mc like '%4%' then '4' when d.swjg_mc like '%5%' then '5' when d.swjg_mc like '%6%' then '6' else '出错' end xq from sb_zzs_2003_ybnsr a, sb_zzs_2003_fb2 b,dj_nsrxx c,dm_swjg d where a.pzxh=b.pzxh and to_char(a.sssq_q,'yyyy-mm')=@time3 and b.yxbz='Y' and b.fblc='1' and a.nsr_swjg_dm=d.swjg_dm group by d.swjg_mc) group by xq) c where a.xq=b.xq and a.xq=c.xq;
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GOsql在PL/SQL下运行正常,但是这个存储过程编译不过,提示无效的存储过程,顺便问一下,JSP下调多参数存储过程怎样传值
解决方案 »
- 未能找到类型命名空间名称“OracleCommand”
- 三张数据量不算大的表关联效率很低的原因
- 基于Oracle 11g select语句的学习贴
- 请教一个查询速率的诡异问题
- 最最菜鸟的问题:oracle的循环控制语句中有没有break和continue语句,要是没有有什么替代语句?
- 如何查找一条纪录的两项 NOT IN 另一个表中
- 我的数据库有很多表,磁盘空间快没了!!
- ORACLE权限问题
- ORACLE存储过程FOR循环报错
- oracle数据库怎么把number类型数加上千分符然后再储存到为number类型的列中
- 用select如何通过行的索引进行操作?
- 在plsql中可以正常运行,在JSP中却报错。
少了个单引号,修改后如下:SELECT A.XQ AS XQ,
A.BY1,
B.SN,
C.SY,
(CASE
WHEN B.SN = 0 THEN
0
ELSE
CAST((A.BY1 - B.SN) / B.SN * 100 AS DECIMAL(10, 2))
END) AS ZJ1,
(CASE
WHEN C.SY = 0 THEN
0
ELSE
CAST((A.BY1 - C.SY) / C.SY * 100 AS DECIMAL(10, 2))
END) AS ZJ2
FROM (SELECT XQ, SUM(ZSE) AS BY1
FROM (SELECT SUM(DISTINCT B.SE) AS ZSE,
CASE
WHEN D.SWJG_MC LIKE '%1%' THEN
'1'
WHEN D.SWJG_MC LIKE '%2%' THEN
'2'
WHEN D.SWJG_MC LIKE '%3%' THEN
'3'
WHEN D.SWJG_MC LIKE '%4%' THEN
'4'
WHEN D.SWJG_MC LIKE '%5%' THEN
'5'
WHEN D.SWJG_MC LIKE '%6%' THEN
'6'
ELSE
'出错'
END XQ
FROM SB_ZZS_2003_YBNSR A,
SB_ZZS_2003_FB2 B,
DJ_NSRXX C,
DM_SWJG D
WHERE A.PZXH = B.PZXH
AND TO_CHAR(A.SSSQ_Q, 'yyyy-mm') = @TIME1
AND B.YXBZ = 'Y'
AND B.FBLC = '1'
AND A.NSR_SWJG_DM = D.SWJG_DM
GROUP BY D.SWJG_MC)
GROUP BY XQ) A,
(SELECT XQ, SUM(ZSE) AS SN
FROM (SELECT SUM(DISTINCT B.SE) AS ZSE,
CASE
WHEN D.SWJG_MC LIKE '%1%' THEN
'1'
WHEN D.SWJG_MC LIKE '%2%' THEN
'2'
WHEN D.SWJG_MC LIKE '%3%' THEN
'3'
WHEN D.SWJG_MC LIKE '%4%' THEN
'4'
WHEN D.SWJG_MC LIKE '%5%' THEN
'5'
WHEN D.SWJG_MC LIKE '%6%' THEN
'6'
ELSE
'出错'
END XQ
FROM SB_ZZS_2003_YBNSR A,
SB_ZZS_2003_FB2 B,
DJ_NSRXX C,
DM_SWJG D
WHERE A.PZXH = B.PZXH
AND TO_CHAR(A.SSSQ_Q, 'yyyy-mm') = @TIME2
AND B.YXBZ = 'Y'
AND B.FBLC = '1'
AND A.NSR_SWJG_DM = D.SWJG_DM
GROUP BY D.SWJG_MC)
GROUP BY XQ) B,
(SELECT XQ, SUM(ZSE) AS SY
FROM (SELECT SUM(DISTINCT B.SE) AS ZSE,
CASE
WHEN D.SWJG_MC LIKE '%1%' THEN
'1'
WHEN D.SWJG_MC LIKE '%2%' THEN
'2'
WHEN D.SWJG_MC LIKE '%3%' THEN
'3'
WHEN D.SWJG_MC LIKE '%4%' THEN
'4'
WHEN D.SWJG_MC LIKE '%5%' THEN
'5'
WHEN D.SWJG_MC LIKE '%6%' THEN
'6'
ELSE
'出错'
END XQ
FROM SB_ZZS_2003_YBNSR A,
SB_ZZS_2003_FB2 B,
DJ_NSRXX C,
DM_SWJG D
WHERE A.PZXH = B.PZXH
AND TO_CHAR(A.SSSQ_Q, 'yyyy-mm') = @TIME3
AND B.YXBZ = 'Y'
AND B.FBLC = '1'
AND A.NSR_SWJG_DM = D.SWJG_DM
GROUP BY D.SWJG_MC)
GROUP BY XQ) C
WHERE A.XQ = B.XQ
AND A.XQ = C.XQ;