create proc proc_empDayStatReport
@statId int,@deptCode varchar(36),@sql varchar(2000) output
as
begin
if @deptCode=''
begin
set @sql = 'select [consumeTime],[deptCode],[deptName],[empCode],[empName],[cardNo]'
select @sql = @sql + ',min(case [cmtName] when '''+[cmtName]+''' then [statMoney] end) as ['+CAST(cmtdId as VARCHAR)+'],min(case [cmtName] when '''+[cmtName]+''' then [statCopy] end) as [c'+CAST(cmtdId as VARCHAR)+']'
from (select distinct [cmtdId],[cmtName] from [LmXfStatEmpcs]) as a
select @sql = @sql+',sum(statMoney) as sumMoney,sum(statCopy) as sumCopy from [LmXfStatEmpcs] where [statId]='+cast(@statId as varchar)+' and deptCode is not null group by [consumeTime],[deptCode],[deptName],[empCode],[empName],[cardNo] order by [consumeTime],[deptCode],[empCode]'
end
end
将上边的存储过程改为Oracle的
@statId int,@deptCode varchar(36),@sql varchar(2000) output
as
begin
if @deptCode=''
begin
set @sql = 'select [consumeTime],[deptCode],[deptName],[empCode],[empName],[cardNo]'
select @sql = @sql + ',min(case [cmtName] when '''+[cmtName]+''' then [statMoney] end) as ['+CAST(cmtdId as VARCHAR)+'],min(case [cmtName] when '''+[cmtName]+''' then [statCopy] end) as [c'+CAST(cmtdId as VARCHAR)+']'
from (select distinct [cmtdId],[cmtName] from [LmXfStatEmpcs]) as a
select @sql = @sql+',sum(statMoney) as sumMoney,sum(statCopy) as sumCopy from [LmXfStatEmpcs] where [statId]='+cast(@statId as varchar)+' and deptCode is not null group by [consumeTime],[deptCode],[deptName],[empCode],[empName],[cardNo] order by [consumeTime],[deptCode],[empCode]'
end
end
将上边的存储过程改为Oracle的
(
statId int,
deptCode varchar,
sql out varchar)
is
beginif deptCode='' thensql :=
'select [consumeTime],[deptCode],[deptName],[empCode],[empName],[cardNo]'
;
sql := sql || ',min(case [cmtName] when '''||[cmtName]||
''' then [statMoney] end) as ['||to_char(cmtdId , VARCHAR2)||
'],min(case [cmtName] when '''||[cmtName]||
''' then [statCopy] end) as [c'
||to_char(cmtdId,varchar2)||']'
from (select distinct [cmtdId],[cmtName] from [LmXfStatEmpcs]) as a;
sql := sql||
',sum(statMoney) as sumMoney,sum(statCopy) as sumCopy from [LmXfStatEmpcs] where [statId]='
||to_char(statId , varchar2)||
' and deptCode is not null group by [consumeTime],[deptCode],[deptName],[empCode],[empName],[cardNo] order by [consumeTime],[deptCode],[empCode]'
;
end if;
end;
deptcode varchar,
sql out varchar)
is
begin
if deptcode = '' then
sql :=
'select [consumeTime],[deptCode],[deptName],[empCode],[empName],[cardNo]';
sql :=
sql
|| ',min(case [cmtName] when '''
|| '[cmtName]'
|| ''' then [statMoney] end) as ['
|| to_char (cmtdid, varchar2)
|| '],min(case [cmtName] when '''
|| '[cmtName]'
|| ''' then [statCopy] end) as [c'
|| to_char (cmtdid, varchar2)
|| ']
from (select distinct [cmtdId],[cmtName] from [LmXfStatEmpcs]) as a';
sql :=
sql
|| ',sum(statMoney) as sumMoney,sum(statCopy) as sumCopy from [LmXfStatEmpcs] where [statId]='
|| to_char (statid, varchar2)
|| ' and deptCode is not null group by [consumeTime],[deptCode],[deptName],[empCode],[empName],[cardNo] order by [consumeTime],[deptCode],[empCode]';
end if;
end;
Compilation errors for PROCEDURE SYSTEM.PROC_DEPTSTATREPORTError: PLS-00103: 出现符号 "["在需要下列之一时:
( - + case mod new null
<an identifier> <a double-quoted delimited-identifier>
<a bind variable> avg count current max min prior sql stddev
sum variance execute forall merge time timestamp interval
date <a string literal with character set specification>
<a number> <a single-quoted SQL string> pipe
<一个带有字符集说明的可带引号的字符串文字>
<一个可带引号的 SQL 字符串>
Line: 15
Text: ''' then [statMoney] end) as ['||to_char(cmtdId , VARCHAR2)||Error: Hint: Parameter 'istatId' is declared but never used in 'proc_deptStatReport'
Line: 3
Text: ideptCode in varchar2,Error: Hint: Comparison with NULL in 'proc_deptStatReport'
Line: 9
Text: then
(statId in int,deptCode in varchar,ssql out varchar)
is
begin
if deptCode='' then
ssql:='select consumeTime,deptCode,deptName,empCode,empName,cardNo,min(decode(cmtName,''[cmtName]'',''[statMoney]'')), ';
ssql:=ssql+'min(decode(cmtName,''[cmtName]'',''[statCopy]'')) from (select distinct cmtdId,cmtName from LmXfStatEmpcs) as a) ';
ssql:=ssql+',sum(statMoney) as sumMoney,sum(statCopy) as sumCopy from LmXfStatEmpcs where statId='+to_char(statId)+' ';
ssql:=ssql+'and deptCode is not null group by consumeTime,deptCode,deptName,empCode,empName,cardNo order by consumeTime,deptCode,empCode;';
end if;
end proc_empDayStatReport;
/
create or replace procedure proc_empDayStatReport(v_statId number,v_deptCode number,v_sql out varchar2 )
as
begin
if v_deptCode='' then
v_sql:='';
for i in(select distinct cmtdId,cmtName from LmXfStatEmpcs)
loop
v_sql:=v_sql||','||'min(case when cmtName='''||i.cmtName||''' then statMoney end) as '||i.cmtdId||',min(case when cmtName='''||i.cmtName||''' then statCopy end) as c'||i.cmtdId;
end loop;
v_sql:='select consumeTime,deptCode,deptName,empCode,empName,cardNo||v_sql||',sum(statMoney) as sumMoney,sum(statCopy) as sumCopy from LmXfStatEmpcs where statId='''||v_statId||''' group by consumeTime,deptCode,deptName,empCode,empName,cardNo order by consumeTime,deptCode,empCode';
end ;
create or replace procedure proc_empDayStatReport(v_statId number,v_deptCode number,v_sql out varchar2 )
as
begin
if v_deptCode='' then
v_sql:='';
for i in(select distinct cmtdId,cmtName from LmXfStatEmpcs)
loop
v_sql:=v_sql||','||'min(case when cmtName='''||i.cmtName||''' then statMoney end) as '||i.cmtdId||',min(case when cmtName='''||i.cmtName||''' then statCopy end) as c'||i.cmtdId;
end loop;
v_sql:='select consumeTime,deptCode,deptName,empCode,empName,cardNo'||v_sql||',sum(statMoney) as sumMoney,sum(statCopy) as sumCopy from LmXfStatEmpcs where statId='''||v_statId||''' group by consumeTime,deptCode,deptName,empCode,empName,cardNo order by consumeTime,deptCode,empCode';
end ;
create or replace procedure proc_empDayStatReport(v_statId number,v_deptCode number,v_sql out varchar2 )
as
begin
if v_deptCode='' then
v_sql:='';
for i in(select distinct cmtdId,cmtName from LmXfStatEmpcs)
loop
v_sql:=v_sql||','||'min(case when cmtName='''||i.cmtName||''' then statMoney end) as '||i.cmtdId||',min(case when cmtName='''||i.cmtName||''' then statCopy end) as c'||i.cmtdId;
end loop;
v_sql:='select consumeTime,deptCode,deptName,empCode,empName,cardNo'||v_sql||',sum(statMoney) as sumMoney,sum(statCopy) as sumCopy from LmXfStatEmpcs where statId='''||v_statId||''' group by consumeTime,deptCode,deptName,empCode,empName,cardNo order by consumeTime,deptCode,empCode';
end if ;end;