Oracle SQL


WITH в ORACLESQL

Оператор WITH позволяет заранее формировать внутренний подзапрос , позволяет обращаться к данному подзапросу по синониму в основном запросе.
Синтаксис
WITH 
T1 as (SELECT field_list FROM T list join WHERE cond group by ...) , 
T2 as (SELECT field_list FROM T list join WHERE cond2 group by ..) , tn as ....
SELECT * FROM T1, T2 where t1.cond= t2.cond

и для того чтобы было совсем понятно - пара примеров :)

WITH T AS (SELECT * FROM ALL_OBJECTS) , T2 AS 
(SELECT * FROM ALL_tables A , T WHERE T.OBJECT_NAME = A.TABLE_NAME ) 
SELECT TABLE_NAME FROM T2 
 
-- сложное обращение 

WITH T AS (SELECT * FROM ALL_OBJECTS) , 
T2 AS (SELECT * FROM ALL_tables) 
SELECT * FROM T, T2 WHERE T.OBJECT_NAME = T2.TABLE_NAME;
 
-- последовательно используем t1,t2

>Splash
>А если перед основным запросом два из WITH, притом что у второго используется первый, а потом они все вместе идут в основной?
with 
t1 as (select object_type, created lstcrdt  , object_name from all_objects),
t2 as (select object_type, count(object_name) cnttype from t1 group by object_type)
select * from t1 inner join t2 on t1.object_type = t2.object_type
добавлено: 21 ноя 17 просмотры: 520, комментарии: 1



Oracle 12c и JSON немножечко...

немного про JSON и ORACLE 12
create table info_user_v (
  id number primary key -- иднтификатор уникальный
, name varchar2(50) -- наименование 
, json_data varchar2(4000) -- данные JSON 
);


в oracle 12 есть специальное ограничение, чтобы добавлять в поле только данные json формата, и чтобы исключить ошибки со структурой JSON
добавим его к нашей таблице
alter table info_user_v add constraint c_1_json_data check(json_data is json);


читать дальше...
добавлено: 05 окт 17 просмотры: 1151, комментарии: 0



Конструкция KEEP FIRST/LAST в SQL за 5 минут, ясно и понятно

Конструкция KEEP FIRST/LAST используется в SQL Oracle для вычисления значения, первой или последней записи в заданной подгруппе, отcортированной по некоторому признаку
она так же позволяет найти результат агрегатной функции по сгруппированным данным, если таких значений несколько

Проще понять это на примере
создадим таблицу курсов валют

CREATE TABLE prices
( 
 ticker VARCHAR2(3), -- валюта сокращ
 pdate DATE, -- дата 
 price FLOAT -- курс валюты
);

заполним таблицу тестовыми данными
за каждый день в таблице может быть несколько курсов заданной валюты
читать дальше...
добавлено: 02 окт 17 просмотры: 1338, комментарии: 0



500 000 !!! ура !

Авторский курс. SQL от новичка до профессионала. Бесплатное вводное занятие. Сертификат. Записывайтся!
Прокачаю до уровня БОГ!

итак, свершилось, на моем блоге 500 000 посещений
Просмотров
Сегодня 503
В этом месяце 14803
Всего 500111
добавлено: 28 мар 17 просмотры: 3662, комментарии: 5



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

Авторский курс. SQL от новичка до профессионала. Бесплатное вводное занятие. Сертификат. Записывайся!
Прокачаю до уровня БОГ!


Подсчитать количество дней недели в заданном году
то есть сколько было в указанном году понедельников вторников воскресений ...
можно следующим образом :
   select count(1) dwcount,
          to_char(to_date('01/01/1940', 'DD/MM/YYYY') + level - 1, 'DAY') daywe -- 1940 это год , который нам нужен
     from dual
   connect by level <
              to_number(to_char(Trunc(To_Date(1940 + 1, 'YYYY'), 'YYYY') - 1, -- 1940 это год , который нам нужен
                                'ddd')) -- считаем колч дней 
    group by to_char(to_date('01/01/1940', 'DD/MM/YYYY') + level - 1, 'DAY'); -- группируем запрос по дням

один читатель, предложил свое решение, более рациональное и эффективное, подвергнув мое решение справедливой критике, публикую
  with t as (select to_date('31/12/'||'1940','dd/mm/yyyy') fd_year from dual)
    select to_char(t.fd_year-level+1,'DAY') fv_day,count(1) fn_count from t
    connect by level <= to_number(to_char(t.fd_year,'ddd'))
  group by to_char(t.fd_year-level+1,'DAY')
добавлено: 20 мар 17 просмотры: 3213, комментарии: 1



индекс на вторичном ключе, зачем он нужен? секретные материалы архитектора СУБД

Авторский курс. SQL от новичка до профессионала. Бесплатное вводное занятие. Сертификат. Записывайся!
Прокачаю до уровня БОГ!


create table tsp(n number primary key); 
-- заполним таблицу данными
insert into tsp
select level from dual connect by level < 100001;

-- создадим вторую таблицу

create table ts(n number primary key, m number, CONSTRAINT fk_ts
    FOREIGN KEY (m)
    REFERENCES tsp(n));

для этой таблицы существует вторичный ключ от m на поле n таблицы tsp
заполним таблицу ts данными
insert into ts(n,m)
select level,mod(level,90000)+1 from dual connect by level < 1000000;

-- попытаемся удалить строки из этой таблицы
delete tsp where n > 99000

-- операция выполнялась 112 секунд
читать дальше...
добавлено: 13 мар 17 просмотры: 3369, комментарии: 5



Обновление данных WHERE CURRENT OF

Авторский курс. SQL от новичка до профессионала. Бесплатное вводное занятие. Сертификат. Записывайся!
Прокачаю до уровня БОГ!


Если вы используете для обновления или удаления данных курсор select for update, то использование WHERE CURRENT OF
в этом случае наиболее удобно.
Выражение CURRENT как показано используется после конструкции WHERE и имеет следующий синтаксис
UPDATE имя таблицы
  SET обновляемые поля и значения
  WHERE CURRENT OF имя курсора;

для обновления данных

Рассмотрим несколько примеров:
подготовим тестовые данные
create table tab11 as select * from all_objects where rownum < 25;


обновление данных с использованием WHERE CURRNT OF
declare 
    cursor C is
    select *
      from tab11 t
     FOR UPDATE OF object_name, owner; 
begin
  FOR i in C loop
   update tab11 set object_name = 'update1' , owner = 'OW'  
   where current of C;
  end loop; 
  commit;  
end;


таким образом, мы, обновляем каждую запись нашего курсора заданным значением.
добавлено: 13 мар 17 просмотры: 2680, комментарии: 0



простой и элегантный способ вставки записей в таблицу без перечисления полей

Авторский курс. SQL от новичка до профессионала. Бесплатное вводное занятие. Сертификат. Записывайся!
Прокачаю до уровня БОГ!


для демонстрации создадим некоторую таблицу на основе all_objects
 create table s_obj as select * from all_objects where rownum < 5

в данном случае таблица содержит 4 записи
с помощью инструкции PL SQL заполним данную таблицу следующим образом
begin
  for i in (select * from all_objects where rownum < 21) loop 
    i.object_name := 'prefix'||i.owner; --меняем данные перед вставкой
    insert into s_obj values i;
  end loop; 
end;

commit;
проверим
select * from s_obj
SYS	ICOL$ ...
SYS	I_USER1 ...
...
SYS	prefixSYS ...
SYS	prefixSYS ...
...


подобную методику вы сможете применять в своих функциях и процедурах pl sql
добавлено: 13 мар 17 просмотры: 2966, комментарии: 0



как размножить строки... cross join и connect by

Авторский курс. SQL от новичка до профессионала. Бесплатное вводное занятие. Сертификат. Записывайся!
Прокачаю до уровня БОГ!


итак , нам необходимо размножить строки в некоторой таблице, на основе заданного запроса
пусть это будет следующий запрос
select object_name from all_objects where object_type = 'CLUSTER'

этот запрос вернет нам 10 строк , по условиям задачи необходимо повторить эти 10 строк по 10 раз

создадим таблицу с размноженными строками , здесь на поможет конструкция cross join и оператор иерархии connect by
вот скрипт создания такой таблицы:
 create table copy1 as select * from 
 (select level lnl from dual connect by level < 11) 
cross join (select object_name from all_objects where object_type = 'CLUSTER') 

где level < 11 количество вставляемых строк

подсчитаем количество строк в данной таблице
 select count(*) from copy1

-- 100 строк, все верно

продемонстрируем работу оператора insert применительно к данной задаче
очистим таблицу
delete from copy1;

произведем вставку
insert into copy1(lnl, object_name) 
select * from (select level lnl from dual connect by level < 11) cross join 
(select object_name from all_objects where object_type = 'CLUSTER')


посмотрим, что получилось
select object_name,count(1) from copy1 group by object_name

успех...
добавлено: 13 мар 17 просмотры: 2262, комментарии: 0



Использование типа Timestamp в oracle

Авторский курс. SQL от новичка до профессионала. Бесплатное вводное занятие. Сертификат. Записывайся!
Прокачаю до уровня БОГ!


TIMESTAMP – специальный тип данных в oracle расширяющий возможности типа данных date

Представлен в трех вариантах:
timestamp
Год, месяц, часы , минуты , секунды

timestamp with local time zone
то же что и предыдущий тип данных, но дополнительно включает еще и следующие значения временных зон
time_zone_hour и time_zone_minute или же time_zone_region

timestamp with local time zone
тип данных со значением часового пояса

поля данных типа timestamp

Создадим таблицу c полями указанных типов
create table ttmstamp(t1 timestamp, t2 timestamp with time zone, t3 timestamp with local time zone);


Для работы с типом Timestamp существует специальная функция
CURRENT_TIMESTAMP, которая возвращает текущее время дата со значениями часового пояса заданного в параметрах сессии .
У данной функции нет параметров.
Пример использования
ALTER SESSION SET TIME_ZONE = '-3:0';

select CURRENT_TIMESTAMP from dual;


ALTER SESSION SET TIME_ZONE = '-4:0';
select CURRENT_TIMESTAMP from dual;

читать дальше...
добавлено: 13 мар 17 просмотры: 3661, комментарии: 0