oracle 判断负值,Oracle存储中将一个count返回值给变量负值负不上，在线求答,该如何处理...

SQL codecreate 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;