存储过程:
create or replace procedure CM_RESULT
(
DEPARTMENTID in VARCHAR2,
EXAMINEID in VARCHAR2,
RejectDay out NUMBER,
InpermissionDay out NUMBER,
CancellationDay out NUMBER,
CertificateDay out NUMBER,
TotalDay out NUMBER,)is
--条件变量:根据输入参数查询sql语句使用不同的where子句。
condition varchar2(100);begin
--where子句选择
if EXAMINEID <> '''' then
condition := 'and examineid = ' || EXAMINEID || ' group by state';
elsif DEPARTMENTID <> '''' then
condition := 'and deptid = ' || DEPARTMENTID || ' group by state';
else
condition := 'group by state';
end if;--======================================获取当日办结件数==================================
declare type type_cur is ref cursor; --使用动态游标
cursor_day type_cur;
sqlstr_day varchar2(200);
CountTemp number(10);
State number(3);
BEGIN
sqlstr_day := 'select count(id),state from cm_casetimer where completedate > trunc(sysdate) ' || condition;
OPEN cursor_day for sqlstr_day;
LOOP
FETCH cursor_day INTO CountTemp, State;
EXIT WHEN cursor_day%NOTFOUND;
case State
when 4 then
RejectDay := CountTemp;
when 5 then
InpermissionDay := CountTemp;
when 6 then
CancellationDay := CountTemp;
when 3 then
CertificateDay := CountTemp;
else
null;
end case;
END LOOP;
TotalDay := RejectDay + InpermissionDay + CancellationDay + CertificateDay;
CLOSE cursor_day;
END;end CM_RESULT;
java执行代码:
proc = conn.prepareCall("{ Call CM_RESULT(?,?,?,?,?,?,?) }");
//设置输入参数
proc.setString(1,departmentid);
proc.setString(2,examineid);
//注册输出参数类型
proc.registerOutParameter(3,Types.NUMERIC);
proc.registerOutParameter(4,Types.NUMERIC);
proc.registerOutParameter(5,Types.NUMERIC);
proc.registerOutParameter(6,Types.NUMERIC);
proc.registerOutParameter(7,Types.NUMERIC);
//执行存储
proc.execute();报错信息:
java.sql.SQLException: ORA-06550: 第 1 行, 第 7 列:
PLS-00201: 必须说明标识符 'CM_RESULT'
ORA-06550: 第 1 行, 第 7 列:
PL/SQL: Statement ignored
create or replace procedure CM_RESULT
(
DEPARTMENTID in VARCHAR2,
EXAMINEID in VARCHAR2,
RejectDay out NUMBER,
InpermissionDay out NUMBER,
CancellationDay out NUMBER,
CertificateDay out NUMBER,
TotalDay out NUMBER,)is
--条件变量:根据输入参数查询sql语句使用不同的where子句。
condition varchar2(100);begin
--where子句选择
if EXAMINEID <> '''' then
condition := 'and examineid = ' || EXAMINEID || ' group by state';
elsif DEPARTMENTID <> '''' then
condition := 'and deptid = ' || DEPARTMENTID || ' group by state';
else
condition := 'group by state';
end if;--======================================获取当日办结件数==================================
declare type type_cur is ref cursor; --使用动态游标
cursor_day type_cur;
sqlstr_day varchar2(200);
CountTemp number(10);
State number(3);
BEGIN
sqlstr_day := 'select count(id),state from cm_casetimer where completedate > trunc(sysdate) ' || condition;
OPEN cursor_day for sqlstr_day;
LOOP
FETCH cursor_day INTO CountTemp, State;
EXIT WHEN cursor_day%NOTFOUND;
case State
when 4 then
RejectDay := CountTemp;
when 5 then
InpermissionDay := CountTemp;
when 6 then
CancellationDay := CountTemp;
when 3 then
CertificateDay := CountTemp;
else
null;
end case;
END LOOP;
TotalDay := RejectDay + InpermissionDay + CancellationDay + CertificateDay;
CLOSE cursor_day;
END;end CM_RESULT;
java执行代码:
proc = conn.prepareCall("{ Call CM_RESULT(?,?,?,?,?,?,?) }");
//设置输入参数
proc.setString(1,departmentid);
proc.setString(2,examineid);
//注册输出参数类型
proc.registerOutParameter(3,Types.NUMERIC);
proc.registerOutParameter(4,Types.NUMERIC);
proc.registerOutParameter(5,Types.NUMERIC);
proc.registerOutParameter(6,Types.NUMERIC);
proc.registerOutParameter(7,Types.NUMERIC);
//执行存储
proc.execute();报错信息:
java.sql.SQLException: ORA-06550: 第 1 行, 第 7 列:
PLS-00201: 必须说明标识符 'CM_RESULT'
ORA-06550: 第 1 行, 第 7 列:
PL/SQL: Statement ignored
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货