-- 修改 sql 語句結(jié)束標(biāo)識(shí)符為 //
delimiter //

-- 創(chuàng)建存儲(chǔ)過程
create procedure 存儲(chǔ)過程名(參數(shù), 參數(shù)...)
begin
    select 列名, 列名... from exam;
end//

-- 修改 sql 語句結(jié)束標(biāo)識(shí)符為 ;
delimiter;

-- 查看 topoc01 中的所有存儲(chǔ)過程
select * from information_schema.routines where routine_schema = 數(shù)據(jù)庫名;

-- 查看存儲(chǔ)過程的定義
show create procedure 存儲(chǔ)過程名;

-- 調(diào)用存儲(chǔ)過程
call 存儲(chǔ)過程名(參數(shù), 參數(shù)...);

-- 刪除存儲(chǔ)過程
drop procedure if exists 存儲(chǔ)過程名;

-- 查看系統(tǒng)變量
show global variables;

-- 查看會(huì)話變量
show session variables;

-- 搭配模糊查詢 - 全局變量
show global variables like 'xxx%';

-- 搭配模糊查詢 - 會(huì)話變量
show session variables like '%xxx%';

-- 使用 select 進(jìn)行精確查詢 - 全局變量
select @@global.系統(tǒng)變量名;

-- 使用 select 進(jìn)行精確查詢 - 會(huì)話變量
select @@session.系統(tǒng)變量名;

-- 設(shè)置系統(tǒng)變量 - 全局變量
set global 系統(tǒng)變量名 = 值;

-- 設(shè)置系統(tǒng)變量 - 會(huì)話變量
set session 系統(tǒng)變量名 = 值;

-- 設(shè)置系統(tǒng)變量 - 全局變量
set @@global.全局變量名 = 值;

-- 設(shè)置系統(tǒng)變量 - 會(huì)話變量
set @@session.會(huì)話變量名 = 值;

-- 申明 + 賦值
set @var_name := 表達(dá)式;

select @var_name :=  表達(dá)式;

select 列名 into @var_name from 表名;

-- 使用自定義變量
select @var_name;

-- 局部變量申明的語法
declare 局部變量名 類型 default 默認(rèn)值;

-- 局部變量在存儲(chǔ)過程中的應(yīng)用

delimiter//

-- 定義存儲(chǔ)過程
create procedure p1()
begin 
    -- 定義局部變量
    declare stu_count int default 0;
    -- 給局部變量賦值
    select count(*) into stu_count from student;
    -- 使用局部變量
    select stu_count;
end//

delimiter ;

-- 條件判斷語句
if 條件1 then
    具體執(zhí)行邏輯;
elseif 條件2 then
    具體執(zhí)行邏輯;
...
else
    具體執(zhí)行邏輯;
end if;

-- 練習(xí)
-- 分?jǐn)?shù)>=90分等級(jí)為優(yōu)秀
-- 分?jǐn)?shù)>=80且分?jǐn)?shù)<90分等級(jí)為良好
-- 分?jǐn)?shù)>=60分且分?jǐn)?shù)<80分等級(jí)為及格
-- 分?jǐn)?shù)<60分等級(jí)為不及格

delimiter //

create procedure p1()
begin
    declare score int default 86;
    declare result varchar(20);

    if score >= 90 then
        set result := '優(yōu)秀';
    elseif score >=80 then
        set result := '良好';
    elseif score >= 60 then 
        set result := '及格';
    else
        set result := '不及格';
    end if;

    select result;
end//

delimiter ;

call p1();

-- 練習(xí)
-- 傳??個(gè)分?jǐn)?shù)的值，判定當(dāng)前分?jǐn)?shù)對(duì)應(yīng)的等級(jí) 
-- 分?jǐn)?shù)>=90分等級(jí)為優(yōu)秀
-- 分?jǐn)?shù)>=80且分?jǐn)?shù)<90分等級(jí)為良好
-- 分?jǐn)?shù)>=60分且分?jǐn)?shù)<80分等級(jí)為及格
-- 分?jǐn)?shù)<60分等級(jí)為不及格

delimiter //

create procedure p2(in score int, out result varchar(20))
begin
    if score >= 90 then
        set result := '優(yōu)秀';
    elseif score >=80 then
        set result := '良好';
    elseif score >= 60 then 
        set result := '及格';
    else
        set result := '不及格';
    end if;
end//

delimiter ;

-- 調(diào)用
call p2(86, @result);
select @result;

-- 傳??個(gè)分?jǐn)?shù)的值，在傳?分?jǐn)?shù)的基礎(chǔ)上加10分，然后返回
delimiter //

create procedure p3(inout score int)
begin
    set score = score + 10;
end//

delimiter ;

set @score := 50;
call p3(@score);
select @score;

-- 語法?
 
CASE case_value
 WHEN when_value THEN statement_list
 [WHEN when_value THEN statement_list] ...
 [ELSE statement_list]
END CASE

 
-- 語法?
 
statement_list 
(如果存在
CASE
 WHEN search_condition THEN statement_list
 [WHEN search_condition THEN statement_list] ...
 [ELSE statement_list]
END CASE

-- ?例?：傳??個(gè)狀態(tài)碼，輸出該狀態(tài)碼表?的含義
-- 0：成功
-- 10001：??名或密碼錯(cuò)誤
-- 10002：您沒有對(duì)應(yīng)的權(quán)限，請(qǐng)聯(lián)系管理員
-- 20001：你傳?的參數(shù)有誤
-- 20002：沒有找到相應(yīng)的結(jié)果

delimiter //

create procedure p4(in code int, out result varchar(20))
begin
    case code
        when 0 then 
            set result := '成功';
        when 10001 then 
            set result := '用戶名或密碼錯(cuò)誤';
        when 10002 then 
            set result := '您沒有對(duì)應(yīng)的權(quán)限，請(qǐng)聯(lián)系管理員';
        when 20001 then 
            set result := '你傳?的參數(shù)有誤';
        when 20002 then 
            set result := '沒有找到相應(yīng)的結(jié)果';
        else 
            set result = '服務(wù)器錯(cuò)誤，請(qǐng)聯(lián)系管理員';
    end case;
end//

delimiter ;

call p4(10001, @result);
select @result;


-- ?例?：根據(jù)傳?的?份，輸出該?份屬于哪個(gè)季度
-- 1~3?為第?季度
-- 4~6?為第二季度
-- 7~9?為第三季度
-- 1~12?為第四季度

delimiter //

create procedure p5(in month int, out result varchar(20))
begin 
    case
        when month >= 1 and month <= 3 then 
            set result := '第?季度';
        when month >= 4 and month <= 6 then
            set result := '第二季度';
        when month >= 7 and month <= 9 then
            set result := '第三季度';
        when month >= 10 and month <= 12 then 
            set result := '第四季度';
        else 
            set result := '非法參數(shù)';
    end case;
end//

delimiter ;

call p5(6, @result);
select @result;

WHILE search_condition Do
    statement_list
END WHILE;

-- 傳??個(gè)數(shù)n，計(jì)算從1累加到n的值
delimiter //

create procedure p6(in n int, out result int)
begin 
  declare num int default 1;
  set result := 0;
  while num <= n do 
   set result := result + num;
   set num := num + 1;
  end while;
end//

delimiter ;

call p6(3, @result);
select @result;

REPEAT
 statement_list
UNTIL search_condition END REPEAT;

delimiter //

create procedure p7(in n int, out sum int)
begin
  set sum := 0;
  repeat 
    set sum := sum + n;
    set n := n - 1;
  until n = 0 end repeat;
end//

delimiter ;

call p7(5, @sum);
select @sum;

[begin_label:] LOOP
    statement_list
END LOOP [end_label]

-- 傳??個(gè)數(shù)n，累加從1累加到n之間偶數(shù)的值
delimiter //

create procedure p8(in n int)
begin
  declare sum int default 0; 
  sum_label: LOOP
    IF n <= 0 THEN
      LEAVE sum_label; 
    END IF; 
    set sum := sum + n;
    set n := n - 1;
  END LOOP sum_label;
  
  select sum;

end//

delimiter ;

call p8(100);

-- 傳??個(gè)數(shù)n，累加從1累加到n之間偶數(shù)的值
delimiter //

create procedure p9(in n int)
BEGIN
  declare sum int default 0;
  sum_label: LOOP
    IF n <= 0 THEN
      LEAVE sum_label; 
    END IF; 
    IF n % 2 != 0 then 
      set n := n - 1;
      ITERATE sum_label;
    END IF;
    set sum := sum + n;
    set n := n - 1;
  END LOOP sum_label;
  
  select sum;

END//

delimiter ;

call p9(10);

-- 聲明游標(biāo)
declare 游標(biāo)名 cursor for 查詢語句;

-- 打開游標(biāo)
open 游標(biāo)名;

-- 獲取游標(biāo)記錄
fetch 游標(biāo)名 into 變量名, 變量名...;

-- 關(guān)閉游標(biāo)
close 游標(biāo)名;

-- 聲明條件處理程序
declare handler_action handler for condition_value, condition_value...
    statement;

handler_action:{
    continue, - 繼續(xù)執(zhí)行當(dāng)前程序
    exit - 退出當(dāng)前程序
}

condition_value:{
     mysql_error_code  -- MYSQL錯(cuò)誤碼
        
    | SQLSTATE [VALUE] sqlstate_value  -- 狀態(tài)碼
 
    | SQLWARNING  -- 所有以 01開頭的 SQLSTATE 代碼
 
    | NOT FOUND   -- 所有以 02開頭的 SQLSTATE 代碼
 
    | SQLEXCEPTION -- 所有沒有被 SQLWARNING 或 NOT FOUND 捕獲的 SQLSTATE 代碼
}

-- 示例：傳入班級(jí)編號(hào)，查詢學(xué)生表中屬于該班級(jí)的學(xué)生信息
-- 并將符合條件的學(xué)生信息寫入到?張新表中

-- 新表及字段 t_student_class(id,student_name,class_name)

-- 實(shí)現(xiàn)邏輯
-- 定義變量?于接收查詢結(jié)果集中每??中列的值
-- 聲明游標(biāo)，?于接收查詢結(jié)果集
-- 創(chuàng)建新表
-- 開啟游標(biāo)
-- 從游標(biāo)中獲取結(jié)果集中的記錄
-- 向新表中寫?數(shù)據(jù)
-- 關(guān)閉游標(biāo)

delimiter //

create procedure p10(in class_id int)
begin
    -- 定義變量?于接收查詢結(jié)果集中每??中列的值
    declare student_name varchar(20);
    declare class_name varchar(20);
    -- 聲明游標(biāo)，?于接收查詢結(jié)果集
    declare s_cursor cursor for 
        select student.name, class.name from student, class 
        where student.class_id = class.id and class.id = class_id;
    -- 創(chuàng)建新表
    create table if not exists t_student_class (
        id int primary key auto_increment,
        student_name varchar(20) not null, 
        class_name varchar(20) not null
    );
    -- 開啟游標(biāo)
    open s_cursor;
    -- 從游標(biāo)中獲取結(jié)果集中的記錄
    while true do
        fetch s_cursor into student_name, class_name;
         -- 向新表中寫?數(shù)據(jù)
        insert into t_student_class values (student_name, class_name);
    -- 關(guān)閉游標(biāo)
    close s_cursor;
end//

delimiter ;

delimiter //

create procedure p10(in class_id int)
begin
    -- 定義變量?于接收查詢結(jié)果集中每??中列的值
    declare student_name varchar(20);
    declare class_name varchar(20);
    -- 定義循環(huán)退出條件
    declare is_done bool default false;
    -- 聲明游標(biāo)，?于接收查詢結(jié)果集
    declare s_cursor cursor for 
        select student.name, class.name from student, class 
        where student.class_id = class.id and class.id = class_id;
    -- 聲明條件處理程序
    declare continue handler for not found
        set is_done := true;
    -- 創(chuàng)建新表
    create table if not exists t_student_class (
        id int primary key auto_increment,
        student_name varchar(20) not null, 
        class_name varchar(20) not null
    );
    -- 開啟游標(biāo)
    open s_cursor;
    -- 從游標(biāo)中獲取結(jié)果集中的記錄
    while not is_done do 
        fetch s_cursor into student_name, class_name;
         -- 向新表中寫?數(shù)據(jù)
        insert into t_student_class values (null, student_name, class_name);
    end while;
    -- 關(guān)閉游標(biāo)
    close s_cursor;
end//

delimiter ;

delimiter //

create procedure p10(in class_id int)
begin
    -- 定義變量?于接收查詢結(jié)果集中每??中列的值
    declare student_name varchar(20);
    declare class_name varchar(20);
    -- 定義循環(huán)退出條件
    declare is_done bool default false;
    -- 聲明游標(biāo)，?于接收查詢結(jié)果集
    declare s_cursor cursor for 
        select student.name, class.name from student, class 
        where student.class_id = class.id and class.id = class_id;
    -- 聲明條件處理程序
    declare continue handler for not found
        set is_done := true;
    -- 創(chuàng)建新表
    create table t_student_class(id,student_name,class_name);
    -- 開啟游標(biāo)
    open s_cursor;
    -- 從游標(biāo)中獲取結(jié)果集中的記錄
    read_loop: loop
        fetch s_cursor into student_name, class_name;
        if is_done then
            leave read_loop;
        end if;
        insert into t_student_class values (null, student_name, class_name);
    end loop read_loop;
    -- 關(guān)閉游標(biāo)
    close s_cursor;
end//

delimiter ;

-- 定義存儲(chǔ)函數(shù)
delimiter //

create function 存儲(chǔ)函數(shù)名(參數(shù)列表) returns 類型 characterristic
begin
    SQL 語句...
    return 返回值;
end//

delimiter ;

characteristic:
    deterministic | not deterministic | no sql | reads sql data | modifies sql data 

-- 使用存儲(chǔ)函數(shù)
select 存儲(chǔ)函數(shù)名(參數(shù)列表);

delimiter //

create function func1(n int) returns int deterministic
begin

    declare total int default 0;
    while n >= 1 do
        set total := total + n;
        set n := n - 1;
    end while;

    return total;

end//

delimiter ;


select func1(100);

-- 定義觸發(fā)器
delimiter //

create trigger if not exists 觸發(fā)器名 
    觸發(fā)時(shí)間 觸發(fā)類型 on 表名 for each row
begin
    觸發(fā)器邏輯;
end

delimiter ;


trigger time: before | after

trigger event: insert / update / delete

-- 查看觸發(fā)器
show triggers;

-- 刪除觸發(fā)器
drop trigger if exists 觸發(fā)器名;

-- 創(chuàng)建 student_log 表
create table student_log (
    id bigint primary key auto_increment,
    operation_type varchar(10) not null comment '操作類型：insert, update, delete',
    operation_time datetime not null comment '操作時(shí)間',
    operation_id bigint not null comment '操作記錄 id',
    operation_data varchar(500) not null comment '操作數(shù)據(jù)'
);

-- 創(chuàng)建 insert 觸發(fā)器
delimiter //

create trigger tgr_student_insert
    after insert on student for each row
begin
    insert into student_log (
        operation_type,
        operation_time,
        operation_id,
        operation_data
    ) values (
        'insert',
        now(),
        new.id,
        concat(new.id, ',' new.name, ',' new.sno, ',' new.age, ',' new.gender, ',' new.enroll_date, ',' new.class_id )
    );
end//

delimiter ;

-- 插入數(shù)據(jù)記錄
insert into student values (null, '曹操', '300001', 28, 1, '2024-9-1', 3);

-- 創(chuàng)建 update 觸發(fā)器
delimiter //

create trigger tgr_student_update
    after update on student for each row
begin
    insert into student_log (
        operation_type,
        operation_date,
        operation_id,
        operation_data
    ) values (
        'update',
        now(),
        new.id,
        concat(old.id, ', ', old.name, ', ', old.sno, ', ', old.age, ', ', old.gender, ', ', old.enroll_date, ', ', old.class_id, ' | ', 
               new.id, ', ', new.name, ', ', new.sno, ', ', new.age, ', ', new.gender, ', ', new.enroll_date, ', ', new.class_id)
    );
end//

delimiter ;

-- 更新學(xué)生表
update student set age = 20 where name = '曹操'; 
   
update student set class_id = 3 where id >= 7;

-- 創(chuàng)建 delete 觸發(fā)器
delimiter //

create trigger tgr_student_delete
    after delete on student for each row
begin
    insert into student_log (
        operation_type,
        operation_time,
        operation_id,
        operation_data
    ) values (
        'delete',
        now(),
        old.id,
        concat(old.id, ', ', old.name, ', ',old.sno, ', ',old.age, ', ',old.gender, ', ',old.enroll_date, ', ',old.class_id)
    );
end//

delimiter ;

-- 刪除數(shù)據(jù)記錄
delete from student where name = '曹操';

select * from information_schema.routines where routine_schema = '數(shù)據(jù)庫名';

show create procedure 存儲(chǔ)過程名;

show triggers; 

show create trigger 觸發(fā)器名;