mysql 变量使用，存储过程

电玩女神 2022-08-20 03:29 281阅读 0赞

set语句的学习：  
  
使用select定义用户变量的实践  
将如下语句改成select的形式：  
 set @VAR=(select sum(amount) from penalties);  
我的修改：  
 select @VAR:=(select sum(amount) from penalties);  
  
我这样改，虽然是可以的。但是，对比的书中的答案，发现这样的方式太蹩脚了。说明没有很好地理解select定义变量的本质。  
在select中，发现有一个用户变量，如果没有定义，那么就会初始化。  
select子句原来的方式根本不受到影响。只是增加了用户变量。所以，还是按照原来的方式使用select子句。那么像：select sum(amount) from penalties。增加变量就成  
  
了:select  @VAR:=sum(amount) from penalties。  
  
将sum(amount)的结果赋给变量@VAR:。变量前面有select，那用户么就是显示该变量了。  
  
  
笔记部分：mysql变量的术语分类：  
1.用户变量：以"@"开始，形式为"@变量名"  
用户变量跟mysql客户端是绑定的，设置的变量，只对当前用户使用的客户端生效  
2.全局变量：定义时，以如下两种形式出现，set GLOBAL 变量名  或者  set @@global.变量名   
对所有客户端生效。只有具有super权限才可以设置全局变量  
  
3.会话变量：只对连接的客户端有效。  
  
4.局部变量：作用范围在begin到end语句块之间。在该语句块里设置的变量  
declare语句专门用于定义局部变量。set语句是设置不同类型的变量，包括会话变量和全局变量

通俗理解术语之间的区别：

用户定义的变量就叫用户变量。这样理解的话，会话变量和全局变量都可以是用户定义的变量。只是他们是对当前客户端生效还是对所有客户端生效的区别了。所以，用户变量包括了会话变量和全局变量

局部变量与用户变量的区分在于两点:1.用户变量是以"@"开头的。局部变量没有这个符号。2.定义变量不同。用户变量使用set语句，局部变量使用declare语句定义 3.作用范围。局部变量只在begin-end语句块之间有效。在begin-end语句块运行完之后，局部变量就消失了。

所以，最后它们之间的层次关系是：变量包括局部变量和用户变量。用户变量包括会话变量和全局变量。  
  
  
使用备忘，set @var 若没有指定GLOBAL 或SESSION ，那么默认将会定义用户变量  
两种方式定义用户变量：  
1."=",如 set @a =3,@a:=5  
2.":="。select常常这样使用  
总结：使用select 和set设置变量的区别，set可以使用以上两种形式设置变量。而select只能使用":="的形式设置变量  
实践积累：用户变量在mysql客户端退出后，会自动消失。之后我打开客户端，使用"select @a;" 显示变了的值为null。说明，未定义的变量初始化是null

实际中的问题

设置常量对group\_concat()的配置影响：  
SET @@GROUP\_CONCAT\_MAX\_LEN=4  
手册中提到设置的语法是这样的：  
SET \[SESSION | GLOBAL\] group\_concat\_max\_len = val;  
  
以下两种形式都能达到达到同样的效果，但是有什么区别？  
  
SET @@global.GROUP\_CONCAT\_MAX\_LEN=4;  
global可以省略，那么就变成了：SET @@GROUP\_CONCAT\_MAX\_LEN=4;

2011.2.25

之前的理解不怎么准确。现在对加深理解后的地方进行总结。

mysql中变量的层次关系是:大体包括用户变量和系统变量。系统变量包括系统会话变量和系统全局变量。

我是这样理解相互之间的区别：

因为用户变量就是用户定义的变量，系统变量就是mysql定义和维护的变量。所以，用户变量与系统变量的区别在于，是谁在管理这些变量。mysql一启动的时候就会读取系统变量(这样做目的是可以确定mysql的以何种机制或模式运行)。 系统会话变量与用户变量都是在当前客户端退出后消失。他们之间的区别可以这样理解，虽然常常看到"set @@varible"的形式去改变系统变量的值，但是并不涉及到定义系统变量。用户变量是可以自己定义(初始化)。系统变量按照只是在改变值。

局部变量只在begin-end语句块中定义并有效。执行到该语句块之后就消失了。定义的方式有明显的特点，使用declare语句。

为什么看到使用系统变量以"@@变量名"和"变量名"的形式出现，怎么理解两者形式的区别？

使用系统变量理论上是可以使用两种形式：1.前面带有符号"@@" 2.符号省略。比如我会看的如下形式：CURRENT\_USER。但是，约定系统变量要使用"@@变量名"的形式，就是在前面加上符号"@@"。

为什么会出现CURRENT\_USER这样没有符号的情况？看书籍《SQL For MySQL Developers A Comprehensive Tutorial and Reference》大致说明的原因，这样做是为了与其他的SQL产品保持一致。

原文  [http://blog.csdn.net/jssg\_tzw/article/details/39703889][http_blog.csdn.net_jssg_tzw_article_details_39703889]

变量可以在子程序（函数、存储过程、匿名块）中声明并使用，这些变量的作用范围是在BEGIN...END程序中。

## 定义变量 ##

## 语法格式: ##

DECLARE var_name [, var_name]... data_type [ DEFAULT value ];

var\_name为局部变量的名称；

DEFAULT value子句指定指定变量的默认值，value除了可以被声明为一个常量外，还可以被指定为一个表达式。如果没有DEFAULT子句，变量的初始值为NULL。

## 示例： ##

DECLARE myparam INT DEFAULT 100;

## 变量赋值 ##

定义变量以后，为变量赋值可以改变变量的默认值。MySQL中使用SET语句为变量赋值。

在存储程序中的SET语句是一般SET语句的扩展版本。被参考变量可能是子程序内声明的变量，或者是全局服务器变量，如系统变量或者用户变量。

## 语法1： ##

SET var_name=expr [, var_name=expr]...;

## 示例1： ##

声明3个变量，分别为var1, var2和var3，数据类型为INT，使用SET为变量赋值

DECLARE var1, var2, var3 INT;
    SET var1=10, var2=20;
    SET var3=var1+var2;

## 语法2：SELECT INTO为变量赋值 ##

SELECT INTO语法把选定的列直接存储到对应位置的变量。

MySQL中还可以通过SELECT...INTO为一个或者多个变量赋值，语法如下：

SELECT col_name[,...] INTO var_name[,...] table_expr [WHERE...];

## 示例2： ##

声明变量v\_employee\_name和v\_employee\_salary,通过SELECT...INTO语句查询指定记录并为变量赋值:

DECLARE v_employee_name VARCHAR(100);
    DECLARE v_employee_salary DECIMAL(8,4);
    
    SELECT employee_name, employee_salary INTO v_employee_name, v_employee_salary FROM employees WHERE employee_id=1;

\------------------------------------------------------------------------------------------------------------------------

如果您们在尝试的过程中遇到什么问题或者我的代码有错误的地方，请给予指正，非常感谢！

联系方式：david.louis.tian@outlook.com

版权＠：转载请标明出处！变量可以在子程序（函数、存储过程、匿名块）中声明并使用，这些变量的作用范围是在BEGIN...END程序中。

## 定义变量 ##

## 语法格式: ##

DECLARE var_name [, var_name]... data_type [ DEFAULT value ];

var\_name为局部变量的名称；

DEFAULT value子句指定指定变量的默认值，value除了可以被声明为一个常量外，还可以被指定为一个表达式。如果没有DEFAULT子句，变量的初始值为NULL。

## 示例： ##

DECLARE myparam INT DEFAULT 100;

## 变量赋值 ##

定义变量以后，为变量赋值可以改变变量的默认值。MySQL中使用SET语句为变量赋值。

在存储程序中的SET语句是一般SET语句的扩展版本。被参考变量可能是子程序内声明的变量，或者是全局服务器变量，如系统变量或者用户变量。

## 语法1： ##

SET var_name=expr [, var_name=expr]...;

## 示例1： ##

声明3个变量，分别为var1, var2和var3，数据类型为INT，使用SET为变量赋值

DECLARE var1, var2, var3 INT;
    SET var1=10, var2=20;
    SET var3=var1+var2;

## 语法2：SELECT INTO为变量赋值 ##

SELECT INTO语法把选定的列直接存储到对应位置的变量。

MySQL中还可以通过SELECT...INTO为一个或者多个变量赋值，语法如下：

SELECT col_name[,...] INTO var_name[,...] table_expr [WHERE...];

## 示例2： ##

声明变量v\_employee\_name和v\_employee\_salary,通过SELECT...INTO语句查询指定记录并为变量赋值:

\------------------------------------------------------------------------------------------------------------------------

如果您们在尝试的过程中遇到什么问题或者我的代码有错误的地方，请给予指正，非常感谢！

联系方式：david.louis.tian@outlook.com

版权＠：转载请标明出处！

最近写一些mysql的存储过程和函数，发现网上比较有价值的文档很少，大都是照着手册上抄来的，有些实际问题解决不了，比如用变量作表名。  
  
经过反复调试，总算找到解决办法，一下是一些简单的记录，比较零碎。部分内容转自http://my.opera.com/Dereky/blog/show.dml/322997  
  
  
1.用变量做表名：  
  
简单的用set或者declare语句定义变量，然后直接作为sql的表名是不行的，mysql会把变量名当作表名。在其他的sql数据库中也是如此，mssql的解决方法是将整条sql语句作为变量，其中穿插变量作为表名，然后用sp\_executesql调用该语句。  
  
这在mysql5.0之前是不行的，5.0之后引入了一个全新的语句，可以达到类似sp\_executesql的功能（仅对procedure有效，function不支持动态查询）：  
  
PREPARE stmt\_name FROM preparable\_stmt;  
EXECUTE stmt\_name \[USING @var\_name \[, @var\_name\] ...\];  
\{DEALLOCATE | DROP\} PREPARE stmt\_name;  
  
为了有一个感性的认识，  
下面先给几个小例子：  
  
mysql> PREPARE stmt1 FROM 'SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse';  
mysql> SET @a = 3;  
mysql> SET @b = 4;  
mysql> EXECUTE stmt1 USING @a, @b;  
\+------------+  
| hypotenuse |  
\+------------+  
| 5 |  
\+------------+  
mysql> DEALLOCATE PREPARE stmt1;  
  
mysql> SET @s = 'SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse';  
mysql> PREPARE stmt2 FROM @s;  
mysql> SET @a = 6;  
mysql> SET @b = 8;  
mysql> EXECUTE stmt2 USING @a, @b;  
\+------------+  
| hypotenuse |  
\+------------+  
| 10 |  
\+------------+  
mysql> DEALLOCATE PREPARE stmt2;  
  
如果你的MySQL 版本是 5.0.7 或者更高的，你还可以在 LIMIT 子句中使用它，示例如下：  
mysql> SET @a=1;mysql> PREPARE STMT FROM "SELECT \* FROM tbl LIMIT ?";  
mysql> EXECUTE STMT USING @a;  
mysql> SET @skip=1; SET @numrows=5;  
mysql> PREPARE STMT FROM "SELECT \* FROM tbl LIMIT ?, ?";  
mysql> EXECUTE STMT USING @skip, @numrows;  
  
使用 PREPARE 的几个注意点：  
A：PREPARE stmt\_name FROM preparable\_stmt;预定义一个语句，并将它赋给 stmt\_name ，tmt\_name 是不区分大小写的。  
B： 即使 preparable\_stmt 语句中的 ? 所代表的是一个字符串，你也不需要将 ? 用引号包含起来。  
C： 如果新的 PREPARE 语句使用了一个已存在的 stmt\_name ，那么原有的将被立即释放！ 即使这个新的 PREPARE 语句因为错误而不能被正确执行。  
D： PREPARE stmt\_name 的作用域是当前客户端连接会话可见。  
E： 要释放一个预定义语句的资源，可以使用 DEALLOCATE PREPARE 句法。  
F： EXECUTE stmt\_name 句法中，如果 stmt\_name 不存在，将会引发一个错误。  
G： 如果在终止客户端连接会话时，没有显式地调用 DEALLOCATE PREPARE 句法释放资源，服务器端会自己动释放它。  
H： 在预定义语句中，CREATE TABLE, DELETE, DO, INSERT, REPLACE, SELECT, SET, UPDATE, 和大部分的 SHOW 句法被支持。  
I： PREPARE 语句不可以用于存储过程，自定义函数！但从 MySQL 5.0.13 开始，它可以被用于存储过程，仍不支持在函数中使用！  
  
下面给个示例：  
CREATE PROCEDURE \`p1\`(IN id INT UNSIGNED,IN name VARCHAR(11))  
BEGIN lable\_exit:  
BEGIN  
SET @SqlCmd = 'SELECT \* FROM tA ';  
IF id IS NOT NULL THEN  
SET @SqlCmd = CONCAT(@SqlCmd , 'WHERE id=?');  
PREPARE stmt FROM @SqlCmd;  
SET @a = id;  
EXECUTE stmt USING @a;  
LEAVE lable\_exit;  
END IF;  
IF name IS NOT NULL THEN  
SET @SqlCmd = CONCAT(@SqlCmd , 'WHERE name LIKE ?');  
PREPARE stmt FROM @SqlCmd;  
SET @a = CONCAT(name, '%');  
EXECUTE stmt USING @a;  
LEAVE lable\_exit;  
END IF;  
END lable\_exit;  
END;  
CALL \`p1\`(1,NULL);  
CALL \`p1\`(NULL,'QQ');  
DROP PROCEDURE \`p1\`;  
  
了解了PREPARE的用法，再用变量做表名就很容易了。不过在实际操作过程中还发现其他一些问题，比如变量定义，declare变量和set @var=value变量的用法以及参数传入的变量。  
  
测试后发现，set @var=value这样定义的变量直接写在字符串中就会被当作变量转换，declare的变量和参数传入的变量则必须用CONCAT来连接。具体的原理没有研究。  
  
EXECUTE stmt USING @a;这样的语句USING后面的变量也只能用set @var=value这种，declare和参数传入的变量不行。  
  
另外php调用mysql存储过程的时候也碰到很多问题，总是出现PROCEDURE p can't return a result set in the given context这样的问题。  
  
\--存储过程名和参数，参数中in表示传入参数，out标示传出参数，inout表示传入传出参数

--存储过程名和参数，参数中in表示传入参数，out标示传出参数，inout表示传入传出参数
    
    create procedure p_procedurecode(in sumdate varchar(10))   
    begin
         declare v_sql varchar(500);    --需要执行的SQL语句
         declare sym varchar(6);
         
         declare var1 varchar(20);
         declare var2 varchar(70);
         declare var3 integer;
    
         --定义游标遍历时，作为判断是否遍历完全部记录的标记
         declare no_more_departments integer DEFAULT 0;     
    
         --定义游标名字为C_RESULT 
         DECLARE C_RESULT CURSOR FOR
                 SELECT barcode,barname,barnum FROM tmp_table;
    
        --声明当游标遍历完全部记录后将标志变量置成某个值
         DECLARE CONTINUE HANDLER FOR NOT FOUND
                 SET no_more_departments=1;
    
         set sym=substring(sumdate,1,6);     --截取字符串，并将其赋值给一个遍历
    
         --连接字符串构成完整SQL语句，动态SQL执行后的结果记录集，在MySQL中无法获取，因此需要转变思路将其放置到一个临时表中（注意代码中的写法）。一般写法如下：
    
         --     'Create TEMPORARY Table   表名（Select的查询语句）;
         set v_sql= concat('Create TEMPORARY Table tmp_table(select aa as aacode,bb as aaname,count(cc) as ccnum from h',sym,' where substring(dd,1,8)=''',sumdate,''' group by aa,bb)');
    
         set @v_sql=v_sql;   --注意很重要，将连成成的字符串赋值给一个变量（可以之前没有定义，但要以@开头）
         prepare stmt from @v_sql;  --预处理需要执行的动态SQL，其中stmt是一个变量
         EXECUTE stmt;      --执行SQL语句
         deallocate prepare stmt;     --释放掉预处理段
    
         OPEN C_RESULT;       --打开之前定义的游标
         REPEAT                      --循环语句的关键词
               FETCH C_RESULT INTO VAR1, VAR2, VAR3;   --取出每条记录并赋值给相关变量，注意顺序
    
              --执行查询语句，并将获得的值付给一个变量 @oldaacode（注意如果以@开头的变量可以不用通过declare语句事先声明）
               select @oldaacode:=vcaaCode from T_sum where vcaaCode=var1 and dtDate=sumdate; 
               if @oldaacode=var1 then    --判断
                  update T_sum set iNum=var3 where vcaaCode=var1 and dtDate=sumdate;
               else
                   insert into T_sum(vcaaCode,vcaaName,iNum,dtDate) values(var1,var2,var3,sumdate);
               end if;
         UNTIL no_more_departments  END REPEAT;    --循环语句结束
         CLOSE C_RESULT;                            --关闭游标
    
         DROP TEMPORARY TABLE tmp_table;       --删除临时表
    end;

[http_blog.csdn.net_jssg_tzw_article_details_39703889]: http://blog.csdn.net/jssg_tzw/article/details/39703889?utm_source=tuicool&utm_medium=referral