oracle - 如何在选择 Oracle 中运行动态查询？

Question

我需要这样的东西：

begin
for group_level in (select * from group_level)
loop
execute immediate 'select :gost from erp_trips' using group_level.gost_id;
end loop;
end;

Group_level 是一个表，其中 gost_id 是您需要从 erp_trips 获取的字段的名称（在本例中为“GOSB”）

代码执行了，但是表没有输出，是什么问题？

标签： oracle

Answer 1

您可以使用：

DECLARE
  p_sql  CLOB;
  c_cur  SYS_REFCURSOR;
  p_gost VARCHAR2(200);
BEGIN
  FOR p_row IN (SELECT gost_id FROM group_level) LOOP
    IF p_sql IS NOT NULL THEN
      p_sql := p_sql || ' UNION ALL ';
    END IF;
    p_sql := p_sql || 'SELECT ' || p_row.gost_id || ' AS gost FROM erp_trips';
  END LOOP;
  OPEN c_cur FOR p_sql;
  LOOP
    FETCH c_cur INTO p_gost;
    EXIT WHEN c_cur%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE( p_gost );
  END LOOP;
END;
/

其中，对于样本数据：

CREATE TABLE group_level ( gost_id ) AS
SELECT 'GOSTA' FROM DUAL UNION ALL
SELECT 'GOSTC' FROM DUAL UNION ALL
SELECT 'GOSTD' FROM DUAL;

CREATE TABLE erp_trips ( gosta, gostb, gostc, gostd ) AS
SELECT LEVEL || 'A', LEVEL || 'B', LEVEL || 'C', LEVEL || 'D'
FROM   DUAL
CONNECT BY LEVEL <= 5;

输出：

1A
2A
3A
4A
5A
1C
2C
3C
4C
5C
1D
2D
3D
4D
5D

db<>在这里摆弄