对应课程地址
-- 存储过程-- 准备数据create database if not exists mydb7_procedure;use mydb7_procedure;create table dept(deptno int primary key,dname varchar(20),loc varchar(20));insert into dept values(10, '教研部','北京'),(20, '学工部','上海'),(30, '销售部','广州'),(40, '财务部','武汉');create table emp(empno int primary key,ename varchar(20),job varchar(20),mgr int,hiredate date,sal numeric(8,2),comm numeric(8, 2),deptno int,-- FOREIGN KEY (mgr) REFERENCES emp(empno),FOREIGN KEY (deptno) REFERENCES dept(deptno) ON DELETE SET NULL ON UPDATE CASCADE);insert into emp values(1001, '甘宁', '文员', 1013, '2000-12-17', 8000.00, null, 20),(1002, '黛绮丝', '销售员', 1006, '2001-02-20', 16000.00, 3000.00, 30),(1003, '殷天正', '销售员', 1006, '2001-02-22', 12500.00, 5000.00, 30),(1004, '刘备', '经理', 1009, '2001-4-02', 29750.00, null, 20),(1005, '谢逊', '销售员', 1006, '2001-9-28', 12500.00, 14000.00, 30),(1006, '关羽', '经理', 1009, '2001-05-01', 28500.00, null, 30),(1007, '张飞', '经理', 1009, '2001-09-01', 24500.00, null, 10),(1008, '诸葛亮', '分析师', 1004, '-04-19', 30000.00, null, 20),(1009, '曾阿牛', '董事长', null, '2001-11-17', 50000.00, null, 10),(1010, '韦一笑', '销售员', 1006, '2001-09-08', 15000.00, 0.00, 30),(1011, '周泰', '文员', 1008, '-05-23', 11000.00, null, 20),(1012, '程普', '文员', 1006, '2001-12-03', 9500.00, null, 30),(1013, '庞统', '分析师', 1004, '2001-12-03', 30000.00, null, 20),(1014, '黄盖', '文员', 1007, '2002-01-23', 13000.00, null, 10);create table salgrade(grade int primary key,losal int,hisal int);insert into salgrade values(1, 7000, 12000),(2, 1, 14000),(3, 14010, 20000),(4, 20010, 30000),(5, 30010, 99990);-- --------------------------------------------------------------------------------------------------- 简单入门-- 创建存储过程/*存储过程格式：delimiter 自定义结束符号create procedure 储存名([ in ,out ,inout ] 参数名 数据类形...)beginsql语句end 自定义的结束符合delimiter ;*/delimiter $$-- 指定存储过程结束符CREATE PROCEDURE proc01 () BEGINSELECTempno,ename FROMemp;END $$delimiter;-- 恢复结束符，防止对存储过程意外的代码产生影响-- 调用存储过程CALL proc01 ();-- --------------------------------------------------------------------------------------------------- 局部变量：只能在存储过程内部使用，外面查不到delimiter $$create procedure proc02() begindeclare var_name varchar(20) default 'aaa'; -- 声明/定义一个变量set var_name = '张三';select var_name;end $$delimiter ;CALL proc02 ();SELECT var_name; -- 存储过程外部查不到-- 为变量赋值delimiter $$CREATE PROCEDURE proc03 () BEGINDECLAREmy_ename VARCHAR ( 20 );SELECTename INTO my_ename FROMemp WHEREempno = '1001';SELECTmy_ename;END $$delimiter ;CALL proc03 ();-- 用户变量：可以在存储过程外面调用，数据库本次连接的会话中有效delimiter $$CREATE PROCEDURE proc04 () BEGINSET @var_name01 = '章北海';END $$delimiter ;call proc04();SELECT @var_name01;-- 存储过程外部也可以查到-- 系统变量分为全局变量和会话变量-- 查看所有全局变量SHOW GLOBAL VARIABLES;-- 查看某个全局变量SELECT @@GLOBAL.auto_increment_increment;-- 修改全局变量的值set global sort_buffer_size = 40000; -- 原值：262144select @@global.sort_buffer_size;-- 查看所有会话变量值SHOW SESSION VARIABLES;-- 查看某个会话变量SELECT @@SESSION.auto_increment_increment;-- 修改会话变量的值，只在当前的会话起作用SET SESSION sort_buffer_size = 50000;SELECT @@SESSION.sort_buffer_size;-- --------------------------------------------------------------------------------------------------- 传入参数：in-- 封装一个有参数的存储过程，传入员工编号，查找员工信息delimiter $$CREATE PROCEDURE proc05 ( IN empno INT )BEGINSELECT* FROMemp WHEREemp.empno = empno;END $$delimiter ;CALL proc05 ( 1002 );-- 封装一个有参数的存储过程，传入部门名和薪资，查询指定部门，并且薪资大于指定值的员工信息delimiter $$CREATE PROCEDURE proc06 (IN dname VARCHAR ( 20 ), IN sal DECIMAL ( 8, 2 )) BEGINSELECT* FROMdept d,emp e WHEREd.deptno = e.deptno AND d.dname = dname AND e.sal > sal;END $$delimiter ;call proc06('学工部', 20000);-- 传出参数：out-- 封装有参数的存储过程，传入员工编号，传出员工姓名和薪资delimiter $$CREATE PROCEDURE proc07 (IN empno INT, OUT ename VARCHAR ( 20 ), OUT sal DECIMAL ( 8, 2 )) BEGINSELECTemp.ename, emp.sal INTO ename, sal FROMemp WHEREemp.empno = empno;END $$delimiter ;CALL proc07 ( 1010, @ename, @sal );SELECT @ename, @sal;-- 传入一个数字，传出整个数字的十倍delimiter $$CREATE PROCEDURE proc08 ( INOUT num INT ) BEGINSET num = num * 10;END $$delimiter;SET @num = 2;CALL proc08 ( @num );SELECT @num;-- 传入员工名，拼接部门编号，传入薪资，求出年薪delimiter $$create procedure proc09(inout ename varchar(20), inout sal int)beginselect CONCAT_WS("_",deptno, ename), emp.sal * 12 into ename, sal from emp where emp.ename = ename;end $$delimiter ;set @ename = '关羽';call proc09(@ename, @sal);select @ename, @sal;-- --------------------------------------------------------------------------------------------------- 流程控制语句-if-- 例1：输入学生的成绩，来判断成绩的级别。/*score > 100 or score < 0， 成绩错误score < 60：不及格score >= 60 and score < 80，及格score >= 80 and score < 90，良好score >=90 and score <= 100，优秀*/delimiter $$CREATE PROCEDURE proc10 ( IN score INT )BEGINIF score > 100 OR score < 0 THEN SELECT '成绩错误';ELSEIF score < 60 THEN SELECT '不及格'; ELSEIF score >= 60 AND score < 80 THEN SELECT '及格'; ELSEIF score >= 80 AND score < 90 THEN SELECT '良好';ELSE SELECT '优秀';END IF;END $$delimiter ;set @score = 80;call proc10(@score);-- 例2：输入员工名字，判断工资的情况/*sal < 10000，真特么低sal >= 10000 and sal < 20000：差不多得了sal >= 20000：万恶的资本家*/delimiter $$CREATE PROCEDURE proc11 (IN ename VARCHAR ( 20 ))BEGINDECLARE empsal DECIMAL ( 8, 2 );SELECT sal INTO empsal FROM emp WHERE emp.ename = ename;IF empsal < 10000 THEN SELECT '真特么低';ELSEIF empsal >= 10000 AND empsal < 20000 THEN SELECT '差不多得了';ELSE SELECT '万恶的资本家';END IF;END $$delimiter ;SET @ename = '诸葛亮';CALL proc11 ( @ename );CALL proc11 ( '程普' );-- --------------------------------------------------------------------------------------------------- 流程控制语句-case/*支付方式：1：微信支付2：支付宝支付3：银行卡支付4：其他方式支付*/delimiter $$CREATE PROCEDURE proc12 ( IN pay_type INT ) BEGINCASE pay_type WHEN 1 THENSELECT'微信支付';WHEN 2 THEN SELECT '支付宝支付';WHEN 3 THEN SELECT '银行卡支付';ELSE SELECT '其他支付';END CASE;END $$delimiter ;CALL proc12 ( 3 );delimiter $$CREATE PROCEDURE proc13 ( IN pay_type INT ) BEGINCASE WHEN pay_type = 1 THEN SELECT '微信支付';WHEN pay_type = 2 THEN SELECT '支付宝支付';WHEN pay_type = 3 THEN SELECT '银行卡支付';ELSE SELECT '其他支付';END CASE;END $$delimiter ;CALL proc13 ( 3 ); -- --------------------------------------------------------------------------------------------------- 流程控制语句-while循环create table user(uid int primary key,username varchar(50),password varchar(50));/*while 循环条件 do循环体;end while;*/-- 例：向user表中插入指定条数的数据-- while循环delimiter $$create procedure proc14_while(in cnt int)begindeclare i int default 1;a:while i <= cnt doinsert into user values(i, concat('user-', i), '123456');set i = i + 1;end while a;end $$delimiter ;call proc14(10);-- while + leave：使用leave的话必须要给循环指定标记，这里标记是a-- leave：直接跳出本层while循环truncate table user; -- 清空数据，比delete from要快delimiter $$create procedure proc15_while_leave(in cnt int)begindeclare i int default 1;a:while 1=1 doinsert into `user` values(i, concat('user-', i), '123456');if i = cnt thenleave a;end if;set i = i + 1;end while a; select '循环结束';end $$delimiter ;call proc15_while_leave(10);-- while + iterate：跳过本次循环的剩余代码，直接进入下一次循环truncate table user;delimiter $$create procedure proc16_while_iterate(in cnt int)begindeclare i int default 0;a:while i < cnt doset i = i + 1;if i = 5 theniterate a;end if;insert into `user` values(i, concat('user-', i), '123456');end while a;end $$delimiter ;call proc16_while_iterate(10);-- --------------------------------------------------------------------------------------------------- 流程控制语句-repeat循环truncate table user;delimiter $$create procedure proc17_repeat(in cnt int)begindeclare i int default 0;repeatset i = i + 1;insert into `user` values(i, concat('user-', i), '123456');until i = cntend repeat;end $$delimiter ;call proc17_repeat(10);-- --------------------------------------------------------------------------------------------------- 流程控制语句-loop循环truncate table user;delimiter $$create procedure proc18_repeat(in cnt int)begindeclare i int default 0;a:loopset i = i + 1;if i > cnt then leave a;elseif i = 5 then iterate a;end if;insert into `user` values(i, concat('user-', i), '123456');end loop a;end $$delimiter ;call proc18_repeat(10);-- --------------------------------------------------------------------------------------------------- 游标：会产生异常，但是没有做处理-- 例1：输入一个部门名，查询该部门员工的编号、姓名、薪资，将查询结果集用游标遍历drop procedure if exists proc19_cursor;delimiter $$create procedure proc19_cursor(in dname varchar(20))begindeclare empno int;declare ename varchar(20);declare sal decimal(8, 2);-- 游标声明必须在其他变量之后declare cur cursor for select e.empno, e.ename, e.sal from emp e, dept d where e.deptno = d.deptno and d.dname = dname; -- 声明游标open cur;-- 打开游标a:loopfetch cur into empno, ename, sal; -- 通过游标获取值select empno, ename, sal;end loop a;close cur; -- 关闭游标end $$delimiter ;call proc19_cursor('教研部');-- --------------------------------------------------------------------------------------------------- 异常处理-- 游标 + 句柄/*DECLARE handler_action HANDLERFOR condition_value [, condition_value] ...statementhandler_action: {CONTINUE| EXIT| UNDO}condition_value: {mysql_error_code| condition_name| SQLWARNING| NOT FOUND特别注意：在语法中，变量声明、游标声明、handler声明是必须按照先后顺序书写的，否则创建存储过程出错。*/-- 游标：对异常游标遍历到结尾的时候的异常进行处理-- 例1：输入一个部门名，查询该部门员工的编号、姓名、薪资，将查询结果集用游标遍历drop procedure if exists proc20_cursor_handler;delimiter $$create procedure proc20_cursor_handler(in dname varchar(20))begindeclare empno int;declare ename varchar(20);declare sal decimal(8, 2);-- 定义标记值declare flag int default 1;-- 游标声明必须在其他变量之后declare cur cursor for select e.empno, e.ename, e.sal from emp e, dept d where e.deptno = d.deptno and d.dname = dname; -- 声明游标-- 定义句柄：异常的处理方式/*1：异常处理完之后程序该怎么执行continue：继续执行剩余代码exit：直接终止程序undo：不支持2：触发条件条件码：1329 - No data - zero rows fetched, selected, or processed条件名：SQLWARNINGNOT FOUNDSQLEXCEPTION3：异常触发之后执行的代码设置flag的值*/declare continue handler for 1329 set flag = 0;open cur;-- 打开游标a:loopfetch cur into empno, ename, sal; -- 通过游标获取值if flag = 1 thenselect empno, ename, sal;else leave a;end if;end loop a;close cur; -- 关闭游标end $$delimiter ;call proc20_cursor_handler('教研部');-- --------------------------------------------------------------------------------------------------- 练习/*创建下个月的每天对应的表user__02_01、user__02_02、...需求描述：我们需要用某个表记录很多数据，比如记录某某用户的搜索、购买行为(注意，此处是假设用数据库保存)，当每天记录较多时，如果把所有数据都记录到一张表中太庞大，需要分表，我们的要求是，每天一张表，存当天的统计数据，就要求提前生产这些表——每月月底创建下一个月每天的表！*/-- 思路：循环构建表名 user__11_01 到 user__11_30；并执行create语句。-- 预备知识：-- 查询当前时间日期SELECT now();-- 获取下个月的日期SELECT date_add(now(), INTERVAL 1 MONTH);-- 获取下个月的年份SELECT YEAR(date_add(now(), INTERVAL 1 MONTH));-- 获取下个月的月份SELECT MONTH (date_add( now(), INTERVAL 1 MONTH));-- 获取下个月的最后一天SELECT DAYOFMONTH(LAST_DAY(date_add( now(), INTERVAL 1 MONTH)));drop database if exists mydb_proc_demo;create database mydb_proc_demo;use mydb_proc_demo;drop procedure if exists proc21_auto_create_tables_next_month;delimiter $$create procedure proc21_auto_create_tables_next_month()begindeclare next_year int; -- 下一个月的年declare next_month int; -- 下一个月declare last_day int; -- 下一个月的最后一天declare next_date DATE; -- 下个月的日期declare next_month_str char(2); -- 月份小于10在前面填充一位0declare next_day_str char(2); -- 天数小于10在前面填充一位0declare table_name varchar(20);declare i int default 1;SET next_date = DATE_ADD(NOW(),INTERVAL 1 MONTH);set next_year = year(next_date);set next_month = month(next_date);set last_day = DAYOFMONTH(LAST_DAY(next_date));if next_month < 10 then set next_month_str = concat('0', next_month);end if;-- select next_year, next_month, next_day;-- 循环拼接表名，并且建表while i <= last_day doif i < 10 then set next_day_str = concat('0', i);else set next_day_str = concat('', i);end if;set table_name = concat_ws('_', next_year, next_month_str, next_day_str);-- select table_name;-- 拼接建表sql语句set @create_table_sql = concat('create table user_', table_name, '(`uid` int primary key, `username` varchar(20), `password` varchar(20))');-- FROM后面不能使用局部变量prepare create_table_stmt from @create_table_sql;-- 预编译sqlexecute create_table_stmt;-- 执行sqldeallocate prepare create_table_stmt;set i = i + 1;end while;end $$delimiter ;call proc21_auto_create_tables_next_month();
