Oracle. Полезные способы решения некоторых задач.

-- 1) Подготовка таблицы
create table TEST_DOUBLE (value number(9));

-- 2) Заполнение таблицы значениями.
Begin
   insert into TEST_DOUBLE (value) 
   select round(dbms_random.value*100,0) from ALL_OBJECTS where rownum <=100;
   commit;
End;
/
-- 3) Просмотр дубликатов.
select * from (
   select rowid, value
      , row_number() over(partition by value order by value) AS rn 
      from TEST_DOUBLE
) where rn > 1; 

-- 4) Удаление дубликатов.
Begin
   delete from TEST_DOUBLE where rowid in (
      select rowid from (select rowid, value
      , row_number() over(partition by value order by value) AS rn 
      from TEST_DOUBLE) where rn > 1);
   commit;
End;
/

select o.*
from (select rownum rw, o.* from (
select o.* from all_tables o order by table_name) o
where rownum < 20) o
where o.rw >= 10;

select * from (
select o.*, row_number() over (order by o.table_name) rw from all_tables o)
where rw >= 10 and rw < 20;

-- Создать таблицу.
create table test (test_id number(38), code varchar2(16)
  , constraint pk_test primary key (test_id)
);
-- Создать триггер.
create or replace trigger tbiur_test
before insert or update on test
for each row
begin
   if :new.test_id is null then
     select sq_test.nextval into :new.test_id from dual;
   end if;
end;
/
-- Использование последовательности напрямую.
Insert into test (test_id, code) values (sq_test.nextval, 'One');
-- Определение значения первичного ключа в триггере.
Insert into test (code) values ('Two');
Insert into test (test_id, code) values (null, 'Three');

-- В Oracle 12c можно обойтись без триггера:
create table test(test_id number(38)default sq_test.nextval

-- Также в Oracle 12c есть следующая возможность:
create global temporary table my_temp_table (
id_temp number generated always as identity,
text_temp varchar2(4000),
constraint my_temp_table_pk primary key (id_temp)
) on commit delete rows

-- Заполняем таблицу тестовыми данными.
insert into sales (select 'Scanner', round(dbms_random.value*20+1),
   trunc(sysdate, 'yy')+dbms_random.value*350
 from all_objects where rownum < 600
);
insert into sales (select 'Printer', round(dbms_random.value*20+1),
   trunc(sysdate, 'yy')+dbms_random.value*350
 from all_objects where rownum < 300
);
-- Задаём форматирование результатов для sqlplus, 
-- чтобы данные одной записи умещались в одну строку
Column product format a15
Column Jan format 999
Column Feb format 999
…
-- Применяем метод, описанный во второй книге Тома Кайта
Select product,
   min(decode(mn, 1, sm, NULL)) Jan,
   min(decode(mn, 2, sm, NULL)) Feb,
   /* и так далее, удалено */
from
 (select product, to_number(to_char(dt, 'MM')) mn, sum(amount) sm
     from sales
     group by product, to_number(to_char(dt, 'MM'))
 )
group by product;
/*
PRODUCT          JAN  FEB  MAR  APR  MAI  JUN  JUL  AUG  SEP  OCT  NOV  DEC
--------------- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
Printer          213  120  426  255  305  349  267  313  282  304  231  129
Scanner          630  582  595  446  622  651  395  579  728  484  573  381
*/

WITH T AS (
   select product, to_char(trunc(dt, 'MM'),'MM') as mm, sum(amount) AS amount
   from sales group by product, trunc(dt, 'MM')
)
SELECT * FROM T PIVOT (
  SUM(amount) FOR (mm) IN (
'01' AS Jan, '02' AS Feb, '03' AS Mar, '04' AS Apr, '05' AS Mai, '06' AS Jun
, '07' AS Jul, '08' AS Aug, '09' AS Sep, '10' AS Oct, '11' AS Nov, '12' AS Dec)
) ORDER BY product;

SELECT LEVEL AS ID FROM dual CONNECT BY LEVEL <= 10;

SELECT to_date('1-7-2018', 'dd-mm-yyyy')+LEVEL-1 AS dt 
FROM dual 
CONNECT BY LEVEL <= (to_date('1-8-2018', 'dd-mm-yyyy') - to_date('1-7-2018', 'dd-mm-yyyy')+1);
-- Вариант с периодом в диапазоне дат: 
SELECT rownum AS rn
, CASE WHEN :p_date_begin + (rownum - 1) * :p_period >= :p_date_end THEN :p_date_end
     ELSE :p_date_begin + (rownum - 1) * :p_period END AS rn_date
FROM dual
CONNECT BY :p_date_begin + (rownum - 1) * :p_period < :p_date_end + :p_period;

-- Создать объектный тип.
create or replace type t_test AS object(
  a number (9), b number (9)
);
/n
-- Создать таблицу.
create or replace type tbl_test AS table of t_test
/

-- Создать Pipeline-функцию.
create or replace function pipe_test (
    p_from number, p_to number
) 
    return tbl_test pipelined                 -- a) ключевое слово
as
  x_Test t_test := t_test(null, null);        -- b) инициализация переменной
begin
  for i in p_from .. p_to loop
    x_Test.a := i;                            -- c) заполнить строку
    x_Test.b := dbms_random.value*100;
    pipe row (x_Test);                        -- d) вернуть строку
  end loop;
  return;
end;
/
-- Использовать так:
select * from table(pipe_test(2,5));

-- Объявление пакета:
create or replace package pkg_test as
  
type t_emp is record ( empno number(4), … /* и так далее */ );
type tbl_emp is table of t_emp;

function get_emp return tbl_emp parallel_enable pipelined;

end;
/
-- Тело пакета:
create or replace package body pkg_test as

function get_emp return tbl_emp parallel_enable pipelined is
begin    
  for i in (select e.* from scott.emp e) loop  -- без инициализации
    pipe row (i);
  end loop;
  return;
end;    

end;
/
-- Использование:
select * from table (pkg_test.get_emp);

-- Создание таблицы:
create table test_double (a number);
-- Заполнение:
begin
    insert into test_double select dbms_random.value*100 from dual connect by level <= 2;
    commit;
end;
-- Вычисление:
select
  exp(sum( ln(decode(sign(a),  0,1,  -1,-a,  a))))            -- 1) заменяем произведение возведением E в степень по сумме логарифмов.
 *decode(mod(sum(decode(sign(a), -1,1, 0)),2),1,-1,1)         -- 2) определяем знак (* -1, если кол-во отрицательных чисел нечетно)
 *sign(min(abs(a)))                                           -- 3) если в исходной выборке все нули, умножаем на 0.
from test_double
;