在Oracle 12c中动态构建select语句 [英] Dynamically build select statement in Oracle 12c

问题描述

我之前曾发布过类似的问题，但该问题的解决方案似乎将完全不同，因此，我希望这不符合重新发布的条件.

I have posted the similar question before but the solution to this question seems like it will be completely different, thus I hope this does not qualify for a repost.

要求: 我在名为SETUPS的表中有2列，其中包含以下列:

Req: I have 2 columns in a table named SETUPS with the following columns:

ID INTEGER NOT NULL RPT_SCRIPT CLOB NOT NULL

RPT_SCRIPT在每个记录中都有select语句.以下是Clob列WHERE ID = 1中的一条语句:

RPT_SCRIPT has select statements in each record. Below is a statement in the clob column WHERE ID = 1:

SELECT ID,
 Title,
 Desc,
 Type,
 LVL_CNT,
 TYPE_10 VALUE_10,
 TYPE_9 VALUE_9,
 TYPE_8 VALUE_8,
 TYPE_7 VALUE_7,
 TYPE_6 VALUE_6,
 TYPE_5 VALUE_5,
 TYPE_4 VALUE_4,
 TYPE_3 VALUE_3,
 TYPE_2 VALUE_2,
 TYPE_1 VALUE_1
  FROM SCHEMA.TABLE
WHERE ID = 1;

目前，我正在为所有记录手动编写这些选择语句.

Currently I am writing these select statements manually for all records.

SETUPS.ID映射到另一个主表META.ID，以便构建选择语句.

SETUPS.ID is mapped to another master table META.ID in order to build the select statement.

模式为TYPE_%的列名称(即TYPE_1)来自META表；表中总共有20列具有这种模式，但是在本示例中，由于META.LVL_CNT = 10，我仅使用了10列.同样，如果META.LVL_CNT = 5，则仅选择列TYPE_1，TYPE_2，TYPE_3，TYPE_4，TYPE_5.

The column names with pattern TYPE_%, i.e. TYPE_1, come from the META table; there are total of 20 columns in the table with this pattern but in this example I've used only 10 because META.LVL_CNT = 10. Similarly if META.LVL_CNT = 5 then only select columns TYPE_1,TYPE_2,TYPE_3,TYPE_4,TYPE_5.

列别名，即VALUE_1，是来自相应列META.ID = 1的值(如本例所示). 将始终提供ID，因此可用于查询表META.

The column aliases, i.e. VALUE_1, are values which come from the corresponding column where META.ID = 1 (as in this example). ID will always be provided, so it can be used to query table META.

EDIT 如我在示例中所示，来自META表的列别名将永远不会具有模式，但是使用LVL_CNT时，在运行时我们将知道列数.我尝试使用@Asfakul提供的逻辑并使用动态检索的列名构建了一个动态sql，但是当使用EXECUTE IMMEDIATE INTO时，我意识到我不知道将检索到多少列，因此将无法动态生成别名.用这种方法.

EDIT The column aliases which come from META table will never have a pattern as I have shown in my example, but with LVL_CNT, at runtime we will know the number of columns. I tried to @Asfakul's provided logic and built a dynamic sql using the column names retrieved dynamically but when using EXECUTE IMMEDIATE INTO I realized I don't know how many columns will be retrieved and therefore wouldn't be able to dynamically generate the alias name with this method.

需要一种使用以上信息自动构建此选择语句的方法.我如何实现此目的?请提供任何示例.

Need an approach to automatically build this select statment using above information.. how can I achieve this? Please provide any examples.

推荐答案

您可以以此为基础

declare
    upper_level number;
    t_sql varchar2(1000);
    l_sql varchar2(1000);
begin
    select lvl_cnt into upper_level from 
    SETUPS S,META S
    where s.id=m.id

    l_sql:='SELECT ID,
     Title,
     Desc,
     Type,'||
     upper_level


    for lvl in 1..upper_level
     loop
         t_sql:=t_sql||'type_'||lvl||','
     end loop;
     l_sql:=l_sql||t_sql
     l_sql:=rtrim(l_sql,',');
     l_sql:=l_sql||' FROM SCHEMA.TABLE
    WHERE ID = 1;';

end

这篇关于在Oracle 12c中动态构建select语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！