Брешенков А.В., Галямова Е.В., Ефремов С.В. - Процедуры и функции в Oracle, Триггеры в Oracle (1075561), страница 2
Текст из файла (страница 2)
No bonus allowed.');END IF;END IF;EXCEPTION -- exception-handling part starts hereWHEN salary_missing THENDBMS_OUTPUT.PUT_LINE('Employee ' || emp_id ||' does not have a value for salary. No update.');WHEN OTHERS THENNULL; -- for other exceptions do nothingEND award_bonus;/-- the following BEGIN..END block calls, or executes, the award_bonus procedure-- using employee IDs 123 and 179 with the bonus rate 0.05 (5%)BEGINaward_bonus (123, 0.05);award_bonus (179, 0.05);END;/После вызова этих процедур появятся сообщения:Employee 123 received a bonus: 325Employee 179 receives a commission.
No bonus allowed.Данная процедура использует два параметра, - идентификатор служащего и размербонуса. Идентификатор используется для получения размера зарплаты и выясненияполучает ли данный служащий комиссию из таблицы служащие (emplyees). Если зарплатау служащего нулевая, вызывается исключение. Если служащий не получает комиссию, тоего зарплата автоматически изменяется с помощью бонуса, в других случаях все остается,как и было.6.
Создание функций с помощью SQL команды CREATE FUNCTION.Команда CREATE FUNCTION позволяет создавать функции, которые будутхраниться в базе данных. К таким хранимым (на схемном уровне) подпрограммаморганизован доступ из объекта «SQL». Вы можете использовать опцию OR REPLACE,чтобы изменить существующую функцию, не удаляя ее сначала.Пример №3 демонстрирует работу функции, которая возвращает объект строковоготипа, в котором первые буквы имени и фамилии заменяются на заглавные, а также вызовфункции.Пример №3. Функция last_first_name(empid NUMBER).CREATE OR REPLACE FUNCTION last_first_name (empid NUMBER)RETURN VARCHAR2 ISlastname employees.last_name%TYPE; -- declare a variable same as last_namefirstname employees.first_name%TYPE; -- declare a variable same as first_nameBEGINSELECT last_name, first_name INTO lastname, firstname FROM employeesWHERE employee_id = empid;RETURN ( 'Employee: ' || empid || ' - ' || UPPER(lastname)|| ', ' || UPPER(firstname) );END last_first_name;/-- you can use the following block to call the functionDECLAREempid NUMBER := 163; -- pick an employee ID to test the functionBEGIN-- display the output of the functionDBMS_OUTPUT.PUT_LINE( last_first_name(empid) );END;/-- you can also call a function from a SQL SELECT statement-- using the dummy DUAL tableSELECT last_first_name(163) FROM DUAL;Пример №4 демонстрирует работу функции, возвращающую объект числовоготипа.
Функция возвращает вычисленный уровень зарплаты служащих определеннойпрофессии, основанный на минимальной и максимальной зарплате и идентификаторепрофессии.Пример №4. Функция emp_sal_rankig(empid NUMBER).-- function calculates the salary ranking of the employee based on the current-- minimum and maximum salaries for employees in the same job categoryCREATE OR REPLACE FUNCTION emp_sal_ranking (empid NUMBER)RETURN NUMBER ISminsal employees.salary%TYPE; -- declare a variable same as salarymaxsal employees.salary%TYPE; -- declare a variable same as salaryjobid employees.job_id%TYPE; -- declare a variable same as job_idsal employees.salary%TYPE; -- declare a variable same as salaryBEGIN-- retrieve the jobid and salary for the specific employee IDSELECT job_id, salary INTO jobid, sal FROM employees WHERE employee_id = empid;-- retrieve the minimum and maximum salaries for employees with the same job IDSELECT MIN(salary), MAX(salary) INTO minsal, maxsal FROM employeesWHERE job_id = jobid;-- return the ranking as a decimal, based on the following calculationRETURN ((sal - minsal)/(maxsal - minsal));END emp_sal_ranking;/-- create a PL/SQL block to call the function, you can also use another subprogram-- because a function returns a value, it is called as part of a line of codeDECLAREempid NUMBER := 163; -- pick an employee ID to test the functionBEGIN-- display the output of the function, round to 2 decimal placesDBMS_OUTPUT.PUT_LINE('The salary ranking for employee ' || empid || ' is: '|| ROUND(emp_sal_ranking(empid),2) );END;/После вызова этой процедуры появится сообщение:The salary ranking for employee 163 is: .637.
Вызов хранимых процедур и функцийВы можете запускать процедуры и функции из BEGIN..END блока или из другихподпрограмм.Вызывая процедуру или функцию необходимо принимать во внимание следующиеособенности:Указание позиции: Вы указываете те же значения параметров и в той жепозиции, на которой они были заданы при создании процедуры. Обращайтевнимание именно на порядок, которым идут параметры.Указание наименования: Вы указываете название параметра и его значение,соединяете их знаком « => ».
В этом случае порядок не важен.Смешанные указания: Вы можете использовать оба способа в одном месте,но при этом необходимо сохранять порядок, с которым были заданыпараметры.Пример №5. Различные техники вызова процедур и функций.-- use a PL/SQL block to execute the procedureBEGINaward_bonus(179, 0.05);END;/-- using named notation for the parameters, rather than positionalBEGINaward_bonus(bonus_rate=>0.05, emp_id=>123);END;/Вы также можете вызывать процедуры или функции во время созданияприложений, Java ил PНР программ.8. Изменение процедур и функцийВы можете изменять процедуры и функции разными способами: на странице SQLкоманд, на странице просмотра объектов, на странице создания SQL скриптов или спомощью SQL команды CREATE OR REPLACE.Если Вы хотите изменить процедуру или функцию с помощью SQL командыCREATE OR REPLACE, то Вы вместо старого исходного кода создаете новый, и тогдапосле компиляции он изменится.Чтобы изменить процедуру или функцию с помощью страницы создания SQLскриптов, необходимо выполнить следующие действия:1.Войдите на страницу базы данных с помощью пароля, полученного отпреподавателя.2.На главной странице щелкните мышкой по иконке «SQL», чтобы отобразитьстраницу управления SQL.3.На странице SQL нажмите на иконку «SQL Commands», чтобы перейти наследующую страницу.4.Перейдите на закладку «Saved SQL».5.Выберите по имени процедуру или функцию, которую хотите изменить.6.Измените исходный код и нажмите «RUN», если хотите запустить наисполнение ее.7.Нажмите «SAVE», чтобы сохранить внесенные изменения.Изменение процедур или функций с помощью страницы просмотра объектов.1.Войдите на страницу базы данных с помощью пароля, полученного отпреподавателя.2.На главной странице щелкните мышкой по объекту «Object Browser», чтобыоткрылась страница просмотра объектов.3.Из выпадающего списка выберите процедуру или функцию, затем щелкните поимени процедуры, которую хотите изменить.4.Когда появилась информация о данном объекте, нажмите «Edit», чтобыизменить исходный код.5.Нажмите кнопку «Compile», чтобы убедиться в том, что измененный кодработает, и в случае появления ошибок их исправить.9.
Удаление процедур и функцийВы можете удалять процедуры и функции разными способами: на странице SQLкоманд, на странице просмотра объектов или с помощью SQL команды DROP.Чтобы удалить процедуру или функцию с помощью страницы просмотра объектовнеобходимо выполнить следующие действия:1.Войдите на страницу базы данных с помощью пароля, полученного отпреподавателя.2.На главной странице щелкните мышкой по объекту «Object Browser», чтобыоткрылась страница просмотра объектов.3.Из выпадающего списка выберите процедуру или функцию, затем щелкните поимени процедуры, которую хотите удалить.4.Когда появилась информация о данном объекте, нажмите «Drop».5.Нажмите кнопку «Finish», чтобы подтвердить данное действие.Пример использовании команды Drop показан ниже.Пример №6.
Удаление процедур и функций с помощью команды «DROP».-- drop the procedure award_bonus to remove from the databaseDROP PROCEDURE award_bonus;-- drop the function emp_sal_ranking to remove from databaseDROP FUNCTION emp_sal_ranking;10. ЗаключениеВ данной лабораторной работе Вы познакомились с существующими встроеннымипроцедурами и функциями, с алгоритмом создания, применения и удаления новыхобъектов и изменения встроенных процедур и функций.Надеемся, что полученные знания Вы сможете применить на практике присоздании собственных приложений под Oracle 10g X.E.11.
Контрольные вопросы.1.«Что такое процедура?», «Что такое функция?»2.«Какими средствами можно создать процедуру или функцию?»3.«Какие PL\SQL команды используются для создания процедур и функций и каков ихсинтаксис?»4.«Как можно посмотреть существующие процедуры или функции?»5.«Как изменить или удалить процедуры или функции?»12. Рекомендуемая литература.1. Гарсиа – Молина, Ульман, Уидом. Системы баз данных.
Полный Курс. Пер. сангл.- М.: Издательский дом Вильямс, 2003 – 1088 стр.2. Грабер М. Введение в SQL: Пер с англ. – М.:Изд-во’ЛОРИ’,1996. – 380с.3. 3. Джеймс Перри, Джеральд Пост. Введение в Oracle 10g “И.Д. Вильямс”, 2006. –700 с.4. Гринвальд Рик, Становьяк Робеот, Додж Гери, Кляйн Девид, Шапиро Бен, ЧельяКристофер Дж. Программирование баз данных Oracle для профессионалов.: Пер.
с англ.: –М. : ООО “И.Д. Вильямс”, 2007. – 784 с.5. Кайт Томас. Oracle для профессионалов: архитектура, методика программированияи основные особенности версии 9i и 10j.: Пер с англ. – М.:Издательский дом ”Вильямс”.2008. – 848 с.6. Кевин Луни, Боб Брила. Oracle Database 10g. Настольная книга администратора базданных.
– М.:Издательство’Лори’,2008. – 732 с.7. Райан стивенс, Рональд Плю. SQL. Пер с англ. – М.:ЗАО ’ИздательствоБином’,1998.-400 с.Методическое пособие по лабораторной работе №4Триггеры в Oracle1Введение.В данной лабораторной работе Вы познакомитесь с существующими встроеннымитриггерами, с алгоритмом создания, применения и удаления новых объектов и изменениявстроенных триггеров.Вы можете создавать, удалять и изменять триггеры разными способами: настранице SQL команд, на странице просмотра объектов, на странице создания SQLскриптов или с помощью командной строки SQL команд.SQL выражение CREATE используется для создания любых объектов в Oracle:CREATE TRIGGER позволит создать триггер.Триггеры баз данных это хранимые процедуры, связанные с таблицами, видами,или событиями.
Триггер может быть вызван однажды, когда происходит какое-тособытие, или много раз, когда происходит какое-либо изменение таблицы (вставка новойстроки, удаление или изменение существующей). Триггер может быть вызван послесобытия, чтобы записать новые данные или произвести необходимые после данногособытия действия. Триггер может быть вызван перед каким-либо событием, чтобыизбежать ошибочных действий или изменить данные так, чтобы они удовлетворялиправилам ведения бизнеса.
![](https://s.studizba.com/z.php?f=/templates/si/i/noavatar.png&w=100&h=100&t=1"){kind=avatar}
