MySQL：PrePareSQL与存储过程

蔚落 2022-03-26 14:36 321阅读 0赞

### 存储过程 ###

*  PrePareSQL
 *   *  优点
     *  prepare 防止SQL注入
     *  prepare 实现动态查询
 *  存储过程
 *   *  优缺点
     *  查看存储过程
     *  初步使用
     *  参数
     *   *  变量
         *  参数
     *  条件执行
     *   *  if
         *  LOOP循环
         *  select 【例子待更改】
         *  使用游标 【只能一行一行的返回，不能一次返回多行】
         *  update

# PrePareSQL #

## 优点 ##

*  防止SQL注入
 *  实现动态查询

## prepare 防止SQL注入 ##

set @s = 'select * FROM employees where emp_no = ?';
     set @a = 100080;
     prepare stmt from @s;
     EXECUTE stmt using @a;  -- 传入变量
     DEALLOCATE PREPARE stmt;

备注：什么叫做SQL注入  
原本要执行

select * FROM employees where emp_no = 100080

如果被非法拼接

select * FROM employees where emp_no = 100080 or 1 = 1

其数据就会被全部偷走  
而如果使用Prepare语句

set @s = 'select * FROM employees where emp_no = ?';
     set @a = 100080 or 1=1;
     prepare stmt from @s;
     EXECUTE stmt using @a;  -- 传入变量
     DEALLOCATE PREPARE stmt;

仍然只会显示这一条语句

## prepare 实现动态查询 ##

set @s = 'select * FROM employees where 1=1';
     set @s = concat(@s, ' AND gender = "m"');
     set @s = concat(@s, ' and birth_date >= "1960-01-01"');
     prepare stmt from @s;
     EXECUTE stmt ;
     DEALLOCATE PREPARE stmt;

set @s = 'select * FROM employees where 1=1';
     set @s = concat(@s, ' AND gender = "m"');
     set @s = concat(@s, ' and birth_date >= "1960-01-01"');
     set @s = concat(@s, ' order by emp_no limit ?, ?');
     set @page_no = 0;
     set @page_count = 10;
     prepare stmt from @s;
     EXECUTE stmt using @page_count, @page_count;
     DEALLOCATE PREPARE stmt;

# 存储过程 #

## 优缺点 ##

优点：

*  节省带宽和网络延迟。如果数据库服务器和web服务器不在同一个网络上
 *  执行速度快。存储过程经过编译之后会比单独一条一条执行要快。但这个效率真是没太大影响。如果是要做大数据量的导入、同步，我们可以用其它手段。
 *  数据库服务器可以缓存存储过程的执行计划，对于反复调用的过程，会大大降低消耗

缺点：

*  调试困难
 *  不能处理复杂逻辑
 *  会给数据库服务器增加额外的压力，扩展性差

尽量不要使用存储过程

## 查看存储过程 ##

*  显示数据库的所有存储过程  
    我们可以用

select name from mysql.proc where db=’数据库名’;

或者

select routine_name from information_schema.routines where routine_schema='数据库名';

或者

show procedure status where db='数据库名';

进行查询。  
查看存储过程的具体信息

SHOW CREATE PROCEDURE 数据库.存储过程名;

## 初步使用 ##

1、创建一个存储过程

delimiter $$
    drop procedure if exists helloword$$
    create procedure helloword()
    begin
    	select "hello world";
    end$$
    DELIMITER ;

2、调用

mysql> call helloword();
    +-------------+
    | hello world |
    +-------------+
    | hello world |
    +-------------+

## 参数 ##

### 变量 ###

*  本地变量可以使用declare语句进行声明。
 *  变量名称必须遵循MYSQL列名规则，并且可以使MYSQL内建的任何数据类型。可以使用DEFAULT子句给变量一个初始值，可以使用set语句给变量赋一个新值

创建

DELIMITER $$
    DROP procedure if exists variable_demo $$
    create procedure variable_demo()
    begin
    	declare my_integer INT;
        declare my_biginteger bigint;
        declare my_pi float default 3.1415926;
    	declare my_varchar varchar(30) default "hello world";
    	
        set my_integer = 20;
        set my_biginteger = power(my_integer, 3);
    	
    	select my_integer;
    END $$
    DELIMITER ;

调用

mysql> CALL variable_demo();
    +------------+
    | my_integer |
    +------------+
    |         20 |
    +------------+

### 参数 ###

参考有三种模式

*  IN：默认模式，参数传入存储程序内部

创建

DELIMITER $$
    DROP procedure if exists my_sqrt $$
    create procedure my_sqrt(input INT)
    begin
    	declare res float;
        set res = sqrt(input);
        select res;
    END $$
    DELIMITER ;

调用

mysql> CALL my_sqrt(16);
    +------+
    | res  |
    +------+
    |    4 |
    +------+

*  OUT：返回给它的调用程序

创建

DELIMITER $$
    DROP procedure if exists my_sqrt $$
    create procedure my_sqrt(input INT, OUT outRes FLOAT)
    begin
        set outRes = sqrt(input);
    END $$
    DELIMITER ;

调用

CALL my_sqrt(9, @out_value) ;
    SELECT @out_value;

*  INOUT

## 条件执行 ##

> [流程控制语句 官方文档][Link 1]

### if ###

delimiter $$
     -- 购买量超过$500, 可以返还 20%，购买量超过$100 可以返还 10%。
    drop procedure if exists discounted_price$$
    
    create procedure discounted_price(
    	nor_price numeric(8, 2), 
        OUT discount_price numeric(8, 2))
        
        begin
    		if(nor_price>500)then
    			set discount_price =nor_price * 0.8;
    		elseif (nor_price>100)then
    			set discount_price =nor_price * 0.9;
            else
    			set discount_price = 0;
    		end if;
        end$$
    
    delimiter ;

调用查询

mysql>call discounted_price(300, @new_price) ;
    mysql> SELECT @new_price;
    +------------+
    | @new_price |
    +------------+
    |     270.00 |
    +------------+

### LOOP循环 ###

delimiter $$
    
    drop procedure if exists simple_loop$$
    
    create procedure simple_loop()
    	begin
    		declare counter int default 0;
            
            my_simple_loop:loop
    			set counter = counter + 1;
                IF counter = 10 then
    				leave my_simple_loop;
    			end if;
            end loop my_simple_loop;
            
            select counter;
        end $$
    
    delimiter ;

调用

call simple_loop();
    +---------+
    | counter |
    +---------+
    |      10 |
    +---------+

### select 【例子待更改】 ###

delimiter $$
    
    drop procedure if exists arguse_count$$
    
    create procedure arguse_count(in_point int)
    		-- reads sql data  -- [加不加无所谓]
    	begin
    		declare sum_all bigint;
    		-- select * from caseinfo limit 1; [成功]
            select count(*) into sum_all from caseinfo where point = in_point;
            
            select sum_all;
        end $$
    
    delimiter ;

执行

mysql> call arguse_count(72);
    +---------+
    | sum_all |
    +---------+
    |     165 |
    +---------+

select to 可以将一个单独的结果放入一个变量中。如果想要多记录查询，请使用游标

### 使用游标 【只能一行一行的返回，不能一次返回多行】 ###

游标可以将一行或者更多的SQL结果集放进存储程序变量中，通常用来执行结果集合中各个记录的处理

delimiter $$
    
    drop procedure if exists employees_cursor$$
    
    create procedure employees_cursor()
    		 reads sql data  -- [加不加无所谓,建议加]
    	begin
    		declare emp_no_id int;
            declare gender_sex char(2);
            declare emp_date varchar(12);
            
    		declare done int default 0;
            
            declare curl cursor for   -- 定义游标
    			select emp_no, gender, hire_date FROM employees limit 100;
            
            declare continue handler for not found set done  = 1;  -- 如果没有：ERROR 1329 (02000): No data - zero rows fetched, selected, or processed
           
           open curl; -- 打开游标
            emp_loop:loop
    			fetch curl into emp_no_id, gender_sex, emp_date;
                if done = 1 then  -- 如果它被设置成 1，那么就说明我们已经获取了最后一个数据，
    				leave emp_loop; 
                end if;
    		END loop emp_loop;
            
            select emp_no_id, gender_sex, emp_date;
        end $$
    
    delimiter ;

### update ###

delimiter $$
    
    drop procedure if exists update_salaries$$
    
    create procedure update_salaries(in_emp_no  int, in_from_date date, new_salary INT)
    		 reads sql data 
    	begin
    		update salaries set salary = new_salary where emp_no = in_emp_no and from_date = in_from_date ;
        end $$
    
    delimiter ;

测试

mysql> select * from salaries limit 1;
    +--------+--------+------------+------------+
    | emp_no | salary | from_date  | to_date    |
    +--------+--------+------------+------------+
    |  10001 |  60117 | 1986-06-26 | 1987-06-26 |
    +--------+--------+------------+------------+
    
    mysql> call update_salaries(10001, '1986-06-26', 6500);
    
    mysql>  select * from salaries limit 1;
    +--------+--------+------------+------------+
    | emp_no | salary | from_date  | to_date    |
    +--------+--------+------------+------------+
    |  10001 |   6500 | 1986-06-26 | 1987-06-26 |
    +--------+--------+------------+------------+

54页  
参考：<MYSQL存储过程编程>

[Link 1]: http://dev.mysql.com/doc/refman/5.7/en/flow-control-statements.html