网站建设范本,微信软文,京东联盟网站建设电脑版,水利建设相关网站什么是存储过程#xff1f; 存储过程是事先经过编译并存储在数据库的一段sql语句的集合 如何创建一个存储过程#xff1f;
create procedure 存储过程名称([参数列表])
beginsql语句;
end#例
create procedure p1()
beginselect * from t_goods;select * from t_user;
end如…什么是存储过程 存储过程是事先经过编译并存储在数据库的一段sql语句的集合 如何创建一个存储过程
create procedure 存储过程名称([参数列表])
beginsql语句;
end#例
create procedure p1()
beginselect * from t_goods;select * from t_user;
end如果是在命令行中创建存储过程会遇见分号结束的问题需要用delimiter来修改sql语句结束的标志 例 “delimiter #” 就是将#作为sql语句结束的标志 怎么使用存储过程
call 存储过程名称([参数列表])
# 例
call p1()怎么查看已经创建的存储过程
# 查询指定数据库的存储过程及状态信息
select * from information_schema.routines where routine_schema数据库名称
# 例
select * from information_schema.routines where routine_schemaseckill# 查询某个存储过程的定义
show create procedure 存储过程名称
# 例
show create procedure p1怎么删除一个存储过程
drop procedure [if exists] 存储过程名称
# 例
drop procedure p1变量
系统变量 系统变量是mysql服务器提供分为全局变量(global)会话变量(session) 如果没有指定session/global默认为session msyql重启后设置的全局参数都会失效要想不失效可以在/etc/my.cnf中配置(linux) 查看系统变量 # 查看所有系统变量
show [session|global] variables
# 例
show global variables# 通过like模糊匹配查找系统变量
show [session|global] variables like ...
# 例
show global variables like admin%# 查看指定变量的值
select [session|global] 系统变量名
# 例 查autocommit的值
select autocommit
select session.autocommit 设置系统变量 set [session|global] 系统变量名值
# 例
set session autocommit 0set [session|global] 系统变量名值
# 例
set session.autocommit 1用户变量 用户变量是用户自己定义的变量用户变量不用提前声明在用的时候直接使用变量名使用就可以其作用域为当前连接 变量赋值 # 四种方式
set 变量名 值, 变量名 值 ...
# 例
set stu_name zdy, stu_age 18set 变量名 : 值, 变量名 : 值 ...
# 例
set stu_name : zzz, stu_age : 20select 变量名: 值, 变量名 : 值 ...
# 例
select stu_name : zdz, stu_age : 16select 字段名 from 表名 into 变量名
# 例 将cout(*)的值存入变量
select count(*) from t_user into user_count使用 select 变量名1, 变量名2....
# 例
select stu_name, stu_age局部变量 局部成效要通过declare成名之后才能使用可作为存储过程内的局部变量和输入参数局部变量的范围是在其内声明的begin…end模块 声明局部变量 变量类型就是数据库字段类型 int、char、varchar… declare 变量名 变量类型[default ...]变量赋值 set 变量名 值
set 变量名 : 值
select 字段名 from 表名 into 变量名 ...整体案例 create procedure p3()
begin# 定义变量declare good_count int default 0;# 变量复制select count(*) from t_goods into good_count;# 查询变量值select good_count;
end存储过程中使用IF条件判断
create procedure p4()
begindeclare score int default 67;declare result varchar(10);if score 80 thenset result : 优秀;elseif score 60 thenset result : 及格;elseset result : 不及格;end if;select result;
endcall p4()存储过程参数 参数有三种类型 IN 该类参数作为输入也就是需要调用时传入值也是默认类型 OUT 该类参数作为输出也就是该参数可以作为返回值 INOUT 该类参数既可以作为输入参数也可以作为输出参数 使用 create procedure 存储过程名称([IN/OUT/INOUT 数名 参数类型])
beginsql语句;
end案例 create procedure p5(in goods_count int, out result varchar(10))
beginif goods_count 1 thenset result : 有库存;elseset result : 库存不足;end if;
endcall p5(2, result)
select result存储过程中使用case 语法一 # 如果case_value等于v1则执行statement1语句等于v2则执行statement2语句...否则执行statementn语句
case case_valuewhen v1 then statement1when v2 then statement2...else statementn
end case
# 例
create procedure p7(in month int, out result varchar(10))
begincase monthwhen 1 thenset result : 一月;when 2 thenset result : 二月;when 3 thenset result : 三月;elseset result : 非法参数;end case;
endcall p7(2, result)
select result语法二 # 如果condition1满足执行statement1如果condition2满足执行statement2...否则执行statementn语句
casewhen condition1 then statement1when condition2 then statement2...else statementn
end case
# 例
create procedure p6(in month int, out result varchar(10))
begincasewhen month 1 month 3 thenset result : 第一季度;when month 4 month 6 thenset result : 第二季度;when month 7 month 9 thenset result : 第三季度;when month 10 month 12 thenset result : 第四季度;elseset result : 非法参数;end case;
endcall p6(2, result)
select result存储过程中使用while 语法(满足条件进入循环) while 条件 dosql逻辑
end while
# 例 计算1-100的累加
create procedure p8(in n int)
begindeclare sum int default 0;while n 0 doset sum : sum n;set n : n - 1;end while;select sum;
endcall p8(100)
存储过程中使用repeat 语法(满足条件退出循环) repeatsql逻辑until 条件
end repeat
# 例
create procedure p9(in n int)
begindeclare sum int default 0;repeatset sum : sum n;set n : n - 1;until n 0end repeat;select sum;
endcall p9(100)
存储过程中使用loop 语法 如果不在sql逻辑中增加退出循环的条件可以用来实现简单的死循环loop可以配合下面的两个语句使用 leave 退出循环相当于Java的break iterate 跳过当前循环相当于Java的continue [label:] loopsql逻辑
end loop [label]
# 例1 1-n的累加
create procedure p10(in n int)
begindeclare sum int default 0;sum:loopif n0 thenleave sum;end if;set sum : sum n;set n : n - 1; end loop sum;select sum;
end
call p10(100)
# 例2 1-n的偶数的累加
create procedure p11(in n int)
begindeclare sum int default 0;count:loopif n0 thenleave count;end if;if n%2 1 thenset n : n - 1; iterate count;end if;set sum : sum n;set n : n - 1; end loop count;select sum;
endcall p11(100)
游标类型 游标类型用于存储查询结果集在存储过程和函数中可以使用游标对结果集进行循环的处理。游标的使用包括游标的声明、open、fetch、close 声明游标 declare 游标名 cursor for 查询语句打开游标 open 游标名称获取游标记录 fetch 游标名称 into 变量1,变量2...关闭游标 close 游标名称案例 根据传入的参数uage来查询用户表tb_user中所有的用户年龄小于等于uage的用户姓名(name)和专业(profession)并将用户的姓名和专业插入到新创建的一张表(id,name,profession)中 create procedure p12(in uage int)
begin# 1. 声明游标存储结果集 声明时要先声明变量再声明游标declare uname varchar(100);declare upro varchar(100);declare u_cursor cursor for select name, peofession from tb_user where age uage;# 2. 准备创建表结构create table if not exists tb_user_pro(id int primary key auto_increment,name varchar(100),profession varchar(100));# 3. 开启游标open u_cursor;while true do # 这里埋了一个坑 条件处理程序解决# 4. 将游标中的数据插入到新表中fetch u_cursor into uname, upro;insert into tb_user_pro values (null, uname, upro);end while;# 5. 关闭游标close u_cursor;
endcall p12(40)
条件处理程序 条件处理程序可以用来定义在流程控制结构执行过程中遇到问题时相应的处理过程语法如下 declare handler_action hanler for condition_value...statement
handler:continue: # 继续执行当前程序exit: # 终止执行当前程序
condition_value:sqlstate sqlstate_value: # 状态码 如 02000sqlwarning: # 所有以01开头的sqlstate代码的简写not found: # 所有以02开头的sqlstate代码的简写sqlexception: # 所有没有被sqlwarning或not found捕获的sqlstate代码的简写案例 create procedure p12(in uage int)
begin# 1. 声明游标存储结果集 声明时要先声明变量再声明游标declare uname varchar(100);declare upro varchar(100);declare u_cursor cursor for select name, peofession from tb_user where age uage;# 声明条件处理程序declare exit handler for SQLSTATE 02000 close u_cursor;# 2. 准备创建表结构create table if not exists tb_user_pro(id int primary key auto_increment,name varchar(100),profession varchar(100));# 3. 开启游标open u_cursor;while true do# 4. 将游标中的数据插入到新表中fetch u_cursor into uname, upro;insert into tb_user_pro values (null, uname, upro);end while;# 5. 关闭游标close u_cursor;
endcall p12(40)
存储函数 存储函数是有返回值的存储过程存储函数的参数只能是in类型存储函数能实现的存储过程都能实现语法如下 create function 存储函数名称([参数列表])
returns type [characteristic...]
beginsql语句;return ...;
end;characteristic:deterministic # 相同的输入参数总是产生相同的结果no sql # 不包含sql语句reads sql data # 包含读取数据的语句但不包含写入数据的语句# 例 1-n的累加
create function fun1(n int)
returns int deterministic
begindeclare sum int default 0;while n0 doset sum : sum n;set n : n - 1;end while;return sum;
endselect fun1(100)
