Oracle SQL

Фильтр по тегу: pl sql


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

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

SQL. 5 дней которые изменят твою жизнь.
моя книга , изучаем Oracle SQL, бесплатно
Oracle SQL. 100 шагов от новичка до профессионала. 20 дней новых знаний и практики или платная печатная версия

Введение
В 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 просмотры: 3814, комментарии: 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 просмотры: 3203, комментарии: 1



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

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

Бесплатные авторские видеокурсы SQL, PLSQL, JAVA
моя книга , изучаем Oracle SQL, бесплатно
Oracle SQL. 100 шагов от новичка до профессионала. 20 дней новых знаний и практики или платная печатная версия

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



Динамический SQL в Oracle - легко и понятно (часть вторая - курсоры FETCH , Close , Open FOR )

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

моя книга , изучаем Oracle SQL, бесплатно
Oracle SQL. 100 шагов от новичка до профессионала. 20 дней новых знаний и практики или платная печатная версия

Использование курсоров в динамическом SQL, команды FETCH и CLOSE
Для работы с динамическим запросом который возвращает несколько строк используются курсоры . Работа с курсорами - это всего три команды: OPEN-FOR , FETCH и CLOSE .

Сначала , вы с помощью команды OPEN, открываете переменную курсора, далее в цикле FOR последовательно считываете результат запроса из нескольких строк запроса. В каждой итерации цикла FOR, вызывается команда FETCH которая считывает из результирующего набора по одной записи. Когда все строки обрабатываются, вы закрываете курсор вызывая метод CLOSE для переменной курсора.
читать дальше...
добавлено: 07 сен 13 просмотры: 46834, комментарии: 2



Hесложный способ перевернуть запрос с группировкой

Чалышев М.М www.orasource.ru ; резюме автора
изучаем SQL группа в контакте - присоединяйтесь

Hесложный способ перевернуть запрос с группировкой
-- условимся, что перечень значений свойств был ограничен
-- для примера создадим таблицу со следующими полями
-- 1) Название
-- 2) Цвет
-- по условиям задачи у нас сеть конечное количество цветов красный , зеленый , синий
create table t1(tid number,tname varchar2(50),tprop varchar2(20));

-- заполним таблицу данными
insert into t1 values(1,'круг','красный');
insert into t1 values(2,'круг','зеленый');
insert into t1 values(3,'круг','зеленый');
insert into t1 values(4,'круг','синий');
insert into t1 values(5,'квадрат','красный');
insert into t1 values(6,'квадрат','синий');
insert into t1 values(7,'квадрат','красный');
insert into t1 values(8,'треугольник','красный');
insert into t1 values(9,'треугольник','синий');


-- выполним свой
select tname, tprop, count(tprop) count from t1 group by tname, tprop



TNAME TPROP COUNT
круг синий 1
круг зеленый 2
круг красный 1
квадрат синий 1
квадрат красный 2
треугольник синий 1
треугольник красный 1


-- таким образом получим количество фигур определенного цвета
-- преобразуем запрос к более интересному виду
select tname, sum(decode(tprop,'синий',1,0)) "синий", sum(decode(tprop,'красный',1,0)) "красный" , sum(decode(tprop,'зеленый',1,0)) "зеленый"  from t1 
group by tname

TNAME синий красный зеленый
квадрат 1 2 0
круг 1 1 2
треугольник 1 1 0


в oracle 11 g появилась конструкция pivot
которая позволяет переворачивать группировки с ограниченным количеством перечислений
приведу пример использования инструкции pivot в рамках решения заданной задачи

SELECT * FROM (select tname, tprop from t1)
  PIVOT ( count(tprop) FOR tprop IN  ('красный','синий','зеленый'));


TNAME 'красный' 'синий' 'зеленый'
квадрат 2 1 0
круг 1 1 2
треугольник 1 1 0



Чалышев М.М www.orasource.ru
добавлено: 24 янв 13 просмотры: 4652, комментарии: 2



О блоге

Чалышев М.М www.orasource.ru ; резюме автора

я работаю с ORACLE около 8 лет, использовал в работе FORMS, REPORT
системы отчетности BI, работал с XML, партицирумыми многомиллионными таблицами
использовал ORACLE Migration tools и JAVA.
В своем блоке я хотел пролить свет на интересные и мало используемые возможности
диалекта SQL ORACLE.
Некоторые записи блога так же коснуться и pl sql , подробностей работы со специальными пакетами ORACLE...
надеюсь это будет интересно и полезно многим...
добавлено: 14 окт 12 просмотры: 1533, комментарии: 0