MYSQL存儲過程的示例分析

這篇文章主要介紹了MYSQL存儲過程的示例分析，具有一定借鑒價值，感興趣的朋友可以參考下，希望大家閱讀完這篇文章之后大有收獲，下面讓小編帶著大家一起了解一下。

0.環境說明：

1.使用說明

存儲過程時數據庫的一個重要的對象，可以封裝SQL語句集，可以用來完成一些較復雜的業務邏輯，并且可以入參出參(類似于java中的方法的書寫)。

創建時會預先編譯后保存，用戶后續的調用都不需要再次編譯。

// 把editUser類比成一個存儲過程
public void editUser(User user,String username){
    String a = "nihao";
    user.setUsername(username);
}
main(){
    User user = new User();
 editUser(user,"張三");
    user.getUseranme();   //java基礎
}

大家可能會思考，用sql處理業務邏輯還要重新學，我用java來處理邏輯（比如循環判斷、循環查詢等）不行嗎？那么，為什么還要用存儲過程處理業務邏輯呢？

優點：

在生產環境下，可以通過直接修改存儲過程的方式修改業務邏輯（或bug），而不用重啟服務器。
 執行速度快，存儲過程經過編譯之后會比單獨一條一條執行要快。
 減少網絡傳輸流量。
 方便優化。

缺點：

過程化編程，復雜業務處理的維護成本高。
 調試不便
 不同數據庫之間可移植性差。-- 不同數據庫語法不一致！

2.準備

數據庫參閱資料中的sql腳本：

delimiter $$ --聲明結束符

3.語法

官方參考網址：

https://dev.mysql.com/doc/refman/5.6/en/sql-statements.html
https://dev.mysql.com/doc/refman/5.6/en/sql-compound-statements.html

#### 3.0 語法結構

```sql
-- 存儲過程結構
CREATE
    [DEFINER = user]
 PROCEDURE sp_name ([proc_parameter[,...]])
    [characteristic ...] routine_body
    
-- 1. proc_parameter參數部分，可以如下書寫：
 [ IN | OUT | INOUT ] param_name type
 -- type類型可以是MySQL支持的所有類型
 
-- 2. routine_body(程序體)部分，可以書寫合法的SQL語句 BEGIN ... END

簡單演示：

-- 聲明結束符。因為MySQL默認使用‘;'作為結束符，而在存儲過程中，會使用‘;'作為一段語句的結束，導致‘;'使用沖突
delimiter $$

CREATE PROCEDURE hello_procedure ()
BEGIN
 SELECT 'hello procedure';
END $$

call hello_procedure();

3.1 變量及賦值

類比一下java中的局部變量和成員變量的聲明和使用

局部變量：

用戶自定義，在begin/end塊中有效

語法：
聲明變量 declare var_name type [default var_value];
舉例：declare nickname varchar(32);


-- set賦值
delimiter $$
create procedure sp_var01()
begin
 declare nickname varchar(32) default 'unkown';
 set nickname = 'ZS';
 -- set nickname := 'SF';
 select nickname;
end$$
-- into賦值
delimiter $$
create procedure sp_var_into()
begin
 declare emp_name varchar(32) default 'unkown' ;
 declare emp_no int default 0;
 select e.empno,e.ename into emp_no,emp_name from emp e where e.empno = 7839;
 select emp_no,emp_name;
end$$

用戶變量：
用戶自定義，當前會話（連接）有效。類比java的成員變量

語法：
@var_name
不需要提前聲明，使用即聲明

-- 賦值
delimiter $$
create procedure sp_var02()
begin
 set @nickname = 'ZS';
 -- set nickname := 'SF';
end$$
call sp_var02() $$
select @nickname$$  --可以看到結果

會話變量：
由系統提供，當前會話（連接）有效

語法：

@@session.var_name

show session variables; -- 查看會話變量
select @@session.unique_checks; -- 查看某會話變量
set @@session.unique_checks = 0; --修改會話變量

全局變量：
由系統提供，整個mysql服務器有效

語法：
@@global.var_name

舉例：

-- 查看全局變量中變量名有char的記錄

show global variables like '%char%'; 

-- 查看全局變量character_set_client的值
select @@global.character_set_client;

3.2 入參出參

-- 語法
in | out | inout param_name type

舉例：

-- IN類型演示
delimiter $$
create procedure sp_param01(in age int)
begin
 set @user_age = age;
end$$
call sp_param01(10) $$
select @user_age$$
-- OUT類型，只負責輸出！
-- 需求：輸出傳入的地址字符串對應的部門編號。
delimiter $$

create procedure sp_param02(in loc varchar(64),out dept_no int(11))
begin
 select d.deptno into dept_no from dept d where d.loc = loc;
 --此處強調，要么表起別名，要么入參名不與字段名一致
end$$
delimiter ;

--測試
set @dept_no = 100;
call sp_param02('DALLAS',@dept_no);
select @dept_no;
-- INOUT類型 
delimiter $$
create procedure sp_param03(inout name varchar(49))
begin
 set name = concat('hello' ,name);
end$$
delimiter ;

set @user_name = '小明';
call sp_param03(@user_name);
select @user_name;

3.3 流程控制-判斷

官網說明
https://dev.mysql.com/doc/refman/5.6/en/flow-control-statements.html

if

-- 語法
IF search_condition THEN statement_list
    [ELSEIF search_condition THEN statement_list] ...
    [ELSE statement_list]
END IF

舉例：

-- 前置知識點：timestampdiff(unit,exp1,exp2) 取差值exp2-exp1差值，單位是unit
select timestampdiff(year,e.hiredate,now()) from emp e where e.empno = '7499         ';
delimiter $$
-- DROP PROCEDURE IF EXISTS sp_param04;
create procedure sp_param05(in ages timestamp)
begin
 declare result varchar(32);
 if timestampdiff(year,ages,now())>40 
  then set result = '元老';
 elseif timestampdiff(year,ages,now())>38 
  then set result = '老員工';
 ELSE 
  SET result = '新手';
 end if;
 select result;
end $$
delimiter;
 
call sp_param05('1970-02-26 10:00:25');
-- 注意：MYSQL時間戳必須從1970年開始。

case

此語法是不僅可以用在存儲過程，查詢語句也可以用！

-- 語法一（類比java的switch）：
CASE case_value
    WHEN when_value THEN statement_list
    [WHEN when_value THEN statement_list] ...
    [ELSE statement_list]
END CASE
-- 語法二：
CASE
    WHEN search_condition THEN statement_list
    [WHEN search_condition THEN statement_list] ...
    [ELSE statement_list]
END CASE

舉例：

-- 需求：入職年限年齡<=38是新手 >38并 <=40老員工 >40元老
delimiter $$
create procedure sp_hire_case()
begin
 declare result varchar(32);
 declare message varchar(64);
 case
    when timestampdiff(year,'2001-01-01',now()) > 40 
  then 
   set result = '元老';
   set message = '老爺爺';
 when timestampdiff(year,'2001-01-01',now()) > 38
  then 
   set result = '老員工';
   set message = '油膩中年人';
 else 
  set result = '新手';
  set message = '萌新';
 end case;
 select result;
end$$
delimiter ;

3.4 流程控制-循環

loop

-- 語法
[begin_label:] LOOP
    statement_list
END LOOP [end_label]

舉例:

需要說明，loop是死循環，需要手動退出循環，我們可以使用leave來退出。

可以把leave看成我們java中的break；與之對應的，就有iterate（繼續循環）——類比java的continue

--需求：循環打印1到10
-- leave控制循環的退出
delimiter $$
create procedure sp_flow_loop()
begin
 declare c_index int default 1;
 declare result_str  varchar(256) default '1';
 cnt:loop
 
  if c_index >= 10
  then leave cnt;
  end if;

  set c_index = c_index + 1;
  set result_str = concat(result_str,',',c_index);
  
 end loop cnt;
 
 select result_str;
end$$

-- iterate + leave控制循環
delimiter $$
create procedure sp_flow_loop02()
begin
 declare c_index int default 1;
 declare result_str  varchar(256) default '1';
 cnt:loop

  set c_index = c_index + 1;
  set result_str = concat(result_str,',',c_index);
  if c_index < 10 then 
   iterate cnt; 
  end if;
  -- 下面這句話能否執行到？什么時候執行到？ 當c_index < 10為false時執行
  leave cnt;
  
 end loop cnt;
 select result_str;
 
end$$

repeat

[begin_label:] REPEAT
    statement_list
UNTIL search_condition -- 直到…為止，才退出循環
END REPEAT [end_label]
-- 需求：循環打印1到10
delimiter $$
create procedure sp_flow_repeat()
begin
 declare c_index int default 1;
 -- 收集結果字符串
 declare result_str varchar(256) default '1';
 count_lab:repeat
  set c_index = c_index + 1;
  set result_str = concat(result_str,',',c_index);
  until c_index >= 10
 end repeat count_lab;
 select result_str;
end$$

while

類比java的while(){}
[begin_label:] WHILE search_condition DO
    statement_list
END WHILE [end_label]
-- 需求：循環打印1到10
delimiter $$
create procedure sp_flow_while()
begin
 declare c_index int default 1;
 -- 收集結果字符串
 declare result_str varchar(256) default '1';
 while c_index < 10 do
  set c_index = c_index + 1;
  set result_str = concat(result_str,',',c_index);
 end while;
 select result_str;
end$$

3.5 流程控制-退出、繼續循環

leave

類比java的breake
-- 退出 LEAVE can be used within BEGIN ... END or loop constructs (LOOP, REPEAT, WHILE).
LEAVE label

iterate

類比java的continue
-- 繼續循環 ITERATE can appear only within LOOP, REPEAT, and WHILE statements
ITERATE label

3.6 游標

用游標得到某一個結果集，逐行處理數據。

類比jdbc的ResultSet
-- 聲明語法
DECLARE cursor_name CURSOR FOR select_statement
-- 打開語法
OPEN cursor_name
-- 取值語法
FETCH cursor_name INTO var_name [, var_name] ...
-- 關閉語法
CLOSE cursor_name
-- 需求：按照部門名稱查詢員工，通過select查看員工的編號、姓名、薪資。（注意，此處僅僅演示游標用法）
-- 更改結束符為$$
delimiter $$
-- 創造存儲過程(帶一個入參)
create procedure sp_create_table02(in dept_name varchar(32))
begin
-- 必須先聲明變量
 declare e_no int;
 declare e_name varchar(32);
 declare e_sal decimal(7,2);
 
 declare lp_flag boolean default true;
-- 其次聲明游標:游標值為query（dept_name)得到的table(e.empno,e.ename,e.sal)
 declare emp_cursor cursor for 
  select e.empno,e.ename,e.sal
  from emp e,dept d
  where e.deptno = d.deptno and d.dname = dept_name;
  
-- 然后聲明 handler 句柄：
-- 關于句柄:https://blog.csdn.net/qq_43427482/article/details/109898934
-- 看完還沒理解，再看：https://www.cnblogs.com/phpper/p/7587556.html
-- 這里涉及了SQL STATE:https://blog.csdn.net/u014653854/article/details/78986780
-- 聲明handler句柄:當每條SQL傳遞ERROR STATE為沒有值的報錯時，設定變量lp_flag為非真，同時繼續執行SQL（如不聲明，當某條循環報錯時，整個SQL將直接停止循環）
 declare continue handler for NOT FOUND set lp_flag = false;
-- 打開游標
 open emp_cursor;
-- 開啟LOOP循環：emp_loop
 emp_loop:loop
-- 將游標值傳遞給三個變量
  fetch emp_cursor into e_no,e_name,e_sal;
-- 如果變量lp_flag為真，則獲取這三個參數的值；否則打斷emp_loop循環
  if lp_flag then
   select e_no,e_name,e_sal;
  else
   leave emp_loop;
  end if;
-- 結束循環
 end loop emp_loop;
-- 定義用戶變量并賦值（用戶變量不需要提前聲明、僅當前會話有效）>鄙人沒有理解這一步什么意義
 set @end_falg = 'exit_flag';
-- 關閉游標
 close emp_cursor;
-- 結束存儲過程
end$$
-- 恢復;結束符
delimiter;

-- 使用該存儲過程并傳參
call sp_create_table02('RESEARCH');

特別注意：

在語法中，變量聲明、游標聲明、handler聲明是必須按照先后順序書寫的，否則創建存儲過程出錯。

3.7 存儲過程中的handler

handler句柄用于定義條件處理

DECLARE handler_action HANDLER
    FOR condition_value [, condition_value] ...
    statement

handler_action: {
    CONTINUE  -- 繼續執行
  | EXIT      -- 退出執行
  | UNDO      -- 什么都不做
}

CONTINUE: Execution of the current program continues. -- 繼續執行當前程序
EXIT: Execution terminates for the BEGIN ... END compound statement in which the handler is declared. This is true even if the condition occurs in an inner block. -- 停止執行在handler被聲明的BEGIN... END的組合程序，即使是在程序內部發生該條件。

condition_value: {
    mysql_error_code
  | SQLSTATE [VALUE] sqlstate_value
  | condition_name
  | SQLWARNING
  | NOT FOUND
  | SQLEXCEPTION
}

SQLWARNING: Shorthand for the class of SQLSTATE values that begin with '01'. -- 即SQL STATE以01開頭的集合代稱
NOT FOUND: Shorthand for the class of SQLSTATE values that begin with '02'. -- 即SQL STATE以O2開頭的集合代稱
SQLEXCEPTION: Shorthand for the class of SQLSTATE values that do not begin with '00', '01', or '02'. -- 即SQL STATE不以00、01、02開頭的集合代稱
-- 各種寫法：
 DECLARE exit HANDLER FOR SQLSTATE '42S01' set @res_table = 'EXISTS';
 DECLARE continue HANDLER FOR 1050 set @res_table = 'EXISTS';
 DECLARE continue HANDLER FOR not found set @res_table = 'EXISTS';

4.練習

——大家注意，存儲過程的業務過程在java代碼中一般也可以實現，我們下面的需求是為了練習存儲過程

4.1 利用存儲過程更新數據

為某部門(需指定)的人員漲薪100;如果是公司總裁，則不漲薪。

delimiter // -- 定義結束符
create procedure high_sal(in dept_name varchar(32)) -- 創建存儲過程
begin -- 開始存儲過程
 declare e_no int; -- 聲明變量
 declare e_name varchar(32);
 declare e_sal decimal(7,2);
 
 declare lp_flag boolean default true;
 
 declare emp_cursor cursor for  -- 聲明游標
  select e.empno,e.ename,e.sal
  from emp e,dept d
  where e.deptno = d.deptno and d.dname = dept_name;
  
 -- 聲明handler句柄（條件處理）
 declare continue handler for NOT FOUND set lp_flag = false;
  
 open emp_cursor; -- 打開游標
 
 emp_loop:loop -- 開啟循環
  fetch emp_cursor into e_no,e_name,e_sal; -- 變量賦值
  
  if lp_flag then -- 流程控制
   if e_name = 'king' then 
    iterate emp_loop; -- 繼續循環
   else 
    update emp e set e.sal = e.sal + 100 where e.empno = e_no; -- 更新數據
   end if;
  else
   leave emp_loop; -- 離開循環
  end if; -- 結束流程
  
 end loop emp_loop; -- 結束循環
 set @end_falg = 'exit_flag'; -- 聲明用戶變量
 close emp_cursor; -- 變比游標
end // -- 結束存儲過程
delimiter; -- 恢復結束符


call high_sal('ACCOUNTING');

4.2 循環創建表

創建下個月的每天對應的表comp_2020_04_01、comp_2020_04_02、...


（模擬）需求描述：

我們需要用某個表記錄很多數據，比如記錄某某用戶的搜索、購買行為(注意，此處是假設用數據庫保存)，當每天記錄較多時，如果把所有數據都記錄到一張表中太龐大，需要分表，我們的要求是，每天一張表，存當天的統計數據，就要求提前生產這些表——每月月底創建下一個月每天的表！

-- 知識點 預處理 prepare語句from后使用局部變量會報錯 
-- https://dev.mysql.com/doc/refman/5.6/en/sql-prepared-statements.html
-- 看不懂英文文檔的看這個：https://www.cnblogs.com/geaozhang/p/9891338.html；話說我也該去做一點英文技術文檔的閱讀提升了
PREPARE stmt_name FROM preparable_stmt
EXECUTE stmt_name [USING @var_name [, @var_name] ...]
{DEALLOCATE | DROP} PREPARE stmt_name

-- 貼一段預處理的案例，需求是：利用字符串定義預處理 SQL (直角三角形斜邊hypotenuse計算)
PREPARE stmt1 FROM 'SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse'; -- POW(x,y)函數,用于計算x的y次方（http://c.biancheng.net/mysql/pow_power.html）；SQRT函數用于求平方根（https://blog.csdn.net/weixin_39554172/article/details/113124290）
SET @a = 3;
SET @b = 4;  -- 用戶變量使用即聲明
EXECUTE stmt1 USING @a, @b; -- 結果為5
DEALLOCATE PREPARE stmt1; 

-- 知識點 時間的處理
-- EXTRACT(unit FROM date)截取時間的指定位置值
-- DATE_ADD(date,INTERVAL expr unit)  日期運算
-- LAST_DAY(date)  獲取日期的最后一天的日期
-- YEAR(date) 返回日期中的年
-- MONTH(date) 返回日期的月
-- DAYOFMONTH(date) 返回日 -- 注：根據https://stackoverflow.com/questions/35838321/day-vs-dayofmonth-in-mysql，其實DAY(date)效果是一樣的
-- 思路：循環構建表名 comp_2020_05_01 到 comp_2020_05_31；并執行create語句。
-- 分析：1. 循環構筑表，僅表名不同，考慮使用存儲過程執行循環處理、使用預處理提高效率。 2. 首先需要一個變量存儲執行SQL；然后年份需要一個變量、月份需要一個變量、日期需要一個變量、拼接出來的表名需要一個變量；除此之外，還需要一個數字用來累增；至此，我們得到了至少需要6個變量。為了補全日期0，增加兩個月份、日的變量用來補充0形成01、02···。 3. 考慮到預處的格式（from后不能有局部變量）,寫7個局部變量，1個用戶變量
delimiter //  --聲明結束符
create procedure sp_create_table()
begin
--  定義一堆局部變量
 declare next_year int;
 declare next_month int;
 declare next_month_maxday int;
  
 declare next_month_str char(2);
 declare next_month_maxday_str char(2);
 
 -- 處理每天的表名
 declare table_name_str char(10);
 -- 統計序列
 declare t_index int default 1;
 -- declare create_table_sql varchar(200);
 
 -- 獲取下個月的年份
 set next_year = year(date_add(now(),INTERVAL 1 month));
 -- 獲取下個月是幾月 
 set next_month = month(date_add(now(),INTERVAL 1 month));
 -- 下個月最后一天是幾號
 set next_month_maxday = dayofmonth(LAST_DAY(date_add(now(),INTERVAL 1 month)));
 
 -- 把1-9月補上0:  01,02····，09
 if next_month < 10
  then set next_month_str = concat('0',next_month);
 else
  set next_month_str = concat('',next_month);
 end if;
 
 
 while t_index <= next_month_maxday do
  
  -- 同上，對天數補0
  if (t_index < 10)
   then set next_month_maxday_str = concat('0',t_index);
  else
   set next_month_maxday_str = concat('',t_index);
  end if;
  
  -- 2020_05_01
  set table_name_str = concat(next_year,'_',next_month_str,'_',next_month_maxday_str);
  -- 拼接create sql語句（用戶變量）
  set @create_table_sql = concat(
     'create table comp_',
     table_name_str,
     '(`grade` INT(11) NULL,`losal` INT(11) NULL,`hisal` INT(11) NULL) COLLATE=\'utf8_general_ci\' ENGINE=InnoDB');
  -- FROM后面不能使用局部變量！這就是我們為什么使用用戶變量的原因
  prepare create_table_stmt FROM @create_table_sql;
  execute create_table_stmt;
  DEALLOCATE prepare create_table_stmt;
  
  set t_index = t_index + 1;
  
 end while; 
end//
delimiter;

call sp_create_table()

-- 以下為個人精簡版

delimiter //
CREATE PROCEDURE sp_createtable1 () BEGIN-- 統計序列
 DECLARE
  t_index INT DEFAULT 1;
 WHILE
   t_index <= DAY (
    LAST_DAY(
    date_add( now(), INTERVAL 1 MONTH ))) DO
   
   SET @create_table_sql = concat(
    'CREATE TABLE comp_',
    YEAR (
    date_add( now(), INTERVAL 1 MONTH )),
    '_',
    MONTH (
    date_add( now(), INTERVAL 1 MONTH )),
    '_',
    t_index,
    '(`grade` INT(11) NULL,`losal` INT(11) NULL,`hisal` INT(11) NULL) COLLATE=\'utf8_general_ci\' ENGINE=InnoDB' 
   );-- FROM后面不能使用局部變量！這就是我們為什么使用用戶變量的原因
  PREPARE create_table_stmt 
  FROM
   @create_table_sql;
  EXECUTE create_table_stmt;
  DEALLOCATE PREPARE create_table_stmt;
  
  SET t_index = t_index + 1;
  
 END WHILE;
 
END // 
delimiter;
CALL sp_createtable1 ()

4.3 其他場景：

“為用戶添加購物積分，并更新到用戶的總積分表中”等需要對多張表進行CRUD操作的業務。
而且內部可以使用事務命令。

5.其他

5.1 characteristic

characteristic:
    COMMENT 'string'
  | LANGUAGE SQL
  | [NOT] DETERMINISTIC
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }

其中，SQL SECURITY的含義如下：

 MySQL存儲過程是通過指定SQL SECURITY子句指定執行存儲過程的實際用戶；
 如果SQL SECURITY子句指定為DEFINER(定義者)，存儲過程將使用存儲過程的DEFINER執行存儲過程，驗證調用存儲過程的用戶是否具有存儲過程的execute權限和DEFINER用戶是否具有存儲過程引用的相關對象(存儲過程中的表等對象)的權限；
 如果SQL SECURITY子句指定為INVOKER(調用者)，那么MySQL將使用當前調用存儲過程的用戶執行此過程，并驗證用戶是否具有存儲過程的execute權限和存儲過程引用的相關對象的權限；
 如果不顯示的指定SQL SECURITY子句，MySQL默認將以DEFINER執行存儲過程。

5.2 死循環處理

-- 如有死循環處理，可以通過下面的命令查看并結束
show processlist;
kill id;

5.3 可以在select語句中寫case

https://dev.mysql.com/doc/refman/5.6/en/control-flow-functions.html
select 
 case
  when timestampdiff(year,e.hiredate,now()) <= 38 then '新手'
  when timestampdiff(year,e.hiredate,now()) <= 40 then '老員工'
  else '元老'
 end hir_loc,
 e.*
from emp e;

5.4 臨時表

臨時表在關閉會話后會被自動銷毀。
https://www.runoob.com/mysql/mysql-temporary-tables.html

create temporary table 表名(
　　字段名 類型 [約束],
　　name varchar(20) 
)Engine=InnoDB default charset utf8;

-- 需求：按照部門名稱查詢員工，通過select查看員工的編號、姓名、薪資。（注意，此處僅僅演示游標用法）
delimiter $$
create procedure sp_create_table02(in dept_name varchar(32))
begin
 declare emp_no int;
 declare emp_name varchar(32);
 declare emp_sal decimal(7,2);
 declare exit_flag int default 0;
 
 declare emp_cursor cursor for
  select e.empno,e.ename,e.sal
  from emp e inner join dept d on e.deptno = d.deptno where d.dname = dept_name;
 
 declare continue handler for not found set exit_flag = 1;
 
 -- 創建臨時表收集數據
 CREATE temporary TABLE `temp_table_emp` (
  `empno` INT(11) NOT NULL COMMENT '員工編號',
  `ename` VARCHAR(32) NULL COMMENT '員工姓名' COLLATE 'utf8_general_ci',
  `sal` DECIMAL(7,2) NOT NULL DEFAULT '0.00' COMMENT '薪資',
  PRIMARY KEY (`empno`) USING BTREE
 )
 COLLATE='utf8_general_ci'
 ENGINE=InnoDB; 
 
 open emp_cursor;
 
 c_loop:loop
  fetch emp_cursor into emp_no,emp_name,emp_sal;
  
  
  if exit_flag != 1 then
   insert into temp_table_emp values(emp_no,emp_name,emp_sal); 
  else
   leave c_loop;
  end if;
  
 end loop c_loop;
 
 select * from temp_table_emp;
 
 select @sex_res; -- 僅僅是看一下會不會執行到
 close emp_cursor;
 
end$$

call sp_create_table02('RESEARCH');

附：本例建表SQL

CREATE TABLE `dept` (
 `deptno` INT(11) NOT NULL COMMENT '部門編號',
 `dname` VARCHAR(32) NULL COMMENT '部門名稱' COLLATE 'utf8_general_ci',
 `loc` VARCHAR(64) NULL COMMENT '部門地址' COLLATE 'utf8_general_ci',
 PRIMARY KEY (`deptno`) USING BTREE
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
;
INSERT INTO DEPT VALUES
 (10,'ACCOUNTING','NEW YORK');
INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS');
INSERT INTO DEPT VALUES
 (30,'SALES','CHICAGO');
INSERT INTO DEPT VALUES
 (40,'OPERATIONS','BOSTON');
 
CREATE TABLE `emp` (
 `empno` INT(11) NOT NULL COMMENT '員工編號',
 `ename` VARCHAR(32) NULL COMMENT '員工姓名' COLLATE 'utf8_general_ci',
 `job` VARCHAR(10) NULL COMMENT '職位' COLLATE 'utf8_general_ci',
 `mgr` INT(11) NULL COMMENT '上級編號',
 `hiredate` DATE NOT NULL COMMENT '入職時間',
 `sal` DECIMAL(7,2) NOT NULL DEFAULT '0.00' COMMENT '薪資',
 `comm` DECIMAL(7,2) NULL COMMENT '年終獎金',
 `deptno` INT(11) NOT NULL COMMENT '部門編號',
 PRIMARY KEY (`empno`) USING BTREE,
 INDEX `FK_emp_dept` (`deptno`) USING BTREE,
 CONSTRAINT `FK_emp_dept` FOREIGN KEY (`deptno`) REFERENCES `procedure_demo`.`dept` (`deptno`) ON UPDATE RESTRICT ON DELETE RESTRICT
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
;

insert into emp values
(7369,'smith','clerk',7902,'1980-12-17',800,null,20);
insert into emp values
(7499,'allen','salesman',7698,'1981-02-20',1600,300,30);
insert into emp values
(7521,'ward','salesman',7698,'1981-02-22',1250,500,30);
insert into emp values
(7566,'jones','manager',7839,'1981-02-04',2975,null,20);
insert into emp values
(7654,'martin','salesman',7698,'1981-09-28',1250,1400,30);
insert into emp values
(7698,'blake','manager',7839,'1981-05-01',2850,null,30);
insert into emp values
(7782,'clark','manager',7839,'1981-06-09',2450,null,10);
insert into emp values
(7788,'scott','analyst',7566,'1987-07-13')-85,3000,null,20);
insert into emp values
(7839,'king','president',null,'1981-11-17',5000,null,10);
insert into emp values
(7844,'turner','salesman',7698,'1981-09-08',1500,0,30);
insert into emp values
(7876,'adams','clerk',7788,'1987-07-13')-51,1100,null,20);
insert into emp values
(7900,'james','clerk',7698,'1981-12-03',950,null,30);
insert into emp values
(7902,'ford','analyst',7566,'1981-12-03',3000,null,20);
insert into emp values
(7934,'miller','clerk',7782,'1982-01-23',1300,null,10);

CREATE TABLE `salgrade` (
 `grade` INT(11) NULL,
 `losal` INT(11) NULL,
 `hisal` INT(11) NULL
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
;
insert into salgrade values (1,700,1200);
insert into salgrade values (2,1201,1400);
insert into salgrade values (3,1401,2000);
insert into salgrade values (4,2001,3000);
insert into salgrade values (5,3001,9999);

感謝你能夠認真閱讀完這篇文章，希望小編分享的“MYSQL存儲過程的示例分析”這篇文章對大家有幫助，同時也希望大家多多支持億速云，關注億速云行業資訊頻道，更多相關知識等著你來學習!