将QHOLELAYER表中(holecode, stratcode)作为关键码。即如果同一
个钻孔在某个标准分层下有相同的stratcode,根据STRATNO将其进行区分,如:holecode 标准分层 stratcode
CC031 1 Qp[3]{5-2} 3
CC031 1 E[1]{1-2} 4
CC031 1 E[1]{1-2} 5-1
应该改为:
CC031 1 Qp[3]{5-2} 3
CC031 1 E[1]{1-2}-1 4
CC031 1 E[1]{1-2}-2 5-1下面是开始语句,在“循环判断更新语句”中如何实现CREATE OR REPLACE PROCEDURE UPDATELAYER
is
CURSOR v_qholelayer_cursor
IS
SELECT * FROM qholelayer ORDER BY holecode, topdepth FOR UPDATE;v_tmpHoleCode qholelayer.holecode%TYPE;
v_tmpStratcode qholelayer.Stratcode%TYPE;
v_tmpTopdepth qholelayer.Topdepth%TYPE;
v_tmpQholelayer v_qholelayer_cursor%ROWTYPE;
v_tmpOrder number:= 1;begin
OPEN v_qholelayer_cursor; 循环判断更新语句
CLOSE v_qholelayer_cursor;
COMMIT;
end UPDATELAYER;
个钻孔在某个标准分层下有相同的stratcode,根据STRATNO将其进行区分,如:holecode 标准分层 stratcode
CC031 1 Qp[3]{5-2} 3
CC031 1 E[1]{1-2} 4
CC031 1 E[1]{1-2} 5-1
应该改为:
CC031 1 Qp[3]{5-2} 3
CC031 1 E[1]{1-2}-1 4
CC031 1 E[1]{1-2}-2 5-1下面是开始语句,在“循环判断更新语句”中如何实现CREATE OR REPLACE PROCEDURE UPDATELAYER
is
CURSOR v_qholelayer_cursor
IS
SELECT * FROM qholelayer ORDER BY holecode, topdepth FOR UPDATE;v_tmpHoleCode qholelayer.holecode%TYPE;
v_tmpStratcode qholelayer.Stratcode%TYPE;
v_tmpTopdepth qholelayer.Topdepth%TYPE;
v_tmpQholelayer v_qholelayer_cursor%ROWTYPE;
v_tmpOrder number:= 1;begin
OPEN v_qholelayer_cursor; 循环判断更新语句
CLOSE v_qholelayer_cursor;
COMMIT;
end UPDATELAYER;
CC031 1 Qp[3]{5-2} 3
CC031 1 E[1]{1-2} 4
CC031 1 E[1]{1-2} 5-1
应该改为:
holecode 标准分层 stratcode stratno
CC031 1 Qp[3]{5-2} 3
CC031 1 E[1]{1-2}-1 4
CC031 1 E[1]{1-2}-2 5-1 格式是这样的
set a.col = a.col||'-'||(select row_number() over(partition by stratcode order by stratno)
from tableA
where a.id = id)
SQL> SELECT * FROM QHOLELAYER;HOLECODE STANDARDLEVEL STRATCODE STRATNO
---------- ------------- -------------------- ----------
CC031 1 Qp[3]{5-2} 3
CC031 1 E[1]{1-2} 4
CC031 1 E[1]{1-2} 5-1SQL> UPDATE QHOLELAYER Q1
2 SET STRATCODE = (SELECT RN
3 FROM (SELECT QH.*,
4 STRATCODE||DECODE(COUNT(1) OVER(PARTITION BY HOLECODE, STANDARDLEVEL, STRATCODE),1,'','-'||ROW_NUMBER() OVER(PARTITION BY HOLECODE, STANDARDLEVEL, STRATCODE ORDER BY STRATNO)) RN
5 FROM QHOLELAYER QH) Q2
6 WHERE Q1.HOLECODE = Q2.HOLECODE
7 AND Q1.STANDARDLEVEL = Q2.STANDARDLEVEL
8 AND Q1.STRATCODE = Q2.STRATCODE
9 AND Q1.STRATNO = Q2.STRATNO)
10 WHERE EXISTS(SELECT 1
11 FROM QHOLELAYER Q2
12 WHERE Q1.HOLECODE = Q2.HOLECODE
13 AND Q1.STANDARDLEVEL = Q2.STANDARDLEVEL
14 AND Q1.STRATCODE = Q2.STRATCODE
15 AND Q1.STRATNO = Q2.STRATNO);3 rows updatedSQL> COMMIT;Commit completeSQL> SELECT * FROM QHOLELAYER;HOLECODE STANDARDLEVEL STRATCODE STRATNO
---------- ------------- -------------------- ----------
CC031 1 Qp[3]{5-2} 3
CC031 1 E[1]{1-2}-1 4
CC031 1 E[1]{1-2}-2 5-1
update QHOLELAYER a
set a.stratcode = (select stratcode || '-' || RN
FROM (select b.*,
row_number() over(partition by 标准分层, stratcode order by stratno) RN
from QHOLELAYER b)
WHERE RN > 1
and holecode = a.holecode
and stratcode = a.stratcode);
STRATCODE||DECODE(COUNT(1) OVER(PARTITION BY HOLECODE, STANDARDLEVEL, STRATCODE),1,'','-'||ROW_NUMBER() OVER(PARTITION BY HOLECODE, STANDARDLEVEL, STRATCODE ORDER BY STRATNO)