Mysql(三)存储过程 procedure和函数

比眉伴天荒 2024-04-17 17:48 82阅读 0赞

# (一)存储过程 procedure #

### 1.概念： ###

> 存储过程是数据库中的一个对象，存储在服务端，用来封装多条SQL语句且带有逻辑性，可以实现一个功能，由于他在创建时，就已经对SQL进行了编译，所以执行效率高，而且可以重复调用，类似与我们Java中的方法\`

### 2.语法 ###

##### （1）创建方式 #####

DELIMITER $$
    
    CREATE
        
        PROCEDURE `库名`.`存储过程名`()
        
        BEGIN
    
        END$$
    
    DELIMITER ;

>     注意：创建存储过程需要管理员分配权限 
>       补充：delimiter是mysql定义结束标记的，在mysql客户端中结束标记默认是分号（；）。
>       如果一次输入的语句较多，并且语句中间有分号，这时需要新指定一个特殊的结束符。
>       delimiter $$ 表示mysql用$$表示mysql语句结束，过程结束后肯定会有一句delimiter ；
>       表示恢复成默认的。

### (2)参数 ###

in:输入参数
      out：输出参数
      inout：输入输出参数
      
    例如：
      DELIMITER $$
      
      CREATE
          PROCEDURE `performance_schema`.`myTestPro`(IN num INT,OUT r INT)
          BEGIN
      	DELETE FROM emp WHERE empno=num;
      	SELECT COUNT(*) FROM emp INTO r;
          END$$
      
      DELIMITER ;

### （3）调用存储过程 call ###

调用存储过程 CALL Myprocedure(9527,@rr)
    查询结果   select @rr；
    删除存储过程 dro 语法： drop procedure myTestPro;
    查看存储过程 show 
    1、show procedure status\G;  -- 查看所有的存储过程状态
    2、show create procedure 存储过程名字\G; -- 查看创建存储过程的语句

#### （4）带有IF逻辑的存储过程 ####

#### if then elseif else ####

DELIMITER $$
    CREATE PROCEDURE pro_testIf(IN num INT,OUT str VARCHAR(20))
    BEGIN
    	IF num=1 THEN
    		SET str='星期一';
    	ELSEIF num=2 THEN              --注意elseif  连在一块
    		SET str='星期二';
    	ELSEIF num=3 THEN
    		SET str='星期三';             -- 注意要用分号结束
    	ELSE
    		SET str='输入错误';
    	END IF;                        -注意要结束if  后面有分号
    END $$

### 带有循环的存储过程 while do ###

DELIMITER $
    CREATE PROCEDURE pro_testWhile(IN num INT,OUT result INT)
    BEGIN
    	-- 定义一个局部变量
    	DECLARE i INT DEFAULT 1;
    	DECLARE vsum INT DEFAULT 0;
    	WHILE i<=num DO
    	      SET vsum = vsum+i;
    	      SET i=i+1;
    	END WHILE;  -- 要记得结束循环
    	SET result=vsum;
    END $

#### 其他循环的写法（了解） ####

例如：  REPEAT 循环 
    DELIMITER $
     CREATE PROCEDURE proRepeat(OUT outnum INT)
         BEGIN
         DECLARE i INT DEFAULT 0;
         REPEAT
             SET i=i+1;
             UNTIL i>=5  -- 注意这里不要加分号,不然语法报错 UNTIL 结束条件
         END REPEAT; -- 记着结束
         SET outnum=i;
      END $
      CALL proRepeat(@num);
      SELECT @num;
      
      
     例如：loop 循环 
     
     DELIMITER $
     CREATE PROCEDURE proLoop(OUT outnum INT)
      BEGIN
         DECLARE i INT DEFAULT 0;
          myloop:LOOP    -- 这里的myloop 是我给循环起的一个标号或者说名字,这是语法要求,不起就会报错
             SET i=i+1;
            IF i>=5 THEN
                LEAVE myloop;  -- 根据我的循环标号中断循环 leave 是中断循环的意思
             END IF;
        END LOOP;
         SET outnum=i;
      END $
      
      CALL proLoop(@num);
      SELECT @num;

#### 控制循环的两个关键字 ####

![在这里插入图片描述][20190829160246207.png]

#### （5）变量 ####

全局变量（内置变量）：可以在多个会话中去访问他
     -- 查看所有全局变量： show variables
            -- 查看某个全局变量： select @@变量名
            -- 修改全局变量： set 变量名=新值
            -- character_set_client: mysql服务器的接收数据的编码
            -- character_set_results：mysql服务器输出数据的编码
            SET character_set_client=gbk   –设置数据库编码
             SELECT @@character_set_client  --查看数据库编码
    -- 会话变量： 只存在于当前客户端与数据库服务器端的一次连接当中。如果连接断开，那么会话变量全部丢失！
            -- 定义会话变量: set @变量=值
            -- 查看会话变量： select @变量
            
    -- 局部变量： 在存储过程中使用的变量就叫局部变量。只要存储过程执行完毕，局部变量就丢失！！
    		定义局部变量的语法：DECLARE i INT DEFAULT 1;  
    		给变量设置值 set i=10；

# （二）函数 #

函数：包括内置函数，和自定义函数
    自定义函数语法
      DELIMITER $$
      
      CREATE
         
          FUNCTION `mytestdb`.`myFun`(num INT)
          RETURNS INT
         
          BEGIN
      	DECLARE i INT DEFAULT 100;
      	SET i=i+num;
          RETURN i;
          END$$
      
      DELIMITER ;
      
      函数的调用 select 函数名();
      
      函数和存储过程的区别
      1.存储过程没有返回值，函数必须要有返回值。但是存储过程可以用out能实现返回值这个作用
      2.存储过程有in out inout 这几个参数类型 函数的参数全是用来收实参

[20190829160246207.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/17/f57ff1dbed9847a5bd53b22ca29a5375.png