mysql call setcoin_mysql 存储过程当中使用动态sql语句

简单的存储过程各个关键字的用法:php

CREATE DEFINER = CURRENT_USER PROCEDURE `NewProc`(in _xnb varchar(50))

BEGIN

## 定义变量

DECLARE _num FLOAT(14,6) DEFAULT 0;

## @表示全局变量 至关于php $

## 拼接赋值 INTO 必需要用全局变量否则语句会报错

## //CONCAT会把'SELECT SUM('和_xnb和') INTO @tnum FROM btc_user_coin'拼接起来,CONCAT的各个参数中间以","号分割

SET @strsql = CONCAT('SELECT SUM(',_xnb,') INTO @tnum FROM btc_user_coin');

## 预处理须要执行的动态SQL，其中stmt是一个变量

PREPARE stmt FROM @strsql;

## 执行SQL语句

EXECUTE stmt;

## 释放掉预处理段

deallocate prepare stmt;

## 赋值给定义的变量

SET _num = @tnum;

SELECT _num

END;;

Mysql 5.0 之后，支持了动态sql语句，咱们能够经过传递不一样的参数获得咱们想要的值html

这里介绍两种在存储过程当中的动态sqljava

1.set sql = (预处理的sql语句，能够是用concat拼接的语句)mysql

set @sql = sqlsql

PREPARE stmt_nameFROM @sql;数据库

EXECUTE stmt_name;函数

{DEALLOCATE | DROP} PREPARE stmt_name;spa

过程过程示例:.net

CREATE DEFINER = `root`@`%` PROCEDURE `NewProc`(IN `USER_ID` varchar(36),IN `USER_NAME` varchar(36))

BEGIN

declare SQL_FOR_SELECT varchar(500); -- 定义预处理sql语句

set SQL_FOR_SELECT = CONCAT("select * from user where user_id = '",USER_ID,"' and user_name = '",USER_NAME,"'"); -- 拼接查询sql语句

set @sql = SQL_FOR_SELECT;

PREPARE stmt FROM @sql; -- 预处理动态sql语句

EXECUTE stmt ; -- 执行sql语句

deallocate prepare stmt; -- 释放prepare

END;

上述是一个简单的查询用户表的存储过程，当咱们调用此存储过程，能够根据传入不一样的参数得到不一样的值。code

可是：上述存储过程当中，咱们必须在拼接sql语句以前把USER_ID,USER_NAME定义好，并且在拼接sql语句以后，咱们没法改变USER_ID,USER_NAME的值，以下：

CREATE DEFINER = `root`@`%` PROCEDURE `NewProc`(IN `USER_ID` varchar(36),IN `USER_NAME` varchar(36))

BEGIN

declare SQL_FOR_SELECT varchar(500); -- 定义预处理sql语句

set SQL_FOR_SELECT = CONCAT("select * from user where user_id = '",USER_ID,"' and user_name = '",USER_NAME,"'"); -- 拼接查询sql语句

set @sql = SQL_FOR_SELECT;

PREPARE stmt FROM @sql; -- 预处理动态sql语句

EXECUTE stmt ; -- 执行sql语句

deallocate prepare stmt; -- 释放prepare

set USER_ID = '2'; -- 主动指定参数USER_ID的值

set USER_NAME = 'lisi';

set @sql = SQL_FOR_SELECT;

PREPARE stmt FROM @sql; -- 预处理动态sql语句

EXECUTE stmt ; -- 执行sql语句

deallocate prepare stmt; -- 释放prepare

END;

咱们用call aa('1','zhangsan');来调用该存储过程，第一次动态执行，咱们获得了‘张三’的信息，而后咱们在第14,15行将USER_ID，USER_NAME改成lisi，咱们但愿获得李四的相关信息，可查出来的结果依旧是张三的信息，说明咱们在拼接sql语句后，不能再改变参数了。

为了解决这种问题，下面介绍第二中方式：

2.set sql = (预处理的sql语句，能够是用concat拼接的语句，参数用 ？代替)

set @sql = sql

PREPARE stmt_nameFROM @sql;

set @var_name = xxx;

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

{DEALLOCATE | DROP} PREPARE stmt_name;

上述的代码咱们就能够改为 ：

CREATE DEFINER = `root`@`%` PROCEDURE `NewProc`(IN `USER_ID` varchar(36),IN `USER_NAME` varchar(36))

BEGIN

declare SQL_FOR_SELECT varchar(500); -- 定义预处理sql语句

set SQL_FOR_SELECT = "select * from user where user_id = ? and user_name = ? "; -- 拼接查询sql语句

set @sql = SQL_FOR_SELECT;

PREPARE stmt FROM @sql; -- 预处理动态sql语句

set @parm1 = USER_ID; -- 传递sql动态参数

set @parm2 = USER_NAME;

EXECUTE stmt USING @parm1 , @parm2; -- 执行sql语句

deallocate prepare stmt; -- 释放prepare

set @sql = SQL_FOR_SELECT;

PREPARE stmt FROM @sql; -- 预处理动态sql语句

set @parm1 = '2'; -- 传递sql动态参数

set @parm2 = 'lisi';

EXECUTE stmt USING @parm1 , @parm2; -- 执行sql语句

deallocate prepare stmt; -- 释放prepare

END;

这样，咱们就能够真正的使用不一样的参数(固然也能够在存储过程当中经过逻辑生成不一样的参数)来使用动态sql了。

几个注意：

存储动态SQL的值的变量不能是自定义变量，必须是用户变量或者全局变量   如：set sql = 'xxx';  prepare stmt from sql;是错的，正确为： set @sql = 'xxx';  prepare stmt from @sql;

即便 preparable_stmt语句中的 ? 所表明的是一个字符串，你也不须要将 ? 用引号包含起来。

若是动态语句中用到了 in ,正常写法应该这样：select * from table_name t where t.field1 in (1,2,3,4,...);

则sql语句应该这样写：set @sql = "select * from user where user_id in (?,?,?) "

由于有可能我不肯定in语句里有几个参数，因此我试过这么写

set @sql = "select * from user where user_id in (?) "

而后参数我传的是  "'1','2','3'"  我觉得程序会将个人动态sql解析出来(select * from user where user_id in ('1','2','3')) 可是并无解析出来，在写存储过程in里面的列表用个传入参数代入的时候，就须要用到以下方式：

1.使用find_in_set函数

select * from table_name t where find_in_set(t.field1,'1,2,3,4');

2.还能够比较笨实的方法，就是组装字符串，而后执行

DROP PROCEDURE IF EXISTS photography.Proc_Test;

CREATE PROCEDURE photography.`Proc_Test`(param1 varchar(1000))

BEGIN

set @id = param1;

set @sel = 'select * from access_record t where t.ID in (';

set @sel_2 = ')';

set @sentence = concat(@sel,@id,@sel_2); -- 链接字符串生成要执行的SQL语句

prepare stmt from @sentence; -- 预编释一下。 “stmt”预编释变量的名称，

execute stmt; -- 执行SQL语句

deallocate prepare stmt; -- 释放资源

END;

存储过程(无参，IN多个输入参数，OUT多个输出参数,INOUT输入输出)

https://blog.csdn.net/www1056481167/article/details/83343341

存储过程：

一、减小编译次数

二、简化操做

三、减小了变异次数减小了和数据库的连接次数，提升效率

关于存储过程的方法：

一、删除存储过程

DROP PROCEDURE 存储过程的名称;

二、查看存储过程信息

show create procedure 存储过程名称;

三、没有存储过程的修改

建立语法：

CREATE PROCEDURE 存储过程名称(参数列表)

BEGIN

方法体(存储过程体)一组方法语句;

END

⭐⭐⭐⭐注意：参数列表包括三部分

一、参数模式 参数名 参数类型

eg:  IN stuname varchar(20)

参数模式

IN :   该参数能够做为输入，也就是须要调用方传入值

OUT:   该参数做为输出，也就是该参数能够做为返回值

INOUT: 既能够做为输入参数，也能够做为输出参数

二、若是存储过程提仅仅只有一句话，那么BEGIN END 能够省略

三、存储过程当中的每一条sql语句的结尾都必须加封号，须要申明结尾符号 DELIMITER 从新设置

如下在建立的过程当中使用DELIMITER $开始，DELIMITER ;结束，是由于mysql是以;结束的，此处建立存储过程的时候须要指定结尾符号为$，整个存储过程执行完成后，再还原为mysql的结束符;便可

1、空参存储过程

DELIMITER $

CREATE PROCEDURE test1()

BEGIN

INSERT INTO admin(username,`password`) VALUES ('JOHN1','000001');

INSERT INTO admin(username,`password`) VALUES ('JOHN2','000002');

INSERT INTO admin(username,`password`) VALUES ('JOHN3','000003');

INSERT INTO admin(username,`password`) VALUES ('JOHN4','000004');

INSERT INTO admin(username,`password`) VALUES ('JOHN5','000005');

END $

调用：CALL test1()；

2、入参存储过程(此处直接举例有多个入参的存储过程的写法)

# 示例，查询是否登录成功

DELIMITER $

CREATE PROCEDURE testParams(IN username varchar(20),IN password varchar(20))

BEGIN

DECLARE RESULT VARCHAR(20) DEFAULT '';#申明并初始化

select COUNT(1) INTO RESULT #赋值将统计到的count(1) 赋值给RESULT结果

from  admin

where admin.username=username #若是参数名相同的话指明参数是那个表的字段便可

and admin.password=password;

END $

DELIMITER ;

调用：call testParams('11','22')；

3、多个出参的存储过程(此处直接举例有多个出餐的存储过程的写法)

#根据女神名，查询男神名和魅力值

DELIMITER $

CREATE PROCEDURE myp7(IN beautyName varchar(20),OUT boyName VARCHAR(20),OUT userCP varchar(20))

BEGIN

SELECT bo.boyName,bo.userCP INTO boyName,userCP #此处赋值的时候多个直接INTO为对应位置的出参对象便可

FROM boys bo

INNER JOIN beauty b on bo.id=b.boyfriend_id

WHERE

b.name=beautyName;

END $

DELIMITER ;

调用：CALL myp7('小昭',@boyName,@userCP);

#此处存储过程的两个入参(也即出参)用@符号定义便可，也能够先定义好传入到里面

SELECT @boyName AS boyName,@userCP as userCP #查询存储过程运行的结果

4、建立带有INOUT的存储过程

#案例 传入a,b 最终a,b翻倍，并返回

DELIMITER $

CREATE PROCEDURE ccgc(INOUT a int,INOUT b int)

BEGIN

SET a=a*2;

SET b=b*2;

END $

DELIMITER ;

调用：

###此处须要提早定义参数值，相似于java的入参，

SET @m=10;

SET @n=30;

call ccgc(@m,@n);

#而后调用，并打印输出

select @m,@n

---------------------