自动选取存储过程

CREATE OR REPLACE PACKAGE BODY icas_insert_line_pkg IS

-- Function and procedure implementations
FUNCTION f_get_currency(iv_company_code IN VARCHAR2) RETURN VARCHAR2 IS
v_func_currency VARCHAR2(15) := '-1'; --中间币种--
BEGIN

--先查出中间币种--
SELECT t.functional_currency_code
INTO v_func_currency
FROM icas_base_company_t t
WHERE t.company_code = iv_company_code
AND rownum = 1;
RETURN v_func_currency;
EXCEPTION
WHEN no_data_found THEN
dbms_output.put_line('no_data_found');
RETURN v_func_currency;
WHEN OTHERS THEN
dbms_output.put_line('others exception');
RETURN v_func_currency;
END;

-- Insert table for all business
PROCEDURE p_insert_table(iv_insert_rec IN tp_insert_line_rec) IS
tab_rec pls_integer := 0;
BEGIN
INSERT INTO icas_selected_lines_t
(icas_selected_lines_t_id,
transactionflow_id,
transaction_id,
ap_company_code,
ap_ic_code,
.....)
VALUES
(iv_insert_rec.icas_selected_lines_t_id,
iv_insert_rec.transactionflow_id,
iv_insert_rec.transaction_id,
iv_insert_rec.ap_company_code,
iv_insert_rec.ap_ic_code,
......)
end;

FUNCTION f_inst_ap(in_to_money IN NUMBER, --结算金额--
iv_to_currency IN VARCHAR2, --结算币种--
in_transaction_id IN NUMBER, --资金调拨、债权债务互抵号--
in_transactionflow_id IN NUMBER, --电子流号--
iv_ratetype IN VARCHAR2, --汇率类型--
iv_ratedate IN VARCHAR2, --汇率日期--
iv_company_code IN VARCHAR2, --公司段--
iv_ic_code IN VARCHAR2, --IC段--
iv_business_type IN VARCHAR2 --业务类别--
) RETURN BOOLEAN IS
v_func_currency VARCHAR2(15) := f_get_currency(iv_company_code);
to_currency_rate NUMBER := icas_get_rate_func(iv_to_currency,
v_func_currency,
iv_ratedate,
iv_ratetype);
dt_date date := sysdate;

CURSOR cur_account_not_one IS
SELECT icas_selected_lines_t_ID_S.Nextval,
in_transactionflow_id,
in_transaction_id,
AP_COMPANY_CODE,
AP_IC_CODE,
......
from ...
where ...;
BEGIN
OPEN cur_account_not_one;
LOOP
FETCH cur_account_not_one BULK COLLECT INTO tp_inst_line_arry;
--dbms_output.put_line('ap number: ' || tp_inst_line_arry.count);
FOR i IN 1 .. tp_inst_line_arry.COUNT LOOP
p_insert_table(tp_inst_line_arry(i));
END LOOP;
EXIT WHEN cur_account_not_one%NOTFOUND;
END LOOP;
CLOSE cur_account_not_one;
tp_inst_line_arry.DELETE;
RETURN TRUE;
EXCEPTION
WHEN OTHERS THEN
RETURN FALSE;
END f_inst_ap;


定义数据类型
TYPE tp_insert_line_rec IS RECORD(
icas_selected_lines_t_id icas_selected_lines_t.icas_selected_lines_t_id%TYPE,
transactionflow_id icas_selected_lines_t.transactionflow_id%TYPE,
transaction_id icas_selected_lines_t.transaction_id%TYPE,
ap_company_code icas_selected_lines_t.ap_company_code%TYPE,
ap_ic_code icas_selected_lines_t.ap_ic_code%TYPE,
....)

TYPE tp_insert_line_tab IS TABLE OF tp_insert_line_rec INDEX BY BINARY_INTEGER;

-- Public variable declarations
tp_inst_line_arry tp_insert_line_tab;