在Oracle选择查询(而不是脚本或过程)中筛选行

7vhp5slm  于 2023-01-25  发布在  Oracle
关注(0)|答案(3)|浏览(118)

我的Oracle存储过程中有如下代码：

SELECT COUNT(EMP_ID) INTO A_VARIABLE 
FROM STUDENT;

IF (A_VARIABLE > 0) THEN
   SELECT *  FROM DEPARTMENT1;
ELSE
   SELECT * FROM DEPARTMENT2;
END IF;

基本上，我必须在SQL查询中（而不是在过程或脚本中）对行进行相同的过滤。
有人能帮助解决这个Oracle查询吗？
我尝试了多种解决方案，但没有得到所需的输出。

oracle

3条答案

按热度按时间
azpvetkf

azpvetkf1#

对于19c（19.7和更高版本），您可以使用SQL_MACRO(table)特性和内联函数声明。
但是同一个查询的不同输出结构有些误导，所以我建议使用union all方法并对齐两个表的列。
下面是SQL宏的示例。
设置：

create table table1
as
select
  level as id,
  level as val
from dual
connect by level < 5
create table table2
as
select
  level as id,
  lpad(level, 4, '0') as val,
  dbms_random.string('x', 5) as str2
from dual
connect by level < 4
create table decision
as
select 'Y' as res
from dual

运行1：

with function f_decide_table
return varchar2
sql_macro(table)
as
  l_res varchar2(100);
begin
  select
    coalesce (
      max(q'[select * from table1]'),
      q'[select * from table2]'
    )

    into l_res
  from decision;

  return l_res;
end;

select *
from f_decide_table()
order by 1

| 识别号|确认|
| - ------|- ------|
| 1个|1个|
| 第二章|第二章|
| 三个|三个|
| 四个|四个|
运行2：

truncate table decision
with function f_decide_table
return varchar2
sql_macro(table)
as
  l_res varchar2(100);
begin
  select
    coalesce (
      max(q'[select * from table1]'),
      q'[select * from table2]'
    )

    into l_res
  from decision;

  return l_res;
end;

select *
from f_decide_table()
order by 1

| 识别号|确认|STR2|
| - ------|- ------|- ------|
| 1个|零零零一|东方九号|
| 第二章|小零零零二|超高频高频|
| 三个|小零零零三|欧盟12B|
fiddle

赞(0）分享  回复(0）举报  2023-01-25
vsnjm48y

vsnjm48y2#

示例student表;根据学生所选的课程，main query 将从一个表（或另一个表）返回数据。
SQL〉选择 * 学生;

ID NAME   COURSE

查询使用CTE对student表中的行进行计数;然后在exists子查询中用于两个 * 相似 * 的select语句，这两个语句从departments表之一获取行。
目前，IT部门没有人，因此departments 2表为：

SQL> with temp as
  2    (select count(*) cnt
  3     from student
  4     where course = 'IT')
  5  --
  6  select * from department1
  7  where exists (select null from temp
  8                where cnt > 0
  9               )
 10  union all
 11  select * from department2
 12  where exists (select null from temp
 13                where cnt = 0
 14               );

    DEPTNO DNAME          LOC
---------- -------------- -------------
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

SQL>

但是，如果有人研究IT，department1将提供数据：

SQL> update student set course = 'IT' where id = 1;

1 row updated.

SQL> select * from student;

        ID NAME   COURSE
---------- ------ ----------
         1 Little IT
         2 Foot   Web

SQL> with temp as
  2    (select count(*) cnt
  3     from student
  4     where course = 'IT')
  5  --
  6  select * from department1
  7  where exists (select null from temp
  8                where cnt > 0
  9               )
 10  union all
 11  select * from department2
 12  where exists (select null from temp
 13                where cnt = 0
 14               );

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS

SQL>
赞(0）分享  回复(0）举报  2023-01-25
7kqas0il

7kqas0il3#

最接近你想做的事（只使用SQL）将意味着您需要知道两个表中的所有列名和数据类型。你必须为一个联合查询做数据类型转换，因为你必须选择相同数量的具有相同数据类型的列。2结果会有你所需要的一切，除了一些空列（那些确实存在于其他表中，但不存在于您正在从中选择的表中）。根据列的总数以及名称和类型的差异，这可能会变得过于复杂，您真的应该考虑一些函数和引用查询。
无论如何，让我们创建一些示例数据来使用：

WITH        -- sample data
    students (ID, A_NAME) AS
        (
            Select 1, 'Mark' From Dual
        ), 
    dept_1 (ID, DEPT_NAME, DEPT_CITY) AS
        (
            Select '10', 'HR', 'NEW_YORK' From Dual Union All
            Select '20', 'IT', 'BOSTON'   From Dual 
        ),
    dept_2 (ID, DEPARTMENT) AS
        (
            Select 30, 'SALES'      From Dual Union All
            Select 40, 'PRODUCTION' From Dual 
        ),
--  dept_1 has 3 columns (ID VARCHAR)  -  same column name different datatype
--  dept_2 has 2 columns (ID NUMBER)
--  The rest of the columns have different names and same datatype
  1. create cte（namings）-强制两个表中所有列的行联合-哑元填充不存在项并转换To_Char（ID）以获得相同的数据类型（联合工作需要此操作）
--  here are total  of 5 columns coded - it can be widen more if needed, this is just a sample to show handling of extra columns
    namings AS
        (
                SELECT  ROWNUM "RN", 'dept_1' "TABLE_NAME", Count(*) "CNT", To_Char(ID) "V1", DEPT_NAME "V2", DEPT_CITY "V3", 'X' "V4", 'X' "V5", 'ID' "C1", 'DEPT_NAME' "C2", 'DEPT_CITY' "C3", 'COL_X4' "C4", 'COL_X5' "C5"
                From    dept_1 
                Where   CASE WHEN (Select Count(*) "CNT" From students Where ID = 999) > 0 THEN 1 ELSE 0 END = 1
                Group By ROWNUM, ID, DEPT_NAME, DEPT_CITY
            Union All
                SELECT  ROWNUM "RN", 'dept_2' "TABLE_NAME", Count(*) "CNT", To_Char(ID) "V1", DEPARTMENT "V2", 'X' "V3", 'X' "V4", 'X' "V5", 'ID' "C1", 'DEPARTMENT' "C2", 'COL_X3' "C3", 'COL_X4' "C4", 'COL_X5' "C5"
                From    dept_2 
                Where   CASE WHEN (Select Count(*) "CNT" From students Where ID = 999) > 0 THEN 1 ELSE 0 END = 0
                Group By ROWNUM,ID, DEPARTMENT
        ),

当来自学生的计数= 0时返回（使用where子句过滤掉）

RN TABLE_NAME        CNT V1                                       V2         V3       V4 V5 C1 C2         C3        C4     C5   
---------- ---------- ---------- ---------------------------------------- ---------- -------- -- -- -- ---------- --------- ------ ------
         2 dept_2              1 40                                       PRODUCTION X        X  X  ID DEPARTMENT COL_X3    COL_X4 COL_X5 
         1 dept_2              1 30                                       SALES      X        X  X  ID DEPARTMENT COL_X3    COL_X4 COL_X5

学生计数〉0时重新生成

RN TABLE_NAME        CNT V1                                       V2         V3       V4 V5 C1 C2         C3        C4     C5   
---------- ---------- ---------- ---------------------------------------- ---------- -------- -- -- -- ---------- --------- ------ ------
         1 dept_1              1 10                                       HR         NEW_YORK X  X  ID DEPT_NAME  DEPT_CITY COL_X4 COL_X5 
         2 dept_1              1 20                                       IT         BOSTON   X  X  ID DEPT_NAME  DEPT_CITY COL_X4 COL_X5

1.创建另一个cte（转置）来取消透视上述数据

transposed AS
        (   SELECT * FROM namings
                UNPIVOT ( (NAMES, VALS) For COL_NAME IN( (C1, V1), (C2, V2), (C3, V3), (C4, V4), (C5, V5) )   )
            WHERE VALS <>  'X'
        )

当学生计数= 0时重新生成

RN TABLE_NAME        CNT COL_NAME NAMES      VALS                                   
---------- ---------- ---------- -------- ---------- ----------------------------------------
         2 dept_2              1 C1_V1    ID         40                                       
         2 dept_2              1 C2_V2    DEPARTMENT PRODUCTION                               
         1 dept_2              1 C1_V1    ID         30                                       
         1 dept_2              1 C2_V2    DEPARTMENT SALES

学生计数〉0时重新生成

RN TABLE_NAME        CNT COL_NAME NAMES      VALS                                   
---------- ---------- ---------- -------- ---------- ----------------------------------------
         1 dept_1              1 C1_V1    ID         10                                       
         1 dept_1              1 C2_V2    DEPT_NAME  HR                                       
         1 dept_1              1 C3_V3    DEPT_CITY  NEW_YORK                                 
         2 dept_1              1 C1_V1    ID         20                                       
         2 dept_1              1 C2_V2    DEPT_NAME  IT                                       
         2 dept_1              1 C3_V3    DEPT_CITY  BOSTON

1.主sql -将数据透视到两个表中的列

SELECT DISTINCT 
        RN, 
        TABLE_NAME,
        MAX(ID_V) OVER(Partition By RN) "ID",  
        MAX(DEPT_NAME_V) OVER(Partition By RN) "DEPT_NAME",
        MAX(DEPT_CITY_V) OVER(Partition By RN) "DEPT_CITY",
        MAX(DEPARTMENT_V) OVER(Partition By RN) "DEPARTMENT" 
    FROM (
            SELECT * FROM transposed
            PIVOT  (
                        COUNT(NAMES), MAX(VALS) "V" FOR NAMES IN('ID' "ID", 'DEPT_NAME' "DEPT_NAME", 'DEPT_CITY' "DEPT_CITY", 'DEPARTMENT' "DEPARTMENT")
                    )
        )
    ORDER BY RN

当学生计数= 0时重新生成

RN TABLE_NAME ID                                       DEPT_NAME                                DEPT_CITY                                DEPARTMENT                             
---------- ---------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ----------------------------------------
         1 dept_2     30                                                                                                                         SALES                                    
         2 dept_2     40                                                                                                                         PRODUCTION

学生计数〉0时重新生成

RN TABLE_NAME ID                                       DEPT_NAME                                DEPT_CITY                                DEPARTMENT                             
---------- ---------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ----------------------------------------
         1 dept_1     10                                       HR                                       NEW_YORK                                                                          
         2 dept_1     20                                       IT                                       BOSTON
赞(0）分享  回复(0）举报  2023-01-25
我来回答

相关问题

    查看更多

    热门标签

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

    最新问答

    更多
    • 蜀ICP备2022004421号-2 NULL123 https://www.null123.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com