mysql中函数、存储过程及变量的使用

向右看齐 2022-05-18 04:26 190阅读 0赞

### 1. 变量声明与赋值 ###

所有的输入输出变量 ，或者使用中的变量，在使用前，要么使用set 声明，要么使用 declare 声明,或者在变量名字前面加@符号，表示用户自定义变量，否则，会导致变量未定义错误。

单变量赋值

DECLARE @id int(16);
    set @a = '12';
    set @b = (select count(*) from user);

多变量赋值

DECLARE id int(16);
    DECLARE name varchar(128);
    select id,name into @id,@name from user where id=1;
    select  @id:=id,@name:=name from  user where id=1;

### 2. 循环的使用 ###

存储过程中的循环使用

DELIMITER $$
    DROP PROCEDURE IF EXISTS 'delete_session' $$
    CREATE DEFINER=`root`@`localhost` PROCEDURE `delete_session`(IN top int)  
    BEGIN  
      
        DECLARE done INT DEFAULT 0;   
        DECLARE temp_id INT;  
    
        DECLARE cur CURSOR for( SELECT id from user);   
        DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;  
     
        OPEN cur;  
        FETCH cur INTO temp_id;  
        WHILE done <> 1 DO  
          DELETE FROM user_session WHERE id = temp_id;  
          FETCH cur INTO temp_id;  
        END WHILE;  
    
        CLOSE cur;  
    END $$
    DELIMITER;

函数中的循环使用

-- 获取根分类中的根分类名称
    delimiter //
    drop function if exists 'getRootCateName' //
    CREATE FUNCTION getRootCateName(cate_id varchar(32))
    RETURNS varchar(128) DETERMINISTIC
    BEGIN
      select pid,category into @p_id,@cate_name from system_category where id=cate_id;
      set @temp_id = @p_id;
      while @temp_id > 0 do
         select pid,category into @p_id,@cate_name from system_category where id=@temp_id;
         set @temp_id = @p_id;
      end while;  
    RETURN @cate_name;
    end//
    delimiter ;

调用

select getRootCateName(28);

###    3. 选择语句的使用 ###

delimiter //
    create function getScoreName(score int(10))
    RETURNS varchar(128) DETERMINISTIC
    BEGIN
      if score>=90 then 
            set @grade ='A';  
        elseif score<90 and score>=80 then 
            set @grade ='B';  
        elseif score<80 and score>=70 then 
            set @grade ='C';  
        elseif score<70 and score>=60 then 
            set @grade ='D';  
        else 
            set @grade ='E';  
        end if; 
    RETURN @grade;
    END //
    delimiter ;

调用

select getScoreName(91);

### 4. 分支语句的使用 ###

delimiter //
    create function getScoreName2(score int(10))
    RETURNS varchar(128) DETERMINISTIC
    BEGIN
      SELECT case 
        when score>=90 then '甲' 
        when score>=80 then '乙' 
        when score>=70 then '丙' 
        when score>=60 then '丁' 
        else '差' 
        end as grade  into @commont_grade ;
    RETURN @commont_grade;
    END //
    delimiter ;

调用

select getScoreName2(98);

### 5. 存储过程输入输出变量的使用 ###

DELIMITER $$  
    DROP PROCEDURE IF EXISTS `user_banlance` $$  
    CREATE PROCEDURE user_banlance (IN `in_userId` INT , IN `in_banlance` INT, OUT out_code INT, OUT out_message VARCHAR(100))  
    _return:BEGIN  
    DECLARE _userId bigint(20); 
        DECLARE _banlance int; -- 余额
        DECLARE _verison INT DEFAULT 0;  
        DECLARE _error  INT DEFAULT 0;  
        DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET _error = 1;   -- 异常处理
        SET out_code = -1;
        SET out_message = '执行失败';
    
        #用户账户不存在
        IF NOT EXISTS (select id from user_banlance where id=in_userId) THEN
            SET out_code = 1;
            SET out_message = '用户不存在';
            LEAVE _return;
        END IF;
    
        select verison,banlance into _verison, _banlance from user_banlance where id=in_userId;
    
        START TRANSACTION; -- 开启事务 乐观锁的使用
        update user_banlance set banlance = banlance - in_banlance,verison = verison + 1 where id = in_userId and verison = _verison;
    
        SET @ret_update = ROW_COUNT();
        IF @ret_update = 0 THEN
            ROLLBACK;
            SET out_code = -4;
            SET out_message = '系统错误';
            LEAVE _return;
        END IF;
    
        IF  _error  <>  0   THEN
            ROLLBACK; 
            SET out_code = -3;
            SET out_message = '系统错误';
            LEAVE _return;
        ELSE    
            COMMIT;
            SET out_code = 1;
            SET out_message = '';
        END IF;
    
    END $$  
    
    DELIMITER ;

\-- 调用  注意输出参数必须是声明的变量，否则会报错

set @b = 0;
    set @c = '';
    call user_banlance(1,10,@b,@c);
    
    select @b,@c

###    6. 存储过程的使用（查询游标） ###

DELIMITER $$  
    DROP PROCEDURE IF EXISTS `user_banlance` $$  
    CREATE PROCEDURE user_banlance (IN `in_userId` INT , IN `in_banlance` INT)  
    BEGIN
      #遍历数据结束标志
      DECLARE done INT DEFAULT FALSE;
      DECLARE _id INT; 
      DECLARE _banlance BIGINT(20);
    #游标
      DECLARE cur CURSOR FOR SELECT id,banlance FROM user_banlance where id > in_userId and banlance > in_banlance ;
        #将结束标志绑定到游标
      DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    
    -- 建表tb_temp
     DROP TABLE IF EXISTS tb_temp;
     CREATE TEMPORARY TABLE tb_temp(
       `id` int UNSIGNED NOT NULL , 
       `banlance` bigint(20) NOT NULL,
       PRIMARY KEY (`id`)
     ) ENGINE = MYISAM DEFAULT charset = utf8 ;
    
      -- 打开游标
      OPEN cur;
      -- 开始循环
      read_loop: LOOP
        -- 提取游标里的数据
        FETCH cur INTO _id,_banlance; 
        IF done THEN
          LEAVE read_loop;
        ELSE 
        replace into tb_temp( id ,banlance) values(_id,_banlance);  -- 替换具有唯一索引 和 主键索引的 相同记录
        END IF;
     END LOOP;
     -- 关闭游标 
    CLOSE cur; 
    select * from tb_temp;
    DROP TABLE IF EXISTS tb_temp;
    END $$  
    
    DELIMITER ;

调用

call user_banlance(2,1500);

游标循环的另一种实现方式

-- 打开游标
    OPEN cur;
    -- 开始循环
    REPEAT
    -- 提取游标里的数据
        FETCH cur INTO _id,_banlance; 
        IF not done THEN
        insert into tb_temp( id ,banlance) values(_id,_banlance);  -- 替换具有唯一索引 和 主键索引的 相同记录
        END IF;
    UNTIL done 
    END REPEAT;
     -- 关闭游标 
    CLOSE cur;

\[注意\]：  
\[1\] fetch 必须在判断之前调用才能避免最后一条记录重复 （最后一次通过循环遍历的时候，“fetch”是失败的，如果这个时候继续处理，你会插入前一次的值。）  
\[2\] drop 和 create 等语句必须在declare 声明语句之后使用，否则会报错

### 6. 存储过程与函数区别 ###

调用 存储过程 call procedure\_name(arges, arges1...);  
      函数 select function\_name(arges);

返回 存储过程可以返回多个值，  
     函数只能有一个返回值