MySQL基础学习（调用存储过程）1、调用存储过程存储过程必须使用CALL语句来调用。如果要调用其它数据库的存储过程，

开启掘金成长之旅！这是我参与「掘金日新计划 · 12 月更文挑战」的第7天，点击查看活动详情

1、调用存储过程

存储过程必须使用CALL语句来调用。如果要调用其它数据库的存储过程，需要指定数据库名称。例如 CALL dbname.spname

DROP TABLE IF EXISTS t_student;
CREATE TABLE t_student
(
  id INT(11) PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(255) NOT NULL,
  age  INT(11),
  address VARCHAR(255),
  sex  CHAR(2)
);
 
INSERT INTO t_student VALUES(NULL,'小明',21,'北京','男');
INSERT INTO t_student VALUES(NULL,'小花',22,'上海','女');
INSERT INTO t_student VALUES(NULL,'小兰',22,'南京','女');

DELIMITER //  --存储过程分隔符设定为//
CREATE PROCEDURE CountStu(IN stu_sex CHAR,OUT num INT)  --stu_sex表示输入，num表示输出
BEGIN 
   SELECT COUNT(*) INTO num FROM t_student WHERE sex = stu_sex; --结果存入num，条件用上变量
END //        --以指定分隔符结束存储过程
DELIMITER ;   --存储过程分隔符设定为;

CALL CountStu('男',@num);
SELECT @num;
 
-- 本质为执行下面的SQL：
 
SELECT COUNT(*) AS @num 
FROM t_student WHERE sex = '男';

CALL CountStu('女',@num);
SELECT @num;

-- 尝试传入的其它的数据
CALL CountStu('3',@num);
SELECT @num;

原来，存储过程并不是什么神秘的东西。它只是将SQL语句抽取出来，定义成模版，并定义接收条件参数。在下次要使用这些SQL的时候，传入我们需要的参数。其实就像Java一样，定义一个方法。

再回头来看最初的存储过程的定义：存储过程就是一条或者多条SQL语句的集合。

现在可以理解为什么要用存储过程。如果我们要做测试数据，需要往表里面批量插入1000条数据。不可能手动输入1000次INSERT语句，这个时候，就能用的上存储过程。另外，当我们需要根据不同的条件去查询指定表里面的数据的时候，也可以预定义一些存储过程。这样，根据不同的条件，我们就能快速的获取到查询结果了。

1.1、调用存储函数

存储函数不需要使用CALL关键字。另外，存储函数的参数类型默认为IN输入。

DELIMITER //
CREATE FUNCTION countStu2(stu_sex CHAR) -- 默认是IN，OUT、INOUT不支持。效果是IN,但是不能加上IN
RETURNS INT
RETURN  (SELECT COUNT(*) FROM t_student WHERE sex = stu_sex); // -- 不加结束标记，运行失败
DELIMITER ;

如果遇到一下错误：

执行创建函数的sql语句时，提示:This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled

解决方案：

在mysql数据库中执行以下语句（临时生效，重启后失效） set global log_bin_trust_function_creators=TRUE;

SELECT countStu2('男');

虽然存储函数与存储过程的定义稍有不同，但是可以实现相同的功能，应该灵活选择。

通过定义存储函数，就能像使用MySQL提供的函数那样。换言之，存储函数是自己定义的，而内部函数是MySQL开发者定义的。

如果SQL语句较为复杂，下面加上BEGIN与END的写法更佳。

DELIMITER //
CREATE FUNCTION countStu2(stu_sex CHAR) -- 默认是IN，OUT、INOUT不支持。效果是IN,但是不能加上IN
RETURNS INT  -- 定义返回值类型
BEGIN
   RETURN  (复杂的SQL);
END // -- 结束标记代表的是存储过程定义的结束。
DELIMITER ;

1.2、使用SHOW STATUS 查看存储过程或函数的状态

SHOW PROCEDURE STATUS LIKE 'C%';

SHOW FUNCTION STATUS LIKE 'C%';

知道了存储过程，如果希望查看具体的存储过程或者存储函数的定义。

SHOW CREATE PROCEDURE school.CountStu;
 
-- Create Procedure 列为核心语句
CREATE DEFINER=`root`@`localhost` PROCEDURE `CountStu`(IN stu_sex CHAR,OUT num INT)
BEGIN 
   SELECT COUNT(*) INTO num FROM t_student WHERE sex = stu_sex;
END

查看存储函数有哪些。

SHOW FUNCTION STATUS LIKE 'C%'

查看具体的存储函数创建语句。

SHOW CREATE FUNCTION school.countStu2
 
-- Create Function 列的语句
CREATE DEFINER=`root`@`localhost` FUNCTION `countStu2`(stu_sex CHAR) 
RETURNS int(11)
RETURN  (SELECT COUNT(*) FROM t_student WHERE sex = stu_sex)

1.3、从information_schema.Routines 表中查询存储过程与函数（了解）

    原来，MySQL中的存储过程与存储函数都存放在information_schema数据库下的Routines表中。

SELECT * FROM information_schema.ROUTINES WHERE ROUTINE_NAME LIKE 'C%'

如果什么时候忘记了存储函数或者存储过程的名字，可以查询这张表的数据。然后确定了是某个存储过程或者是存储函数，就可以使用SHOW CREATE PROCEDURE / FUNCTION 数据库.sp_name查看指定的创建语句了。

1.4、修改存储过程

语法：ALTER PROCEDURE | FUNCTION sp_name [ 存储特性 ]

修改存储过程，将读写权限改为 MODIFIES SQL DATE 并指明调用者
ALTER PROCEDURE countStu2
MODIFIES SQL DATE   -- 表示子程序中包含写数据的语句
SQL SECURITY INVOKER  -- 表示调用者才能执行

1.5、删除存储过程

删除存储过程语法：DROP PROCEDURE [ IF EXISTS ] sp_name；

删除存储函数语法：DROP FUNCTION [ IF EXISTS ] function_name；

SHOW PROCEDURE STATUS LIKE 'C%'

DROP PROCEDURE IF EXISTS study.CountTec;  -- 删除study库下的名为CountTec存储过程
 
SHOW PROCEDURE STATUS LIKE 'c%' --再次查看有哪些存储过程

2、存储过程与存储函数的补充

2.1、MySQL的存储过程与存储函数有什么区别？

存储函数只能通过return语句返回单个值或者表对象。

存储过程不能用return，但是可以使用多个out参数返回多个值。

2.2、存储过程如何修改代码？

虽然提供了ALTER PROCEDURE sp_name [存储特性]，但是只能修改存储过程的存储特性，不能修改SQL。需要删除并重新创建。

2.3、存储过程中能调用其它存储过程吗？

可以在存储过程中的SQL中通过CALL调用其它存储过程，但是不能用DROP删除其它存储过程。

2.4、IN、OUT等参数与字段名相同了怎么办？

需要将它们区别开来，否则将会出现错误。

2.5、存储过程的IN参数可能是中文怎么办？

在定义存储过程的时候，加上character set gbk。

例如：

DELIMITER //
CREATE PROCEDURE getAddressByName(IN u_name VARCHAR(50) character set gbk , OUT address VARCHAR(50))
BEGIN
     SQL;
END//
DELIMITER ;

MySQL基础学习（调用存储过程）