1. Mysql 創(chuàng)建存儲過程

1.1 前言知識

1.1.1 語法結(jié)構(gòu)

1. 無參的存儲過程

   delimiter $
   CREATE PROCEDURE 存儲過程名()
   	begin
   		存儲過程體
   	end $;
   

2. 有參數(shù)的存儲過程

   delimiter $
   CREATE PROCEDURE 存儲過程名(in|out|inout 參數(shù)名1 參數(shù)類型，參數(shù)名2 參數(shù)類型……)
   	begin
   		存儲過程體
   	end $;
   

3. 刪除存儲過程：

   DROP PROCEDURE IF EXISTS `存儲過程名`;  
   

1.1.2 簡單解釋

• 部分語法簡單介紹：
  1. delimiter $$
$$ 是分隔符，用其他符號也行，比如一個$ 或者//等
  2. 定義變量：DECLARE
     例子：

     DECLARE `de_test` VARCHAR(20) DEFAULT '';
     

  3. @符號
     • 使用 SET 直接賦值變量，變量名以 @ 開頭：如:set @dogNum = 1002;
     • 其他使用例子如下：
       
       
  4. prepare語法格式
  • 處理動態(tài)sql，比如表名做變量的sql

    prepare stmt from 'sql語句; --定義
    execute stmt; -- 執(zhí)行
    deallocate prepare stmt;  -- 刪除定義（釋放資源）
    

1.2 創(chuàng)建存儲過程入門例子

1.2.1 無參存儲過程

1.2.1.1 不帶變量

1. 創(chuàng)建如下：

   DROP PROCEDURE IF EXISTS `sp_select_one_age_dogs`;  
   
   delimiter $
   CREATE PROCEDURE sp_select_one_age_dogs()
   	begin
   		select * from dog d where d.dog_age <=1;
   	end $
   

   
2. 測試看效果
   • 查看所有的狗狗
     
   • 調(diào)用存儲過程查看年齡不超過1歲的狗狗

     call sp_select_one_age_dogs();
     

     

1.2.1.2 帶變量

1. 創(chuàng)建如下：

   DROP PROCEDURE IF EXISTS `sp_test`;  
   
   delimiter $
   CREATE PROCEDURE sp_test()
   	begin
   		DECLARE `col_test` VARCHAR(20) DEFAULT '';
   		select 'test' into col_test from dual;
   		select col_test;
   	end $;
   

   
2. 測試效果

• 調(diào)用存儲過程：call sp_test();
  

1.2.2 有入?yún)⒌拇鎯^程

• 創(chuàng)建存儲過程

  DROP PROCEDURE IF EXISTS `sp_select_dog_by_num`;  
  
  delimiter $
  CREATE PROCEDURE sp_select_dog_by_num(in dogNum int(10))
  	begin
  		select d.dog_num ,d.dog_name ,d.dog_kind ,d.dog_age  
  	    from dog d where d.dog_num =dogNum;
  	end $
  

  
• 調(diào)用存儲過程
  • 調(diào)用方式1：直接給定參數(shù)值1001

    call sp_select_dog_by_num(1001);
    

    
  • 調(diào)用方式2：通過變量調(diào)用

    set @dogNum = 1002;
    call sp_select_dog_by_num(@dogNum);
    

    注意：賦值也可以用：set @dogNum := 1002;
    
    

1.2.3 有出參的存儲過程

• 直接在上面無參存儲過程 sp_test() 的基礎(chǔ)上改一個出參的存儲過程，如下：
  1. 創(chuàng)建出參存儲過程：

     DROP PROCEDURE IF EXISTS `sp_test_out`;  
     
     delimiter $
     CREATE PROCEDURE sp_test_out(out col_test varchar(20))
     	begin
     		select 'test' into col_test from dual;
     	end $;
     

     
  2. 測試看效果
     • 調(diào)用存儲過程，注意加：@

       call sp_test_out(@col_test);
       

       
     • 查看調(diào)用結(jié)果

       select @col_test;
       

       

1.2.4 有入?yún)⒑痛鎯Φ拇鎯^程

1. 創(chuàng)建存儲過程

   delimiter $
   CREATE PROCEDURE sp_select_dogName_by_num(in dogNum int(10),out dogName varchar(20))
   	begin
   		select d.dog_name into dogName from dog d where d.dog_num =dogNum;
   	end $
   

   
2. 調(diào)用看效果

   set @dogNum := 1003;
   call sp_select_dogName_by_num(@dogNum,@dogName);
   
   select @dogName;
   

   

1.2.5 inout的存儲過程

• 根據(jù)部門id找父節(jié)點(diǎn)（部門id或公司id），如下：
  • 創(chuàng)建存儲過程

    DROP PROCEDURE IF EXISTS `sp_select_pId_by_deptId`;  
    
    delimiter $
    CREATE PROCEDURE sp_select_pId_by_deptId(inout v_code varchar(10))
    	begin
    		SELECT t.PARENT_ID into v_code FROM SYS_COMPANY_DEPT t 
            where t.DEPT_ID =v_code;
    	end $
    	
    delimiter ;
    

    
  • 調(diào)用測試效果

    set @code := 'C001';
    call sp_select_pId_by_deptId(@code);
    select @code;
    

    
    

1.3 實(shí)用存儲過程例子

1.3.1 根據(jù)表名添加字段的存儲過程

• 動態(tài)給表添加字段 create_time 和 update_time
• 創(chuàng)建存儲過程

  drop procedure if exists `add_col_date`;  
  delimiter $$
  create procedure add_col_date(in tableName varchar(50))  
  begin 
  	set @tableName = tableName;
  	set @createTimeSql = concat(' alter table ',@tableName,' add create_time datetime;'); 
  	set @updateTimeSql = concat(' alter table ',@tableName,' add update_time datetime;'); 
  	select  @createTimeSql;
  	prepare stmt from @createTimeSql; 
  	prepare stmt2 from @updateTimeSql;
  	execute stmt;
  	execute stmt2;
  	deallocate prepare stmt; -- 釋放數(shù)據(jù)庫連接
  	deallocate prepare stmt2; 
  end $$
  delimiter ;
  

  
• 調(diào)用存儲過程，查看效果
  • 測試一張表，首先先看這個表的結(jié)構(gòu)：
    
  • 確定沒有那兩個字段，然后調(diào)用存儲過程
    
  • 再次查看表結(jié)構(gòu)，字段已添加上
    

1.3.2 遞歸查詢的存儲過程

1.3.2.1 遞歸查父id的存儲過程

1. 先看想實(shí)現(xiàn)的效果
   
2. 創(chuàng)建存儲過程

   drop procedure if exists sp_find_pId_by_deptId;
   
   delimiter $$
   create procedure sp_find_pId_by_deptId(inout deptId varchar(10))
   begin
       declare count_num int(10);  
       SET @@max_sp_recursion_depth = 10;
   	select count(0) into count_num from sys_company_dept t where t.`TYPE` ='1' and t.dept_id =deptId;
       if (count_num = 0) then
           select t.PARENT_ID into deptId from sys_company_dept t where t.dept_id =deptId;
   		call sp_find_pId_by_deptId(deptId); 
       end if;
   end $$
   delimiter ;
   

   
3. 測試效果

   set @deptId:='D001';
   call sp_find_pid_by_deptId(@deptId);
   select @deptId;
   

   

1.3.2.2 注意問題

• 遇到的問題：

  call sp_find_pid_by_deptId(@deptId)
  1456 - Recursive limit 0 (as set by the max_sp_recursion_depth variable) was exceeded for routine sp_find_pid_by_deptId
  

  
• 問題原因：
  原因是：存儲過程里默認(rèn)不允許遞歸，遞歸深度是0，可以查一下默認(rèn)的遞歸深度：

  select @@max_sp_recursion_depth;
  

  
• 解決問題：
  在存儲過程里設(shè)置遞歸深度即可：

  SET @@max_sp_recursion_depth = 10;
  

  

2. Mysql 創(chuàng)建函數(shù)

2.1 創(chuàng)建語法 與刪除語法

1. 創(chuàng)建語法
   • 如下：

     delimiter $$
     #在函數(shù)名后面一定要加上returns 函數(shù)返回類型
     create function fun_get_dog_name(dogNum VARCHAR(10)) 
     returns VARCHAR(30) 
     begin
     	declare dogName VARCHAR(30);  #在函數(shù)中定義一個變量，用來接收函數(shù)返回值
     	 函數(shù)邏輯處理
     	return dogName;   # 返回變量                
     end $$
     

2. 刪除語法：

   drop function if exists 函數(shù)名;
   

2.2 創(chuàng)建函數(shù)例子

2.2.1 入門例子

• 創(chuàng)建如下：

  drop function if exists fun_get_dog_name;
  
  delimiter $$
  #在函數(shù)名后面一定要加上returns 函數(shù)返回類型
  create function fun_get_dog_name(dogNum VARCHAR(10)) returns VARCHAR(30) 
  begin
  	declare dogName VARCHAR(30);  #在函數(shù)中定義一個變量，用來接收函數(shù)返回值
  	select d.dog_name into dogName from dog d where d.dog_num =dogNum;
  	return dogName;                   
  end $$
  

  
• 測試看效果

  select fun_get_dog_name('1001');
  

  
• 好了，簡單的一個小知識，就到這吧

文章來源地址http://www.zghlxwxcb.cn/news/detail-463891.html

到了這里，關(guān)于Mysql 創(chuàng)建存儲過程和函數(shù)及各種例子的文章就介紹完了。如果您還想了解更多內(nèi)容，請?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！