CREATE TABLE main_tab (
seq_id NUMBER(10),
e_id NUMBER(10),
code NUMBER(10),
CONSTRAINT pk_main_tab PRIMARY KEY(seq_id)
);
INSERT INTO main_tab VALUES(1,11,3);
CREATE TABLE transact_tab (
seq_id,
e_id,
code NUMBER(10),
start_date DATE,
end_date DATE
);
我有兩個表main_tab，其中我需要根據 table 的列transact_tab將記錄插入到表中。前任。如果 然后在表中，它應該加載 3 條記錄，如 1、2 和 3，以及列，因為當前值將為空。transact_tabcodemain_tabcode = 3 transact_tabstart_dateend_dateSYSDATEcodeend_date
上述要求的代碼：-
DECLARE
l_transact_row transact_tab%ROWTYPE;
BEGIN
FOR r IN (SELECT * FROM main_tab) LOOP
FOR i IN 1 .. r.code LOOP
BEGIN
SELECT * INTO l_transact_row
FROM transact_tab
WHERE e_id = r.e_id AND code = i;
IF l_transact_row.end_date IS NULL AND i <> r.code THEN
UPDATE transact_tab SET end_date = SYSDATE WHERE e_id = r.e_id AND code = i;
END IF;
EXCEPTION WHEN NO_DATA_FOUND THEN
INSERT INTO transact_tab (e_id, code,start_date, end_date)
VALUES (r.e_id, i, SYSDATE, CASE WHEN i = r.code THEN NULL ELSE SYSDATE END);
END ;
END LOOP;
END LOOP;
END;
資料庫版本：Oracle 18c。
這段代碼非常適合自上而下的方法，我需要將資料加載到其中，transact_tab但如果我的資料code減少了，那么邏輯也應該處理這種情況。示例..如果code從 3 更改為 2，那么在這種情況下，transact_tab如果代碼減少，它應該將已經存在的值更新為 NULL。
transact_tab如果code從 3 減少到 2 英寸的預期輸出main_tab應該是：
------ ------ ------------ ----------
| e_id | code | start_date | end_date |
------ ------ ------------ ----------
| 11 | 1 | 19-06-22 | 19-06-22 |
| 11 | 2 | 19-06-22 | NULL |
| 11 | 3 | NULL | NULL |
------ ------ ------------ ----------
uj5u.com熱心網友回復：
你不能使用類似下面的東西 -
insert into transact_tab (e_id,code, start_date,end_date)
select e_id,level,
case when code<level then null else sysdate end,
case when code<=level then null else sysdate end
from main_tab
connect by level<=3;
在這里參考小提琴。
DECLARE
l_transact_row transact_tab%ROWTYPE;
BEGIN
FOR r IN (SELECT * FROM main_tab) LOOP
FOR i IN 1 .. r.code LOOP
BEGIN
SELECT * INTO l_transact_row
FROM transact_tab
WHERE e_id = r.e_id AND code = i;
UPDATE transact_tab SET (start_date,end_date) = (select sysdate,sysdate from dual) WHERE code < r.code;
UPDATE transact_tab SET (start_date,end_date) = (select null,null from dual) WHERE code > r.code;
UPDATE transact_tab SET (start_date,end_date) = (select sysdate,null from dual) WHERE code = r.code;
EXCEPTION WHEN NO_DATA_FOUND THEN
INSERT INTO transact_tab (e_id, code,start_date, end_date)
VALUES (r.e_id, i, SYSDATE, CASE WHEN i = r.code THEN NULL ELSE SYSDATE END);
END ;
END LOOP;
END LOOP;
END;
請參閱 fiddle here。
使用合并方法，它處理 main_tab 代碼值的增加和減少，以及“如果代碼為 3，則將有 4 個條目，即 0、1、2 和 3”
merge into transact_tab t1
using
(select * from
(select e_id,level-1 as lvl,
case when code<level-1 then null else sysdate end start_date,
case when code<=level-1 then null else sysdate end end_date
from main_tab
connect by level <=
(select max(code) 1 from (
(select max(code) code from transact_tab) union all
(select code from main_tab) )
)
) ) t2
on (t1.e_id = t2.e_id
and t1.code = t2.lvl)
when matched then
update set t1.start_date=t2.start_date, t1.end_date=t2.end_date
when NOT matched then
insert (e_id,code,start_date,end_date)
VALUES
(t2.e_id,t2.lvl,t2.start_date,t2.end_date);
請參閱此處的小提琴，以查看實際的合并方法。
uj5u.com熱心網友回復：
編輯后的版本，代碼更少。Basicaly - 它有效，但我沒有測驗在不同日期更改 CODE 值的所有可能方式。可能還有一些調整要做。試試看。
Declare
maxRepeats Number(3) := 0;
subMaxCode Number(3) := 0;
Begin
FOR r IN(SELECT * FROM a_main_tab) LOOP
maxRepeats := r.CODE;
SELECT Nvl(Max(CODE), 0) Into subMaxCode FROM a_transact_tab WHERE SEQ_ID = r.SEQ_ID And E_ID = r.E_ID; -- And start_date Is Not Null;
FOR i IN 1..maxRepeats LOOP
If subMaxCode >= 0 And subMaxCode = maxRepeats THEN
EXIT;
ElsIf subMaxCode >= 0 And subMaxCode < maxRepeats And i > subMaxCode THEN
Begin
INSERT INTO a_transact_tab (seq_id, e_id, code, start_date, end_date)
VALUES(r.seq_id, r.e_id, i, SYSDATE, CASE WHEN i = maxRepeats THEN Null ELSE SYSDATE END);
commit;
End;
ElsIf subMaxCode >= 0 And subMaxCode < maxRepeats And i <= subMaxCode THEN
Begin
UPDATE A_TRANSACT_TAB
SET start_date = CASE WHEN start_date Is Null Then SYSDATE ELSE start_date END,
end_date = CASE WHEN end_date Is Null Then SYSDATE ELSE end_date END
WHERE seq_id = r.seq_id and e_id = r.e_id and code = i;
commit;
End;
ElsIf subMaxCode >= 0 And subMaxCode >= maxRepeats And i <= subMaxCode THEN --UPD_x_TO_y
Begin
UPDATE A_TRANSACT_TAB updt
SET updt.start_date = CASE WHEN CODE > maxRepeats THEN Null ELSE CASE WHEN updt.start_date Is Not Null THEN updt.start_date ELSE SYSDATE END END,
updt.end_date = CASE WHEN CODE >= maxRepeats THEN Null ELSE CASE WHEN updt.start_date Is Not Null THEN updt.start_date ELSE SYSDATE END END
WHERE updt.seq_id = r.seq_id and updt.e_id = r.e_id and updt.code Between 1 and subMaxCode;
commit;
End;
EXIT;
End If;
END LOOP;
END LOOP;
End;
在此（第二次）編輯和模擬之后，測驗結果如下：
-- 17-JUN-22 Code is 3 *transact table empty
-- SEQ_ID E_ID CODE START_DATE END_DATE
-- ------ ------ ----- ---------- ----------
-- 1 11 1 17-JUN-22 17-JUN-22
-- 1 11 2 17-JUN-22 17-JUN-22
-- 1 11 3 17-JUN-22
--
-- ----------------------------------------------
-- 18-JUN-22 Code changed from 3 to 2
-- SEQ_ID E_ID CODE START_DATE END_DATE
-- ------ ------ ----- ---------- ----------
-- 1 11 1 17-JUN-22 17-JUN-22
-- 1 11 2 17-JUN-22
-- 1 11 3
--
-- ----------------------------------------------
-- 19-JUN-22 Code changed from 2 to 5
-- SEQ_ID E_ID CODE START_DATE END_DATE
-- ------ ------ ----- ---------- ----------
-- 1 11 1 17-JUN-22 17-JUN-22
-- 1 11 2 17-JUN-22 19-JUN-22
-- 1 11 3 19-JUN-22 19-JUN-22
-- 1 11 4 19-JUN-22 19-JUN-22
-- 1 11 5 19-JUN-22
--
-- ----------------------------------------------
-- 20-JUN-22 (1st) Code changed from 5 to 4
-- SEQ_ID E_ID CODE START_DATE END_DATE
-- ------ ------ ----- ---------- ----------
-- 1 11 1 17-JUN-22 17-JUN-22
-- 1 11 2 17-JUN-22 19-JUN-22
-- 1 11 3 19-JUN-22 19-JUN-22
-- 1 11 4 19-JUN-22
-- 1 11 5
--
-- ----------------------------------------------
-- 20-JUN-22 (2nd) Code changed from 4 to 7
-- SEQ_ID E_ID CODE START_DATE END_DATE
-- ------ ------ ----- ---------- ----------
-- 1 11 1 17-JUN-22 17-JUN-22
-- 1 11 2 17-JUN-22 19-JUN-22
-- 1 11 3 19-JUN-22 19-JUN-22
-- 1 11 4 19-JUN-22 20-JUN-22
-- 1 11 5 20-JUN-22 20-JUN-22
-- 1 11 6 20-JUN-22 20-JUN-22
-- 1 11 7 20-JUN-22
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/494506.html
上一篇：獲取可變陣列值作為連接文本串列