Mysql存储过程.docx

1存储过程1.1:定义create procedure 名字 (in 参数 int,out 参数 int)begin存储过程体end$$?DELIMITER?// ??CREATE?PROCEDURE?proc1(OUT?s?int) ??BEGIN??SELECT?COUNT(*)?INTO?s?FROM?user; ??END??// ??DELIMITER?1.2调用存储过程call 存储过程(参数..);2:如何定义变量declare 变量名 类型（长度）；3:条件分支语句3.1:IF THENIF…条件THEN 执行的代码 END IF3.2 CASE WHENcase 变量名when ‘in’ then set sex=’男’;when ‘f’ then set sex=’女’；else if sex=’无’;end case;3.3循环语句while 条件 doend while举例：delimiter$$create procedure dowhile(){ begin declare v1 int default 5; while v10 do set v1=v1-1; end while; end$$delimiter;3.4:和while do作用一样类似于java中的do..whilerepeat要执行的代码循环体until v11;end repeat；4:游标declare 游标名cursor for游标名 检索（select语句）打开游标：open 游标名;关闭游标：close 游标名;5：Sqlyog如何创建存储过程：表：这个是我的表结构DELIMITER $$USE `java02_test`$$DROP PROCEDURE IF EXISTS `cst`$$CREATE DEFINER=`root`@`localhost` PROCEDURE `cst`(IN v1 INT,IN v2 INT)BEGIN WHILE v1v2 DO INSERT INTO customers VALUES(v1,郑州); SET v1= v1-1;END WHILE; END$$DELIMITER ;6:存储过程传参：USE `java02_test`$$DROP PROCEDURE IF EXISTS `cst`$$CREATE DEFINER=`root`@`localhost` PROCEDURE `cst`(IN v1 INT,IN v2 INT,OUT i INT)BEGIN /*循环*/ WHILE v1v2 DO INSERT INTO customers VALUES(v1,郑州); /*变量设值*/ SET v1= v1-1; /*变量设值*/ SET i=1000;END WHILE; END$$DELIMITER ;外部如何接收存储过程返回值以及如何传参数给存储过程：7:批量更新数据DELIMITER $$USE `java_01`$$DROP PROCEDURE IF EXISTS `updateDate`$$CREATE PROCEDURE `java_01`.`updateDate`() BEGIN DECLARE class_08_count INT;DECLARE i INT DEFAULT 1;SELECT COUNT(*) INTO class_08_count FROM class_08;WHILE(iclass_08_count) DO UPDATE class_08 SET date_d=DATE_ADD(date_d,INTERVAL i DAY) WHERE id=i; SET i=i+1;END WHILE; END$$DELIMITER ;