名站网址导航为大家提供关于数据库教程相关的教程网站知识。
SELECT SUBSTR (T.RPT_ID,Oracle 11g站点服务器与客户端卸载、安装全过程
INSTR (T.RPT_ID,',',1,C.LV) 1,
INSTR (T.RPT_ID,',',1,C.LV 1)- (INSTR (T.RPT_ID,',',1,C.LV) 1))
AS RPT_ID
FROM (SELECT ',' || '85,86,87' || ',' RPT_ID,
LENGTh ('85,86,87' || ',') - NVL (LENGTh (REPLACE ('85,86', ',')), 0) CNT
FROM DUAL) t,
(SELECT LEVEL lv
FROM DUAL
CONNECT BY LEVEL <= LENGTh('85,86,87' || ',') - NVL(LENGTh(REPLACE('85,86,87', ',')), 0)) c
WhERE T.cnt >= c.lv
说明:CNT表示串里面有多少字符。
当是字符串是表中的字段时,取level<=最大个数
如:
1).取最大个数
SELECT MAX(LENGTh(RPT_ID || ',') -
NVL(LENGTh(REPLACE(RPT_ID, ',')), 0)) INTO v_c
FROM DIM_AUDIT_TABLE@sjmh_inter;
2).l转成行
SELECT T.T_NAME,
T.T_NAME_COMM,
T.T_COLUMN,
T.T_COLUMN_COMM,
t.COMMENT_NL,
t.COMMENT_NL_TIME,
t.SEQ_USER_ID,
SUBSTR(T.RPT_ID,
INSTR(T.RPT_ID, '','', 1, C.LV) 1,
INSTR(T.RPT_ID, '','', 1, C.LV 1) -
(INSTR(T.RPT_ID, '','', 1, C.LV) 1)) AS RPT_ID
FROM (SELECT A.T_NAME,
A.T_NAME_COMM,
A.T_COLUMN,
A.T_COLUMN_COMM,
a.COMMENT_NL,
a.COMMENT_NL_TIME,
a.SEQ_USER_ID,
'','' || A.RPT_ID || '','' RPT_ID,
LENGTh(A.RPT_ID || '','') -
NVL(LENGTh(REPLACE(A.RPT_ID, '','')), 0) CNT
FROM DIM_AUDIT_TABLE@sjmh_inter A
WhERE a.COMMENT_NL is not null) T,
(SELECT LEVEL LV FROM DUAL CONNECT BY LEVEL <= '||v_c||') C
WhERE C.LV <= T.CNT;
注:假如如果是上面具体相关代码是远程的具体相关代码,(SELECT LEVEL LV FROM DUAL CONNECT BY LEVEL <= '||v_c||') C,不要用dual表,可以改用all_objects或user_objects, 要不然能查询,但是把查询出来的SQL插入到某个表时,只能插入一行
关于数据库教程相关的教程网站知识今天我们就说到这里了,希望可以帮到大家。