PL/SQL 过程/函数动态显示来自不同表的数据以及第一个数据行中的列名 [英] PL/SQL procedure/function to show data from different tables dynamically alongwith the column names in first data row

问题描述

我有如下需求.

有 70 个表，我必须根据某些条件从这 70 个不同的表中构建 70 个查询.

假设表名是TAB_1,TAB_2,TAB_3.....,TAB_70.每个表中的列数和列的数据类型都不同.我将获得用户输入，并且必须将该值传递给 Oracle PL/SQL 函数或过程 GET_RESULT() 并以表格格式获取输出(与运行查询时得到的结果相同).

此外，我必须在第一个数据行中显示列名.

示例:

我要两张桌子，TAB_1 和 TAB_2.

TAB_1

ID 季度风险
00001 Q0 2
00001 Q1 3
00001 Q2 1
00001 Q3 1
00001 Q4 2

TAB_2

ID 状态
00001 ACTIVE
00002 PURGED
00003 ACTIVE
00004 ACTIVE

如果我得到用户输入 1，我会将它传递给过程的参数 GET_RESULTS(1) 并获得如下输出:

Col1 Col2 Col3
ID 季度风险
00001 Q0 2
00001 Q1 3
00001 Q2 1
00001 Q3 1
00001 Q4 2

如果 GET_RESULTS(2) 那么:

Col1 Col2
ID 状态
00001 ACTIVE
00002 PURGED
00003 ACTIVE
00004 ACTIVE

有人可以帮忙吗?

解决方案

使用数据字典构建选择正确列的 SQL 语句.使用动态 SQL 打开该语句的引用，并从函数中返回游标.

示例架构

创建表tab_1为选择 '00001' id, 'Q0' 季度, 2 双重联合的风险全部选择 '00001' id, 'Q1' 季度，来自双重联合的 3 个风险全部选择 '00001' id, 'Q2' 季度, 1 个来自双重联合的风险选择 '00001' id, 'Q3' 季度, 1 个来自双重联合的风险选择 '00001' id, 'Q4' 季度, 2 风险来自双重;创建表 tab_2 作为选择'00001' id, 'ACTIVE' 状态从双重联合所有选择 '00002' id, 'PURGED' status from dual union all选择 '00003' id, 'ACTIVE' 状态从双联合所有选择'00004' id, 'ACTIVE' status from dual;

功能

create or replace function get_results(p_id number) return sys_refcursor isv_sql varchar2(32767);v_refcursor sys_refcursor;开始--获取SQL语句.选择'选择' ||listagg(column_name, ',') 组内(按 column_id 排序) ||' 来自 ' ||表名进入 v_sql来自 user_tab_columns其中 table_name = 'TAB_' ||p_id和 column_id <= 2按表名分组；为 v_sql 打开 v_refcursor；返回 v_refcursor;结尾;/

调用函数

只要应用程序理解引用，该函数就应该可以工作.下面是最近版本的 SQL*Plus 中的示例:

SQL>从双中选择 get_results(1);GET_RESULTS(1)--------------------光标语句:1光标语句:1曲名----- -00001 Q000001 Q100001 Q200001 Q300001 Q4

I have a requirement like below.

There are 70 tables and I have to build 70 queries from those 70 different tables depending on some condition.

Let us say the table names are TAB_1,TAB_2,TAB_3.....,TAB_70.The number of columns and data type of the columns are different in each table. I will get user input and I have to pass that value to an Oracle PL/SQL function or procedure GET_RESULT() and get the output in tabular format(same as we get when we run a query).

Also, I have to show the column names in the 1st data row.

Example:

I am taking two tables, TAB_1 and TAB_2.

TAB_1

ID Quarter Risk
00001 Q0 2
00001 Q1 3
00001 Q2 1
00001 Q3 1
00001 Q4 2

TAB_2

ID Status
00001 ACTIVE
00002 PURGED
00003 ACTIVE
00004 ACTIVE

If I get user input 1, I will pass it to a procedure's parameter, GET_RESULTS(1) and get an output like below:

Col1 Col2 Col3
ID Quarter Risk
00001 Q0 2
00001 Q1 3
00001 Q2 1
00001 Q3 1
00001 Q4 2

If GET_RESULTS(2) then :

Col1 Col2
ID STATUS
00001 ACTIVE
00002 PURGED
00003 ACTIVE
00004 ACTIVE

Can someone help?

解决方案

Use the data dictionary to build a SQL statement that selects the correct columns. Use dynamic SQL to open a refcursor to that statement, and return the cursor from the function.

Sample Schema

create table tab_1 as
select '00001' id, 'Q0' quarter, 2 risk from dual union all
select '00001' id, 'Q1' quarter, 3 risk from dual union all
select '00001' id, 'Q2' quarter, 1 risk from dual union all
select '00001' id, 'Q3' quarter, 1 risk from dual union all
select '00001' id, 'Q4' quarter, 2 risk from dual;

create table tab_2 as
select '00001' id, 'ACTIVE' status from dual union all
select '00002' id, 'PURGED' status from dual union all
select '00003' id, 'ACTIVE' status from dual union all
select '00004' id, 'ACTIVE' status from dual;

Function

create or replace function get_results(p_id number) return sys_refcursor is
    v_sql varchar2(32767);
    v_refcursor sys_refcursor;
begin
    --Get SQL statement.
    select
        'select ' || 
        listagg(column_name, ',') within group (order by column_id) ||
        ' from ' || table_name
    into v_sql
    from user_tab_columns
    where table_name = 'TAB_' || p_id
        and column_id <= 2
    group by table_name;

    open v_refcursor for v_sql;

    return v_refcursor;
end;
/

Calling the Function

The function should work as long as the application understands refcursors. Below is an example in a recent version of SQL*Plus:

SQL> select get_results(1) from dual;

GET_RESULTS(1)
--------------------
CURSOR STATEMENT : 1

CURSOR STATEMENT : 1

ID    QU
----- --
00001 Q0
00001 Q1
00001 Q2
00001 Q3
00001 Q4

这篇关于PL/SQL 过程/函数动态显示来自不同表的数据以及第一个数据行中的列名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！