create or replace procedure PR_QianFeiQingDan
(
s_st in varchar(2),
s_tjfs in varchar(5),
s_YM in varchar(6)
)
is
BEGIN
orderCondition varchar(128);
joinCondition varchar(128);
field varchar(30);
condition varchar(128);
sql varchar(4000);
ym varchar(32);
zhandian varchar(32); condition:='';
joinCondition:=''; if nvl(s_YM,'')='' then
begin
s_YM:=to_char(sysdate,'yyyymm');
end
end if; if nvl(s_st,'')='' then
begin
s_st:='00';
end
end if;
if s_st<>'00' then
begin
condition:=condition+' and a.s_st='''+s_st+'''';
end
end if; if s_tjfs='0' then--收费方式
begin
field:=',S_ShouFeiMC s_sffs';
orderCondition:='order by S_ShouFeiMC';
joinCondition:=' left join KG_YongHuSFFS c on a.i_sffs=c.I_ShouFeiBH ';
end
end if;
if s_tjfs='1' then--用水性质
begin
field:=',S_FeiLeiMC s_sffs';
orderCondition:='order by S_FeiLeiMC';
joinCondition:=' left join JG_YongShuiFL c on a.I_JH=c.I_JH and a.I_TJH=c.I_TiaoJiaH ';
end
end if; if s_tjfs='2' then--册本
begin
field:=',S_CH s_sffs';
orderCondition:='order by S_CH';
end
end if; if s_tjfs='3' then--抄表员
begin
field:=',Name s_sffs';
orderCondition:='order by Name';
joinCondition:=' left join kg_cebenxx c on a.S_Ch=c.S_Ch
left join CB_ChaoBiaoYXX e on S_ChaoBiaoYBH=e.S_YuanGongBH';
end
end if; ym:=substr(s_YM,1,4)+'年'+substr(5,2)+'月'
sql:=' select a.s_cid'+field+'
,a.s_hm
,a.s_dz
,(i_y*100+i_m) ym
,a.n_je,a.N_YingShouZNJ,b.S_LianXiDH
,nvl(a.n_je,0) count
,'''+ym+''' time
, case when S_ZhanDianMC is null then ''供水总公司'' else S_ZhanDianMC end st
,row_number() over('+orderCondition+') id
,case '+s_tjfs+' when 0 then ''收费方式'' when 1 then ''用水性质''
when 2 then ''册本号'' else ''抄表员'' end ''tjfs''
from zw_yingyez a
left join kg_kehuxx b on a.s_cid=b.s_cid '+joinCondition+'
left join sx_zhandianxx d on d.s_st='+s_st+'
where a.i_jlzt=0 and a.N_JE>0 and i_xiaozhang=0 and (i_y*100+i_m)='+s_YM+''+condition
execute sql
END
(
s_st in varchar(2),
s_tjfs in varchar(5),
s_YM in varchar(6)
)
is
BEGIN
orderCondition varchar(128);
joinCondition varchar(128);
field varchar(30);
condition varchar(128);
sql varchar(4000);
ym varchar(32);
zhandian varchar(32); condition:='';
joinCondition:=''; if nvl(s_YM,'')='' then
begin
s_YM:=to_char(sysdate,'yyyymm');
end
end if; if nvl(s_st,'')='' then
begin
s_st:='00';
end
end if;
if s_st<>'00' then
begin
condition:=condition+' and a.s_st='''+s_st+'''';
end
end if; if s_tjfs='0' then--收费方式
begin
field:=',S_ShouFeiMC s_sffs';
orderCondition:='order by S_ShouFeiMC';
joinCondition:=' left join KG_YongHuSFFS c on a.i_sffs=c.I_ShouFeiBH ';
end
end if;
if s_tjfs='1' then--用水性质
begin
field:=',S_FeiLeiMC s_sffs';
orderCondition:='order by S_FeiLeiMC';
joinCondition:=' left join JG_YongShuiFL c on a.I_JH=c.I_JH and a.I_TJH=c.I_TiaoJiaH ';
end
end if; if s_tjfs='2' then--册本
begin
field:=',S_CH s_sffs';
orderCondition:='order by S_CH';
end
end if; if s_tjfs='3' then--抄表员
begin
field:=',Name s_sffs';
orderCondition:='order by Name';
joinCondition:=' left join kg_cebenxx c on a.S_Ch=c.S_Ch
left join CB_ChaoBiaoYXX e on S_ChaoBiaoYBH=e.S_YuanGongBH';
end
end if; ym:=substr(s_YM,1,4)+'年'+substr(5,2)+'月'
sql:=' select a.s_cid'+field+'
,a.s_hm
,a.s_dz
,(i_y*100+i_m) ym
,a.n_je,a.N_YingShouZNJ,b.S_LianXiDH
,nvl(a.n_je,0) count
,'''+ym+''' time
, case when S_ZhanDianMC is null then ''供水总公司'' else S_ZhanDianMC end st
,row_number() over('+orderCondition+') id
,case '+s_tjfs+' when 0 then ''收费方式'' when 1 then ''用水性质''
when 2 then ''册本号'' else ''抄表员'' end ''tjfs''
from zw_yingyez a
left join kg_kehuxx b on a.s_cid=b.s_cid '+joinCondition+'
left join sx_zhandianxx d on d.s_st='+s_st+'
where a.i_jlzt=0 and a.N_JE>0 and i_xiaozhang=0 and (i_y*100+i_m)='+s_YM+''+condition
execute sql
END
其他的没有仔细看
create or replace procedure PR_QianFeiQingDan(
s_st in varchar, --参数只指定类型,不指定大小
s_tjfs in varchar,
s_YM in varchar
)
is
orderCondition varchar(128);
joinCondition varchar(128);
field varchar(30);
condition varchar(128);
s_sql varchar(4000); --sql是oracle保留字,不能用作变量名称
ym varchar(32);
zhandian varchar(32);
BEGIN
condition:='';
joinCondition:=''; if nvl(s_YM,'')='' then
begin
s_YM:=to_char(sysdate,'yyyymm');
end; --缺少分号
end if; if nvl(s_st,'')='' then
begin
s_st:='00';
end; --缺少分号
end if;
if s_st<>'00' then
begin
condition:=condition+' and a.s_st='''+s_st+'''';
end;--缺少分号
end if; if s_tjfs='0' then--收费方式
begin
field:=',S_ShouFeiMC s_sffs';
orderCondition:='order by S_ShouFeiMC';
joinCondition:=' left join KG_YongHuSFFS c on a.i_sffs=c.I_ShouFeiBH ';
end;--缺少分号
end if;
if s_tjfs='1' then--用水性质
begin
field:=',S_FeiLeiMC s_sffs';
orderCondition:='order by S_FeiLeiMC';
joinCondition:=' left join JG_YongShuiFL c on a.I_JH=c.I_JH and a.I_TJH=c.I_TiaoJiaH ';
end;--缺少分号
end if; if s_tjfs='2' then--册本
begin
field:=',S_CH s_sffs';
orderCondition:='order by S_CH';
end;--缺少分号
end if; if s_tjfs='3' then--抄表员
begin
field:=',Name s_sffs';
orderCondition:='order by Name';
joinCondition:=' left join kg_cebenxx c on a.S_Ch=c.S_Ch
left join CB_ChaoBiaoYXX e on S_ChaoBiaoYBH=e.S_YuanGongBH';
end;--缺少分号
end if; ym:=substr(s_YM,1,4)+'年'+substr(5,2)+'月';--缺少分号
s_sql:=' select a.s_cid'+field+'
,a.s_hm
,a.s_dz
,(i_y*100+i_m) ym
,a.n_je,a.N_YingShouZNJ,b.S_LianXiDH
,nvl(a.n_je,0) count
,'''+ym+''' time
, case when S_ZhanDianMC is null then ''供水总公司'' else S_ZhanDianMC end st
,row_number() over('+orderCondition+') id
,case '+s_tjfs+' when 0 then ''收费方式'' when 1 then ''用水性质''
when 2 then ''册本号'' else ''抄表员'' end ''tjfs''
from zw_yingyez a
left join kg_kehuxx b on a.s_cid=b.s_cid '+joinCondition+'
left join sx_zhandianxx d on d.s_st='+s_st+'
where a.i_jlzt=0 and a.N_JE>0 and i_xiaozhang=0 and (i_y*100+i_m)='+s_YM+''+condition;--缺少分号
execute immediate s_sql;--缺少分号,用execute immediate
END错误处都有注释,拼接如果有错误,自己找找
2.if语句里没有begin,end
第2点不赞成
这个过程很简单,if语句块内是没有必要begin..end;
但如果if块内的业务逻辑很复杂的话,用begin..end;连分段执行是很有必要的。
--太长了初步看了下过程里面语法的变量声明位置错误 应该是 在is 后面
create or replace procedure PR_QianFeiQingDan
(
s_st in varchar(2),
s_tjfs in varchar(5),
s_YM in varchar(6)
)
is
orderCondition varchar(128);
joinCondition varchar(128);
field varchar(30);
condition varchar(128);
sql varchar(4000);
ym varchar(32);
zhandian varchar(32);
BEGIN
condition:='';
joinCondition:=''; if nvl(s_YM,'')='' then
begin
s_YM:=to_char(sysdate,'yyyymm');
end
end if; if nvl(s_st,'')='' then
begin
s_st:='00';
end
end if;
if s_st<>'00' then
begin
condition:=condition+' and a.s_st='''+s_st+'''';
end
end if; if s_tjfs='0' then--收费方式
begin
field:=',S_ShouFeiMC s_sffs';
orderCondition:='order by S_ShouFeiMC';
joinCondition:=' left join KG_YongHuSFFS c on a.i_sffs=c.I_ShouFeiBH ';
end
end if;
if s_tjfs='1' then--用水性质
begin
field:=',S_FeiLeiMC s_sffs';
orderCondition:='order by S_FeiLeiMC';
joinCondition:=' left join JG_YongShuiFL c on a.I_JH=c.I_JH and a.I_TJH=c.I_TiaoJiaH ';
end
end if; if s_tjfs='2' then--册本
begin
field:=',S_CH s_sffs';
orderCondition:='order by S_CH';
end
end if; if s_tjfs='3' then--抄表员
begin
field:=',Name s_sffs';
orderCondition:='order by Name';
joinCondition:=' left join kg_cebenxx c on a.S_Ch=c.S_Ch
left join CB_ChaoBiaoYXX e on S_ChaoBiaoYBH=e.S_YuanGongBH';
end
end if; ym:=substr(s_YM,1,4)+'年'+substr(5,2)+'月'
sql:=' select a.s_cid'+field+'
,a.s_hm
,a.s_dz
,(i_y*100+i_m) ym
,a.n_je,a.N_YingShouZNJ,b.S_LianXiDH
,nvl(a.n_je,0) count
,'''+ym+''' time
, case when S_ZhanDianMC is null then ''供水总公司'' else S_ZhanDianMC end st
,row_number() over('+orderCondition+') id
,case '+s_tjfs+' when 0 then ''收费方式'' when 1 then ''用水性质''
when 2 then ''册本号'' else ''抄表员'' end ''tjfs''
from zw_yingyez a
left join kg_kehuxx b on a.s_cid=b.s_cid '+joinCondition+'
left join sx_zhandianxx d on d.s_st='+s_st+'
where a.i_jlzt=0 and a.N_JE>0 and i_xiaozhang=0 and (i_y*100+i_m)='+s_YM+''+condition
execute sql
END
execute immediate sql;
好像就是一个select
2 连接要用 || 不是 +
还有一楼说的
还有 拼接sql是 ''''代表一个 '
s_st in varchar, --参数只指定类型,不指定大小
s_tjfs in varchar,
s_YM in varchar
)
is
orderCondition varchar(128);
joinCondition varchar(128);
field varchar(30);
condition varchar(128);
s_sql varchar(4000); --sql是oracle保留字,不能用作变量名称
ym varchar(32);
s_YM1 varchar(6);
s_st1 varchar(5);
--变量声明放在is的后面begin的前面
BEGIN
condition:='';
joinCondition:='';
s_YM1:=s_YM;
s_st1:=s_st;
if s_YM is null then
begin
s_YM1:=to_char(sysdate,'yyyymm');
end; --分号
end if; if s_st is null then
begin
s_st1:='00';
end; --分号
end if;
if s_st<>'00' then
begin
condition:=condition+' and a.s_st='''+s_st1+'''';
end;--分号
end if; if s_tjfs='0' then--收费方式
begin
field:=',S_ShouFeiMC s_sffs';
orderCondition:='order by S_ShouFeiMC';
joinCondition:=' left join KG_YongHuSFFS c on a.i_sffs=c.I_ShouFeiBH ';
end;--分号
end if;
if s_tjfs='1' then--用水性质
begin
field:=',S_FeiLeiMC s_sffs';
orderCondition:='order by S_FeiLeiMC';
joinCondition:=' left join JG_YongShuiFL c on a.I_JH=c.I_JH and a.I_TJH=c.I_TiaoJiaH ';
end;--分号
end if; if s_tjfs='2' then--册本
begin
field:=',S_CH s_sffs';
orderCondition:='order by S_CH';
end;--分号
end if; if s_tjfs='3' then--抄表员
begin
field:=',Name s_sffs';
orderCondition:='order by Name';
joinCondition:=' left join kg_cebenxx c on a.S_Ch=c.S_Ch
left join CB_ChaoBiaoYXX e on S_ChaoBiaoYBH=e.S_YuanGongBH';
end;--分号
end if; ym:=substr(s_YM1,1,4)+'年'+substr(5,2)+'月';--分号
s_sql:='select a.s_cid'+field+'
,a.s_hm
,a.s_dz
,(i_y*100+i_m) ym
,a.n_je,a.N_YingShouZNJ,b.S_LianXiDH
,nvl(a.n_je,0) count
,'''+ym+''' time
, case when S_ZhanDianMC is null then ''供水总公司'' else S_ZhanDianMC end st
,row_number() over('+orderCondition+') id
,case '+s_tjfs+' when 0 then ''收费方式'' when 1 then ''用水性质''
when 2 then ''册本号'' else ''抄表员'' end ''tjfs''
from zw_yingyez a
left join kg_kehuxx b on a.s_cid=b.s_cid '+joinCondition+'
left join sx_zhandianxx d on d.s_st='+s_st1+'
where a.i_jlzt=0 and a.N_JE>0 and i_xiaozhang=0 and (i_y*100+i_m)='+s_YM1+''+condition;--分号
execute immediate s_sql;--分号,用execute immediate
END;
Oracle不能给参数变量赋值,也就是下面这里是错误的。
if nvl(s_YM,'')='' then
begin
s_YM:=to_char(sysdate,'yyyymm');
end; --缺少分号
end if;
正确的如下:if s_st is null then
begin
s_st1:='00';
end; --分号
end if;
再请教一下,这段SQL有语法错误吗?编译可以通过,执行提示“没有发现需要的FROM关键字”。s_sql:='select a.s_cid'||field||'
,a.s_hm
,a.s_dz
,(i_y*100||i_m) ym
,a.n_je,a.N_YingShouZNJ,b.S_LianXiDH
,nvl(a.n_je,0) count
,'''||ym||''' time
, case when S_ZhanDianMC is null then ''供水总公司'' else S_ZhanDianMC end st
,row_number() over('||orderCondition||') id
,case '||s_tjfs||' when 0 then ''收费方式'' when 1 then ''用水性质''
when 2 then ''册本号'' else ''抄表员'' end tjfs
from zw_yingyez a
left join kg_kehuxx b on a.s_cid=b.s_cid '||joinCondition||'
left join sx_zhandianxx d on d.s_st='||s_st1||'
where a.i_jlzt=0 and a.N_JE>0 and i_xiaozhang=0 and (i_y*100||i_m)='||s_YM1||''||condition;--分号