mysql5.7的存储过程,MySQL5.7从入门到精通-------存储过程与存储函数（3）

10.2.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,'大宇',22,'苏州','男');

INSERT INTO t_student VALUES(NULL,'小宇',20,'盐城','男');

INSERT INTO t_student VALUES(NULL,'小雨',20,'盐城','女');

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;

10.2.2  调用存储函数

存储函数不需要使用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 ;SELECT countStu2('男');

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

CREATE FUNCTION countStu2(stu_sex CHAR) -- 默认是IN，OUT、INOUT不支持。效果是IN,但是不能加上IN

RETURNS INT -- 定义返回值类型

BEGIN

RETURN (复杂的SQL);

END // -- 结束标记代表的是存储过程定义的结束。而不是不懂装懂的把结束标记放在SQL结尾

DELIMITER ;

10.3.1  使用SHOW STATUS 查看存储过程或函数的状态SHOW PROCEDURE STATUS LIKE 'C%';

SHOW FUNCTION STATUS LIKE 'C%';

知道了存储过程，如果希望查看具体的存储过程或者存储函数的定义。SHOW CREATE PROCEDURE study.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 study.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)

10.3.3  从information_schema.Routines 表中查询存储过程与函数(了解)

原来，MySQL中的存储过程与存储函数都存放在information_schema数据库下的Routines表中。SELECT * FROM information_schema.ROUTINES WHERE ROUTINE_NAME LIKE 'C%'

10.4.1  修改存储过程

语法：ALTER  PROCEDURE |  FUNCTION  sp_name [ 存储特性 ]修改存储过程，将读写权限改为 MODIFIES SQL DATE 并指明调用者

ALTER PROCEDURE countStu2

MODIFIES SQL DATE -- 表示子程序中包含写数据的语句

SQL SECURITY INVOKER -- 表示调用者才能执行

10.5.1  删除存储过程

删除存储过程语法：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%' --再次查看有哪些存储过程