使用沒有 CREATE 權限的字元串數組

我有一組表，我需要確定每個表的一個特定欄位中是否存在一組特定的大約 1000 個行 ID。

select 'Accounts', row_id Acct_id, pr_postn_id
from schema.s_org_ext
where pr_postn_id in ('1-ABCD','1-BCDE')
UNION
select 'Orders', row_id Order_id, pr_postn_id
from schema.s_orders
where pr_postn_id in ('1-ABCD','1-BCDE')

以上方法適用於兩個 row_id，但是IN當我需要更新列表時，複製/粘貼 1000 個 ID 的子句很麻煩，尤其是當列表出現在IN十個UNION查詢的每個子句中時。

我正在尋找要在子句中使用的數組或字元串變數，並在後續查詢中以某種巨大的 VARCHAR2 ID 列表IN的形式引用該變數或數組。UNION SELECT ... WHERE id IN

通常我只會創建一個表，但在這個系統中我是業務分析師，而不是 dba。CREATE 特權不會發生在我身上。如果我確實嘗試在數據庫中創建任何內容，我會得到

[Error] Compilation (2: 1): ORA-01031: insufficient privileges

我嘗試使用帶游標的 DECLARE 塊，但沒有成功（沒有違反權限，只是錯誤的 SQL 程式碼）

DECLARE 
   TYPE posrec_t IS RECORD (
   pos_row_id VARCHAR2(15) );
   TYPE poslist IS TABLE OF posrec; 
   todaypos poslist :=
     poslist (
     posrec ('1-103KO-18'),posrec ('1-10TOV-1'));
   posrec posrec_t;
   

CURSOR MYCURSOR IS select 'Opportunity' Data_Object, row_id Data_object_Id, pr_postn_id
from siebel.s_oppor
WHERE PR_POSTN_ID IN posrec
UNION 
[etc]
BEGIN
OPEN MYCURSOR;
END;

使用DECLARE BEGIN END塊語法，似乎我可以創建一個包含 1000 個 ID 的短期臨時表，這些表作為定義語句出現一次（並且不需要CREATE權限），但我不知道如何使它工作。

我會使用以下方法：

1. 創建一個字元串數組類型：

create or replace type string_array as varray(1000) of varchar2(10);
/

2. 在with子句中使用它來將值列表構造為虛擬表，並在查詢中使用該表，如下所示：

with t as (
 select column_value as code from table(string_array('1-ABCD','1-BCDE',...))
)
select 'Accounts', row_id Acct_id, pr_postn_id
from schema.s_org_ext
where pr_postn_id in (select code from t)
UNION
select 'Orders', row_id Order_id, pr_postn_id
from schema.s_orders
where pr_postn_id in (select code from t)
UNION
...

由於您無法創建任何內容，因此您唯一的選擇是顯式編寫 ID 列表。為避免重複，您可以將其放入公用表表達式中：

with params (id) as (
 select '1-ABCD' from dual union all 
 select '1-BCDE' from dual union all 
 ....
 select '999-XYZ' from dual
)  
select 'Accounts', row_id Acct_id, pr_postn_id
from schema.s_org_ext
where pr_postn_id in (select id from params)
UNION ALL
select 'Orders', row_id Order_id, pr_postn_id
from schema.s_orders
where pr_postn_id in (select id from params)

由於 Oracle 仍然不支持 VALUES 子句，您需要編寫多行來select ... from dual將所有值聯合在一起 - 但至少您只需要編寫一次。

創建一個為您生成的電子表格select .. from dual union all ... select（基於“純值”，可能會使維護此列表更容易一些。然後將生成的值從電子表格複製並粘貼到 WITH 子句中。

引用自：https://dba.stackexchange.com/questions/281558