mysql创建存储过程

创建：(如果不加 delimiter //  有多少个declare就会报多少个You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 3，原因请查询官方文档)

mysql> delimiter //

mysql> create procedure pr_a(a int, b int)
    -> begin
    -> declare c int;
    -> set c=a+b;
    -> select c as num;

    -> end //

调用：

call pr_a(2,3);
+------+
| num  |
+------+
|    5 |
+------+

example：

drop procedure if exists create_comment;
DELIMITER $$
CREATE
  PROCEDURE create_comment()
  BEGIN


     DECLARE i INT;
     DECLARE table_name VARCHAR(20); 
     DECLARE table_pre VARCHAR(20); 
     DECLARE sql_text VARCHAR(2000); 
     DECLARE drop_text VARCHAR(2000); 
     SET i=0;
     SET table_name='';
     SET table_pre='comment_video_pid_';
     SET sql_text='';
     SET drop_text='';
     WHILE i<2 DO
      IF i<10 THEN SET table_name=CONCAT(table_pre,'0',i);
      ELSE SET table_name=CONCAT(table_pre,i);
      END IF;


      SET drop_text = CONCAT('DROP TABLE IF EXISTS ', table_name);
      SET @drop_text=drop_text;
      PREPARE drop_stmt FROM @drop_text;
      EXECUTE drop_stmt;
      DEALLOCATE PREPARE drop_stmt;


      SET sql_text=CONCAT('CREATE TABLE `', table_name, '` (
 `id` varchar(30) NOT NULL COMMENT \'id\',
          `ssouid` bigint(10) NOT NULL COMMENT \'用户id\',
          PRIMARY KEY (`id`)
        ) ENGINE=INNODB DEFAULT CHARSET=utf8;');
        
      SELECT sql_text; 
      SET @sql_text=sql_text;
      PREPARE stmt FROM @sql_text;
      EXECUTE stmt;
      DEALLOCATE PREPARE stmt;  
      SET i=i+1;
    END WHILE;


  END $$
      
DELIMITER ;
call create_comment();