mysql 存储过程 mod_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/