根据Oracle SQL中的配置向表中添加列

enxuqcxy  于 2023-03-29  发布在  Oracle
关注(0)|答案(3)|浏览(141)

我想根据一个配置来构建一个表，该配置告诉我从数据库中的哪个表添加哪个列，并使用哪个连接条件。为了更清楚，我有以下设置：

  • 我的输入表（称之为INPUT）只有两个字段：KEY1和KEY2。它们中的每一个表示独立密钥（没有重复）而不是联合密钥;
  • 一个配置（称之为CONF），由以下列组成：
SOURCE
FIELD
KEY
LABEL

SOURCE列是我的数据库中一个现有表的名称，它包含字段KEY 1或字段KEY 2。FIELD是SOURCE中一个列的名称。根据如何与INPUT表连接，KEY可以假定值KEY 1或KEY 2。最后，LABEL是一个任意名称。CONF表由大约20行组成。
对于每一行，我想根据行的具体信息在INPUT表中添加一列。在伪代码中，这将是这样的：

SELECT INPUT.*, {SOURCE}.{FIELD} AS {LABEL}
FROM INPUT
LEFT JOIN {SOURCE} ON INPUT.{KEY} = {SOURCE}.{KEY}

迭代上面的内容，最终你得到一个2 + CONF表的行数和INPUT表的相同行数的表。上面的内容，至少对我来说，用编程语言实现是很明显的。我有一个带有远程db服务器的python应用程序，我可以在python中循环并收集部分结果。然而，这将意味着大量的网络流量和应用程序的大量内存使用（oracle db服务器的资源要大得多）。所以，理想的做法是让db创建这个表，然后在python中获取一个块，后处理它，然后转到下一个块。
其他重要内容：

  • 我们的数据库连接只有选择、插入和更新权限。我们不能创建新表。
  • CONF表可能会有所不同，应用程序的用户可以向其中添加新行（这将转换为输出的新列）。
  • INPUT表大约有150万行。

有可能完全在oracle sql中完成上述操作吗？

oracle

3条答案

按热度按时间
8hhllhi2

8hhllhi21#

您的指示
左连接{SOURCE}打开INPUT.{KEY}={SOURCE}.{KEY}
表示要连接的现有表具有与INPUT TABLE相同的列- KEY_1和/或KEY_2。
我创建了两个这样的表ET_1和ET_2：

Select * From ET_1;
FLD_1                FLD_2                KEY_1                KEY_2               
-------------------- -------------------- -------------------- --------------------
ABC                  DEF                  G                    H                  

Select * From ET_2;
COL_1                COL_2                KEY_2               
-------------------- -------------------- --------------------
PQR                  STU                  D

... INPUT_TBL和CONF_TBL用于测试目的...

Select * From INPUT_TBL;
KEY_1                KEY_2               
-------------------- --------------------
A                    B                   
C                    D                   
E                    F                   
G                    H                   
I                    J                   
K                    L                  

Select * From CONF_TBL;
SOURCE               FIELD                KEY                  LABEL               
-------------------- -------------------- -------------------- --------------------
ET_1                 FLD_1                KEY_1                LBL_1               
ET_1                 FLD_2                KEY_2                LBL_2               
ET_2                 COL_1                KEY_2                LBL_3               
ET_2                 COL_2                KEY_2                LBL_4

您所要求的SQL代码可以使用PL/SQL动态生成：

SET SERVEROUTPUT ON
Declare
    CURSOR c IS Select "SOURCE", "FIELD", "KEY", "LABEL" From CONF_TBL;
    mSource   VarChar2(20);
    mField    VarChar2(20);
    mKey      VarChar2(20);
    mLabel    VarChar2(20);
    mSQL_sel  VarChar2(1000) := 'Select i.*, ';
    mSQL_from VarChar2(1000) := 'From INPUT_TBL i ';
    mSQL_join VarChar2(1000);
    mSQL      VarChar2(4000);
    cnt       Number  := 0;
    ta        VarChar2(32);
Begin
    OPEN c;
    LOOP
        Fetch c Into mSource, mField, mKey, mLabel;
        Exit When c%NOTFOUND;
        cnt := cnt + 1;
        ta := 'ta' || cnt;
        mSQL_sel := mSQL_sel || ta || '.' || mField || ' "' || Nvl(mLabel, ta || '_' || mField) || '", ';
        mSQL_join := mSQL_join || 'LEFT JOIN ' || mSource || ' ' || ta || ' ON(' || ta || '.' || mKey || ' = i.' || mKey || ')' || Chr(10);

    END LOOP;
    CLOSE c;
    mSQL_sel := SubStr(mSQL_sel, 1, Length(mSQL_sel) -2);
    mSQL_join := SubStr(mSQL_join, 1, Length(mSQL_join) -1);
    mSQL := mSQL_sel || Chr(10) || mSQL_from || Chr(10) || mSQL_join;
    DBMS_OUTPUT.PUT_LINE(mSQL);
End;
/

结果（mSQL）代码可以用作常规sql或refcursor或其他任何东西……下面是根据上述表生成的代码和结果

Select i.*, ta1.FLD_1 "LBL_1", ta2.FLD_2 "LBL_2", ta3.COL_1 "LBL_3", ta4.COL_2 "LBL_4"
From INPUT_TBL i 
LEFT JOIN ET_1 ta1 ON(ta1.KEY_1 = i.KEY_1)
LEFT JOIN ET_1 ta2 ON(ta2.KEY_2 = i.KEY_2)
LEFT JOIN ET_2 ta3 ON(ta3.KEY_2 = i.KEY_2)
LEFT JOIN ET_2 ta4 ON(ta4.KEY_2 = i.KEY_2)

PL/SQL procedure successfully completed.

KEY_1                KEY_2                LBL_1                LBL_2                LBL_3                LBL_4               
-------------------- -------------------- -------------------- -------------------- -------------------- --------------------
C                    D                                                              PQR                  STU                 
A                    B                                                                                                       
G                    H                    ABC                  DEF                                                           
E                    F                                                                                                       
K                    L                                                                                                       
I                    J                                                                                                       

6 rows selected.

如果表CONF_TBL中未定义标签（可选），则结果如下：

Select i.*, ta1.FLD_1 "ta1_FLD_1", ta2.FLD_2 "ta2_FLD_2", ta3.COL_1 "ta3_COL_1", ta4.COL_2 "ta4_COL_2"
From INPUT_TBL i 
LEFT JOIN ET_1 ta1 ON(ta1.KEY_1 = i.KEY_1)
LEFT JOIN ET_1 ta2 ON(ta2.KEY_2 = i.KEY_2)
LEFT JOIN ET_2 ta3 ON(ta3.KEY_2 = i.KEY_2)
LEFT JOIN ET_2 ta4 ON(ta4.KEY_2 = i.KEY_2)

PL/SQL procedure successfully completed.

KEY_1                KEY_2                ta1_FLD_1            ta2_FLD_2            ta3_COL_1            ta4_COL_2           
-------------------- -------------------- -------------------- -------------------- -------------------- --------------------
C                    D                                                              PQR                  STU                 
A                    B                                                                                                       
G                    H                    ABC                  DEF                                                           
E                    F                                                                                                       
K                    L                                                                                                       
I                    J                                                                                                       

6 rows selected.

*U P D A T E

如果要避免相同联接的可能重复，可以更改光标以在同一行中生成（重复联接的）字段。

CURSOR c IS 
              Select "SOURCE", "FIELD" as FIELD_1, 
                  CASE  WHEN CNT = 2 THEN 'xxx'
                        WHEN LEAD(CNT, 1) OVER(Order By "SOURCE", "KEY",  "FIELD") = 2 
                        THEN LEAD("FIELD", 1) OVER(Order By "SOURCE", "KEY",  "FIELD")
                  END as FIELD_2, 
                  "KEY", "LABEL"
              From  ( Select  "SOURCE", "FIELD", "KEY", "LABEL", 
                              Count("FIELD") OVER(Partition By "SOURCE", "KEY" Order By "FIELD") "CNT"
                      From    CONF_TBL
                      Order By "SOURCE", "KEY",  "FIELD" );
--
--  Cursor result
SOURCE               FIELD_1              FIELD_2              KEY                  LABEL               
-------------------- -------------------- -------------------- -------------------- --------------------
ET_1                 FLD_1                                     KEY_1                                    
ET_1                 FLD_2                                     KEY_2                                    
ET_2                 COL_1                COL_2                KEY_2                                    
ET_2                 COL_2                xxx                  KEY_2

接下来，您应该调整代码以处理两个字段…

SET SERVEROUTPUT ON
Declare
    CURSOR c IS 
              Select "SOURCE", "FIELD" as FIELD_1, 
                  CASE  WHEN CNT = 2 THEN 'xxx'
                        WHEN LEAD(CNT, 1) OVER(Order By "SOURCE", "KEY",  "FIELD") = 2 
                        THEN LEAD("FIELD", 1) OVER(Order By "SOURCE", "KEY",  "FIELD")
                  END as FIELD_2, 
                  "KEY", "LABEL"
              From  ( Select  "SOURCE", "FIELD", "KEY", "LABEL", 
                              Count("FIELD") OVER(Partition By "SOURCE", "KEY" Order By "FIELD") "CNT"
                      From    CONF_TBL
                      Order By "SOURCE", "KEY",  "FIELD" );
    mSource   VarChar2(20);
    mField_1   VarChar2(20);
    mField_2   VarChar2(20);
    mKey      VarChar2(20);
    mLabel    VarChar2(20);
    mSQL_sel  VarChar2(1000) := 'Select i.*, ';
    mSQL_from VarChar2(1000) := 'From INPUT_TBL i ';
    mSQL_join VarChar2(1000);
    mSQL      VarChar2(4000);
    cnt       Number  := 0;
    ta        VarChar2(32);
Begin
    OPEN c;
    LOOP
        Fetch c Into mSource, mField_1, mField_2, mKey, mLabel;
        Exit When c%NOTFOUND;
        If mField_2 = 'xxx' Then
            GoTo NextRow;
        End If;
        cnt := cnt + 1;
        ta := 'ta' || cnt;
        --
        mSQL_sel := mSQL_sel || ta || '.' || mField_1 || ' "' || Nvl(mLabel, ta || '_' || mField_1) || '", ';
        If mField_2 Is Not Null And mField_2 != 'xxx' Then
            mSQL_sel := mSQL_sel || ta || '.' || mField_2 || ' "' || Nvl(mLabel, ta || '_' || mField_2) || '_A' || '", ';
        End If;
        If mField_2 Is Null OR mField_2 != 'xxx' Then
            mSQL_join := mSQL_join || 'LEFT JOIN ' || mSource || ' ' || ta || ' ON(' || ta || '.' || mKey || ' = i.' || mKey || ')' || Chr(10);
        End If;
        <<NextRow>>
        Null;
    END LOOP;
    CLOSE c;
    mSQL_sel := SubStr(mSQL_sel, 1, Length(mSQL_sel) -2);
    mSQL_join := SubStr(mSQL_join, 1, Length(mSQL_join) -1);
    mSQL := mSQL_sel || Chr(10) || mSQL_from || Chr(10) || mSQL_join;
    DBMS_OUTPUT.PUT_LINE(mSQL);
End;
/

SQL命令，它的结果是：

Select i.*, ta1.FLD_1 "ta1_FLD_1", ta2.FLD_2 "ta2_FLD_2", ta3.COL_1 "ta3_COL_1", ta3.COL_2 "ta3_COL_2_A"
From INPUT_TBL i 
LEFT JOIN ET_1 ta1 ON(ta1.KEY_1 = i.KEY_1)
LEFT JOIN ET_1 ta2 ON(ta2.KEY_2 = i.KEY_2)
LEFT JOIN ET_2 ta3 ON(ta3.KEY_2 = i.KEY_2)

PL/SQL procedure successfully completed.

KEY_1                KEY_2                ta1_FLD_1            ta2_FLD_2            ta3_COL_1            ta3_COL_2_A         
-------------------- -------------------- -------------------- -------------------- -------------------- --------------------
C                    D                                                              PQR                  STU                 
A                    B                                                                                                       
G                    H                    ABC                  DEF                                                           
E                    F                                                                                                       
K                    L                                                                                                       
I                    J                                                                                                       

6 rows selected.
赞(0）分享  回复(0）举报  2023-03-29
bvuwiixz

bvuwiixz2#

对于这种固定列数的特殊情况，您可以使用SQL_MACRO（从19.7开始可用）和本地PL/SQL声明来生成带有marco的SQL查询，并将其作为“标准”SQL结果集使用。
下面是一个例子。
准备一些表格：

begin
  for i in 1..5 loop
    execute immediate replace(replace(q'{create table src^^i^^
    as
    select
      lpad(abs(^^reverse^^ - level), 3, '0') as source_key_^^i^^
      , 'This is a row #' || level || ' from src^^i^^' as label_^^i^^
    from dual
    connect by level < 10
    }', '^^i^^', i), '^^reverse^^', mod(i, 2)*99);
  end loop;
end;

灌装配置：

insert into conf values('src1', 'source_key_1', 'key2', 'label_1');
insert into conf values('src2', 'source_key_2', 'key1', 'label_2');
insert into conf values('src3', 'source_key_3', 'key1', 'label_3');

动态构建动态查询：

with function f_get_query
return varchar2
sql_macro(table)
as
  res varchar2(32000);
begin
  select
    listagg(replace(replace(replace(replace(replace(q'{
        select ^^conf^^ as src, input_.*, c.^^label^^ as label_VALUE
        from input_
          left join ^^tab^^ c
          on input_.^^input_key^^ = c.^^tab_key^^
      }',
      '^^tab^^', dbms_assert.sql_object_name(conf.source)),
      '^^tab_key^^', conf.field),
      '^^input_key^^', conf.key_),
      '^^label^^', conf.label),
      '^^conf^^', dbms_assert.enquote_literal(conf.source)
    ), chr(10) || 'union all' || chr(10)) as res

    into res
  from conf;

  return res;
end;

select *
from f_get_query()
order by 1,2,3

对于这些数据

create table input_ (key1, key2)
as
select
  lpad(level, 3, '0')
  , lpad(99 - level, 3, '0')
from dual
connect by level < 13

它返回
| SRC|按键1|按键2|标签值|
| --------------|--------------|--------------|--------------|
| src 1基因|零零一|小零九八|这是src 1中的第1行|
| src 1基因|002|小零九七|这是src 1中的第2行|
| src 1基因|零零三|零九六|这是src 1中的第3行|
| src 1基因|004|小零九五|这是src 1中的第4行|
| src 1基因|005|小零九四|这是src 1中的第5行|
| src 1基因|零零六|零九三|这是src 1中的第6行|
| src 1基因|007|小零九二|这是src 1中的第7行|
| src 1基因|小零零八|小零九一|这是src 1中的第8行|
| src 1基因|009|小零九零|这是src 1中的第9行|
| src 1基因|零一零|零八九|* 无效 |
| src 1基因|011|零八八| 无效 |
| src 1基因|012|零八七| 无效 |
| src 2基因|零零一|小零九八|这是src 2中的第1行|
| src 2基因|002|小零九七|这是src 2中的第2行|
| src 2基因|零零三|零九六|这是src 2中的第3行|
| src 2基因|004|小零九五|这是src 2中的第4行|
| src 2基因|005|小零九四|这是src 2中的第5行|
| src 2基因|零零六|零九三|这是src 2中的第6行|
| src 2基因|007|小零九二|这是src 2中的第7行|
| src 2基因|小零零八|小零九一|这是src 2中的第8行|
| src 2基因|009|小零九零|这是src 2中的第9行|
| src 2基因|零一零|零八九| 无效 |
| src 2基因|011|零八八| 无效 |
| src 2基因|012|零八七| 无效 |
| src 3基因|零零一|小零九八| 无效 |
| src 3基因|002|小零九七| 无效 |
| src 3基因|零零三|零九六| 无效 |
| src 3基因|004|小零九五| 无效 |
| src 3基因|005|小零九四| 无效 |
| src 3基因|零零六|零九三| 无效 |
| src 3基因|007|小零九二| 无效 |
| src 3基因|小零零八|小零九一| 无效 |
| src 3基因|009|小零九零| 无效 |
| src 3基因|零一零|零八九| 无效 |
| src 3基因|011|零八八| 无效 |
| src 3基因|012|零八七| 无效 *|
db<>fiddle

赞(0）分享  回复(0）举报  2023-03-29
fnx2tebb

fnx2tebb3#

普通SQL中的动态SQL - DBMS_XMLGEN

如果你必须使用普通SQL，并且不能创建或调用任何PL/SQL对象，你仍然可以使用DBMS_XMLGEN trick来创建动态SQL。但是这种方法有几个缺点：
1.查询必须返回一个静态数字、类型和列。这对你来说可能仍然有效，因为你只有一个动态列。但是，该列总是需要转换为字符串，这通常是一个糟糕的选择。你不能动态地更改列的名称，但你可以将标签作为一个单独的列返回。
1.查询很难构建和理解。
1.性能可能会受到影响。

PL/SQL对象中的动态SQL-返回动态SYS_REFCURSOR

返回动态数据的更常见，也更容易的方法是创建一个returns a SYS_REFCURSOR built from a string的PL/SQL函数或过程。这种方法的小缺点是：
1.您必须能够在正确的模式上创建PL/SQL对象。请记住，您的模式不一定需要创建对象的权限;你可以要求一个特权用户，比如DBA，为你创建简单的PL/SQL对象。如果你有一个合理的DBA，他们不会让你在模式上创建任何对象的情况是非常罕见的。
1.您的应用程序必须能够使用PL/SQL游标。

赞(0）分享  回复(0）举报  2023-03-29
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备13028337号-1 大数据知识库 https://www.saoniuhuo.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com