mysql - 存储过程procedure

目录

1.创建存储过程

2.管理存储过程

3.存储过程定义变量

4.存储过程实例

---

1.创建存储过程

(1)创建语法

CREATE
    [DEFINER = { user | CURRENT_USER }]
    PROCEDURE sp_name ([proc_parameter[,...]])
    [characteristic ...] routine_body

proc_parameter:
    [ IN | OUT | INOUT ] param_name type

func_parameter:
    param_name type

type:
    Any valid MySQL data type

characteristic:
    COMMENT 'string'
  | LANGUAGE SQL
  | [NOT] DETERMINISTIC
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }

routine_body:
    Valid SQL routine statement

 

(2)DEFINER

DEFINER用于指明存储过程是由哪个用户定义的，默认存储过程的定义者是存储过程，跟存储过程的使用权限无关。

格式：DEFINER = { user | CURRENT_USER }]

(1)默认当前用户：DEFINER =CURRENT_USER

(2)指定其他用户：'user_name'@'host_name'

例如：

说明：上面示例指定definer为用户'admin'@'localhost'，所以任意用户A访问该PROCEDURE 时，能否成功取决于A是否有调用该PROCEDURE的权限，以及definer是否有procedure中的SELECT的权限。

 

(3)SQL SECURITY

DEFINER默认为当前用户，也可指定其他用户。如果想通过访问者来判断是否具有访问该PROCEDURE 的权限，则可用SQL SECURITY指定。

(1)SQL SECURITY DEFINER : 按定义者权限来执行存储过程

(2)SQL SECURITY INVOKER（默认状况) :按调用者权限来执行这个程序, 

例如:

说明：该示例虽然指定了DEFINER ，但同时也指定了SQL SECURITY 类型为INVOKER ，SQL SECURITY 优先级高，所以安全类型为INVOKER，用户能否访问取决于用户是否有执行该PROCEDURE 的权限及该PROCEDURE 中的SELECT 权限（与select操作的表有关）。

当然，也可用SQL SECURITY 指定DEFINER：SQL SECURITY DEFINER

invoker 调用-- 按调用者权限来执行这个程序, 默认状况

definer 定义-- 按定义者权限来执行这个程序.

 

(3)IN,OUT,INOUT参数

存储过程根据需要可能会有输入、输出、输入输出参数，如果有多个参数用","分割开。MySQL存储过程的参数用在存储过程的定义，共有三种参数类型,IN,OUT,INOUT:

• IN参数的值必须在调用存储过程时指定，在存储过程中修改该参数的值不能被返回，为默认值
• OUT:该值可在存储过程内部被改变，并可返回
• INOUT:调用时指定，并且可被改变和返回

MySQL 存储过程参数如果不显式指定“in”、“out”、“inout”，则默认为“in”。习惯上，对于是“in” 的参数，我们都不会显式指定。

 

(5)其他注意点

• MySQL 存储过程名字后面的“()”是必须的，即使没有一个参数，也需要“()”;
• MySQL 存储过程中的每条语句的末尾，都要加上分号 “;”
• MySQL 存储过程中,int不用指定长度, 其他要指定长度;
• MySQL 存储过程中的变量，不需要在变量名字前加“@”;
• MySQL 存储过程的参数不能指定默认值;
• MySQL 存储过程不需要在 procedure body 前面加 “as”;
• 如果 MySQL 存储过程中包含多条 MySQL 语句，则需要 begin end 关键字;
• 不能在 MySQL 存储过程中使用 “return” 关键字.

 

2.管理存储过程

(1)查看过程创建脚本

show create procedure 存储过程名;

(2)删除存储过程

drop procedure if exists myPro;

(3)查看存储过程的信息：

show procedure status like '存储过程名'\G

(4)查看存储过程的Definer信息：

select db,name,type,definer from mysql.proc

where name='存储过程名' and type='PROCEDURE';

(5)修改存储过程的definer 

update mysql.proc set definer='root@localhost' where db='db_name'; 

(6)修改sql security 

alter procedure pro_name sql security invoker; 

(7)调用存储过程

call proc_name(参数);

 

3.存储过程定义变量

变量可以在子程序中声明并使用，这些变量的作用范围是在BEGIN...END程序中

(1)定义变量

DECLARE var_name[,varname]...date_type[DEFAULT VALUE];

(2)变量赋值

•  SET 变量名 = 表达式值 [,variable_name = expression ...];

被SET的变量可能是子程序内的变量，或者是全局服务器变量，如系统变量或者用户变量

• MYSQL中还可以通过SELECT...INTO为一个或多个变量赋值

DECLARE NAME CHAR(50);
DECLARE id DECIMAL(8,2);
SELECT id,NAME INTO id ,NAME FROM t3 WHERE id=2;

 

(3)存储过程定义变量两种方式

• 使用set或select直接赋值，变量名以 @ 开头.

例如:set @var=1; 可以在一个会话的任何地方声明，作用域是整个会话，称为会话变量(用户变量)。
SELECT 'Hello World' into @x;
SELECT @x;
SET @y='Goodbye Cruel World';
SELECT @y;
SET @z=1+2+3;
SELECT @z;

• 以 DECLARE 关键字声明的变量，只能在存储过程中使用，称为存储过程变量

例如：DECLARE var1  INT DEFAULT 0;   主要用在存储过程中，或者是给存储传参数中。

两者的区别是： 在调用存储过程时，以DECLARE声明的变量都会被初始化为 NULL。而会话变量（即@开头的变量）则不会被再初始化，在一个会话内，只须初始化一次，之后在会话内都是对上一次计算的结果，就相当于在是这个会话内的全局变量。

 

4.存储过程实例

(1)创建存储过程案例
drop PROCEDURE if exists uds.pro_amz_storage_fee_update;
DELIMITER //
CREATE PROCEDURE uds.pro_amz_storage_fee_update(in in_date varchar(20))
SQL SECURITY DEFINER
  BEGIN
    declare out_date  date;
    declare data_date date;
    --
    set sql_safe_updates=0;
     xxxx statement;
    END;
    //
DELIMITER ;

 

(2)动态存储过程

drop PROCEDURE if exists double_data_out;
DELIMITER //
CREATE PROCEDURE double_data_out(table_name varchar(255), column_name varchar(255))
  BEGIN
-- 定义sql
   set @STMT:=CONCAT(" select * from ",table_name," where ",column_name,
   " in (select ",column_name," from ",table_name," group by ",column_name,
   " having count( ",column_name," )>1 ) ",'order by ',column_name,";"); 
   prepare stmt from @stmt;  -- 预处理需要执行的动态SQL，
   EXECUTE stmt;
   deallocate prepare stmt;     -- 释放掉预处理段
  COMMIT;
   END;
  //

 

(3)存储过程中使用事务(transaction)
MYSQL的事务处理主要有两种方法
1)用begin,rollback,commit来实现
begin开始一个事务 / rollback事务回滚 / commit 事务确认
2)直接用set来改变mysql的自动提交模式
#mysql默认是自动提交
set autocommit = 0 禁止自动提交
set autocommit = 1 开启自动提交
但注意当set autocommit = 0后，所有sql都将作为事务处理，直到用commit确认或 rollback才结束。
MYSQL只有 INNODB和BDB类型的数据表才支持事务处理，其他的类型是不支持的!
(3)存储过程中使用事务
create procedure test_sp1( )    
    begin    
    declare t_error integer default 0;    
    declare continue handler for sqlexception set t_error=1;  
    -- 开始事务  
    start transaction;    
        insert into test values(null, 'test sql 001');       
        insert into test values('1', 'test sql 002');       
    if t_error = 1 then    
        rollback;    
    else    
        commit;    
    end if;    
   select t_error;  
end