Создание хранимых функций

1. Создание хранимых функций

2. Рассматриваемые вопросы

10 - 2
Использование функций
Создание хранимых функций
Вызов функции
Удаление функции
Разница между процедурой и функцией

3. Обзор хранимых функций

• Функция – именованный блок PL/SQL,
возвращающий значение.
• Хранится как объект схемы в базе данных для
повторного выполнения.
• Функция используется в выражении или
вызывается для предоставления значения
параметра.
10 - 3

4. Создание функции: синтаксис

Блок PL/SQL должен содержать, по крайней мере, один
оператор RETURN.
CREATE [OR REPLACE] FUNCTION имя_функции
[(параметр1 [режим1] тип_данных1, ...)]
RETURN тип_данных IS|AS
[объявления_локальных_переменных; …]
BEGIN
-- действия;
RETURN выражение;
END [имя_функции];
10 - 4
Блок PL/SQL

5. Отличия между процедурами и функциями

Процедура
Функция
Выполняется как
команда PL/SQL
Вызывается как часть
выражения
Нет предложения
RETURN в заголовке
Заголовок должен содержать
предложение RETURN
Может возвращать одно
или несколько значений
Должна возвращать одно
значение
Может содержать
оператор RETURN
Должна содержать, по крайней
мере, один оператор RETURN
10 - 5

6. Разработка функций

Просмотр ошибок и
предупреждений
в SQL Developer
YES
Команда SHOW ERRORS
в SQL*Plus
создание/редактирование
функции
Имеются ли ошибки и Просмотр ошибок и
предупреждения
предупреждений
компилятора?
компилятора
NO
Представления
USER/ALL/DBA_ERRORS
Вызов (использование) функции
10 - 6

7. Пример хранимой функции

• Создание функции:
CREATE OR REPLACE FUNCTION get_sal
(id employees.employee_id%TYPE) RETURN NUMBER IS
sal employees.salary%TYPE := 0;
BEGIN
SELECT salary
INTO
sal
FROM
employees
WHERE employee_id = id;
RETURN sal;
END get_sal;
/
• Вызов функции из выражения или при передаче
значения параметра:
EXECUTE dbms_output.put_line(get_sal(100))
10 - 7

8. Способы вызова функций

-- Вызов из выражения PL/SQL, использование хост-переменной
-- для получения результата
VARIABLE b_salary NUMBER
EXECUTE :b_salary := get_sal(100)
-- Использование локальной переменной для получения результата
DECLARE
sal employees.salary%type;
BEGIN
sal := get_sal(100);
DBMS_OUTPUT.PUT_LINE('The salary is: '|| sal);
END;
/
10 - 8

9. Способы вызова функций

-- Вызов при передаче параметра другой подпрограмме
EXECUTE dbms_output.put_line(get_sal(100))
-- В команде SQL (с некоторыми ограничениями)
SELECT job_id, get_sal(employee_id) FROM employees;
...
10 - 9

10. Создание и компиляцияфункций в SQL Developer

1
3
2
4
10 - 10

11. Вызов функций в SQL Developer

2
1
`
Замените ID на
`
10 - 11
фактическое значение
3

12. Преимущества применения пользовательских функций в командах SQL

• Расширяют возможности SQL, когда обработка
слишком сложна, неудобна или не может быть
выполнена стандартными средствами SQL.
• С помощью пользовательских функций в
предложении WHERE можно эффективно
производить фильтрацию данных и не делать этого в
коде приложения.
• Манипулирование данными можно осуществлять
путем применения пользовательских функций.
10 - 12

13. Пример вызова функций в SQL-выражениях

CREATE OR REPLACE FUNCTION tax(p_value IN NUMBER)
RETURN NUMBER IS
BEGIN
RETURN (p_value * 0.08);
END tax;
/
SELECT employee_id, last_name, salary, tax(salary)
FROM
employees
WHERE department_id = 100;
10 - 13

14. Вызов пользовательских функций

Пользовательские функции выполняются как встроенные
однострочные функции и могут быть использованы в
следующих конструкциях:
• Список выборки команды SELECT.
• Условие в предложениях WHERE и HAVING.
• Предложения CONNECT BY, START WITH, ORDER BY
и GROUP BY в запросе.
• Предложение VALUES в команде INSERT.
• Предложение SET в команде UPDATE.
10 - 14

15. Ограничения на вызов функций из SQL-выражений

• Функция, вызываемая из SQL-выражения, должна:
– храниться в базе данных
– принимать параметры только в режиме IN с
допустимыми типами данных SQL (но не с типами,
присущими только PL/SQL)
– возвращать тип данных, который допускается в SQL
и не является специальным типом PL/SQL
• При вызове функций в командах SQL:
– параметры должны задаваться позиционно
– необходимо быть владельцем функции или иметь
привилегию EXECUTE
10 - 15

16. Контроль побочных эффектов при вызове функций из выражений SQL

• Функции, вызываемые из команды SELECT, не могут
содержать команды DML
• Функции, вызываемые из команды UPDATE или
DELETE, выполняемой над таблицей T, не могут
содержать запрос или DML-операции над той же самой
таблицей T
• Функции, вызываемые из команд SQL, не могут
завершать транзакции (то есть не могут выполнять
операции COMMIT или ROLLBACK)
Примечание: в функции недопустимы вызовы
подпрограмм, нарушающих одно из этих ограничений.
10 - 16

17. Ограничения на вызов функций из SQL: пример

CREATE OR REPLACE FUNCTION dml_call_sql(p_sal NUMBER)
RETURN NUMBER IS
BEGIN
INSERT INTO employees(employee_id, last_name,
email, hire_date, job_id, salary)
VALUES(1, 'Frost', '[email protected]',
SYSDATE, 'SA_MAN', p_sal);
RETURN (p_sal + 100);
END;
UPDATE employees
SET salary = dml_call_sql(2000)
WHERE employee_id = 170;
10 - 17

18. Использование метода передачи параметров по имени и комбинированного метода в SQL

• PL/SQL позволяет передавать значения в
подпрограммы используя позиционный,
комбинированный, или метод передачи по имени
параметра
• До версии Oracle Database 11g, только позиционный
метод поддерживался в SQL
• Начиная с Oracle Database 11g, комбинированный и
метод передачи по имени параметра могут также
использоваться при вызове подпрограмм PL/SQL из
команд SQL
• Для подпрограмм, принимающих большой список
параметров, можно не передавать значения
параметров, имеющих значения по умолчанию
10 - 18

19. Использование метода передачи параметров по имени и комбинированного метода в SQL : Пример

CREATE OR REPLACE FUNCTION f(
p_parameter_1 IN NUMBER DEFAULT 1,
p_parameter_5 IN NUMBER DEFAULT 5)
RETURN NUMBER
IS
v_var number;
BEGIN
v_var := p_parameter_1 + (p_parameter_5 * 2);
RETURN v_var;
END f;
/
FUNCTION f( Compiled.
SELECT f(p_parameter_5 => 10) FROM DUAL;
F(p_parameter_5 =>10)
---------------------21
10 - 19

20. Удаление функций: использование команды SQL DROP или SQL Developer

• Использование команды DROP:
DROP FUNCTION f;
• Использование SQL Developer:
2
1
10 - 20
3

21. Просмотр информации о функциях в словаре данных

DESCRIBE USER_SOURCE
SELECT
FROM
WHERE
ORDER
text
user_source
type = 'FUNCTION‘ AND name = ‘MYFUNCTION’
BY line;
...
10 - 21

22. Итоги

• Создание функции по команде SQL CREATE
FUNCTION для подсчета и возврата значения
• Вызов функции в выражении PL/SQL
• Использование хранимых функций PL/SQL в
командах SQL
• Удаление функции из базы данных
10 - 22

23. Обзор практического занятия 10

• Создание хранимых функций
– Для выполнения запроса к таблице базы данных и
возврата отдельных значений
– Для использования в команде SQL
– Для вставки в таблицу базы данных новой строки со
значениями, заданными в параметрах
– Использование значений параметров по умолчанию
• Вызов хранимой функции из команды SQL
• Вызов хранимой функции из хранимой процедуры
10 - 23