011 学习笔记--视图 + 存储过程(if\case\while\repeat\loop\cursor) + 存储函数

视图：

视图：是一种虚拟的表。视图中的数据在数据库中并不实际存在，行和列的数据来自自定义视图中查询使用的表，并且是在使用视图时动态生成的。
创建视图：create or replace view viewname as select 语句 [with[cascaded|local|check option]]
- 例如：create or REPLACE view View_GetUsers as select * from tb_users;
查询视图：
- 查看创建视图语句：show create view View_GetUsers
- 查看视图数据：select * from View_GetUsers
修改
- create or replace view viewname as select ...... [with[cascaded|local|check option]]
- alter view viewname as select ...... [with[cascaded|local|check option]]
删除：drop view if exists viewname

注：[with[cascaded|local|check option]]，为可选项.................

存储过程：

特点：封装、复用，可以接受参数，也可以返回数据，减少网络交互，效率提升

语法：

创建
- create procudure 存储过程名称(参数列表)
- begin
- -----------sql
- end;
调用
- call 存储过程名称(参数列表)

-- --------------------------------存储过程--------------------------------
-- 创建
create PROCEDURE proctest()
begin 
	select count(*) from tb_users;
end;
-- 调用
call proctest();
-- 查看
-- 查询指定数据库的存储过程及状态信息
select * from information_schema.ROUTINES where ROUTINE_SCHEMA='proctest';
-- 查询某个存储过程的定义
show create PROCEDURE proctest;

-- 查看结果如下
CREATE DEFINER=`root`@`localhost` PROCEDURE `proctest`()
begin 
	select count(*) from tb_users;
end

-- 删除
drop PROCEDURE if EXISTS proctest;

变量 —— 默认session　　
- 系统变量：是Mysql服务器提供，不是用户定义的，属于服务器层面。分为全局变量(golbal)、回话变量(session)
  - 查看系统变量
    - show [session|global] variables;——查询所有系统变量
    - show [session|global] variables like '......';——可以通过like模糊匹配方式查找变量
    - select @@[session|global] 系统变量名； ——查看指定变量的值
  - 设置系统变量
    - set [session|global] 系统变量名 = 值；
    - set @@ [session|global] 系统变量名 = 值；
    - -- -- -- -- -- -- -- -- -- -- -- -- -- 变量-- -- -- -- -- -- -- -- -- -- -- -- show session variables; show session variables like 'auto%'; show global variables; show global variables like 'auto%'; select @@session.autocommit; select @@global.autocommit; set SESSION autocommit =0; set SESSION autocommit =1;　
      注：如果没有制定session/global，默认是session--回话变量。mysql服务重新启动之后，所设置的全局参数会失效，要想不失效，可以在/etc/my.cnf中配置。
- 用户自定义变量
  - 定义：是用户根据需要自己定义的变量，用户变量不用提前生命，在用的时候直接用 @变量名称就可以使用。其作用域为当前连接。　
  - 赋值：
    - set @var_name=expr,.......
    - set @var_name:=expr,.......
    - select @var_name := exper.....
    - select 字段名 into @var_name from 表名
  - 使用：select @var_name;
  - 示例如下所示：
  - ```
  -- 用户自定义变量
  -- 赋值
  set @myname='sunkun';
  set @myage:=18;
  set @mygender:='boy',@myhobby:='C#';
  
  select @mynickname:='kun';
  select count(*) into @mycunt from tb_users;
  -- 使用
  select @myname,@myage,@mygender,@myhobby,@mynickname,@mycunt;
```
  注：用户定义的变量无需对其进行声明或初始化，只不过获取到的值为NULL。
- 局部变量
  - 定义：是根据需要定义在局部生效的变量，访问之前，需要declare声明。可用作存储过程内的局部变量和输入参数，局部变量的范围是在其内声明的begin..............end块。
  - 声明：declare 变量名变量类型 [default...]；变量类型就是数据库字段类型：int\bigint\char\varchar\date\time等。
  - 赋值：set 变量名 = 值、set 变量名：= 值、select 字段名 into 变量名 from 表名....
  - 示例如下所示：
  - ```
  -- 创建
  create PROCEDURE proctest2()
  begin 
  	DECLARE stucount int DEFAULT 0;
  	DECLARE stuname varchar(50);
  	
  	set stucount =18,stuname='sunwugang';
  	select stuname,stucount;
  	
  end;
  -- 调用
  call proctest2();
```
IF

语法：
if 条件 then ......
ELSEIF 条件 THEN ......  -- 可选
else ......  --  可选
end if

- 示例如下所示：

create PROCEDURE proc3()
BEGIN
	DECLARE age int DEFAULT 22;
	DECLARE temp VARCHAR(20);

	if age>=18 and age<=30 then set temp:='初级';
	ELSEIF age >=30 and age<=40 THEN set temp='中级';
	else set temp :='高级';
	end if;

select temp;
END;

call proc3(); -- return  初级

参数

-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- 输入输出参数示例
drop PROCEDURE if EXISTS proc4;
create PROCEDURE proc4(in age int,out temp varchar(20))
BEGIN
-- 	DECLARE age int DEFAULT 22;
-- 	DECLARE temp VARCHAR(20);

	if age<=25 then 
		set temp:='初级';
	ELSEIF age<=40 THEN 
		set temp='中级';
	else set temp :='高级';
	end if;

select temp;

END;

call proc4(28,@temp); 
select @temp;


drop PROCEDURE if EXISTS proc5;
create PROCEDURE proc5(in age int,in name varchar(50),out temp varchar(20))
BEGIN
-- 	DECLARE age int DEFAULT 22;
-- 	DECLARE temp VARCHAR(20);

	if age>=18 and age<=30 then set temp:=CONCAT(name,',初级');
	ELSEIF age >=30 and age<=40 THEN set temp=CONCAT(name,',中级');
	else set temp :=  CONCAT(name,',高级');
	end if;

END;
call proc5(28,'sunwugang',@temp); 
select @temp;


drop PROCEDURE if EXISTS proc6;
create PROCEDURE proc6(in name varchar(50),inout age int,out temp varchar(20))
BEGIN
-- 	DECLARE age int DEFAULT 22;
-- 	DECLARE temp VARCHAR(20);
 set age:= age+10;

	if age>=18 and age<=30 then set temp:=CONCAT(name,',初级');
	ELSEIF age >=30 and age<=40 THEN set temp=CONCAT(name,',中级');
	else set temp :=  CONCAT(name,',高级');
	end if;

END;

set @age=22;
call proc6('sunwugang',@age,@temp); 
select @age,@temp;

case

-- case when else end 
/*
(
case 字段 when xxx then xxx
          when xxx then xxx
				  else xxx
end
)as 重命名字段
*/
select t.*,
(case content when '001' then '一级戒备' 
              when '002' then '二级戒备'
              else '三级戒备' 
end)戒备戒备
from remarkinfo t
ORDER BY t.content;

drop PROCEDURE if EXISTS proc7;
create PROCEDURE proc7(in month int)
BEGIN
	DECLARE result VARCHAR(100);
	
	CASE
		when month>=1 and month<=3 then 
				 set result:='第一季度';
		when month>=4 and month<=6 then 
				 set result:='第二季度';
		when month>=7 and month<=9 then 
				 set result:='第三季度';
		when month>=10 and month<=12 then 
				 set result:='第四季度';
		else set result:='无效参数';
	end case; 

select CONCAT('输入的月份为：',month,',所属季度为：',result);
END;

call proc7(8);

循环

while：满足条件执行循环

drop PROCEDURE if EXISTS proc8;
create PROCEDURE proc8(in num int,out count int)
BEGIN
	DECLARE result int DEFAULT 0;

 	while num >0 do
 		set result := result + num;
 		set num := num -1;
  end WHILE;

  set count:=result;
END;
 
call proc8(4,@count);
select @count;

repeat：当满足条件的时候退出循环

-- repeat 循环
drop PROCEDURE if EXISTS proc9;
create PROCEDURE proc9(in num int,out count int)
BEGIN
	DECLARE result int DEFAULT 0;

 	repeat 
 		set result := result + num;
 		set num := num -1;
  until num<=0
	end repeat;

  set count:=result;
END;
 
call proc9(4,@count);
select @count;

loop

实现简单的循环，如果不在sql逻辑中增加退出循环的条件，可以用其来实现简单的死循环。Loop可以配合以下两个语句使用：
LEAVE：配合循环使用，退出循环；
ITERATE：必须用在循环中，作用是跳过当前循环剩下的语句，直接进入下一次循环；
示例如下所示：

-- LOOP 计算从1累加到N的值(只计算偶数)，N为入参
drop PROCEDURE if EXISTS proc10;
create PROCEDURE proc10(in num int,out count int)
BEGIN
	DECLARE total int DEFAULT 0;

 	sum:LOOP 
			if num <=0 then 
				leave sum;					-- leave 退出循环
			end if;

			set total := total + num;
			set num := num -1;

	end LOOP sum;

  set count:=total;
END;
 
call proc10(4,@count);
select @count;


/*
leave 退出循环
iterate 类似continue
*/
-- LOOP 计算从1累加到N的值，N为入参
drop PROCEDURE if EXISTS proc11;
create PROCEDURE proc11(in num int,out count int)
BEGIN
	DECLARE total int DEFAULT 0;

 	sum:LOOP 
			if num <=0 then 
				leave sum;					-- 
			end if;

			if num %2 =1 then 
				set num := num -1;
				iterate sum;					-- iterate 类似continue
			end if;

			set total := total + num;
			set num := num -1;

	end LOOP sum;

  set count:=total;
END;
 
call proc11(4,@count);
select @count;

cursor游标

游标，是用来存储查询结果集的数据类型，在存储过程和函数中可以使用游标对结果集进行循环的处理。
游标的使用包括：游标的声明、OPEN、FETCH、CLOSE，其语法如下所示：
- 声明游标：declare 游标名称 cursor for 查询语句；
- 打开游标：open 游标名称；
- 获取游标记录：fetch 游标名称 into 变量1，变量2，变量N;
- 关闭游标：close 游标名称；
条件处理程序
- 条件处理程序(handler)可用来定义在流程控制结构执行过程中遇到问题时相应的处理步骤，语法如下：
示例如下所示：实现将age =18的用户信息插入 tb_users2
select * from tb_users;

-- -------------------------------------游标-------------------------------------

-- 存储过程，实现age =18的 数据插入 tb_users2
drop PROCEDURE if EXISTS proc12;
create PROCEDURE proc12(in inage int)
BEGIN
	declare uname varchar(100);
	declare uage int;
	declare ustatus char(1);
	declare ugender char(1);
	declare uphone varchar(11);

-- 	1.声明游标
	DECLARE user_cursor cursor for select NAME,age,status,gender,phone from tb_users where age = inage; 	

-- 方式一条件处理程序，当SQLSTATE为 02000时，退出游标 为状态码
-- 	DECLARE exit handler for SQLSTATE '02000' CLOSE user_cursor;

-- 方式二
 	DECLARE exit handler for not found CLOSE user_cursor;

-- 创建表,可将建表部分至于存储过程之外
  DROP table if EXISTS tb_users2;
	create table if not exists tb_users2
	(
		id int auto_increment PRIMARY key COMMENT '主键',
		name VARCHAR(100) not null unique COMMENT '姓名',
		age INT CHECK (age >0 && a<=120),
		status char(1) DEFAULT '1' COMMENT '状态',
		gender char(1) COMMENT'性别',
		phone VARCHAR(11) COMMENT '手机号'
	)COMMENT'用户表';

-- 2.打开游标
	open user_cursor;
	while true do 
-- 	3.循环操作数据
		fetch user_cursor into uname,uage,ustatus,ugender,uphone;
		insert into tb_users2 values(null,uname,uage,ustatus,ugender,uphone);
	end while;
-- 4.关闭游标
	close user_cursor;
END;
 
call proc12(18);
SELECT * from tb_users2;

存储函数：

存储函数是有返回值的存储过程，存储函数的参数只能是 IN 类型的。其语法如下所示
示例如下所示：

-- -----------------------------存储函数-----------------------------
drop function  if EXISTS fun1;
create function fun1(num int)
returns int DETERMINISTIC
BEGIN
	declare total int DEFAULT 0;
-- DECLARE stucount int DEFAULT 0;
	
	while num>0 do
		set total:=total + num;
		set num:=num -1;
	end while;
	
	return total;
END;

-- 调用存储函数
select fun1(4);

posted on 2023-07-21 17:14 sunwugang 阅读(42) 评论(0) 收藏举报

刷新页面返回顶部

欢迎莅临 SUN WU GANG 的园子！！！

公告