oracle - 从 oracle 数据库中检索和显示数据
问题描述
首先，我要感谢您光临并花费您宝贵的时间来查看我的问题。
我在 Oracle 数据库中有 2 个不同的表。
第一个表包含有关另一个表中存在的列的元数据。将第一个 (COL_TAB) 表视为 Oracle 默认提供的 ALL_TAB_COLS 的自定义版本。
COL_TAB
----------------------------------------------
| TABLE_NAME | COL_NAME | COL_DESC |
----------------------------------------------
| TABLE1 | TAB1_COL_2 | TABLE 1 COLUMN 2 |
| TABLE1 | TAB1_COL_4 | TABLE 1 COLUMN 4 |
| TABLE1 | TAB1_COL_3 | TABLE 1 COLUMN 3 |
| TABLE1 | TAB1_COL_5 | |
| TABLE1 | TAB1_COL_1 | TABLE 1 COLUMN 1 |
----------------------------------------------
TABLE1
--------------------------------------------------------------------
| TAB1_COL_3 | TAB1_COL_1 | TAB1_COL_5 | TAB1_COL_2 |
--------------------------------------------------------------------
| TAB1_COL3_DATA1 | TAB1_COL1_DAT | TAB1_COL5_DAT2 | TAB1_COL2_DAT |
| TAB1_COL3_DATA2 | TAB1_COL1_DAT | TAB1_COL5_DAT1 | TAB1_COL2_DAT |
| TAB1_COL3_DATA3 | TAB1_COL1_DAT | TAB1_COL5_DAT3 | TAB1_COL2_DAT |
--------------------------------------------------------------------
我想将数据显示为 2 个不同的输出：
FIRST OUTPUT:
------------------------------------------------------------------------------------------------
| TABLE 1 COLUMN 3 | TABLE 1 COLUMN 1 | TAB1_COL_5 | TABLE 1 COLUMN 2 | TABLE 1 COLUMN 4 |
------------------------------------------------------------------------------------------------
-> 如果 COL_DESC 为空白或 null，则需要在输出中显示 COL_NAME。-> "TABLE 1 COLUMN 3" 和 "TABLE 1 COLUMN 1" 总是需要显示为第 1 列和第 2 列，然后是其余列。-> 如果 COL_TAB 表中定义的任何列未在 TABLE1 中使用，则需要在输出的最后一列显示该列，例如 TABLE1 中未使用 TAB1_COL_4，所以它被显示在最后。
SECOND OUTPUT:
------------------------------------------------------------------------------------------------
| TAB1_COL3_DATA1 | TAB1_COL1_DAT | TAB1_COL5_DAT2 | TAB1_COL2_DAT | |
| TAB1_COL3_DATA2 | TAB1_COL1_DAT | TAB1_COL5_DAT1 | TAB1_COL2_DAT | |
| TAB1_COL3_DATA3 | TAB1_COL1_DAT | TAB1_COL5_DAT3 | TAB1_COL2_DAT | |
------------------------------------------------------------------------------------------------
-> 第二个输出中的列的顺序需要与第一个输出中显示的列的顺序同步。
我确实尝试了以下查询来显示第一个输出，但它不起作用（我确定它不正确）：
SELECT NVL(COL_DESC, COL_NAME) AS COL_TEXT
FROM COL_TAB
WHERE TABLE_NAME = 'TABLE1'
PIVOT(MIN(COL_TEXT)
FOR COL_TEXT IN (SELECT COL_NAME FROM COL_TAB WHERE TABLE_NAME = 'TABLE1'));
以防万一，如果有任何不清楚的地方，请告诉我。我会尽力再解释一遍。再次感谢您提前提供的帮助。
解决方案
您可以按确定的顺序获取列描述/名称，例如：
select coalesce(ct.col_desc, ct.col_name)
from col_tab ct
left join user_tab_columns utc
on utc.table_name = ct.table_name and utc.column_name = ct.col_name
where ct.table_name = 'TABLE1'
order by utc.column_id, ct.col_name;
COALESCE(CT.COL_
----------------
TABLE 1 COLUMN 3
TABLE 1 COLUMN 1
TAB1_COL_5
TABLE 1 COLUMN 2
TABLE 1 COLUMN 4
将这些行旋转到列需要动态完成。
您还可以生成动态查询，以类似的方式以相同的顺序获取数据。
这使用 SQL*Plus（或 SQLcl，或 SQL Developer）绑定变量引用游标来获取两个输出，并使用块中定义的表名；但可以很容易地适应成为传递表名并为引用游标提供参数的过程：
var rc1 refcursor;
var rc2 refcursor;
declare
l_table_name varchar2(30) := 'TABLE1';
l_stmt varchar2(4000);
begin
select 'select '
|| listagg('''' || coalesce(ct.col_desc, ct.col_name) || '''', ',')
within group (order by utc.column_id, ct.col_name)
|| ' from dual'
into l_stmt
from col_tab ct
left join user_tab_columns utc
on utc.table_name = ct.table_name and utc.column_name = ct.col_name
where ct.table_name = l_table_name;
dbms_output.put_line(l_stmt);
open :rc1 for l_stmt;
select 'select '
|| listagg(coalesce(utc.column_name, 'null') || ' as ' || ct.col_name, ',')
within group (order by utc.column_id, ct.col_name)
|| ' from ' || l_table_name
into l_stmt
from col_tab ct
left join user_tab_columns utc
on utc.table_name = ct.table_name and utc.column_name = ct.col_name
where ct.table_name = l_table_name;
dbms_output.put_line(l_stmt);
open :rc2 for l_stmt;
end;
/
运行语句的块获取dbms_output只是为了调试，但可能会感兴趣：
select 'TABLE 1 COLUMN 3','TABLE 1 COLUMN 1','TAB1_COL_5','TABLE 1 COLUMN 2','TABLE 1 COLUMN 4' from dual
select TAB1_COL_3 as TAB1_COL_3,TAB1_COL_1 as TAB1_COL_1,TAB1_COL_5 as TAB1_COL_5,TAB1_COL_2 as TAB1_COL_2,null as TAB1_COL_4 from TABLE1
然后您可以打印参考游标（同样，特定于客户端的行为）：
print rc1
'TABLE1COLUMN3' 'TABLE1COLUMN1' 'TAB1_COL_ 'TABLE1COLUMN2' 'TABLE1COLUMN4'
---------------- ---------------- ---------- ---------------- ----------------
TABLE 1 COLUMN 3 TABLE 1 COLUMN 1 TAB1_COL_5 TABLE 1 COLUMN 2 TABLE 1 COLUMN 4
print rc2
TAB1_COL_3 TAB1_COL_1 TAB1_COL_5 TAB1_COL_2 TAB1_COL_4
--------------- ------------- -------------- ------------- ----------
TAB1_COL3_DATA1 TAB1_COL1_DAT TAB1_COL5_DAT2 TAB1_COL2_DAT
TAB1_COL3_DATA2 TAB1_COL1_DAT TAB1_COL5_DAT1 TAB1_COL2_DAT
TAB1_COL3_DATA3 TAB1_COL1_DAT TAB1_COL5_DAT3 TAB1_COL2_DAT
这 2 列在所有表中都很常见。
在这种情况下，您可以使用 case 表达式来扩展排序逻辑：
within group (order by case ct.col_name
when 'TAB1_COL_3' then 1
when 'TAB1_COL_1' then 2
else 3 end,
utc.column_id, ct.col_name)
然后得到：
'TABLE1COLUMN3' 'TABLE1COLUMN1' 'TAB1_COL_ 'TABLE1COLUMN2' 'TABLE1COLUMN4'
---------------- ---------------- ---------- ---------------- ----------------
TABLE 1 COLUMN 3 TABLE 1 COLUMN 1 TAB1_COL_5 TABLE 1 COLUMN 2 TABLE 1 COLUMN 4
TAB1_COL_3 TAB1_COL_1 TAB1_COL_5 TAB1_COL_2 TAB1_COL_4
--------------- ------------- -------------- ------------- ----------
TAB1_COL3_DATA1 TAB1_COL1_DAT TAB1_COL5_DAT2 TAB1_COL2_DAT
TAB1_COL3_DATA2 TAB1_COL1_DAT TAB1_COL5_DAT1 TAB1_COL2_DAT
TAB1_COL3_DATA3 TAB1_COL1_DAT TAB1_COL5_DAT3 TAB1_COL2_DAT
或者可能使用描述而不是名称，这取决于它是保持相同的名称还是描述（从示例中很难猜到）。
如果您能展示如何动态完成旋转，那就太好了。
它最终在这里并不真正需要，并且比listagg我上面使用的更复杂；但你可以做类似的事情；
select '
select * from (
select row_number()
over (order by case ct.col_name
when ''TAB1_COL_3'' then 1
when ''TAB1_COL_1'' then 2
else 3
end,
utc.column_id, ct.col_name) as pos,
coalesce(ct.col_desc, ct.col_name) as name
from col_tab ct
left join user_tab_columns utc
on utc.table_name = ct.table_name and utc.column_name = ct.col_name
where ct.table_name = :tab
)
pivot (max(name) as col for (pos) in ('
|| listagg(level, ',') within group (order by level)
|| '))'
into l_stmt
from dual
connect by level <= (select count(*) from col_tab where table_name = l_table_name);
dbms_output.put_line(l_stmt);
open :rc1 for l_stmt using l_table_name;
它得到的输出显示生成的动态查询为：
select * from (
select row_number()
over (order by case ct.col_name
when 'TAB1_COL_3' then 1
when 'TAB1_COL_1' then 2
else 3
end,
utc.column_id, ct.col_name) as pos,
coalesce(ct.col_desc, ct.col_name) as name
from col_tab ct
left join user_tab_columns utc
on utc.table_name = ct.table_name and utc.column_name = ct.col_name
where ct.table_name = :tab
)
pivot (max(name) as col for (pos) in (1,2,3,4,5))
结果集为：
1_COL 2_COL 3_COL 4_COL 5_COL
---------------- ---------------- ---------------- ---------------- ----------------
TABLE 1 COLUMN 3 TABLE 1 COLUMN 1 TAB1_COL_5 TABLE 1 COLUMN 2 TABLE 1 COLUMN 4
您可以将列名用于枢轴而不是 ，pos我认为这只会使阅读变得更加困难，因为您需要在它们周围加上引号。
推荐阅读
- sql - 删除级联创建表时外键错误
- chef-infra - 如何运行第一个批处理文件并转到第二个批处理文件而不等待完成第一个批处理文件
- sql-server - ssis - 将具有不同结构的多个csv加载到sql server
- c# - 如何使用 iText 验证自定义格式的数字签名？
- google-bigquery - BigQuery 加载镶木地板错误 - Parquet 中的字段 INT32 与架构中的 double 类型不兼容
- mongodb - Mongo Db 查询以匹配并在文档中添加字段
- python - 在 Python 3 中读取不断更新（每秒 20 次）文件的适当方法？
- c# - 关于日期格式的问题 - 16:15 到 16:00
- corda - 在同一个 Corda 流中创建多个事务有什么风险？
- java - 如何在java中连接两行？