如題，資料庫里面是有記錄的，但是沒有回傳值，請教各位大拿是那兒出了問題呢。
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;
uj5u.com熱心網友回復：
EXECUTE IMMEDIATE 'SELECT ROWID這句，生成的動態陳述句，你跑下，應該出錯了；
uj5u.com熱心網友回復：
從表面上看，listagg(column_name,',') 組成了in使用的條件正常時，應該是 in ('a','b','c')，但變數里面的內容沒有單引號
uj5u.com熱心網友回復：
只要這一句有值，就有回傳值
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
運行完后，在OUTPUT內有列印結果
uj5u.com熱心網友回復：
我這是10G 的，在自己的上面運行的陳述句和結果
DECLARE
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
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/103446.html
標籤：開發
上一篇：關于初寫存盤程序的問題請教
下一篇：有沒有比較難的SQL