Oracle SQL


mini GItHub на ORACLE

Название интересное, но это не важно...
на самом это деле небольшая система аудита для заданной схемы на БД.
для начала создадим таблицу для аудита

create table a_code(
  username varchar2(50)
, os_user  varchar2(50)
, date_changed date
, object_type varchar2(50)
, object_name varchar2(50)
, user_source clob
); 


добавим функционал для поддержки версионности

create or replace trigger A_CODE_trigger
  before create on schema
declare
  l_date  date := sysdate;
  l_ver   number;
  l_user  varchar2(20);
  l_cl    clob;
begin
  if (ora_dict_obj_type in
            ( 'PACKAGE'
             ,'PACKAGE BODY'
             ,'PROCEDURE'
             ,'FUNCTION' ) )
  then
    select sys_context( 'userenv', 'os_user' ) into l_user from dual;
    l_cl := '';
    for i in (
    SELECT * FROM user_source
     where name = ora_dict_obj_name
       and type = ora_dict_obj_type order by user_source.LINE
       )  loop   
        l_cl := l_cl||i.text;
       end loop;
    insert into a_code(username , os_user  , date_changed, object_type, object_name, user_source) 
    values (user, l_user , sysdate, ora_dict_obj_name, ora_dict_obj_type, l_cl);
  end if;
end;
/

мы знаем когда и кто менял код, а так же сохраняем разные версии данного кода
добавлено: 21 дек 17 просмотры: 583, комментарии: 2



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



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

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

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

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

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



500 000 !!! ура !

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

итак, свершилось, на моем блоге 500 000 посещений
Просмотров
Сегодня 503
В этом месяце 14803
Всего 500111
добавлено: 28 мар 17 просмотры: 3788, комментарии: 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 просмотры: 3363, комментарии: 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 просмотры: 3524, комментарии: 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 просмотры: 2787, комментарии: 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 просмотры: 3126, комментарии: 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 просмотры: 2385, комментарии: 0