MySQL存储过程

MySQL存储过程

— 变量声明、循环、条件分支、绑定变量

DELIMITER $$
CREATE PROCEDURE `myProcedure`()
BEGIN   
	#变量声明
    DECLARE var_int INT DEFAULT 0;
    DECLARE var_char VARCHAR(50) DEFAULT '';
	DECLARE tbl_name VARCHAR(50) DEFAULT '';
    #变量赋值
    SET @var_int = 1;
    SET @var_char = 'msg';
	SET @tbl_name = 'tbl';
    #while循环
    WHILE var_int < 10 DO
	  #条件表达式
      IF var_char = '' THEN
	    SET @tb_name = 'default_table_name';
      ELSE
	    SET @tb_name = 'actual_table_name';
      END IF;
      #生成SQL   
      SET @exe_sql = CONCAT('ALTER TABLE ', @tbl_name, " SOME EXPRESSION ");
	  #prepare statement
      PREPARE stmt FROM @exe_sql; 
	  #execute statement
      EXECUTE stmt;
      DEALLOCATE PREPARE stmt;
	  #循环++
      SET @var_int = @var_int + 1;
    END WHILE;
END$$
DELIMITER ;