Oracle SQL


уже МИЛЛИОН!!! 1 000 000!

Просмотров

Сегодня 1718
В этом месяце 10530
Всего 1000003

Сегодня счетчик посещений моего блога превзошел миллион просмотров, что дальше ? Продолжаю вести курсы, публикую книгу , постараюсь и дальше радовать вас новыми интересными заметками, дорогие читатели.
С уважением, Чалышев Максим Михайлович.
добавлено: 14 апр 19 просмотры: 965, комментарии: 1



SERIALIZABLE режим

Чалышев Максим Михайлович

SQL. 5 дней которые изменят вашу жизнь.

В СУБД ORACLE есть возможность, чтобы пользователь всегда видел только те данные в таблицах, которые были доступны с начала его сессии
Такой режим (уровень изоляции) называется SERIALIZABLE. Для того чтобы включить этот режим используется команда

Alter session set isolation_level=serializable;

Следующий пример показывает отличие режима SERIALIZABLE от стандартного режима эксплуатации СУБД. Уровня изоляции READ COMMITTED
Откроем в двух разных окнах программу SQL Developer (или создадим новый Worksheet) подключимся к схеме SYS как администратор.
создадим таблицу man5
Create table Man5(prt number, name varchar2(50));



INSERT INTO man5 VALUES(20, 'Олег');
INSERT INTO man5 VALUES(21, 'Влад');
INSERT INTO man5 VALUES(22, 'Саша');
Commit;


читать дальше...
добавлено: 18 фев 19 просмотры: 1261, комментарии: 0



Контекст сеанса

Чалышев Максим Михайлович
SQL. 5 дней которые изменят твою жизнь.

Введение
В каждой сессии есть возможность использовать специальные структуры данных, именуемые контекст.
Контексты - это набор данных вида - параметр значение. Контекст предоставляет дополнительные возможности для приложений, использующих СУБД Oracle.
Теория и практика
Контекст - это предопределённый набор параметров и значений создаваемый в рамках определенной сессии. Несколько таких наборов параметр значение, объединяется с помощью структуры называемой контекстом.

Задается значение контекста с помощью процедуры DBMS_SESSION.SET_CONTEXT

Функция SYS_CONTEXT позволяет получить значение заданного контекста в раках сессии.
Для создания контекста специальная команда CREATE CONTEXT.
Сначала следует создать специальною процедуру для управления контекстом.

CREATE OR REPLACE PROCEDURE set_mycontext_value ( par IN VARCHAR2, val IN VARCHAR2 ) 
AS BEGIN  DBMS_SESSION.SET_CONTEXT ( 'myctx', par, val ); END;

CREATE OR REPLACE CONTEXT myctx USING set_mycontext_value;

читать дальше...
добавлено: 18 фев 19 просмотры: 1125, комментарии: 0



Планировщик JOB заданий. Управление.

Чалышев Максим Михайлович

SQL. 5 дней которые изменят твою жизнь.

Введение
В Oracle есть специальный механизм запланировать выполнение определённой программы, на заданное время, это может быть оператор SQL, программа на языке PLSQL, либо даже внешняя программа. Этот механизм называется механизмом заданий Job

Данный механизм может применяться, например, для планирования запуска тяжелых запросов в ночное время или на выходные дни, распределения задач построения отчетности.

Теория и практика

Для управления заданиями в Oracle существует специальные пакет DBMS_JOB:
Создание заданий
Можно использовать механизм dbms_job
Или механизм dbms_scheduler – более современный способ
DBMS_JOB.SUBMIT(
 JOB OUT BINARY_INTEGER,
 WHAT IN VARCHAR2,
 NEXT_DATE IN DATE DEFAULT SYSDATE,
 INTERVAL IN VARCHAR2 DEFAULT NULL,
 NO_PARSE IN BOOLEAN DEFAULT FALSE,
 INSTANCE IN BINARY_INTEGER DEFAULT any_instance,
 FORCE IN BOOLEAN DEFAULT FALSE
);


Здесь:
JOB – Входной параметр уникальный идентификатор задания. Идентификатор генерируется специальной системной последовательностью.
WHAT - анонимный PL/SQL блок, в данном блоке указывается последовательность команд которая будет выполнена в процессе работы задания.
В же в этом параметре можно также писать команды вставки удаления редактирования (inser update delete), а так же команды для создания создания индексов таблиц, создания индексов, ограничений.
NEXT_DATE – дата время следующего выполнения задания.
Если будет указана дата меньше чем текущую дата, то выполнение задания будет начато немедленно.
INTERVAL – Вычисляемая дата следующего выполнения задания в столбце NEXT_DATE. Примеры интервала задания:
NULL
Задание выполнится однократно и удалится.
читать дальше...
добавлено: 18 фев 19 просмотры: 1181, комментарии: 0



Быстрая очистка таблиц и EXECUTE IMMEDIATE.

Чалышев Максим Михайлович
SQL. 5 дней, которые изменят твою жизнь.

Введение


Для быстрой очистки таблиц в Oracle применяется специальный оператор TRUNCATE TABLE.
Данный оператор является оператором DDL, оператором мгновенного выполнения и поэтому для его вызова нам потребуется оператор динамического SQL.
Динамический SQL используется в ORACLE с помощью команды EXECUTE IMMEDIATE
Теория и практика
Оператор EXECUTE IMMEDIATE выполняет динамический оператор SQL или анонимный PL / SQL блок. В нашем случае мы будем использовать EXECUTE IMMEDIATE для работы с TRUNCATE TABLE.
Текст динамического SQL заключается в кавычки.
Оператор TRUNCATE TABLE используется для быстрого удаления всех записей из таблицы в Oracle. По результату аналогичен DELETE, без условий WHERE, но выполняется гораздо быстрее.
Синтаксис
TRUNCATE TABLE table_name
  [ PRESERVE MATERIALIZED VIEW LOG | PURGE MATERIALIZED VIEW LOG ]
  [ DROP STORAGE | REUSE STORAGE ] ;

читать дальше...
добавлено: 18 фев 19 просмотры: 1064, комментарии: 0



Внешние таблицы External Table

Чалышев Максим Михайлович
SQL. 5 дней, которые изменят вашу жизнь.
Внешние таблицы – специальный механизм Oracle СУБД с помощью которого можно обращаться в данным , хранящимся в файлах вне базы данных как к обычным таблицам.

Для загрузки данных могут использоваться команды драйвера OracleLoader. В ExternalTable не могут применяться операторы изменения данных (DELETE INSERT UPDATE MERGE).

Но, к таким таблицам вполне могут применяться стандартные запросы SELECT, с использованием групповых операций, агрегатных функций, аналитического SQL.

Все это делает механизм внешних таблиц особенно эффективным для проектов DWH(хранилищ данных) , при формировании ETL(процедур загрузки) для данных.

Теория и практика

Предположим, у нас есть несколько файлов заданного формата, файлы расположены в каталоге temp на диске c:
Необходимо подключить эти файлы как внешние таблицы к экземпляру нашей базы данных.

Название файлов city.csv и man.csv, кодировка UTF8 - ниже представлено содержание этих файлов текстовом виде.

city.csv

1,Москва,10000000
2,Владимир,500000
3,Орел,300000
4,Курск,200000
5,Казань,2000000
7,Котлас,110000
8,Мурманск,400000
9,Ярославль,500000 

man.csv
9152222221,Андрей,Николаев,1,22
9152222222,Максим,Москитов,1,31
9153333333,Олег,Денисов,3,34
9173333334,Алиса,Никифорова,4,31
9173333335,Таня,Иванова,4,31
9213333336,Алексей,Иванов,7,25
9213333331,Андрей,Некрасов,2,27
9213333332,Миша,Рогозин,2,21
9214444444,Алексей,Галкин,1,38

Вы можете создать эти файлы сами с помощью любого текстового редактора. Напоминаю, что кодировка файлов UTF8.
Перед использованием внешних таблиц необходимо создать специальный объект directory указывающий на каталог, где расположены файлы для внешних таблиц

CREATE OR REPLACE DIRECTORY ext_tab_data AS 'c:/temp';

Здесь
• ext_tab_data – название объекта directory
• AS 'c:/temp' – каталог, где расположены файлы для формирования внешних таблиц.
Далее формируем временные таблицы
Создаем таблицу для файла city.csv
читать дальше...
добавлено: 18 фев 19 просмотры: 1166, комментарии: 1



Sum по disitnct полю , в другой колонке

Чалышев Максим Михайлович
Учебный тренинг. SQL от новичка до профессионала.
Бесплатные авторские видеокурсы SQL, PLSQL, JAVA
-- итак требуется найти сумму по distinct полю на основе идентификатора, который находится в другой колонке
-- е1сть доп условие нельзя использовать подзапрос, то есть необходимо выполнить задачу за одно обращение


-- подготовим данные для тестового примера
drop table t; -- если надо
create table t as
WITH r AS (
select /*+ materialize */ * from ALL_OBJECTS WHERE ROWNUM < 100 )
select object_id as id,length(r.object_name) as fid,
 (select count(1) from r a where length(r.object_name) = length(a.object_name))  as price   
from r


-- есть вторичный ключ fid от которого зависит значение в поле price
-- необходимо найти сумму по полю price в distinct поля fid одним запросом, то есть без вложенного select

select sum(price) sm from t 
-- это неправильное решение

select sum(distinct price) sm from t 
-- и это неправильное решение price может быть одинаковый для разных fid
-- надо как то так
select sum(price distinct fid) sm from t
-- но так написать нельзя

-- первое решение,
-- работает быстро, но решение не идеально, ключ может быть и не числовой
-- сумма Id + price - сумма id
-- , во избежание неприятностей использовали to_number(rpad(fid, 20)
select sum(distinct to_number(rpad(fid,10,'0')) +price) -  sum(distinct to_number(rpad(fid,10,'0'))) as sm  from t


-- решение второе - используем model
  select r from  t 
   model
   return updated rows   
   dimension by(id)
  measures(0 r, nullif(price, lag(price)over(partition by fid order by id)) lg)
  rules upsert(r[0] = sum(lg)[any])
добавлено: 23 май 18 просмотры: 2330, комментарии: 1



Ретроспективные запросы и корзина ORACLE

Чалышев Максим Михайлович
Учебный тренинг. SQL от новичка до профессионала.
Бесплатные авторские видеокурсы SQL, PLSQL, JAVA

А что если нам необходимо узнать , какие данные были в раньше до выполнения операций модификации данных....
как это сделать ?,
Для этого в Oracle предусмотрен механизм ретроспективных запросов (Flashback Query);
То есть, мы можем с помощью запросов посмотреть в прошлое.
Пример подобного запроса
для примера нам понадобится таблица
create table obj_t -- table 
as select owner, object_type  from all_objects group by owner, object_type;
;

запомним время создания таблицы 17:01:01

delete obj_t where object_type = 'TABLE';

удалим наименования таблиц
и запомним время 17:10:14

delete obj_t where owner in ('SYS', 'SYSTEM');

удалим наименования таблиц
и запомним время и запомним время 17:11:22

select *
  from obj_t ;

нет таблиц , нет обьектов owner = sys system

select *
  from obj_t as of scn timestamp_to_scn(to_timestamp('19/04/2018 17:10:00','DD/MM/YYYY HH24:MI:SS')) ;

есть таблицы, нет обьектов owner = sys system

select *
  from obj_t as of scn timestamp_to_scn(to_timestamp('19/04/2018 17:11:00','DD/MM/YYYY HH24:MI:SS')) ;

видим обьекты owner = sys system
читать дальше...
добавлено: 17 май 18 просмотры: 2824, комментарии: 0



Конструкция With и Function

Чалышев Максим Михайлович
Учебный тренинг. SQL от новичка до профессионала.
Бесплатные авторские видеокурсы SQL, PLSQL, JAVA

В SQL диалекте Oracle 12C есть возможность определить функцию или процедуру на языке PL/SQL с помощью оператора WITH, используя обычный SQL


Синаксис
WITH
 PROCEDURE <NAME_PROCEDURE> 
 BEGIN
 ... 
 END;

 FUNCTION <NAME_FUNCTION>
 BEGIN
   ...
 END;
SELECT <NAME_FUNCTION>
FROM <TABLE>;


Примеры
Вывести на экран тип объекта Перевернуть слова означающие типы объектов в ALL_OBJECTS, ограничить выборку 100 строками
WITH
  FUNCTION reversive_fnc(p_name VARCHAR2) RETURN VARCHAR2
  is i NUMBER; v VARCHAR2(50);
  begin
    FOR i IN 1..LENGTH(p_name) LOOP
      v := v || SUBSTR(p_name, LENGTH(p_name)-i+1, 1);
    END LOOP; 
    return v; 
  end;
SELECT DISTINCT reversive_fnc(object_type) as rname, object_type FROM all_objects WHERE rownum < 101;


Добавить к идентификатору объекта заданное кол нулей , преобразовать к числу, ограничить выборку 100 строками
WITH
  FUNCTION incid_fnc(p_id NUMBER, p_count NUMBER) RETURN NUMBER
  is
  begin     
    return TO_NUMBER(rpad(p_id , p_count, '0')); 
  end;
SELECT object_id, incid_fnc(object_id, 10) fn FROM all_objects WHERE rownum < 101
добавлено: 02 май 18 просмотры: 3483, комментарии: 1



mini GItHub на ORACLE

Чалышев Максим Михайлович
Учебный тренинг. SQL от новичка до профессионала.
Бесплатные авторские видеокурсы SQL, PLSQL, JAVA

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

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