Тема PL SQL Вопросы 1 2 3 4 5

Тема: PL/SQL Вопросы: 1. 2. 3. 4. 5. 6. 7. Общая характеристика PL/SQL Условные операторы Циклы Курсоры Типы данных и подтипы Процедуры и функции Триггеры 1

Литература 1. Скотт Урман. Oracle 9 i. Программирование на языке PL/SQL. М. : «ЛОРИ» . 2004. – 528 с. 2

Общая характеристика PL/SQL используется в СУБД Oracle и представляет собой расширение языка SQL в направлении создания средств, позволяющих создавать программы с обработкой данных запросов. В отличие от SQL, PL/SQL допускает объявление переменных, массивов, разветвления, циклы, создание процедур и функций. 3

Условные операторы В программах PL/SQL для организации разветвлений служат условные операторы (оператор IF). Например, следующий блок запрашивает базу данных для определения числа студентов и вставляет сообщения в таблицу temp_table: DECLARE v_Total. Students NUMBER; BEGIN -- Извлекаем из базы данных число студентов SELECT COUNT(*) INTO v_Total. Students FROM students; 4

Условные операторы -- На основе этого значения вставляем подходящую строку в temp_table IF v_Total. Students = 0 THEN INSERT INTO temp_table (char_col) VALUES(‘Никто не зарегистрирован' ); ELSEIF v_Total. Students < 5 THEN INSERT INTO temp_table (char_col) VALUES('Зарегистрировано несколько студентов'); 5

Условные операторы ELSEIF v_Total. Students < 10 THEN INSERT INTO temp_table (char_col) VALUES('Зарегистрировано немного студентов'); ELSE INSERT INTO temp_table (char_col) VALUES('Зарегистрировано много студентов'); END IF; END; 6

Циклы В PL/SQL поддерживаются различные виды циклов. Цикл (loop) позволяет неоднократно выполнять одну и ту же последовательность операторов. Например, в следующем блоке используется простой цикл для ввода цифр от 1 до 50 в таблицу temp_table: DECLARE v_Counter BINARY_INTEGER : = 1; BEGIN LOOP INSERT INTO temp_table (num_col) VALUES (v_Counter); v_Counter : = v_Сounter + 1; EXIT WHEN v_Counter > 50; END LOOP; END; 7

Циклы Числовой цикл FOR упрощает синтаксис программы. Операции из предыдущего примера можно выполнить так: BEGIN FOR v_LCounter IN 1. . 50 LOOP INSERT INTO temp_table (num_col) VALUES (v_LCounter); END LOOP; END; 8

Курсоры Курсор (cursor) используется для обработки нескольких строк, извлекаемых из базы данных с помощью оператора SELECT. С помощью курсора можно поочередно обрабатывать строки из возвращаемого набора строк. Для работы со строками курсора, нужно выполнить три операции: • OPEN (открыть курсор); • FETCH (выбрать строки); • CLOSE (закрыть курсор). Пример. Использование курсора для подсчета в базе данных имен и фамилий всех студентов: DECLARE v_First. Name VARCHAR 2(20); v_Last. Name VARCHAR 2(20); 9

Курсоры -- Объявление курсора. Задаем SQLоператор, возвращающий нужные строки. CURSOR c_Students IS SELECT first_name, last_name FROM students; BEGIN -- Начинаем обработку курсора. OPEN c_Students; LOOP 10

Курсоры -- Считываем одну строку. FETCH c_Students INTO v_First. Name, v_Last. Name; -- Выход из цикла после считывания строк. EXIT WHEN c_Students%NOTFOUND; /* Обрабатываем данные */ END LOOP; -- Заканчиваем обработку. CLOSE c_Students; END; 11

Сборные конструкции PL/SQL аналогичны массивам в других языках третьего поколения. В PL/SQL применяются сборные конструкции трех типов: • индексные таблицы, • вложенные таблицы, • изменяемые массивы. Например: DECLARE -- тип - индексная таблица TYPE t_Index. By IS TABLE OF NUMBER INDEX BY BINARY_INTEGER; -- тип - вложенная таблица TYPE t_Nested IS TABLE OF NUMBER; 12

Сборные конструкции -- изменяемый массив TYPE t_Varray IS VARRAY(10) OF NUMBER; -- переменные заданных типов v_Index. By t_Index. By; v_Nested t_Nested; v_Varray t_Varray; BEGIN v_Index. By(1) : = 1; v_Index. By(2) : = 2; v_Nested : = t_Nested(1, 2, 3, 4, 5); v_Varray : = t_Varray(1, 2); END; 13

Использование %TYPE Для работы с данными, хранимыми в таблицах базы данных, используются переменные PL/SQL. При этом переменной, работающей с некоторым столбцом, следует присваивать тип, соответствующий типу столбца. Например, столбец first_name таблицы students имеет тип VARCHAR 2(20). С учетом этого можно объявить переменную следующим образом: DECLARE v_First. Name VARCHAR 2(20); 14

Использование %TYPE Если описание столбца first_nane изменится, например, таблица будет модифицирована и столбец firstjiame получит тип VARCHAR 2(25). То текст программы PL/SQL, в которой используется столбец, должен быть изменен: DECLARE v_First. Name VARCHAR 2(25); Вместо явного задания типа переменной удобно использовать атрибут %TYPE. Он добавляется к ссылке на столбец таблицы или к другой переменной и возвращает ее тип. Например: DECLARE v_First. Name students. first_name%TYPE; 15

Использование %TYPE При использовании атрибута %TYPE переменная v_First. Name будет иметь тот тип, который присвоен столбцу first_name таблицы students. Тип определяется всякий раз, когда данный блок выполняется для анонимных и именованных блоков и когда компилируются хранимые объекты (процедуры, функции и т. д. ). Атрибут %TYPE может использоваться с переменными, объявленными ранее. Пример. Варианты применения атрибута %TYPE: DECLARE v_Room. ID classes. room_id%TYPE; -- NUMBER (5) v_Room. ID 2 v_Room. ID%TYPE; -- NUMBER (5) v_Temp. Var NUMBER(7, 3) NOT NULL : = 12. 3; v_Another. Var v_Temp. Var%TYPE; -- NUMBER (7, 3) 16

Процедуры и функции — это поименованные блоки, аналогичные процедурам и функциям в других языках программирования, они создаются командой CREATE и уничтожаются командой DROP. Синтаксис создания процедуры: CREATE [OR REPLACE] PROCEDURE [имя_ схемы. ]имя_процедуры [(имя_параметра [{IN | OUT | IN OUT}] тип_данных [, имя_параметра [{IN | OUT | IN OUT}] тип_данных. . . ])] {IS I AS} программа_на_PL/SQL 17

Процедуры и функции Значение параметра типа IN должно быть определено при обращении к программе и не изменяется программой. Попытка изменить в теле программы значение параметра вида IN приведет к ошибке. Значение параметра типа OUT при вызове процедуры не задается, оно определяется в теле процедуры, это возвращаемый параметр. Параметру типа IN OUT при вызове должно быть присвоено значение, которое может быть изменено в теле программы. 18

Процедуры и функции Ключевое слово OR REPLACE указывает на безусловное замещение старого текста процедуры. Если ключевое слово OR REPLACE не указано и процедура определена, то замещения старого значения кода процедуры не происходит и возвращается сообщение об ошибке. При описании переменных процедуры не используется ключевое слово DECLARE. Блок определения данных начинается сразу после ключевого слова AS (или IS, по выбору пользователя). 19

Процедуры и функции Пример. Создание хранимой процедуры. CREATE OR REPLACE PROCEDURE Add. New. Student ( p_First. Name students. first_name%TYPE, p_Last. Name students. last_name%TYPE, p_Major students. major%TYPE) AS BEGIN -- Вставить новую строку в таблицу students. Используем -- student_sequence для генерации нового ID, -- 0 для current_credits. 20

Процедуры и функции INSERT INTO students (ID, first_name, last_name, major, current_credits) VALUES (student_sequence. nextval, p_First. Name, p_Last. Name, p_Major, 0); END Add. New. Student; / BEGIN -- Вызов хранимой процедуры Add. New. Student('Zelda', 'Zudnik', 'Computer Science'); END; / ROLLBACK; 21

Хранимые функции Синтаксис, применяемый при создании хранимой функции, похож на синтаксис создания процедуры: CREATE [OR REPLACE] FUNCTION имя_функции [{аргумент [{IN | OUT |IN OUT}] тип, …, аргумент [{IN | OUT |IN OUT}] тип)] RETURN возвращаемый_тип {IS | AS} тело_функции Здесь аргумент и тип аналогичны аргументу и типу, указываемым при создании процедуры, возвращаемый_тип — это тип значения, возвращаемого функцией, а тело_функции — блок PL/SQL, содержащий код функции. Для тела функции применимы те же правила, что и для тела процедуры. 22

Хранимые функции Пример. Рассмотрим хранимую функцию, возвращающую TRUE, если учебная группа заполнена более чем на 80%, и FALSE в противном случае. CREATE OR REPLACE FUNCTION Almost. Full ( p_Department classes. department%TYPE, p_Course classes. course%TYPE) RETURN BOOLEAN IS v_Current. Students NUMBER; v_Max. Students NUMBER; v_Return. Value BOOLEAN; v_Full. Percent CONSTANT NUMBER : = 80; 23

Хранимые функции BEGIN -- Получим текущее и максимальное число студентов. SELECT current_students, max_students INTO v_Current. Students, v_Max. Students FROM classes WHERE department = p_Department AND course = p_Course; -- Если процент заполнения группы больше значения -- v_Full. Percent, возвращается TRUE, иначе FALSE. 24

Хранимые функции IF (v_Current. Students / v_Max. Students * 100) >= v_Full. Percent THEN v_Return. Value : = TRUE; ELSE v_Return. Value : = FALSE; END IF; RETURN v_Return. Value; END Almost. Full; Функция Almost Full возвращает логическое значение. Ниже приводится блок PL/SQL, в котором вызывается эта функция. Вызов функции не является оператором — представляет собой фрагмент условного оператора IF, расположенного внутри цикла. 25

Хранимые функции SQL> DECLARE 2 CURSOR c_Classes IS 3 SELECT department, course A FROM classes; 5 BEGIN 6 FOR v_Class. Reco rd IN c_Classes LOOP 7 -- Выведем информацию о группах, в которых 8 -- осталось мало места. 9 IF Almost. Full(v_Class. Record. department, 10 v_Class. Record. course) THEN 26

Хранимые функции 11 DBMS_OUTPUT. PUT_LINE( 12 v_Class. Record. department || ' ' || 13 v_Class. Record. course || ' is almost full! '); 14 END IF; 15 END LOOP; 16 END; 17 / MUS 410 is almost full! PL/SQL procedure successfully completed. 27

Триггеры Триггер — это процедура, которая запускается автоматически при возникновении событий, связанных с выполнением операций вставки, удаления или модификации данных таблицы. Событие, управляющее запуском триггера, описывается в виде логических условий. Триггер запускается при выполнении одной из трех операций изменения содержимого таблицы: INSERT, DELETE или UPDATE. Триггер может запускаться и несколькими операторами, но хотя бы один оператор из трех должен быть указан в условии запуска триггера. Триггеры позволяют обеспечить сложный контроль защиты данных или специальный аудит. 28

Триггеры Если триггер запускается при выполнении оператора UPDATE, то для условий срабатывания могут быть указаны конкретные изменяемые столбцы. Код триггера может выполняться либо до (с ключевым словом BEFORE), либо после (с ключевым словом AFTER) тех операторов, которые инициировали запуск триггера. Для управления триггерами предназначены операторы, которые могут переключать режим, разрешая или запрещая запуск триггера: — ALTER TRIGGER (изменяется режим указанного триггера) с опцией DISABLE или ENABLE и ALTER TABLE (переключается режим у всех триггеров, связанных с таблицей). 29

Триггеры Оператор определения триггера имеет формат: CREATE [OR REPLACE] TRIGGER имя_триггера {BEFORE | AFTER} [имя_схемы. ] {INSERT | DELETE | UPDATE [OF имя_столбца [ , имя_столбца. . . ]] } [OR {INSERT | DELETE | UPDATE [OF имя_столбца [ , имя_столбца. . . ]] }. . . ] ON [имя_схемы. ]{имя_таблицы имя_представления } [FOR EACH ROW] [WHEN условие ] программа_на_ PL/SQL 30

Триггеры Ключевое слово ON задает ассоциированной с триггером. имя таблицы, Необязательное ключевое слово FOR EACH ROW определяет триггер как строчный. Необязательное ключевое слово WHEN задает дополнительное логическое условие, сужающее область событий, при наступлении которых триггер запускается. 31

Триггеры - пример REM This is an example of a DML trigger. CREATE OR REPLACE TRIGGER Update. Major. Stats /* Keeps the major_stats table up-to-date with changes made to the students table. */ AFTER INSERT OR DELETE OR UPDATE ON students DECLARE CURSOR c_Statistics IS SELECT major, COUNT(*) total_students, SUM(current_credits) total_credits FROM students 32

Триггеры - пример GROUP BY major; BEGIN /* First delete from major_stats. This will clear the statistics, and is necessary to account for the deletion of all students in a given major. */ DELETE FROM major_stats; 33