Oracle存储中将一个count返回值给变量负值负不上，在线求答
存储如下：
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;
