Материал: Письменные лекции по дисциплине «Базы данных»
● Последняя часть CREATE PROCEDURE — это пара скобок содержит список параметров. Поскольку эта процедура не имеет никаких параметров, список пуст.
Следующая часть SELECT * FROM JOBS; $$ — это последний оператор в синтаксисе хранимых процедур MySQL. Точка с запятой здесь не является обязательной, так как реальным окончанием оператора является $$.
Вызов процедуры:
CALL имя_процедуры([параметр [, ...]])
CALL имя_процедуры[()]
MySQL хранимые процедуры, которые не принимают аргументов, могут вызываться без скобок. Поэтому CALL job_data() равносильно
CALL job_data.
Создание функции происходит следующим образом:
CREATE FUNCTION имя_функции ([параметр_функции [, ...]]) RETURNS тип [характеристика ...] тело_подпрограммы
Удаление процедуры/функции используется для сброса в MySQL
вызванной хранимой процедуры или функции.
DROP {PROCEDURE | FUNCTION} [IF EXISTS] имя_процедуры
3.2. Переменные
Переменные бывают пользовательскими (такая переменная создается в момент присвоения ей значения и хранится до завершения соединения с сервером; пишется со знаком @, например, @total), переменными хранимого кода (имеют ограниченную область видимости; пишется без знака @) и системными.
Пользовательская переменная:
SET @total = 5;
Переменная хранимого кода:
DECLARE total INT DEFAULT 5;
или
DECLARE total INT;
SET total = 5;
Системная переменная может быть глобальной или переменной сеанса.
Глобальная системная переменная:
SET GLOBAL total = 5;
или
SET @@global.total = 5;
Системная переменная сеанса:
SET total = 5;
или
SET SESSION total = 5;
или
SET @@total = 5;
или
SET @@local.total = 5;
В следующем примере показано использование пользовательских переменных внутри хранимой процедуры.
mysql> |
DELIMITER $$ |
mysql> |
CREATE PROCEDURE my_procedure_User_Variables() |
-> |
BEGIN |
-> |
SET @x = 15; |
-> |
SET @y = 10; |
-> |
SELECT @x, @y, @x - @y; |
-> |
END $$ |
Query OK, 0 rows affected (0.32 sec) mysql> DELIMITER ; $$
Теперь выполним процедуру:
mysql> CALL my_procedure_User_Variables();
+------ |
+------ |
+--------- |
+ |
| @x |
| @y |
| @x - @y | |
|
+------ |
+------ |
+--------- |
+ |
| |
15 | |
10 | |
5 |
| |
|
+ |
------ |
+ |
------+ |
--------- |
+ |
1 |
row |
in set (0.01 sec) |
|
||
Query |
OK, 0 rows affected (0.01 sec) |
||||
3.3. Параметры процедуры
Синтаксис процедуры:
CREATE [DEFINER = { пользователь | CURRENT_USER }] PROCEDURE имя_процедуры ([параметр_процедуры[,...]]) [характеристики ...] тело_подпрограммы
параметр_процедуры: [ IN | OUT | INOUT ] имя_парамета type
Варианты синтаксиса:
CREATE PROCEDURE имя_процедуры () …
CREATE PROCEDURE имя_процедуры ([IN] имя_параметра type)… CREATE PROCEDURE имя_процедуры ([OUT] имя_параметра type)… CREATE PROCEDURE имя_процедуры ([INOUT] имя_параметра type)…
В первом примере список параметров пуст.
Во втором примере параметр IN передает значение в процедуру. Эта процедура может изменить значение. Но, когда процедура возвращает значение, оно не будет видно для вызывающего агента.
Втретьем примере параметр OUT передает значение из процедуры обратно вызывающему агенту. Его начальное значение в процедуре NULL, и, когда процедура возвращает значение, оно видно вызывающему агенту.
Вчетвертом примере параметр INOUT инициализируется вызывающим агентом, он может быть изменен процедурой, и когда процедура возвращает значение, любые изменения, произведенные MySQL хранимой процедурой, будут видны вызывающему агенту.
Впроцедуре каждый параметр по умолчанию является параметром IN. Чтобы изменить это, используйте перед именем параметра ключевое
слово OUT или INOUT.
Пример параметра IN.
mysql> CREATE PROCEDURE my_proc_IN (IN var1 INT)
-> BEGIN
-> SELECT * FROM jobs LIMIT var1;
-> END$$
Query OK, 0 rows affected (0.00 sec)
LIMIT используется для ограничения количества возвращаемых записей таблицы jobs на основании предельного значения var1.
Чтобы выбрать первые две строки из таблицы jobs выполним следующую команду:
mysql> CALL |
my_proc_in(2)$$ |
|
|
|
|
|
||
+--------- |
|
+------------------------------- |
|
+------------ |
|
+------------ |
|
+ |
| JOB_ID |
| |
JOB_TITLE |
| MIN_SALARY |
| MAX_SALARY | |
||||
+--------- |
|
+------------------------------- |
|
+------------ |
|
+------------ |
|
+ |
| AD_PRES |
| |
President |
| |
20000 |
| |
40000 |
| |
|
| AD_VP |
| |
Administration Vice President | |
15000 |
| |
30000 |
| |
||
+--------- |
|
+------------------------------- |
|
+------------ |
|
+------------ |
|
+ |
2 |
rows in |
set (0.00 sec)Query OK, 0 rows affected (0.03 sec) |
|
|
||||
Пример параметра OUT.
Дальше представлен MySQL хранимой процедуры пример, в котором используется параметр OUT. В рамках процедуры MySQL функция MAX() извлекает максимальную зарплату из столбца MAX_SALARY
таблицы jobs:
mysql> CREATE PROCEDURE my_proc_OUT (OUT highest_salary INT) -> BEGIN
-> SELECT MAX(MAX_SALARY) INTO highest_salary FROM JOBS; -> END$$
Query OK, 0 rows affected (0.00 sec)
В теле процедуры параметр получает самую высокую зарплату из столбца MAX_SALARY. После вызова процедуры слово OUT сообщает СУБД, что значение исходит от процедуры. highest_salary — это имя выходного параметра и в операторе CALL мы передали его значение переменной сеанса с именем @M:
mysql> CALL my_proc_OUT(@M)$$
Query OK, 1 row affected (0.03 sec)
mysql> SELECT @M$$+------- |
+ |
|
| @M |
| |
|
+------- |
+ |
|
| 40000 | |
|
|
+------- |
+ |
|
1 row in set (0.00 sec) |
|
|
Пример параметра INOUT.
В следующем примере показана простая хранимая процедура MySQL, которая использует параметр INOUT и параметр IN. Пользователь предоставляет 'M' или 'F' через параметр IN (emp_gender) для подсчета количества сотрудников мужского или женского пола из таблицы user_details. Параметр INOUT (mfgender) возвращает результат пользователю. Вот код и результат выполнения процедуры:
mysql> CALL my_proc_OUT(@M)$$ |
|
|
Query OK, 1 row affected (0.03 sec) |
|
|
mysql> CREATE PROCEDURE my_proc_INOUT |
(INOUT mfgender |
|
INT, IN emp_gender CHAR(1)) |
|
|
|
-> BEGIN |
|
|
-> SELECT COUNT(gender) INTO |
mfgender FROM |
user_details WHERE gender = emp_gender; |
|
|
|
-> END$$ |
|
Query OK, 0 rows affected (0.00 sec) |
|
|
Теперь проверяем количество сотрудников мужского и женского |
||
пола в указанной таблице: |
|
|
mysql> CALL my_proc_INOUT(@C,'M')$$ |
|
|
Query OK, 1 row affected (0.02 sec) |
|
|
mysql> SELECT @C$$ |
|
|
+------+ |
|
|
| @C |
| |
|
+------+ |
|
|
| |
3 | |
|
+------+ |
|
|