#oracle #plsql
#Oracle #plsql
Вопрос:
На работе они разработали код, в котором они объявляют одну таблицу с %ROWTYPE (в этой таблице 243 столбца), и для каждого столбца они вставили значение.
MYTABLE
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;
Комментарии:
1. «эта таблица была создана, потому что данные считываются из файла csv, а затем вставляются в эту таблицу». Другими словами, эта таблица была создана, потому что никто не хотел использовать встроенные возможности Oracle, такие как SQL * Loader или внешние таблицы.
Ответ №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>
Ответ №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|
---- ---------- ------------------
Ответ №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 — Сформулируйте запрос для вставки для столбцов из шага 1 со значениями, извлеченными из шага 2.
lv_query := 'insert into TESTTAB ('||lv_columns||') values ('||lv_values||')';
Шаг 4 — Используйте EXECUTE IMMEDIATE для выполнения запроса insert.
execute immediate lv_query;
Для полного решения обратитесь к ссылке DBFIDDLE — https://dbfiddle.uk/?rdbms=oracle_18amp;fiddle=edcc497332e87c50273369440646e402
Ответ №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;
/