Пример заполнения ассоциативного массива и цикл для вывода данных.
Ключом является строка varchar2(1):
declare
-- объявление типа
TYPE resultRec is record
(
ready varchar2(1),
cnt number
);
type resultTab is table of resultRec INDEX BY VARCHAR2(1);
--
p_iter varchar2(1); -- итератор
p_validation_res_tbl resultTab; -- экземпляр коллекции
--
-- запись данных в коллекцию
procedure put_result(p_ready varchar2)
is
begin
if (p_validation_res_tbl.exists(p_ready)) then
p_validation_res_tbl(p_ready).cnt := p_validation_res_tbl(p_ready).cnt + 1;
else
p_validation_res_tbl(p_ready).ready := p_ready;
p_validation_res_tbl(p_ready).cnt := 1;
end if;
end;
begin
-- запись данных в коллекцию через вспомогательную процедуру
put_result('E');
put_result('E');
put_result('S');
put_result('W');
-- первичное выставление итератора коллекции
p_iter := p_validation_res_tbl.FIRST;
-- проверка, на то, что коллекция не пустая
if (p_iter is null) then
dbms_output.put_line('p_validation_res_tbl is null');
else
-- цикл по коллекции
WHILE p_iter IS NOT NULL
LOOP
-- Пример оращения к данным p_validation_res_tbl(p_iter).cnt, где
-- p_validation_res_tbl: экземпляр коллекции
-- p_iter : текущий итератор
-- cnt : поле из record resultRec
dbms_output.put_line(p_validation_res_tbl(p_iter).ready
||':'
||p_validation_res_tbl(p_iter).cnt
||' row(s)');
-- получение следующей записи
p_iter := p_validation_res_tbl.NEXT(p_iter);
END LOOP;
end if;
end;
>
E:2 row(s)
S:1 row(s)
W:1 row(s)
Скрипт помогающий создать PARTITION BY RANGE по полю DATE
SET LINESIZE 150
SET SERVEROUTPUT ON SIZE 1000000
WHENEVER SQLERROR CONTINUE
SPOOL C:\create_partition_date.log
declare
iter number;
l_date_start date := to_date('01-11-2011','dd-mm-yyyy');
l_date_end date := to_date('01-01-2023','dd-mm-yyyy');
l_name_part varchar2(20) := 'TEST';
l_part_field varchar2(20) := 'MY_DATE';
begin
dbms_output.put_line('PARTITION BY RANGE('||l_part_field||')');
dbms_output.put_line('(');
dbms_output.put_line(' PARTITION '||l_name_part
||'_MIN VALUES LESS THAN (to_date('''
||to_char(add_months(l_date_start,-1),'dd-mm-yyyy')
||''',''dd-mm-yyyy''))');
for j in (
with t as (
select l_date_start d1,
l_date_end d2
from dual
)
--
select decode(level,1,d1,
trunc(add_months(d1,level-1),'mm')
) as date_from
from t
connect by add_months(trunc(d1,'mm'),level-1) <= d2
)
loop
dbms_output.put_line(',PARTITION '||l_name_part||'_'
||to_char(add_months(j.date_from,-1),'mm_yyyy')
||' VALUES LESS THAN (to_date('''
||to_char(j.date_from,'dd-mm-yyyy')
||''',''dd-mm-yyyy''))');
end loop;
dbms_output.put_line(',PARTITION '||l_name_part
||'_MAX VALUES LESS THAN (MAXVALUE)');
dbms_output.put_line(')');
end;
/
spool off
PARTITION BY RANGE(MY_DATE)
(
PARTITION TEST_MIN VALUES LESS THAN (to_date('01-10-2011','dd-mm-yyyy'))
,PARTITION TEST_10_2011 VALUES LESS THAN (to_date('01-11-2011','dd-mm-yyyy'))
,PARTITION TEST_11_2011 VALUES LESS THAN (to_date('01-12-2011','dd-mm-yyyy'))
...
,PARTITION TEST_11_2022 VALUES LESS THAN (to_date('01-12-2022','dd-mm-yyyy'))
,PARTITION TEST_12_2022 VALUES LESS THAN (to_date('01-01-2023','dd-mm-yyyy'))
,PARTITION TEST_MAX VALUES LESS THAN (MAXVALUE)
)
PL/SQL procedure successfully completed
SQL> spool off
Stopped spooling to C:\create_partition_date.log
Поиск значения по всем таблицам
Вариант1:
DECLARE
match_count INTEGER;
query_str VARCHAR2(400);
l_owner VARCHAR2(10) := 'SCOTT';
l_search_text VARCHAR2(100) := '10';
BEGIN
FOR t IN (select table_name, column_name FROM all_tab_columns a
where owner = l_owner
and a.data_type in ('NUMBER', 'VARCHAR2')
)
LOOP
begin
match_count := 0;
query_str := 'SELECT COUNT(*) FROM ' ||l_owner||'.'|| t.table_name ||
' WHERE to_char(' || t.column_name || ') = :1';
EXECUTE IMMEDIATE query_str
INTO match_count
USING l_search_text;
IF match_count > 0 THEN
dbms_output.put_line(l_owner||'.'||t.table_name || ' ' || t.column_name || ' ' || match_count);
dbms_output.put_line(' SELECT t.'|| t.column_name || ', t.* from '||l_owner||'.'|| t.table_name||' t where to_char(' || t.column_name || ') = '''||l_search_text||'''');
dbms_output.put_line('');
END IF;
exception
when others then
dbms_output.put_line(t.table_name||'.'||t.column_name||' : '||SQLERRM||' : '||SQLCODE);
dbms_output.put_line(query_str);
dbms_output.put_line('');
/*raise*/
end;
END LOOP;
END;
Вариант2:
--SET SERVEROUTPUT ON SIZE 1000000
DECLARE
TYPE tab_columns_rec_type IS RECORD (
table_name all_tab_columns.table_name%type
,column_name all_tab_columns.column_name%type
,data_type all_tab_columns.data_type%type
);
type tab_columns_tab_type is table of tab_columns_rec_type index by binary_integer;
l_tab_columns_rec tab_columns_tab_type;
match_count INTEGER;
query_str VARCHAR2(400);
l_owner VARCHAR2(10) := 'SCOTT';
l_search_text VARCHAR2(100) := '10'; -- % добавляем здесь, если ищем не по точному вхождению
l_number NUMBER;
is_number NUMBER(1);
l_str_length NUMBER;
/*
l_mode:
1 - NUMBER и VARCHAR2
2 - only NUMBER
3 - only VARCHAR2
*/
l_mode NUMBER := 1;
-- При каком количестве найденные совпадений
-- прекращать поиск
l_found_count NUMBER := 1;
l_exists_count NUMBER;
BEGIN
l_str_length := length(l_search_text);
begin
l_number := to_number(l_search_text);
is_number := 1;
exception
when VALUE_ERROR then
is_number := 0;
end;
if (l_mode = 1 and is_number = 1) then
select table_name, column_name, data_type
bulk collect into l_tab_columns_rec
FROM all_tab_columns a
where owner = l_owner
and a.data_length >= l_str_length
and ( a.data_type = 'VARCHAR2' or a.data_type = 'NUMBER' );
elsif (l_mode = 2 and is_number = 1) then
select table_name, column_name, data_type
bulk collect into l_tab_columns_rec
FROM all_tab_columns a
where owner = l_owner
and a.data_length >= l_str_length
and a.data_type = 'NUMBER';
else
select table_name, column_name, data_type
bulk collect into l_tab_columns_rec
FROM all_tab_columns a
where owner = l_owner
and a.data_length >= l_str_length
and a.data_type = 'VARCHAR2';
end if;
l_exists_count := 0;
if NOT (l_tab_columns_rec is null or l_tab_columns_rec.count = 0) then
for j in l_tab_columns_rec.first..l_tab_columns_rec.last
LOOP
begin
match_count := 0;
if (l_tab_columns_rec(j).data_type = 'VARCHAR2') then
query_str := 'SELECT count(*) FROM ' ||l_owner||'.'|| l_tab_columns_rec(j).table_name ||
' WHERE rownum = 1 and to_char(' || l_tab_columns_rec(j).column_name || ') = :1';
EXECUTE IMMEDIATE query_str
INTO match_count
USING l_search_text;
elsif (l_tab_columns_rec(j).data_type = 'NUMBER') then
query_str := 'SELECT count(*) FROM ' ||l_owner||'.'|| l_tab_columns_rec(j).table_name ||
' WHERE rownum = 1 and ' || l_tab_columns_rec(j).column_name || ' = :1';
EXECUTE IMMEDIATE query_str
INTO match_count
USING l_number;
end if;
IF match_count > 0 THEN
l_exists_count := l_exists_count + 1;
dbms_output.put_line(l_tab_columns_rec(j).data_type||' '||l_owner||'.'||l_tab_columns_rec(j).table_name || ' ' || l_tab_columns_rec(j).column_name );
dbms_output.put_line(' SELECT t.'|| l_tab_columns_rec(j).column_name || ', t.* from '||l_owner||'.'|| l_tab_columns_rec(j).table_name||' t where to_char(' || l_tab_columns_rec(j).column_name || ') like '''||l_search_text||'''');
dbms_output.put_line('');
if (l_exists_count = l_found_count) then
exit;
end if;
END IF;
exception
when others then
dbms_output.put_line(l_tab_columns_rec(j).table_name||'.'||l_tab_columns_rec(j).column_name||' : '||SQLERRM||' : '||SQLCODE);
dbms_output.put_line(query_str);
dbms_output.put_line('');
/*raise*/
end;
END LOOP;
end if;
END;
Ассоциативные массивы
Вариант вложенной таблицы, в которой элементы могут иметь произвольные значения индексов, расположенные в определенном порядке.
Позволяет работать со столбцами как с единой переменной — массивом.
Является временной формой коллекции, существует в течении сессии.
Другое название ассоциативных массивов — индексированные таблицы или pl\sql таблицы.
Ассоциативный массив это наиболее часто используемый вид коллекции. Его не надо инициализировать или расширять. В Oracle9i Database Release 2 и более поздних версиях ассоциативные массивы можно индексировать не только по числам, но и по строкам.
Пример 1: INDEX BY PLS_INTEGER
SQL> set serveroutput on size unlimited;
SQL> DECLARE
2 TYPE XX_COST_TYPE IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
3 cost XX_COST_TYPE;
4 l_res NUMBER;
5 BEGIN
6 cost(1) := 5 ;
7 cost(20) := 10 ;
8 cost(12) := 15 ;
9 l_res := cost(1) + cost(20) + cost(12);
10 DBMS_OUTPUT.PUT_LINE(l_res);
11 END;
12 /
30
PL/SQL procedure successfully completed
Пример 2: INDEX BY VARCHAR2
SQL> DECLARE
2 TYPE XX_COST_TYPE IS TABLE OF NUMBER INDEX BY VARCHAR2(100);
3 cost XX_COST_TYPE;
4 L_SELECTED VARCHAR2(100) := 'CHAIR';
5 BEGIN
6 cost('TABLE') := 5000;
7 cost('CHAIR') := 84020 ;
8 cost('LAMP') := 8300;
9 cost('PENCIL'):= 110;
10 DBMS_OUTPUT.PUT_LINE(cost(L_SELECTED));
11 END;
12 /
84020
PL/SQL procedure successfully completed
Ограничения:
Нельзя создать тип ассоциативного массива на уровне схемы:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> CREATE OR REPLACE TYPE xx_test_type IS TABLE OF NUMBER INDEX BY PLS_INTEGER
2 /
Warning: Type created with compilation errors
Предупреждение: Тип создан с ошибками компиляции.
SQL> show errors
Ошибки для TYPE XX_TEST_TYPE:
LINE/COL ERROR
-------- -----------------------------------------------------------------
0/0 PL/SQL: Compilation unit analysis terminated
1/22 PLS-00355: использование таблицы pl/sql не разрешается в этом
контексте
Вложенные таблицы
Вложенную таблицу можно рассматривать как одномерный массив, в котором индексами служат значения целочисленного типа. Вложенная таблица может иметь пустые элементы, которые появляются после их удаления встроенной процедурой DELETE. Количество элементов может динамически увеличиваться.
Максимальное число строк вложенной таблицы составляет 2 гигабайта.
Наиболее важным отличием между вложенными и индексированными таблицами является то, что вложенные таблицы являются постоянной формой коллекции.
Свойства вложенных таблиц
- Могут использоваться как в SQL (тип столбца в таблице), так и в PL\SQL коде
- Содержат однородные данные, т.е. все строки имеют одинаковую структуру данных
- Требуется инициализация, при использовании в PL\SQL
- Порядок элементов не зафиксирован
- Используется память PGA (для всех 3 типов коллекций)
- При попытке чтения элемента с несуществующим индексом -> исключение NO_DATA_FOUND
Читать дальше про “Вложенные таблицы (Nested Tables)” »
Процедура SLEEP останавливает процесс на указанное время.
DBMS_LOCK.SLEEP(seconds IN NUMBER);
где seconds — время в секундах (и долей секунд) на которое надо остановить процесс.
-- Вывод сообщения в лог
procedure output_log_data(p_log_str in varchar2) is
g_output_dbms number := 1; -- Вывод сообщений в dbms_output.put_line
g_output_oebs number := 2; -- Вывод сообщений в fnd_file.put_line
v_output_current number := g_output_oebs;
begin
if fnd_global.conc_request_id = -1 then
v_output_current := g_output_dbms;
end if;
if v_output_current = g_output_dbms then
dbms_output.put_line(p_log_str);
elsif v_output_current = g_output_oebs then
--fnd_file.put_line(fnd_file.output, p_log_str); --Строка выводится в "Просмотр Результатов"
fnd_file.put_line(fnd_file.log, p_log_str); --Строка выводится в "Просмотр Журнала"
end if;
end output_log_data;
Последние комментарии