当前位置: 澳门新濠3559 > 数据库 > 正文

但是存储过程上变量只能作用于begin...end块中,

时间:2019-11-09 19:34来源:数据库
在MySQL/MariaDB中有好几种变量类型:用户自定义变量、系统变量、一般的临时变量(即本地变量,或称为局部变量)。 说明:现在市面上定义变量的教程和书籍基本都放在存储过程上说明,

在MySQL/MariaDB中有好几种变量类型:用户自定义变量、系统变量、一般的临时变量(即本地变量,或称为局部变量)。

说明:现在市面上定义变量的教程和书籍基本都放在存储过程上说明,但是存储过程上变量只能作用于begin...end块中,而普通的变量定义和使用都说的比较少,针对此类问题只能在官方文档中才能找到讲解。

1.基本用法

drop PROCEDURE if EXISTS sp1; -- 如果存在sp1存储过程则删除掉

create PROCEDURE sp1() SELECT 1; --创建最简单的存储过程,其中存储过程必须加(),调用的时候也一样。

call sp1();  --调用存储过程

show procedure status; --查看所有的存储过程信息

show create procedure sp1; --查看某个存储过程

*注意:存储过程之间可以调用,不可以删除。

简单创建:

drop PROCEDURE if EXISTS sp1;
create PROCEDURE sp1() SELECT * from emp;  --相当于一般的查询语句。

alter procedure : 修改存储过程仅能修改以下的characteristic。(即修改权限可以,存储内容无法修改)

characteristic

ALTER {PROCEDURE | FUNCTION} sp_name [characteristic ...

characteristic:

    { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }

  | SQL SECURITY { DEFINER | INVOKER }

  | COMMENT 'string'

Alter procedure sp3 READS SQL DATA
SQL SECURITY DEFINER;

SELECT SPECIFIC_NAME,SQL_DATA_ACCESS,
SECURITY_TYPE FROM information_schema.Routines WHERE ROUTINE_NAME='emp';

   注意:delimiter 分割符使用,sql命令中因";"为默认的分隔符,也是执行语句,因此无法编写存储过程。这个时候就要重新定义分割符。

mysql> select * from emp;
+--------+----------+----------+---------+
| emp_id | emp_name | emp_type | emp_age |
+--------+----------+----------+---------+
| 1 | 张三 | 临时工 | 20 |
| 2 | 李四 | 正式工 | 40 |
| 3 | 王五 | 合同工 | 25 |
+--------+----------+----------+---------+
3 rows in set

mysql> delimiter //
mysql> select * from emp//
+--------+----------+----------+---------+
| emp_id | emp_name | emp_type | emp_age |
+--------+----------+----------+---------+
| 1 | 张三 | 临时工 | 20 |
| 2 | 李四 | 正式工 | 40 |
| 3 | 王五 | 合同工 | 25 |
+--------+----------+----------+---------+
3 rows in set

mysql>

T-SQL语句用于管理SQL Server数据库引擎实例,创建和管理数据库对象,以及查询、插入、修改和删除数据。

1.用户变量

用户变量是基于会话的,也是基于用户的,所以我觉得称之为会话变量更合适,但会话变量一般用来表示系统会话变量(后面会说明),所以还是称之为用户变量好了。

只有本用户才能引用自身的用户变量,其他用户无法引用,且当用户退出会话时,用户变量自动销毁。

用户变量使用"@"开头,用户变量可以直接赋值,无需事先声明。在引用未赋值的用户变量时,该变量值为null

有以下三种方式设置用户变量:

  1. set语句,此时可以使用"="或者":="操作符;
  2. select语句,此时只能使用":="格式赋值,因为除了set语句中,"="都会被视为比较操作符。;
  3. select ... into var_name from TABLE语句,此时要求select语句只能返回标量值,即单行数据。因此为了保险,select into var_name的时候,应尽量结合limit语句限制输出。
set @a1=1,@a2=3,@a3:=2;
select @a4:=@a1+@a3;
select 33,'abc' into @a5,@a6 from dual;

查看变量值可以使用select语句。

MariaDB [test]> select @a1,@a2,@a3,@a4,@a5,@a6,@a7;
+------+------+------+------+------+------+------+
| @a1  | @a2  | @a3  | @a4  | @a5  | @a6  | @a7  |
+------+------+------+------+------+------+------+
|    1 |    3 |    2 |    3 |   33 | abc  | NULL |
+------+------+------+------+------+------+------+

在mariadb 10.2.6中,引入了一张系统架构表information_schema.USER_VARIABLES,该表中记录了当前用户当前会话定义的用户变量信息。该信息架构表在mysql中没有。

MariaDB [test]> SELECT * FROM information_schema.USER_VARIABLES;           
+---------------+----------------+---------------+--------------------+
| VARIABLE_NAME | VARIABLE_VALUE | VARIABLE_TYPE | CHARACTER_SET_NAME |
+---------------+----------------+---------------+--------------------+
| a6            | abc            | VARCHAR       | utf8               |
| i             | 2              | INT           | utf8               |
| a5            | 33             | INT           | utf8               |
| a1            | 1              | INT           | utf8               |
| a4            | 3              | INT           | utf8               |
| a2            | 3              | INT           | utf8               |
| a3            | 2              | INT           | utf8               |
+---------------+----------------+---------------+--------------------+

前言

2.变量

    1.局部变量

      局部变量被定义在begin和end之间使用,且要放在复杂sql语句的前面,如同一个方法的私有变量一样。

      声明:DECLARE p varchar(200);  --不带默认值

               DECLARE p varchar(200) DEFAULT '王五'; --带默认值

    mysql文档声明:  DECLARE var_name[,...] type [DEFAULT value]

    也就是可以同时对多个变量进行定义。用“,”号隔开

              declare t1,t2,t3 int DEFAULT 2;

      赋值:set p = '李四';

               mysql文档赋值:SET var_name = expr [, var_name = expr] ...   用“,”号隔开

    set t1=5,t3=6;

    set t1=(select count(*) from emp); 

              select *** into         

    select emp.emp_name into v_name from emp LIMIT 0,1;  --变量赋值仅可获得一个结果,变量名不可和表中的输出列名一样,不然拿不到sql中的值。
    select v_name; --显示结果 

     使用 select var_name; 可以查看结果。  

澳门新濠3559 1澳门新濠3559 2

1 drop PROCEDURE if EXISTS sp1;
2 create PROCEDURE sp1()
3 BEGIN
4 declare t1,t2,t3 int DEFAULT 2;
5 set t1=5,t3=6;
6 select t2;
7 select t3;
8 end;
9 call sp1();

sp1

   注意: declare 不但可以对变量声明,而且还可以声明条件,声明处理(多用于异常控制)。

           声明条件:

      DECLARE condition_name CONDITION FOR condition_value
    声明处理
      DECLARE....HANDLER..FOR

 1 handler_action:  
 2     CONTINUE  
 3   | EXIT  
 4   | UNDO  
 5   
 6 condition_value:  
 7     mysql_error_code  
 8   | SQLSTATE [VALUE] sqlstate_value  
 9   | condition_name  
10   | SQLWARNING  
11   | NOT FOUND  
12   | SQLEXCEPTION  

DECLARE foreign_key_error CONDITION FOR 1216;  

DECLARE CONTINUE HANDLER FOR foreign_key_error MySQL_statements;  

为1216 error_code 起名字 foreign_key_error, 声明当遇到foreign_key_error 这种状态时,程序继续。

   2.用户变量

  用户变量是当本次客户请求连接开始到请求结束之间。当我们在程序中调用一个存储过程A,A存储过程又调用了B存储过程,在A中定义的用户变量,在B中可以        使用。如同一个类中的私有变量一样。

     声明: @

 赋值: set = 赋值,select := 赋值

1 drop PROCEDURE if exists sp2;
2 create procedure sp2()
3 BEGIN
4 set @v1='1234';
5 select @v1:='5678';
6 select @v1;
7 end;
8 call sp2();

3. 系统变量

     分为:全局变量和 会话变量。

           全局变量影响服务器整体操作。要想更改全局变量,必须具有SUPER权限。

   全局变量通过set方式设置,会在当前已经启动的系统中生效,但是如果系统重启,动态设置的全局变量会丢失。静态设置就是在my.ini/my.cnf文件中配置,然后重启服务。

           会话变量不需要权限可以自己修改。会话变量的生命周期和用户变量一致,都是客户端连接时产生,客户端关闭时关闭。但会话变量属于客户的环境使用状况设置,而用户变            量用于存储过程。

    系统变量不可自定义,只能对现有的进行修改。否则报出 unknown system varitables [v_name].

    如果设置SET变量时不指定GLOBAL、SESSION或者LOCAL,默认使用SESSION。查看时如果不设置,则会SESSION-》GLOBAL。

set session identity = 0;  -- 修改会话变量
set @@session.identity = 0;  --修改会话变量
set @@identity = 0;       --修改会话变量
set identity =0; 
show session VARIABLES like 'identity'; --查看会话变量 
show session VARIABLES;--查看会话变量 

select @@identity;--查看会话变量

--LOCAL 和 SESSION 是同义词,表达同样的意思

set local identity = 0;
set @@local.identity = 0;

show local VARIABLES like 'identity';
show local VARIABLES;
select @@identity;

set global autocommit = 'ON';  --修改系统服务变量
set @@global.autocommit = 'ON'; --修改系统服务变量

show global VARIABLES like 'autocommit'; --查看系统服务变量
show global VARIABLES; --查看修改系统服务变量
select @@autocommit; --查看修改系统服务变量

 

2.系统变量

在MySQL/mariadb中维护两种系统变量:全局系统变量和会话系统变量。系统变量是用来设置MySQL服务运行属性和状态的。

全局系统变量使用global或者"@@global."关键字来设置。会话系统变量使用session或者"@@session."关键字来设置,其中session可以替换为Local,它们是同义词。如果省略这些关键字,则默认为session系统变量。设置global系统变量要求具有super权限。

-- 设置全局系统变量
set global sort_buffer_size=32M;
set @@global.sort_buffer_size=32M;
-- 设置会话系统变量
set session sort_buffer_size=32M;
set @@session.sort_buffer_size=32M;
set sort_buffer_size=32M;
-- 查看全局系统变量值
select @@global.sort_buffer_size;
show global variables like "sort_buffer%";
-- 查看会话系统变量,不能使用select sort_buffer_size
select @@session.sort_buffer_size;
select @@sort_buffer_size;
show [session] variables like "sort_buffer%";
-- 一次性设置多个变量,包括会话变量、全局变量以及用户变量
SET @x = 1, SESSION sql_mode = '';
SET GLOBAL sort_buffer_size = 1000000, SESSION sort_buffer_size = 1000000;
SET @@global.sort_buffer_size = 1000000, @@local.sort_buffer_size = 1000000;
SET GLOBAL max_connections = 1000, sort_buffer_size = 1000000;

全局系统变量对全局有效,当有新的会话打开时,新会话会继承全局系统变量的值,所以设置全局系统变量之后新打开的会话都会继承设置后的值。设置全局系统变量对已经打开的连接无效,但是其他已经打开的连接可以查看到设置后的全局系统变量值。

系统变量按照是否允许在运行时修改,还分为动态变量和静态变量。能在运行过程中修改的变量称为动态变量,只能在数据库实例关闭状态下修改的变量称为静态变量或只读变量。动态变量使用set修改。如果在数据库实例运行状态下修改静态变量,则会给出错误。如:

set @@innodb_undo_tablespaces=3;
ERROR 1238 (HY000): Variable 'innodb_undo_tablespaces' is a read only variable

系统变量除了可以在运行中的环境下设置,还可以在配置文件中或者mysqld/mysqld_safe这样的命令行中设置,甚至mysql客户端命令行也可以传递。在配置文件中设置系统变量时,下划线或者短横线都允许,它们表示同一个意思。例如下面的两行配置是等价的:

innodb_file_per_table=1
innodb-file-per-table=1

MySQL存储过程中,定义变量有两种方式: 

3. 参数

 参数默认是IN,也就是如果是IN的参数可以不用指定。指定有三种:IN, OUT, 或INOUT,这只对PROCEDURE是合法的,Function仅接收IN,且语句中要加强制返回 Return关键     字。

         IN:

              create procedure sp3(IN v_name varchar(25)) 或create procedure sp3(v_name varchar(25))      ---默认为IN

1 drop PROCEDURE if EXISTS sp3;
2 create PROCEDURE sp3(ov VARCHAR(25))
3 BEGIN
4 select * from emp where emp.emp_name = ov;
5 end;
6 call sp3('张三');

但是存储过程上变量只能作用于begin...end块中, --调用存储过程。OUT:

1 create PROCEDURE sp3(OUT ov VARCHAR(25))
2 BEGIN
3 select emp.emp_type into ov from emp where emp.emp_name = '张三';
4 end;
5 call sp3(@V);
6 select @V;

INOUT :

1 drop PROCEDURE if EXISTS sp3;
2 create PROCEDURE sp3(INOUT ov VARCHAR(25))
3 BEGIN
4 select emp.emp_type into ov from emp where emp.emp_name = ov;
5 end;
6 set @v_name = '张三';
7 call sp3(@v_name);
8 select @v_name;

Ø 变量

3.局部变量

局部变量也称为本地变量,只能在begin...and语句块中生效。它不像用户变量,本地变量必须使用declare事先声明,所以declare也必须在begin...end中使用。

局部变量无论是声明还是调用的时候都不需要任何多余的符号(即不需要@符号),直接使用其名称var_name即可。

使用declare声明变量,可以一次性声明多个同类型的变量,需要时可有直接为其指定默认值,不指定时默认为null。

decalre var_name,... type [default value];

使用set为变量赋值。MySQL/mariadb中set支持一次性赋值多个变量。

在begin...end中的set是一般set语句的扩展版本,它既可以设置系统变量、用户变量,也可以设置此处的本地变量。

set var_name=expr,[var_name=expr1,...]

或者使用select...into语句从表中获取值来赋值给变量,但是这样的赋值行为要求表的返回结果必须是单列且单行的标量结果。例如下面的语句将col的列值赋值给var_name变量。

select col into var_name from table_name;

因为局部变量只能在begin...end中使用,所以此处使用存储过程的例子来演示。

DROP PROCEDURE IF EXISTS haha;
DELIMITER $$
CREATE PROCEDURE haha()
BEGIN
    DECLARE a INT;
    SET a=1;
    SET @i:=2;
    SELECT a,@i;
END$$
DELIMITER ;

CALL haha();
     a        @i
------  --------
     1         2

在MySQL中,begin...end只能定义在存储程序中,所以declare也只能定义在存储程序内。但在mariadb中,begin...end是允许定义在存储程序(存储函数,存储过程,触发器,事件)之外的,所以decalre也算是能够定义在存储程序之外吧。需要定义在存储程序之外时,使用 begin not atomic 关键字即可。例如:

delimiter $$ 
begin not atomic
    declare a int;
    set a=3;
    select a;
end$$

1、使用set或select直接赋值,变量名以@开头

4.cursor光标 

    光标可以对查询进行遍历,判断。

  1. 创建光标

          declare cur1 cursor for select emp_name from emp;

      2. 打开游标

          open cur1;

      3. 游标查询

         fetch cur1 into [v_name];

       4. 关闭游标

        close cur1;

      注意:   emp_names 一定要赋予初始值'',要不然为null,null和任意字符concat结果都为null;

以下例子:实现group_concat 功能;

 1 drop PROCEDURE if EXISTS sp1;
 2 create PROCEDURE sp1()
 3 BEGIN
 4 DECLARE done INT DEFAULT 0;
 5 DECLARE emp_names,temp VARCHAR(150) DEFAULT '';
 6 DECLARE a varchar(15);  
 7 declare cursor1 CURSOR for select emp_name from emp;
 8 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1; /*这两个是一个意思*/
 9 DECLARE CONTINUE HANDLER FOR NOT FOUND SET @l_last_sale=1;  /*定义循环结束后的状态*/
10 open cursor1;
11 REPEAT
12  FETCH cursor1 into a;
13 if not done then
14   set emp_names=CONCAT(a,',',emp_names);
15 end if;
16 until done end REPEAT;
17 close cursor1;
18 select temp;
19 select emp_names;
20 end;
21 call sp1();

1、 局部变量(Local Variable)
局部变量是用户可以自定义的变量,它的作用范围是仅在程序内部,在程序中通常用来储存从表中查询到的数据或当做程序执行过程中的暂存变量。使用局部变量必须以@开头,而且必须用declare命令后才能使用。

3.1 declare锚定其他对象的数据类型

在mariadb 10.3中(注意版本号,目前10.3版本还在测试中),declare语句允许在存储程序中使用TYPE OFROW TYPE OF 关键字基于表或游标来锚定数据类型。在mysql中不支持数据类型的锚定功能。

例如:

DECLARE tmp TYPE OF t1.a; -- 基于表t1中的a列获取数据类型
DECLARE rec1 ROW TYPE OF t1; -- 锚定表t1中行数据类型
DECLARE rec2 ROW TYPE OF cur1; -- 基于游标cur1获取行数据类型

通过其他对象来锚定本地变量的数据类型时,如果对象的数据类型改变,则本地数据类型也随之改变。这在某些时候非常有利于维护存储程序。

在定义存储程序时,不会检查declare锚定的对象是否存在。但在调用存储程序时,会先检查锚定对象是否存在。

当declare语句的锚定是基于表对象(不是游标)时,在调用存储程序的瞬间就会检查锚定的表是否存在,然后立刻声明该变量。因此:

  • (1).带有锚定功能的decalre语句可以定义在存储程序的任意位置;
  • (2).在存储程序中删除锚定的表对象,或者修改了锚定的表结构,都不会改变存储程序调用时声明的变量类型;
  • (3).所有带锚定功能的declare都是在存储程序调用之初被赋值的。

当declare语句的锚定是基于游标对象时,变量的数据类型是在执行变量声明语句时才获取到的。数据类型仅只锚定一次,之后不再改变。如果游标中的ROW TYPE OF变量是定义在一个循环之中,则数据类型在循环的开头就已经获取,且之后的循环不再改变。

示例:

create table t1(a int,b char(20));
drop procedure if exists haha;
delimiter $$
create procedure haha()
begin
    declare x type of t1.a;
    set x=1;
    select x;
end$$
delimiter ;

call haha();

例如:

 5. 结构控制

      1.IF:  

 1 drop PROCEDURE if EXISTS sp3;
 2 create PROCEDURE sp3(INOUT ov VARCHAR(25))
 3 BEGIN
 4     IF ov = '张' THEN
 5         set ov='张三'; 
 6     ELSEIF ov='李' THEN
 7         set ov='李四'; 
 8     ELSE
 9         set ov='王五';
10     end if;
11 select * from emp where emp.emp_name = ov;
12 end;
13 set @ov='样';
14 call sp3(@ov);

       2. CASE:

 1 drop PROCEDURE if EXISTS sp3;
 2 create PROCEDURE sp3(INOUT ov VARCHAR(25))
 3 BEGIN
 4   CASE ov 
 5   when '张' THEN
 6         set ov='张三'; 
 7     when '李' THEN
 8         set ov='李四'; 
 9     ELSE
10         set ov='王五';
11     end case;
12 select * from emp where emp.emp_name = ov;
13 end;
14 set @ov='样';
15 call sp3(@ov);

 1 drop PROCEDURE if EXISTS sp3;
 2 create PROCEDURE sp3(INOUT ov VARCHAR(25))
 3 BEGIN
 4   CASE when ov='张' THEN
 5         set ov='张三'; 
 6     when ov='李' THEN
 7         set ov='李四'; 
 8     ELSE
 9         set ov='王五';
10     end case;
11 select * from emp where emp.emp_name = ov;
12 end;
13 set @ov='样';
14 call sp3(@ov);
  1. Loop

    [begin_label:] LOOP

        statement_list

    END LOOP [end_label]

    label1: loop end loop label1;

LOOP允许某特定语句或语句群的重复执行,实现一个简单的循环构造。在循环内的语句一直重复直循环被退出,退出通常伴随着一个LEAVE 语句。

LOOP语句可以被标注。除非begin_label存在,否则end_label不能被给出,并且如果两者都出现,它们必须是同样的。

基本语法:

set @var=1; 

4. LEAVE语句 (相当于循环中的break,不同点可以跳出任意及循环,而一般java退出为一层一层退出for循环)

LEAVE label

这个语句被用来退出任何被标注的流程控制构造。它和BEGIN ... END或循环一起被使用。

5. ITERATE语句(相当于循环中的continue,不同点可以继续任意循环)

ITERATE label

ITERATE只可以出现在LOOP, REPEAT, 和WHILE语句内。ITERATE意思为:“再次循环。”

 1 drop PROCEDURE if EXISTS sp3;
 2 create PROCEDURE sp3(IN ov INT,OUT a int, out b int)
 3 BEGIN
 4    set a=0,b=0;
 5   label1:LOOP
 6         set ov= ov + 1;
 7         label2:LOOP
 8             set a=a + 1;
 9             set ov=ov+10;
10             if ov>30 THEN
11                 
12                 leave label2;
13             end IF;
14         end loop label2;
15        set b=b + 1;
16         if ov < 60 THEN
17             ITERATE label1;
18     ELSE
19        leave label1;
20     end if;
21    end LOOP label1;
22 select * from emp;
23 end;
24 set @ov=0;
25 call sp3(@ov, @a, @b);
26 select @a,@b;

声明变量
declare @变量名 变量类型 [@变量名 变量类型]
变量赋值
set @变量名 = 变量值;
select @变量名 = 变量值;

可以在一个会话的任何地方声明,作用域是整个会话,称为用户变量。

6. REPEAT语句(相当于java中go....while()语句,loop是死循环需要内部打断执行才可以,repeat是只要满足条件就退出

[begin_label:] REPEAT

    statement_list

UNTIL search_condition

END REPEAT [end_label]

 1 drop PROCEDURE if EXISTS sp3;
 2 create PROCEDURE sp3(IN ov INT,OUT a int, out b int)
 3 BEGIN
 4 select @a := 20; 
 5 REPEAT
 6     set @a=@a+1;
 7 UNTIL @a>ov
 8 end REPEAT;
 9 select * from emp;
10 set a = @a;
11 end;
12 set @ov=50;
13 call sp3(@ov, @a, @b);
14 select @a,@b;

7. WHILE语句(相当于java中的while语句)

[begin_label:] WHILE search_condition DO

    statement_list

END WHILE [end_label]

 1 drop PROCEDURE if EXISTS sp3;
 2 create PROCEDURE sp3(IN ov INT,OUT a int, out b int)
 3 BEGIN
 4 select @a := 20; 
 5 WHILE @a<ov DO
 6     set @a=@a+1;
 7 end WHILE;
 8 select * from emp;
 9 set a = @a;
10 end;
11 set @ov=30;
12 call sp3(@ov, @a, @b);
13 select @a,@b;

局部变量示例:
declare @id char(10)--声明一个长度的变量id
declare @age int --声明一个int类型变量age
select @id = 22 --赋值操作
set @age = 55 --赋值操作
print convert(char(10), @age) + '#' + @id
select @age, @id
go

2、以declare关键字声明的变量,只能在存储过程中使用,称为存储过程变量,例如: 

存储过程:预编译的sql,减少网络之间的数据传输,复杂sql语句多sql语句的处理。

查询数据示例
declare @id int, @name varchar(20);
set @id = 1;
select @name = name from student where id = @id;
select @name;

declare var1 int default 0; 

二. FUNCTION

function的创建使用和procedure一样,就是将名字换成function即可。

1.不同点:

  •  function一般作为其它查询语句的一部分而存在。而procedure一般独立执行。
  •  function只能参数形式仅可为IN,仅通过return返回单个值,procedure有INOUT ,OUT形式。可返回多个值,且不需要声明return。
  • function是为了解决针对性的某个问题,而存储过程多为了满足查找某项输出数据样式而设立。

创建和删除function

1 drop function if exists f1;
2 create function f1(v1 VARCHAR(20)) returns VARCHAR(50)
3 return CONCAT(v1,'HelloWord','!');
4 select f1('My first Function ');

查看创建的function:     SHOW CREATE FUNCTION f1;

 1 drop function if exists f2;
 2 create function f2(v1 VARCHAR(20)) returns VARCHAR(50)
 3 BEGIN
 4 if v1='张' THEN
 5 set v1='张三';
 6 ELSEIF v1='李' THEN
 7 set v1='李四';
 8 else 
 9 set v1='王五';
10 end if;
11 return v1;
12 END;
13 select f2('My first Function ');

    参考文摘:

局部变量可用于程序中保存临时数据、传递数据。Set赋值一般用于赋值指定的常量个变量。而select多用于查询的结果进行赋值,当然select也可以将常量赋值给变量。
在使用select进行赋值的时候,如果查询的结果是多条的情况下,会利用最后一条数据进行赋值,前面的赋值结果将会被覆盖。

主要用在存储过程中,或者是给存储传参数中。

MySql 存储过程实例(附完整注释) 

2、 全局变量(Global Variable)
全局变量是系统内部使用的变量,其作用范围并不局限于某一程序而是任何程序均可随时调用的。全局变量一般存储一些系统的配置设定值、统计数据。

两者的区别是: 

mysql存储过程语法及实例 

MySQL存储过程中的用户变量,系统变量,局部变量 cla 

 

--全局变量
select @@identity;--最后一次自增的值
select identity(int, 1, 1) as id into tab from student;--将student表的列,以/1自增形式创建一个tab
select * from tab;
select @@rowcount;--影响行数
select @@cursor_rows;--返回连接上打开的游标的当前限定行的数目
select @@error;--T-SQL的错误号
select @@procid;

在调用存储过程时,以declare声明的变量都会被初始化为null。而会话变量(即@开头的变量)则不会被再初始化,在一个会话内,只须初始化一次,之后在会话内都是对上一次计算的结果,就相当于在是这个会话内的全局变量。

--配置函数
set datefirst 7;--设置每周的第一天,表示周日
select @@datefirst as '星期的第一天', datepart(dw, getDate()) AS '今天是星期';
select @@dbts;--返回当前数据库唯一时间戳
set language 'Italian';
select @@langId as 'Language ID';--返回语言id
select @@language as 'Language Name';--返回当前语言名称
select @@lock_timeout;--返回当前会话的当前锁定超时设置(毫秒)
select @@max_connections;--返回SQL Server 实例允许同时进行的最大用户连接数
select @@MAX_PRECISION AS 'Max Precision';--返回decimal 和numeric 数据类型所用的精度级别
select @@SERVERNAME;--SQL Server 的本地服务器的名称
select @@SERVICENAME;--服务名
select @@SPID;--当前会话进程id
select @@textSize;
select @@version;--当前数据库版本信息

主体内容

--系统统计函数
select @@CONNECTIONS;--连接数
select @@PACK_RECEIVED;
select @@CPU_BUSY;
select @@PACK_SENT;
select @@TIMETICKS;
select @@IDLE;
select @@TOTAL_ERRORS;
select @@IO_BUSY;
select @@TOTAL_READ;--读取磁盘次数
select @@PACKET_ERRORS;--发生的网络数据包错误数
select @@TOTAL_WRITE;--sqlserver执行的磁盘写入次数

  • 局部变量 
  • 用户变量 
  • 会话变量 
  • 全局变量 

Ø 输出语句

会话变量和全局变量叫系统变量。

T-SQL支持输出语句,用于显示结果。常用输出语句有两种:

一、局部变量,只在当前begin/end代码块中有效

基本语法

局部变量一般用在sql语句块中,比如存储过程的begin/end。其作用域仅限于该语句块,在该语句块执行完毕后,局部变量就消失了。declare语句专门用于定义局部变量,可以使用default来说明默认值。set语句是设置不同类型的变量,包括会话变量和全局变量。 
局部变量定义语法形式

print 变量或表达式
select 变量或表达式

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

 

例如在begin/end语句块中添加如下一段语句,接受函数传进来的a/b变量然后相加,通过set语句赋值给c变量。 

示例:

set语句语法形式set var_name=expr [, var_name=expr]...; set语句既可以用于局部变量的赋值,也可以用于用户变量的申明并赋值。

select 1 + 2;
select @@language;
select user_name();

declare c int default 0;
set c=a+b;
select c as C;

print 1 + 2;
print @@language;
print user_name();

或者用select …. into…形式赋值

print在输出值不少字符串的情况下,需要用convert转换成字符串才能正常输出,而且字符串的长度在超过8000的字符以后,后面的将不会显示。

select into 语句句式:select col_name[,...] into var_name[,...] table_expr [where...];

Ø 逻辑控制语句

例子:

1、 if-else判断语句

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;

语法

二、用户变量,在客户端链接到数据库实例整个过程中用户变量都是有效的。

if <表达式>
<命令行或程序块>
else if <表达式>
<命令行或程序块>
else
<命令行或程序块>

MySQL中用户变量不用事前申明,在用的时候直接用“@变量名”使用就可以了。 

示例:

第一种用法:set @num=1; 或set @num:=1; //这里要使用set语句创建并初始化变量,直接使用@num变量 

--if简单示例
if (2 > 3)
  print '2 > 3';
else if (3 > 2)
  print '3 > 2';
else
  print 'other';

第二种用法:select @num:=1; 或 select @num:=字段名 from 表名 where ……, 

--简单查询判断
declare @id char(10),
    @pid char(20),
    @name varchar(20);
set @name = '广州';
select @id = id from ab_area where areaName = @name;
select @pid = pid from ab_area where id = @id;
print @id + '#' + @pid;

select语句一般用来输出用户变量,比如select @变量名,用于输出数据源不是表格的数据。

if @pid > @id
  begin
    print @id + '%';
    select * from ab_area where pid like @id + '%';
  end
else
  begin
    print @id + '%';
    print @id + '#' + @pid;
    select * from ab_area where pid = @pid;
  end
go

注意上面两种赋值符号,使用set时可以用“=”或“:=”,但是使用select时必须用“:=赋值”

2、 while…continue…break循环语句

用户变量与数据库连接有关,在连接中声明的变量,在存储过程中创建了用户变量后一直到数据库实例接断开的时候,变量就会消失。

基本语法

在此连接中声明的变量无法在另一连接中使用。

while <表达式>
begin
<命令行或程序块>
[break]
[continue]
<命令行或程序块>
end

用户变量的变量名的形式为@varname的形式。

 

名字必须以@开头。

示例:

声明变量的时候需要使用set语句,比如下面的语句声明了一个名为@a的变量。

--while循环输出
declare @i int;
set @i = 1;
while (@i < 11)
  begin
    print @i;
    set @i = @i + 1;
  end
go

set @a = 1;

--while continue
declare @i int;
set @i = 1;
while (@i < 11)
  begin
    if (@i < 5)
      begin
      set @i = @i + 1;
      continue;
      end
    print @i;
    set @i = @i + 1;
  end
go

声明一个名为@a的变量,并将它赋值为1,MySQL里面的变量是不严格限制数据类型的,它的数据类型根据你赋给它的值而随时变化 。(SQL SERVER中使用declare语句声明变量,且严格限制数据类型。) 

--while break
declare @i int;
set @i = 1;
while (1 = 1)
  begin
    print @i;
    if (@i >= 5)
      begin
        set @i = @i + 1;
        break;
      end
澳门新濠3559,    set @i = @i + 1;
  end
go

我们还可以使用select语句为变量赋值 。 

 

比如:

3、 case

set @name = '';
select @name:=password from user limit 0,1;
#从数据表中获取一条记录password字段的值给@name变量。在执行后输出到查询结果集上面。

基本语法

(注意等于号前面有一个冒号,后面的limit 0,1是用来限制返回结果的,表示可以是0或1个。相当于SQL SERVER里面的top 1) 

case
when <条件表达式> then <运算式>
when <条件表达式> then <运算式>
when <条件表达式> then <运算式>
[else <运算式>]
end

如果直接写:select @name:=password from user;

 

如果这个查询返回多个值的话,那@name变量的值就是最后一条记录的password字段的值 。 

示例:

用户变量可以作用于当前整个连接,但当当前连接断开后,其所定义的用户变量都会消失。 

select *,
case sex
  when 1 then '男'
  when 0 then '女'
  else '火星人'
  end as '性别'
from student;

用户变量使用如下(我们无须使用declare关键字对用户变量进行定义,可以直接这样使用)定义,变量名必须以@开始:

select areaName, '区域类型' = case
  when areaType = '省' then areaName + areaType
  when areaType = '市' then 'city'
  when areaType = '区' then 'area'
  else 'other'
  end
from ab_area;

#定义
select @变量名  或者 select @变量名:= 字段名 from 表名 where 过滤语句;
set @变量名;
#赋值 @num为变量名,value为值
set @num=value;或select @num:=value;

 

对用户变量赋值有两种方式,一种是直接用”=”号,另一种是用”:=”号。其区别在于使用set命令对用户变量进行赋值时,两种方式都可以使用;当使用select语句对用户变量进行赋值时,只能使用”:=”方式,因为在select语句中,”=”号declare语句专门用于定义局部变量。set语句是设置不同类型的变量,包括会话变量和全局变量。

4、 其他语句

例如:

延时执行
waitfor delay '00:00:03';--定时三秒后执行
print '定时三秒后执行';

begin
#Routine body goes here...
#select c as c;
declare c int default 0;
set @var1=143;  #定义一个用户变量,并初始化为143
set @var2=34;
set c=a+b;
set @d=c;
select @sum:=(@var1+@var2) as sum, @dif:=(@var1-@var2) as dif, @d as C;#使用用户变量。@var1表示变量名

set c=100;
select c as CA;
end

#在查询中执行下面语句段
call `order`(12,13);  #执行上面定义的存储过程
select @var1;  #看定义的用户变量在存储过程执行完后,是否还可以输出,结果是可以输出用户变量@var1,@var2两个变量的。
select @var2;

 

在执行完order存储过程后,在存储过程中新建的var1,var2用户变量还是可以用select语句输出的,但是存储过程里面定义的局部变量c不能识别。

 

系统变量:

 

系统变量又分为全局变量与会话变量。

 

全局变量在MySQL启动的时候由服务器自动将它们初始化为默认值,这些默认值可以通过更改my.ini这个文件来更改。

 

会话变量在每次建立一个新的连接的时候,由MySQL来初始化。MySQL会将当前所有全局变量的值复制一份。来做为会话变量。

 

(也就是说,如果在建立会话以后,没有手动更改过会话变量与全局变量的值,那所有这些变量的值都是一样的。)

本文转自:

全局变量与会话变量的区别就在于,对全局变量的修改会影响到整个服务器,但是对会话变量的修改,只会影响到当前的会话(也就是当前的数据库连接)。

 

我们可以利用

show session variables;

语句将所有的会话变量输出(可以简写为show variables,没有指定是输出全局变量还是会话变量的话,默认就输出会话变量。)如果想输出所有全局变量:

show global variables

有些系统变量的值是可以利用语句来动态进行更改的,但是有些系统变量的值却是只读的。

对于那些可以更改的系统变量,我们可以利用set语句进行更改。

系统变量在变量名前面有两个@; 

如果想要更改会话变量的值,利用语句:

set session varname = value;
或者
set @@session.varname = value;

比如:

mysql> set session sort_buffer_size = 40000;
Query OK, 0 rows affected(0.00 sec)
用select @@sort_buffer_size;输出看更改后的值是什么。
如果想要更改全局变量的值,将session改成global:
set global sort_buffer_size = 40000;
set @@global.sort_buffer_size = 40000;

不过要想更改全局变量的值,需要拥有super权限 。

(注意,root只是一个内置的账号,而不是一种权限 ,这个账号拥有了MySQL数据库里的所有权限。任何账号只要它拥有了名为super的这个权限,就可以更改全局变量的值,正如任何用户只要拥有file权限就可以调用load_file或者into outfile,into dumpfile,load data infile一样。)

利用select语句我们可以查询单个会话变量或者全局变量的值:

select @@session.sort_buffer_size
select @@global.sort_buffer_size
select @@global.tmpdir

凡是上面提到的session,都可以用local这个关键字来代替。

比如:  

select @@local.sort_buffer_size
local是session的近义词。

无论是在设置系统变量还是查询系统变量值的时候,只要没有指定到底是全局变量还是会话变量。都当做会话变量来处理。 

比如: 

set @@sort_buffer_size = 50000; 
select @@sort_buffer_size; 

上面都没有指定是blobal还是session,所以全部当做session处理。

三、会话变量

服务器为每个连接的客户端维护一系列会话变量。在客户端连接数据库实例时,使用相应全局变量的当前值对客户端的会话变量进行初始化。设置会话变量不需要特殊权限,但客户端只能更改自己的会话变量,而不能更改其它客户端的会话变量。会话变量的作用域与用户变量一样,仅限于当前连接。当当前连接断开后,其设置的所有会话变量均失效。

设置会话变量有如下三种方式更改会话变量的值:

set session var_name = value;
set @@session.var_name = value;
set var_name = value;  #缺省session关键字默认认为是session

查看所有的会话变量
show session variables;

查看一个会话变量也有如下三种方式:

select @@var_name;
select @@session.var_name;
show session variables like "%var%";

凡是上面提到的session,都可以用local这个关键字来代替。 

比如: 

select @@local.sort_buffer_size 
local是session的近义词。

四、全局变量

全局变量影响服务器整体操作。当服务器启动时,它将所有全局变量初始化为默认值。这些默认值可以在选项文件中或在命令行中指定的选项进行更改。要想更改全局变量,必须具有super权限。全局变量作用于server的整个生命周期,但是不能跨重启。即重启后所有设置的全局变量均失效。要想让全局变量重启后继续生效,需要更改相应的配置文件。

要设置一个全局变量,有如下两种方式:

set global var_name = value; //注意:此处的global不能省略。根据手册,set命令设置变量时若不指定GLOBAL、SESSION或者LOCAL,默认使用SESSION
set @@global.var_name = value; //同上

查看所有的全局变量 

show global variables; 

要想查看一个全局变量,有如下两种方式:

select @@global.var_name;
show global variables like “%var%”;

 

参考:

(以上内容转自此篇文章)

 

 

http://blog.163.com/longsu2010@yeah/blog/static/173612348201162595425697/ 

编辑:数据库 本文来源:但是存储过程上变量只能作用于begin...end块中,

关键词: