新手，碰到問題：從第一個游標里取查到的資料，在第二個游標里做引數。不知道該怎么寫了。另外，SCSM130_01的值可能不止一個，所以要用到UPDATE去更新插入表里的資料。這個更蒙了......程序如下：
create or replace procedure sp_bi_scsu412(P_CODE VARCHAR2,P_DATE DATE) is
l_scsu412_K1 SCSU412.SCSU412_K1%TYPE;
L_SCSU412_K2 SCSU412.SCSU412_K2%TYPE;
L_SCSU412_K3 SCSU412.SCSU412_K3%TYPE;
L_SCSU412_K4 SCSU412.SCSU412_K4%TYPE;
L_SCSU412_K5 SCSU412.SCSU412_K5%TYPE;
L_SCSU412_01 SCSU412.SCSU412_01%TYPE;
L_SCSU412_02 SCSU412.SCSU412_02%TYPE;
L_SCSU412_03 SCSU412.SCSU412_03%TYPE;
L_SCSU412_04 SCSU412.SCSU412_04%TYPE;
L_SCSU412_05 SCSU412.SCSU412_05%TYPE;
L_SCSU412_06 SCSU412.SCSU412_06%TYPE;
L_SCSU412_07 SCSU412.SCSU412_07%TYPE;
L_SCSU412_K6 SCSU412.SCSU412_K6%TYPE;
TYPE CUR IS REF CURSOR;
CURA CUR;
CURSOR CUR_1 IS
SELECT A.SCSU410_02, --分公司代號
A.SCSU410_03, --分公司名稱
A.SCSU410_04, --本部代號
A.SCSU410_05, --本部名稱
B.SCSU411_06, --門店代號
B.SCSU411_07, --門店名稱
A.SCSU410_06, --發起人工號
A.SCSU410_07, --發起人名稱
COUNT(A.SCSU410_01), --目標鋪貨門店數
A.SCSU410_01, --提報年月
B.SCSU411_08, --匯總機型
B.SCSU411_02 --虛擬客戶代號
FROM CUX_K2_SCSU410 A, CUX_K2_SCSU411 B
WHERE A.PROCINSTID = B.PROCINSTID
AND B.SCSU411_08 = P_CODE
AND A.SCSU410_01 = TO_CHAR(P_DATE,'YYYYMM')
GROUP BY A.SCSU410_02,
A.SCSU410_03,
A.SCSU410_04,
A.SCSU410_05,
B.SCSU411_06,
B.SCSU411_07,
A.SCSU410_06,
A.SCSU410_07,
A.SCSU410_01,
B.SCSU411_08,
B.SCSU411_02,
B.SCSU411_06;
BEGIN
OPEN CUR_1;
LOOP
FETCH CUR_1
INTO l_scsu412_K1,
L_SCSU412_01,
L_SCSU412_K2,
L_SCSU412_02,
L_SCSU412_K3,
L_SCSU412_03,
L_SCSU412_K4,
L_SCSU412_04,
L_SCSU412_05,
L_SCSU412_07,
L_SCSU412_K5,
L_SCSU412_K6;
EXIT WHEN CUR_1%NOTFOUND;
OPEN CURA FOR SELECT TO_CHAR(L_SCSU412_07,'YYYYMM'),sum(SCSU071_07)
FROM SCSU070, SCSU071, SCSM130
WHERE SCSU070_K1 = SCSU071_K1
AND SCSU070_K2 = SCSU071_K2
AND SCSU070_06 = L_SCSU412_K6
AND SCSU070_07 = L_SCSU412_01
AND SCSU071_04 = SCSM130_01
AND SCSM130_K1 = L_SCSU412_K5;
IF L_SCSU412_06 = '' THEN
SELECT TO_CHAR(L_SCSU412_07,'YYYYMM'),sum(SCSU071_07)
FROM SCSU070, SCSU071, SAIM530
WHERE SCSU070_K1 = SCSU071_K1
AND SCSU070_K2 = SCSU071_K2
AND SCSU070_06 = L_SCSU412_K6
AND SCSU070_07 = L_SCSU412_01
AND SCSU071_04 = SAIM530_K2
AND SAIM530_K1 = L_SCSU412_K5;
END IF;
LOOP
FETCH CURA INTO c2rec;
EXIT WHEN CURA%NOTFOUND;
sql;
END LOOP;
CLOSE CURA;
BEGIN
DELETE FROM SCSU412;
INSERT INTO SCSU412
(scsu412_K1,
SCSU412_K2,
SCSU412_K3,
SCSU412_K4,
SCSU412_01,
SCSU412_02,
SCSU412_03,
SCSU412_04,
SCSU412_05,
SCSU412_06,
SCSU412_07,
SCSU412_K5,
SCSU412_K6)
VALUES
(l_scsu412_K1,
L_SCSU412_K2,
L_SCSU412_K3,
L_SCSU412_K4,
L_SCSU412_01,
L_SCSU412_02,
L_SCSU412_03,
L_SCSU412_04,
L_SCSU412_05,
L_SCSU412_06,
L_SCSU412_07,
L_SCSU412_K5,
L_SCSU412_K6);
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
COMMIT;
END LOOP;
end sp_bi_scsu412;
上面的程序應該不對，但基本能看出來邏輯是什么。求大神幫忙教一下 該怎么寫
uj5u.com熱心網友回復：
可以不用游標，樓主看看merge intouj5u.com熱心網友回復：
不需要：從第一個游標里取查到的資料，在第二個游標里做引數。解決方法有兩種：
1.創建臨時表，把
FROM CUX_K2_SCSU410 A, CUX_K2_SCSU411 B
FROM SCSU070, SCSU071, SCSM130
FROM SCSU070, SCSU071, SAIM530
寫到臨時表中，然后關聯查詢。
2.使用merge into也可以處理。
uj5u.com熱心網友回復：
推薦采用方法1。采用merge into代碼可讀性會比較差，而且很容易出錯。
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/79244.html
標籤：基礎和管理
上一篇：重復插入資料
下一篇：未找到 Oracle 客戶端和網路組件。這些組件是由 Oracle 公司提供的，是 Oracle 7.3.3 版(或更高)客戶軟體安裝的一部分。