for rec in (select d.id, d.zjjgdm, d.abbrname,d.isarea from t_sys_department d where d.status = '1' and d.isarea = '0' and d.zjjgdm not in ('999999999','007330109','766572053','007329917','457227790' )) loop知道一点原因,这个循环不起作用,这个查询我可以在数据库查询到51条纪录的,用java调用该过程的时候是进不了这个循环,如果在这个语句后加个条件查询一条语句,这个过程又正常了,但是我要结果是51条的不是一条。
这个循环再plsql中是正常的?
我用plsql 测试过程的话 是可以打印51条结果出来的 ,但是用java调用这个过程就不能执行这个过程,在 select d.id, d.zjjgdm, d.abbrname,d.isarea from t_sys_department d where d.status = '1' and d.isarea = '0' and d.zjjgdm not in ('999999999','007330109','766572053','007329917','457227790' ) 这个语句加一个查询出一条的条件 就可以执行这个过程 for rec in (select d.id, d.zjjgdm, d.abbrname,d.isarea from t_sys_department d where d.status = '1' and d.isarea = '0' and d.zjjgdm not in ('999999999','007330109','766572053','007329917','457227790' )) loop 这个有什么问题
create or replace procedure p_zwgk_jxcp(p_year varchar2, p_quarter varchar2) as v_bwzs number(9); --不完整数 v_bwzinfo varchar2(2000);--不完整栏目信息 v_wzx_df number(9, 2); --完整性得分 v_jsx_hups number(9); --及时性黄牌数 v_jsx_hops number(9); --及时性红牌数 v_jsx_df number(9, 2); --及时性得分 v_jsxinfo varchar2(2000);--及时性扣分信息 v_ysq_hups number(9); --依申请黄牌数 v_ysq_hops number(9); --依申请黄牌数 v_ysq_df number(9, 2); --依申请得分 v_ysqinfo varchar2(2000);--依申请扣分信息 v_hps number(9); --好评数 v_eps number(9); --恶评数 v_zps number(9); --总评数 v_myd number(9, 2); --满意度 v_myd_df number(9, 2); --满意度得分 v_xzxxs number(9); --新增信息数 v_xzxx_df number(10, 2); --新增信息得分 v_xzlms number(10, 2); --新增栏目数 v_xzlms_df number(10, 2); --新增栏目数得分 v_xxlpm number(3); --信息量排名 v_xxlsum number(10); --信息量总数 v_xxlpm_df number(9, 2); --信息量排名得分 v_sub_sum number(10,2); --扣分小计 v_zqx_df number(9,2); --准确性扣分 v_jbf number(10,2); --基本分 v_add_sum number(10,2); -- 加分奖励 v_zf number(9, 2); --总得分 v_newid varchar2(30); --主键ID err_code t_sys_sql_err.err_code%type; --错误信息代码 err_text t_sys_sql_err.err_text%type; --错误信息 str_month varchar2(20); --起始月份 end_month varchar2(20); --结束月份 begin begin --先删除存在的数据 delete from t_zwgk_jxcp j where j.cyear=p_year and j.cquarter=p_quarter; --根据部门进行循环计算 --计算所有单位 for rec in (select d.id, d.zjjgdm, d.abbrname,d.isarea from t_sys_department d where d.status = '1' and d.zjjgdm != '999999999' and d.zjjgdm != '007330109' and d.isarea = '0' /*and d.zjjgdm = '007330045'*/) loop dbms_output.put_line('单位名称:' || rec.abbrname || ' 机构代码:' || rec.zjjgdm); /************************** 完整性 ***************************/ select count(1) into v_bwzs from t_jc_zwgk_integrality i where i.organizieuid = rec.zjjgdm and i.isintegrality = '0' and i.status = '1'; --查询出所有的不完整栏目名称 ,循环拼成字符串 for lname in ( select m.explain from t_jc_zwgk_integrality i,t_zwgk_item m where i.organizieuid = rec.zjjgdm and i.zwgkuid = m.zwgkuid and i.isintegrality = '0' and i.status = '1' and m.status = '1' and m.layer <> '1' ) loop v_bwzinfo:= v_bwzinfo || lname.explain ||'*'; end loop; if v_bwzinfo is null then v_bwzinfo:='所有栏目都已公开'; else v_bwzinfo:='*'||v_bwzinfo; end if; -- dbms_output.put_line('栏目info'||v_bwzinfo);
--扣分计算 v_wzx_df := v_bwzs * 3 * -1; if v_wzx_df < -15 then v_wzx_df := -15; end if; -- dbms_output.put_line('绝对值是'||v_wzx_df);
/************************** 及时性 ***************************/ select count(1) into v_jsx_hups --黄牌数 from t_jc_zwgk_busiindex b where b.supervisetype = '2' and b.currentstatus = '3' and b.status = '1' and to_char(b.yellowadvisetime, 'yyyyq') = p_year || p_quarter and b.organizieuid = rec.zjjgdm;
select count(1) into v_jsx_hops --红牌数 from t_jc_zwgk_busiindex b where b.supervisetype = '2' and b.currentstatus = '4' and b.status = '1' and to_char(b.REDADVISETIME, 'yyyyq') = p_year || p_quarter and b.organizieuid = rec.zjjgdm; --扣分计算 v_jsx_df := (v_jsx_hups * 3 + v_jsx_hops * 5)*-1; if v_jsx_df < -30 then v_jsx_df := -30; end if;
/************************** 依申请公开 ***************************/ select count(1) into v_ysq_hups --黄牌数 from t_jc_zwgk_busiindex b where b.supervisetype = '5' and b.currentstatus = '3' and b.status = '1' and to_char(b.yellowadvisetime, 'yyyyq') = p_year || p_quarter and b.organizieuid = rec.zjjgdm;
select count(1) into v_ysq_hops --红牌数 from t_jc_zwgk_busiindex b where b.supervisetype = '5' and b.currentstatus = '4' and b.status = '1' and to_char(b.REDADVISETIME, 'yyyyq') = p_year || p_quarter and b.organizieuid = rec.zjjgdm; --扣分计算 v_ysq_df := (v_ysq_hups * 3 + v_ysq_hops * 5)*-1; if v_ysq_df < -20 then v_ysq_df:=-20; end if;
问题终于解决了谢谢大家,折磨了我一天的bug到头来发现只是个粗心所导致的问题,其实应该也算是oracle的一个小bug 解决方法 select dense_rank() over(order by zs desc) pm, zs, zjjgdm, abbrname from (select count(m.id) zs, d.zjjgdm, d.abbrname from t_sys_department d left join t_zwgk_message m on m.organizieuid = d.zjjgdm and m.status = '1' and d.isarea = '0' where to_char(m.createtime, 'yyyyq')<= p_year || p_quarter group by d.zjjgdm, d.abbrname)) 将where改成and ,这个地方oracle应该要报语法错误才对,居然也编译成功了,看来有时候不能太相信工具。
--个人觉得这样应该是更合理 select dense_rank() over(order by zs desc) pm, zs, zjjgdm, abbrname from (select count(m.id) zs, d.zjjgdm, d.abbrname from t_sys_department d left join t_zwgk_message m on m.organizieuid = d.zjjgdm where m.status = '1' and d.isarea = '0' and to_char(m.createtime, 'yyyyq')<= p_year || p_quarter group by d.zjjgdm, d.abbrname))
CallableStatement call = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
call = conn.prepareCall(proc_sql);
call.setString(1, cyear);
call.setString(2, cquarter);
call.execute();debug调试的话,是正常正常的不会又任何异常产生
call = conn.prepareCall(proc_sql);
call.setString(1, cyear);
call.setString(2, cquarter);
call.execute();
上面的少了代码
from t_sys_department d
where d.status = '1'
and d.isarea = '0'
and d.zjjgdm not in ('999999999','007330109','766572053','007329917','457227790' )) loop知道一点原因,这个循环不起作用,这个查询我可以在数据库查询到51条纪录的,用java调用该过程的时候是进不了这个循环,如果在这个语句后加个条件查询一条语句,这个过程又正常了,但是我要结果是51条的不是一条。
from t_sys_department d
where d.status = '1'
and d.isarea = '0'
and d.zjjgdm not in ('999999999','007330109','766572053','007329917','457227790' ) 这个语句加一个查询出一条的条件 就可以执行这个过程
for rec in (select d.id, d.zjjgdm, d.abbrname,d.isarea
from t_sys_department d
where d.status = '1'
and d.isarea = '0'
and d.zjjgdm not in ('999999999','007330109','766572053','007329917','457227790' )) loop
这个有什么问题
p_quarter varchar2) as
v_bwzs number(9); --不完整数
v_bwzinfo varchar2(2000);--不完整栏目信息
v_wzx_df number(9, 2); --完整性得分
v_jsx_hups number(9); --及时性黄牌数
v_jsx_hops number(9); --及时性红牌数
v_jsx_df number(9, 2); --及时性得分
v_jsxinfo varchar2(2000);--及时性扣分信息
v_ysq_hups number(9); --依申请黄牌数
v_ysq_hops number(9); --依申请黄牌数
v_ysq_df number(9, 2); --依申请得分
v_ysqinfo varchar2(2000);--依申请扣分信息
v_hps number(9); --好评数
v_eps number(9); --恶评数
v_zps number(9); --总评数
v_myd number(9, 2); --满意度
v_myd_df number(9, 2); --满意度得分
v_xzxxs number(9); --新增信息数
v_xzxx_df number(10, 2); --新增信息得分
v_xzlms number(10, 2); --新增栏目数
v_xzlms_df number(10, 2); --新增栏目数得分
v_xxlpm number(3); --信息量排名
v_xxlsum number(10); --信息量总数
v_xxlpm_df number(9, 2); --信息量排名得分
v_sub_sum number(10,2); --扣分小计
v_zqx_df number(9,2); --准确性扣分
v_jbf number(10,2); --基本分
v_add_sum number(10,2); -- 加分奖励
v_zf number(9, 2); --总得分
v_newid varchar2(30); --主键ID
err_code t_sys_sql_err.err_code%type; --错误信息代码
err_text t_sys_sql_err.err_text%type; --错误信息
str_month varchar2(20); --起始月份
end_month varchar2(20); --结束月份
begin
begin
--先删除存在的数据
delete from t_zwgk_jxcp j where j.cyear=p_year and j.cquarter=p_quarter;
--根据部门进行循环计算
--计算所有单位
for rec in (select d.id, d.zjjgdm, d.abbrname,d.isarea
from t_sys_department d
where d.status = '1'
and d.zjjgdm != '999999999'
and d.zjjgdm != '007330109'
and d.isarea = '0'
/*and d.zjjgdm = '007330045'*/) loop
dbms_output.put_line('单位名称:' || rec.abbrname || ' 机构代码:' ||
rec.zjjgdm);
/**************************
完整性
***************************/
select count(1)
into v_bwzs
from t_jc_zwgk_integrality i
where i.organizieuid = rec.zjjgdm
and i.isintegrality = '0'
and i.status = '1';
--查询出所有的不完整栏目名称 ,循环拼成字符串
for lname in ( select m.explain
from t_jc_zwgk_integrality i,t_zwgk_item m
where i.organizieuid = rec.zjjgdm
and i.zwgkuid = m.zwgkuid
and i.isintegrality = '0'
and i.status = '1'
and m.status = '1' and m.layer <> '1' ) loop
v_bwzinfo:= v_bwzinfo || lname.explain ||'*';
end loop;
if v_bwzinfo is null then
v_bwzinfo:='所有栏目都已公开';
else
v_bwzinfo:='*'||v_bwzinfo;
end if;
-- dbms_output.put_line('栏目info'||v_bwzinfo);
--扣分计算
v_wzx_df := v_bwzs * 3 * -1;
if v_wzx_df < -15 then
v_wzx_df := -15;
end if;
-- dbms_output.put_line('绝对值是'||v_wzx_df);
-- dbms_output.put_line('完整数=' || v_bwzs || '完整性得分=' || v_wzx_df);
/**************************
及时性
***************************/
select count(1)
into v_jsx_hups --黄牌数
from t_jc_zwgk_busiindex b
where b.supervisetype = '2'
and b.currentstatus = '3'
and b.status = '1'
and to_char(b.yellowadvisetime, 'yyyyq') = p_year || p_quarter
and b.organizieuid = rec.zjjgdm;
select count(1)
into v_jsx_hops --红牌数
from t_jc_zwgk_busiindex b
where b.supervisetype = '2'
and b.currentstatus = '4'
and b.status = '1'
and to_char(b.REDADVISETIME, 'yyyyq') = p_year || p_quarter
and b.organizieuid = rec.zjjgdm;
--扣分计算
v_jsx_df := (v_jsx_hups * 3 + v_jsx_hops * 5)*-1;
if v_jsx_df < -30 then
v_jsx_df := -30;
end if;
v_jsxinfo:= '及时性 黄牌数:'||v_jsx_hups||' 每张扣3分,'||' 红牌数:'||v_jsx_hops||' 每张扣5分';
dbms_output.put_line('及时性扣分信息'||v_jsxinfo);
-- dbms_output.put_line('及时:黄牌数=' || v_jsx_hups || ' 红牌数=' ||
-- v_jsx_hops || ' 得分=' || v_jsx_df);
/**************************
准确性---预留
***************************/
/**************************
依申请公开
***************************/
select count(1)
into v_ysq_hups --黄牌数
from t_jc_zwgk_busiindex b
where b.supervisetype = '5'
and b.currentstatus = '3'
and b.status = '1'
and to_char(b.yellowadvisetime, 'yyyyq') = p_year || p_quarter
and b.organizieuid = rec.zjjgdm;
select count(1)
into v_ysq_hops --红牌数
from t_jc_zwgk_busiindex b
where b.supervisetype = '5'
and b.currentstatus = '4'
and b.status = '1'
and to_char(b.REDADVISETIME, 'yyyyq') = p_year || p_quarter
and b.organizieuid = rec.zjjgdm;
--扣分计算
v_ysq_df := (v_ysq_hups * 3 + v_ysq_hops * 5)*-1;
if v_ysq_df < -20 then
v_ysq_df:=-20;
end if;
v_ysqinfo := '依申请 黄牌数:'||v_ysq_hups||' 每张扣3分,'||' 红牌数:'||v_ysq_hops||' 每张扣5分';
dbms_output.put_line('依申请扣分信息'||v_ysqinfo);
-- dbms_output.put_line('依申请公开:黄牌数=' || v_ysq_hups || ' 红牌数=' ||
-- v_ysq_hops || ' 得分=' || v_ysq_df);
/**************************
满意度
***************************/
-- dbms_output.put_line('季度 ' || p_quarter);
if p_quarter = '1' then
str_month := p_year||'-01-01';
end_month := p_year||'-04-01';
elsif p_quarter = '2' then
str_month := p_year||'-04-01';
end_month := p_year||'-07-01';
elsif p_quarter = '3' then
str_month := p_year||'-07-01';
end_month := p_year||'-10-01';
else
str_month := p_year||'-10-01';
end_month := to_char(to_number(p_year)+1)||'-01-01';
end if;
--dbms_output.put_line('开始月份 '||str_month ||' '||'结束月份' || end_month || '加一年后的年份'||to_char(to_number(p_year)+1));
select ( select count(*)
from t_zwgk_research tr1, t_zwgk_message tm1
where tr1.originaluid = tm1.originaluid
and tm1.organizieuid = d.zjjgdm
and tr1.status = 1
and tm1.status = 1
and submittime between
to_date(str_month, 'yyyy-mm-dd') and
to_date(end_month, 'yyyy-mm-dd')
and tr1.isapproveof = 1) good,
(select count(*)
from t_zwgk_research tr2, t_zwgk_message tm2
where tr2.originaluid = tm2.originaluid
and tm2.organizieuid = d.zjjgdm
and tr2.status = 1
and tm2.status = 1
and submittime between
to_date(str_month, 'yyyy-mm-dd') and
to_date(end_month, 'yyyy-mm-dd')
and tr2.isapproveof = 0) no_good
into v_hps, v_eps
from t_sys_department d
where d.zjjgdm <> '999999999'
and d.zjjgdm = rec.zjjgdm;
v_zps := v_hps + v_eps;
dbms_output.put_line('满意度:总评数=' || v_zps);
--计算满意度
if v_zps = 0 then
v_myd := 100; --如果总评数为0时,满意度默认为100
else
v_myd := (v_hps / v_zps) * 100;
end if;
dbms_output.put_line('满意度=' || v_myd);
--计算满意度得分
if v_myd >= 90 then
v_myd_df := 0; --当 >=90 不扣分,即:得满分15分 --改成要扣的分数
elsif v_myd < 90 and v_myd >= 80 then
v_myd_df := -5; --当 <90 并且 >=80 时,黄牌扣 5 分
elsif v_myd < 80 then
v_myd_df := -10; --当 <80 时,红牌扣 10 分
end if;
dbms_output.put_line('满意度=' || v_myd);
dbms_output.put_line('满意度:好评数=' || v_hps || ' 恶评数=' || v_eps ||
' 得分=' || v_myd_df);
/**************************
新增信息量
***************************/
select count(1)
into v_xzxxs
from t_zwgk_message m
where to_char(m.createtime, 'yyyyq') = p_year || p_quarter
and m.status = '1'
and m.organizieuid = rec.zjjgdm;
dbms_output.put_line('新增信息数=' || v_xzxxs);
--加分计算
v_xzxx_df := v_xzxxs * 0.1;
if v_xzxx_df > 12 then
--如果大于13分,计13分
v_xzxx_df := 12;
elsif v_xzxx_df < 0 then
--如果小于0分,计0分
v_xzxx_df := 0;
end if;
dbms_output.put_line('新增信息量:s新增数=' || v_xzxxs || ' 新增得分=' ||
v_xzxx_df);
/**************************
公开信息量排名
***************************/
select pm,
decode(pm,
1,
4,
2,
3.6,
3,
3.2,
4,
2.8,
5,
2.4,
6,
2.0,
7,
1.6,
8,
1.2,
9,
0.8,
10,
0.4,
0) df,
zs
into v_xxlpm, v_xxlpm_df ,v_xxlsum
from (select dense_rank() over(order by zs desc) pm,
zs,
zjjgdm,
abbrname
from (select count(m.id) zs, d.zjjgdm, d.abbrname
from t_sys_department d
left join t_zwgk_message m on m.organizieuid =
d.zjjgdm
and m.status = '1' and d.isarea = '0'
where to_char(m.createtime, 'yyyyq')<= p_year || p_quarter
group by d.zjjgdm, d.abbrname))
where zjjgdm = rec.zjjgdm;
dbms_output.put_line('信息量排名:排名=' || v_xxlpm || ' 得分=' || v_xxlpm_df);
--如果为null或者小于0时,当作0分
if v_xxlpm_df is null or v_xxlpm_df < 0 then
v_xxlpm_df := 0;
end if;
dbms_output.put_line('信息量排名:排名=' || v_xxlpm || ' 得分=' || v_xxlpm_df);
/**************************
扣分小计
***************************/
if v_zqx_df is null then
v_zqx_df := 0;
end if;
v_sub_sum := v_wzx_df + v_jsx_df + v_ysq_df + v_myd_df + v_zqx_df;
dbms_output.put_line('扣分小计=' || v_sub_sum);
/*********************
计算加分奖励总分
*********************/
if v_xzlms_df is null then
v_xzlms_df :=0;
end if;
v_add_sum := v_xzxx_df + v_xzlms_df + v_xxlpm_df;
dbms_output.put_line('加分奖励=' || v_add_sum);
/**************************
计算得分
***************************/
--基本分
v_jbf := 100 + v_sub_sum;
--总分
v_zf := v_jbf + v_add_sum;
dbms_output.put_line('总分=' || v_zf);
-- dbms_output.put_line('总得分=' || v_zf);
--dbms_output.put_line('加分奖励=' || v_xzxx_df);
--dbms_output.put_line('加分奖励=' || v_xzlms_df);
--dbms_output.put_line('加分奖励=' || v_xxlpm_df);
--dbms_output.put_line('加分奖励=' || v_add_sum);
/*******************************************
将结果分数插入 t_zwgk_jxcp 表
*******************************************/
v_newid := func_getnewid(20);
insert into t_zwgk_jxcp
(ID,
DEPTZZJGDM,
DEPTNAME,
ZDF,
WZX,
JSX,
YSQGK,
MYD,
XZXXL,
ZXXLPM,
CDATE,
CYEAR,
CMONTH,
CQUARTER,
XZXXLS,
GKXXLS,
JFJL,
KFXJ,
JBF,
WZXINFO,
JSXINFO,
YSQINFO,
bya)
values
(v_newid,
rec.zjjgdm,
rec.abbrname,
v_zf,
v_wzx_df,
v_jsx_df,
v_ysq_df,
v_myd_df,
v_xzxx_df,
v_xxlpm_df,
sysdate,
p_year,
'',
p_quarter,
v_xzxxs,
v_xxlsum,
v_add_sum,
v_sub_sum,
v_jbf,
v_bwzinfo,
v_jsxinfo,
v_ysqinfo,
rec.isarea);
dbms_output.put_line('插入完成');
dbms_output.put_line('栏目info'||v_bwzinfo);
v_bwzinfo:='';
end loop;
exception
when others then
ROLLBACK; /* 回滚事务 */
err_code := sqlcode;
err_text := sqlerrm;
INSERT INTO T_SYS_SQL_ERR /* 记录错误信息到 t_sys_sql_err */
(ID, MODEL_NAME, ERR_CODE, ERR_TEXT, TIME, OTHERS)
VALUES
(SEQ_SQL_ERR.nextval,
'p_zwgk_jxcp:执行存储过程-政务公开绩效测评错误。',
err_code,
err_text,
sysdate,
'政务公开绩效测评');
end;
commit;
end p_zwgk_jxcp;
select d.id, d.zjjgdm, d.abbrname,d.isarea
from t_sys_department d
where d.status = '1'
and d.zjjgdm != '999999999'
and d.zjjgdm != '007330109'
and d.isarea = '0'
/*and d.zjjgdm = '007330045'*/
里面加上/*and d.zjjgdm = '007330045'*/就有一条数据,不加就没有数据吗?
and d.zjjgdm != '999999999'
and d.zjjgdm != '007330109'
select dense_rank() over(order by zs desc) pm,
zs,
zjjgdm,
abbrname
from (select count(m.id) zs, d.zjjgdm, d.abbrname
from t_sys_department d
left join t_zwgk_message m on m.organizieuid =
d.zjjgdm
and m.status = '1' and d.isarea = '0'
where to_char(m.createtime, 'yyyyq')<= p_year || p_quarter
group by d.zjjgdm, d.abbrname))
将where改成and ,这个地方oracle应该要报语法错误才对,居然也编译成功了,看来有时候不能太相信工具。
select dense_rank() over(order by zs desc) pm,
zs,
zjjgdm,
abbrname
from (select count(m.id) zs, d.zjjgdm, d.abbrname
from t_sys_department d
left join t_zwgk_message m on m.organizieuid =
d.zjjgdm
where m.status = '1' and d.isarea = '0'
and to_char(m.createtime, 'yyyyq')<= p_year || p_quarter
group by d.zjjgdm, d.abbrname))
on是你的join条件,where是整个join后的查询视图的条件。oracle怎么知道你哪些是连接条件哪些是查询条件?
明明是你自己粗心就不要怪到工具上头啦。这个根本没有语法错误!