mysql----变量和存储过程

变量的使用

• 1 : 查看所有系统变量

show global|【session】variables;

• 2 : 查看满足条件的部分系统变量

show global|【session】 variables like ‘条件’;
show global|【session】 variables like ‘%char%’; 查看系统变量包含char的

• 3、查看指定的系统变量的值

select @@global|【session】系统变量名;
select @@name 查看变量name的值

• 4、为某个系统变量赋值

方式一：
set global|【session】系统变量名=值;
方式二：
set @@global|【session】系统变量名=值;
set @name=10； 新建立一个name变量 值为10

自定义变量

说明：变量由用户自定义，而不是系统提供的
使用步骤：
1、声明
2、赋值
3、使用（查看、比较、运算等）

用户变量

作用域：针对于当前会话（连接）有效，作用域同于会话变量

#赋值操作符：=或:=
#①声明并初始化
SET @变量名=值;
SET @变量名:=值;
SELECT @变量名:=值;

#②赋值（更新变量的值）
#方式一：
	SET @变量名=值;
	SET @变量名:=值;
	SELECT @变量名:=值;
#方式二：
	SELECT 字段 INTO @变量名
	FROM 表;
#③使用（查看变量的值）
SELECT @变量名;

set @name = 10;  -- 设置一个name变量赋值为10
select @name   -- 查看自定义的name变量

---
select last_name into @name from employees where  email = 'NKOCHHAR'
select @name

局部变量

作用域：仅仅在定义它的begin end块中有效应用在 begin end中的第一句话
因为局部变量只能在begin… end中使用那么就是只能在存储过程中使用

#①声明
DECLARE 变量名 类型;
DECLARE 变量名 类型 【DEFAULT 值】;


#②赋值（更新变量的值）

#方式一：
	SET 局部变量名=值;
	SET 局部变量名:=值;
	SELECT 局部变量名:=值;
#方式二：
	SELECT 字段 INTO 具备变量名
	FROM 表;
#③使用（查看变量的值）
SELECT 局部变量名;

#局部变量必须在begin..end中使用
DECLARE m INT DEFAULT 1;
DECLARE n INT DEFAULT 1;
DECLARE SUM INT;
SET SUM=m+n;
SELECT SUM;

局部变量的注意点

declare变量的作用域是所在的批处理，if阻断不了它的作用域. 也就是我们的局部变量在if语句或者一个存储过程中定义后外部还是可以使用的

用户变量和局部变量的使用对比

#用户变量和局部变量的对比

		  作用域			     定义位置		            语法
用户变量	  当前会话		       会话的任何地方		     加@符号，不用指定类型
局部变量	  定义它的BEGIN END中 	BEGIN END的第一句话	一般不用加@,需要指定类型

delimiter

其实就是告诉mysql解释器，该段命令是否已经结束了，mysql是否可以执行了。默认情况下，delimiter是分号;。在命令行客户端中，如果有一行命令以分号结束，那么回车后，mysql将会执行该命令。

• DELIMITER 定好结束符为"$$", 然后最后又定义为";", MYSQL的默认结束符为";"

在sql语句中我们默认以分号;为一个语句的结束 代表可以执行这段sql了，起使delimiter默认就是分号;就是一个语句的结束,
那么有了分号是delimiter的默认方式时候什么时候不使用默认方式呢？在你使用函数或者存储过程的时候，因为如果一个函数或者存储过程内有好看几个sql不需要的时候你就要使用delimter来指定一个结束符号，不让分号作为这个函数或者存储过程的结束标记，不然你在存储过程中输入了第一个sql之后然后输入了分号就会执行这段sql后就会报错，我们使用delimiter来指定一个结束符时，不论函数或者存储过程中输入多少个sql只要不看到delimiter指定的结束符 此函数或者存储过程就不会执行。

CREATE FUNCTION `SHORTEN`(S VARCHAR(255), N INT) 
     RETURNS varchar(255) 
 BEGIN 
 IF ISNULL(S) THEN ci
     RETURN '';  -- 此处使用了分号 那么输入到此时就会默认结束然后执行这段sql
 ELSEIF N<15 THEN 
     RETURN LEFT(S, N); 
 ELSE 
     IF CHAR_LENGTH(S) <=N THEN 
    RETURN S; 
     ELSE 
   RETURN CONCAT(LEFT(S, N-10), '...', RIGHT(S, 5)); 
     END IF; 
 END IF; 

默认情况下，不可能等到用户把这些语句全部输入完之后，再执行整段语句。
因为mysql一遇到分号，它就要自动执行。
即，在语句RETURN ‘’;时，mysql解释器就要执行了。
这种情况下，就需要事先把delimiter换成其它符号，如//或$$。

delimiter //   --我们在此处指定结束符为// 
CREATE FUNCTION `SHORTEN`(S VARCHAR(255), N INT) 
     RETURNS varchar(255) 
 BEGIN 
IF ISNULL(S) THEN 
     RETURN '';   -- 因为上面使用delimiter指定了结束符为//所以这里不会结束sql去执行的，会继续往下面走
 ELSEIF N<15 THEN 
     RETURN LEFT(S, N); 
 ELSE 
     IF CHAR_LENGTH(S) <=N THEN 
    RETURN S; 
    ELSE 
   RETURN CONCAT(LEFT(S, N-10), '...', RIGHT(S, 5)); 
     END IF; 
 END IF; 
 END;// -- 这里才会指定整个方法的结束

delimiter “;” 用于向 MySQL 提交查询语句。在存储过程中每个 SQL 语句的结尾都有个 “;”，如果这时候，每逢 “;” 就向 MySQL 提交的话，当然会出问题了。于是更改 MySQL 的 delimiter，上面 MySQL 存储过程就编程这样子了：

---

存储过程

存储过程和函数：类似于java中的方法
好处：
1、提高代码的重用性
2、简化操作

含义：一组预先编译好的SQL语句的集合，理解成批处理语句
1、提高代码的重用性
2、简化操作
3、减少了编译次数并且减少了和数据库服务器的连接次数，提高了效率

创建语法

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

	存储过程体（一组合法的SQL语句）
END

调用存储过程

CALL 存储过程名(实参列表);

1、参数列表包含三部分
参数模式  参数名  参数类型
举例：
in stuname varchar(20)

参数模式：
in：该参数可以作为输入，也就是该参数需要调用方传入值
out：该参数可以作为输出，也就是该参数可以作为返回值
inout：该参数既可以作为输入又可以作为输出，也就是该参数既需要传入值，又可以返回值

2、如果存储过程体仅仅只有一句话，begin end可以省略
存储过程体中的每条sql语句的结尾要求必须加分号。
存储过程的结尾可以使用 delimiter 重新设置
语法：
delimiter 结束标记
案例：
delimiter $

创建带in模式参数的存储过程

#案例1：创建存储过程实现 根据女神名，查询对应的男神信息

CREATE PROCEDURE myp2(IN beautyName VARCHAR(20))
BEGIN
	SELECT bo.*
	FROM boys bo
	RIGHT JOIN beauty b ON bo.id = b.boyfriend_id
	WHERE b.name=beautyName;
	

END $

#调用
CALL myp2('柳岩')$


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



create procedure myp3(in beautyName varchar(20))

begin 

	select * from boys join beauty on boys.id  = beauty.boyfriend_id where beauty.name = beautyName;-- 存储过程内部的sql每一个的结束都要使用分号来结束不然就会报错

end %%

call myp3('周芷若')
------

如果存储过程内仅仅有一个sql这个时候就不需要delimiter定义结束符号了 可以省略delimiter

create procedure myp4(in beautyName varchar(20))


  select * from boys join beauty on boys.id = beauty.boyfriend_id where beauty.name = beautyName ;

end 

call myp4('周芷若')


#案例2 ：创建存储过程实现，用户是否登录成功

delimiter ## 
create procedure myp6(in username varchar(20),in pwd varchar(10))
begin 
		declare result int default 0;  -- 建立一个变量默认值为0  @在局部变量的创建和调用可以省略@
		select count(*) into @result from admin  -- 变量赋值给count(*) 查验出来的结果
		where 
		admin.username = username and
		admin.password=pwd;
		select IF(@result>0,'成功','失败');  

end ##;


call myp6('老刘','1111')

创建out 模式参数的存储过程(j接受返回的值)

#案例1：根据输入的女神名，返回对应的男神名


delimiter %%

create procedure myp7(in beautyName varchar(20), out boyName varchar(20))

begin
	select boys.boyname into boyName from boys join beauty on 
		boys.id = beauty.boyfriend_id 
	where 
		beauty.`name` = beautyName;
	

end %%

drop procedure myp7
call myp7('周芷若', @boyName)  -- 执行定义一个参数boyName接受返回的值


select @boyName  -- 调用定义的局部变量

--
#案例2：根据输入的女神名，返回对应的男神名和魅力值
select database()
create procedure my10(in bName varchar(32), out byName varchar(32),out usercp int )
begin 
		select boys.boyname ,boys.usercp into byName,usercp   -- 如果时返回一个表内的多个参数然后赋值的话 波许这样使用不可以分开使用
		from boys 
		right join 
			beauty b
		on 
			b.boyfriend_id = boys.id
		where 
			b.name = bName;
			
end ;

-- 调用
call my10('小昭',@name,@cp)
select @name,@cp

创建带inout模式参数的存储过程

CREATE PROCEDURE myp8(INOUT a INT ,INOUT b INT)
BEGIN
	SET a=a*2;
	SET b=b*2;
END $

#调用
SET @m=10$
SET @n=20$
CALL myp8(@m,@n)$
SELECT @m,@n$

删除存储过程

#语法：drop procedure 存储过程名
DROP PROCEDURE p1;
DROP PROCEDURE p2,p3;#×

查看存储过程的信息

DESC myp2;×
SHOW CREATE PROCEDURE  myp2;

practices

# 创建存储过程实现，用户是否登录成功


delimiter $$
create procedure  myp5(in username varchar(10), in pwd varchar(10) )

		select count(*) from admin 
		where 
		 admin.username = username and admin.password = pwd;
		 
end $$

call myp5('老王',1234)


delimiter ## 
create procedure myp6(in username varchar(20),in pwd varchar(10))
begin 
		declare result int default 0;  -- 建立一个变量默认值为0
		select count(*) into @result from admin  -- 变量赋值给count(*) 查验出来的结果
		where 
		admin.username = username and
		admin.password=pwd;
		select IF(@result>0,'成功','失败');  

end ##;


call myp6('老刘','1111')


#案例1：根据输入的女神名，返回对应的男神名


delimiter %%

create procedure myp7(in beautyName varchar(20), out boyName varchar(20))

begin
	select boys.boyname into boyName from boys join beauty on 
		boys.id = beauty.boyfriend_id 
	where 
		beauty.`name` = beautyName;
	

end %%

drop procedure myp7
call myp7('周芷若', @boyName)  -- 调用并传值这个时候  外部局部变量就需要加上@


select @boyName  -- 调用定义的局部变量





#案例1：传入a和b两个值，最终a和b都翻倍并返回

delimiter %%
create procedure myp9(inout a int ,inout b int )

begin 
	
	set a=a*2;
	set b=b*2;

end %%
set @m=10;
set @n = 15;
call myp9(@m,@n);
select @m,@n


select * from boys;
#案例2：根据输入的女神名，返回对应的男神名和魅力值


select database()
create procedure my10(in bName varchar(32), out byName varchar(32),out usercp int )
begin 
		select boys.boyname ,boys.usercp into byName,usercp   -- 如果时返回一个表内的多个参数然后赋值的话 波许这样使用不可以分开使用
		from boys 
		right join 
			beauty b
		on 
			b.boyfriend_id = boys.id
		where 
			b.name = bName;
			
end ;

-- 调用
call my10('小昭',@name,@cp)
select @name,@cp

-- 创建存储过程实现传入女神编号，返回女神名称和男神名称

create procedure my1(in gname varchar(32), out bname varchar(32),out boyName varchar(32))

begin 
	
		select b.name, boys.boyName into bname, boyName
		from boys right join 
		beauty b on
		
		boys.id = b.boyfriend_id
		where b.name = gname;

end ;


call my1('周芷若',@m,@n)
select @m,@n


select * from beauty

desc beauty
-- 创建存储过程实现传入两个女神生日返回大小

create procedure my2(in b1birsthday datetime, in b2birsthday datetime ,out result int )

begin 
		
		select DATEDIFF(b1birsthday,b2birsthday) into result;

end 


--调用
call my2('1988-02-03',now(),@re);

select @re


# 创建存储过程实现传入一个日期，格式化成 xx 年 xx 月 xx 日并返回

create procedure my3(in mydate datetime,out strdate varchar(50))

begin
	select DATE_FORMAT(mydate,'%y年%m月%n日') into strdate;
end 

call my3('1988-02-03',@str)
select @str


/*
创建存储过程或函数实现传入女神名称，返回：女神 and 男神 格式的字符串
如 传入 ：小昭
返回： 小昭 and 张无忌


*/

select * from beauty
create procedure my4(in beautyname varchar(30), out strName varchar(50))

begin 

	select  CONCAT(b.name,'and', boyName) into strName
	from boys right join
	beauty b 
	on
		b.boyfriend_id = boys.id
		where b.name = beautyname;

end 

call my4('小昭',@str)
select @str

# 创建存储过程或函数，根据传入的条目数和起始索引，查询 beauty 表的记录

create procedure my5(in be int , in cou int)

begin 
	select * from beauty limit be,cou;
end 

call my5(1,6);  # 从第二条开始查 查出6条

函数

什么是函数

含义：一组预先编译好的SQL语句的集合，理解成批处理语句

函数的作用

1、提高代码的重用性
2、简化操作
3、减少了编译次数并且减少了和数据库服务器的连接次数，提高了效率

存储过程和函数的区别

存储过程：可以有0个返回，也可以有多个返回，适合做批量插入、批量更新
函数：有且仅有1 个返回，适合做处理数据后返回一个结果

创建语法

CREATE FUNCTION 函数名(参数列表) RETURNS 返回类型
BEGIN
函数体
END

注意

1.参数列表 包含两部分：
参数名 参数类型

2.函数体：肯定会有return语句，如果没有会报错
如果return语句没有放在函数体的最后也不报错，但不建议

return 值;
3.函数体中仅有一句话，则可以省略begin end
4.使用 delimiter语句设置结束标记

调用语法

SELECT 函数名(参数列表)

#案例：返回公司的员工个数
create FUNCTION mym1() returns int 
begin 
		declare c int default 0;  -- 定义局部变量
		SELECT COUNT(*) into c   -- 赋值
		from employees ;
		return c ;  -- 局部变量不需要@ 

end 

drop function mym1;

select mym1();

#案例1：根据员工名，返回它的工资
delimiter &&
create function m1(eName varchar(32)) returns double 
begin 
	set @sal=0; #定义用户变量 
   select salary into @sal  # 赋值
	 from employees where last_name =  eName;
	 return @sal;
end &&
drop function m1
select * from employees
select m1('Kochhar')


#案例2：根据部门名，返回该部门的平均工资

create function m2(dName varchar(32)) returns int

begin 
		
		declare sal DOUBLE default 0;
		select AVG(salary) into sal
			from departments d join employees e 
		on 
			d.department_id = e.department_id where d.department_name = dName;
		return sal;
		
end 

select m2('IT')

查看函数

show create function 函数名
show create function my3查看函数my3的函数信息

删除函数

drop
DROP FUNCTION myf3;

# 创建函数，实现传入两个 float，返回二者之和
create function m3(f1 float,f2 float) returns FLOAT

begin 
		
			set @cou=0;
			select f1+f2 into @cou;
			return @cou;
		
end 
select m3(1.1,3.2);

# 2、创建函数，实现传入工种名，返回该工种的员工人数

create function m4(jName varchar(32)) returns int 
begin
	declare num int;
  select  count(*) into num from jobs right join employees e 
	on
		e.job_id = jobs.job_id
		where 
			jobs.job_title = jName;
		return num;
end 
select * from jobs
drop function m4
select m4("Accounting Manager");


# 3、创建函数，实现传入员工名，返回该员工的领导名

select * FROM employees

create FUNCTION m6(eame VARCHAR(32)) RETURNS varchar(64)
BEGIN
		DECLARE mame varchar(64);
		select m.last_name into mame  from employees e inner JOIN employees m
		on
			e.manager_id =m.employee_id
		where e.last_name = eame;
		return mame;
end


select m6('De Haan')

本文地址：https://blog.csdn.net/Lovely_red_scarf/article/details/107666737