MySQL分析与整理 — 存储过程

不念不忘少年蓝@ 2023-02-27 08:05 48阅读 0赞

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpbGk0MDM0Mg_size_16_color_FFFFFF_t_70_pic_center]

> 文章中所有操作均是在 MySQL 5.7 版本下进行的

存储过程是具有特定功能的多个 SQL 语句集组成的，它存储在数据库中，一次编译后永久有效，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。存储过程是数据库中的一个重要对象。在数据量特别庞大的情况下利用存储过程能达到倍速的效率提升。

存储过程可以用流控制语句编写，有很强的灵活性，可以完成复杂的判断和较复杂的运算。存储过程被创建后，可以在程序中被多次调用，而不必重新编写该存储过程的 SQL 语句，并且可以随时对存储过程进行修改，对应用程序源代码毫无影响。存储过程是预编译的，它可以包含大量的 SQL 代码，首次运行一个存储过程时查询，优化器对其进行分析优化，并且给出最终被存储在系统表中的执行计划。数据库管理员通过执行某一存储过程的权限进行限制，能够实现对相应的数据的访问权限的限制，避免了非授权用户对数据的访问，可以保证数据安全。

### 1 使用存储过程 ###

#### 1.1 创建存储过程 ####

-- 创建语法
    delimiter $
    create procedure procedure_name(parameters)
    begin
    	procedure_body
    end
    $
    /*
    procedure_name：存储过程名
    parameters：参数列表，参数可以没有，也可以多个，详细如下：
    参数形式为三部分组成：参数传输类型，参数名，参数数据类型
    参数传输类型包括：
    	输入参数in，传统的传值参数，默认值，可省略不写
    	输出参数out，该值可在存储过程内部被改变，并可返回，如果希望存储过程有返回值可以用输出参数解决
    	输入输出参数inout，调用时指定，并且可被改变和返回
    begin ... end：存储过程代码块开始结束
    	begin ... end可嵌套（每个嵌套块及其中的每条语句，必须以分号结束，最外层begin-end存储过程块不需要分号结束）：
    		 begin
    		 	begin
    		 		begin
    		 			...
    		 		end;
    		 	end;
    		 end
    	begin ... end可设置标签（leave和iterate语句可能用到）：
            label1: begin
    			label2: begin
    				label3: begin
                          	...
    				end label3;
    			end label2;
    		end label1
    procedure_body：多个sql语句组成，包括语句控制、声明变量等
    delimiter $，自定义语句结束符为$
    不用了，记得改回来，用“delimiter ;”命令恢复为英文分号
    */

#### 1.2 查看存储过程 ####

-- 查看存储过程
    select name from mysql.proc where db = '需要查看哪个数据库' and type = 'PROCEDURE';
    /*
    show procedure status;是查看所有的存储过程
    */
    -- 查看存储过程的创建代码
    show create procedure 存储过程名;

#### 1.3 调用存储过程 ####

-- 存储过程名后必须有括号，即使存储过程没有参数传递也得有括号
    call 存储过程名(参数);

#### 1.4 删除存储过程 ####

drop procedure 存储过程名;

### 2 存储过程应用示例 ###

#### 2.1 增加示例表和示例数据 ####

-- 示例数据库和示例数据
    drop table if exists tbl_account;
    create table tbl_account(
    	id int primary key,
        aname varchar(10),
        balance double
    )ENGINE=InnoDB default charset=utf8;
    insert into tbl_account values 
    (1001, '老孙', 1000),(1002, '老唐', 2300),(1003, '老猪', 20000);
    -- 再创建一个简单示例表
    drop table if exists tbl_account_bak;
    create table tbl_account_bak(
    	id int primary key,
        info varchar(10)
    )ENGINE=InnoDB default charset=utf8;

#### 2.2 无参数的存储过程 ####

/*
    获取tbl_account最高的balance
    将id和balance存入tbl_account_bak表
    */
    drop procedure if exists proc_test1;
    delimiter $
    create procedure proc_test1()
    begin
    	-- 定义两个变量接收id和balance
    	declare rs_b double;
    	declare rs_id int;
    	-- select into语句查询到数据赋值给id和balance
    	select id, max(balance) into rs_id, rs_b from tbl_account;
    	-- 将rs_id和rs_binsert到tbl_account_bak
    	insert into tbl_account_bak values (rs_id, rs_b);
    end
    $
    delimiter ;
    
    -- 调用测试
    call proc_test1();
    -- 查询tbl_account_bak
    select * from tbl_account_bak;
    +------+-------+
    | id   | info  |
    +------+-------+
    | 1001 | 20000 |
    +------+-------+

#### 2.3 带输入参数的存储过程 ####

/*
    通过存储过程proc_test2进行insert数据
    输入参数可以省略in关键字
    */
    drop procedure if exists proc_test2;
    delimiter $
    create procedure proc_test2(id int, aname varchar(10), in balance double)
    begin
    	insert into tbl_account values (id, aname, balance);
    end
    $
    delimiter ;
    
    -- 调用测试
    call proc_test2(1004, '老沙', 12345);
    -- 查询tbl_account
    select * from tbl_account ;
    +------+--------+---------+
    | id   | aname  | balance |
    +------+--------+---------+
    | 1001 | 老孙   |    1000 |
    | 1002 | 老唐   |    2300 |
    | 1003 | 老猪   |   20000 |
    | 1004 | 老沙   |   12345 |
    +------+--------+---------+

#### 2.4 带输入参数和输出参数的存储过程 ####

/*
    通过存储过程proc_test3两个参数
    add_balance所有人都增加的余额
    max_balance返回增加后余额最多的人名字
    输出参数会自动返回数据，无需处理
    */
    drop procedure if exists proc_test3;
    delimiter $
    create procedure proc_test3(in add_balance double, out max_balance double)
    begin
    	-- 所有人增加余额
    	update tbl_account set balance = balance + add_balance;
    	-- 查询余额最多的人名赋值给输出参数
    	select max(balance) into max_balance from tbl_account;
    end
    $
    delimiter ;
    
    -- 调用测试，按照mysql规定，直接用一个@变量接收即可
    call proc_test3(1000, @balance);
    select @balance;
    +----------+
    | @balance |
    +----------+
    |    21000 |
    +----------+

### 结语 ###

以上的示例非常简单，并没有太复杂的流程控制等复杂操作，目的就是为了能让朋友们一下子就能看明白。其实平时的开发中，存储过程是相当复杂的一个代码块，代码块里除了简单的 crud 操作还会有各种流程控制语句，游标，预处理等等复杂的操作（游标和预处理可以在作者其它文章查看到）。

大部分关系型数据库都支持存储过程，不同的数据库环境语法结构有所区别。当前不管是项目还是产品开发，存储过程的应用非常广泛。

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2xpbGk0MDM0Mg_size_16_color_FFFFFF_t_70_pic_center]: /images/20230209/e45145b3879b482589b6e080f93ee1d5.png