我们可以在execute immediate中使用集合变量作为表吗

Question

Declare
Type t_approved_node is record( node_rowid Hr 
node_rowid%type, Node_+type hr.node_type%type);
Type t_val is table of t_approved_node Index by pls_integer;
V_node t_val;
V_tab varchar2(20);
V_col varchar2(400);
V_nrf_flg hr.hr_flag%type;
V_ubrf_flg hr.hr_flag%type := 3;
V_col_str varchar2(4000);
Begin
Begin
Select hr_flag into v_nrf_flg from hr;
End;
Begin
Select h.node_rowid, h.node_type bulk collect into v_node 
from hr h,  hr_attr_wfm  haw
Where h.hr_relation_id = haw.uc_hr_relation_id
And h.node_type = 'UBR';
Begin
V_tab := 'UC_UBR';
Select listagg(column_name, ',' within group(order by 
column_id)
Into v_col from user_tab_columns where table_name = v_tab;
End;
V_col_str := regex_replace( v_col, 'HR_FLAG', v_ubrf_flg);
Execute immediate ' insert into ' || v_tab || '( ' ||
V_col || ') ' || ' select '|| v_col_str || ' from ' ||
V_tab || 'R ' || q' [ where node_type = ' UBR' a 
and hr_flag =:1 and  exists( ] ' || ' select 1 ' || ' from table( ' || 
v_node ||  ')y' || q' [ where y.node_rowid = R.node_rowid ] )'
Using v_nrf_flag;
End;
End;

我试图执行上面的块得到下面的错误。

调用||时参数的数量或类型错误

最终查询应类似

insert into UC_UBR ( v_col)/*3 columns into v_col variable*/
select v_col_str /* 3 columns in v_col_str variable*/ from UC_UBR R where  hr_flag =:1
and    exists
(select 1 from table(v_node) /*collection variable*/ y
where y.node_rowid = r.node_rowod;

有人能帮忙吗？

Answer 1

您的示例代码充满了错误，根本没有任何意义。但如果我把重点放在你的问题上，那么答案是"是的"；。参见此示例：

CREATE OR REPLACE TYPE t_app AS OBJECT( nodeid NUMBER, Nodetype VARCHAR2(100));
CREATE OR REPLACE TYPE t_val IS TABLE OF t_app;
DECLARE

V_node t_val;
V_result t_val;
V_app t_app;
V_count NUMBER;
Sql_stmt VARCHAR2(100);

nodeid NUMBER; 
Nodetype VARCHAR2(100);
BEGIN
SELECT t_app(nodeid, Nodetype) BULK COLLECT INTO V_node FROM HR;

Sql_stmt := 'SELECT count(*) FROM TABLE(:t)';
EXECUTE IMMEDIATE Sql_stmt INTO V_count USING V_node;
DBMS_OUTPUT.PUT_LINE ( 'V_count = ' || V_count );
Sql_stmt := 'SELECT nodeid, Nodetype FROM TABLE(:t) WHERE ROWNUM = 1';
EXECUTE IMMEDIATE Sql_stmt INTO nodeid, Nodetype USING V_node;
DBMS_OUTPUT.PUT_LINE ( 'nodeid = ' || nodeid  );
DBMS_OUTPUT.PUT_LINE ( 'Nodetype = ' || Nodetype  );
Sql_stmt := 'SELECT t_app(nodeid, Nodetype) FROM TABLE(:t) WHERE ROWNUM = 1';
EXECUTE IMMEDIATE Sql_stmt INTO V_app USING V_node;
DBMS_OUTPUT.PUT_LINE ( 'V_app =  ' || XMLTYPE(V_app).getClobVal()  );
Sql_stmt := 'SELECT t_app(nodeid, Nodetype) FROM TABLE(:t)';
EXECUTE IMMEDIATE Sql_stmt BULK COLLECT INTO V_result USING V_node;
END;

Answer 2

如果您发布了您使用的真实代码，这将有所帮助，因为其中充满了语法错误(缺少sql_stmt局部变量声明put.line(？((。

我不知道你打算用这样一个select语句做什么，因为你不能执行它，它没有任何意义，但是——给你；请参见第20行。

SQL> set serveroutput on
SQL>
SQL> DECLARE
2     TYPE t_app IS RECORD
3     (
4        nodeid     NUMBER,
5        Nodetype   VARCHAR2 (20)
6     );
7
8     TYPE t_val IS TABLE OF t_app
9        INDEX BY PLS_INTEGER;
10
11     V_node    t_val;
12     V_tab     VARCHAR2 (20);
13
14     sql_stmt  VARCHAR2 (200);
15  BEGIN
16     SELECT empno, ename
17       BULK COLLECT INTO v_node
18       FROM emp;
19
20     Sql_stmt := 'select 1 from (' || v_node (1).nodeid || 'Y)';
21
22     DBMS_OUTPUT.put_line (sql_stmt);
23  END;
24  /
select 1 from (7369Y)
PL/SQL procedure successfully completed.
SQL>