存储如下:create or replace procedure proc_Sheet_Logo_prcce_ToJob(nowTime in varchar2) as
problem_type_id VARCHAR2(50);
problem_type_3g_percentage VARCHAR2(50);
problem_type_2g_percentage VARCHAR2(50);
problem_type_gh_percentage VARCHAR2(50);
problem_type_kd_percentage VARCHAR2(50);
problem_type_rh_percentage VARCHAR2(50);
problem_type_qt_percentage VARCHAR2(50);
v_num number;
--nowTime varchar2(50);
cursor prob_cur is
select f.problem_type_id,
f.problem_type_3g_percentage,
f.problem_type_2g_percentage,
f.problem_type_gh_percentage,
f.problem_type_kd_percentage,
f.problem_type_rh_percentage,
f.problem_type_qt_percentage
from tbl_upload_h1 f, tbl_sheet_problem_tree p
where f.problem_type_id = p.problem_type_id
and p.is_leaf = '1';
begin
-- nowTime := to_char(sysdate-1,'yyyy-MM-dd');
open prob_cur;
loop
fetch prob_cur
into problem_type_id,
problem_type_3g_percentage,
problem_type_2g_percentage,
problem_type_gh_percentage,
problem_type_kd_percentage,
problem_type_rh_percentage,
problem_type_qt_percentage;
select count(distinct m.main_sheet_flow_no)
into v_num
from tbl_main_sheet m
where m.deal_time >= nowTime || ' 00:00:00'
and m.deal_time <= nowTime || ' 23:59:59'
and m.sheet_category_id = '1'
and m.main_status <> 25
and exists (select 1
from tbl_sheet_content_field c
where m.main_sheet_flow_no = c.sheet_flow_no
and c.field_Code like 'complainTarget%'
and c.field_Value = problem_type_id)
and exists (select 1
from tbl_sheet_content_field c
where m.main_sheet_flow_no = c.sheet_flow_no
and c.field_Code like 'anlyBusinessType%'
and c.field_Value = '1');
if (ceil(v_num * problem_type_3g_percentage) > 0) then
PROC_SHEET_H1_LOGO_NEW(nowTime || ' 00:00:00',
nowTime || ' 23:59:59',
problem_type_id,
problem_type_3g_percentage,
ceil(v_num * problem_type_3g_percentage));
end if; select nvl(count(distinct m.main_sheet_flow_no), 0)
into v_num
from tbl_main_sheet m
where m.deal_time >= nowTime || ' 00:00:00'
and m.deal_time <= nowTime || ' 23:59:59'
and m.sheet_category_id = '1'
and m.main_status <> 25
and exists (select 1
from tbl_sheet_content_field c
where m.main_sheet_flow_no = c.sheet_flow_no
and c.field_Code like 'complainTarget%'
and c.field_Value = problem_type_id)
and exists (select 1
from tbl_sheet_content_field c
where m.main_sheet_flow_no = c.sheet_flow_no
and c.field_Code like 'anlyBusinessType%'
and c.field_Value = '2');
if (ceil(v_num * problem_type_2g_percentage) > 0) then
PROC_SHEET_H1_LOGO_NEW(nowTime || ' 00:00:00',
nowTime || ' 23:59:59',
problem_type_id,
problem_type_2g_percentage,
ceil(v_num * problem_type_2g_percentage));
end if; select nvl(count(distinct m.main_sheet_flow_no), 0)
into v_num
from tbl_main_sheet m
where m.deal_time >= nowTime || ' 00:00:00'
and m.deal_time <= nowTime || ' 23:59:59'
and m.sheet_category_id = '1'
and m.main_status <> 25
and exists (select 1
from tbl_sheet_content_field c
where m.main_sheet_flow_no = c.sheet_flow_no
and c.field_Code like 'complainTarget%'
and c.field_Value = problem_type_id)
and exists (select 1
from tbl_sheet_content_field c
where m.main_sheet_flow_no = c.sheet_flow_no
and c.field_Code like 'anlyBusinessType%'
and c.field_Value = '3');
if (ceil(v_num * problem_type_gh_percentage) > 0) then
PROC_SHEET_H1_LOGO_NEW(nowTime || ' 00:00:00',
nowTime || ' 23:59:59',
problem_type_id,
problem_type_gh_percentage,
ceil(v_num * problem_type_gh_percentage));
end if; select nvl(count(distinct m.main_sheet_flow_no), 0)
into v_num
from tbl_main_sheet m
where m.deal_time >= nowTime || ' 00:00:00'
and m.deal_time <= nowTime || ' 23:59:59'
and m.sheet_category_id = '1'
and m.main_status <> 25
and exists (select 1
from tbl_sheet_content_field c
where m.main_sheet_flow_no = c.sheet_flow_no
and c.field_Code like 'complainTarget%'
and c.field_Value = problem_type_id)
and exists (select 1
from tbl_sheet_content_field c
where m.main_sheet_flow_no = c.sheet_flow_no
and c.field_Code like 'anlyBusinessType%'
and c.field_Value = '4');
if (ceil(v_num * problem_type_kd_percentage) > 0) then
PROC_SHEET_H1_LOGO_NEW(nowTime || ' 00:00:00',
nowTime || ' 23:59:59',
problem_type_id,
problem_type_kd_percentage,
ceil(v_num * problem_type_kd_percentage));
end if; select nvl(count(distinct m.main_sheet_flow_no), 0)
into v_num
from tbl_main_sheet m
where m.deal_time >= nowTime || ' 00:00:00'
and m.deal_time <= nowTime || ' 23:59:59'
and m.sheet_category_id = '1'
and m.main_status <> 25
and exists (select 1
from tbl_sheet_content_field c
where m.main_sheet_flow_no = c.sheet_flow_no
and c.field_Code like 'complainTarget%'
and c.field_Value = problem_type_id)
and exists (select 1
from tbl_sheet_content_field c
where m.main_sheet_flow_no = c.sheet_flow_no
and c.field_Code like 'anlyBusinessType%'
and c.field_Value = '5');
if (ceil(v_num * problem_type_rh_percentage) > 0) then
PROC_SHEET_H1_LOGO_NEW(nowTime || ' 00:00:00',
nowTime || ' 23:59:59',
problem_type_id,
problem_type_rh_percentage,
ceil(v_num * problem_type_rh_percentage));
end if; select nvl(count(distinct m.main_sheet_flow_no), 0)
into v_num
from tbl_main_sheet m
where m.deal_time >= nowTime || ' 00:00:00'
and m.deal_time <= nowTime || ' 23:59:59'
and m.sheet_category_id = '1'
and m.main_status <> 25
and exists (select 1
from tbl_sheet_content_field c
where m.main_sheet_flow_no = c.sheet_flow_no
and c.field_Code like 'complainTarget%'
and c.field_Value = problem_type_id)
and exists (select 1
from tbl_sheet_content_field c
where m.main_sheet_flow_no = c.sheet_flow_no
and c.field_Code like 'anlyBusinessType%'
and c.field_Value = '6');
if (ceil(v_num * problem_type_qt_percentage) > 0) then
PROC_SHEET_H1_LOGO_NEW(nowTime || ' 00:00:00',
nowTime || ' 23:59:59',
problem_type_id,
problem_type_qt_percentage,
ceil(v_num * problem_type_3g_percentage));
end if;
exit when prob_cur%NOTFOUND;
end loop;
end proc_Sheet_Logo_prcce_ToJob;
调试发现,v_num的值等于0,为什么呢?已经纠结一天了,求救!!!!!
problem_type_id VARCHAR2(50);
problem_type_3g_percentage VARCHAR2(50);
problem_type_2g_percentage VARCHAR2(50);
problem_type_gh_percentage VARCHAR2(50);
problem_type_kd_percentage VARCHAR2(50);
problem_type_rh_percentage VARCHAR2(50);
problem_type_qt_percentage VARCHAR2(50);
v_num number;
--nowTime varchar2(50);
cursor prob_cur is
select f.problem_type_id,
f.problem_type_3g_percentage,
f.problem_type_2g_percentage,
f.problem_type_gh_percentage,
f.problem_type_kd_percentage,
f.problem_type_rh_percentage,
f.problem_type_qt_percentage
from tbl_upload_h1 f, tbl_sheet_problem_tree p
where f.problem_type_id = p.problem_type_id
and p.is_leaf = '1';
begin
-- nowTime := to_char(sysdate-1,'yyyy-MM-dd');
open prob_cur;
loop
fetch prob_cur
into problem_type_id,
problem_type_3g_percentage,
problem_type_2g_percentage,
problem_type_gh_percentage,
problem_type_kd_percentage,
problem_type_rh_percentage,
problem_type_qt_percentage;
select count(distinct m.main_sheet_flow_no)
into v_num
from tbl_main_sheet m
where m.deal_time >= nowTime || ' 00:00:00'
and m.deal_time <= nowTime || ' 23:59:59'
and m.sheet_category_id = '1'
and m.main_status <> 25
and exists (select 1
from tbl_sheet_content_field c
where m.main_sheet_flow_no = c.sheet_flow_no
and c.field_Code like 'complainTarget%'
and c.field_Value = problem_type_id)
and exists (select 1
from tbl_sheet_content_field c
where m.main_sheet_flow_no = c.sheet_flow_no
and c.field_Code like 'anlyBusinessType%'
and c.field_Value = '1');
if (ceil(v_num * problem_type_3g_percentage) > 0) then
PROC_SHEET_H1_LOGO_NEW(nowTime || ' 00:00:00',
nowTime || ' 23:59:59',
problem_type_id,
problem_type_3g_percentage,
ceil(v_num * problem_type_3g_percentage));
end if; select nvl(count(distinct m.main_sheet_flow_no), 0)
into v_num
from tbl_main_sheet m
where m.deal_time >= nowTime || ' 00:00:00'
and m.deal_time <= nowTime || ' 23:59:59'
and m.sheet_category_id = '1'
and m.main_status <> 25
and exists (select 1
from tbl_sheet_content_field c
where m.main_sheet_flow_no = c.sheet_flow_no
and c.field_Code like 'complainTarget%'
and c.field_Value = problem_type_id)
and exists (select 1
from tbl_sheet_content_field c
where m.main_sheet_flow_no = c.sheet_flow_no
and c.field_Code like 'anlyBusinessType%'
and c.field_Value = '2');
if (ceil(v_num * problem_type_2g_percentage) > 0) then
PROC_SHEET_H1_LOGO_NEW(nowTime || ' 00:00:00',
nowTime || ' 23:59:59',
problem_type_id,
problem_type_2g_percentage,
ceil(v_num * problem_type_2g_percentage));
end if; select nvl(count(distinct m.main_sheet_flow_no), 0)
into v_num
from tbl_main_sheet m
where m.deal_time >= nowTime || ' 00:00:00'
and m.deal_time <= nowTime || ' 23:59:59'
and m.sheet_category_id = '1'
and m.main_status <> 25
and exists (select 1
from tbl_sheet_content_field c
where m.main_sheet_flow_no = c.sheet_flow_no
and c.field_Code like 'complainTarget%'
and c.field_Value = problem_type_id)
and exists (select 1
from tbl_sheet_content_field c
where m.main_sheet_flow_no = c.sheet_flow_no
and c.field_Code like 'anlyBusinessType%'
and c.field_Value = '3');
if (ceil(v_num * problem_type_gh_percentage) > 0) then
PROC_SHEET_H1_LOGO_NEW(nowTime || ' 00:00:00',
nowTime || ' 23:59:59',
problem_type_id,
problem_type_gh_percentage,
ceil(v_num * problem_type_gh_percentage));
end if; select nvl(count(distinct m.main_sheet_flow_no), 0)
into v_num
from tbl_main_sheet m
where m.deal_time >= nowTime || ' 00:00:00'
and m.deal_time <= nowTime || ' 23:59:59'
and m.sheet_category_id = '1'
and m.main_status <> 25
and exists (select 1
from tbl_sheet_content_field c
where m.main_sheet_flow_no = c.sheet_flow_no
and c.field_Code like 'complainTarget%'
and c.field_Value = problem_type_id)
and exists (select 1
from tbl_sheet_content_field c
where m.main_sheet_flow_no = c.sheet_flow_no
and c.field_Code like 'anlyBusinessType%'
and c.field_Value = '4');
if (ceil(v_num * problem_type_kd_percentage) > 0) then
PROC_SHEET_H1_LOGO_NEW(nowTime || ' 00:00:00',
nowTime || ' 23:59:59',
problem_type_id,
problem_type_kd_percentage,
ceil(v_num * problem_type_kd_percentage));
end if; select nvl(count(distinct m.main_sheet_flow_no), 0)
into v_num
from tbl_main_sheet m
where m.deal_time >= nowTime || ' 00:00:00'
and m.deal_time <= nowTime || ' 23:59:59'
and m.sheet_category_id = '1'
and m.main_status <> 25
and exists (select 1
from tbl_sheet_content_field c
where m.main_sheet_flow_no = c.sheet_flow_no
and c.field_Code like 'complainTarget%'
and c.field_Value = problem_type_id)
and exists (select 1
from tbl_sheet_content_field c
where m.main_sheet_flow_no = c.sheet_flow_no
and c.field_Code like 'anlyBusinessType%'
and c.field_Value = '5');
if (ceil(v_num * problem_type_rh_percentage) > 0) then
PROC_SHEET_H1_LOGO_NEW(nowTime || ' 00:00:00',
nowTime || ' 23:59:59',
problem_type_id,
problem_type_rh_percentage,
ceil(v_num * problem_type_rh_percentage));
end if; select nvl(count(distinct m.main_sheet_flow_no), 0)
into v_num
from tbl_main_sheet m
where m.deal_time >= nowTime || ' 00:00:00'
and m.deal_time <= nowTime || ' 23:59:59'
and m.sheet_category_id = '1'
and m.main_status <> 25
and exists (select 1
from tbl_sheet_content_field c
where m.main_sheet_flow_no = c.sheet_flow_no
and c.field_Code like 'complainTarget%'
and c.field_Value = problem_type_id)
and exists (select 1
from tbl_sheet_content_field c
where m.main_sheet_flow_no = c.sheet_flow_no
and c.field_Code like 'anlyBusinessType%'
and c.field_Value = '6');
if (ceil(v_num * problem_type_qt_percentage) > 0) then
PROC_SHEET_H1_LOGO_NEW(nowTime || ' 00:00:00',
nowTime || ' 23:59:59',
problem_type_id,
problem_type_qt_percentage,
ceil(v_num * problem_type_3g_percentage));
end if;
exit when prob_cur%NOTFOUND;
end loop;
end proc_Sheet_Logo_prcce_ToJob;
调试发现,v_num的值等于0,为什么呢?已经纠结一天了,求救!!!!!
条件这个地方我感觉你可以改一下
and c.field_Value = '1'||'2'||'3'||'4'||'5'||'6'
如果查询出来的结果有的话,是不是
ceil(v_num * problem_type_qt_percentage)判断之后你执行别的存储过程了
你需要看一下PROC_SHEET_H1_LOGO_NEW 这个存储过程。
说明一下:我需要下载点东西好久不来,分特别少,就差几分下不了,
楼主结贴的时候记得稍稍送点分给我,多谢了。