mysql_17_存储过程和函数

-- 第十一章:存储过程和函数
-- 第一节:存储过程和函数的引入
-- 存储过程核函数是在数据库中定义一些sql语句的集合，然后直接调用这些存储过程核函数来执行已经定义好的
-- sql语句。存储过程核函数可以避免开发人员重复的编写相同的sql语句。而且，存储过程和函数是在Mysql服务
-- 器中存储和执行的，可以减少客户端和服务端的数据传输;


-- 第二节:创建存储过程和函数
-- 2.1创建存储过程
-- CREATE PROCEDURE sp_name([proc_parameter[,...]])
-- [characteristic...] routine_body
-- sp_name 参数是存储过程的名称；
-- proc_parameter 表示存储过程的参数列表；
-- characteristic 参数指定存储过程的特性；
-- routine_body 参数是 SQL 代码的内容，可以用 BEGIN...END 来标志 SQL 代码的开始和结束。
-- proc_parameter 中的每个参数由 3 部分组成。这 3 部分分别是输入输出类型、参数名称和参数类型。
-- [ IN | OUT | INOUT ] param_name type
-- 其中，IN 表示输入参数；OUT 表示输出参数；INOUT 表示既可以是输入，也可以是输出；param_name 参数是
-- 存储过程的参数名称；type 参数指定存储过程的参数类型，该类型可以是 MySQL 数据库的任意数据类型；
-- Characteristic 参数有多个取值。其取值说明如下：
-- LANGUAGE SQL：说明 routine_body 部分是由 SQL 语言的语句组成，这也是数据库系统默认的语言。
-- [ NOT ] DETERMINISTIC ：指明存储过程的执行结果是否是确定的。DETERMINISTIC 表示结果是确定的。每
-- 次执行存储过程时，相同的输入会得到相同的输出。NOT DETERMINISTIC 表示结果是非确定的，相同的输入
-- 可能得到不同的输出。默认情况下，结果是非确定的。
-- { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA } ：指明子程序使用 SQL 语句的限制；
-- CONTAINS SQL 表示子程序包含 SQL 语句，但不包含读或写数据的语句；NO SQL 表示子程序中不包含 SQL
-- 语句；READS SQL DATA 表示子程序中包含读数据的语句；MODIFIES SQL DATA 表示子程序中包含写数据的
-- 语句。默认情况下，系统会指定为 CONTAINS SQL；
-- SQL SECURITY { DEFINER | INVOKER }；指明谁有权限来执行。DEFINER 表示只有定义者自己才能够执行；
-- INVOKER 表示调用者可以执行。默认情况下，系统指定的权限是 DEFINER。
-- COMMENT ‘string’ ：注释信息；
USE db_book;

CREATE PROCEDURE pro_book (IN bT INT,OUT count_num INT)
        READS SQL DATA
        BEGIN
                SELECT COUNT(*) FROM t_book WHERE bookTypeId=bT;
        END

-- 使用存储过程
CALL pro_book(1,@total); -- @total:会话变量，写上就是全局变量，不写就是局部变量

-- 2.2创建存储函数
-- CREATE FUNCTION sp_name ( [func_parameter[,...]] )
-- RETURNS type
-- [ characteristic... ] routine_body
-- sp_name 参数是存储函数的名称；func_parameter 表示存储函数的参数列表；RETURNS type 指定返回值的
-- 类型；characteristic 参数指定存储过程的特性，该参数的取值与存储过程中的取值是一样的；routine_body 参数
-- 是 SQL 代码的内容，可以用 BEGIN...END 来标志 SQL 代码的开始和结束；
-- func_parameter 可以由多个参数组成，其中每个参数由参数名称和参数类型组成，其形式如下：
-- param_name type 其中，param_name 参数是存储函数的参数名称；type 参数指定存储函数的参数类型，
-- 该类型可以是 MySQL 数据库的任意数据类型；
CREATE FUNCTION func_book(bookId INT)
        RETURNS VARCHAR(20)
        BEGIN
                RETURN(SELECT bookName FROM t_book WHERE id=bookId);
        END    

-- 使用存储函数
SELECT func_book(1);


-- 2.3变量的使用
CREATE TABLE t_user(
id int PRIMARY KEY NOT NULL auto_increment,
userName VARCHAR(20),
password VARCHAR(20)
)
-- 1.定义变量
-- DECLARE var_name [,...] type [ DEFAULT value ]
CREATE PROCEDURE pro_user()
        BEGIN
                DECLARE a,b VARCHAR(20);
                INSERT INTO t_user VALUES (NULL,a,b);
        END

-- 调用pro_user存储过程
CALL pro_user();
SELECT * FROM t_user;-- 可知变量默认为:NULL

-- 2.为变量赋值
-- SET var_name = expr [,var_name=expr] ...
CREATE PROCEDURE pro_user2()
        BEGIN
                DECLARE a,b VARCHAR(20);
                SET a="haha",b="heihei";-- 为变量赋值
                INSERT INTO t_user VALUES (NULL,a,b);
        END

-- 调用pro_user2存储过程
CALL pro_user2();
SELECT * FROM t_user;

-- SELECT col_name[,...] INTO var_name[,...]
-- FROM table_name WHERE condition
CREATE TABLE t_user2(
id2 int PRIMARY KEY NOT NULL auto_increment,
userName2 VARCHAR(20),
password2 VARCHAR(20)
)
INSERT INTO t_user2 VALUES (NULL,"zhangsan","zhangsan123");

CREATE PROCEDURE pro_user3()
        BEGIN
                DECLARE a,b VARCHAR(20);
                SELECT userName2,password2 INTO a,b FROM t_user2 WHERE id2=1;
                INSERT INTO t_user VALUES (NULL,a,b);
        END

-- 调用pro_user2存储过程
CALL pro_user3();
SELECT * FROM t_user2;
SELECT * FROM t_user;

-- 2.4游标的使用
INSERT INTO t_user2 VALUES (NULL,"lisi","Lisi_2580");
-- 查询语句可能查询出多条记录，在存储过程和函数中使用游标来逐条读取查询结果集中的记录。
-- 游标的使用包括声明游标、打开游标、使用游标、和关闭游标。游标必须声明在处理程序之前，
-- 并且声明在变量和条件之后。
-- 1.游标声明
-- DECLARE cursor_name CURSOR FOR select_statement;
-- 2.打开游标
-- OPEN cursor_name;
-- 3.使用游标
-- FETCH cursor_name INTO var_name[,var_name...];
-- 4.关闭游标
-- CLOSE cursor_name;
CREATE PROCEDURE pro_user4()
        BEGIN
                DECLARE a,b VARCHAR(20);
                DECLARE cur_t_user2 CURSOR FOR SELECT userName2,password2 from t_user2 WHERE id2=2;-- 声明游标
                OPEN cur_t_user2;-- 打开游标
                FETCH cur_t_user2 INTO a,b;-- 使用游标
                INSERT INTO t_user VALUES (NULL,a,b);
                close cur_t_user2;-- 关闭游标
        END

CALL pro_user4();
SELECT * FROM t_user2;
SELECT * FROM t_user;

-- 2.5流程控制的使用
-- 存储过程和函数中可以使用流程控制来控制语句的执行。Mysql中可以使用IF语句、CASE语句、LOOP
-- 语句、LEAVE 语句、ITERATE 语句、REPEAT 语句和 WHILE 语句来进行流程控制。
-- 1.IF语句
-- IF search_condition THEN statement_list
-- [ ELSEIF search_condition THEN statement_list ]...
-- [ ELSE statement_list ]
-- END IF
CREATE PROCEDURE pro_user5(IN bookId INT)
        BEGIN
                SELECT COUNT(*) INTO @num FROM t_user WHERE id= bookId;
                IF @num>0 THEN UPDATE t_user SET userName="已更新" WHERE id=bookId;
                ELSE
                    INSERT INTO t_user VALUES (NULL,"已插入","已设密码");
                END IF;
        END

SELECT * FROM t_user;

CALL pro_user5(2);
SELECT * FROM t_user;

CALL pro_user5(5);
SELECT * FROM t_user;

-- 2.CASE语句
-- CASE case_value
-- WHEN when_value THEN statement_list
-- [WHEN when_value THEN statement_list]...
-- [ELSE statement_list ]
-- END CASE
CREATE PROCEDURE pro_user6(IN bookId INT)
        BEGIN
                SELECT COUNT(*) INTO @num FROM t_user WHERE id= bookId;
                CASE @num
                WHEN 1 THEN UPDATE t_user SET userName="CASE已更新" WHERE id=bookId;
                WHEN 6 THEN INSERT INTO t_user VALUES (NULL,"CASE已插入","CASE已设密码");
                ELSE INSERT INTO t_user VALUES (NULL,"CASE_ELSE已插入","CASE_ELSE已设密码");
                END CASE;
        END

SELECT * FROM t_user;

CALL pro_user6(1);
SELECT * FROM t_user;

CALL pro_user6(6);
SELECT * FROM t_user;

CALL pro_user6(7);
SELECT * FROM t_user;
-- 3.LOOP,LEAVE语句
-- LOOP 语句可以使某些特定的语句重复执行，实现一个简单的循环。但是 LOOP 语句本身没有停止循环
-- 的语句，必须是遇到 LEAVE 语句等才能停止循环。LOOP 语句的语法的基本形式如下：
-- [begin_label：]LOOP
-- Statement_list
-- END LOOP [ end_label ]
-- LEAVE 语句主要用于跳出循环控制。语法形式如下：
-- LEAVE label
DELETE FROM t_user;

CREATE PROCEDURE pro_user7(IN totalNum INT)
        BEGIN
                abc:LOOP
                IF totalNum=0 THEN LEAVE abc;
                ELSE INSERT INTO t_user VALUES (totalNum,"LOOP已插入","LOOP已设密码");
                END if;
                SET totalNum=totalNum-1;
                END LOOP abc;
        END

SELECT * FROM t_user;
CALL pro_user7(10);
SELECT * FROM t_user;

DELETE FROM t_user;
DROP PROCEDURE pro_user7;

-- 4.ITERATE语句
-- ITERATE 语句也是用来跳出循环的语句。但是，ITERATE 语句是跳出本次循环，然后直接进入下一次
-- 循环。基本语法：
-- ITERATE label ;
DELETE FROM t_user;

CREATE PROCEDURE pro_user8(IN totalNum INT)
        BEGIN
                abc:LOOP
                SET totalNum=totalNum-1;
                IF totalNum=0 THEN LEAVE abc;            
                ELSEIF totalNum=5 THEN ITERATE abc;
                END if;
                INSERT INTO t_user VALUES (totalNum,"LOOP已插入","LOOP已设密码");
                END LOOP abc;
        END

SELECT * FROM t_user;
CALL pro_user8(10);
SELECT * FROM t_user;

DELETE FROM t_user;

-- 5.REPEAT语句
-- REPEAT 语句是有条件控制的循环语句。当满足特定条件时，就会跳出循环语句。REPEAT 语句的基本
-- 语法形式如下：
-- [ begin_label : ] REPEAT
-- Statement_list
-- UNTIL search_condition
-- END REPEAT [ end_label ]
DELETE FROM t_user;

CREATE PROCEDURE pro_user9(IN totalNum INT)
        BEGIN
                REPEAT
                SET totalNum=totalNum-1;
                INSERT INTO t_user VALUES (totalNum,"已插入","已设密码");
                UNTIL totalNum=3    -- 此处不能加分号
                END REPEAT;
        END

SELECT * FROM t_user;
CALL pro_user9(10);
SELECT * FROM t_user;

DELETE FROM t_user;

-- 6.WHILE语句
-- [ begin_label : ] WHILE search_condition DO
-- Statement_list
-- END WHILE [ end_label ]
DELETE FROM t_user;

CREATE PROCEDURE pro_user10(IN totalNum INT)
        BEGIN
                WHILE totalNum>0 DO
                INSERT INTO t_user VALUES (totalNum,"WHILE已插入","WHILE已设密码");
                SET totalNum=totalNum-1;
                END WHILE;
        END

SELECT * FROM t_user;
CALL pro_user10(10);
SELECT * FROM t_user;

DELETE FROM t_user;


-- 第三节:调用存储过程和函数
-- 3.1调用存储过程
-- CALL sp_name( [parameter[,...]] )
-- 3.2调用存储函数
-- fun_name( [parameter[,...]] )


-- 第四节:查看存储过程和函数
-- 4.1 SHOW STATUS 语句查看存储过程和函数的状态
-- SHOW { PROCEDURE | FUNCTION } STATUS [ LIKE ‘pattern’ ] ;
SHOW PROCEDURE STATUS LIKE "pro_%";
SHOW FUNCTION STATUS LIKE "func_book";

-- 4.2 SHOW CREATE 语句查看存储过程和函数的定义
-- SHOW CREATE { PROCEDURE | FUNCTION } sp_name;
SHOW CREATE PROCEDURE pro_book;
SHOW CREATE FUNCTION func_book;

-- 4.3 从information_schema.Routines表中查看存储过程和函数的信息


-- 第五节:修改存储过程和函数
-- ALTER { PROCEDURE | FUNCTION } sp_name [ characteristic ... ]
-- characteristic :
-- { CONTAINS SQL } NO SQL | READS SQL DATA | MODIFIES SQL DATA }
-- | SQL SECURITY { DEFINER | INVOKER }
-- | COMMENT ‘string’
-- 其中:
-- sp_name 参数表示存储过程或函数的名称；
-- characteristic 参数指定函数的特性。
-- CONTAINS SQL 表示子程序包含 SQL 语句，但不包含读或写数据的语句；
-- NO SQL 表示子程序中不包含 SQL 语句；
-- READS SQL DATA表示子程序中包含数据的语句;
-- MODIFIES SQL DATA 表示子程序中包含写数据的语句。
--  SQL SECURITY{ DEFINER | INVODER } 指明谁有权限来执行。
-- DEFINER 表示只有定义者自己才能够执行；
-- INVODER 表示调用者可以执行。
-- COMMENT ‘string’ 是注释信息。
SHOW PROCEDURE STATUS LIKE "pro_book";
ALTER PROCEDURE pro_book COMMENT "测试一个comment";
SHOW PROCEDURE STATUS LIKE "pro_book";

ALTER PROCEDURE pro_book COMMENT "";
SHOW PROCEDURE STATUS LIKE "pro_book";


-- 第六节:删除存储过程和函数
-- DROP {PROCEDURE | FUNCTION } sp_name ;
DROP PROCEDURE pro_user10;
DROP FUNCTION func_book;