MySQL：PrePareSQL与存储过程

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

条件执行

流程控制语句 官方文档

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存储过程编程>