别用游标啊,直接就用 insert into 目标表(col1,col2,col3) select col1,col2,col3 from 源表
create or replace procedure PRO_TRANSACTION_SUMMARY_DATA(P_WORKDATE in VARCHAR2) is TYPE CARD_CURSOR IS REF CURSOR; CUR_CARD_C CARD_CURSOR; 循环的游标 E_OT_AMOUNT NUMBER(18, 2); -- 日交易金额2860 E_CONTACT VARCHAR2(60); -- 商户名称 E_MERCHANT_NO VARCHAR2(20); -- 商户编号 E_OT_BS NUMBER(10); -- 日交易笔数 E_BIG_OT_BS NUMBER(10); -- 大额交易笔数 E_BIG_OT_AMOUNT NUMBER(18, 2); -- 大额交易金额 E_INT_OT_BS NUMBER(10); -- 整数交易笔数 E_INT_OT_AMOUNT NUMBER(18, 2); -- 整数交易金额 ......。。---还有几个变量 BEGIN --判断表中是否有数据 若有则删除 DELETE FROM TRANSACTION_SUMMARY 目标表 WHERE exists (select 1 from TRANSACTION_SUMMARY WHERE ROWNUM = 1); -- 取得大额交易起点参数 SELECT LARGE_POINT * 100 INTO E_LAGER_POINT FROM FXPC_PARAMETER; -- 取得整数交易起点参数 SELECT INTEGER_POINT * 100 INTO E_INTEGER_POINT FROM FXPC_PARAMETER; -- 取得跨行交易单位名称 商户名称 OPEN CUR_CARD_C FOR SELECT DISTINCT TYDWNO 编号, TYDWNAME 名称 FROM TEMP_BFHPOSLC 原来的表 WHERE WORKDATE = P_WORKDATE; LOOP FETCH CUR_CARD_C INTO E_MERCHANT_NO, E_CONTACT; EXIT WHEN CUR_CARD_C%NOTFOUND; BEGIN
-- 日交易金额2860 日交易笔数 SELECT SUM(TO_NUMBER(AMOUNT)), TO_NUMBER(COUNT(TYDWNO)) INTO E_OT_AMOUNT, E_OT_BS FROM TEMP_BFHPOSLC 原来的表 WHERE TYDWNAME = E_CONTACT AND WORKDATE = P_WORKDATE AND TYDWNO = E_MERCHANT_NO; IF (E_OT_BS = 0) THEN E_OT_AMOUNT := 0; END IF;
-- 大额交易笔数 大额交易金额 SELECT TO_NUMBER(COUNT(TYDWNO)), SUM(TO_NUMBER(AMOUNT)) INTO E_BIG_OT_BS, E_BIG_OT_AMOUNT FROM TEMP_BFHPOSLC 原来的表 WHERE TYDWNAME = E_CONTACT AND WORKDATE = P_WORKDATE AND TO_NUMBER(AMOUNT) > TO_NUMBER(E_LAGER_POINT) AND TYDWNO = E_MERCHANT_NO; IF (E_BIG_OT_BS = 0) THEN E_BIG_OT_AMOUNT := 0; END IF;
-- 整数交易笔数 SELECT TO_NUMBER(COUNT(TYDWNO)), SUM(TO_NUMBER(AMOUNT)) INTO E_INT_OT_BS, E_INT_OT_AMOUNT FROM TEMP_BFHPOSLC 原来的表 WHERE TYDWNAME = E_CONTACT AND WORKDATE = P_WORKDATE AND to_number(AMOUNT) > TO_NUMBER(E_INTEGER_POINT) AND MOD(to_number(AMOUNT), 10000) = 0 AND TYDWNO = E_MERCHANT_NO; IF (E_INT_OT_BS = 0) THEN E_INT_OT_AMOUNT := 0; END IF;
-- 撤销笔数 撤销金额2865 SELECT TO_NUMBER(COUNT(TYDWNO)), SUM(TO_NUMBER(AMOUNT)) INTO E_WD_BS, E_WD_AMOUNT FROM TEMP_BFHPOSLC 原来的表 WHERE TYDWNAME = E_CONTACT AND WORKDATE = P_WORKDATE AND TYDWNO = E_MERCHANT_NO AND TRXCODE = '2865'; IF (E_WD_BS = 0) THEN E_WD_AMOUNT := 0; END IF;
Oracle Bulk 与 性能优化 说明
http://blog.csdn.net/tianlesoftware/article/details/6578351Oracle bulk 示例
http://blog.csdn.net/tianlesoftware/article/details/6599003
insert into 目标表(col1,col2,col3) select col1,col2,col3 from 源表
TYPE CARD_CURSOR IS REF CURSOR;
CUR_CARD_C CARD_CURSOR; 循环的游标 E_OT_AMOUNT NUMBER(18, 2); -- 日交易金额2860
E_CONTACT VARCHAR2(60); -- 商户名称
E_MERCHANT_NO VARCHAR2(20); -- 商户编号
E_OT_BS NUMBER(10); -- 日交易笔数
E_BIG_OT_BS NUMBER(10); -- 大额交易笔数
E_BIG_OT_AMOUNT NUMBER(18, 2); -- 大额交易金额
E_INT_OT_BS NUMBER(10); -- 整数交易笔数
E_INT_OT_AMOUNT NUMBER(18, 2); -- 整数交易金额 ......。。---还有几个变量
BEGIN
--判断表中是否有数据 若有则删除 DELETE FROM TRANSACTION_SUMMARY 目标表
WHERE exists (select 1 from TRANSACTION_SUMMARY WHERE ROWNUM = 1); -- 取得大额交易起点参数
SELECT LARGE_POINT * 100 INTO E_LAGER_POINT FROM FXPC_PARAMETER;
-- 取得整数交易起点参数
SELECT INTEGER_POINT * 100 INTO E_INTEGER_POINT FROM FXPC_PARAMETER;
-- 取得跨行交易单位名称 商户名称 OPEN CUR_CARD_C FOR
SELECT DISTINCT TYDWNO 编号, TYDWNAME 名称
FROM TEMP_BFHPOSLC 原来的表
WHERE WORKDATE = P_WORKDATE;
LOOP
FETCH CUR_CARD_C
INTO E_MERCHANT_NO, E_CONTACT;
EXIT WHEN CUR_CARD_C%NOTFOUND;
BEGIN
-- 日交易金额2860 日交易笔数
SELECT SUM(TO_NUMBER(AMOUNT)), TO_NUMBER(COUNT(TYDWNO))
INTO E_OT_AMOUNT, E_OT_BS
FROM TEMP_BFHPOSLC 原来的表
WHERE TYDWNAME = E_CONTACT
AND WORKDATE = P_WORKDATE
AND TYDWNO = E_MERCHANT_NO;
IF (E_OT_BS = 0) THEN
E_OT_AMOUNT := 0;
END IF;
-- 大额交易笔数 大额交易金额
SELECT TO_NUMBER(COUNT(TYDWNO)), SUM(TO_NUMBER(AMOUNT))
INTO E_BIG_OT_BS, E_BIG_OT_AMOUNT
FROM TEMP_BFHPOSLC 原来的表
WHERE TYDWNAME = E_CONTACT
AND WORKDATE = P_WORKDATE
AND TO_NUMBER(AMOUNT) > TO_NUMBER(E_LAGER_POINT)
AND TYDWNO = E_MERCHANT_NO;
IF (E_BIG_OT_BS = 0) THEN
E_BIG_OT_AMOUNT := 0;
END IF;
-- 整数交易笔数
SELECT TO_NUMBER(COUNT(TYDWNO)), SUM(TO_NUMBER(AMOUNT))
INTO E_INT_OT_BS, E_INT_OT_AMOUNT
FROM TEMP_BFHPOSLC 原来的表
WHERE TYDWNAME = E_CONTACT
AND WORKDATE = P_WORKDATE
AND to_number(AMOUNT) > TO_NUMBER(E_INTEGER_POINT)
AND MOD(to_number(AMOUNT), 10000) = 0
AND TYDWNO = E_MERCHANT_NO;
IF (E_INT_OT_BS = 0) THEN
E_INT_OT_AMOUNT := 0;
END IF;
-- 撤销笔数 撤销金额2865
SELECT TO_NUMBER(COUNT(TYDWNO)), SUM(TO_NUMBER(AMOUNT))
INTO E_WD_BS, E_WD_AMOUNT
FROM TEMP_BFHPOSLC 原来的表
WHERE TYDWNAME = E_CONTACT
AND WORKDATE = P_WORKDATE
AND TYDWNO = E_MERCHANT_NO
AND TRXCODE = '2865';
IF (E_WD_BS = 0) THEN
E_WD_AMOUNT := 0;
END IF;
。。
。。还有很多
-- 插入数据
INSERT INTO TRANSACTION_SUMMARY
(OT_AMOUNT, -- 日交易金额
MERCHANT, -- 商户名称
MERCHANT_NO, -- 商户编号
OT_BS, -- 日交易笔数
BIG_OT_BS, -- 大额交易笔数
BIG_OT_AMOUNT, -- 大额交易金额
INT_OT_BS, -- 整数交易笔数
INT_OT_AMOUNT, -- 整数交易金额
WD_BS, -- 撤销笔数
WD_AMOUNT, -- 撤销金额
RETURN_BS, -- 退货笔数
RETURN_AMOUNT, -- 退货金额
CREDIT_BS, -- 信用卡笔数
CREDIT_AMOUNT, -- 信用卡金额
DEBIT_CARD_BS, -- 借记卡笔数
DEBIT_CARD_AMOUNT, -- 借记卡金额
TRANSACTION_DATE) -- 交易日期
VALUES
(E_OT_AMOUNT / 100,
E_CONTACT,
E_MERCHANT_NO,
E_OT_BS,
E_BIG_OT_BS,
E_BIG_OT_AMOUNT / 100,
E_INT_OT_BS,
E_INT_OT_AMOUNT / 100,
E_WD_BS,
E_WD_AMOUNT / 100,
E_RETURN_BS,
E_RETURN_AMOUNT / 100,
E_CREDIT_BS,
E_CREDIT_AMOUNT / 100,
E_DEBIT_CARD_BS,
E_DEBIT_CARD_AMOUNT / 100,
P_WORKDATE);
END LOOP;
CLOSE CUR_CARD_C;
-- 日交易笔数
-- 大额交易金额
-- 撤销笔数
-- 退货笔数
-- 信用卡笔数。。每次的条件都不一样。不用循环怎么一次全插进去啊
偶在一个数据库下啊。从一张表生成数据插入到另外一张表