====7. Програмування в MySQL====

**Перевірка умов.**\\ 
** Конструкції 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\\ 

Функції та процедури можуть бути кодами, які зберігаються в БД (К_БД)\\ 
  *К_БД зберігаються на сервері. Тому їх виклик відбувається швидше (не потрібно передавати SQL-команди через мережу) 
  *	К_БД створюється один раз, і використовується багатократно. При програмуванні зменшується дублювання.
  *	Зростає безпека додатків: користовачу можна задати привілей на використання  К_БД і заборонити безпосередній доступ до БД.
  *	Спрощується супровід і розширення додатків.

**Функція:**\\ 
  *не може повертати набори даних;  (але можна TEMPORARY TABLE!)
  *не використовує COMMIT і ROLLBACK;
  *не використовує рекурсію;
  *повинна генерувати значення, яке вона повертає.

Для створення процедури і функції необхідно мати  прівілей 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.