-- 修改SQL語句結(jié)束符為//
delimiter //
-- 創(chuàng)建存儲(chǔ)過程
create procedure if not exists 存儲(chǔ)過程名(參數(shù)列表)
  begin
    -- SQL語句
  end//
-- 還原SQL語句結(jié)束符為;
delimiter ;
-- 調(diào)用存儲(chǔ)過程
call 存儲(chǔ)過程名(參數(shù)列表);
-- 刪除存儲(chǔ)過程
drop procedure if exists 存儲(chǔ)過程名;
-- 查看當(dāng)前數(shù)據(jù)庫已創(chuàng)建的存儲(chǔ)過程
select * from information_schema.routines where routine_schema = 數(shù)據(jù)庫名;

-- 查詢?nèi)?會(huì)話變量
show [global|session] variables [like...];
select @@[global|session].系統(tǒng)變量名;
-- 修改全局/會(huì)話變量
set [global|session] 系統(tǒng)變量名 = 值;
set @@[global|session].系統(tǒng)變量名 = 值;

-- 設(shè)置用戶變量
set @user_demo = 值;
-- 查詢用戶變量
select @user_demo;
-- 設(shè)置并查詢用戶變量
select @user_demo ：= 值;

delimiter //
create procedure if not exists query()
begin
  declare num int default 0;
  set num := 100;
  select num;
end//
delimiter ;
call query();

if 條件1 then
-- sql語句1
elseif then
--sql語句2
else
-- sql語句3
end if;

-- 示例1
delimiter //
create procedure demo_if(in score int,out result varchar(20))
begin
  if score >= 90 then
    set result := '優(yōu)秀';
  elseif score >= 80 and score < 90 then
    set result = '良好';
  elseif score >= 70 and score < 80 then
    set result = '及格';
  else
    set result = '不及格';
  end if;
end//
delimiter ;
call demo_if(60,@result);
select @result;
-- 示例2
set @score := 70;
delimiter //
create procedure demo2_if(inout score int)
begin
  set score := score + 10;
end//
delimiter ;
call demo_if_2(@score);
select @score;

-- 簡單case表達(dá)式：將一個(gè)表達(dá)式與一組簡單值進(jìn)行比較
case case_value
    when when_value1 then result1
    when when_value2 then result2
    ...
    else else_result
end
-- 搜索case表達(dá)式：將一個(gè)表達(dá)式與一組獨(dú)立的布爾表達(dá)式進(jìn)行比較
case
    when condition1 then result1
    when condition2 then result2
    ...
    else else_result
end

-- 示例1
delimiter //
create procedure demo_case(in code int,out result varchar(50))
begin
  case code
    when 0 then
      set result := '成功';
    when 10001 then
      set result := '用戶名或密碼錯(cuò)誤';
    when 10002 then
      set result := '沒有權(quán)限';
    when 20001 then
      set result := '傳入?yún)?shù)有誤';
    when 20002 then
      set result := '無結(jié)果';
    else 
      set result := '內(nèi)部錯(cuò)誤';
  end case;
end//
delimiter ;
call demo_case(10002,@result);
select @result;
-- 示例2
delimiter //
create procedure demo2_case(in month int,out result varchar(50))
begin
  case
    when month >=1 and month <= 3 then
      set result = '第一節(jié)度';
    when month >=4 and month <= 6 then
      set result = '第二節(jié)度';
    when month >=7 and month <= 9 then
      set result = '第三節(jié)度';
    when month >=10 and month <= 12 then
      set result = '第四節(jié)度';
    else
      set result = '非法輸入';
  end case;
end//
delimiter ;
call demo2_case(7,@result);
select @result;

-- search_condition：循環(huán)條件表達(dá)式
-- statement_list：循環(huán)體內(nèi)要執(zhí)行的SQL語句
while search_condition do
	statement_list
end while;

delimiter //
create procedure demo_while(in n int)
begin
  declare sum int default 0;
  while n > 0 do
    set sum := sum + n;
    set n := n - 1;
  end while;
  select sum;
end//
delimiter ;
call demo_while(100);

repeat
	statement_list
until search_condition end repeat;

delimiter //
create procedure demo_repeat(in n int)
begin
  declare sum int default 0;
  repeat 
    set sum := sum + n;
    set n := n - 1;
  until n <= 0 end repeat;
  select sum;
end//
delimiter ;
call demo_repeat(100);

-- loop_label：標(biāo)記loop的標(biāo)簽
loop_label:loop
	statement_list
end loop loop_label;

-- 求1-100所有偶數(shù)的和
delimiter //
create procedure demo_loop(in n int)
begin
  declare total int default 0;
  sum: loop
    if n <= 0 then
      leave sum; 
    end if;
    if n % 2 = 1 then
      set n := n - 1;
      iterate sum;
    end if;
    set total := total + n;
    set n := n - 1;
  end loop sum;
  select total;
end//
delimiter ;
call demo_loop(100);

-- 聲明游標(biāo)
declare cursor_name cursor for 查詢語句
-- 打開游標(biāo)
open cursor_name;
-- 獲取游標(biāo)記錄
fetch cursor_name into 

declare handler_type handler for condition statement;

-- 1.handler_type：條件處理程序類型
(1) continue -- 繼續(xù)執(zhí)行后續(xù)語句
(2) exit -- 終止當(dāng)前begin...end塊 
-- 2.condition：指定了處理程序會(huì)響應(yīng)的條件類型
(1) mysql_error_code -- MySQL錯(cuò)誤碼
(2) sqlstate sqlstate_value -- 表示特定的SQL狀態(tài)碼
(3) not found -- 捕獲所有以'02'開頭的sqlstate代碼
(4) sqlwarning -- 捕獲所有以'01'開頭的sqlstate代碼的警告
(5)sqlexception -- 捕獲所有不以'00'(成功)、'01'(警告)、'02'(未找到)開頭的sqlstate代碼的錯(cuò)誤
-- 3.statement：SQL語句

-- 創(chuàng)建班級(jí)表
create table class (id int primary key);
-- 創(chuàng)建學(xué)生表
create table student(id int primary key auto_increment,name varchar(20),sno varchar(10),age int,gender tinyint default 1,enroll_date datetime default now(),class_id int,foreign key (class_id) references class(id));
-- 向班級(jí)表插入數(shù)據(jù)
insert into class values (1),(2);
-- 向?qū)W生表插入數(shù)據(jù)
insert into student (name,sno,age,class_id) values ('劉備','100001',18,1),('關(guān)羽','100002',18,1),('張飛','100003',18,2),('趙云','100004',18,2);

delimiter //
create procedure demo_cursor(in input_class_id int)
begin
  -- 聲明變量用于接收每一列的結(jié)果
  declare student_name varchar(20);
  declare class_id int;
  declare flg bool default true;
  -- 聲明游標(biāo)用于接收查詢結(jié)果
  declare demo_cursor cursor for
    select s.name as student_name,c.id as id 
      from student as s,class as c 
      where s.class_id = c.id and s.class_id = input_class_id;
  -- 聲明處理程序
  declare continue handler for not found set flg := false;
  -- 創(chuàng)建新表
  create table class_new (id int primary key auto_increment,student_name varchar(20),class_id int,foreign key (class_id) references class(id)
  );
  -- 開啟游標(biāo)
  open demo_cursor;
  loop_read: loop 
    fetch demo_cursor into student_name,class_id;
    if not flg then
      leave loop_read;
    end if;
    insert into class_new values(null,student_name,class_id);
    end loop loop_read;
  -- 關(guān)閉游標(biāo)
  close demo_cursor;
end//
delimiter ;

call demo_cursor(1);

mysql> select * from class_new;
+----+--------------+----------+
| id | student_name | class_id |
+----+--------------+----------+
|  1 | 劉備         |        1 |
|  2 | 關(guān)羽         |        1 |
+----+--------------+----------+

create function 存儲(chǔ)函數(shù)名 (參數(shù)列表)
  returns 返回值類型 [characteristic]

create function func1(n int) returns int deterministic
begin
  declare `time` int default 0;
  while n > 0 do
    set `time` := `time` + n;
    set n := n - 1;
  end while;
  return `time`;
end;

select func1(100);

create trigger 觸發(fā)器名 
  {before|after} {insert|update|delete} on 表名
  for each row
  begin
    -- 觸發(fā)器邏輯
  end;

-- 創(chuàng)建日志表
create table log (id int primary key auto_increment,operation_type varchar(20) not null comment '操作類型',operation_time datetime not null comment '操作時(shí)間',
  operation_id int not null comment '操作的記錄的id',operation_date varchar(500) comment '操作數(shù)據(jù)'
);

-- insert觸發(fā)器
create trigger trg_insert after insert on student for each row
begin
  insert into log values (null,'insert',now(),new.id,concat(new.id,',',new.name,',',new.sno,',',new.age,',',new.gender,',',new.enroll_date,',',new.class_id));
end;
-- 向?qū)W生表插入數(shù)據(jù)
insert into student values (null,'馬超',200001,18,1,now(),1);

mysql> select * from log;
+----+----------------+---------------------+--------------+------------------------------------------+
| id | operation_type | operation_time      | operation_id | operation_date                           |
+----+----------------+---------------------+--------------+------------------------------------------+
|  1 | insert         | 2025-10-31 17:25:17 |            9 | 9,馬超,200001,18,1,2025-10-31 17:25:17,1 |
+----+----------------+---------------------+--------------+------------------------------------------+

-- update觸發(fā)器
create trigger trg_update after update on student for each row
begin
  insert into log values (null,'update',now(),new.id,concat(' 更新前數(shù)據(jù): ',old.id,',',old.name,',',old.sno,',',old.age,',',old.gender,',',old.enroll_date,',',old.class_id,' ,更新后數(shù)據(jù): ',new.id,',',new.name,',',new.sno,',',new.age,',',new.gender,',',new.enroll_date,',',new.class_id));
end;
-- 更新學(xué)生表的數(shù)據(jù)
update student set age = 20,enroll_date = now(),class_id = 2 where name = '馬超';

mysql> select * from log;
+----+----------------+---------------------+--------------+---------------------------------------------------------------------------------------------------------------+
| id | operation_type | operation_time      | operation_id | operation_date                                                                                                |
+----+----------------+---------------------+--------------+---------------------------------------------------------------------------------------------------------------+
|  3 | insert         | 2025-10-31 17:40:14 |           10 | 10,馬超,200001,18,1,2025-10-31 17:40:14,1                                                                     |
|  4 | update         | 2025-10-31 17:41:11 |           10 |  更新前數(shù)據(jù): 10,馬超,200001,18,1,2025-10-31 17:40:14,1 ,更新后數(shù)據(jù): 10,馬超,200001,20,1,2025-10-31 17:41:11,2 |
+----+----------------+---------------------+--------------+---------------------------------------------------------------------------------------------------------------+

-- delete觸發(fā)器
create trigger trg_delete after delete on student for each row
begin
  insert into log values (null,'delete',now(),old.id,concat('刪除的數(shù)據(jù): ',old.id,',',old.name,',',old.sno,',',old.age,',',old.gender,',',old.enroll_date,',',old.class_id));
end;

delete from student where name = '馬超';

mysql> select * from student;
+----+------+--------+------+--------+---------------------+----------+
| id | name | sno    | age  | gender | enroll_date         | class_id |
+----+------+--------+------+--------+---------------------+----------+
|  1 | 劉備 | 100001 |   18 |      1 | 2025-10-26 22:11:23 |        1 |
|  2 | 關(guān)羽 | 100002 |   18 |      1 | 2025-10-26 22:11:23 |        1 |
|  3 | 張飛 | 100003 |   18 |      1 | 2025-10-26 22:11:23 |        2 |
|  4 | 趙云 | 100004 |   18 |      1 | 2025-10-26 22:11:23 |        2 |
+----+------+--------+------+--------+---------------------+----------+
4 rows in set (0.00 sec)
mysql> select * from log;
+----+----------------+---------------------+--------------+---------------------------------------------------------------------------------------------------------------+
| id | operation_type | operation_time      | operation_id | operation_date                                                                                                |
+----+----------------+---------------------+--------------+---------------------------------------------------------------------------------------------------------------+
|  3 | insert         | 2025-10-31 17:40:14 |           10 | 10,馬超,200001,18,1,2025-10-31 17:40:14,1                                                                     |
|  4 | update         | 2025-10-31 17:41:11 |           10 |  更新前數(shù)據(jù): 10,馬超,200001,18,1,2025-10-31 17:40:14,1 ,更新后數(shù)據(jù): 10,馬超,200001,20,1,2025-10-31 17:41:11,2 |
|  5 | delete         | 2025-10-31 17:47:09 |           10 | 刪除的數(shù)據(jù): 10,馬超,200001,20,1,2025-10-31 17:41:11,2                                                         |
+----+----------------+---------------------+--------------+---------------------------------------------------------------------------------------------------------------+