Oracle存储中将一个count返回值给变量负值负不上，在线求答
存储如下：
- SQL code
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，为什么呢？已经纠结一天了，求救！！！！！
[解决办法]
程序里结果它就是0，只能说程序的语句跟你select出10条的语句不是同一条
[解决办法]
我好久不做oracle，也可能说错
条件这个地方我感觉你可以改一下
and c.field_Value = '1'||'2'||'3'||'4'||'5'||'6'
如果查询出来的结果有的话，是不是
ceil(v_num * problem_type_qt_percentage)判断之后你执行别的存储过程了
你需要看一下PROC_SHEET_H1_LOGO_NEW 这个存储过程。
说明一下：我需要下载点东西好久不来，分特别少，就差几分下不了，
楼主结贴的时候记得稍稍送点分给我，多谢了。
[解决办法]