MySQL存储结构用法案例分析(2)-木庄网络博客

1.打印student1中的部分信息

---------------------------------------------------------------
DROP PROCEDURE IF EXISTS test_pro1;
---------------------------------------------------------------
DELIMITER &&
CREATE PROCEDURE test_pro1()
BEGIN
 set @sentence = 'select * from student1 where age<19;';
 prepare stmt from @sentence;
 execute stmt;
 deallocate prepare stmt;
END &&
DELIMITER ;

2.复制表存储过程的编写(不带参数)

---------------------------------------------------------------
DROP PROCEDURE IF EXISTS test_pro2;
---------------------------------------------------------------
DELIMITER &&
create procedure test_pro2()
begin
  DECLARE stop_flag INT DEFAULT 0;
  DECLARE s_name TEXT default '';
  DECLARE s_age INT default 0;
  DECLARE cur1 CURSOR FOR (select name, age from student1 where age<19);
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET stop_flag=1;
  open cur1;
    fetch cur1 into s_name, s_age;
  while stop_flag<>1 DO
    insert into student2(name, age) values(s_name, s_age);
    fetch cur1 into s_name, s_age;
  end while;
  close cur1;
end &&
DELIMITER ;

3.复制表存储过程的编写(带参数)

---------------------------------------------------------------
DROP PROCEDURE IF EXISTS test_pro3;
---------------------------------------------------------------
DELIMITER &&
create procedure test_pro3(IN p_age INT)
begin
  DECLARE stop_flag INT DEFAULT 0;
  DECLARE s_name TEXT default '';
  DECLARE s_age INT default 0;
  DECLARE cur1 CURSOR FOR (select name, age from student1 where age<p_age);
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET stop_flag=1;
  open cur1;
    fetch cur1 into s_name, s_age;
  while stop_flag<>1 DO
    insert into student2(name, age) values(s_name, s_age);
    fetch cur1 into s_name, s_age;
  end while;
  close cur1;
end &&
DELIMITER ;

这里的SQLSTATE '02000'和NOT FOUND系统返回值是一样的。

4.使用方式