有没有一个快速的PLSQL函数可以为给定的schema.table返回逗号分隔的列名列表

Question

我正试图在PL/SQL环境中设置一些简单的实用程序。最终，我希望硬编码的MYTABLE被绑定变量所取代。

我从以下内容开始，它返回一个错误：

DECLARE
TYPE colNames_typ IS TABLE OF all_tab_cols.column_name%type index by PLS_INTEGER;
v_ReturnVal    colNames_typ;
v_sql          VARCHAR2(32000);
BEGIN
v_sql :='SELECT column_name FROM all_tab_cols WHERE table_name = ''MYTABLE'' ' ;
EXECUTE IMMEDIATE (v_sql)
INTO v_returnVal;
-- Convert assoc array to a comma delimited list. 
END;

返回错误：

PLS-00597: expression 'V_RETURNVAL' in the INTO list is of wrong type

我想不出比一个变量类型与源完全相同的条目表更"正确"的类型了。

任何帮助都会很棒！

感谢

Answer 1

是否有一个快速的PLSQL函数用于返回给定schema.table的逗号分隔的列名列表？

使用LISTAGG:

DECLARE
v_owner      ALL_TAB_COLUMNS.OWNER%TYPE := 'SCHEMA_NAME';
v_table_name ALL_TAB_COLUMNS.TABLE_NAME%TYPE := 'TEST_DATA';
v_columns    VARCHAR2(32000);
BEGIN
SELECT LISTAGG( '"' || column_name || '"', ',' )
WITHIN GROUP ( ORDER BY COLUMN_ID )
INTO   v_columns
FROM   all_tab_columns
WHERE  owner = v_owner
AND    table_name = v_table_name;
DBMS_OUTPUT.PUT_LINE( v_columns );
END;
/

(注意：您还需要传递所有者，或者，如果您在不同的模式中有两个名称相同的表，那么您将获得这两个表的列。(

(注意2：我假设您希望将列名列表放入动态查询中；如果是，则您希望用双引号将列标识符括起来。如果不这样做，并且列标识符区分大小写，则您将获得不正确的名称，因为Oracle在查询中解析未加引号的标识符时会隐式将其转换为大写。如果不需要引号，则使用SELECT LISTAGG( column_name, ',' )。(。)

如果你有桌子的话：

CREATE TABLE test_data (
A NUMBER,
B DATE,
C VARCHAR2(20),
E TIMESTAMP,
Z INTERVAL DAY TO SECOND,
Q CHAR(5)
);

输出：

"A","B","C","E","Z","Q"

db<gt；小提琴这里

Answer 2

不确定这是否是所询问的：

create or replace function get_cols_string(target_owner all_tab_cols.owner%type, target_table_name all_tab_cols.table_name%type) return varchar2 is
outputString varchar2(32767);
oneMore      boolean := false;
BEGIN
for current_col in 
(SELECT column_name FROM all_tab_cols 
WHERE owner = target_owner and table_name = target_table_name) loop
if(oneMore) then
outputString := outputString || ', ';
end if;
outputString := outputString || current_col.column_name;
oneMore := TRUE;
end loop;
return outputString;
END;
/

Rem Test the above with simple cases
create table tab1 (c1 number);
create table tab2 (c1 number, c2 number);
set serveroutput on
declare
owner_name varchar2(32767) := 'SYS';
table_name varchar2(32767) := 'TAB1';
begin
dbms_output.put_line('For: ' || owner_name || '.' || table_name);
dbms_output.put_line(get_cols_string(owner_name, table_name));
end;
/
declare
owner_name varchar2(32767) := 'SYS';
table_name varchar2(32767) := 'TAB2';
begin
dbms_output.put_line('For: ' || owner_name || '.' || table_name);
dbms_output.put_line(get_cols_string(owner_name, table_name));
end;
/
declare
owner_name varchar2(32767) := 'SYS';
table_name varchar2(32767) := 'ALL_TAB_COLS';
begin
dbms_output.put_line('For: ' || owner_name || '.' || table_name);
dbms_output.put_line(get_cols_string(owner_name, table_name));
end;
/

Answer 3

你问"之前的方法失败的原因是什么嗯，是的。这个错误源于Oracle是一个非常严格的类型，从而使您假设没有"；一个比具有与源完全相同的变量类型的条目表更"正确"的类型"；false。type_A的集合(表(不是变量type _A。您试图将type_a的变量存储到type_a集合中，从而导致错误的类型异常。

现在，这并不意味着你真的很遥远。你想把select返回的type_a变量的集合存储到type_a的集合中。你可以做到这一点，你只需要让甲骨文知道。你可以通过BULK COLLECT INTO来完成。下面显示了该过程并创建列名的CSV。

注意：@MTO发布了卓越的解决方案，这只是向您展示了您的原始解决方案是如何实现的。尽管如此，这还是一种有用的技巧。

declare  
type colnames_typ is table of all_tab_cols.column_name%type;

k_comma  constant varchar2(1) := ',';

v_returnval colnames_typ;
v_sql       varchar2(32000);
v_sep       varchar2(1) := ' ';
v_csv_names varchar2(512); 
begin
v_sql := 'select column_name from all_tab_cols where table_name = ''MYTABLE'' order by column_id';
execute immediate (v_sql)
bulk collect into v_returnval;

-- Convert assoc array to a comma delimited list. 
for indx in 1 .. v_returnval.count
loop
v_csv_names :=  v_csv_names || v_sep || v_returnval(indx); 
v_sep :=k_comma; 
end loop;   
v_csv_names := trim(v_csv_names);

dbms_output.put_line(v_csv_names); 
end;