mysql创建存储过程
  fztgkkRjHIsV 2023年11月09日 31 0



一、变量的使用

1.用户变量:以”@”开始,形式为”@变量名。” 

用户变量跟MySQL客户端是绑定的,设置的变量,只对当前用户使用的客户端生效,使用如下图: 

mysql创建存储过程_存储过程

 

2.全局变量:定义时,以如下两种形式出现,set GLOBAL 变量名 或者 set @@global.变量名。show global variables; 

对所有客户端生效。只有具有super权限才可以设置全局变量。 

3.会话变量:只对连接的客户端有效。一旦客户端失去连接,变量失效。show session variables; 

4.局部变量:作用范围在begin到end语句块之间。在该语句块里设置的变量declare语句专门用于定义局部变量。DECLARE l_numeric number(8,2) DEFAULT 9.95;

全局变量和会话变量的区别:全局变量在MySQL启动的时候由服务器自动将它们初始化为默认值,这些默认值可以通过更改my.ini这个文件来更改。会话变量在每次建立一个新的连接的时候,由MYSQL来初始化。MYSQL会将当前所有全局变量的值复制一份。来做为会话变量。全局变量与会话变量的区别就在于,对全局变量的修改会影响到整个服务器,但是对会话变量的修改,只会影响到当前的会话(也就是当前的数据库连接)。非root用户在修改全局变量时会报没有权限,在修改会话变量时也需要注意,有些变量是不能修改的,只能由root用户进行修改,例如:event_scheduler。

二、存储过程的优点

  1. 存储过程增强了SQL语言的功能和灵活性。存储过程可以用流控制语句编写,有很强的灵活性,可以完成复杂的判断和较复杂的运算。
  2. 当SQL语句有变动时,可以只修改数据库中的存储过程而不必修改代码。
  3. 存储过程能实现较快的执行速度。如果某一操作包含大量的SQL代码或分别被多次执行,那么存储过程要比批处理的执行速度快很多。因为存储过程是预编译的。在首次运行一个存储过程时查询,优化器对其进行分析优化,并且给出最终被存储在系统表中的执行计划。而批处理的SQL语句在每次运行时都要进行编译和优化,速度相对要慢一些。
  4. 减少网络传输。在客户端调用一个存储过程当然比执行一串SQL传输的数据量要小。
  5. 存储过程可被作为一种安全机制来充分利用。系统管理员通过执行某一存储过程的权限进行限制,能够实现对相应的数据的访问权限的限制,避免了非授权用户对数据的访问,保证了数据的安全。

三、存储过程的参数

MySQL存储过程的参数用在存储过程的定义,共有三种参数类型,IN,OUT,INOUT,形式如: 
CREATE PROCEDURE([[IN |OUT |INOUT ] 参数名 数据类型…]) 
IN 输入参数:表示该参数的值必须在调用存储过程时指定,在存储过程中修改该参数的值不能被返回,为默认值 
OUT 输出参数:该值可在存储过程内部被改变,并可返回 
INOUT 输入输出参数:调用时指定,并且可被改变和返回

四、存储过程实例

  • if else end语句
DROP PROCEDURE IF EXISTS `prc_test1`;

CREATE DEFINER = `root`@`localhost` PROCEDURE `prc_test1`(in parameter int)
BEGIN
     declare var int;  
     set var=1;  
     if var=0 then 
     set parameter=11;  
     elseif var=1 then
     set parameter=22;  
     else 
     set parameter=33; 
     end if;
     select parameter;
END;
  • case语句
BEGIN
     declare var int;  
     set var = parameter; 
     case var
     when 1 then  
     set parameter=11;  
     when 2 then
     set parameter=22;  
     else 
     set parameter=33; 
     end case;
     select parameter;
END
  • while do … end while语句
BEGIN
     declare var int;  
     set var = parameter; 
     while var>0 do
     set var = var - 1; 
     set parameter = parameter -2; 
     end WHILE;
     select parameter;
END;
  • repeat … until end repeat
BEGIN
     declare var int;  
     set var = parameter; 
     REPEAT
     set var = var - 1; 
     set parameter = parameter -2; 
     UNTIL var<0
     end REPEAT;
     select parameter;
END
  • loop ··· end loop
BEGIN
     declare var int;  
     set var = parameter; 
     LOOP_LABLE:loop
     set var = var - 1; 
     set parameter = parameter -2; 
     if var<0 THEN
   LEAVE LOOP_LABLE;
     END IF;
     end LOOP;
     select parameter;
END

标号可以用在begin repeat while 或者loop 语句前,语句标号只能在合法的语句前面使用。可以跳出循环,使运行指令达到复合语句的最后一步。如上面的loop示例。

  • 游标的使用 
    1、定义游标 
    2、打开游标 
    3、使用游标 
    4、关闭游标
DROP PROCEDURE IF EXISTS `prc_test1`;

CREATE DEFINER = `root`@`localhost` PROCEDURE `prc_test1`()
BEGIN
     DECLARE var int;   
     /**跳出循环标识**/
   DECLARE done INT DEFAULT FALSE;
     /**声明游标**/
     DECLARE cur CURSOR FOR select age from person;
   /**循环结束设置跳出标识**/
     DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
   /**打开游标**/
   OPEN cur;

     LOOP_LABLE:loop
        FETCH cur INTO var;
        select var;
     if done THEN
   LEAVE LOOP_LABLE;
     END IF;
     end LOOP;
     /**关闭游标**/
   CLOSE cur;
END;
  • 存储过程中变量赋值
语法1:
SET var_name=expr [, var_name=expr]...;
语法2:
SELECT col_name[,...] INTO var_name[,...] from table [WHERE...];



五、实例


java Service 调用处理


public Integer updatePayMoneyByProceedure(Map<String, Object> moneyPay) {
		int updateRow = -1;
		userPayMapper.updatePayMoneyByProceedure(moneyPay);
		Object NewRecNo = moneyPay.get("result");
		updateRow = (Integer) NewRecNo;
		return updateRow;
}



mybatis调用方式


<select id="updatePayMoneyByProceedure" parameterType="java.util.HashMap" statementType="CALLABLE">
    <![CDATA[
           {
           call UserPayUpdateMoney (
            #{user_id_in,mode=IN,jdbcType=INTEGER},
            #{pay_money_in,mode=IN,jdbcType=INTEGER},
            #{result,mode=OUT,jdbcType=INTEGER}
            )
           }
       ]]>
  </select>



存储过程

BEGIN
  
  START TRANSACTION;
        select pay_money into result from user_pay
            where user_id=user_id_in  for update;
        IF result IS NULL then
            insert into user_pay (user_id,pay_money, pay_give_money, create_time)
                values(user_id_in,0, 0, current_timestamp());
            select pay_money into result from user_pay
                where user_id=user_id_in for update;
        end if;
        update user_pay set pay_money = pay_money+pay_money_in,
        pay_give_money=pay_give_money+pay_give_money_in,
        update_time=current_timestamp()
            where user_id=user_id_in;
      SELECT pay_money+pay_give_money into money_out from user_pay 
        where user_id=user_id_in;
            SET result=ROW_COUNT();
      SET money_out = money_out;
  commit;
    END



msyql 存储过程创建

/**
*====> User_Pay中Money存储过程 start
*/

DELIMITER 

CREATE PROCEDURE  `chatdb`.`UserPayUpdateMoney`(
        IN `user_id_in` INTEGER,
        IN `pay_money_in` INTEGER,
        OUT `result` INTEGER
    )
BEGIN
DECLARE var INT DEFAULT -1;
 
    START TRANSACTION;
    
        select pay_money into result from user_pay
            where user_id=user_id_in  for update;
        IF result IS NULL then
            insert into user_pay (user_id,pay_money, pay_give_money, create_time)
                values(user_id_in,0, 0, current_timestamp());
            select pay_money into result from user_pay
                where user_id=user_id_in for update;
        end if;
         
 set var=result+pay_money_in;

 IF var>-1 then
     update user_pay set pay_money = pay_money+pay_money_in,
        update_time=current_timestamp()
            where user_id=user_id_in;
            SET result=ROW_COUNT();
 ELSE
            SET result=-1;
 END IF;        
 
    commit;
    END


DELIMITER ;

/**
*====> User_Pay中Money存储过程 end
*/


mysql创建存储过程_存储过程_02




【版权声明】本文内容来自摩杜云社区用户原创、第三方投稿、转载,内容版权归原作者所有。本网站的目的在于传递更多信息,不拥有版权,亦不承担相应法律责任。如果您发现本社区中有涉嫌抄袭的内容,欢迎发送邮件进行举报,并提供相关证据,一经查实,本社区将立刻删除涉嫌侵权内容,举报邮箱: cloudbbs@moduyun.com

  1. 分享:
最后一次编辑于 2023年11月09日 0

暂无评论

推荐阅读
fztgkkRjHIsV