MySQL存储过程

于 2022-11-28 11:08:02 发布
阅读量734 收藏 1
点赞数
分类专栏： mysql 文章标签： mysql 数据库 sql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_43442817/article/details/127806591
版权

MySQL存储过程

一、查看命令

1.1 查看全部的存储过程

show procedure status;

1.2 查看某个数据库的所有存储过程

show procedure status where db='db_name';

1.3 查看某个存储过程的详情

show create procedure pro_name;

二、语法结构

2.1 新增存储过程

CREATE
    [DEFINER = user]
	PROCEDURE sp_name ([proc_parameter[,...]])
    [characteristic ...] routine_body
    
-- proc_parameter参数部分，可以如下书写：
	[ IN | OUT | INOUT ] param_name type
	-- type类型可以是MySQL支持的所有类型
	
-- routine_body(程序体)部分，可以书写合法的SQL语句 BEGIN ... END

例如：

-- 声明结束符。因为MySQL默认使用‘;’作为结束符，而在存储过程中，会使用‘;’作为一段语句的结束，导致‘;’使用冲突
DELIMITER $$
-- 判断存储过程是否存在，存在先删除
DROP PROCEDURE IF EXISTS create_data;
CREATE
   -- databaseName为库名
    PROCEDURE databaseName.`create_data`()
    BEGIN
    -- 此处为具体需要执行的sql语句
	SELECT 'create_data';
    END$$
DELIMITER ;
-- 执行该存储过程
CALL create_data();

2.2 删除存储过程

DROP PROCEDURE IF EXISTS pro_name;

三、变量及赋值

3.1 局部变量

语法：
声明变量 
declare var_name type [default var_value];
例如：
declare count int DEFAULT 0;

set赋值

DELIMITER $$
CREATE PROCEDURE test.sp_var01()
BEGIN
	DECLARE nickname VARCHAR(32) DEFAULT 'unkown';
	SET nickname = 'ZS';
	SELECT nickname;
END$$
DELIMITER ;

into赋值

DELIMITER $$
CREATE PROCEDURE test.sp_var01()
BEGIN
	DECLARE nickname VARCHAR(32) DEFAULT 'unkown';
	SELECT 'aa' into nickname;
	SELECT nickname;
END$$
DELIMITER ;

3.2 用户变量

用户自定义的变量，当前会话有效

语法： 
@var_name
不需要提前声明，使用即声明

例如：
DELIMITER $$
CREATE PROCEDURE test.sp_var02()
BEGIN
	SET @nickname = 'zk';
END$$
DELIMITER ;
CALL sp_var02() ;
SELECT @nickname;

3.3 全局变量

由系统提供，整个mysql服务器有效

语法：
@@global.var_name

例：
-- 查看全局变量中变量名有char的记录
show global variables like '%char%'; 

-- 查看全局变量character_set_client的值
select @@global.character_set_client;

四、入参和出参

-- 语法 参数如果是varchar 要写大小
in | out | inout param_name type

4.1 in：输入数据

DROP PROCEDURE IF EXISTS `sp_param01`;

DELIMITER $$
CREATE PROCEDURE sp_param01(IN age INT)
BEGIN
	SET @user_age = age;
END$$
DELIMITER ;
CALL sp_param01(10);
SELECT @user_age;

4.2 out：只负责输出数据

-- 如果查询的是表，且表中列明和参数名相同，那么就换参数或者给列起别名
DELIMITER $$
CREATE PROCEDURE sp_param02(OUT dept_no INT(11))
BEGIN
	SET dept_no = 10;
END$$
DELIMITER ;
-- 测试
SET @dept_no = 100;
CALL sp_param02(@dept_no);
SELECT @dept_no;

4.3 inout

-- INOUT类型 
DELIMITER $$
CREATE PROCEDURE sp_param03(INOUT user_name VARCHAR(64))
BEGIN
	SET user_name = CONCAT('hello' ,user_name);
END$$
DELIMITER ;

SET @user_name = '小明';
CALL sp_param03(@user_name);
SELECT @user_name;

可以联合使用

DELIMITER $$
CREATE PROCEDURE sp_param04(IN user_name VARCHAR(64), OUT user_name1 VARCHAR(64))
BEGIN
	SET user_name1 = CONCAT('hello' ,user_name);
END$$
DELIMITER ;

SET @user_name = '小明',
@user_name1 = '小明';
CALL sp_param04(@user_name,@user_name1);
SELECT @user_name,@user_name1;

五、流程控制

5.1 判断

5.1.1 if

-- 语法
IF search_condition THEN statement_list
    [ELSEIF search_condition THEN statement_list] ...
    [ELSE statement_list]
END IF

-- 例如：
DELIMITER $$
CREATE PROCEDURE test_if()
BEGIN
	DECLARE result1 VARCHAR(20);
	IF(result1 <> '')
		THEN SET result1 = 'nq';
	ELSE
	 SET result1 = 'eq';
	END IF;
	SELECT result1;
END$$
DELIMITER ;
CALL test_if();

5.1.2 case

-- 语法一（类比java的switch）：
CASE case_value
    WHEN when_value THEN statement_list
    [WHEN when_value THEN statement_list] ...
    [ELSE statement_list]
END CASE
-- 语法二：
CASE
    WHEN search_condition THEN statement_list
    [WHEN search_condition THEN statement_list] ...
    [ELSE statement_list]
END CASE

--例：
DELIMITER $$
CREATE PROCEDURE test_case()
BEGIN
 DECLARE result1 VARCHAR(20);
 DECLARE sno VARCHAR(20);
 DECLARE sname VARCHAR(20);
 CASE WHEN TIMESTAMPDIFF(YEAR,'2001-01-01',NOW()) > 40 
 THEN SET result1 = '距2001年大于40年';
 ELSE
  SET result1 = '距2001年小于40年';
	END CASE;
SELECT no FROM test.student into sno;
CASE sno
	WHEN 1 THEN
		SET sname = 'bbb';
	ELSE
		SET sname = 'ccc';
END CASE;

SELECT result1;
SELECT sno,sname;
END$$
DELIMITER ;
CALL test_case();

5.2 循环

5.2.1 loop

-- 语法
[begin_label:] LOOP
    statement_list
END LOOP [end_label]
## loop是死循环，需要手动退出循环，我们可以使用leave来退出。
## 可以把leave看成我们java中的break；与之对应的，就有iterate（继续循环）——类比java的continue

# leave控制循环的退出
DELIMITER $$
CREATE PROCEDURE test_loop()
BEGIN
 DECLARE l_index int DEFAULT 1;
flag:LOOP
	IF l_index >= 10
		THEN LEAVE flag;# 满足条件退出循环
	END IF;
	SET l_index = l_index + 1;
END LOOP flag;
SELECT l_index;
END$$
DELIMITER ;
CALL test_loop();

# iterate + leave控制循环
DELIMITER $$
CREATE PROCEDURE test_loop2()
BEGIN
 DECLARE l_index int DEFAULT 1;
flag:LOOP
	SET l_index = l_index + 1;
	IF l_index < 10
		THEN ITERATE flag;# 满足条件继续循环
	END IF;
	# 当l_index>=10时退出循环
	LEAVE flag;
END LOOP flag;
SELECT l_index;
END$$
DELIMITER ;
CALL test_loop2();

5.2.2 repeat

-- 语法
[begin_label:] REPEAT
    statement_list
UNTIL search_condition	-- 直到…为止，才退出循环
END REPEAT [end_label]

# 例：
DELIMITER $$
CREATE PROCEDURE test_repeat()
BEGIN
 DECLARE l_index int DEFAULT 1;
REPEAT
	SET l_index = l_index + 1;
UNTIL l_index >= 10 END REPEAT;
SELECT l_index;
END$$
DELIMITER ;
CALL test_repeat();

5.2.3 while

-- 语法
[begin_label:] WHILE search_condition DO
    statement_list
END WHILE [end_label]
# 类似于java的while循环

# 例：
DELIMITER $$
CREATE PROCEDURE test_while()
BEGIN
 DECLARE l_index int DEFAULT 1;
WHILE l_index < 10 DO
	SET l_index = l_index + 1;
END WHILE;

SELECT l_index;
END$$
DELIMITER ;
CALL test_while();

5.3 游标和HANDLER

用游标得到某一个结果集，逐行处理数据。
类比jdbc的ResultSet
特别注意：
在语法中，变量声明、游标声明、handler声明是必须按照先后顺序书写的，否则创建存储过程出错。

-- 声明语法
DECLARE cursor_name CURSOR FOR select_statement
-- 打开语法
OPEN cursor_name
-- 取值语法
FETCH cursor_name INTO var_name [, var_name] ...
-- 关闭语法
CLOSE cursor_name

例子

CREATE TABLE `dept` (
	`deptno` INT(11) NOT NULL COMMENT '部门编号',
	`dname` VARCHAR(32) NULL COMMENT '部门名称' COLLATE 'utf8_general_ci',
	`loc` VARCHAR(64) NULL COMMENT '部门地址' COLLATE 'utf8_general_ci',
	PRIMARY KEY (`deptno`) USING BTREE
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
;

INSERT INTO `dept`(`deptno`, `dname`, `loc`) VALUES (1, '部门1', '111');
INSERT INTO `dept`(`deptno`, `dname`, `loc`) VALUES (2, '部门2', '222');
INSERT INTO `dept`(`deptno`, `dname`, `loc`) VALUES (3, '部门3', '333');
	
CREATE TABLE `emp` (
	`empno` INT(11) NOT NULL COMMENT '员工编号',
	`ename` VARCHAR(32) NULL COMMENT '员工姓名' COLLATE 'utf8_general_ci',
	`deptno` INT(11) NOT NULL COMMENT '部门编号',
	PRIMARY KEY (`empno`) USING BTREE,
	INDEX `FK_emp_dept` (`deptno`) USING BTREE
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
;

-- 按照部门名称查询员工，通过select查看员工的编号、姓名、薪资。（注意，此处仅仅演示游标用法）
DELIMITER $$
DROP PROCEDURE IF EXISTS test_handle;
CREATE PROCEDURE test_handle()
BEGIN
	-- 定义变量
	DECLARE flag int DEFAULT 0;
	DECLARE deptNumber INT(11);# 用deptNo变量名时无法正确映射
	-- 定义游标，并将sql结果集赋值到游标中，deptInfo为游标名
	DECLARE deptInfo CURSOR FOR SELECT deptno from dept;
	-- 声明当游标遍历完后将标志变量置为某个值
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET flag = 1;
	-- 打开游标
	OPEN deptInfo;
	-- 将游标中的值赋值给变量，注意：变量名不要与sql返回的列名相同，变量顺序要和sql结果列的顺序一致
	FETCH deptInfo INTO deptNumber;
	WHILE flag <> 1 DO
	INSERT INTO emp ( `empno`, `ename`, `deptno` ) VALUES(RAND()*9000, UUID(), deptNumber);
	-- 当s等于1时代表遍历已完成，退出循环
	FETCH deptInfo INTO deptNumber;
END WHILE;
	-- 关闭游标
	CLOSE deptInfo;
END$$
DELIMITER ;
CALL test_handle();

参考文章：MySQL存储过程语法

sentryY
关注
  • 0
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
专栏目录
mysql存储过程
06-19
良好的语法提示与代码格式化及强悍的数据库管理功能。
MySQL 存储过程（超详细）
最新发布
小钟不想敲代码
03-07 2079
MySQL 存储过程（超详细）
mysql存储过程创建表
weixin_56542271的博客
11-26 1603
mysql存储过程创建表
Mysql存储过程
01-31
我们常用的操作数据库语言SQL语句在执行的时候需要要先编译，然后执行，而存储过程（Stored Procedure）是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中，用户通过指定存储过程的名字并给定参数（如果该...
MySQL 存储过程
07-24
Mysql存储过程的使用方式、使用方法、常用函数、参考文档
C#中调用MySQL存储过程的方法
09-03
主要介绍了C#中调用MySQL存储过程的方法,涉及C#数据库操作的相关技巧,具有一定参考借鉴价值,需要的朋友可以参考下
Mysql存储过程和函数区别介绍
09-10
主要介绍了Mysql存储过程和函数的区别,需要的朋友可以参考下
理解MySQL存储过程和函数
09-10
主要帮助大家学习理解MySQL存储过程和函数，感兴趣的小伙伴们可以参考一下
MySQL之存储过程按月创建表的方法步骤
09-14
主要介绍了MySQL之存储过程按月创建表的方法步骤，文中通过示例代码介绍的非常详细，对大家的学习或者工作具有一定的参考学习价值，需要的朋友们下面随着小编来一起学习学习吧
mysql存储过程循环表
12-15
一个简单易理解的mysql存储过程 循环表操作 使用游标
MYSQL存储过程
热门推荐
aiqi6666的博客
06-23 1万+
本文实例来自于易百教程 一.存储过程的定义： 存储过程是存储在数据库目录中的一段声明性SQL语句，优点有： 通常存储过程有助于提高应用程序的性能 存储过程有助于减少应用程序和数据库服务器之间的流量 存储的程序对任何应用程序都是可重用的和透明的 存储的程序是安全的 二.存储过程入门： 第一个存储过程 说明：DELIMITER 定好结束符为”//”, 然后最后又定义为”;”，//...
MySQL存储过程详解(实用)
Java方向程序员_张帅鹏
11-18 626
1、存储过程简介 一个存储过程是一个可编程的函数，它在数据库中创建并保存。 (1)存储过程增强了SQL语言的功能和灵活性 (2)存储过程允许标准组件是编程。 (3)存储过程能实现较快的执行速度   预编译。 比批处理快 (4)存储过程能够减少网络流量 (5)存储过程可被作为 2、创建存储过程 (1) delimiter //   从新设置分隔符  表示;不代表语句结束。 (2)c

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值