mysql存储过程procedure的学习记录

最新推荐文章于 2021-02-22 23:30:26 发布

lx6766

最新推荐文章于 2021-02-22 23:30:26 发布

阅读量149

点赞数

分类专栏： mysql

本文链接：https://blog.csdn.net/lsx6766/article/details/89154891

版权

mysql 专栏收录该内容

23 篇文章 0 订阅

订阅专栏

1 使用前，重新命令结束符

DELIMITER $$

存储过程核心代码....

$$

DELIMITER ; -- 恢复默认的分号结尾，即每个sql语句的结束标识

2 关于变量的定义：

1 在mysql>>下的变量为当前通过mysql -h120.0.0.1 -uroot -p这个登录用户下的“用户变量” 声明方式为 @aaa=1;

在存储过程里面的变量为局部变量，声明方式为 declare _aaa int(11);

设置变量的方式： set xxx=111;

3存储过程的定义 :

create procedure xxx(参数...)

begin

   执行逻辑....

end

4 一个完整的存储过程示例：

DELIMITER $$

USE `ultimate`$$ -- 注意：从此时开始默认结尾符号已经变成了$$

DROP PROCEDURE IF EXISTS `p_gen_dim_iteminfo`$$

CREATE DEFINER=`user1`@`%` PROCEDURE `p_gen_y1`()
BEGIN
SET @wsql = (SELECT "
    select

    i.itemid,
    (select category from dim_um_genre g where i.genre=g.genre) genre_category,
    ( select group_concat(r.name) from dim_artist r,  itemartist ir  where ir.itemid=i.itemid and ir.artistid=r.artistid  ) artist_name,
    if( (select max(subitemtype)
        from contentfileinfo c where i.itemid=c.itemid and subitemtype='FLAC-1000K-FTD' ) is null,'N','Y') flac1000,
    DATE_FORMAT(i.modifyDate,'%Y-%m-%d') modifyDate,
    TIME_TO_SEC( time_format(if(length(i.duration)=5,concat('00:',i.duration),i.duration) ,'%H:%i:%s')) duration_sec,
    IF(i.company_type=4
      OR
      (SELECT c.id FROM music_lib.list1 c
       WHERE
       (c.cpid=i.cpid AND c.spid=i.spid)
      OR(c.spid=i.spid AND c.cpid IS NULL)
      OR(c.albumid=a.albumid AND c.albumid IS NOT NULL)
      limit 1
      ) IS NOT NULL ,'Y','N') is_t1
from iteminfo i
left join itemalbum ia on i.itemid=ia.itemid
left join albuminfo a on a.albumid=ia.albumid" );


 SET @SQL=(SELECT CONCAT('create table dm_iteminfo_',DATE_FORMAT(DATE_SUB(NOW(),INTERVAL 1 DAY),'%Y%m%d'),' as ', @wsql));
 SELECT @SQL;
 PREPARE stmt FROM @SQL;
 EXECUTE stmt;
END$$

DELIMITER ;

5 如果要查询一个内容，在执行过程中显示，类似于java log日志那般打印出来，主需要select语句，通常我们是select一个表去查询需要的结果，但其实select本质可以查询任何存在的内容，可以是常量，而不仅仅是查db表，例如：

select "aaa"; -- 可以是常量值
select now(); -- 可以是mysql有的函数；
select name from user where id=1 ; -- 我们最熟悉的select

6 关于执行sql,在最开始学习myql时，我们知道，要有statement执行句柄，要准备好语句，要有connection连接对象等等，在到后来，我们有了封装好的工具，我们核心要做的就是定义好sql，在到后面我们有了框架，连工具都不用自己封装了，仅仅需要写业务sql了，慢慢的，对于一些底层实际操作的内容我们似乎淡忘了，但这却是使用mysql的核心的真正一直在操作的东西

 SELECT @SQL;
 PREPARE stmt FROM @SQL;
 EXECUTE stmt;

很多事情，很多内容，可能刚一接触时，会抵触，为什么会这么多内容，但随着关注点的变化，我们再回头看时会发现，这些内容虽不必深挖，但一定要知道，知道到底是谁在操控着这一切，做到有所依，有所用，有所思。东西虽然看似一步步简化了，我们使用上也越来越便捷了，但请一定不要忘记，最开始它的样子。丢掉的记忆，迟早一天是要找回来的。否则他就会成为一颗隐藏的雷

7关于存储过程，除了上面那样标准的格式外，如果要执行的内容很简单的话（实则大材小用），只有一条执行语句的话，也就没必要提前更改结束符了，可以直接一句搞定，使用简写形式了，如下：

 mysql> CREATE PROCEDURE p1() SET @last_procedure='p1';
 mysql> CREATE PROCEDURE p2() SELECT CONCAT('Last procedure was ',@last_procedure); 
 mysql> CALL p1( ); 
 mysql> CALL p2( );

8 关于执行，调用就更简单了，直接call即可，但注意，我之前老是在call前面加上procedure ，注意，不用，不用，不用！！！

9 关于如何定时执行存储过程：

这属于是Events事件范畴了，我给出一个示例：

DELIMITER $$

ALTER DEFINER=`test_user`@`%` EVENT `test_event_proc` ON SCHEDULE EVERY 1 DAY STARTS '2018-10-22 04:30:00' ON COMPLETION PRESERVE ENABLE DO CALL aaa_proc("","","DS")$$

DELIMITER ;

10 一个很少的学习存储过程和函数的博客：

https://www.cnblogs.com/Honeycomb/p/10672659.html

11 关于函数

首先看下每个db库下的这几个包：

tables:用户自定义表，这里我截取的是msyql库，这个是系统表，其他的用户自定义库，即包含的是用户自定义表

views:他的用户，当db里的表不能满足需求时，我们可能需要几个表的视图view，方便后续再一个个表去查找，view就可以把之前的查询结果作为view存储了

store procs 即存储过程，一般是执行一些复杂的逻辑程序，类似于java程序接口service那样的逻辑

functions 函数，除了mysql提供的函数外，我们可能也想根据实际业务需要去定制函数，此时就是他；

triggers 我目前没有接触到

events :像上面提到的定时执行某个存储过程，这就是一个事件；

关于函数，我另外写一篇博客，就不再继续写在这里了。

12 关于begin，end 如果不加的话，那存储过程中智慧有一句sql，看下面的示例就理解了：

mysql> use demo;
Database changed
mysql> delimiter //
mysql> create procedure aaa()
    -> select count(*) from users where id>1;  -- 到这里存储过程就结束了
    -> select count(*) from users ;  这里是另外的查询， 因为上面的结束符还是//，所以这里还不会技术
    -> // -- 到这里不论上面写了几个sql会一并执行；
Query OK, 0 rows affected (0.04 sec)

+----------+
| count(*) |
+----------+
|        4 |
+----------+
1 row in set (0.04 sec)

mysql> delimiter ;
mysql> call aaa(); -- 调用存储过程
+----------+
| count(*) |
+----------+
|        3 |
+----------+
1 row in set (0.04 sec)

mysql> show create procedure aaa;  -- 这里show查看存储过程详情,就可以直观看到存储过程的执行体都有哪些了
+-----------+-----------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| Procedure | sql_mode                                                                                                              | Create Procedure                                                                           | character_set_client | collation_connection | Database Collation |
+-----------+-----------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| aaa       | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION | CREATE DEFINER=`root`@`localhost` PROCEDURE `aaa`()
select count(*) from users where id>1; | utf8mb4              | utf8mb4_0900_ai_ci   | utf8mb4_0900_ai_ci |
+-----------+-----------------------------------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
1 row in set (0.00 sec)

14 上面其实，要尤其注意delimiter结束符的作用，下面例子就针对结束符来看：

mysql> delimiter //
mysql> select count(*) from users where id>1;
    -> select count(*) from users ; //
+----------+
| count(*) |
+----------+
|        3 |
+----------+
1 row in set (0.00 sec)

+----------+
| count(*) |
+----------+
|        4 |
+----------+
1 row in set (0.01 sec)