从 oracle 数据库中检索和显示数据 [英] Retrieving and displaying data from oracle database
问题描述
首先，我要感谢您光临并花费您宝贵的时间来解决我的问题.
我在一个 Oracle 数据库中有 2 个不同的表.
第一个表保存有关另一个表中存在的列的元数据.将第一个 (COL_TAB) 表视为 Oracle 默认附带的 ALL_TAB_COLS 的自定义版本.
COL_TAB----------------------------------------------|TABLE_NAME |COL_NAME |COL_DESC |----------------------------------------------|表1 |TAB1_COL_2 |表 1 第 2 栏 ||表1 |TAB1_COL_4 |表 1 第 4 栏 ||表1 |TAB1_COL_3 |表 1 第 3 栏 ||表1 |TAB1_COL_5 |||表1 |TAB1_COL_1 |表 1 第 1 栏 |----------------------------------------------表格1--------------------------------------------------------------------|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 个不同的输出:
第一个输出:------------------------------------------------------------------------------------------------|表 1 第 3 栏 |表 1 第 1 栏 |TAB1_COL_5 |表 1 第 2 栏 |表 1 第 4 栏 |------------------------------------------------------------------------------------------------->如果 COL_DESC 为空或空，则需要在输出中显示 COL_NAME.->表1第3栏"和表 1 第 1 栏"总是需要显示为第一和第二列，然后是其余的列.->如果 COL_TAB 表中定义的任何列未在 TABLE1 中使用，则需要在输出的最后一列中显示此类列，例如，TAB1_COL_4 没有在 TABLE1 中使用，所以它显示在最后.
第二个输出:------------------------------------------------------------------------------------------------|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 ||------------------------------------------------------------------------------------------------->SECOND OUTPUT 中 COLUMNS 的顺序需要与 FIRST OUTPUT 中显示的列顺序同步.
我确实尝试了以下查询来显示 FIRST OUTPUT，但它不起作用(我确定它不正确):
SELECT NVL(COL_DESC, COL_NAME) AS COL_TEXTFROM COL_TABWHERE TABLE_NAME = 'TABLE1'枢轴(最小(COL_TEXT)FOR COL_TEXT IN (SELECT COL_NAME FROM COL_TAB WHERE TABLE_NAME = 'TABLE1'));如果有任何不清楚的地方，请告诉我.我会尽力再解释一遍.再次感谢您提前提供帮助.
您可以获得列描述/名称 - 以确定的顺序 - 使用以下内容:
选择合并(ct.col_desc, ct.col_name)来自 col_tab ct左连接 user_tab_columns UTC在 utc.table_name = ct.table_name 和 utc.column_name = ct.col_name其中 ct.table_name = 'TABLE1'按 utc.column_id、ct.col_name 排序；COALESCE(CT.COL_----------------表 1 第 3 栏表 1 第 1 栏TAB1_COL_5表 1 第 2 栏表 1 第 4 栏将这些行转为列需要动态完成.
您也可以生成动态查询，以类似的方式以相同的顺序获取数据.
这使用 SQL*Plus(或 SQLcl，或 SQL Developer)绑定变量引用游标来获取两个输出，并使用在块内定义的表名；但可以很容易地改编成一个过程，它传递了表名并为引用游标提供了参数:
var rc1 refcursor;var rc2 refcursor;宣布l_table_name varchar2(30) := 'TABLE1';l_stmt varchar2(4000);开始选择选择"||listagg('''' || 合并(ct.col_desc, ct.col_name) || '''', ',')组内(按 utc.column_id、ct.col_name 排序)||'来自双重'进入l_stmt来自 col_tab ct左连接 user_tab_columns UTC在 utc.table_name = ct.table_name 和 utc.column_name = ct.col_name其中 ct.table_name = l_table_name;dbms_output.put_line(l_stmt);打开 :rc1 为 l_stmt;选择选择"||listagg(coalesce(utc.column_name, 'null') || ' as ' || ct.col_name, ',')组内(按 utc.column_id、ct.col_name 排序)||' 来自 ' ||l_table_name进入l_stmt来自 col_tab ct左连接 user_tab_columns UTC在 utc.table_name = ct.table_name 和 utc.column_name = ct.col_name其中 ct.table_name = l_table_name;dbms_output.put_line(l_stmt);打开 :rc2 for l_stmt;结尾;/运行块获取语句的 dbms_output 仅用于调试，但可能会引起兴趣:
select 'TABLE 1 COLUMN 3','TABLE 1 COLUMN 1','TAB1_COL_5','TABLE 1 COLUMN 2','TABLE 1 COLUMN 4' from dual选择TAB1_COL_3作为TAB1_COL_3，TAB1_COL_1作为TAB1_COL_1，TAB1_COL_5作为TAB1_COL_5，TAB1_COL_2作为TAB1_COL_2，空作为表1中的TAB1_COL_4然后你可以打印引用游标(同样，客户端特定的行为):
打印rc1'TABLE1COLUMN3''TABLE1COLUMN1''TAB1_COL_'TABLE1COLUMN2''TABLE1COLUMN4'——————————————————————————————-------- ----------------表 1 第 3 列 表 1 第 1 列 TAB1_COL_5 表 1 第 2 列 表 1 第 4 列打印 rc2TAB1_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_DATTAB1_COL3_DATA2 TAB1_COL1_DAT TAB1_COL5_DAT1 TAB1_COL2_DATTAB1_COL3_DATA3 TAB1_COL1_DAT TAB1_COL5_DAT3 TAB1_COL2_DAT<块引用>
这 2 列在所有表中都是通用的.
在这种情况下，您可以使用 case 表达式来扩展排序逻辑:
组内(按大小写排序 ct.col_name当 'TAB1_COL_3' 然后 1当 'TAB1_COL_1' 然后 2否则 3 结束，utc.column_id, ct.col_name)然后得到:
'TABLE1COLUMN3' 'TABLE1COLUMN1' 'TAB1_COL_'TABLE1COLUMN2' 'TABLE1COLUMN4'——————————————————————————————-------- ----------------表 1 第 3 列 表 1 第 1 列 TAB1_COL_5 表 1 第 2 列 表 1 第 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_DATTAB1_COL3_DATA2 TAB1_COL1_DAT TAB1_COL5_DAT1 TAB1_COL2_DATTAB1_COL3_DATA3 TAB1_COL1_DAT TAB1_COL5_DAT3 TAB1_COL2_DAT或者可能使用描述而不是名称，这取决于是名称还是描述保持不变(从示例中很难猜到).
<块引用>
如果您能展示如何动态完成旋转，那就太好了.
这里最终真的不需要它，而且比我上面使用的listagg要复杂；但你可以做类似的事情;
选择 '选择 * 从 (选择 row_number()over(按大小写排序 ct.col_name当 ''TAB1_COL_3'' 然后 1当 ''TAB1_COL_1'' 然后 2其他 3结尾，utc.column_id, ct.col_name) 作为 pos,合并(ct.col_desc，ct.col_name)作为名称来自 col_tab ct左连接 user_tab_columns UTC在 utc.table_name = ct.table_name 和 utc.column_name = ct.col_name其中 ct.table_name = :tab)枢轴 (max(name) as col for (pos) in ('||listagg(level, ',') 组内(按级别排序)||'))'进入l_stmt从双重按级别连接 <= (select count(*) from col_tab where table_name = l_table_name);dbms_output.put_line(l_stmt);使用 l_table_name 为 l_stmt 打开 :rc1；获取显示生成的动态查询的输出:
select * from (选择 row_number()over(按大小写排序 ct.col_name当 'TAB1_COL_3' 然后 1当 'TAB1_COL_1' 然后 2其他 3结尾，utc.column_id, ct.col_name) 作为 pos,合并(ct.col_desc，ct.col_name)作为名称来自 col_tab ct左连接 user_tab_columns UTC在 utc.table_name = ct.table_name 和 utc.column_name = ct.col_name其中 ct.table_name = :tab)枢轴(max(name)作为(pos)在(1,2,3,4,5)中的col)结果集为:
1_COL 2_COL 3_COL 4_COL 5_COL---------------- ---------------- ---------------- --------------------------------表 1 第 3 列 表 1 第 1 列 TAB1_COL_5 表 1 第 2 列 表 1 第 4 列您可以使用列名作为数据透视表而不是 pos，我认为这只会让阅读变得更加困难，因为您需要在它们周围加上引号.
First of all, I would like to THANK YOU for stopping by and spending your precious time for looking at my problem.
I have 2 different tables within an Oracle Database.
The first table holds metadata about the columns present in the other table. Think of the first (COL_TAB) table as a custom version of the ALL_TAB_COLS which comes by default with Oracle.
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 |
--------------------------------------------------------------------
I want to display the data as 2 different outputs:
FIRST OUTPUT:
------------------------------------------------------------------------------------------------
| TABLE 1 COLUMN 3 | TABLE 1 COLUMN 1 | TAB1_COL_5 | TABLE 1 COLUMN 2 | TABLE 1 COLUMN 4 |
------------------------------------------------------------------------------------------------
-> In case, if the COL_DESC is blank or null, then the COL_NAME needs to be displayed in the output. -> "TABLE 1 COLUMN 3" AND "TABLE 1 COLUMN 1" always need to be displayed as 1st and 2nd column followed by the rest of the columns. -> In case, if any column defined within the COL_TAB table isn't being used in TABLE1, then such a column needs to be displayed at the last column in the output, for example, the TAB1_COL_4 isn't being used in TABLE1, so it is being displayed in the last.
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 | |
------------------------------------------------------------------------------------------------
-> The order of the COLUMNS in the SECOND OUTPUT needs to be in sync with the order of columns as displayed within in the FIRST OUTPUT.
I did try the below query for displaying the FIRST OUTPUT, but it isn't working (I'm sure it's not correct):
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'));
In case, if anything isn't clear please do let me know. I would try my best to explain it again. Thanks again for your help in advance.
You can get the column description/names - in a deterministic order - with something like:
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
Pivoting those rows to columns would need to be done dynamically.
You can also generate a dynamic query to get the data in the same order in a similar way.
This uses SQL*Plus (or SQLcl, or SQL Developer) bind variable ref cursors to get the two outputs, and uses a table name defined within the block; but could easily be adapted to be a procedure that is passed the table name and have out parameters for the ref cursors:
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;
/
Running the block gets dbms_output of the statements just for debugging, but might be of interest:
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
and then you can print the ref cursors (again, client-specific behaviour):
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
Those 2 columns are common across all the tables.
In that case you can use a case expression to extend the ordering logic:
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)
which then gets:
'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
or possibly using the description instead of the name, depending on whether it's the name or description that stays the same (hard to guess from the example).
It would be really great if you could show up how pivoting can be done dynamically.
it isn't really needed here in the end, and is more complicated than the listagg I used above; but you could do something like;
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;
which gets output showing the generated dynamic query as:
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))
and result set as:
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
You could use the column names for the pivot instead of the pos, it would just make it even harder to read I think, as you'd need to include quotes around them.
这篇关于从 oracle 数据库中检索和显示数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
