Перевірка умов.
Конструкції IF
A). IF [val_1] THEN [Result_1]; END IF; B). IF [val_1] THEN [Result_1]; ELSE [Result_2]; END IF; C). IF [val_1] THEN [Result_1]; ELSEIF [val_2] THEN [Result_2] ; ELSEIF [val_3] THEN [Result_3] ; ELSEIF [val_N] THEN [Result_N] ; ELSE [default_result]; END IF;
Приклад
DELIMITER // CREATE FUNCTION what_is_today() RETURN VARCHAR(255) BEGIN DECLARE message VARCHAR(255); IF DAYOFWEEK(NOW()) BETWEEN 1 AND 5 THEN SET message = ‘робочий день’; ELSE SET message = ‘вихідний’; END IF; RETURN message; END// DELIMITER ; SELECT what_is_today();
Приклад
DELIMITER // CREATE PROCEDURE add_dept (IN num INT, IN name VARCHAR(255), IN locat VARCHAR(255), OUT return_val INT) ; BEGIN DECLARE coun, lastid INT; SELECT COUNT(*) INTO coun FROM dept WHERE dname = name; IF coun = 0 THEN SELECT MAX(deptno) INTO lastid FROM dept; INSERT INTO dept(deptno, dname, loc) VALUES((lastid+1), name, locat); SET return_val =1; ELSE SET return_val =0; END IF; SELECT return_val ; END // DELIMITER ;
Оператор CASE
1). CASE x WHEN a1 THEN b1 [ WHEN a2 THEN b2] ... [WHEN an THEN bn] [ELSE b0] END CASE;
якщо х = ai, то оператор повертає значення bi; якщо значення х не співпало ні з одним ai, то оператор повертає значення b0 (ELSE), якщо параметр ELSE не заданий, то оператор повертає NULL.
2). CASE WHEN x1 THEN b1 [WHEN x2 THEN b2] ... [WHEN xn THEN bn] [ELSE b0] END CASE;
Послідовно перевіряється істинність логічних виразів хi: якщо хi істинний, то оператор повертає значення bi; інакше b0 (або NULL).
Приклад
DELIMITER // CREATE FUNCTION todays() RETURN VARCHAR(255) BEGIN DECLARE message VARCHAR(255); CASE DAYOFWEEK( NOW() ) WHEN 1 THEN SET message = ‘понеділок’; WHEN 2 THEN SET message = ‘вівторок’; … ELSE SET message = ‘неділя’; END CASE; RETURN message; END// DELIMITER ; SELECT todays();
Цикли. Конструкція LOOP
DELIMITER // CREATE FUNCTION factorial(num INT UNSIGNED) RETURN INT BEGIN DECLARE res INT DEFAULT 1; IF num = 0 THEN RETURN 0; ENF IF; Fact: LOOP IF num>0 THEN SET res = res*num; SET num = num – 1; ELSE LEAVE Fact; END IF; END LOOP Fact; RETURN res; END// DELIMITER ; SELECT factorial ( 5 );
Цикли. Конструкція WHILE
DELIMITER // CREATE FUNCTION factorial (num INT UNSIGNED) RETURN INT BEGIN DECLARE res INT DEFAULT 1; IF num = 0 THEN RETURN 0; ENF IF; Fact: WHILE num>0 DO SET res = res*num; SET num = num – 1; END WHILE Fact; RETURN res; END// DELIMITER ; SELECT factorial ( 5 );
Цикли. Конструкція REPEAT
DELIMITER // CREATE FUNCTION factorial (num INT UNSIGNED) RETURN INT BEGIN DECLARE res INT DEFAULT 1; IF num = 0 THEN RETURN 0; NF IF; Fact: REPEAT SET res = res*num; SET num = num – 1; UNTIL num <= 0 END REPEAT Fact; RETURN res; END// DELIMITER ; SELECT factorial ( 5 );
Команди LEAVE і ITERATE (в циклах)
DELIMITER // CREATE PROCEDURE f ( ) BEGIN DECLARE k INT DEFAULT 1; h: WHILE k <= 5 DO IF k = 3 THEN LEAVE g; END IF; SELECT k; SET k = k + 1; ITERATE h; END WHILE h; g: END// DELIMITER ;
Функції та процедури користувача
Cтворення процедури CREATE PROCEDURE
Створення функції CREATE FUNCTION
Функції та процедури можуть бути кодами, які зберігаються в БД (К_БД)
Функція:
Для створення процедури і функції необхідно мати прівілей CREATE ROUTINE.
CREATE [DEFINER = user] PROCEDURE p_name ([параметр_пр[ ,... ]]) тіло_процедури;
параметр_пр: [ IN | OUT | INOUT ] ім'я тип
CREATE [DEFINER = user] FUNCTION f_name ([[параметр_ф [,...]]) RETURNS type тіло_функції
параметр_ф: ім'я тип.
Кожен параметр функції є IN параметром за замовчуванням. Вказувати параметри, як IN, OUT або INOUTдопустимо лише для PROCEDURE. Для FUNCTION параметри завжди розглядаються, як IN параметри.
Список параметрів, взятий у круглі дужки, повинен бути присутнім завжди. Якщо параметрів немає, слід використовувати порожній список ().
Імена параметрів не чутливі до регістру.
Оператор USE всередині процедур не допускаються.
MySQL зберігає параметр sql_mode системной змінної, що діє при створенні підпрограми, і виконує її з цим параметром, незалежно від поточного режиму SQL сервера.
Процедури
Основні компоненти: вхідні параметри (аргументи), вихідні параметри (результат), тіло процедури (SQL-оператори).
Створення процедури:
DELIMITER // CREATE PROCEDURE Pr () -- процедура без параметрів BEGIN SELECT COUNT(*) FROM Orders ; -- Звідки знає про Orders? END // DELIMITER ;
CALL Pr(); - виклик процедури. Користувач, який викликає підпрограму, повинен мати привілей EXECUTE.
SHOW CREATE PROCEDURE Pr; -- перегляд тіла процедури SHOW PROCEDURE STATUS; -- список процедур на сервері DROP PROCEDURE процедура;
Приклади
delimiter // CREATE PROCEDURE simpleproc (OUT param1 INT) BEGIN SELECT COUNT (*) INTO param1 FROM t; END // Delimiter ; CALL simpleproc (@a); SELECT @a; -- @a = <кількість рядків в таблиці t>
CREATE FUNCTION hello (s CHAR (20)) RETURNS CHAR (50) BEGIN RETURN CONCAT ( 'Hello,', s, '!'); END; SELECT hello ( 'world'); Hello, world!
Параметри IN, OUT в процедурах
DELIMITER // CREATE PROCEDURE Get_Name_2 (IN num INT, OUT o_name VARCHAR(30 ) ) BEGIN SELECT name INTO o_name FROM Costumers WHERE id = num; END // DELIMITER ; CALL Get_Name_2 (5, @v); SELECT @v; -- @v – глобальна змінна (змінна сеансу)
Параметри INOUT
DELIMITER // CREATE PROCEDURE add_one (INOUT num INT ) BEGIN SELECT (num+1) INTO num; END // DELIMITER ; SET @d = 5; -- @d – змінна сеансу (глобальна змінна) CALL add_one( @a); -- результат @a = 6.
Функції
DELIMITER // CREATE FUNCTION today ( ) -- функція без параметрів RETURNS VARCHAR(255) BEGIN RETURN DATE_FORMAT (NOW(), ‘%D %M %Y’); END // DELIMITER ; SELECT today ( ); -- варіант використання (виклику) функції *SHOW CREATE FUNCTION today ( ); -- перегляд тіла функції *SHOW FUNCTION STATUS; -- список функцій на сервері *DROP FUNCTION today ( );
Параметри функції (вхідні)
DELIMITER // CREATE FUNCTION add_dept (код INT, назва VARCHAR(15), місто VARCHAR(20) ) RETURNS INT BEGIN INSERT INTO dept VALUES (код, назва, місто); RETURN 1; END // DELIMITER ; SELECT add_dept (50, ‘Рога и копыта’, ’Ужгород’); SELECT dname, ‘місто:’, loc FROM dept WHERE deptno = 50; Результат – Рога и копыта місто: Ужгород
Використання функцій
DELIMITER // CREATE FUNCTION get_circle_area (radius INT) RETURNS FLOAT BEGIN RETURN PI()*radius*radius; END // DELIMITER ; SELECT get_circle_area ( 15 );
DELIMITER // CREATE FUNCTION get_circle_area ( ) RETURNS INT BEGIN SET @area = PI()*@radius*@radius; RETURN NULL; END // DELIMITER ; SET @radius = 2; SELECT get_circle_area ( ); SELECT @area;
Локальні змінні кодів
DELIMITER // CREATE PROCEDURE add_2 (INOUT num INT ) BEGIN DECLARE count INT DEFAULT 10; SELECT (num+count) INTO num; END // DELIMITER ; CALL add_2( 3); Результат = 13.