我正在嘗試修改以下查詢以在所有版本的 Oracle 資料庫上運行。'LAST_LOGIN' 列在 Oracle 11g 中不可用，但其余列在 11g、12c 和 19 版本中是通用的。
select username, profile, account_status, to_char(ctime,'DD-MON-YYYY HH24:MI:SS') CTIME,
to_char(ptime,'DD-MON-YYYY HH24:MI:SS') PTIME, DEFAULT_TABLESPACE,
TEMPORARY_TABLESPACE, EXTERNAL_NAME, AUTHENTICATION_TYPE,
to_char(LAST_LOGIN,'DD-MON-YYYY HH24:MI:SS') LAST_LOGIN
from dba_users a,
sys.user$ b
where b.name ( )= a.username
非常感謝任何幫助。謝謝。
uj5u.com熱心網友回復：
我正在嘗試修改以下查詢以在所有版本的 Oracle 資料庫上運行。
dba_users查看視圖的底層源代碼，該last_login值來自，特別是資料型別為 DATEsys.user$的列。spare6從存盤為 UTC 的操作方式來看；；last_login將其轉換為時間戳（奇怪的是使用 to_char/to_date 而不是強制轉換），使用 from_tz 來宣告這是 UTC，并轉換為會話時區。
所以而不是
to_char(LAST_LOGIN,'DD-MON-YYYY HH24:MI:SS') LAST_LOGIN
你可以使用
to_char(from_tz(cast(b.spare6 as timestamp), 'UTC')
at time zone sessiontimezone, 'DD-MON-YYYY HH24:MI:SS') LAST_LOGIN
或者，如果您可以在原始 UTC 時間而不是您自己的會話時區中獲得值，您可以這樣做：
to_char(b.spare6, 'DD-MON-YYYY HH24:MI:SS') LAST_LOGIN_UTC
我不知道是什么時候spare6添加的，但它在 11g 中存在；如果您真的是指“所有版本”，那么可能會有一些舊版本停止存在，并且該查詢也不起作用；但我不知道那是 10g、9i 還是更早的版本。
在不同版本的 Oracle DB 上運行的 SQL 查詢
更一般地說，要為不同的版本使用不同的 SQL，您需要使用某種形式的動態 SQL，可能帶有條件編譯。
uj5u.com熱心網友回復：
您可以使用條件編譯來制作具有不同功能的函式（或程序），具體取決于 Oracle 版本。
例如：
CREATE FUNCTION test_conditional_compilation
RETURN SYS_REFCURSOR
IS
cur SYS_REFCURSOR;
BEGIN
OPEN cur FOR
SELECT 1 AS a,
$IF DBMS_DB_VERSION.VER_LE_11_2
$THEN NULL
$ELSE 2
$END AS b
FROM DUAL;
RETURN cur;
END;
/
然后：
DECLARE
cur SYS_REFCURSOR;
a INT;
b INT;
BEGIN
cur := test_conditional_compilation();
LOOP
FETCH cur INTO a, b;
EXIT WHEN cur%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(a || ', ' || COALESCE(TO_CHAR(b), 'NULL'));
END LOOP;
END;
/
在 Oracle 11g 中，輸出：
1, NULL
db<>在這里擺弄
并且，在 Oracle 21 中，輸出：
1, 2
db<>在這里擺弄
對于您的代碼，可能是：
CREATE FUNCTION get_details
RETURN SYS_REFCURSOR
IS
cur SYS_REFCURSOR;
BEGIN
OPEN cur FOR
SELECT username,
profile,
account_status,
to_char(ctime,'DD-MON-YYYY HH24:MI:SS') AS CTIME,
to_char(ptime,'DD-MON-YYYY HH24:MI:SS') AS PTIME,
DEFAULT_TABLESPACE,
TEMPORARY_TABLESPACE,
EXTERNAL_NAME,
AUTHENTICATION_TYPE,
$IF DBMS_DB_VERSION.VER_LE_11_2
$THEN NULL
$ELSE to_char(LAST_LOGIN,'DD-MON-YYYY HH24:MI:SS')
$END AS LAST_LOGIN
FROM dba_users a
LEFT OUTER JOIN sys.user$ b
ON (b.name = a.username);
END;
/
如果您只想運行查詢并NULL為 Oracle 11 中缺少的列提供值。如果您可以從其他地方獲取該列（根據 Alex Poole 的回答），那么您可以將其用于 Oracle 11 并使用條件編譯來更改列的來源。
如果要在 SQL（而不是 PL/SQL）中使用該函式，可以回傳嵌套表集合型別而不是游標。
uj5u.com熱心網友回復：
我無法使用XMLTable 方法對查詢進行建模，因為在這種情況下我必須使用 2 個視圖來查詢
您不能使用ora:view，但可以使用與 XMLTable 相同的原理并提取列；訣竅是從您的 with 生成資料的 XML 視圖，dbms_xmlgen.getxmltype()將原始查詢作為字串傳遞，但操作該查詢字串以僅last_login在版本 > 11 時包含該列 - 您可以通過交叉連接獲得v$instance.
這有點粗糙和混亂，但我認為可以滿足您的要求：
select username, profile, account_status,
to_char(ctime, 'DD-MON-YYYY HH24:MI:SS') ctime,
to_char(ptime, 'DD-MON-YYYY HH24:MI:SS') ptime,
default_tablespace, temporary_tablespace, external_name, authentication_type,
to_char(last_login, 'DD-MON-YYYY HH24:MI:SS') last_login
from v$instance i
cross join xmltable('/ROWSET/ROW'
passing dbms_xmlgen.getxmltype(
q'^
select a.username, a.profile, a.account_status, a.default_tablespace,
a.temporary_tablespace, a.external_name, a.authentication_type,
^' || case when to_number(substr(i.version, 1, instr(i.version, '.') - 1)) > 11
then q'^ to_char(a.last_login,'YYYY-MM-DD"T"HH24:MI:SS') last_login,^'
end || q'^
to_char(b.ctime, 'YYYY-MM-DD"T"HH24:MI:SS') ctime,
to_char(b.ptime,'YYYY-MM-DD"T"HH24:MI:SS') ptime
from dba_users a
left join sys.user$ b on b.name = a.username
^')
columns
username, profile, account_status, default_tablespace, temporary_tablespace,
external_name, authentication_type, ctime timestamp, ptime timestamp, last_login timestamp
) x;
無論如何，它在 19c 中作業；我沒有 11g 資料庫來嘗試它，但它應該只在last_login列中回傳 null。
內部位可能最令人困惑，它構建動態查詢字串：
q'^
select a.username, a.profile, a.account_status, a.default_tablespace,
a.temporary_tablespace, a.external_name, a.authentication_type,
^' || case when to_number(substr(i.version, 1, instr(i.version, '.') - 1)) > 11
then q'^ to_char(a.last_login,'YYYY-MM-DD"T"HH24:MI:SS') last_login,^'
end || q'^
to_char(b.ctime, 'YYYY-MM-DD"T"HH24:MI:SS') ctime,
to_char(b.ptime,'YYYY-MM-DD"T"HH24:MI:SS') ptime
from dba_users a
left join sys.user$ b on b.name = a.username
^'
That's mostly your original, reordered a bit and with modern join syntax and columns prefixed with the alias of the table they come from. Because of the case expression, in 11g that will evaluate to:
select a.username, a.profile, a.account_status, a.default_tablespace,
a.temporary_tablespace, a.external_name, a.authentication_type,
to_char(b.ctime, 'YYYY-MM-DD"T"HH24:MI:SS') ctime,
to_char(b.ptime,'YYYY-MM-DD"T"HH24:MI:SS') ptime
from dba_users a
left join sys.user$ b on b.name = a.username
while in 12c and 19c etc. it will evaluate to:
select a.username, a.profile, a.account_status, a.default_tablespace,
a.temporary_tablespace, a.external_name, a.authentication_type,
to_char(a.last_login,'YYYY-MM-DD"T"HH24:MI:SS') last_login,
to_char(b.ctime, 'YYYY-MM-DD"T"HH24:MI:SS') ctime,
to_char(b.ptime,'YYYY-MM-DD"T"HH24:MI:SS') ptime
from dba_users a
left join sys.user$ b on b.name = a.username
i.e. the same query but with the last_login reference added.
The three date columns are being formatted as strings in ISO-8601 format. That is so that when they are extracted with the XMLTable columns clause they can be declared as timestamps, and will be converted to that data type automatically.
The main query select list can then format those timestamps however you want.
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/437068.html