存储过程
一、存储过程介绍
1、什么是存储过程?
存储过程是实现某个特定功能的sql语句的集合,编译后的存储过程会保存在数据库中,通过存储过程的名称反复的调用执行。
2、存储过程的优点?
(1)存储过程创建后,就可以反复的调用和使用,不需要重新写复杂的语句
(2)创建,修改存储过程不会对数据有任何的影响
(3)存储过程可以通过输入参数返回输出值
(4)通过存储过程中加入控制语句,可以加强sql语句的功能性和灵活性
(5)对于单个l语句增删改查,可以直接封装一个集合中,存储过程一旦创建就可以直接调用,且可以重复调用
(6)单个sql语句每一次执行都需要对数据进行编译,而存储过程被创建只需要编译一次,后续即可调用
(7)创建的存储过程,可以重复进行调用,可以减少数据库开发人员的工作量
(8)防止sql 注入
(9)造数据(重点)
3、Mysql 5.0版本后支持存储过程,存储过程=sql语句集合+控制语句
===================================================================
格式:
delimiter // #分隔符 符号 //
create PROCEDURE 存储名( 参数名:in out inout)
BEGIN 开始
sql语句 执行语句
END 结束
// 分隔符号
call 存储名(参数) 调用一个存储过程
案例:
delimiter //
create PROCEDURE hz001()
BEGIN
SELECT * from emp ;
select * from dept ;
END
//
call hz001()
三、存储过程的基本语句:
(1) 删除存储过程
格式:drop procedure 存储名称
案例:drop procedure hz001
(2)查看单个存储过程的详情
格式:show create procedure 存储过程;
案例:show create procedure hz001;
(3)查看所有已经建好的存储过程详情
格式:show procedure status
(4)查询指定数据库里创建的存储过程
格式:show procedure status where db="数据库名"
案例:show procedure status where db="hz017"
四、创建存储
1、创建无参数存储过程
格式:
delimiter //
create PROCEDURE 存储名1() #无参数,是括号内无参数
BEGIN #开始
sql语句 #执行语句
END #结束
// 结束符号
call 存储名() 调用存储名称
案例:
delimiter //
create PROCEDURE hz1() #无参数,是括号内无参数
BEGIN
SELECT * from emp where dept2=101 ;
END
//
call hz1()
2、带有in参数的存储
in表示输入数据
格式:
delimiter //
create PROCEDURE 存储名(in x int ) #in参数,是括号
BEGIN
sql语句
END
//
call 存储过程(入参)
案例:
delimiter //
create PROCEDURE hz(in x int ) #无参数,是括号内无参数
BEGIN
SELECT * from emp where dept2=x ;
END
//
call hz(105)
3、带out 的参数(输出参数)
格式:
delimiter //
create PROCEDURE 存储名(out x int ) #in参数,是括号
BEGIN
sql语句
END
//
call 存储过程(入参)
案例:
delimiter //
create PROCEDURE hz5(out y int ) #无参数,是括号内无参数
BEGIN
SELECT age into y from emp where dept2=105;
END
//
call hz5(@y)
select @y
带有 in 和 out 的存储过程
案例:
delimiter //
create PROCEDURE hz6(in x int ,out y int ) #无参数,是括号内无参数
BEGIN
SELECT age into y from emp where dept2=x;
END
//
call hz6(109,@y)
select @y
===========================================================================
inout参数 代表着输入和输出
案例1:
delimiter //
create PROCEDURE hz8(inout m int ) #无参数,是括号内无参数
BEGIN
set m=m+1;
END
//
set @m=2
call hz8(@m)
select @m
案例2:
delimiter //
create PROCEDURE hz9(inout x int ) #无参数,是括号内无参数
BEGIN
SELECT age into x from emp where dept2=x;
END
//
set @x=109
call hz8(@x)
select @x
备注:
CHARACTER set UTF8 解决?号
===========================================
用户变量:
定义语法L
set @ 变量名
赋值的方法:
(1)方式一
set @ 变量名:=值
或
set @变量名=值;
select @变量名:=值;
(2)方式二
通过查询结果为变量赋值:
select 字段 into 变量名 from 表名 where 条件
(3)declare 声明变量
例如:declare i int default 0 ;
============================================
循环语句:
三种:
(1)while ......do ......end where
while 循环语句:
格式:while 条件 do
sql语句
end while
(2) loop ......end loop
(3) repeat.......until ,,,,,end repeat
============================================
造数据场景:
1、通过存储过程在一个指定的空表中插入10条数据?
案例:
drop table ff ;
CREATE table ff(id int (10),age int(10));
select * from ff ;
delimiter //
drop procedure if exists hz10; #增强健壮性, 判断是否存在指定存储过程,如果存在就删除;
create procedure hz10()
BEGIN
DECLARE i int DEFAULT 0 ;
while (i<10) DO
INSERT into ff(id )VALUES (i);
set i=i+1;
end WHILE;
select * from ff ;
END
//
call hz10()
场景2:插入指定数据的行数
delimiter //
DROP table if EXISTS ff ;
drop procedure if exists hz10; #增强健壮性, 判断是否存在指定存储过程,如果存在就删除;
CREATE table ff(id int (10),age int(10));
create procedure hz10( in x int )
BEGIN
DECLARE i int DEFAULT 0 ;
while (i<x) DO
INSERT into ff(id )VALUES (i);
set i=i+1;
end WHILE;
select * from ff ;
END
//
call hz10(5)
场景三: 指定插入的数据,并将已有的数据统计,在已有的数据基础上在添加
delimiter //
DROP table if EXISTS ff ;
drop procedure if exists hz10; #增强健壮性, 判断是否存在指定存储过程,如果存在就删除;
CREATE table ff(id int (10),age int(10));
create procedure hz10( in x int )
BEGIN
DECLARE i int DEFAULT (select Count(*) from ff ) ;
while (i<x) DO
INSERT into ff(id )VALUES (i+1);
set i=i+1;
end WHILE;
select * from ff ;
END
//
call hz10(100)
==============================================
面试题:
1、你会存储码?
2、存储的结构?
3、在工作中你们用存储做什么? 造数据
4、你会写存储吗?
============================================
if 语句
if 的单分支
if 条件 then
执行sql1
else
执行sql2
end if ;
案例:
delimiter //
create procedure hz11( in a int )
BEGIN
if a>10 THEN
SELECT * from emp ;
ELSE
SELECT * from dept;
end if;
END
//
call hz11(9)
一个if 对应一个end if
if 多分支
在if 判断语句汇总,有几个判断分支if,就有几个结束语 end if ;
if 条件 1 then
执行sql1
else if 条件2 then
执行sql2
else if 条件3 then
执行sql3
end if ;
end if ;
end if;
end
案例:
create procedure hz12( in a int )
BEGIN
if a>100 THEN
SELECT * from emp ;
ELSE if a<10 then
SELECT * from dept;
else if a=50 then
select * from student2 ;
ELSE
SELECT * from ff ;
end IF;
end IF;
end if;
END
//
call hz12(9)
=========================================
面试题:根据student学生表去写
1.当传入的参数(大于0)小于等于表里面数据的条数时,则根据分组显示班级的总成绩
2.当传入的参数大于表里面数据的条数时,则统计表里面的数据有多少条
3.当传入其他,则查询表里面的所有数据
方法1:
delimiter //
create procedure hz09( in x int )
BEGIN
DECLARE i int DEFAULT (select Count() from student2 );
if x<=i and x>0 THEN
SELECT sum(chinese+math+english) from student2 group by class;
ELSE if x>i then
SELECT count() from student2;
else
select * from student2;
end if;
end if;
END
//
call hz09(6)
方法2:
select * from xsb;
delimiter //
create procedure student1( in a int )
BEGIN
if a>0 and a<=(select count(id) from xsb) THEN
SELECT sum(english+chinese+math) from xsb GROUP BY class ;
ELSE if a>(select count(id) from xsb) then
SELECT count(id) from xsb;
ELSE
SELECT * from xsb ;
end IF;
end IF;
END
//