DECLARE TYPE TYP_REC IS RECORD ( num number ,vname varchar2(30) ); TYPE rec_table_type IS TABLE OF TYP_REC ; my_tab rec_table_type; BEGIN my_tab := rec_table_type(); my_tab.EXTEND; my_tab(1).num := 21; my_tab(1).vname := 'норма'; my_tab.EXTEND; my_tab(2).num := 21; my_tab(2).vname := 'норма1'; my_tab.EXTEND; my_tab(3).num := 21; my_tab(3).vname := 'норма1'; DBMS_OUTPUT.PUT_LINE('my_tab(1) is '||my_tab(1).num||' - '||my_tab(1).vname); For i IN my_tab.FIRST .. my_tab.LAST Loop dbms_output.put_line( 'Line = ' || my_tab(i).num ) ; dbms_output.put_line( 'Code = ' || my_tab(i).vname ) ; End loop; END;
Создание таблиц - краткий обзор
Нашел вот такую статью, показалась интересной - перевел, и внес некоторые корректировки
Это некий краткий - на одну страницу, обзор с примерами на тему - создание таблиц в oracle
Чтобы пользователь мог создать таблицу он должен обладать соответствующей системной привилегией create table.
Кроме того в дисковом пространстве должно быть достаточно места для создания таблиц.
Превращаем столбцы в строчки LISTAGG
-- Превращаем столбцы в строчки
-- функция LISTAGG
-
Описание функции listagg
согласно переводу Oracle Reference Database listagg может использоваться
1) Как одиночная агрегатная функция, LISTAGG обрабатывает все строки и возвращает одно значение.
2) Как групповая агрегатная функция, LISTAGG обрабатывает и возвращает данные для каждой группы определенной в GROUP BY.
3) Как аналитическая функция, LISTAGG обрабатывает данные разбитые на блоки, задаваемые одним или несколькими выражениями query_partition_clause.
давайте рассмотрим самый интересный пример:
Небольшой пример работы с вложенными таблицами nested table
Предикаты any , all в SQL запросах
-- Использование any , all в запросах , очень похоже на использования предиката exists , но есть некоторые отличия
-- рассмотрим работу данных предикатов на примерах
-- для примера создадим таблицу со следующими полями
-- 1) Название
-- 2) Цвет
-- 3) вес
drop table t1; create table t1(tid number,tname varchar2(20),tprop varchar2(20), tvalue number); -- заполним таблицу данными insert into t1 values(1,'круг','красный', 10); insert into t1 values(2,'круг','зеленый', 30); insert into t1 values(3,'круг','зеленый', 30); insert into t1 values(4,'круг','синий', 20); insert into t1 values(5,'квадрат','красный', 10); insert into t1 values(6,'квадрат','синий', 20); insert into t1 values(7,'квадрат','красный', 20); insert into t1 values(8,'треугольник','красный', 30); insert into t1 values(9,'треугольник','синий', 10);
Hесложный способ перевернуть запрос с группировкой
Hесложный способ перевернуть запрос с группировкой
-- условимся, что перечень значений свойств был ограничен
-- для примера создадим таблицу со следующими полями
-- 1) Название
-- 2) Цвет
-- по условиям задачи у нас сеть конечное количество цветов красный , зеленый , синий
create table t1(tid number,tname varchar2(20),tprop varchar2(20));
Как работают RollUp и Cube?
Подари,продай, обменяй - множество недорогих и отличных вещей объявления вместо AVITO - ВКонтакте
-- rollup и cube специальные групповые опepации oracle
-- применяются начиная с версии 8i
-- очень удобно использовать rollup cube при подведении итогов
-- некоторые из операций могут быть заменены га аналитический sql
-- давайте разберем на примерах
Как убить сессию запросом Oracle 11g Release 11.2
следующий запрос убивает и вешает сессию наглухо
select x.id, rtrim(xmlagg(xmlelement("a",x.recno||';')).extract('//a/text()').getStringVal(),';') as recno, min(recno) from (select 1 as id, 101 as recno from dual union all select 2 as id, 102 as recno from dual union all select 3 as id, 103 as recno from dual union all select 3 as id, 123 as recno from dual union all select 4 as id, 104 as recno from dual union all select 5 as id, 105 as recno from dual) x group by x.id order by min(recno);
Случайность в PL SQL - DBMS_RANDOM
Для работы со случайными значениями в PL SQL используется пакет DBMS_RANDOM
DBMS_RANDOM пакет содержит встроенный генератор случайных чисел
Модель безопасности
Этот пакет должен быть установлен как SYS . По умолчанию в пакете инициализируется с текущим именем пользователя, текущее время с точностью до секунды, и на нынешней сессии. Oracle рекомендует пользователям, которым необходимо выполнять этот пакет должны быть EXECUTE привилегии , и не должны полагаться на PUBLIC EXECUTE привилегии.
• DBMS_RANDOM.RANDOM возвращает целые [-2 ^ 31, 2 ^ 31).
• DBMS_RANDOM.VALUE возвращает чисела из [0,1) с 38 цифр точности.
DBMS_RANDOM может быть явно инициализирован, но не нужно специально инициализировать каждый раз перед вызовом генератора случайных чисел. Будет автоматически инициализировать с даты, идентификатор пользователя и идентификатор процесса, если явной инициализации не выполняется.
Задача с валютами
Исходные данные
-- есть таблица в с номером валюты, датой заполнения курса, курсом валюты
-- оператор вводит курс валюты, когда он поменялся , то есть если несколько дней подряд одинаковый курс - то, в таблице будет единственное значение курса на дату изменения
-- необходимо вывести курс валюты на заданную дату , по каждой валюте
-- если на данную дату нет курса тогда выводится курс на последнюю дату, если в заданную дату курс менялся несколько раз , то надо вывести все изменения
-- Подготовим данные для примера
-- создадим таблицу с валютами\
Передача диапазонов значений в процедуру , в качестве входного параметра
необходимо сделать процедуру которая выводила бы значения числовой последовательностей например,
от - 1 до 5, 15-20, 33-38 , то есть произвольный перечень числовых диапазонов
напишем процедуру, где с помощью регулярных выражений разберем параметр и выведем диапазоны как запрос
для работы с текстовым передаваемым выражением используем конструкцию CONNECT BY
Oracle SQL- основные функции работы со строками
TRANSLATE
Функция TRANSLATE(str1, SRC, DEST) возвращает строку
Параметры :
str1 - строка которая будет преобразована
SRC - символы исходные преобразования
DEST - в которые будет осуществляться преобразование
Описание
преобразовывает каждый символ в последовательности SRC в символ в той же позиции в последовательности DEST
чтобы было более понятно, рассмотрим несколько примеров:
select TRANSLATE('EXAMPLEABCD!','ABCD','ИБЦД') from dual
-- EXИMPLEИБЦД!
символы 'ABCD' - преобразованы в ИБЦД , посл символов EXAMPLE осталась непреобразованной
Интересные приемы работы с ORACLE SQL
При работе с данными часто попадаются таблицы, в которых описан некий временной процесс, который описывает жизнь некоего объекта.
В какие-то временные моменты с ним происходят различные действия.
Он меняет свой статус. Например движение вагона по железнодорожной сети (меняется статус на груженый, порожний, ремонтный).
Или включение-выключение услуг телефонной связи (финансовая или добровольная блокировка услуги). Часто бывает так, что строки, расположенные по времени между этими событиями, не несут в себе информации о статусе объекта.
Работа с внешним приложением по SOAP протоколу в ORACLE .Работа с XML в Oracle.
создании данного материала неоценимую помощь мне оказал мой коллега
Доброквашин Михаил.
В этом материале будут разобраны следующие моменты:
Работа с XML в Oracle (формирование XML, хранение XML в базе, извлечение и парсинг XML документа)
Придача XML документа по HTTP протоколу, ожидание ответа, авторизация на HTTP сервере, получение ответа.
Работа с данными тип LOB.
Использование пакета DBMS_XMLGEN
XML_GEN используется начиная с oracle 11 g, вызов в прошлых версиях ORCALE sys.xml_gen
Применение пакета XML_GEN
XML_GEN используется для преобразования результатов SQL запроса в, XML формат
результат как возвращается как CLOB, и как xmltype
XML_GEN поддерживает методы работы с контекстом выражения SQL, преобразовывает контекст в XML выражение
Oracle SQL UNION, MINUS, INTERSECT - несложные, но эффективные приемы
В этом небольшом материале речь пойдет о сравнении и сопоставлении некоторых одноформатных данных
С такой задачей я столкнулся во время работы над одним из проектов.
Предположим , что есть некий эталонный набор данных, одна или несколько таблиц со структурой заданного формата.
С определенной периодичностью из внешних источников поступают данные в таблицах такой же структуры, но сам данные могут незначительно различаться
Итак, вот несколько несложных , приемов которые помогут вам решить подобную задачу
Функции работы с датами ORACLE SQL
Наиболее часто используемые в повседневной работе функции для преобразования, обработки данных типа DATE
SYSDATE - текущая дата, sysdate-1 - вчера , sysdate-1/24 - час назад :)
последний день месяца
SELECT LAST_DAY(sysdate) "LASTDAY" FROM DUAL;
-- 30.09.2012
первый день месяца
SELECT TRUNC(sysdate, 'MONTH') "FIRSTDAYOFMONTH" FROM DUAL;
-- 01.09.2012
первый день месяца, другая запись
Временные таблицы в Oracle
GLOBAL TEMPORARY
Что же такое временные таблицы, и зачем они нужны?
Давайте разберемся...
Временные таблицы используются в Oracle для хранения данных, которые относятся к одной сессии или одной транзакции.
Причем применение Oracle временных таблиц существенно отличается от применения временных таблиц в том же MS SQL. ORACLE временная таблица это тот же DDL обьект со всеми ограничениями , в MS SQL подобных ограничений нет.
итак подробнее
Использование регулярных выражений REGEXP в ORACLE SQL
Согласно Вики
Регулярные выражения (англ. regular expressions, сокр. RegExp, RegEx, жарг. регэкспы или регексы) это формальный язык поиска и осуществления манипуляций с подстроками в тексте , основанный на использовании метасимволов (символов-джокеров, англ. wildcard characters).
По сути это строка-образец (англ. pattern, по-русски её часто называют «шаблоном», «маской»), состоящая из символов и метасимволов и задающая правило поиска.
Для работы с регулярными выражениями в Oracle SQL используются следующие операторы REGEXP_LIKE, REGEXP_REPLACE, REGEXP_SUBSTR, REG_EXPCOUNT, REG_INSTR
Рассмотрим работу каждой из этих команд и для наглядности создадим временную таблицу preserved_rows и заполним ее след.данными:
- Фамилия, ДАТА РОЖД, город проживания - данные представлены сплошным символьным буфером, с разделителем ","
CREATE GLOBAL TEMPORARY TABLE regtest ON COMMIT PRESERVE ROWS AS SELECT 'Зайцев,01111998,Киев' dt FROM dual UNION SELECT 'Иванов,01011982,Воронеж' dt FROM dual UNION SELECT 'Петров,01011988,Москва' dt FROM dual;
REGEXP_LIKE
-- REGEXP_LIKE выбирает из таблицы все строки соответвующие заданному шаблону регулярного выражения REGEXP
-- пример использования REGEXP_LIKE(выражение; 'regexp шаблон') :