概述
由MySQL5.0 版本开始支持存储过程。
如果在实现用户的某些需求时,需要编写一组复杂的SQL语句才能实现的时候,那么我们就可以将这组复杂的SQL语句集提前编写在数据库中,由JDBC调用来执行这组SQL语句。把编写在数据库中的SQL语句集称为存储过程。
存储过程:(PROCEDURE)是事先经过编译并存储在数据库中的一段SQL语句的集合。调用存储过程可以简化应用开发人员的很多工作,减少数据在数据库和应用服务器之间的传输,对于提高数据处理的效率是很有好处的。
就是数据库 SQL 语言层面的代码封装与重用。
存储过程就类似于Java中的方法,需要先定义,使用时需要调用。存储过程可以定义参数,参数分为IN、OUT、INOUT类型三种类型。
DELIMITER $$CREATE /*[DEFINER = { user | CURRENT_USER }]*/ PROCEDURE 数据库名.存储过程名([in变量名 类型,out 参数 2,...]) /*LANGUAGE SQL | [NOT] DETERMINISTIC | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA } | SQL SECURITY { DEFINER | INVOKER } | COMMENT 'string'*/ BEGIN [DECLARE 变量名 类型 [DEFAULT 值];] 存储过程的语句块; END$$DELIMITER ;
存储过程中的参数分别是 in,out,inout三种类型;
存储过程中的语句必须包含在BEGIN和END之间。
DECLARE中用来声明变量,变量默认赋值使用的DEFAULT,语句块中改变变量值,使用SET 变量=值;
DELIMITER $$CREATE PROCEDURE `demo`.`demo1`() -- 存储过程体 BEGIN -- DECLARE声明 用来声明变量的 DECLARE de_name VARCHAR(10) DEFAULT ''; SET de_name = "jim"; -- 测试输出语句(不同的数据库,测试语句都不太一样。 SELECT de_name; END$$DELIMITER ;
调用存储过程
CALL demo1();
先定义一个student数据库表:
现在要查询这个student表中的sex为男的有多少个人。
DELIMITER $$CREATE PROCEDURE `demo`.`demo2`(IN s_sex CHAR(1),OUT s_count INT) -- 存储过程体 BEGIN -- 把SQL中查询的结果通过INTO赋给变量 SELECT COUNT(*) INTO s_count FROM student WHERE sex= s_sex; SELECT s_count; END$$DELIMITER ;
调用这个存储过程
-- @s_count表示测试出输出的参数CALL demo2 ('男',@s_count);
IF 语句包含多个条件判断,根据结果为 TRUE、FALSE执行语句,与编程语言中的 if、else if、else 语法类似。
DELIMITER $$CREATE PROCEDURE `demo`.`demo3`(IN `day` INT) -- 存储过程体 BEGIN IF `day` = 0 THEN SELECT '星期天'; ELSEIF `day` = 1 THEN SELECT '星期一'; ELSEIF `day` = 2 THEN SELECT '星期二'; ELSE SELECT '无效日期'; END IF; END$$DELIMITER ;
调用这个存储过程
CALL demo3(2);
case是另一个条件判断的语句,类似于编程语言中的 choose、when语法。MySQL 中的 case语句有两种语法格式。
DELIMITER $$CREATE PROCEDURE demo4(IN num INT) BEGIN CASE -- 条件开始 WHEN num<0 THEN SELECT '负数'; WHEN num>0 THEN SELECT '正数'; ELSE SELECT '不是正数也不是负数'; END CASE; -- 条件结束 END$$DELIMITER;
调用这个存储过程
CALL demo4(1);
2.第二种
DELIMITER $$CREATE PROCEDURE demo5(IN num INT) BEGIN CASE num -- 条件开始 WHEN 1 THEN SELECT '输入为1'; WHEN 0 THEN SELECT '输入为0'; ELSE SELECT '不是1也不是0'; END CASE; -- 条件结束 END$$DELIMITER;
调用此函数
CALL demo5(0);
DELIMITER $$CREATE PROCEDURE demo6(IN num INT,OUT SUM INT) BEGIN SET SUM = 0; WHILE num<10 DO -- 循环开始 SET num = num+1; SET SUM = SUM+num; END WHILE; -- 循环结束 END$$DELIMITER;
调用此函数
-- 调用函数CALL demo6(0,@sum);-- 查询函数SELECT @sum;
REPEATE…UNTLL 语句的用法和 Java中的 do…while 语句类似,都是先执行循环操作,再判断条件,区别是REPEATE 表达式值为 false时才执行循环操作,直到表达式值为 true停止。
-- 创建过程DELIMITER $$CREATE PROCEDURE demo7(IN num INT,OUT SUM INT) BEGIN SET SUM = 0; REPEAT-- 循环开始 SET num = num+1; SET SUM = SUM+num ; UNTIL num>=10 END REPEAT; -- 循环结束 END$$DELIMITER;
调用此函数
CALL demo7(9,@sum);SELECT @sum;
定义一个循环语句 LOOP
循环语句,用来重复执行某些语句。
执行过程中可使用 LEAVE语句或者ITEREATE来跳出循环,也可以嵌套IF等判断语句。
DELIMITER $$CREATE PROCEDURE demo8(IN num INT,OUT SUM INT) BEGIN SET SUM = 0; demo_sum:LOOP-- 循环开始 SET num = num+1; IF num > 10 THEN LEAVE demo_sum; -- 结束此次循环 ELSEIF num <= 9 THEN ITERATE demo_sum; -- 跳过此次循环 END IF; SET SUM = SUM+num; END LOOP demo_sum; -- 循环结束 END$$DELIMITER;
调用此函数
CALL demo8(0,@sum);SELECT @sum;
DELIMITER $$CREATE PROCEDURE demo9(IN s_student VARCHAR(10),IN s_sex CHAR(1),OUT s_result VARCHAR(20)) BEGIN -- 声明一个变量 用来决定这个名字是否已经存在 DECLARE s_count INT DEFAULT 0; -- 验证这么名字是否已经存在 SELECT COUNT(*) INTO s_count FROM student WHERE `name` = s_student; IF s_count = 0 THEN INSERT INTO student (`name`, sex) VALUES(s_student, s_sex); SET s_result = '数据添加成功'; ELSE SET s_result = '名字已存在,不能添加'; SELECT s_result; END IF; END$$DELIMITER;
调用此函数
CALL demo9("Jim","女",@s_result);
再次调用次函数
CALL demo9("Jim","女",@s_result)
SHOW PROCEDURE STATUS
SHOW PROCEDURE STATUS WHERE db = 'db名字' AND NAME = 'name名字';
SHOW PROCEDURE STATUS WHERE NAME LIKE '%mo%';
SHOW CREATE PROCEDURE 存储过程名;
DROP PROCEDURE 存储过程名;
在mybatis当中,调用存储过程
<parameterMap type="savemap" id=“usermap"> <parameter property="name" jdbcType="VARCHAR" mode="IN"/> <parameter property="sex" jdbcType="CHAR" mode="IN"/> <parameter property="result" jdbcType="VARCHAR" mode="OUT"/></parameterMap><insert id="saveUserDemo" parameterMap="savemap" statementType="CALLABLE"> {call saveuser(?, ?, ?)} </insert >
调用数据库管理
HashMap<String, Object> map = new HashMap<String, Object>(); map.put("name", "Jim"); map.put("sex","男"); userDao.saveUserDemo(map); map.get(“result”);//获得输出参数
通过这样就可以调用数据库中的存储过程的结果。