MYSQL存储过程初级入门

MySql的添加存储过程

DROP PROCEDURE IF EXISTS aaa;  /*删除存储过程*/
DELIMITER $ /*自定义结束符号为$*/
CREATE PROCEDURE aaa()  /*创建存储过程aaa*/
  BEGIN /*开始*/
    DECLARE i INT DEFAULT 1;/*初始化i为1*/
    START TRANSACTION;/*可有可无 事务*/
    WHILE i <= 100 DO /*循环次数*/
      INSERT INTO student	(name, sex, email) VALUES (concat('javacode',i),if(mod(i,2),1,2),concat('javacode',i,'@163.com'));
      SET i = i + 1; /*相当于for循环得i++*/
      if i%10=0 THEN /*if判断*/
        COMMIT; /*提交事务*/
        START TRANSACTION;/*开启事务*/
      END IF;/*if判断结束*/
    END WHILE;/*循环结束*/
    COMMIT;/*提交*/
  END $/*结束*/

DELIMITER ;  /*结束符号改成;*/
CALL aaa();  /* 启动存储过程 */

MySql得查询存储过程
不带参数

/*user1如果存在的话就会被删除*/
DROP PROCEDURE IF EXISTS student1;
create PROCEDURE student1()
begin
select * from student;
end;

CALL student1();

带参数

DROP PROCEDURE IF EXISTS student2;
create PROCEDURE student2(IN id1 int)
begin
select * from student where id = id1;
end;

CALL student2(57); /*执行student1*/

MySql得删除存储过程
传值

DROP PROCEDURE IF EXISTS student3;
create PROCEDURE student3(IN id1 int)
begin
delete from student where id = id1;
end;

CALL student3(57); /*执行student1*/

初始化值

DROP PROCEDURE IF EXISTS student3;
create PROCEDURE student3()
begin
DECLARE id1 INT DEFAULT 56;/*初始化id1为56*/
delete from student where id = id1;
end;

CALL student3(); /*执行student3*/

MySql得修改存储过程

DROP PROCEDURE IF EXISTS student4;
create PROCEDURE student4(IN id1 INT,IN name1 VARCHAR(255) )
begin
update student set name = name1 where id = id1;
end;

CALL student4(50,"哈哈哈"); /*执行student1*/