Mysql存储过程二

1、MySQL中创建存储过程时通过DEFINER和SQL SECURITY设置访问权限

procedure与function、trigger等创建时紧接着CREATE都有个definer可选项，该definer规定了访问该procedure等的安全控制

CREATE DEFINER=`root`@`%` FUNCTION `f_emp_get_shift_time`(`@attent_date` date,
`@shift` VARCHAR(50),
`@type` VARCHAR(50)) RETURNS datetime

BEGIN
   ......
END;

 

CREATE DEFINER=`root`@`%` PROCEDURE `p_asset_setItManager`(`uuid` varchar(50),`user_id_par` varchar(20),`apply_no_par` varchar(20), `table_name` varchar(50))
BEGIN
   ......
END;

上面示例指定definer为用户

root`@`%`

，所以任意用户A访问该PROCEDURE 时，能否成功取决于A是否有调用该PROCEDURE的权限，以及definer是否有procedure中的SELECT的权限。

 

2、mysql 存储过程中的 prepare语句(存储过程中动态增减表字段)

在存储过程中创建一张临时表,然和在动态增减临时表的字段,所以就用到了mysql的prepare预编译语句。

基本语法：

PREPARE stmt from '你的sql语句';
EXECUTE stmt (如果sql有参数的话, USING xxx,xxx); // 这里USING的只能是会话变量
DEALLOCATE PREPARE stmt; 

这三句话分别就是预定义好sql.
执行预定义的sql
释放掉数据库连接
使用这个语法便可以在存储过程中写一些ddl语句，但是在网上看到的是在存储过程中最好是不要写ddl,因为ddl操作会锁表，总之就是不建议在存储过程中去更改表结构。不过我们这里是对临时表的改变，是不影响的啦。

• 他还可以在存储过程中动态的拼接表名，字段名，来达到动态查询的效果
• sql语句中还可以用?来代表参数，这样可以有效的防止sql注入

  delimiter //
  create procedure myTest()
  begin
  set @_sql = 'select ? + ?';
  set @a = 5;
  set @b = 6;
  PREPARE stmt from @_sql; // 预定义sql
  EXECUTE stmt USING @a,@b;// 传入两个会话变量来填充sql中的 ?
  DEALLOCATE PREPARE stmt; // 释放连接
  end //

  调用上面的存储过程，会得到11的结果，就是这么简单，关于存储过程我的其他博客里面有，可以去看，值得一提的是，如果是要动态的选择表名，表名并不能用 ? 来当占位符。我们只能采用字符串拼接的方法。

• delimiter //
  create procedure myTest(in columnName varchar(32)) // 传入一个字符串
  BEGIN
  drop table if exists tmpTable; // 如果临时表存在先删除掉
  set @_sql = concat('create temporary table if not exists tmpTable( ', columnName, ' varchar(32), id int(11), _name varchar(32));'); 
  
  // 创建临时表的语法，我们把传入的参数拼接进来
  PREPARE stmt from @_sql;    
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;  // 执行
  desc tmpTable;
  end //

  以上存储过程我们可以看到我们传入的字符串可以动态的添加到临时表里面去。
  创建临时表时还可以直接从结果集创建。 create temporary table tmpTable select * from tableName;

3、MySQL-存储过程-游标 CURSOR FOR

1，游标

  游标是一个存储在MySQL服务器上的数据库查询，它不是一条select语句，而是被该语句所检索出来的结果集。

2，定义游标

  这个过程并没有检索到数据，只是定义要使用的select语句

  DECLARE t_cursor CURSOR FOR SELECT t.id FROM t_dept t;

2，定义游标

  这个过程并没有检索到数据，只是定义要使用的select语句

  DECLARE t_cursor CURSOR FOR SELECT t.id FROM t_dept t;

3，如果没有数据返回或者select出现异常,程序继续,并将变量done设为true ，

  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=true;

3，打开游标

  open t_cursor;

4，使用游标

  使用fetch来取出数据

  fetch t_cursor in variable;

5，关闭游标

  close t_cursor;

过程：定义游标（使用游标前必须先定义游标）—》打开游标—》关闭游标

 

 

MySQL 预处理语句prepare、execute、deallocate的使用

PREPARE stmt_name FROM preparable_stmt

EXECUTE stmt_name
    [USING @var_name [, @var_name] ...]    -

{DEALLOCATE | DROP} PREPARE stmt_name

mysql> PREPARE pr1 FROM 'SELECT ?+?';
Query OK, 0 rows affected (0.01 sec)
Statement prepared

mysql> SET @a=1, @b=10 ;
Query OK, 0 rows affected (0.00 sec)

mysql> EXECUTE pr1 USING @a, @b;
+------+
| ?+?  |
+------+
| 11   |
+------+
1 row in set (0.00 sec)

mysql> EXECUTE pr1 USING 1, 2;    -- 只能使用用户变量传递。
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the 
right syntax to use near '1, 2' at line 1

mysql> DEALLOCATE PREPARE pr1;
Query OK, 0 rows affected (0.00 sec)

使用PAREPARE STATEMENT，可以减少每次执行SQL的语法分析，
比如用于执行带有WHERE条件的SELECT和DELETE，或者UPDATE，或者INSERT，只需要每次修改变量值即可。
同样可以防止SQL注入，参数值可以包含转义符和定界符。
适用在应用程序中，或者SQL脚本中均可。