Oracle SQL


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 просмотры: 465, комментарии: 0



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

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

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

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

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



500 000 !!! ура !

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

итак, свершилось, на моем блоге 500 000 посещений
Просмотров
Сегодня 503
В этом месяце 14803
Всего 500111
добавлено: 28 мар 17 просмотры: 3517, комментарии: 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 просмотры: 2991, комментарии: 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 просмотры: 3187, комментарии: 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 просмотры: 2503, комментарии: 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 просмотры: 2762, комментарии: 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 просмотры: 2095, комментарии: 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 просмотры: 2902, комментарии: 0



Использование GUID в ORACLE

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


GUID некоторая уникальная последовательность символов, в некоторых случаях, может использоваться в качестве первичного ключа.
Рассмотрим основные работы с GUID в ORACLE.

Получить GUID в ORACLE можно, воспользовавшись функцией
sys_guid()
запрос в этом случае будет выглядеть следующим образом
select sys_guid() from dual

результат
4A9B3CF364FB92CAE050A8C0670A0D3A
Для получения GUID в PL SQL используются несколько аналогичная команда
Следует так же отметить, что для ранения GUID в ORACLE используются следующие типы данных
raw(16) и varchar2(32);

следующие примеры демонстрируют работу c GUID в PL/SQL ORACLE
declare
  p_raw raw(16); 
begin
  p_raw := sys_guid;
  dbms_output.put_line(p_raw);
end;

результат 4A9B3CF3650092CAE050A8C0670A0D3A

declare
  p_vc2 varchar2(32);
begin
  p_vc2 := sys_guid;
  dbms_output.put_line(p_vc2);
end;

результат 4A9B3CF3652292CAE050A8C0670A0D3A
читать дальше...
добавлено: 13 мар 17 просмотры: 2334, комментарии: 1