Брешенков А.В., Галямова Е.В., Ефремов С.В. - Процедуры и функции в Oracle, Триггеры в Oracle

Московский Государственный Технический Университет им. Н.Э. БауманаФакультет «Информатика и системы управления»Кафедра «Компьютерные системы и сети»А.В. Брешенков, Е.В. Галямова, С.В. ЕфремовМетодическое пособие по лабораторной работе №3Процедуры и функции в OracleМетодическое пособие по лабораторной работе №4Триггеры в OracleМосква 2010ОГЛАВЛЕНИЕМетодическое пособие по лабораторной работе №3Процедуры и функции в Oracle………………………………………………………….1.Введение……………………………………………………………………………32.Создание процедур и функций с помощью страницы SQL команд…………….43.Создание процедур и функций с помощью страницы просмотра объектов……54.Просмотр процедур и функций с помощью страницы просмотра объектов…...65.Создание процедуры с помощью SQL команды CREATE PROCEDURE……...76.Создание функций с помощью SQL команды CREATE FUNCTION…………..97.Вызов хранимых процедур и функций………………………………………..…..108.Изменение процедур и функций…………………………………………………..119.Удаление процедур и функций……………………………………………………1210.

Заключение………………………………………………………………………….1311. Контрольные вопросы……………………………………………………..………1312. Рекомендуемая литература………………………………………………………...13Методическое пособие по лабораторной работе №4Триггеры в Oracle…………………………………………………………………………141Введение…………………………………………………………………………...142Обзор триггеров…………………………………………………………………...142.1Типы триггеров……………………………………………………………..142.2Именование триггеров……………………………………………………..152.3В каких случаях вызывается триггер?…………………………………….152.4Моменты запуска триггера………………………………………………...152.4.1 Запуск триггера с помощью опций BEFORE и AFTER………...162.4.2 Запуск триггера с помощью опции FOR EACH ROW………….162.4.3 Запуск триггера с помощью задания условий…………………..162.4.4 Запуск триггера с помощью опции INSTEAD OF………………1732.5Доступ к колонкам таблиц в строковых триггерах………………………172.6Определение DML операции, запускающей триггер…………………….172.7Включение и выключение триггера………………………………………18Создания триггеров…………………..…………………………………………..183.1Рекомендации по созданию триггеров………………………………..….183.2Ограничения по созданию триггеров…………………………………….193.2.1 SQL выражения, приемлемые при создании триггеров……….193.2.2 Ограничение на системные триггеры…………………………..193.2.3 Привилегии, которые нужны для работы с триггерами………19Управление триггерами…………………………………………………….2044.1Создание триггеров с помощью страницы SQL команд……………….204.2Создание триггеров с помощью страницы просмотра объектов……...214.3Просмотр триггеров с помощью страницы просмотра объектов……..224.4Создание триггеров с опциями «AFTER» и «FOR EACH ROW»……..234.5Создание триггеров с опциями «BEFORE» и условием «WHEN»……244.6Создание триггеров с опцией «INSTEAD OF»…………………………244.7Создание триггера с перехватчиком ошибок…………………………...254.8Создание триггера, запускающегося один раз при каждом изменении4.9Создание «LOGON» и «LOGOFF» триггера……………………………274.10 Изменение триггеров……………………………………………………..284.11 Удаление триггеров………………………………………………………284.12 Включение и отключение триггеров……………………………………29Компиляция триггеров…………………………………………………….2955.1Ошибки при создании триггеров………………………………………..295.2Зависимости триггеров…………………………………………………..295.3Перекомпиляция триггеров………………………………………….…..306Заключение…………………………………………………………………307Контрольные вопросы……………………………………………….……308Рекомендуемая литература…………………………………...…………...311.

Введение.В данной лабораторной работе Вы познакомитесь с существующими встроеннымипроцедурами и функциями, с алгоритмом создания, применения и удаления новыхобъектов и изменения встроенных процедур и функций.Вы можете создавать, запускать, удалять и изменять процедуры и функцииразными способами: на странице SQL команд, на странице просмотра объектов, настранице создания SQL скриптов или с помощью командной строки SQL команд.SQL выражение CREATE используется для создания любых объектов в Oracle:CREATE PROCEDURE позволит создать процедуру, CREATE FUNCTION – функцию.Процедуры и функции похожи на маленькие программы, они состоят издекларативной (описательной) части, части выполнения или перехвата исключений.Процедура – это подпрограмма, которая выполняет какое-то конкретное действие. Выуказываете ее имя, параметры, переменные, создаете блок выполнения (BEGIN – END),который содержит основной код и перехватывает какие-либо исключения.

Функция – этоподпрограмма, которая считает и выдает искомое значение. Процедуры и функции оченьпохожи, за исключением того, что функции кроме выполнения действий возвращаютзначение.2. Создание процедур и функций с помощью страницы SQL команд.Вы можете создать объекты данного типа с помощью страницы SQL команд, дляэтого вам необходимо выполнить следующие действия:1.Войдите на страницу базы данных с помощью пароля, полученного отпреподавателя.2.На главной странице щелкните мышкой по иконке «SQL», чтобыотобразить страницу управления SQL.3.На странице SQL нажмите на иконку «SQL Commands», чтобы перейтина следующую страницу.4.Введите в появившемся окне PL/SQL код, для создания PL/SQLпроцедуры или функции.

Можете воспользоваться примером процедуры,отображенной на рисунке №1.5.Выберите (выделите) код создания процедуры или функции и нажмитекнопку «RUN».Рисунок 1. Создание процедуры today_is.6.Выберите (выделите) код создания процедуры или функции и нажмитекнопку «RUN».Рисунок 2. Выполнение процедуры today_is.7.Если Вы хотите сохранить PL/SQL код для будущего использования,нажмите кнопку «SAVE».8.В поле «NAME» введите название Вашего кода. Вы можете также ввестидополнительные опции. Нажмите кнопку «SAVE», чтобы сохранить Вашкод.9.Для доступа к сохраненным процедурам и функциям нажмите назакладку «Saved SQL» и выберите необходимую Вам процедуру илифункцию.3. Создание процедур и функций с помощью страницы просмотраобъектов.Вы можете создать объекты данного типа с помощью страницы просмотраобъектов, для этого вам необходимо выполнить следующие действия:1.Войдите на страницу базы данных с помощью пароля, полученного отпреподавателя.2.На главной странице щелкните мышкой по треугольнику справа от кнопки«Object Browser», чтобы появился выпадающий список.3.Выберите из списка пункт «Create» --} «Procedure».4.Введите имя процедуры «award_bonus», проверьте стоит ли галочка напротив«Include Arguments» и нажмите кнопку «Next».5.Введите информацию об аргументах и нажмите «Next».

В качестве примераможете взять ее из рассмотренной ниже процедуры Example.NameIn/OuttypeEmp_idINNumberBonus_rateINNumberТаблица 1. Процедуры и аргументы.6.Введите исходный код в «Procedure body» и нажмите «Next». В качествепримера можете взять ее из рассмотренной в примере №2 процедуры .Рисунок 3. Исходный код процедуры «Award_Bonus».7.Выберите вкладку «SQL», чтобы посмотреть исходный код.Нажмите«Prevoius», если вы хотите внести в него изменения.8.Если больше не нужно вносить никаких изменений, нажмите кнопку «Finish».Нажмите кнопку «Edit», чтобы добавить или удалить подпрограммы ипеременные снаружи блока BEGIN..END.9.Нажмите кнопку «Compile», чтобы скомпилировать текст. Если появятсяошибки, исправьте исходный код и нажмите еще раз «Compile».

После каждойкомпиляции сохраняются все внесенные изменения.10.Нажмите кнопку «Finish», чтобы закончить процесс создания процедуры.4. Просмотр процедур и функций с помощью страницы просмотраобъектов.Для того, чтобы посмотреть, какие процедуры и функции существуют в Вашей базеиспользуете страницу просмотра объектов «Object Browser».1.Войдите на страницу базы данных с помощью пароля, полученного отпреподавателя.2.На главной странице щелкните мышкой по объекту «Object Browser», чтобыоткрылась страница просмотра объектов.3.Из выпадающего списка выберите процедуру или функцию, затем щелкните поимени процедуры, которую создали в предыдущем пункте («Award_bonus»).Появится информация о выбранной процедуре или функции.Рисунок 4. Информация о процедуре Award_bonus.5.

Создание процедуры с помощью SQL команды CREATE PROCEDURE.Команда CREATE PROCEDURE позволяет создавать процедуры, которые будутхраниться в базе данных. К таким хранимым (на схемном уровне) подпрограммаморганизован доступ из объекта «SQL». Вы можете использовать опцию OR REPLACE,чтобы изменить существующую процедуру, не удаляя ее сначала.Пример №1. Хранимая процедура «today_is», возвращающая текущую дату.CREATE OR REPLACE PROCEDURE today_is ASBEGIN-- display the current system date in long formatDBMS_OUTPUT.PUT_LINE( 'Today is ' || TO_CHAR(SYSDATE, 'DL') );END today_is;/-- to call the procedure today_is, you can use the following blockBEGINtoday_is(); -- the parentheses are optional hereEND;/Когда Вы создаете процедуру или функцию, то можете указать, какие параметрыбудут переданы ей или получены из нее.

В следующем примере будет использоватьсяопция IN для передачи параметров в процедуры emp_id() и bonus_rate().Пример №2. Хранимые процедуры с параметрами.-- including OR REPLACE is more convenient when updating a subprogram-- IN is the default for parameter declarations so it could be omittedCREATE OR REPLACEPROCEDUREaward_bonus(emp_id IN NUMBER, bonus_rate IN NUMBER)AS-- declare variables to hold values from table columns, use %TYPE attributeemp_commemployees.commission_pct%TYPE;emp_salemployees.salary%TYPE;-- declare an exception to catch when the salary is NULLsalary_missing EXCEPTION;BEGIN -- executable part starts here-- select the column values into the local variablesSELECT salary, commission_pct INTO emp_sal, emp_comm FROM employeesWHERE employee_id = emp_id;-- check whether the salary for the employee is null, if so, raise an exceptionIF emp_sal IS NULL THENRAISE salary_missing;ELSEIF emp_comm IS NULL THEN-- if this is not a commissioned employee, increase the salary by the bonus rate-- for this example, do not make the actual update to the salary-- UPDATE employees SET salary = salary + salary * bonus_rate-- WHERE employee_id = emp_id;DBMS_OUTPUT.PUT_LINE('Employee ' || emp_id || ' receives a bonus: '|| TO_CHAR(emp_sal * bonus_rate) );ELSEDBMS_OUTPUT.PUT_LINE('Employee ' || emp_id|| ' receives a commission.