如题,数据库里面是有记录的,但是没有返回值,请教各位大拿是那儿出了问题呢。DECLARE
lv_str NUMBER :=146;
lv_rowid ROWID;
BEGIN
FOR lv_rec IN (SELECT OWNER||'.'||TABLE_NAME TABLE_NAME,listagg(column_name,',') within group (order by TABLE_NAME) COL
FROM all_TAB_COLUMNS
WHERE DATA_TYPE='NUMBER'
and table_name like 'S_PROMO%'
GROUP BY OWNER,TABLE_NAME
)
LOOP
BEGIN
EXECUTE IMMEDIATE 'SELECT ROWID FROM '||lv_rec.table_name||' WHERE '||lv_str||' IN ('||lv_rec.col||') AND ROWNUM=1'
INTO lv_rowid;
EXCEPTION
WHEN NO_DATA_FOUND THEN
lv_rowid := NULL;
END;
IF lv_rowid IS NOT NULL THEN
DBMS_OUTPUT.PUT_LINE('TABLE NAME='||lv_rec.table_name||' ROWID='||lv_rowid);
EXIT;
END IF;
END LOOP;
END;
lv_str NUMBER :=146;
lv_rowid ROWID;
BEGIN
FOR lv_rec IN (SELECT OWNER||'.'||TABLE_NAME TABLE_NAME,listagg(column_name,',') within group (order by TABLE_NAME) COL
FROM all_TAB_COLUMNS
WHERE DATA_TYPE='NUMBER'
and table_name like 'S_PROMO%'
GROUP BY OWNER,TABLE_NAME
)
LOOP
BEGIN
EXECUTE IMMEDIATE 'SELECT ROWID FROM '||lv_rec.table_name||' WHERE '||lv_str||' IN ('||lv_rec.col||') AND ROWNUM=1'
INTO lv_rowid;
EXCEPTION
WHEN NO_DATA_FOUND THEN
lv_rowid := NULL;
END;
IF lv_rowid IS NOT NULL THEN
DBMS_OUTPUT.PUT_LINE('TABLE NAME='||lv_rec.table_name||' ROWID='||lv_rowid);
EXIT;
END IF;
END LOOP;
END;
正常时,应该是 in ('a','b','c'),但变量里面的内容没有单引号
FROM all_TAB_COLUMNS
WHERE DATA_TYPE='NUMBER'
and table_name like 'S_PROMO%'
GROUP BY OWNER,TABLE_NAME 运行完后,在OUTPUT内有打印结果
lv_str NUMBER := 146;
lv_rowid ROWID;
BEGIN
FOR lv_rec IN (SELECT OWNER || '.' || TABLE_NAME TABLE_NAME,
WM_CONCAT(COLUMN_NAME) COL
FROM all_TAB_COLUMNS
WHERE DATA_TYPE = 'NUMBER'
GROUP BY OWNER, TABLE_NAME
) LOOP
BEGIN
EXECUTE IMMEDIATE 'SELECT ROWID FROM ' || lv_rec.table_name ||
' WHERE ' || lv_str || ' IN (' || lv_rec.col ||
') AND ROWNUM=1'
INTO lv_rowid;
EXCEPTION
WHEN NO_DATA_FOUND THEN
lv_rowid := NULL;
END;
IF lv_rowid IS NOT NULL THEN
DBMS_OUTPUT.PUT_LINE('TABLE NAME=' || lv_rec.table_name || '; ROWID=' ||
lv_rowid ||';'|| lv_str ||':'|| lv_rec.COL );
EXIT;
END IF;
END LOOP;
END;
TABLE NAME=SYS.T; ROWID=AFx6OLAD4AAAQ2YAAt;146:SNAP_ID,DBID,SAMPLE_ID,INSTANCE_NUMBER,USER_ID,SQL_PLAN_HASH_VALUE,SQL_OPCODE,PLSQL_ENTRY_SUBPROGRAM_ID,PLSQL_SUBPROGRAM_ID,P2,P1,SEQ#,EVENT_ID,BLOCKING_SESSION_SERIAL#,BLOCKING_SESSION,QC_INSTANCE_ID,QC_SESSION_ID,SERVICE_HASH,FLAGS,CURRENT_BLOCK#,CURRENT_FILE#,CURRENT_OBJ#,TIME_WAITED,WAIT_TIME,WAIT_CLASS_ID,P3,PLSQL_OBJECT_ID,PLSQL_ENTRY_OBJECT_ID,FORCE_MATCHING_SIGNATURE,SQL_CHILD_NUMBER,SESSION_SERIAL#,SESSION_ID