极速掌握MySQL存储过程，开学秒室友

DELIMITER //
CREATE PROCEDURE demo_in_parameter(IN p_in int)
BEGIN
SELECT p_in;
SET p_in=2;
SELECT p_in;
END;
//
DELIMITER ;
##调用，给一个变量，不影响外面参数的值
call demo_in_parameter(3);
MySQL [db1]> set @p_in=66;
MySQL [db1]> call demo_in_parameter(@p_in);
注意：CMD窗口执行，记得留意空格

##case 语句
DELIMITER //
CREATE PROCEDURE proc3 (in parameter int)
begin
declare var int;
set var=parameter+1;
case var
when 0 then
insert into t values(17);
when 1 then
insert into t values(18);
else
insert into t values(19);
end case;
end;
//
DELIMITER ;

##repeat···· end repeat,它在执行操作后检查结果
DELIMITER //
CREATE PROCEDURE proc5 ()
begin
declare v int;
set v=0;
repeat
insert into t values(v);
set v=v+1;
until v>=5
end repeat;
end;
//
DELIMITER ;
mysql> call proc5;
mysql> select * from t2;

DROP PROCEDURE create_table_pro;
DELIMITER //
CREATE PROCEDURE create_table_pro()
BEGIN
# 定义接受sql数据的变量
DECLARE courseNo varchar(50);
DECLARE studentNo varchar(20);
DECLARE studentName varchar(20);
DECLARE classNo varchar(20);
DECLARE score int;
# 声明表名称
DECLARE tblname VARCHAR(32);
# 总行数
DECLARE cnt INT DEFAULT 0;
# 循环变量i
DECLARE i INT DEFAULT 0;
declare tmp_cursor cursor for(
select 
c.courseNo,
a.studentNo,
a.studentName,
a.classNo,
b.score from tb_student a,tb_score b,tb_course c
where a.studentNo = b.studentNo
and b.courseNo = c.courseNo
order by 1);
SELECT COUNT(*) INTO cnt from tb_student a,tb_score b,tb_course c
where a.studentNo = b.studentNo
and b.courseNo = c.courseNo;
open tmp_cursor;
 # 开始循环
  REPEAT
    SET i := i+1;
      FETCH tmp_cursor INTO courseNo,studentNo,studentName,classNo,score;
    SET tblname = CONCAT('course_',courseNo);
    SET @delTabl = CONCAT('DELETE FROM ',tblname,' WHERE studentNo=','''',studentNo,'''');
    SET @createTbsql = CONCAT('create table if not exists ',tblname,'(studentNo char(10) not null primary key,
    studentName varchar(10) not null,classNo char(6),credite int) ENGINE=InnoDB DEFAULT CHARSET=GB2312;');
    SET @insertTabl = CONCAT(' insert into ',tblname,' values(',
    '''',studentNo,'''','\,',
    '''',studentName,'''','\,',
    '''',classNo,'''','\,',
    '''',score,'''',
      ')'
    );
    select @insertTabl;
    # 执行动态删除表语句
    PREPARE temp FROM @delTabl;
    EXECUTE temp;
      DEALLOCATE PREPARE temp;
      # 执行动态生成的创建表语句
    PREPARE temp FROM @createTbsql;
    EXECUTE temp;
      DEALLOCATE PREPARE temp;
    # 执行动态表插入语句
    PREPARE temp FROM @insertTabl;
    EXECUTE temp;
      DEALLOCATE PREPARE temp;
      UNTIL i>=cnt END REPEAT;
# 循环结束
close tmp_cursor;
END;
//
DELIMITER ;