欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识！
您现在的位置是： 首页  >  IT编程

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

程序员文章站 2022-06-21 10:37:12
变量的使用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的...

变量的使用

  • 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

相关标签： mysql

上一篇： 本地配置mongodb，配置为服务，100错误解决方案

下一篇： 中兴新机型通过Wi-Fi联盟认证 或是Axon 8

推荐阅读