Уважаемые посетители моего блога,
обращаю ваше внимание на ежегодную конференцию для разработчиков Riga DevDay 2016, которая пройдёт со 2 по 4 марта в Риге.
Стоимость, расписание сессий, другие подробности по ссылке
www.rigadevday.lv и www.facebook.com/rigadevday
LISTAGG упорядочивает данные, объединенные в группы конструкцией ORDER BY, затем соединяет указаный столбец measure_expr.
1) Как одиночная агрегатная функция, LISTAGG обрабатывает все строки и возвращает одно значение.
2) Как групповая агрегатная функция, LISTAGG обрабатывает и возвращает данные для каждой группы определенной в GROUP BY.
3) Как аналитическая функция, LISTAGG обрабатывает данные разбитые на блоки, задаваемые одним или несколькими выражениями query_partition_clause.
Пример агрегатной функции
SQL> select deptno, LISTAGG(t.ename, ', ') WITHIN GROUP (order by t.empno) as ename_list
2 from scott.emp t
3 group by deptno;
DEPTNO ENAME_LIST
------ --------------------------------------------------------------------------------
10 CLARK, KING, MILLER
20 SMITH, JONES, SCOTT, ADAMS, FORD, SMIT2
30 ALLEN, WARD, MARTIN, BLAKE, TURNER, JAMES
Пример аналитической функции
SQL> select deptno,
2 ename,
3 job,
4 hiredate, listagg(t.ename, ', ')
6 WITHIN GROUP(order by t.empno) over(partition by deptno) as ename_list
7 from scott.emp t
8 where hiredate > to_date('01-01-1982','dd-mm-yyyy');
DEPTNO ENAME JOB HIREDATE ENAME_LIST
------ ---------- --------- ----------- -----------------------
10 MILLER CLERK 23.01.1982 MILLER
20 SCOTT ANALYST 09.12.1982 SCOTT, ADAMS, SMIT2
20 ADAMS CLERK 12.01.1983 SCOTT, ADAMS, SMIT2
20 SMIT2 CLERK 25.07.2011 SCOTT, ADAMS, SMIT2
Oracle Database SQL Language Reference 11g Release 2 : LISTAGG
Поиск значения по всем таблицам
Вариант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 поддерживает три вида коллекций
Плюсы использования коллекций
Процедура SLEEP останавливает процесс на указанное время.
DBMS_LOCK.SLEEP(seconds IN NUMBER);
где seconds — время в секундах (и долей секунд) на которое надо остановить процесс.
Последние комментарии