--
-- Описание для базы данных test1
--
DROP DATABASE IF EXISTS test_cursor;
CREATE DATABASE IF NOT EXISTS test_cursor
CHARACTER SET cp1251
COLLATE cp1251_general_ci;

-- 
-- Установка базы данных по умолчанию
--
USE test_cursor;

--
-- Описание для таблицы test1
--
CREATE TABLE IF NOT EXISTS test1 (
  IdTest1 int(10) UNSIGNED NOT NULL,
  val boolean,
  PRIMARY KEY (IdTest1)
)
ENGINE = INNODB
AVG_ROW_LENGTH = 37
CHARACTER SET cp1251
COLLATE cp1251_general_ci;

--
-- Описание для таблицы test2
--
CREATE TABLE IF NOT EXISTS test2 (
  IdTest2 int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  IdTest1 int(10) UNSIGNED DEFAULT NULL,
  PRIMARY KEY (IdTest2),
  CONSTRAINT FK_test2_test1_IdTest1 FOREIGN KEY (IdTest1)
  REFERENCES test1 (IdTest1) ON DELETE CASCADE ON UPDATE CASCADE
)
ENGINE = INNODB
AUTO_INCREMENT = 1501
AVG_ROW_LENGTH = 32
CHARACTER SET cp1251
COLLATE cp1251_general_ci;

DELIMITER $$

--
-- Описание для процедуры proc_fill_test1
--
CREATE DEFINER = 'root'@'%'
PROCEDURE proc_fill_test1 ()
BEGIN
  DECLARE i int;
  DELETE
    FROM test1;
  SET i = 1;
  WHILE i < 1000 DO
    INSERT INTO test1 (IdTest1)
      VALUES (i);
    SET i = i + 1;
  END WHILE;
END
$$

--
-- Описание для функции fn_fill_test2
--
CREATE DEFINER = 'root'@'%'
FUNCTION fn_fill_test2 (IN_Id int)
RETURNS boolean
BEGIN
  DECLARE result boolean;
  DECLARE tmp decimal(5,1);
  DELETE
    FROM test2
  WHERE IdTest1 = IN_Id;
  SET tmp = IN_Id/2;
  SET result = (tmp = ROUND(IN_Id/2));
  IF result THEN
    INSERT INTO test2 (IdTest1)
      VALUES (IN_Id);
  END IF;
  RETURN result;
END
$$

--
-- Описание для процедуры proc_fill_test2_with_cursor
--
CREATE DEFINER = 'root'@'%'
PROCEDURE proc_fill_test2_with_cursor ()
BEGIN
  DECLARE vId int;
  DECLARE Done tinyint(1) DEFAULT 0;
  DECLARE cur CURSOR FOR
  SELECT
    t.IdTest1
  FROM test1 t
  WHERE IdTest1 > 0;
  DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET Done = 1;
  SET Done = FALSE;
  OPEN cur;
  REPEAT
    FETCH cur INTO vid;
    SET @result = fn_fill_test2(vId);
  UNTIL Done
  END REPEAT;
  CLOSE cur;
END
$$

--
-- Описание для процедуры proc_fill_test2_without_cursor
--
CREATE DEFINER = 'root'@'%'
PROCEDURE proc_fill_test2_without_cursor ()
BEGIN
  UPDATE test1 t
  SET Val = fn_fill_test2(t.IdTest1);
END
$$

DELIMITER ;

CALL proc_fill_test1;
DROP PROCEDURE proc_fill_test1;