MYSQL存储过程语法和例子

学好MYSQL存储过程，函数 只看这边足够，保证各种使用没问题

1 简单例子

CREATE DEFINER=`root`@`127.0.0.1` PROCEDURE `test1`(
	IN `username` INT,
	IN `address` INT,
	OUT `ret` INT
)
LANGUAGE SQL  
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''  注释
BEGIN

DECLARE i INT DEFAULT 0;

INSERT INTO test(username,address) VALUES(username,address);

SELECT COUNT(*) INTO i FROM test ;

SET ret=i;

END

变量用@开头
存储过程没有返回数据，只有入参和出参

在sql里面 测试方式:

call test1(‘a’,‘b’,@ret);
select @ret;

通过sql语句创建存储过程 开头要加 DELIMITER $$，不同mysql版本可能 方式不一样

2 高级使用-动态游标

DELIMITER $$
CREATE DEFINER=`root`@`127.0.0.1` PROCEDURE `test2`(
	IN `param` char,
	IN `address` char,
	OUT `ret` INT
)

COMMENT ''
BEGIN
DECLARE done BOOLEAN DEFAULT 0 ;
  -- 自定义变量
  DECLARE nameCount int DEFAULT 0 ;
  DECLARE tmp int DEFAULT 0 ;
  DECLARE  my_sqll varchar(500) DEFAULT '';
  DECLARE var_pay_time TIMESTAMP DEFAULT NULL ;
  DECLARE var_product VARCHAR (100) DEFAULT NULL ;
  DECLARE var_source VARCHAR (100) DEFAULT NULL ;
  -- 声明游标
  DECLARE cur CURSOR FOR 
  -- 作用于哪个语句
  SELECT  id   FROM  warehouse  ;
  -- 设置结束标志
  -- 这条语句定义了一个 CONTINUE HANDLER，它是在条件出现时被执行的代码。这里，它指出当 SQLSTATE '02000'出现时，SET done=1 。SQLSTATE '02000'是一个未找到条件，当REPEAT由于没有更多的行供循环而不能继续时，出现这个条件
  DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1 ;
  -- 打开游标
  OPEN cur ;
  -- 使用repeat循环语法
  REPEAT
    -- 批读取数据到指定变量上
    FETCH cur INTO nameCount  ;
    -- 进行逻辑操作
            SELECT COUNT(*) INTO tmp from information_schema.tables WHERE TABLE_NAME=concat('warehouse_inv_',nameCount); 
             
              IF(tmp <=0 ) then
                    set my_sqll=concat('create table warehouse_inv_',nameCount,'(id int,inv_qty int,canuse_qty int )' );
                    set @ms=my_sqll; 
                    PREPARE s1 from @ms; 
                    EXECUTE s1; 
                    deallocate prepare s1; 
              END IF; 
    -- 循环结束条件
    UNTIL done 
  END REPEAT ;
  -- 关闭游标
  CLOSE cur ;
  
END  

使用时去除备注

3 高级用法-事务:

BEGIN
    DECLARE result_code INTEGER DEFAULT 0; -- 定义返回结果并赋初值0
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET result_code=1; -- 在执行过程中出任何异常设置result_code为1
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET result_code = 2; -- 如果表中没有下一条数据则置为2
 
    START TRANSACTION; -- 开始事务
    /*
    * 这里写具体的业务处理...
    * 业务处理过程中可以根据实际情况自定义result_code
    */
    IF result_code = 1 THEN -- 可以根据不同的业务逻辑错误返回不同的result_code，这里只定义了1和0
        ROLLBACK; 
    ELSE 
        COMMIT; 
    END IF;
SELECT result_code;
END

存储过程中 动态执行 sql可这样:

declare my_sqll varchar(500); 
  set my_sqll='select * from aa_list'; 
 set @ms=my_sqll; 
 PREPARE s1 from @ms; 
 EXECUTE s1; 
 deallocate prepare s1; 

光标里的sql 字段 最好加 别名
事务隔离级别:

//查看当前事物级别：
SELECT @@tx_isolation;

//设置read uncommitted级别：
set session transaction isolation level read uncommitted;

//设置read committed级别：
set session transaction isolation level read committed;

//设置repeatable read级别：
set session transaction isolation level repeatable read;

//设置serializable级别：
set session transaction isolation level serializable;

可加我微信 [玄玄小道] !