select office_name,sales_incharge_name,article_no,article_name,process_location,sale_yearmonth,
customer_cd,customer_info,
contract_section,customer_type,order_no,sale_amount_notax,
purchase_amount_notax,
income_amount_notax,
sum_request_amount_notax,sum_income_amount_notax ,
F_GetRemainAmount_ORDERNO('200609','200609','200609',order_no,sum_request_amount_notax,sum_income_amount_notax,office_name,sales_incharge_name,article_no,article_name,process_location,sale_yearmonth,
customer_cd,customer_info,
contract_section,customer_type,'1') one,
F_GetRemainAmount_ORDERNO('200609','200609','200609',order_no,sum_request_amount_notax,sum_income_amount_notax,office_name,sales_incharge_name,article_no,article_name,process_location,sale_yearmonth,
customer_cd,customer_info,
contract_section,customer_type,'0') two,
F_GetRemainAmount_ORDERNO('200609','200608','200608',order_no,sum_request_amount_notax,sum_income_amount_notax,office_name,sales_incharge_name,article_no,article_name,process_location,sale_yearmonth,
customer_cd,customer_info,
contract_section,customer_type,'0') three,
F_GetRemainAmount_ORDERNO('200609','200607','200607',order_no,sum_request_amount_notax,sum_income_amount_notax,office_name,sales_incharge_name,article_no,article_name,process_location,sale_yearmonth,
customer_cd,customer_info,
contract_section,customer_type,'0') four,
F_GetRemainAmount_ORDERNO('200609','200604','200606',order_no,sum_request_amount_notax,sum_income_amount_notax,office_name,sales_incharge_name,article_no,article_name,process_location,sale_yearmonth,
customer_cd,customer_info,
contract_section,customer_type,'0') five,
F_GetRemainAmount_ORDERNO('200609','200510','200603',order_no,sum_request_amount_notax,sum_income_amount_notax,office_name,sales_incharge_name,article_no,article_name,process_location,sale_yearmonth,
customer_cd,customer_info,
contract_section,customer_type,'0') six,
F_GetRemainAmount_ORDERNO('200609','200410','200509',order_no,sum_request_amount_notax,sum_income_amount_notax,office_name,sales_incharge_name,article_no,article_name,process_location,sale_yearmonth,
customer_cd,customer_info,
contract_section,customer_type,'0') seven,
F_GetRemainAmount_ORDERNO('200609','198008','200409',order_no,sum_request_amount_notax,sum_income_amount_notax,office_name,sales_incharge_name,article_no,article_name,process_location,sale_yearmonth,
customer_cd,customer_info,
contract_section,customer_type,'0') eight
from test问题：
1、前100条记录 one、two、three、four、five、six、seven、eight 8列返回值都是正确的，
从100条记录后，返回的数据都为0了，没有正常返回
急，，，，，，，，，，分不够再加
customer_cd,customer_info,
contract_section,customer_type,order_no,sale_amount_notax,
purchase_amount_notax,
income_amount_notax,
sum_request_amount_notax,sum_income_amount_notax ,
F_GetRemainAmount_ORDERNO('200609','200609','200609',order_no,sum_request_amount_notax,sum_income_amount_notax,office_name,sales_incharge_name,article_no,article_name,process_location,sale_yearmonth,
customer_cd,customer_info,
contract_section,customer_type,'1') one,
F_GetRemainAmount_ORDERNO('200609','200609','200609',order_no,sum_request_amount_notax,sum_income_amount_notax,office_name,sales_incharge_name,article_no,article_name,process_location,sale_yearmonth,
customer_cd,customer_info,
contract_section,customer_type,'0') two,
F_GetRemainAmount_ORDERNO('200609','200608','200608',order_no,sum_request_amount_notax,sum_income_amount_notax,office_name,sales_incharge_name,article_no,article_name,process_location,sale_yearmonth,
customer_cd,customer_info,
contract_section,customer_type,'0') three,
F_GetRemainAmount_ORDERNO('200609','200607','200607',order_no,sum_request_amount_notax,sum_income_amount_notax,office_name,sales_incharge_name,article_no,article_name,process_location,sale_yearmonth,
customer_cd,customer_info,
contract_section,customer_type,'0') four,
F_GetRemainAmount_ORDERNO('200609','200604','200606',order_no,sum_request_amount_notax,sum_income_amount_notax,office_name,sales_incharge_name,article_no,article_name,process_location,sale_yearmonth,
customer_cd,customer_info,
contract_section,customer_type,'0') five,
F_GetRemainAmount_ORDERNO('200609','200510','200603',order_no,sum_request_amount_notax,sum_income_amount_notax,office_name,sales_incharge_name,article_no,article_name,process_location,sale_yearmonth,
customer_cd,customer_info,
contract_section,customer_type,'0') six,
F_GetRemainAmount_ORDERNO('200609','200410','200509',order_no,sum_request_amount_notax,sum_income_amount_notax,office_name,sales_incharge_name,article_no,article_name,process_location,sale_yearmonth,
customer_cd,customer_info,
contract_section,customer_type,'0') seven,
F_GetRemainAmount_ORDERNO('200609','198008','200409',order_no,sum_request_amount_notax,sum_income_amount_notax,office_name,sales_incharge_name,article_no,article_name,process_location,sale_yearmonth,
customer_cd,customer_info,
contract_section,customer_type,'0') eight
from test问题：
1、前100条记录 one、two、three、four、five、six、seven、eight 8列返回值都是正确的，
从100条记录后，返回的数据都为0了，没有正常返回
急，，，，，，，，，，分不够再加
===============================================================================
函数 F_GetRemainAmount_ORDERNOCREATE OR REPLACE FUNCTION F_GetRemainAmount_ORDERNO(
v_date in varchar2,
v_start_date in varchar2,
v_end_date in varchar2,
v_order_no in varchar2,
v_sum_request_amount in number,
v_sum_income_amount in number,
v_office_name in varchar2,
v_sales_incharge_name in varchar2,
v_article_no in varchar2,
v_article_name in varchar2,
v_process_location in varchar2,
v_sale_yearmonth in varchar2,
v_customer_cd in varchar2,
v_customer_info in varchar2,
v_contract_section in varchar2,
v_customer_type in varchar2,
v_flag in varchar2)
RETURN number IS --v_sum_request_amount number;
--v_sum_income_amount number;
v_curr_date_income number;
v_curr_date_request number;
v_tmp_amount number;begin /*
v_sum_request_amount := F_GetMonthAmount(v_date,' ', ' ',v_order_no,v_office_name,v_sales_incharge_name,v_article_no,
v_article_name,v_process_location,v_sale_yearmonth,v_customer_cd,
v_customer_info,v_contract_section,v_customer_type,'SUM_REQUEST_AMOUNT'); v_sum_income_amount := F_GetMonthAmount(v_date,' ', ' ',v_order_no,v_office_name,v_sales_incharge_name,v_article_no,
v_article_name,v_process_location,v_sale_yearmonth,v_customer_cd,
v_customer_info,v_contract_section,v_customer_type,'SUM_INCOME_AMOUNT');
*/
IF ROUND(v_sum_request_amount - v_sum_income_amount,2) = 0 THEN
RETURN 0;
END IF; IF ROUND(v_sum_request_amount - v_sum_income_amount,2) > 0 THEN
if v_flag = '1' then
SELECT NVL(SUM(request_amount_notax),0)
INTO v_curr_date_request
FROM v_month_article_customer_list
WHERE sale_date <= v_date AND pre_income_date > v_date AND office_name = v_office_name AND
sales_incharge_name = v_sales_incharge_name AND article_no = v_article_no AND
process_location = v_process_location AND
sale_yearmonth = v_sale_yearmonth AND customer_cd = v_customer_cd AND
contract_section = v_contract_section AND
customer_type = v_customer_type AND ORDER_NO = v_order_no; if v_curr_date_request <=0 then
return 0;
end if; IF ROUND(v_curr_date_request - (v_sum_request_amount - v_sum_income_amount),2) < 0 THEN
return v_curr_date_request;
end if; return ROUND(v_curr_date_request - (v_sum_request_amount - v_sum_income_amount),2); end if; v_curr_date_request := F_GetMonthAmount_ORDERNO(v_date,v_end_date,' ', ' ',v_order_no,v_office_name,v_sales_incharge_name,v_article_no,
v_article_name,v_process_location,v_sale_yearmonth,v_customer_cd,
v_customer_info,v_contract_section,v_customer_type,'SUM_REQUEST_AMOUNT');
if ROUND(v_sum_income_amount - v_curr_date_request,2) >= 0 THEN
RETURN 0;
END IF; v_tmp_amount := F_GetMonthAmount_ORDERNO(v_date,' ',v_start_date,v_end_date,v_order_no,v_office_name,v_sales_incharge_name,v_article_no,
v_article_name,v_process_location,v_sale_yearmonth,v_customer_cd,
v_customer_info,v_contract_section,v_customer_type,'REQUEST_AMOUNT');
if ROUND(v_sum_income_amount - (v_curr_date_request - v_tmp_amount),2) <= 0 THEN
RETURN ROUND(v_tmp_amount,2);
END IF; return ROUND(ABS(v_sum_income_amount - v_curr_date_request),2);
END IF; IF ROUND(v_sum_request_amount - v_sum_income_amount,2) < 0 THEN
if v_flag = '1' then
return 0;
end if; v_curr_date_income := F_GetMonthAmount_ORDERNO(v_date,v_end_date,' ', ' ',v_order_no,v_office_name,v_sales_incharge_name,v_article_no,
v_article_name,v_process_location,v_sale_yearmonth,v_customer_cd,
v_customer_info,v_contract_section,v_customer_type,'SUM_INCOME_AMOUNT');
if ROUND(v_sum_request_amount - v_curr_date_income,2) >= 0 THEN
RETURN 0;
END IF; v_tmp_amount := F_GetMonthAmount_ORDERNO(v_date,' ',v_start_date,v_end_date,v_order_no,v_office_name,v_sales_incharge_name,v_article_no,
v_article_name,v_process_location,v_sale_yearmonth,v_customer_cd,
v_customer_info,v_contract_section,v_customer_type,'INCOME_AMOUNT');
if ROUND(v_sum_request_amount - (v_curr_date_income - v_tmp_amount),2) <= 0 THEN
RETURN ROUND(0 - v_tmp_amount,2);
END IF; return ROUND(v_sum_request_amount - v_curr_date_income,2); RETURN 1;
END IF;EXCEPTION WHEN OTHERS THEN
return 0;
end;
函数 F_GetMonthAmount_ORDERNOCREATE OR REPLACE FUNCTION F_GetMonthAmount_ORDERNO(
v_date1 in varchar2,
v_date in varchar2,
v_start_date in varchar2,
v_end_date in varchar2,
v_order_no in varchar2,
v_office_name in varchar2,
v_sales_incharge_name in varchar2,
v_article_no in varchar2,
v_article_name in varchar2,
v_process_location in varchar2,
v_sale_yearmonth in varchar2,
v_customer_cd in varchar2,
v_customer_info in varchar2,
v_contract_section in varchar2,
v_customer_type in varchar2,
v_flag in varchar2)
RETURN number IS v_sum_request_amount number;
v_sum_income_amount number;begin if v_flag = 'SUM_INCOME_AMOUNT' THEN
-- È¡µ±ÔÂÀÛ¼ÆÇëÇó¶îÓëÀÛ¼ÆÈë½ð¶î
SELECT NVL(SUM(income_amount_notax),0)
INTO v_sum_income_amount
FROM v_month_article_customer_list
WHERE sale_date <= v_date AND office_name = v_office_name AND
sales_incharge_name = v_sales_incharge_name AND article_no = v_article_no AND
process_location = v_process_location AND
sale_yearmonth = v_sale_yearmonth AND customer_cd = v_customer_cd AND
contract_section = v_contract_section AND
customer_type = v_customer_type AND ORDER_NO = v_order_no; return v_sum_income_amount;
END IF; if v_flag = 'SUM_REQUEST_AMOUNT' THEN
-- È¡µ±ÔÂÀÛ¼ÆÇëÇó¶îÓëÀÛ¼ÆÈë½ð¶î
SELECT NVL(SUM(request_amount_notax),0)
INTO v_sum_request_amount
FROM v_month_article_customer_list
WHERE sale_date <= v_date AND pre_income_date <= v_date1
AND office_name = v_office_name AND
sales_incharge_name = v_sales_incharge_name AND article_no = v_article_no AND
process_location = v_process_location AND
sale_yearmonth = v_sale_yearmonth AND customer_cd = v_customer_cd AND
contract_section = v_contract_section AND
customer_type = v_customer_type AND ORDER_NO = v_order_no; return v_sum_request_amount;
END IF; if v_flag = 'INCOME_AMOUNT' THEN
-- È¡µ±ÔÂÀÛ¼ÆÇëÇó¶îÓëÀÛ¼ÆÈë½ð¶î
SELECT NVL(SUM(income_amount_notax),0)
INTO v_sum_income_amount
FROM v_month_article_customer_list
WHERE sale_date >= v_start_date and sale_date <= v_end_date AND
office_name = v_office_name AND
sales_incharge_name = v_sales_incharge_name AND article_no = v_article_no AND
process_location = v_process_location AND
sale_yearmonth = v_sale_yearmonth AND customer_cd = v_customer_cd AND
contract_section = v_contract_section AND
customer_type = v_customer_type AND ORDER_NO = v_order_no; return v_sum_income_amount;
END IF; if v_flag = 'REQUEST_AMOUNT' THEN
-- È¡µ±ÔÂÀÛ¼ÆÇëÇó¶îÓëÀÛ¼ÆÈë½ð¶î
SELECT NVL(SUM(request_amount_notax),0)
INTO v_sum_request_amount
FROM v_month_article_customer_list
WHERE sale_date >= v_start_date and sale_date <= v_end_date AND
pre_income_date <= v_date1 and
office_name = v_office_name AND
sales_incharge_name = v_sales_incharge_name AND article_no = v_article_no AND
process_location = v_process_location AND
sale_yearmonth = v_sale_yearmonth AND customer_cd = v_customer_cd AND
contract_section = v_contract_section AND
customer_type = v_customer_type AND ORDER_NO = v_order_no; return v_sum_request_amount;
END IF;EXCEPTION WHEN OTHERS THEN
return 0;end;