将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;
解决方案 »
- 大侠教我看一下这个问题
- 两年java经验,转DBA 大家提点意见(批评也好、鼓励也好一律接受)
- java.sql.SQLException: ORA-00907: 缺失右括号
- 请高手指点明灯:用触发器备份含用BLOB表-急
- 在线求教写sql的问题
- 请问这样的要求用SQL语句怎样实现??好难??????
- 请问如何把文本格式的数据导入到Oracle中?
- 救命啊,oracle8.16 从sqlplus登录总是有错,提示 TNS: 监听进程不能解析在连接描述符中给出的 SERVICE_NAME
- 一个sql语句,帮我看看!
- navicat for oracle 中文乱码,如何正确显示中文呀
- Oracle9i 监控指标取得问题
- 怎么这个过程不能用,谢谢1急
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)