Язык PL SQL Учебный центр информационных технологий Информатика 2013

Язык PL/SQL Учебный центр информационных технологий «Информатика» 2013

Содержание лекции № 2: Введение в язык запросов PL/SQL Способы работы с языком PL/SQL Пользовательские и системные функции языка, выражения, идентификаторы, ключевые слова

Задачи выполняемые при помощи PL/SQL Реализация бизнес-логики в Oracle Server с помощью хранимых процедур и триггеров; Формирование XML-документов, их хранение в базе данных, а также для выполнения операций с этими документами; Связывание WEB страниц с базой данных ORACLE; Выполнение и автоматизация задач автоматизирования базы данных – от защиты на уровне строк до управления сегментами отката в программах PL/SQL

Введение в язык запросов PL/SQL – процедурный язык пошагового программирования содержащий в себе язык SQL PL/SQL – имеет строгие правила области видимости переменных, поддерживает параметризированные вызовы процедур и функций PL/SQL – предусматривает строгий контроль типов, все ошибки несовместимости типов выявляются на этапе компиляции и выполнения. Также поддерживает явное и неявное преобразование типа PL/SQL – имеет элемент Exception Handler (обработчик исключительных ситуаций) для синхронной обработки ошибок на этапе выполнения кода

Защита приложения и целостности выполнения транзакций PL/SQL может обеспечить строгий контроль за выполнением логических транзакций. Вместо доступа к обновлению, изменению или вставки данных в несколько таблиц пользователь получает разрешение только на выполнение процедуры. Единственным способом выполнения транзакции является вызов процедуры, а это гарантирует целостность транзакции.

Строковые типы данных Oracle Тип данных Размер Описание Char От 1 до 2000 байт Символьный тип постоянной длины, строка дополняется пробелами до указанного размера Varchar 2 От 1 до 4000 байт Хранятся строки переменной длины, строка не дополняется пробелами до указанной длины Nchar От 1 до 2000 байт Строка постоянной длины, длина определяется количеством символов в национальной кодировке От 1 до 4000 байт Строка переменной длины, длина определяется количеством символов в национальной кодировке Nvarchar 2 Параметр NLS_LENGTH_SEMANTICS определяет будет ли длина Char или Varchar 2 определяться размером или числом национальных символов

Хранение данных большого размера Тип данных Размер Описание CLOB 32 терабайт Текст в байтах переменной длины NCLOB 32 терабайт Текст символов переменной длины LONG 2 гигабайта Текст переменной длины. Используется для хранения данных в словаре данных BLOB 128 терабайт Неструктурированные бинарные данные BFILE Бинарные файлы, хранятся в операционной системе, доступ возможен только на чтение

Типы данных для хранения числовых значений Тип данных Размер Описание Number Положительное 1 x 10 -130 до 9. 99. . . 9 x 10125 Отрицательное -1 x 10 -130 до 9. 99. . . 9 x 10125 Числовой тип с фиксированным числом знаков после запятой NUMBER (precision, scale) BINARY_FLOAT BINARY_DOUBLE Аналогичны типам FLOAT и DOUBLE в Java и XMLSchema Значения с десятичной точностью, позволяет производить более быстрые вычисления, в отличии от Number не требуют преобразования для операций машинной арифметики

Типы данных для хранения даты Тип данных Описание DATE Поддерживает временные отметки только с точностью до секунды. TIMESTAMP Разновидность типа DATE, которая полезна для хранения особо точных значений даты и времени с точностью до миллисекунды. TIMESTAMP WITH TIME ZONE Разновидность типа TIMESTAMP, включающая мощную логику поддержки временных зон. TIMESTAMP WITH LOCAL TIME ZONE Разновидность типа TIMESTAMP с автоматическим подключением и отключением использования локальной временной зоны.

Функции для работы с датами Операции с датами: Next_day(date 1, ’Понедельник’)- дата следующего дня недели Sysdate – текущая дата Trunc(date 1) – дата округлённая до времени 00: 00 Month_between(date 1, date 2) – число месяцев между двумя датами Date 1 -date 2 – разница в днях между двумя датами Last_day(date 1)- последний день месяца указанной даты Add_days(date 1, 6) , add_days(date 1, -6) – прибавление и вычитание дней из даты

Отображение даты Каждый раз, когда надо вывести значение DATE, Oracle вызывает функцию TO_CHAR автоматически с заданным по умолчанию форматом DATE. Однако, можно отменить заданный по умолчанию формат, вызвав TO_CHAR с явно указанным собственным форматом DATE TO_CHAR(<дата>, '<формат>') например – TO_CHAR(date 1, ’dd. mm. yyyy HH 24: MI: SS’) MM Номер месяца (например, 07) MON Сокращенное название месяца (например, JUL) MONTH Полное название месяца (например, JULY) DD День месяца (например, 24) DY Сокращенное название дня (например, FRI) YYYY Год, в виде 4 -х цифр (например, 1998) YY 2 последние цифры года (например, 98) RR Подобно YY, но к 2 цифрам года, округленного в диапазоне от 1950 до 2049. AM (или PM) Индикатор меридиана (до или после полудня) HH Час дня (1 -12) HH 24 Час дня (0 -23) MI Минуты (0 -59) SS Секунды (0 -59)

Типы данных PL/SQL Коллекции и записи collection, record LOB типы включая. BFILE, BLOB, CLOB, и NCLOB REF CURSORS и REFs на объекты Числовые типы BINARY_DOUBLE, BINARY_FLOAT, BINARY_INTEGER, DECIMAL, DOUBLE PRECISION, FLOAT, INTEGER, NATURALN, NUMBER, NUMERIC, PLS_INTEGER, POSITIVEN, REAL, SIGNTYPE, SMALLINT Булево значение BOOLEAN Дата - DATE, TIMESTAMP WITH TIMEZONE, TIMESTAMP WITH LOCAL TIMEZONE, INTERVAL YEAR TO MONTH, INTERVAL DAY TO SECOND

Специальные символы в PL/SQL Символы Описание : Идентификатор хост переменной ** Оператор возведения в степень <> или != или ^= или ~= Оператор сравнения не равно ; Завершает объявление и операторы % Индикатор атрибута (курсора %ISOPEN или неявных объявлений %ROWTYPE), или символ подстановки в условии LIKE _ Символ подстановки одного символа в условии like @ Индикатор удалённого местоположения объекта || Оператор конкатенации << и >> Ограничители метки <= и >= Операторы сравнения, «меньше или равно» и «больше или равно» : = Оператор присваивания -- и /* */ Однострочный и многострочный комментарий => И. . Оератор ассоциации и оператор диапазона

Идентификаторы Идентификатор – это имя объекта PL/SQL, которым может быть: Константа; Скалярная переменная; Составная переменная(запись или коллекция); Исключение; Процедура; Функция; Пакет; Тип; Курсор; Зарезервированное слово; Метка Идентификатор должен начинаться с буквы, иметь длину до 30 символов, не должен содержать пробелы, может содержать символы $, «_» и «#»

Зарезервированные слова в ORACLE Некоторые идентификаторы имеют в языке PL/SQL специальное назначение, их нельзя использовать в качестве имён объектов в своих программах. Ключевые слова; Идентификаторы пакета STANDART. Представление содержащее зарезервированные слова: select * from v$reserved_words order by keyword

ABORT CREATE GOTO PACKAGE SUM ACCEPT CURRENT GRANT PARTITION TABAUTH ACCESS CURSOR GROUP PCTFREE TABLE ADD DATABASE HAVING PRAGMA TABLES ALL DATA_BASE IDENTIFIED PRIOR TASK ALTER DATE IF PRIVATE TERMINATE AND DBA IN PROCEDURE THEN ANY DEBUGOFF INDEX PUBLIC TO ARRAY DEBUGON INDEXES RAISE UNION AS DECLARE INTERSECT RANGE UNIQUE ASSERT DEFAULT INTO RECORD UPDATE ASSIGN DEFINITION IS RELEASE USE AT DELAY LEVEL REM VALUES AUTHORI DELETE ZATION LIKE RENAME VARCHAR AVG DELTA LIMITED RESOURCE VARCHAR 2 BEGIN DESC LOOP RETURN VARIANCE MAX ROLLBACK VIEW BETWEEN DIGITS

BODY DO MIN ROWNUM VIEWS BOOLEAN DROP MINUS ROWTYPE WHEN BY ELSE MOD RUN WHERE CASE ELSIF NEW SAVEPOINT WHILE CHAR END NOCOMPRESS WSCHEMA WITH CHAR_BASE ENTRY NOT SELECT WORK CHECK EXCEPTION NULL SEPARATE XOR CLOSE EXCEP NUMBER SET CLUSTER TION_INIT NUMBER_BASE SIZE CLUSTERS EXISTS OR SPACE COLAUTH EXIT ON SQL COLUMNS FALSE OPEN SQLCODE COMMIT FETCH OPTION SQLERRM COMPRESS FOR OR START CONNECT FORM ORDER STATEMENT CONSTANT FROM OTHERS STDDEV COUNT FUNCTION OUT SUBTYPE CRASH GENERIC

Правила наименования делают код более читабельным для понимания и упрощают отладку кода Идентификатор Правило Пример Variable v_prefix v_product_name Constant c_prefix c_tax Parameter p_prefix p_cust_id Exception e_prefix e_check_credit_limit Cursor cur_prefix cur_order Type typ_prefix typ_customer

Структура PL/SQL блока Имеет наглядную структуру блоков, облегчающую расширение и сопровождение приложений PL/SQL Анонимный PL/SQL блок DECLARE BEGIN Хранимая программа <header> IS|AS DECLARE BEGIN EXCEPTION END

Процедуры Процедура – поименованный pl/sql блок, который выполняет последовательность действий Процедура хранится в базе данных как объект схемы Параметры в процедурах могут передаваться в трёх режимах: IN, OUT, IN OUT

Объявление процедуры CREATE [OR REPLACE] PROCEDURE procedure_name [(parametr 1 [mode] datetype 1, parametr 2 [mode] datetype 2, …)] IS|AS [объявление локальных переменных; …] BEGIN ---действия; End [procedure_name]; Опция OR REPLACE позволяет, при изменении объекта сохранить все имеющиеся на объект привилегии.

Пример создания процедуры CREATE OR REPLACE PROCEDURE get_avg_order (p_cust_id NUMBER, p_cast_last_name VARCHAR 2, P_order_tot NUMBER) IS v_cust_id customers. customer_id%type; v_cust_name customers. cust_last_name%type; BEGIN SELECT customers_id, customers. cust_last_name INTO v_cust_id, v_cust_name, v_avg_order FROM CUSTOMERS, ORDERS WHERE customers. customer_id=orders. customer_id AND customers. customer_id=101; END; Таблицы схемы OE

Функции Функция – это блок который возвращает значение Функции хранятся в базе данных как объекты схемы Функции могут использоваться для вычисления значений Функция может использоваться в SQL запросе и в PL/SQL коде (в SQL запросе не могут использоваться функции производящие операции над объектами)

Объявление функции CREATE [OR REPLACE] FUNCTION function_name [(parameter 1 [mode] datetype 1, parameter 2 [mode] datetype 2, …)] RETURN datatype IS|AS [объявление локальных переменных; …] BEGIN ---действия; RETURN возвращаемое значение; End[function_name];

Пример функции CREATE OR REPLACE FUNCTION get_credit (v_id customers. customer_id%TYPE) RETURN NUMBER IS v_credit customer. credit_limit%TYPE : =0; BEGIN SELECT credit_limit INTO v_credit FROM customers WHERE customer_id=v_id; RETURN v_credit; END; Пример вызова функции: EXECUTE dbms_output. put_line(get_credit(101));

Способы вызова функции Можно использовать хост переменную для хранения результата: VARIABLE v_credit NUMBER EXECUTE : v_credit : = get_credit(101); Можно использовать как локальную переменную для хранения результата: v_credit: =get_credit(101); Можно использовать в SQL выражении: SELECT get_credit(customer_id) FROM customers;

Вложенные блоки Вложенный блок – блок, который расположен в другом блоке DECLARE CURSOR cur_emp IS …; BEGIN DECLARE v_total_sales number; BEGIN DECLARE v_hiredate; BEGIN … END;

Условные операторы Оператор IF позволяет реализовать в программе условную логику. Тип оператора IF Описание IF THEN END IF; Это простейшая форма оператора IF. Условие, содержащееся между ключевыми словами IF и THEN , определяет, должна ли выполнятся группа операторов, находящихся между THEN и IF. IF THEN ELSE END IF; Конструкция реализуют логику «или-или» . В зависимости от условия, заданного между ключевыми словами IF и THEN, выполняется либо код, находящийся между THEN и ELSE, либо код между ELSE и END IF. IF THEN ELSIF ELSE END IF; Это самая сложная форма оператора IF, которая выбирает действие на основе набора взаимоисключающих условий и выполняет соответствующую группу исполняемых операторов.

Примеры использования оператора IF IF salary>40000 THEN give_bonus(employee_id, 500); END IF; IF salary <= 40000 THEN give_bonus(employee_id, 0) ELSE give_bonus(employee_id, 500); END IF; IF salary BETWEEN 10000 AND 20000 THEN give_bonus(employee_id, 1000); ELSEIF salary > 40000 THEN give_bonus(employee_id, 500); ELSE give_bonus(employee_id, 0); END IF;

Оператор CASE Операторы CASE позволяют выбрать для выполнения одну из нескольких последовательностей исполняемых операторов. Простой оператор CASE – связывает одну или несколько последовательностей операторов PL/SQL с соответствующим значением (последовательность для выполнения выбирается с учётом результатов вычисления выражения, возвращающего указанное значение). Поисковый оператор CASE - выбирает для выполнения одну из последовательностей операторов в зависимости от результатов вычислений списка логических условий (выполняется последовательность операторов, связанная с первым условием, результат проверки которого оказался равным TRUE). CASE выражение – позволяет выбрать для вычислений одно или несколько выражений.

Простой оператор CASE выражение WHEN результат_1 THEN Операторы_1 WHEN результат_2 THEN Операторы_2 … ELSE --Конструкция ELSE не обязательна Операторы_else END CASE; CASE employee_type WHEN ‘S’ THEN Award_salary_bonus(employee_id); WHEN ‘H’ THEN Award_hourly_bonus(employee_id); WHEN ‘C’ THEN Award_commissioned_bonus(employee_id); END CASE;

Поисковый оператор CASE WHEN выражение_1 THEN Операторы_1 WHEN выражение_2 THEN Операторы_2 … ELSE --Конструкция ELSE не обязательна Операторы_else END CASE; CASE WHEN salary>=10000 AND salary<=20000 THEN give_bonus(employee_id, 1500); WHEN salary>20000 AND salary<=40000 THEN give_bonus(employee_id, 1000); WHEN salary>40000 THEN give_bonus(employee_id, 500); ELSE give_bonus(employee_id, 0); END CASE;

Циклы Простой цикл LOOP: PROCEDURE set_all_ranks(p_max_rank_in INTEGER) IS v_ranking_level NUMBER(3): =1; BEGIN LOOP EXIT WHEN v_ranking_level>p_max_rank_in; set_rank(v_ranking_level); v_ranking_level: =v_ranking_level+1; END LOOP; END;

Цикл FOR PROCEDURE set_all_ranks(p_max_rank_in IN INTEGER) IS v_ranking_level NUMBER(3); BEGIN FOR v_ranking_level IN 1…p_max_rank_in LOOP set_rank(v_ranking_level); END LOOP; END;

Цикл WHILE PROCEDURE set_all_ranks(p_max_rank_in IN INTEGER) IS v_ranking_level NUMBER(3): =1; BEGIN WHILE v_ranking_level<p_max_rank_in LOOP set_rank(v_ranking_level); v_ranking_level: =p_max_rank_in+1; END LOOP; END;

Метки Метка PL/SQL – это способ наименования некоторого фрагмента программы Формат метки - <<идентификатор>> С помощью метки можно указать имя анонимного блока (на время его выполнения): <<outerblock>> DECLARE v_counter integer: =0; BEGIN … DECLARE v_cunter INTEGER: =1; BEGIN IF v_counter =outoblock. v_counter THEN … END IF; END; Без метки невозможно различить переменные с одинаковым именем. <<insert_but_ignore_dups>> BEGIN INSERT INTO catalog VALUES (…); EXCEPTION WHEN DUP_VAL_ON_INDEX THEN NULL; END;

Оператор GOTO позволяет переходить к месту метки, но нельзя производить переход из предшествующих блоков, условных операторов в последующий блок BEGIN. . . GOTO insert_row; . . . <<insert_row>> INSERT INTO EMPLOYEES VALUES. . . END; DECLARE v_Status NUMBER : = 1; BEGIN IF v_Status = 1 THEN GOTO mybranch; ELSE v_Status : = 1; END IF; <<mybranch>> NULL; END;

Оператор EXIT - Позволяет выйти из цикла или перейти в целевую точку выхода обозначенную меткой. BEGIN <<outer_loop>> LOOP EXIT outer_loop WHEN …; END LOOP; END; CREATE OR REPLACE FUNCTION exitfunc(p_pass_string VARCHAR 2) RETURN NUMBER IS v_len Number : = 1; BEGIN WHILE len <= LENGTH(p_PASS_STRING) LOOP v_len : = v_len + 1; EXIT WHEN SUBSTR(p_PASS_STRING, v_len, 1) = ' '; END LOOP; RETURN v_len ; END;

Обработка исключений В языке PL/SQL ошибки всех видов интерпретируются как исключения, к ним относятся: Ошибки, которые генерируются системой (нехватка памяти, отсутствие соединение с удалённой базой данных). Ошибки, вызванные действием пользователя. Предупреждения, выдаваемые приложением пользователю. PL/SQL перехватывает ошибки и реагирует на них при помощи обработчиков исключений. Механизм функционирования обработчиков исключений позволяет чётко определить код обработки ошибок от исполняемых операторов, позволяющему реализовать обработку ошибок управляемую событием.

Пример вызова исключения DECLARE v_lname VARCHAR 2(15); BEGIN SELECT cust_last_name INTO v_lname FROM customers WHERE cust_first_name=‘Мария’; DBMS_OUTPUT. PUT_LINE(‘Фамилия Марии : ’||v_lname); EXCEPTION WHEN TOO_MANY_ROWS THEN DBMS_OUTPUT. PUT_LINE(‘Возвращается слишком много значений. ’) END; Когда происходит ошибка, выполнение исполняемого блока прерывается и управление передаётся отдельному разделу исключения в текущем блоке. Исключение может быть вызвано из кода программы: RAISE TOO_MANY_ROWS; Выполнение кода прервётся и управление перейдёт в блок исключений.

Ошибки предопределённые сервером ORACLE Имя исключения Номер ошибки Описание ACCESS_INTO_NULL ORA 06530 Попытка присвоить значение неинициализированн ому объекту CASE_NOT_FOUND ORA 06592 Нет нужного значения в условиях WHEN и нет секции ELSE COLLECTION_IS_NULL ORA 06531 Попытка обратиться к методу коллекции или вложенной таблице, которая не инициализирована (кроме EXISTS) CURSOR_ALREADY_OPEN ORA 06511 Попытка открыть уже открытый курсор

Имя исключения Номер ошибк и Описание DUP_VAL_ON_INDEX ORA 00001 Попытка вставить дублирующее значение INVALID_CURSOR ORA 01001 Неверная операция над курсором INVALID_NUMBER ORA 01722 Ошибка преобразования строки в число LOGIN_DENIED ORA 01017 Попытка связи с сервером с неверным именем пользователя или паролем NO_DATA_FOUND ORA 01403 Строка SELECT не вернула данные NOT_LOGGED_ON ORA 01012 Попытка соединится с сервером при закрытом соединении PROGRAM_ERROR ORA 06501 PL/SQL имеет внутреннюю проблему ROWTYPE_MISMATCH ORA 06504 Несовместимость переменных хост курсора

Имя исключения Номер ошибк и Описание STORAGE_ERROR ORA 06500 PL/SQL запустился вне памяти или память повреждена SUBSCRIPT_BEYOND_COUNT ORA 06533 Запрошен элемент коллекции больший чем число элементов SUBSCRIPT_OUTSIDE_LIMIT ORA 06532 Запрошен элемент вложенной таблицы, находящийся вне диапазона элементов SYS_INVALID_ROWID ORA 01410 Неверный RAWID строки TIMEOUT_OFF_RESOURCE ORA 00051 Истекло время ожидания ресурса TOO_MANY_ROWS ORA 01422 Однострочный SELECT вернул более одной строки VALUE_ERROR ORA 06502 Арифметическая ошибка ZERO_DEVIDE ORA- Попытка деления на ноль

Директивы компилятора Псевдоинструкция, которая передаёт некоторую информацию компилятору и при трансляции не включается в исполняемый код. Синтаксис – PRAGMA директива: Директивы компилятора Описание AUTONOMOUS_TRANSACTION Предписывает исполнительному ядру PL/SQL выполнить сохранение или откат любых изменений, внесённых в базу данных в текущем блоке, без воздействия на главную транзакцию. EXCEPTION_INIT Указывает компилятору связать конкретный номер ошибки с идентификатором, который объявлен в программе как исключения RESTRICT_REFERENCES Задаёт для компилятора уровень чистоты программы (отсутствие в ней действий, вызывающих побочные эффекты) SERIALLY_REUSABLE Указывает исполняемому ядру PL/SQL, что данные уровня пакета не должны сохраняться между обращением к ним.

Создание имени для исключений Пользователь может задать имя для встроенного исключения при помощи директивы exeption_init Пример: DECLARE e_no_such_sequence EXEPTION; PRAGMA EXCEPTION_INIT(e_no_such_sequence, -2289); BEGIN … … EXCEPTION WHEN e_no_such_sequence THEN … END;

Автономные транзакции – это возможность выполнять и затем сохранять или отменять инструкции DML (Data Manipulation Language – язык манипулирования данными) INSERT, UPDATE и DELETE безотносительно к основной транзакции сеанса. Пример – механизм регистрации ошибок PROCEDURE write_log( p_code IN INTEGER, p_text IN VARCHAR 2) IS PRAGMA AUTONOMOUS_TRANSACTION; BEGIN INSERT INTO log VALUES(p_code, p_text, USER, SYSDATE); COMMIT; END;

Inline-подстановка в PL/SQL На этапе выполнения кода происходит подстановка тела функции вместо вызова BEGIN PRAGMA INLINE(my_func, 'YES'); -- включаем подстановку for f in (select * from employees) loop x: = my_func(f. Name, f. amount) + 17; -- не вызов, а тело -- функции! end loop; PRAGMA INLINE(my_func, 'NO'); -- выключаем подстановку … END; Увеличение скорости выполнения: вместо передачи параметров, возврата управления и результатов Включение/выключение подстановки в коде

Права создателя и права вызывающего Definer rights – права создателя – программа выполняется от имени владельца программы и с его привилегиями Invoker rights – права вызывающего – процедура вызывается с привилегиями пользователя, от которого идёт вызов программы Пример программы, которая выполняется с правами вызывающего: CREATE OR REPLACE PROCEDURE runddl (p_ddl_in IN VARCHAR 2) AUTHID CURRENT_USER IS BEGIN EXECUTE IMMEDIATE p_ddl_in; END;

Рекомендуемая литература: С. Фейерштейн, Б. Прибыл Oracle PL/SQL для профессионалов

Контакты: Хайновская Наталья natalix 83@mail. ru

СПАСИБО ЗА ВНИМАНИЕ!