存储过程一、存储过程介绍1、定义存储过程是实现某个特点功能的sql语句的集合编译后的存储过程会保存在数据中通过存储过程的名称反复的调用执行2、存储过程的优点1存储创建以后就可以反复的调用不需要写复杂的语句2存储过程可以添加输入和输出参数实现变量 例如in out inot3存储过程可以加入控制语句增加sql语句的功能性和灵活 if4创建存储过程可以减少数据开发人员的工作量5防止sql注入6造数据重点二、存储过程的格式1创建存储过程格式#分隔符delimiter//drop PROCEDURE if exists 存储名称; #判断是否存在存储存在删除create PROCEDURE 存储名称()BEGINsql语句END//call 存储名称案例#分隔符delimiter //drop PROCEDURE if exists cc1;CREATE PROCEDURE cc1()BEGINselect * from emp ;select * from dept;select * from student;END//call cc1()2查看已经建好存储过程格式show create procedure 存储名称 ;案例show create procedure cc1 ;3查看所有的存储过程格式show procedure status ;案例show procedure status ;4删除存储过程格式drop procedure 存储名案例drop procedure c15判断是否存在新建名的存储存在则删除这条语句增强健壮性drop PROCEDURE if EXISTS 存储名;案例drop PROCEDURE if EXISTS cc1;三、存储过程的运用1创建无参数存储过程案例delimiter//drop PROCEDURE if EXISTS cum;create PROCEDURE cum()BEGINselect * from emp where dept2101;END//call cum()2创建带有in的存储过程案例2delimiter//drop PROCEDURE if EXISTS cum;create PROCEDURE cum(in x int) #in是传入参数BEGINselect * from emp where dept2x;END//call cum(102)3创建带有out 的存储过程delimiter//drop PROCEDURE if EXISTS cum;create PROCEDURE cum(out y int)BEGINselect age into y from emp where sid1565;END//call cum(y)select y 显示变量值注意点用户变量方式1set 变量名值或者set 变量名值select 变量名值方法2通过查询结果为变量值select 字段名 into 变量名 form 表名 where 条件方法3declare 声明 变量例如declare i int default 0 4创建带有in out 的存储过程案例delimiter//drop PROCEDURE if EXISTS cum;create PROCEDURE cum(in x int,out y int)BEGINselect age into y from emp where sidx;END//call cum(1776,y)select y5创建带有inout 的存储过程案例delimiter//drop PROCEDURE if EXISTS cum;create PROCEDURE cum(inout m int)BEGINset mm1;END//set m2call cum(m)select m四、存储造数据1、循环语句while语句(重点讲)格式while 条件 dosql语句end while2loop ....end loop3repeat until ....end repeat2、造数据1新建一个表例如create table s9(id int(10) PRIMARY key ,name VARCHAR(20)) ;2插入固定的数据0-9select * from s9 ;create table s10(id int(10) ,name VARCHAR(20)) ;delimiter//drop PROCEDURE if EXISTS zs;create PROCEDURE zs()BEGINDECLARE i int DEFAULT 0;while (i10) DOINSERT into s10(id) VALUES(i);set ii1;END WHILE;select *from s9;ENDcall zs()2插入指定的数量的数据100或1000create table s10(id int(10) ,name VARCHAR(20)) ;delimiter//drop PROCEDURE if EXISTS zs;create PROCEDURE zs(in x int)BEGINDECLARE i int DEFAULT 0;while (ix) DOINSERT into s10(id) VALUES(i);set ii1;END WHILE;select *from s10;ENDcall zs(1000)(3)插入数据将表中已经存在的数据进行统计在已有的基础上添加数据delimiter//drop PROCEDURE if EXISTS zs;create PROCEDURE zs(in x int)BEGINDECLARE i int DEFAULT (select count(*) from s10);while (ix) DOINSERT into s10(id) VALUES(i);set ii1;END WHILE;select *from s10;ENDcall zs(15)4插入数据每次会新建表重新插入数据delimiter//drop PROCEDURE if EXISTS zs;create PROCEDURE zs(in x int)BEGINDECLARE i int DEFAULT 0;drop table if EXISTS s10 ;create table s10(id int(10),name VARCHAR(20)) ;while (ix) DOINSERT into s10(id) VALUES(i);set ii1;END WHILE;select *from s10;ENDcall zs(3)五、 if语句1if单分格式if 条件 THENsql语句1ELSEsql语句2end IF;注意点一个if对应一个end if案例delimiter//drop PROCEDURE if EXISTS zs;create PROCEDURE zs(in x int)BEGINif x10 THENselect * from emp;ELSEselect * from dept;end IF;end//call zs(1)2if多分支if 条件1 thensql1else if 条件2 thensql2else if 条件3 thensql3else if 条件4 thensql4elsesql5end if;end if;end if;end if;案例delimiter//drop PROCEDURE if EXISTS zs;create PROCEDURE zs(in x int)BEGINif x10 THENselect * from emp;else if x1 THENselect * from mm;else if x0 THENselect * from nn;else if x100 THENselect * from xx;ELSEselect * from dept;end IF;end if;end if;end if;end//call zs(99)作业面试题根据student学生表去写1.当传入的参数大于0小于等于表里面数据的条数时则根据分组显示班级的总成绩2.当传入的参数大于表里面数据的条数时则统计表里面的数据有多少条3.当传入其他则查询表里面的所有数据delimiter//drop PROCEDURE if EXISTS pd;create PROCEDURE pd(in x int)BEGINDECLARE i int DEFAULT(select count(*) from student2);if x0 and xi THENselect sum(englishchinesemath) from student2 group by class ;ELSE if xi THENselect count(*) from student2;ELSEselect * from student2 ;end if;end IF;END//call pd(-1)
