PL/SQL

Программная конструкция Описание Среда выполнения Анонимный блок Неименованный блок PL/SQL, вставленный в приложение или созданный интерактивно. Сервер Oracle, клиентская часть Хранимая процедура или функция Именованный блок PL/SQL, хранящийся на сервере, может принимать параметры и многократно вызываться по имени. Сервер Oracle, клиентская часть Пакет Именованный модуль PL/SQL, объединяющий воедино взаимосвязанные процедуры, функции. Состоит из спецификации и тела пакета. Сервер Oracle, клиентская часть Триггер базы данных Блок PL/SQL, связанный с таблицей БД и выполняемый автоматически, когда над этой таблицей выполняется операции DML. Сервер Oracle

Структура блока PL/SQL: DECLARE - необязательно Константы, переменные, типы, курсоры, пользовательские исключения BEGIN - обязательно Команды SQL Команды PL/SQL EXCEPTION - необязательно Действия при возникновении ошибки END; - обязательно

Секция объявлений DECLARE v_date_time TIMESTAMP; ….

Секция исключительных ситуаций EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(sqlerrm); End;

Минимальный блок pl/sql Begin NULL; End;

Простой пример DECLARE В_NAME VARCHAR2(100); BEGIN SELECT NAME INTO В_NAME FROM SALERS WHERE ID=106; dbms_output.put_line('Фамилия продавца: '||В_NAME); EXCEPTION WHEN OTHERS THEN dbms_output.put_line('Произошла ошибка!'); END;

Типы блоков Анонимные блоки Процедуры Функции

Типы переменных Переменные PL/SQL Скалярные Составные Ссылочные LOB (большие объекты) Прочие переменные (не PL/SQL) Связанные переменные и хост-переменные

Основные скалярные типы данных Тип Описание Числовые NUMBER [(точность, масштаб)] Числа с точность и масштабом. Точность в диапазоне от 1 до 38, а масштаб – от -84 до 127 BINARY_INTEGERОсновной тип для целых числе от до PLS_INTEGERОсновной тип для целых числе со знаком до Символьные CHAR [ (Максимальная_Длина)] Основной тип для символьных данных постоянной длины до байтов. VARCHAR2 (Максимальная_Длина) Основной тип для символьных данных переменной длины 4Gb.

Основные скалярные типы данных Тип Описание Даты DATE Основной тип для дат и времени. Значение DATE включают время в секундах с полуночи. Дипозон дат: 4712 г. До.н.э – 9999 н.э. TIMESTAMP [(точность)] Расширяет тип данных DATE и хранит год, месяц, день, час, минуту, секунду и доли секунды. Логические BOOLEAN Основной тип для хранения значений, используемых в логических вычислениях: True (истинно), False(ложно) или NULL (не определено).

Требование к переменным должны начинаться с буквы могут включать буквы и цифры могут включать специальные символы, например, знак доллара ($), символы подчеркивания (_) и решетки (#) максимальная длина – 30 символов не могут быть зарезервированными словами. identifier [CONSTANT] datatype [NOT NULL] [:= | DEFAULT expr];

Ключевые слова Select keyword, length From v_$reserved_words Where (length>1 or keyword = A) And keyword != < Order by keyword

Переменные инициализируются всякий раз при входе в блок. По умолчанию переменные принимают неопределенное значение NULL. Переменную можно инициализировать в декларативной секции, в том числе и с использованием ключевого слова DEFAULT. Переменные, не имеющие стандартного начального значения можно инициализировать с использованием знака присваивания ":=". Если переменная имеет ограничение NOT NULL - присвоить неопределенное значение такой переменной невозможно.

Примеры объявления переменных. DECLARE В_START_DATE DATE; В_NAME VARCHAR2(100) NOT NULL; В_SUM NUMBER :=0; К_LIMIT CONSTANT NUMBER:=10000;

Атрибут %TYPE Атрибут %TYPE позволяет объявлять переменную типа, соответствующего: типу другой переменной; типу столбца базы данных. Например: var1 REAL(14,2); var2 var1%TYPE; - Переменная var2 будет иметь тип как var1 var_f1 user1.tbl1.f1%TYPE; - Переменная var_f1 будет иметь тот же тип, что и поле f1 таблицы tbl1 пользователя user1

Атрибут %ROWTYPE Атрибут %ROWTYPE позволяет объявлять переменную типа "запись", соответствующую строке таблицы. Переменная такого типа имеет поля, совпадающие с полями таблицы по имени и типу. DECLARE tbl1_rec1 tbl1%ROWTYPE; - Для строки из таблицы tbl1 tbl1_rec2 tbl1%ROWTYPE; CURSOR c1 IS SELECT * FROM tbl1; emp_rec2 c1%ROWTYPE; - Для строки курсора с 1, - созданного из таблицы tbl1 emp_rec3 с 1%ROWTYPE; BEGIN SELECT * INTO tbl1_rec1 FROM tbl1 WHERE tbl1.f1=1; emp_rec2 := emp_rec1; - Присвоение значения всем полям записи END

Составные типы данных PL/SQL RECORD (запись) PL/SQL TABLE (таблица) Nested TABLE (вложенная таблица) VARRAY (массив переменной длины)

Записи PL/SQL TYPE type_name IS RECORD ( field_name1 {field_type | variable%TYPE | table.column%TYPE | table%ROWTYPE} [NOT NULL], field_name2 {field_type | variable%TYPE | table.column%TYPE | table%ROWTYPE} [NOT NULL],...);

Таблицы PL/SQL Тип данных, который подобен таблицам базы данных. Таблица PL/SQL должна содержать два компонента: Главный ключ типа BINARY_INTEGER, по которому индексируется таблица PL/SQL. Столбец скалярного типа данных или типа RECORD. TYPE type_name IS TABLE OF { column_type | variable%TYPE | table.column%TYPE } [NOT NULL] INDEX BY BINARY_INTEGER;

Процедуры и функции для работы с таблицами PL/SQL Метод Описание EXISTS(n)Возвращает "истинно", если n-й элемент в таблице существует. COUNTВозвращает текущее количество элементов в таблице FIRST LAST Возвращает первый и последний номера индексов в таблице. В случае если таблица пуста возвращается NULL. PRIOR(n)Возвращает индекс, предшествующий индексу n в таблице PL/SQL. NEXT(n)Возвращает индекс, следующий за индексом n в таблице PL/SQL. EXTEND(n,j) С помощью этого метода в таблицу добавляется следующее: EXTEND добавляет 1 элемент со значением NULL. EXTEND(n) добавляет n элементов со значением NULL. EXTEND(n,j) добавляет n копий j-го элемента. TRIM Удаляет один элемент с конца таблицы. TRIM(n) удаляет n элементов. DELETE DELETE удаляет из таблицы все элементы. DELETE(n) удаляет из таблицы PL/SQL n-ый элемент. DELETE(n,m) удаляет из таблицы все элементы в диапазоне n..m.

Связанная переменная переменная, которая объявляется во внешней среде, а затем используется для динамической передачи параметра в программу PL/SQL. :BV_Car := Audi;

Работа с данными в PL/SQL Для извлечения данных из БД и их изменения (DML) используются команды SQL. Управление транзакциями также осуществляется при помощи команд SQL - COMMIT и ROLLBACK. Команды DDL и DCL явным образом использовать в PL/SQL нельзя. Для этой цели можно воспользоваться возможностями встроенного пакета DBMS_SQL.

Выборка строк данных SELECT список_полей INTO список_переменных FROM список_таблиц WHERE условие;

DECLARE В_NAME VARCHAR2(100); BEGIN SELECT NAME INTO В_NAME FROM SALERS WHERE ID=5.. В_NAME:='Продавец '||В_NAME||' имеет идентификатор 5'.. END;

Изменение данных в PL/SQL BEGIN INSERT INTO CARS VALUES (AUDI', A2', Cерая', );.. END; Пример вставки данных в PL/SQL BEGIN DELETE FROM SALERS WHERE ID=200;.. END; Пример удаления данных в PL/SQL

Операторы ветвления IF условие THEN выражения; [ELSIF условие THEN выражения;] [ELSE выражения;] END IF;

Циклы простой цикл цикл FOR цикл WHILE

Простой цикл LOOP Команда 1;.. EXIT [WHEN условие];.. END LOOP;

Цикл FOR FOR имя_счетчика IN верхняя_граница..нижняя_граница LOOP Команда 1; Команда 2;.. END LOOP;

Цикл WHILE WHILE условие LOOP Команда 1; Команда 2;.. END LOOP;

Курсоры Курсор – это средство извлечения данных из базы данных Oracle. Курсоры содержат определения столбцов и объектов (таблиц, представлений и т.п.) из которых будут извлекаться данные, а также набор критериев, определяющих какие именно строки должны быть выбраны

Неявные курсоры - простой оператор SELECT... INTO извлекает одну строку данных непосредственно в переменные локальной программы. Явные курсоры - вы можете явно объявить курсов в разделе объявлений (локального блока или пакета). Курсорные переменные - дополнительный уровень гибкости обеспечивают курсорные переменные (объявленные на основе типа REF CURSOR), которые позволяют передавать указатель на результирующее множество, полученное по запросу. Курсорные выражения - выражения CURSOR преобразуют оператор SELECT в указатель (типа REF CURSOR) на результирующее множество и могут использоваться в сочетании с табличными функциями для повышения производительности приложений.

Неявные курсоры создается всякий раз, когда в PL/SQL выполняется запрос, Oracle присваивает ему системное имя SQL%ROWCOUNT Количество строк, выбранных или измененных последней командой SQL SQL%FOUND Логический атрибут, возвращающий значение "истинно", если последняя команда SQL обработала одну или несколько строк. SQL%NOTFOUND Логический атрибут, возвращающий значение "истинно", если последняя команда SQL не обработала ни одной строки. SQL%ISOPEN Всегда возвращает значение "ложно", т.к. PL/SQL закрывает неявные курсоры сразу после выполнения. Атрибуты, которые можно применять для неявного курсора для определения результатов выполнения команды SQL: