表的循环列的Oracle结构

Question

在工作中，他们开发了一个代码，其中他们用%ROWTYPE声明一个表(该表有243列(，并为每一列插入一个值。

神话

COL_001 VARCHAR2(4000)
COL_002 VARCHAR2(4000)
..
COL_243 VARCHAR2(4000)

正如你所看到的，它们都是VARCHAR2。创建此表是因为数据是从csv文件中读取的，然后插入到该表中。

代码：

CREATE OR REPLACE PROCEUDRE AS TEST
v_rec MYTABLE%ROW_TYPE;
BEGIN
v_rec.COL001 := value;
v_rec.COL002 := value;
...
v_rec.COL243 := value;
END TEST;

其他数据结构是否可以创建循环？

CREATE OR REPLACE PROCEUDRE AS TEST
v_rec MYTABLE%ROW_TYPE;
BEGIN
for i in 1..243 loop
v_rec.i:= value;
end loop;
END TEST;

Answer 1

这就是我理解这个问题的方式-您可以使用集合。

样品表；我不想创建一个有200列的表，所以-3就可以了

SQL> select * from test;
COL_001                                  COL_002        COL_003
---------------------------------------- -------------- -------------
10                                       ACCOUNTING     NEW YORK
20                                       RESEARCH       DALLAS
30                                       SALES          CHICAGO
40                                       OPERATIONS     BOSTON

PL/SQL代码：

SQL> set serveroutput on
SQL> declare
2    -- this is were you'll store contents of the table
3    type testtype is table of test%rowtype index by pls_integer;
4    test_tab testtype;
5  begin
6    -- fetch table contents into TEST_TAB
7    select *
8      bulk collect into test_tab
9      from test;
10
11    -- you'd do something with it; I'm just displaying some values
12    for i in test_tab.first .. test_tab.last loop
13      dbms_output.put_line(test_tab(i).col_001 ||' '|| test_tab(i).col_002);
14    end loop;
15  end;
16  /
10 ACCOUNTING
20 RESEARCH
30 SALES
40 OPERATIONS
PL/SQL procedure successfully completed.
SQL>

Answer 2

要循环遍历列，可以使用dbms_sql实用程序以下是示例

DROP TABLE ABC;
CREATE TABLE ABC (COL1, COL2, COL243) AS
SELECT '1001', 'LO SABIA', 'I KNEW IT' FROM DUAL UNION
SELECT '1002', 'NO IMPORTA','IT DOES NOT MATTER' FROM DUAL;
/
DECLARE
--
l_query            VARCHAR2(4000) := 'Select * from abc';-------------replace table name to test
l_thecursor        INTEGER DEFAULT dbms_sql.open_cursor;
l_columnvalue      VARCHAR2(4000);
l_status           INTEGER;
l_colcnt           NUMBER := 0;
l_separator        VARCHAR2(1) := '|';
l_desctbl          dbms_sql.desc_tab;
l_insert_col_list  VARCHAR2(3000);
l_insert_row_line  VARCHAR2(3000);
l_insert_data_line VARCHAR2(3000);
TYPE numberstab IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
l_col_pad numberstab; --column size
--
BEGIN
dbms_sql.parse(l_thecursor, l_query, dbms_sql.native);
l_status := dbms_sql.execute(l_thecursor);
--
--describe columns
dbms_sql.describe_columns(l_thecursor, l_colcnt, l_desctbl);
FOR i IN 1 .. l_colcnt
LOOP
l_col_pad(i) := greatest(l_desctbl(i).col_name_len,
CASE l_desctbl(i).col_type
WHEN 12 --date
THEN
10
WHEN 2 THEN --number
6
ELSE
greatest(l_desctbl(i).col_max_len, 4)
END);
l_insert_col_list := l_insert_col_list || l_separator ||
lpad(l_desctbl(i).col_name, l_col_pad(i), ' ');
l_insert_row_line := l_insert_row_line || '+' || lpad('-', l_col_pad(i), '-');
dbms_sql.define_column(l_thecursor, i, l_columnvalue, 4000);
END LOOP;
--
l_insert_row_line := l_insert_row_line || '+';
dbms_output.put_line(l_insert_row_line);
dbms_output.put_line(l_insert_col_list || '|');
dbms_output.put_line(l_insert_row_line);
--
--ROWS
WHILE (dbms_sql.fetch_rows(l_thecursor)) > 0
LOOP
--COLUMNS
FOR i IN 1 .. l_colcnt
LOOP            
dbms_sql.column_value(l_thecursor, i, l_columnvalue);--retrieve column value
--replace this section with the code you need 
IF l_columnvalue IS NULL
THEN
l_insert_data_line := l_insert_data_line || l_separator ||
lpad('Null', l_col_pad(i), ' ');
ELSIF l_desctbl(i).col_type IN (12)
THEN
l_insert_data_line := l_insert_data_line || l_separator ||
lpad(to_char(to_date(l_columnvalue, 'dd-mon-yy'), 'yyyy-mm-dd'),
l_col_pad(i), ' ');
ELSE
l_insert_data_line := l_insert_data_line || l_separator ||
lpad(l_columnvalue, l_col_pad(i), ' ');
END IF;
END LOOP;
dbms_output.put_line(l_insert_data_line || '|');
l_insert_data_line := '';
--
END LOOP;
dbms_output.put_line(l_insert_row_line);
dbms_sql.close_cursor(l_thecursor);
END;

这将产生以下输出

+----+----------+------------------+
|COL1|      COL2|            COL243|
+----+----------+------------------+
|1001|  LO SABIA|         I KNEW IT|
|1002|NO IMPORTA|IT DOES NOT MATTER|
+----+----------+------------------+

Answer 3

方法：步骤1-从元数据中将列名提取到字符串中。使用XMLAGG将行转换为列。

select
rtrim (xmlagg (xmlelement (e, column_name || ',') order by column_name).extract ('//text()'),',') 
into lv_columns from
(select table_name,column_name from  user_Tab_columns where table_name ='TESTTAB'
order by column_name asc) tab
group by
table_name;

步骤2-使用CONNECT BY LEVEL根据列数生成序列号。再次使用XMLAGG将结果行转换为列，并将其提取为字符串。根据需要将4的数字更改为243。

select
rtrim (xmlagg (xmlelement (e, level || ',')).extract ('//text()'),',') 
into lv_values FROM   dual
CONNECT BY level < 4
;

步骤3-用从步骤2中提取的值为步骤1中的列制定插入查询。

lv_query := 'insert into TESTTAB ('||lv_columns||') values ('||lv_values||')';

步骤4-使用EXECUTE IMMEDIATE执行插入查询。

execute immediate lv_query;

有关完整的解决方案，请参阅DBFIDDLE链接-https://dbfiddle.uk/?rdbms=oracle_18&fiddle=edcc497332e87c50273369440646e402

Answer 4

也许这就是你所问的？

create or replace package test_pkg as
v_rec mytable%rowtype;
procedure proc;
end test_pkg;
/
create or replace package body test_pkg as
procedure proc is
begin
for i in 1 .. 243 loop
execute immediate 'begin
test_pkg.v_rec.col_' || lpad(i, 3, '0') || ' := :x;
end;'
using value;
end loop;
end proc;
end test_pkg;
/