发布培训的免费网站模板下载,山西太原做企业网站建设的公司,自己做网站怎么跳过备案,列表网推广收费标准存储过程 1、基础语法2、变量2.1 系统变量2.2 用户自定义变量2.3 局部变量 3、if 流程控制4、参数5、case 流程控制6、循环结构6.1 while 循环6.2 repeat 循环6.3 loop 循环 7、游标 存储过程是事先经过编译并存储在数据库中的一段 SQL 语句的集合#xff0c;调用存储过程可以… 存储过程 1、基础语法2、变量2.1 系统变量2.2 用户自定义变量2.3 局部变量 3、if 流程控制4、参数5、case 流程控制6、循环结构6.1 while 循环6.2 repeat 循环6.3 loop 循环 7、游标 存储过程是事先经过编译并存储在数据库中的一段 SQL 语句的集合调用存储过程可以简化应用开发人员的很多工作减少数据在数据库和服务器之间的传输对于提高数据处理的效率是有好处的。 存储过程就是数据库 SQL 语言层面的代码封装与重用。 特点 1- 封装、复用 2- 可以接收参数也可以返回数据 3- 减少网络交互效率提升 1、基础语法
创建
-- 创建存储过程
create procedure 存储过程名称([参数列表])
beginSQL 语句;
end;-- 范例
create procedure p1()
beginselect * from user;select id, name from emp where id 10;
end;在命令行中执行创建存储过程的 SQL 时需要通过关键字 delimiter 指定 SQL 语句的结束符。
-- 例如指定 为 SQL 语句的结束符
delimiter 调用
-- 调用存储过程
call 存储过程名称([参数列表]);-- 范例
call p1();查看
-- 查询指定数据库的存储过程及状态信息
-- xxx指定数据库
select * from information_schema.routines where routine_schema xxx;-- 查询某个存储过程的定义
show create procedure 存储过程名称;删除
-- 删除存储过程
drop procedure [if exists] 存储过程名称;2、变量
变量有三种系统变量用户自定义变量局部变量。
2.1 系统变量
系统变量是 MySQL 服务器提供不是用户定义的属于服务器层面。分为全局变量global、会话变量session。默认是 session 级别。 查看系统变量语法
-- 查看所有系统变量
show [global | session] variables;-- 可以通过 like 模糊匹配方式查找变量
show [global | session] variables like ...;-- 查看指定变量的值注意globalsession后面需要加.
select [global. | session.]系统变量名;设置系统变量语法
set [global | session] 系统变量名 值;set [global | session]系统变量名 值;2.2 用户自定义变量
用户自定义变量是用户根据需要自己定义的变量用户变量不用提前声明在用的时候直接用 “变量名” 使用就可以了。其作用域为当前连接。 给用户自定义变量赋值语法
-- 使用 set 方式赋值
set var_name expr [, var_name expr...];
-- 推荐使用 : 进行赋值。因为 MySQL 中的赋值运算符和比较运算符都是 为了区分推荐使用 :
set var_name : expr [, var_name : expr...];-- 使用 select 方式赋值
select var_name : expr [, var_name : expr...];-- 将 table_name 表中的 column_name 字段的值赋值给 var_name 变量
select column_name into var_name from table_name;使用自定义变量语法
select var_name [, var_name...];用户定义的变量无需对其进行声明或初始化只不过获取到的值为 NULL。
2.3 局部变量
局部变量是根据需要定义的在局部生效的变量访问之前需要 declare 声明。可用作存储过程内的局部变量和输入参数局部变量的范围是在存储过程内声明的 begin … end 块内。 局部变量声明语法
-- 声明局部变量需要指定变量类型
-- 变量类型就是数据库字段类型int、bigint、char、varchar、date、time等。
-- 可以使用 default 指定默认值
declare 变量名 变量类型 [default ...];局部变量赋值语法
set 变量名 值;set 变量名 : 值;select 字段名 into 变量名 from 表名...;3、if 流程控制
语法
if 条件1 then...
elseif 条件2 then -- 可选...
else -- 可选...
end if;案例
-- 根据定义的分数 score 变量判定当前分数对应的分数等级
-- score 85等级为优秀
-- score 60且 score 85等级为及格
-- score 60等级为不及格create procedure p1()
begindeclare score int default 58;declare result varchar(10);if score 85 thenset result : 优秀;elseif score 60 thenset result : 及格;else set result : 不及格;end if;select result;
end;4、参数
存储过程的参数有三种
类型含义in该类参数作为输入也就是需要调用时传入值默认out该类参数作为输出也就是该参数可以作为返回值inout既可以作为输入参数也可以作为输出参数
语法
create procedure 存储过程名称([in/out/inout 参数名 参数类型])
begin-- sql 语句
end;案例1成绩等级评定
-- 根据传入的分数 score 变量判定当前分数对应的分数等级并返回
-- score 85等级为优秀
-- score 60且 score 85等级为及格
-- score 60等级为不及格create procedure p1(in score int, out result varchar(10))
beginif score 85 thenset result : 优秀;elseif score 60 thenset result : 及格;elseset result : 不及格;end if;
end;-- 调用该存储过程
-- 输出参数需要用户自定义变量接收
call p1(68, result);
-- 展示结果
select result;案例2成绩转换
-- 将传入的 200 分制的分数进行换算换算成百分制然后返回
create procedure p2(inout score double)
beginset score : score * 0.5;
end;-- 由于是 inout 类型的参数所以在调用该存储过程前需要准备一个自定义变量并赋值
set score : 78;
-- 调用该存储过程
call p2(score);
-- 展示转换结果
select score;5、case 流程控制
语法
-- 语法一
case case_valuewhen when_value1 then statement_list1[when when_value2 then statement_list2]...[else statement_list]
end case;-- 语法二
case when search_condition1 then statement_list1[when search_condition2 then statement_list2]...[else statement_list]
end case;案例
-- 根据传入的月份判定月份所属的季节要求采用 case 结构
-- 1. 1-3月份为第一季度
-- 2. 4-6月份为第二季度
-- 3. 7-9月份为第三季度
-- 4. 10-12月份为第四季度
create procedure p3(in month int, out quarter varchar(10))
begincase when month 1 and month 3 then set quarter : 第一季度;when month 4 and month 6 then set quarter : 第二季度;when month 7 and month 9 then set quarter : 第三季度;when month 10 and month 12 then set quarter : 第四季度;else set quarter : 非法参数;end case;
end;6、循环结构
6.1 while 循环
while 循环是有条件的循环控制语句。满足条件后再执行循环体中的 SQL 语句。 语法
-- 先判定条件如果条件为 true则执行逻辑否则不执行逻辑
while 条件 doSQL 逻辑...
end while;案例
-- 计算从 1 累加到 n 的值n 为传入的参数值。
create procedure p4(in n int)
begindeclare total int default 0; while n 0 doset total : total n;set n : n - 1;end while;select total;
end;6.2 repeat 循环
repeat 是有条件的循环控制语句当满足条件的时候退出循环。 语法
-- 先执行一次逻辑然后判定逻辑是否满足如果满足则退出。如果不满足则继续下一次循环。
-- 注意 until 后面没有结束符
repeat SQL 逻辑...
until 条件
end repeat;案例
-- 计算从 1 累加到 n 的值n 为传入的参数值。
create procedure p5(in n int)
begindeclare total int default 0;repeatset total : total n;set n : n - 1;until n 0end repeat;select total;
end;6.3 loop 循环
loop 实现简单的循环如果不在 SQL 逻辑中增加退出循环条件可以用其来实现简单的死循环。loop 可以配合以下两个语句使用
leave配合循环使用退出循环。功能相当于 breakiterate必须用在循环中作用是跳过当前循环剩下的语句直接进入下一次循环。功能相当于 continue
语法
-- 对于 loop 循环可以设置一个标记。
-- begin_label 就是自定义的标记来标识当前的 loop 循环
[begin_label:] loopSQL 逻辑...
end loop [end_label];-- 退出指定标记的循环体
leave label;-- 直接进入下一次循环
iterate label;案例1
-- 计算从 1 累加到 n 的值n 为传入的参数值
create procedure p6(in n int)
begindeclare total int default 0;sum:loopif n 0 thenleave sum;end if;set total : total n;set n : n - 1;end loop sum;select total;
end;案例2
-- 计算从 1 到 n 之间的偶数累加的值n 为传入的参数值
create procedure p7(in n int)
begindeclare total int default 0;sum:loopif n 0 thenleave sum;else if mod(n, 2) 1 thenset n : n - 1;iterate sum;end if;set total : total n;set n : n - 1;end loop sum;select total;
end;7、游标
游标cursor是用来存储查询结果集的数据类型在存储过程和函数中可以使用游标对结果集进行循环的处理。游标的使用包括游标的声明、open、fetch、close其语法分别如下。
-- 声明游标
declare 游标名称 cursor for 查询语句;-- 打开游标
open 游标名称;-- 获取游标记录
fetch 游标名称 into 变量 [, 变量];-- 关闭游标
close 游标名称;案例 根据传入的参数 uage来查询用户表 tb_user 中所有的用户年龄小于等于 uage 的用户姓名name和职业job并将用户的姓名和职业插入到所创建的一张新表id, name, job中。
-- 逻辑
-- A. 声明游标存储查询结果集
-- B. 准备创建表结构
-- C. 开启游标
-- D. 获取游标中的记录
-- E. 插入数据到新表中
-- F. 关闭游标create procedure p8(in uage int)
begindeclare uname varchar(10); -- 注意需要先声明变量再声明游标declare ujob varchar(10);declare u_cursor cursor for select name, job from user where age uage;drop table if exists user_job;create table if not exists user_job(id int primary auto_increment commit 主键ID,name varchar(10) unique commit 姓名,job varchar(10) commit 职业) commit 用户职业表;open u_cursor;while true dofetch u_cursor into uname, ujob;insert into user_job values(null, uname, ujob);end while; close u_cursor;
end;-- 调用存储过程 p8()
call p8(40);注意需要先声明变量再声明游标。 上面的代码执行会报错这是因为 while 的条件为 true为死循环而循环体中u_cursor 游标遍历完后就拿不到数据了再向 user_job 表插入数据就会报错。报错信息为
[02000][1329] No data -zero rows fetched, selected, or procedded可以通过条件处理程序解决这个问题。条件处理程序可以用来定义在流程控制结构执行过程中遇到问题时相应的处理步骤。 条件处理程序语法
declare handler_action handler for condition_value [, condition_value]... statement;-- handler_action
-- continue: 继续执行当前程序
-- exit终止执行当前程序
-- condition_value
-- sqlstate sqlstate_value状态码如02000
-- sqlwarning所有以 01 开头的 sqlstate 代码的简写
-- not found所有以 02 开头的 sqlstate 代码的简写
-- sqlexception所有没有被 sqlwarning 或 not found 捕获的 sqlstate 代码的简写-- condition_value 可以不指定具体的状态码若要捕获所有以 02 开头的状态码可以直接写 not found。完善后的 sql 如下;
create procedure p8(in uage int)
begin-- 注意需要先声明变量再声明游标declare uname varchar(10); declare ujob varchar(10);declare u_cursor cursor for select name, job from user where age uage;-- 声明条件处理程序-- 指定处理行为是 exit 退出循环-- 指定条件为 sql 状态码为 02000 时触发-- 退出循环后运行的语句为关闭游标 close u_cursor;declare exit handler for sqlstate 02000 close u_cursor;drop table if exists user_job;create table if not exists user_job(id int primary auto_increment commit 主键ID,name varchar(10) unique commit 姓名,job varchar(10) commit 职业) commit 用户职业表;open u_cursor;while true dofetch u_cursor into uname, ujob;insert into user_job values(null, uname, ujob);end while; close u_cursor;
end;-- 调用存储过程 p8()
call p8(40);
