oracle PL/SQL多行动态查询

6gpjuf90 于 2023-01-04 发布在 Oracle

关注(0)|答案(2)|浏览(189)

我正在尝试编写一个PL/SQL脚本，该脚本读取表并基于rule_id和parameter_id生成动态查询。
我的脚本应该基于规则ID和参数ID从parameter_value读取，并在动态查询中使用参数值。
我的表rules看起来像这样：

这是我的脚本-我做错了什么？我得到了一个错误
ORA-01747无效的用户.表.列、表.列或列说明

declare 
v_rule_id number(10);
v_parameter_id number(10);
v_parameter_value varchar2(100);
v_source_table varchar2(100);
v_lookup_table varhcar2(100);
v_source_column varchar2(100);
v_lookup_column varchar2(100);
v_date varhchar2(100);
v_query varchar2(1000);

BEGIN
FOR RL IN (SELECT RULE_ID FROM RULE)
LOOP
FOR PRM IN (SELECT PARAMETER_ID,PARAMETER_VALUE FROM RULE)
LOOP
IF PRM.PARAM_ID = 1 THEN 
v_source_table:= PRM.PARAMETER_VALUE;
ELSIF PRM.PARAM_ID = 2 THEN
V_lookup_table := PRM.PARAMETER_VALUE;
ELSIF PRM.PARAM_ID = 3 THEN
V_source_column := PRM.PARAMETER_VALUE;
ELSIF PRM.PARAM_ID = 4 THEN
V_lookup_column := PRM.PARAMETER_VALUE;
ELSIF PRM.PARAM_ID = 5 THEN
V_date := PRM.PARAMETER_VALUE;
END IF;
v_query := 'SELECT * FROM (SELECT DISTINCT A.' || v_source_column || ', count(*) as count from'|| v_source_table || ' A LEFT JOIN' || V_lookup_table || ' ON A.'||V_source_column ||' = B.'|| V_lookup_column || 'WHERE B.'||V_lookup_table||' IS NULL GROUP BY A.'||V_source_column  ||'ORDER BY 2 DESC' );
EXECUTE IMMEDIATE v_query;
END LOOP;
END LOOP;
END;

oracle

来源：https://stackoverflow.com/questions/74982472/pl-sql-dynamic-query-based-on-multiple-rows

2条答案

按热度按时间

lmvvr0a81#

好吧......调试一下，你就会发现问题出在哪里。

提示所有未来堆栈溢出的帖子：在粘贴代码之前选中并运行代码。
第二个提示：写几行，然后测试，如果需要的话修改，然后继续写几行。否则你会以大量的错误结束，你会失去概述。
第三个技巧：使用dbms_output.put_line（或日志框架，如logger）来检测代码。

享受下面的调试过程!
手动创建样本数据，因为海报提供了一个截图。请提供这个代码自己下次-这是你的工作，而不是我们的。

CREATE TABLE rule (RULE_ID,PARAMETER_ID,PARAMETER_EXPLANATION,PARAMETER_VALUE) AS
SELECT 1,1,'TABLE_1','A' FROM DUAL UNION ALL 
SELECT 1,2,'TABLE_2','B' FROM DUAL UNION ALL 
SELECT 1,3,'COLUMN_1','X' FROM DUAL UNION ALL 
SELECT 1,4,'COLUMN_2','Y' FROM DUAL UNION ALL 
SELECT 1,5,'DATE','20221231' FROM DUAL UNION ALL 
SELECT 2,1,'TABLE_1','C' FROM DUAL UNION ALL 
SELECT 2,2,'TABLE_2','D' FROM DUAL UNION ALL 
SELECT 2,3,'COLUMN_1','Z' FROM DUAL UNION ALL 
SELECT 2,4,'COLUMN_2','Q' FROM DUAL UNION ALL 
SELECT 2,5,'DATE','20221231' FROM DUAL;

Table RULE created.

运行上面的代码：

run anonymous pl/sql block

ORA-06550: line 28, column 299:
PLS-00103: Encountered the symbol ")" when expecting one of the following:

   * & = - + ; < / > at in is mod remainder not rem
   <an exponent (**)> <> or != or ~= >= <= <> and or like like2
   like4 likec between || member submultiset
ORA-06550: line 31, column 5:
PLS-00103: Encountered the symbol "LOOP" when expecting one of the following:

   ;
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.

修复第28行运行块中的错误

Error report -
ORA-06550: line 6, column 16:
PLS-00201: identifier 'VARHCAR2' must be declared
ORA-06550: line 0, column 1:
PL/SQL: Compilation unit analysis terminated
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

修复第6行运行块中的错误

ORA-06550: line 9, column 8:
PLS-00201: identifier 'VARHCHAR2' must be declared
ORA-06550: line 0, column 1:
PL/SQL: Compilation unit analysis terminated
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

修复第8行运行块中的错误

Error report -
ORA-06550: line 17, column 8:
PLS-00302: component 'PARAM_ID' must be declared
ORA-06550: line 17, column 1:
PL/SQL: Statement ignored
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

将出现的PARAM_ID替换为PARAMETER_ID，运行块

Error report -
ORA-01747: invalid user.table.column, table.column, or column specification
ORA-06512: at line 29
ORA-06512: at line 29
01747. 00000 -  "invalid user.table.column, table.column, or column specification"
*Cause:    
*Action:

啊...我们得到了错误!
下面是给出原始错误的代码：

declare 
v_rule_id number(10);
v_parameter_id number(10);
v_parameter_value varchar2(100);
v_source_table varchar2(100);
v_lookup_table varchar2(100);
v_source_column varchar2(100);
v_lookup_column varchar2(100);
v_date varchar2(100);
v_query varchar2(1000);

BEGIN
FOR RL IN (SELECT RULE_ID FROM RULE)
LOOP
FOR PRM IN (SELECT PARAMETER_ID,PARAMETER_VALUE FROM RULE)
LOOP
IF PRM.PARAMETER_ID = 1 THEN 
v_source_table:= PRM.PARAMETER_VALUE;
ELSIF PRM.PARAMETER_ID = 2 THEN
V_lookup_table := PRM.PARAMETER_VALUE;
ELSIF PRM.PARAMETER_ID = 3 THEN
V_source_column := PRM.PARAMETER_VALUE;
ELSIF PRM.PARAMETER_ID = 4 THEN
V_lookup_column := PRM.PARAMETER_VALUE;
ELSIF PRM.PARAMETER_ID = 5 THEN
V_date := PRM.PARAMETER_VALUE;
END IF;
v_query := 'SELECT * FROM (SELECT DISTINCT A.' || v_source_column || ', count(*) as count from'|| v_source_table || ' A LEFT JOIN' || V_lookup_table || ' ON A.'||V_source_column ||' = B.'|| V_lookup_column || 'WHERE B.'||V_lookup_table||' IS NULL GROUP BY A.'||V_source_column  ||'ORDER BY 2 DESC';
EXECUTE IMMEDIATE v_query;
END LOOP;
END LOOP;
END;
/

现在是时候做正确的调试了，注解掉EXECUTE IMMEDIATE v_query;并替换add dbms_output.put_line(v_query);，看看你要执行什么。* * 许多行**如：

SELECT * FROM (SELECT DISTINCT A., count(*) as count fromA A LEFT JOIN ON A. = B.WHERE B. IS NULL GROUP BY A.ORDER BY 2 DESC
SELECT * FROM (SELECT DISTINCT A., count(*) as count fromA A LEFT JOINB ON A. = B.WHERE B.B IS NULL GROUP BY A.ORDER BY 2 DESC
SELECT * FROM (SELECT DISTINCT A.X, count(*) as count fromA A LEFT JOINB ON A.X = B.WHERE B.B IS NULL GROUP BY A.XORDER BY 2 DESC
SELECT * FROM (SELECT DISTINCT A.X, count(*) as count fromA A LEFT JOINB ON A.X = B.YWHERE B.B IS NULL GROUP BY A.XORDER BY 2 DESC
SELECT * FROM (SELECT DISTINCT A.X, count(*) as count fromA A LEFT JOINB ON A.X = B.YWHERE B.B IS NULL GROUP BY A.XORDER BY 2 DESC
SELECT * FROM (SELECT DISTINCT A.X, count(*) as count fromC A LEFT JOINB ON A.X = B.YWHERE B.B IS NULL GROUP BY A.XORDER BY 2 DESC

etc...

sql语句是（1）不完整的，（2）关键字是串联的，而且有太多的行。内部循环和外部循环都做了一个完整的选择。
...一些工作...
最终溶液：

set serveroutput on size 999999
clear screen
declare 
v_rule_id number(10);
v_parameter_id number(10);
v_parameter_value varchar2(100);
v_source_table varchar2(100);
v_lookup_table varchar2(100);
v_source_column varchar2(100);
v_lookup_column varchar2(100);
v_date varchar2(100);
v_query varchar2(1000);

BEGIN
FOR RL IN (SELECT RULE_ID FROM RULE)
LOOP
FOR PRM IN (SELECT PARAMETER_ID,PARAMETER_VALUE FROM RULE WHERE rule_id = rl.rule_id)
LOOP
IF PRM.PARAMETER_ID = 1 THEN 
v_source_table:= PRM.PARAMETER_VALUE;
ELSIF PRM.PARAMETER_ID = 2 THEN
V_lookup_table := PRM.PARAMETER_VALUE;
ELSIF PRM.PARAMETER_ID = 3 THEN
V_source_column := PRM.PARAMETER_VALUE;
ELSIF PRM.PARAMETER_ID = 4 THEN
V_lookup_column := PRM.PARAMETER_VALUE;
ELSIF PRM.PARAMETER_ID = 5 THEN
V_date := PRM.PARAMETER_VALUE;
END IF;
END LOOP;
v_query := 'SELECT * FROM (SELECT DISTINCT A.' || v_source_column || ', count(*) as count from '|| v_source_table || ' A LEFT JOIN ' || V_lookup_table || ' ON A.'||V_source_column ||' = B.'|| V_lookup_column || ' WHERE B.'||V_lookup_table||' IS NULL GROUP BY A.'||V_source_column  ||' ORDER BY 2 DESC' ;
dbms_output.put_line(v_query);
--EXECUTE IMMEDIATE v_query; --uncomment if all tables exist.
END LOOP;
END;
/

SELECT * FROM (SELECT DISTINCT A.X, count(*) as count from A A LEFT JOIN B ON A.X = B.Y WHERE B.B IS NULL GROUP BY A.X ORDER BY 2 DESC
SELECT * FROM (SELECT DISTINCT A.X, count(*) as count from A A LEFT JOIN B ON A.X = B.Y WHERE B.B IS NULL GROUP BY A.X ORDER BY 2 DESC
SELECT * FROM (SELECT DISTINCT A.X, count(*) as count from A A LEFT JOIN B ON A.X = B.Y WHERE B.B IS NULL GROUP BY A.X ORDER BY 2 DESC
SELECT * FROM (SELECT DISTINCT A.X, count(*) as count from A A LEFT JOIN B ON A.X = B.Y WHERE B.B IS NULL GROUP BY A.X ORDER BY 2 DESC
SELECT * FROM (SELECT DISTINCT A.X, count(*) as count from A A LEFT JOIN B ON A.X = B.Y WHERE B.B IS NULL GROUP BY A.X ORDER BY 2 DESC
SELECT * FROM (SELECT DISTINCT A.Z, count(*) as count from C A LEFT JOIN D ON A.Z = B.Q WHERE B.D IS NULL GROUP BY A.Z ORDER BY 2 DESC
SELECT * FROM (SELECT DISTINCT A.Z, count(*) as count from C A LEFT JOIN D ON A.Z = B.Q WHERE B.D IS NULL GROUP BY A.Z ORDER BY 2 DESC
SELECT * FROM (SELECT DISTINCT A.Z, count(*) as count from C A LEFT JOIN D ON A.Z = B.Q WHERE B.D IS NULL GROUP BY A.Z ORDER BY 2 DESC
SELECT * FROM (SELECT DISTINCT A.Z, count(*) as count from C A LEFT JOIN D ON A.Z = B.Q WHERE B.D IS NULL GROUP BY A.Z ORDER BY 2 DESC
SELECT * FROM (SELECT DISTINCT A.Z, count(*) as count from C A LEFT JOIN D ON A.Z = B.Q WHERE B.D IS NULL GROUP BY A.Z ORDER BY 2 DESC

如果每个select语句中的所有表实际上都存在于数据库中，则这将成功执行。

赞(0）回复(0）举报 2023-01-04

hgc7kmma2#

如果您可以不用PL/SQL而只使用普通SQL来获得查询会怎样？
假设您的两个表如下所示：

CREATE TABLE
  A_TBL_1 (ID, TXT, SOME_COL, COL_1_T1, DATE_T1) AS
      (
          SELECT 1,   'TEXT for ID 1', 'Something else 1 in tbl_1', 'X',  To_Date('20221231', 'yyyymmdd') From Dual Union All
          SELECT 2,   'TEXT for ID 2', 'Something else 2 in tbl_1', 'Y',  To_Date('20221231', 'yyyymmdd') From Dual Union All
          SELECT 3,   'TEXT for ID 3', 'Something else 3 in tbl_1', 'Z',  To_Date('20221231', 'yyyymmdd') From Dual
      );
CREATE TABLE     
  A_TBL_2 (ID, TXT, SOME_COL, COL_1_T2) AS
      (
          SELECT 11,   'TEXT for ID 11', 'Something else 11 in tbl_2', 'X' From Dual Union All
          SELECT 12,   'TEXT for ID 12', 'Something else 12 in tbl_2', 'Y' From Dual Union All
          SELECT 13,   'TEXT for ID 13', 'Something else 13 in tbl_2', 'X' From Dual
      );

......而且你的规则是这样设置的

CREATE TABLE
    A_RULE_TBL (RULE_ID, PAR_ID, PAR_EXPL, PAR_VAL) AS
      (
        SELECT 1, 1, 'A_TBL_1',     'a'     FROM DUAL UNION ALL 
        SELECT 1, 2, 'A_TBL2',      'b'     FROM DUAL UNION ALL 
        SELECT 1, 3, 'COL_1_T1',    'X'     FROM DUAL UNION ALL 
        SELECT 1, 4, 'COL_1_T2',    'X'     FROM DUAL UNION ALL 
        SELECT 1, 5, 'DATE_T1',     '20221231' FROM DUAL UNION ALL 
        SELECT 2, 1, 'A_TBL_1',     'a'     FROM DUAL UNION ALL 
        SELECT 2, 2, 'A_TBL_2',     'b'     FROM DUAL UNION ALL 
        SELECT 2, 3, 'COL_1_T1',    'Y'     FROM DUAL UNION ALL 
        SELECT 2, 4, 'COL_1_T2',    'Y'     FROM DUAL UNION ALL 
        SELECT 2, 5, 'DATE_T1',      '20221231' FROM DUAL
      );

如果我们使用CTE（命名参数）透视和取消透视规则_

WITH
    params AS
        (   Select    *
            From      A_RULE_TBL 
            PIVOT (
                    Max(CASE WHEN PAR_ID = 1 THEN PAR_EXPL END) "SRC_TBL",
                    Max(CASE WHEN PAR_ID = 2 THEN PAR_EXPL END) "LKP_TBL",
                    Max(CASE WHEN PAR_ID = 3 THEN PAR_EXPL END) "SRC_COL",
                    Max(CASE WHEN PAR_ID = 4 THEN PAR_EXPL END) "LKP_COL",
                    Max(CASE WHEN PAR_ID = 5 THEN PAR_EXPL END) "DATE",
                    --
                    Max(CASE WHEN PAR_ID = 1 THEN PAR_VAL END) "SRC_TBL_VAL",
                    Max(CASE WHEN PAR_ID = 2 THEN PAR_VAL END) "LKP_TBL_VAL",
                    Max(CASE WHEN PAR_ID = 3 THEN PAR_VAL END) "SRC_COL_VAL",
                    Max(CASE WHEN PAR_ID = 4 THEN PAR_VAL END) "LKP_COL_VAL",
                    Max(CASE WHEN PAR_ID = 5 THEN PAR_VAL END) "DATE_VAL"
                    FOR RULE_ID IN(1 "ID1", 2 "ID2")    )
        
            UNPIVOT(  (SRC_TBL, SRC_COL, LKP_TBL, LKP_COL, A_DATE, SRC_TBL_VAL, SRC_COL_VAL, LKP_TBL_VAL, LKP_COL_VAL, DATE_VAL) 
                        FOR RULE_ID
                        IN  (
                            (ID1_SRC_TBL, ID1_SRC_COL, ID1_LKP_TBL, ID1_LKP_COL, ID1_DATE, ID1_SRC_TBL_VAL, ID1_SRC_COL_VAL, ID1_LKP_TBL_VAL, ID1_LKP_COL_VAL, ID1_DATE_VAL ) as 1,
                            (ID2_SRC_TBL, ID2_SRC_COL, ID2_LKP_TBL, ID2_LKP_COL, ID2_DATE, ID2_SRC_TBL_VAL, ID2_SRC_COL_VAL, ID2_LKP_TBL_VAL, ID2_LKP_COL_VAL, ID2_DATE_VAL ) as 2   )
                  )
            ORDER BY RULE_ID
      )
--  
--  R e s u l t
--     RULE_ID SRC_TBL  SRC_COL  LKP_TBL  LKP_COL  A_DATE   SRC_TBL_VAL SRC_COL_VAL LKP_TBL_VAL LKP_COL_VAL DATE_VAL
--  ---------- -------- -------- -------- -------- -------- ----------- ----------- ----------- ----------- --------
--           1 A_TBL_1  COL_1_T1 A_TBL_2  COL_1_T2 DATE_T1  a           X           b           X           20221231 
--           2 A_TBL_1  COL_1_T1 A_TBL_2  COL_1_T2 DATE_T1  a           Y           b           Y           20221231

生成的数据集包含构造不同SQL命令所需的一切。这里，对于RULE_ID = 1，将有SQL用于左连接表和选择不匹配的行。对于RULE_ID = 2，将有匹配的行。

SELECT 
    'Select ' || SRC_TBL_VAL || '.' || SRC_COL || ', Count(*) "CNT" ' || Chr(10) ||
    'From ' || SRC_TBL || ' ' || SRC_TBL_VAL || ' ' ||  Chr(10) ||
    'Left Join ' || LKP_TBL || ' ' || LKP_TBL_VAL || ' ON(' || LKP_TBL_VAL || '.' || LKP_COL || ' = ' || SRC_TBL_VAL || '.' || SRC_COL || ')' ||  Chr(10) ||
    'Where ' || LKP_TBL_VAL || '.' || LKP_COL || ' Is ' || CASE RULE_ID WHEN 2 THEN 'Not' ELSE '' END   || ' Null ' ||  Chr(10) ||
    'Group By ' || SRC_TBL_VAL  || '.' || SRC_COL || ' ' ||  Chr(10) ||
    'Order By Count(*) DESC' "SQL_COMMANDS"
FROM      params
ORDER BY  RULE_ID
/*  R e s u l t :
SQL_COMMANDS                                       
--------------------------------------------------
Select a.COL_1_T1, Count(*) "CNT"                 
From A_TBL_1 a                                    
Left Join A_TBL_2 b ON(b.COL_1_T2 = a.COL_1_T1)   
Where b.COL_1_T2 Is  Null                         
Group By a.COL_1_T1                               
Order By Count(*) DESC                            

Select a.COL_1_T1, Count(*) "CNT"                    
From A_TBL_1 a                                       
Left Join A_TBL_2 b ON(b.COL_1_T2 = a.COL_1_T1)      
Where b.COL_1_T2 Is Not Null                         
Group By a.COL_1_T1                                  
Order By Count(*) DESC                         
*/

如果对上述示例数据运行第一个查询，则结果为：

--  COL_1_T1        CNT
--  -------- ----------
--  Z                 1

...而第二个结果为：

--  COL_1_T1        CNT
--  -------- ----------
--  X                 2 
--  Y                 1

您可以选择一些或所有其他列，并且可以使用不同的连接和where条件、分组、排序等来构造sql命令。

赞(0）回复(0）举报 2023-01-04

我来回答

oracle PL/SQL多行动态查询

2条答案

相关问题

热门标签

最新问答