变量、函数、存储过程

一、变量

1.1全局变量

mysql全局变量，全局变量影响服务器整体操作，当服务启动时，它将所有全局变量初始化为默认值。要想更改全局变量，必须具有super权限。
其作用域为server的整个生命周期。

-- 显示所有的全局变量
show global variables;

-- 设置全局变量的值的两种方式
set global sql_warnings=ON;        -- global不能省略
set @@global.sql_warnings=OFF;

-- 查询全局变量的值的两种方式
select @@global.sql_warnings;
show global variables like '%sql_warnings%';

1.2会话变量

mysql会话变量，服务器为每个连接的客户端维护一系列会话变量。
其作用域仅限于当前连接，即每个连接中的会话变量是独立的。

-- 显示所有的会话变量
show session variables;

-- 设置会话变量的值的三种方式
set session auto_increment_increment=1;
set @@session.auto_increment_increment=2;
set auto_increment_increment=3;        -- 当省略session关键字时，默认缺省为session，即设置会话变量的值

-- 查询会话变量的值的三种方式
select @@auto_increment_increment;
select @@session.auto_increment_increment;
show session variables like '%auto_increment_increment%';        -- session关键字可省略

-- 关键字session也可用关键字local替代
set @@local.auto_increment_increment=1;
select @@local.auto_increment_increment;

1.3用户变量

mysql用户变量，mysql中用户变量不用提前申明，在用的时候直接用“@变量名”使用就可以了。
其作用域为当前连接。

-- 第一种用法，使用set时可以用“=”或“:=”两种赋值符号赋值
set @age=19;

set @age:=20;

-- 第二种用法，使用select时必须用“:=”赋值符号赋值
select @age:=22;

select @age:=StuAge 
from demo.student 
where StuNo='A001';

1.4局部变量

mysql局部变量，只能用在begin/end语句块中，比如存储过程中的begin/end语句块。
其作用域仅限于该语句块。

-- declare语句专门用于定义局部变量，可以使用default来说明默认值
declare age int default 0;

-- 局部变量的赋值方式一
set age=18;

-- 局部变量的赋值方式二
select StuAge 
into age
from demo.student 
where StuNo='A001';

二、函数

格式：

CREATE FUNCTION 函数名(参数)
RETURN 返回值数据类型
[WITH{Encryption | Schemabinding }]
[AS]
BEGIN
SQL语句(必须有return 变量或值)
End

有且只有、必有一个返回值。
用户定义自定义函数像内置函数一样返回标量值，也可以将结果集用表格变量返回。

2.1标量函数

先创建函数

create function SumRes(@sco nvarchar(20))
returns real //返回值类型
as
begin

declare @sum real //变量
declare @code varchar(11) //变量

set @code = @sco + '%' //赋值
select @sum = sum(result) from LearnResult where scode like @code //查询结果赋值

return @sum 
end

使用，引用自定义函数

select 用户名.函数名 as 字段别名
select root.SumRes('sooo2') as result

用户自定义函数返回值可放在局部变量中,用set select exec 赋值

declare @sum1 real,@sum2 real,@sum3 real
set @sum1 = dbo.SumRes('sooo2')
select @sum2 = dbo.SumRes('sooo2')
exec @sum3 = dbo.SumRes'sooo2'
select @sum1,@sum2.@sum3

2.2表格值函数

create function tabcmess(@code nvarchar(50))
returns @dt table(id @nvarchar(20),name @nvarchar(50))
as

begin
//添加表格变量数据
insert into @dt select id name from tablename where name like @code

return

end

例子：
先创建表生成数据

create Table DepCompany
(
ID int,
panytemID nvarchar(20), --公司等级
panyartmentID nvarchar(20), --部门级别
panyName nvarchar(50), --部门名称
panyTurnover bigint --部门营业额
)

/*
公司分为总公司(代号1) -> 分公司(代号2) -> 支公司(代号3)
部门等级 -> 按照公司等级"牵引"部门所属是哪个等级的公司(例如："支公司3"旗下有 "龙湖部门3"，"金平部门3"，"达豪部门3")
*/

insert into DepCompany(ID,panytemID,panyartmentID,panyName,panyTurnover) 
values (1,'1','1','部门一部',10000000),
(2,'1','1','部门二部',10000000),
(3,'1','1','部门三部',100000),
(4,'1','1','部门四部',10000000),
(5,'1','1','部门五部',100000),
(6,'1','1','部门六部',100000),
(7,'2','2','部门一部',10000000),
(8,'2','2','部门二部',100000),
(9,'2','2','部门三部',10000000),
(10,'1','1','部门七部',100000),
(11,'1','1','部门八部',100000),
(12,'1','1','部门九部',100000),
(13,'3','3','部门一部',10000000),
(14,'3','3','部门二部',100000),
(15,'3','3','部门三部',100000),
(16,'3','3','部门四部',10000000),
(17,'2','2','部门四部',100000),
(18,'2','2','部门五部',10000000),
(19,'2','2','部门六部',100000),
(20,'1','1','部门十部',10000000),
(21,'2','2','部门七部',100000)

创建函数

create function Company2(@ID nvarchar(10))
returns @dt table(ID int, PanytemID nvarchar(10),PanyartmentID nvarchar(10),PanyName nvarchar(50),panyTurnover int)
as
begin

insert into @dt select ID,panytemID,panyartmentID,panyName,panyTurnover from DepCompany where panytemID = @ID

return
end

使用函数

select * from dbo.Company2('2')

三、存储过程

一个简单示例

//创建存储过程
create procedure GetUsers()
begin 
    select * from user; 
end;

//查看存储过程
show create procedure GetUsers;

//调用存储过程
call GetUsers();

//删除存储过程
drop procedure if exists GetUsers;

存储过程可以传递参数，不存在返回值概念，但是参数可以传入传出。
存储过程无法被修改，只可以删除之后重建。

转载文章MySQL变量的使用SQL自定义函数function