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