II. RF KMF DokuWiki

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.

II. RF KMF DokuWiki

Felhasználói eszközök

Eszközök a webhelyen

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

Eszközök az oldalon