Превращаем столбцы в строчки LISTAGG

-- Превращаем столбцы в строчки
-- функция LISTAGG
-
Описание функции listagg
согласно переводу Oracle Reference Database listagg может использоваться
1) Как одиночная агрегатная функция, LISTAGG обрабатывает все строки и возвращает одно значение.
2) Как групповая агрегатная функция, LISTAGG обрабатывает и возвращает данные для каждой группы определенной в GROUP BY.
3) Как аналитическая функция, LISTAGG обрабатывает данные разбитые на блоки, задаваемые одним или несколькими выражениями query_partition_clause.
давайте рассмотрим самый интересный пример:

Подробнее...

Небольшой пример работы с вложенными таблицами nested table

DECLARE
 TYPE TYP_REC IS RECORD
   (
     num   number
     ,vname varchar2(30)
    );
     TYPE rec_table_type IS TABLE OF TYP_REC ;     
    my_tab rec_table_type;    
 BEGIN
   my_tab := rec_table_type();
   my_tab.EXTEND;
   my_tab(1).num := 21;
   my_tab(1).vname := 'норма';
   my_tab.EXTEND;
   my_tab(2).num := 21;
   my_tab(2).vname := 'норма1';
   my_tab.EXTEND;
   my_tab(3).num := 21;
   my_tab(3).vname := 'норма1';

   DBMS_OUTPUT.PUT_LINE('my_tab(1) is '||my_tab(1).num||' - '||my_tab(1).vname);
   For i IN my_tab.FIRST .. my_tab.LAST Loop
     dbms_output.put_line( 'Line  = ' || my_tab(i).num ) ;
     dbms_output.put_line( 'Code  = ' || my_tab(i).vname ) ;
    End loop;
 
    
 END;

Предикаты any , all в SQL запросах

-- Использование any , all в запросах , очень похоже на использования предиката exists , но есть некоторые отличия
-- рассмотрим работу данных предикатов на примерах
-- для примера создадим таблицу со следующими полями 
-- 1) Название
-- 2) Цвет
-- 3) вес

drop table t1;
create table t1(tid number,tname varchar2(20),tprop varchar2(20), tvalue number);
-- заполним таблицу данными
insert into t1 values(1,'круг','красный', 10);
insert into t1 values(2,'круг','зеленый', 30);
insert into t1 values(3,'круг','зеленый', 30);
insert into t1 values(4,'круг','синий', 20);
insert into t1 values(5,'квадрат','красный', 10);
insert into t1 values(6,'квадрат','синий', 20);
insert into t1 values(7,'квадрат','красный', 20);
insert into t1 values(8,'треугольник','красный', 30);
insert into t1 values(9,'треугольник','синий', 10);

Подробнее...

Hесложный способ перевернуть запрос с группировкой

Hесложный способ перевернуть запрос с группировкой
-- условимся, что перечень значений свойств был ограничен
-- для примера создадим таблицу со следующими полями 
-- 1) Название
-- 2) Цвет
-- по условиям задачи у нас сеть конечное количество цветов красный , зеленый , синий

create table t1(tid number,tname varchar2(20),tprop varchar2(20));

Подробнее...

Как работают RollUp и Cube?

Подари,продай, обменяй - множество недорогих и отличных вещей объявления вместо AVITO - ВКонтакте

-- rollup и cube специальные групповые опepации oracle 
-- применяются начиная с версии 8i 
-- очень удобно использовать rollup cube при подведении итогов
-- некоторые из операций могут быть заменены га аналитический sql

-- давайте разберем на примерах

Подробнее...

Как убить сессию запросом Oracle 11g Release 11.2

следующий запрос убивает и вешает сессию наглухо

select x.id,
       rtrim(xmlagg(xmlelement("a",x.recno||';')).extract('//a/text()').getStringVal(),';') as recno,
       min(recno)
  from
  (select 1 as id, 101 as recno from dual
  union all
  select 2 as id, 102 as recno from dual
  union all
  select 3 as id, 103 as recno from dual
  union all
  select 3 as id, 123 as recno from dual
  union all
  select 4 as id, 104 as recno from dual
  union all
  select 5 as id, 105 as recno from dual) x
 group by x.id
 order by min(recno);

Случайность в PL SQL - DBMS_RANDOM

Для работы со случайными значениями в PL SQL используется пакет DBMS_RANDOM
DBMS_RANDOM пакет содержит встроенный генератор случайных чисел

Модель безопасности 
Этот пакет должен быть установлен как SYS . По умолчанию в пакете инициализируется с текущим именем пользователя, текущее время с точностью до секунды, и на нынешней сессии. Oracle рекомендует пользователям, которым необходимо выполнять этот пакет должны быть EXECUTE привилегии , и не должны полагаться на PUBLIC EXECUTE привилегии. 
• DBMS_RANDOM.RANDOM возвращает целые [-2 ^ 31, 2 ^ 31). 
• DBMS_RANDOM.VALUE возвращает чисела из [0,1) с 38 цифр точности. 
DBMS_RANDOM может быть явно инициализирован, но не нужно специально инициализировать каждый раз перед вызовом генератора случайных чисел. Будет автоматически инициализировать с даты, идентификатор пользователя и идентификатор процесса, если явной инициализации не выполняется.

Подробнее...

Задача с валютами

Исходные данные
-- есть таблица в с номером валюты, датой заполнения курса, курсом валюты
-- оператор вводит курс валюты, когда он поменялся , то есть если несколько дней подряд одинаковый курс - то, в таблице будет единственное значение курса на дату изменения
-- необходимо вывести курс валюты на заданную дату , по каждой валюте
-- если на данную дату нет курса тогда выводится курс на последнюю дату, если в заданную дату курс менялся несколько раз , то надо вывести все изменения
-- Подготовим данные для примера
-- создадим таблицу с валютами\

Подробнее...

Передача диапазонов значений в процедуру , в качестве входного параметра