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*/