mysql学习笔记07

最新推荐文章于 2024-06-21 21:22:44 发布

Charlotte384

最新推荐文章于 2024-06-21 21:22:44 发布

阅读量430

点赞数 11

文章标签： mysql 学习笔记

本文链接：https://blog.csdn.net/qq_68289621/article/details/136158640

版权

数据准备

CREATE TABLE dept  (
  deptno int NOT NULL,
  dname varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  loc varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`deptno`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

INSERT INTO dept VALUES (10, '教研部', '北京');
INSERT INTO dept VALUES (20, '学工部', '上海');
INSERT INTO dept VALUES (30, '销售部', '广州');
INSERT INTO dept VALUES (40, '财务部', '武汉');

CREATE TABLE emp  (
  empno int NOT NULL,
  ename varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  job varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  mgr int NULL DEFAULT NULL,
  hiredate date NULL DEFAULT NULL,
  sal decimal(7, 2) NULL DEFAULT NULL,
  COMM decimal(7, 2) NULL DEFAULT NULL,
  deptno int NULL DEFAULT NULL
) ;
drop table emp;
INSERT INTO emp VALUES (1001, '甘宁', '文员', 1013, '2000-12-17', 8000.00, NULL, 20);
INSERT INTO emp VALUES (1002, '黛绮丝', '销售员', 1006, '2001-02-20', 16000.00, 3000.00, 30);
INSERT INTO emp VALUES (1003, '殷天正', '销售员', 1006, '2001-02-22', 12500.00, 5000.00, 30);
INSERT INTO emp VALUES (1004, '刘备', '经理', 1009, '2001-04-02', 29750.00, NULL, 20);
INSERT INTO emp VALUES (1005, '谢逊', '销售员', 1006, '2001-09-28', 12500.00, 14000.00, 30);
INSERT INTO emp VALUES (1006, '关羽', '经理', 1009, '2001-05-01', 28500.00, NULL, 30);
INSERT INTO emp VALUES (1007, '张飞', '经理', 1009, '2001-09-01', 24500.00, NULL, 10);
INSERT INTO emp VALUES (1008, '诸葛亮', '分析师', 1004, '2007-04-19', 30000.00, NULL, 20);
INSERT INTO emp VALUES (1009, '曾阿牛', '董事长', NULL, '2001-11-17', 50000.00, NULL, 10);
INSERT INTO emp VALUES (1010, '韦一笑', '销售员', 1006, '2001-09-08', 15000.00, 0.00, 30);
INSERT INTO emp VALUES (1011, '周泰', '文员', 1008, '2007-05-23', 11000.00, NULL, 20);
INSERT INTO emp VALUES (1012, '程普', '文员', 1006, '2001-12-03', 9500.00, NULL, 30);
INSERT INTO emp VALUES (1013, '庞统', '分析师', 1004, '2001-12-03', 30000.00, NULL, 20);
INSERT INTO emp VALUES (1014, '黄盖', '文员', 1007, '2002-01-23', 13000.00, NULL, 10);

CREATE TABLE salgrade  (
  grade int NOT NULL,
  losal int NULL DEFAULT NULL,
  hisal int NULL DEFAULT NULL
) ;

INSERT INTO salgrade VALUES (1, 7000, 12000);
INSERT INTO salgrade VALUES (2, 12010, 14000);
INSERT INTO salgrade VALUES (3, 14010, 20000);
INSERT INTO salgrade VALUES (4, 20010, 30000);
INSERT INTO salgrade VALUES (5, 30010, 99990);

游标

是用来存储查询结果集的数据类型，在存储过程中可以使用光标对结果集进行循环处理。

-- 操作游标（cursor）

-- 1.声明游标
-- 2.打开游标
-- 3.通过游标获取值
-- 4.关闭游标
use test04_procedure;

-- 输入一个部门名字，查询该部门员工地编号，工资，和名字，将查询结果集添加到游标
delimiter $$
create procedure pro15_cursor(in n varchar(20) )
begin
    declare id int ;
    declare salary DECIMAL;
    declare name varchar(50);
    -- 声明游标
    declare my_cursor cursor for 
                                                        select empno,sal,ename 
                                                        from emp a,dept b 
                                                        where a.deptno = b.deptno and b.dname = n;
    -- 打开游标
    open my_cursor;
    -- 通过游标获取值
    fetch my_cursor into id,salary,name;
    select id,salary,name;
    -- 关闭游标
    close my_cursoe
end $$
delimiter;


call pro15_cursor('销售部');

存储过程-异常处理

use test04_procedure;

-- 输入一个部门名字，查询该部门员工地编号，工资，和名字，将查询结果集添加到游标
delimiter $$
create procedure pro16_cursor(in n varchar(20) )
begin
    declare id int ;
    declare salary DECIMAL;
    declare name varchar(50);
    -- 声明游标
    declare my_cursor cursor for 
                                                        select empno,sal,ename 
                                                        from emp a,dept b 
                                                        where a.deptno = b.deptno and b.dname = n;
    -- 打开游标
    open my_cursor;
    -- 通过游标获取值
    lo:loop
    fetch my_cursor into id,salary,name;
    select id,salary,name;
    end loop lo;
    -- 会异常，报错> 1329 - No data - zero rows fetched, selected, or processed,因为死循环但是查询地表数据有限报错,但是调用可以显示表中所以数据
    -- 关闭游标
    close my_cursor;
end $$
delimiter;

drop procedure pro16_cursor;
call pro16_cursor('销售部');

此时就想要handler异常处理

-- 定义顺序：变量，游标，句柄
-- declare 行为（continue,exit,undo） handler  
-- for 报错码|报错类型（SQLWARNING,NOT FOUND,SQLEXECEPTION)
-- 操作
declare continue handler fro 1932 set x= 0;

对上进行修改，加入异常处理的使用

-- 使用异常处理改造上面代码
-- 输入一个部门名字，查询该部门员工地编号，工资，和名字，将查询结果集添加到游标
delimiter $$
create procedure pro17_cursor(in n varchar(20) )
begin
    declare id int ;
    declare salary DECIMAL;
    declare name varchar(50);
    declare flag int DEFAULT 1;
    -- set flag = 1;
    -- 声明游标
    declare my_cursor cursor for 
                                                        select empno,sal,ename 
                                                        from emp a,dept b 
                                                        where a.deptno = b.deptno and b.dname = n;
    -- 声明句柄，异常处理
    declare continue handler for 1329 set flag=0;
    

    -- 打开游标
    open my_cursor;
    -- 通过游标获取值
    lo:loop
    fetch my_cursor into id,salary,name;
    
    
    if flag=0 then leave lo;
    end if;
    select id,salary,name;
    end loop lo;
    -- 会异常，报错> 1329 - No data - zero rows fetched, selected, or processed,因为死循环但是查询地表数据有限报错,但是调用可以显示表中所以数据
    -- 关闭游标
    close my_cursor;
end $$
delimiter;

call pro17_cursor('销售部');
drop procedure pro17_cursor;

存储过程-练习

题目要求：我们想要某个表记录很多数据，每天一张表，保存当天的数据，要求提前生产这些表---每月月底创建下一个月每天的表

思路：循环创建表名，user_2021_11_01到user_2020_11_30;并执行create语句

-- prepare stmt_name from prepare_stmt 想要做预处理
-- execute stmt_name [using @var_name,……]
-- date_add(date,interval  expr unit)
-- last_day(date)
-- year(date)
-- month(date)
-- dayofmonth(date) 返回日

/*
题目要求：我们想要某个表记录很多数据，每天一张表，保存当天的数据，要求提前生产这些表---每月月底创建下一个月每天的表

思路：循环创建表名，user_2021_11_01到user_2020_11_30;并执行create语句
            
*/
create database test05_procedure_demo;
use test05_procedure_demo;
delimiter $$
create procedure demo()
begin 
        declare next_year int;  
        declare next_month int;
        -- 声明下一个月有多少天
        declare next_month_num  int;
        
        declare index_day int DEFAULT 1;
        
        declare next_month_str  varchar(20);
        declare next_day_str varchar(20);
        declare table_name  varchar(60);
        
        
        
        set  next_year = year(date_add(now(),interval 1 month));
        set next_month = month(date_add(now(),interval  1 month));
        set next_month_num = dayofmonth(LAST_DAY(date_add(now(),interval 1 month)));
        
        
        if next_month < 10 then
            set next_month_str = concat('0',next_month);
        else
            set next_month_str = concat('',next_month);
        end if; 
            

        while index_day <= next_month_num do    
            if index_day < 10 then
                set next_day_str = concat('0',index_day);
            else
                set next_day_str = concat('',index_day);
            end if;
            
            
            set  table_name = concat(next_year,'_',next_month_str,'_',next_day_str);
            
            -- 拼接create_table_sql 语句
            set @create_table_sql = concat('create table logs_',table_name,'(uid  int , ename varchar(10),log  varchar(50)) ');
            PREPARE create_table  from @create_table_sql;
            EXECUTE create_table;
            deallocate PREPARE create_table;
            set index_day = index_day + 1;
        end while;
end $$
delimiter;
call demo();