八、存储过程和函数

一、存储过程

　　含义：一组预先编译好的SQL语句的集合，理解成批处理语句

提高代码的重用性

简化操作

减少了编译次数并且减少了和数据库服务器的连接次数，提高了效率

1.1、创建语法

CREATE PROCEDURE 存储过程名(参数列表)
BEGIN
    存储过程体（一组合法的SQL语句）
END

参数列表包含三部分： 参数模式+ 参数名+参数类型

-- 举例：
in stuname varchar(20)

参数模式：

in：该参数可以作为输入，也就是该参数需要调用方传入值
out：该参数可以作为输出，也就是该参数可以作为返回值
inout：该参数既可以作为输入又可以作为输出，也就是该参数既需要传入值，又可以返回值

如果存储过程体仅仅只有一句话，begin end可以省略

　　　存储过程体中的每条sql语句的结尾要求必须加分号。存储过程的结尾可以使用 delimiter 重新设置

-- 语法：
    delimiter 结束标记
-- 案例：
        delimiter $

1.2、调用语法

　　CALL +存储过程名(实参列表);

-- 测试数据
CREATE TABLE pro_test (
  id BIGINT,
  NAME VARCHAR (30),
  phoneNo VARCHAR (30)
) ;
-- 插入数据
INSERT INTO `pro_test`(`id`, `name`,`phoneNo`) VALUES ('1',  'lisi','13333333'),(2,'zhsngsan','1444444')

带参数IN的存储过程

-- 简单的带 IN           
DELIMITER $$
CREATE PROCEDURE up_test(IN Pid BIGINT)
 BEGIN
    UPDATE pro_test SET `NAME` = '4444' WHERE `id` = Pid;
 END$$
DELIMITER ;
#调用 
CALL up_test(1);

带参数OUT的存储过程

-- 带 In 和out的
DELIMITER $$
CREATE PROCEDURE sel_one(IN Pid BIGINT ,OUT pname VARCHAR(30),OUT  nos VARCHAR(30))
 BEGIN
    SELECT NAME INTO pname FROM pro_test WHERE id = Pid;
    SELECT phoneNo INTO nos FROM pro_test WHERE id = Pid;
 END $$
DELIMITER ;
-- 定义两个参数，接受out的返回
SET @puname = '';
SET @phoneNo='';
-- 调用
CALL sel_one(1,@puname,@phoneNo1);
-- 查看
SELECT    @puname AS NAME,@phoneNo1 AS phoneNo;

1.3、删除存储过程

-- 语法：drop procedure 存储过程名
DROP PROCEDURE p1;

1.4、查看存储过程的信息

SHOW CREATE PROCEDURE  myp2;

二、函数

含义：一组预先编译好的SQL语句的集合，理解成批处理语句
1、提高代码的重用性
2、简化操作
3、减少了编译次数并且减少了和数据库服务器的连接次数，提高了效率

区别：
存储过程：可以有0个返回，也可以有多个返回，适合做批量插入、批量更新。
函数：有且仅有1 个返回，适合做处理数据后返回一个结果。

2.1、创建语法

CREATE FUNCTION 函数名(参数列表) RETURNS 返回类型
BEGIN
    函数体
END

　　注意：

- 参数列表包含两部分：参数名 +参数类型
- 函数体：肯定会有return语句，如果没有会报错，如果return语句没有放在函数体的最后也不报错，但不建议
- 函数体中仅有一句话，则可以省略begin end。
- 使用 delimiter语句设置结束标记

2.2、调用语法

SELECT 函数名(参数列表)

-- 建表
CREATE TABLE  fun_tbl SELECT * FROM pro_test

无入参有返回

DELIMITER $$
CREATE FUNCTION fun01() RETURNS INT
  BEGIN
  -- 定义局部变量
  DECLARE count_no INT DEFAULT 0;
  -- 赋值
  SELECT COUNT(*) INTO count_no FROM fun_tbl;
  RETURN count_no ;
  END $$
DELIMITER ;

-- 调用函数 
SELECT fun01();

有参有返回

DELIMITER $$
CREATE FUNCTION fun02(t_id BIGINT) RETURNS VARCHAR(30)
　　BEGIN
　　-- 定义局部变量
　　SET @nos =0;
　　-- 赋值
　　SELECT fun_tbl.`phoneNo` INTO @nos   FROM fun_tbl WHERE id = t_id;
　　RETURN @nos ;
　　END $$
DELIMITER ;

-- 调用函数 
SELECT fun02(1);

2.3、查看函数

SHOW CREATE FUNCTION 函数名;

2.3、删除函数

DROP FUNCTION 函数名;

三、流程控制

3.1、分支结构

(1)、if函数

语法：if(条件,值1，值2)
功能：实现双分支
应用在begin end中或外面

-- 函数分支
SELECT IF(TRUE,fun02(1),fun01());

(2)、case结构

　　语法：

　　情况1：类似于switch

case 变量或表达式
    when 值1 then 语句1;
    when 值2 then 语句2;
    ...
    else 语句n;
end

　　情况2：

case 
    when 条件1 then 语句1;
    when 条件2 then 语句2;
    ...
    else 语句n;
end

　　应用在begin end 中或外面

(3)、if结构

-- 语法：
if 条件1 then 语句1;
    elseif 条件2 then 语句2;
    ....
    else 语句n;
end if;

　　只能应用在begin end 中

CREATE FUNCTION test_if(score FLOAT) RETURNS CHAR
BEGIN
    DECLARE ch CHAR DEFAULT 'A';
    IF score>90 THEN SET ch='A';
    ELSEIF score>80 THEN SET ch='B';
    ELSEIF score>60 THEN SET ch='C';
    ELSE SET ch='D';
    END IF;
    RETURN ch;
END $

3.2、循环结构

(1)、while

[标签:]while 循环条件 do
    循环体;
end while[标签];

DELIMITER $$
CREATE FUNCTION insert_clu(num INT) RETURNS INT
BEGIN
     -- 定义变量记录表的多少条数据      
     DECLARE result INT DEFAULT 0;
     -- 条件比较变量
     DECLARE nos INT DEFAULT 0;
     -- 循环
     WHILE num>nos DO
     INSERT INTO fun_tbl (`name`, `phoneNo`)VALUES (RAND()+'',RAND()+'');
     SET nos = nos+1;
     END WHILE; 
     -- 统计表中数据
     SELECT COUNT(*) INTO result FROM fun_tbl;
     RETURN result;
END$$         
DELIMITER ;

-- 调用
SELECT insert_clu(5);

(2)、loop

-- 语法：
loop_name:loop
        if 条件 THEN -- 满足条件时离开循环
                leave loop_name;  -- 和 break 差不多都是结束训话
        end if；
end loop;

　　可以用来模拟简单的死循环

DELIMITER $
CREATE PROCEDURE sum2(a INT)
BEGIN
        DECLARE SUM INT DEFAULT 0;
        DECLARE i INT DEFAULT 1;
        loop_name:LOOP -- 循环开始
            IF i>a THEN 
                LEAVE loop_name;  -- 判断条件成立则结束循环  好比java中的 boeak
            END IF;
            SET SUM=SUM+i;
            SET i=i+1;
        END LOOP;  -- 循环结束
        SELECT SUM; -- 输出结果
END$
DELIMITER ;
-- 执行存储过程
CALL sum2(100);

(3)、repeat

-- 语法：
repeat
    循环体
until 条件 end repeat;

-- 实例；
DELIMITER $
CREATE PROCEDURE sum3(a INT)
BEGIN
        DECLARE SUM INT DEFAULT 0;
        DECLARE i INT DEFAULT 1;
        REPEAT -- 循环开始
            SET SUM=SUM+i;
            SET i=i+1;
        UNTIL i>a END REPEAT; -- 循环结束
        SELECT SUM; -- 输出结果
END $
DELIMITER &

-- 执行存储过程
CALL sum3(100);

　案例：批量插入，根据次数插入到admin表中多条记录，如果次数>20则停止

TRUNCATE TABLE admin$
DROP PROCEDURE test_while1$
CREATE PROCEDURE test_while1(IN insertCount INT)
BEGIN
    DECLARE i INT DEFAULT 1;
    a:WHILE i<=insertCount DO
        INSERT INTO admin(username,`password`) VALUES(CONCAT('xiaohua',i),'0000');
        IF i>=20 THEN LEAVE a;
        END IF;
        SET i=i+1;
    END WHILE a;
END $


CALL test_while1(100)$

　　leave语句，相当于 break,　　

　　批量插入，根据次数插入到admin表中多条记录，只插入偶数次

TRUNCATE TABLE admin$
DROP PROCEDURE test_while1$
CREATE PROCEDURE test_while1(IN insertCount INT)
BEGIN
    DECLARE i INT DEFAULT 0;
    a:WHILE i<=insertCount DO
        SET i=i+1;
        IF MOD(i,2)!=0 THEN ITERATE a;
        END IF;
        
        INSERT INTO admin(username,`password`) VALUES(CONCAT('xiaohua',i),'0000');
        
    END WHILE a;
END $

CALL test_while1(100)$

四、定义条件和处理程序

定义条件：是事先定义程序执行过程中遇到的问题。
处理程序：定义了在遇到这些问题时应当采取的处理方式。并且保证存储过程或函数在遇到警告或错误时能继续执行。这样增强存储程序处理问题的能力，避免程序异常停止运行。

4.1、定义条件

-- 语法：
DECLARE  condition_name CONDITION FOR (condition_type)

condition_name:表示条件名称
condition_type:表示条件类型
- SQLSTATE [VALUES] sqlstate_value
- mysql_error_code
sqlstate_value和mysql_error_code:表示MySQL的错误码
sqlstate_value为长度为5的字符串类型错误码 mysql_error_code为数值类型错误码

-- 案例：
ERROR 1142(42000)的sqlstate_value值为42000，mysql_error_code的值为1420
-- 定义"ERROR 1148(42000)" 错误 名称为test_conditon
-- 方式一：
DECLARE test_codition CONDITION FOR SQLSTATE 42000
-- 方式二：
DECLARE test_codition CONDITION FOR 1148

4.2、定义处理程序

-- 语法：
DECLARE  handler_type  HANDLER  FOR  condition_value[,....] sp_statement

handler_type:错误处理方式，3个取值

CONTINUE：遇到错误不处理继续执行
EXIT：遇到错误马上退出
UNDO：遇到错误之后撤回之前的操作 MySQL暂时不支持

condition_value:表示错误类型：

SQLSTATE [VALUE] sqlstate_value :包含5个字符的字符串错误值
condition_name：表示DECLARE CONDITION 定义的错误条件
SQLWARNING：匹配所有已01开头的SQLSTATE错误代码
NOT FOUND:匹配所有已02开头的SQLSTATE错误代码
SQLEXCEPTION：匹配所有没有被SQLWARNING 或者 NOT FOUND 捕获的SQLSTATE错误码
mysql_error_code：匹配数值类型错误码

-- 方式一：捕获sqlstate_value
DECLARE CONDITION HANDLER FOR SQLSTATE '42s20' SET @info='NO_SUCH_TABLE'
-- 方式二：捕获mysql_error_code
DECLARE CONDITION HANDLER FOR 1142 SET @info='NO_SUCH_TABLE'
-- 方式三：先定义条件，然后在调用
DECLARE no_such_table CONDITION FOR 1146
DECLARE no_such_table CONDITION FOR 1146

五、游标的使用

　　查询语句如果返回多条记录，数据量非常大，需要在存储过程和存储函数中使用光标来逐条读取查询结果中的记录。

-- 声明游标
DECLARE cursor_name CURSOR FOR select_statement;

cursor_name:游标的名称
select_statement:SELECT语句内容

-- 打开游标
OPEN cursor_name;

-- 使用游标
FETCH cursor_name INTO var_name[,var_name]....

-- 关闭游标
CLOSE cursor_name

-- 建表
CREATE TABLE `student` (
  `sid` VARCHAR(8) NOT NULL,
  `sname` VARCHAR(10) DEFAULT NULL,
  `sex` VARCHAR(2) DEFAULT NULL,
  `age` INT(11) DEFAULT NULL,
  `classno` VARCHAR(6) DEFAULT NULL,
  PRIMARY KEY (`sid`)
) CHARSET='utf8'
-- 表中的数据
INSERT INTO student VALUES
('20200101','张三','男','19','202001'),
('20200102','李四','女','20','202002'),
('20200103','王五','男','19','202003')

#创建存储过程
DELIMITER $
CREATE PROCEDURE proc1()
    BEGIN
        #定义变量
        DECLARE cur_sid VARCHAR(20);
        DECLARE cur_sname VARCHAR(20);
        DECLARE cur_sex VARCHAR(20);
        DECLARE cur_age VARCHAR(20);
        DECLARE cur_classno VARCHAR(20);
        #定义游标
        DECLARE student_cur1 CURSOR FOR     SELECT sid,sname,sex,age,classno FROM student;
        #打开游标
        OPEN student_cur1;
        #提取游标数据
        FETCH student_cur1 INTO cur_sid,cur_sname,cur_sex,cur_age,cur_classno;
        #输出游标数据
        SELECT cur_sid,cur_sname,cur_sex,cur_age,cur_classno;
        #关闭游标
        CLOSE student_cur1;
    END$
DELIMITER ;

# 执行存储过程
CALL proc1();

# 删除存储过程
DROP PROCEDURE IF EXISTS proc1

游标的循环遍历

#创建存储过程
DELIMITER $
CREATE PROCEDURE proc2()
    BEGIN
        #定义变量
        DECLARE cur_sid VARCHAR(20);
        DECLARE cur_sname VARCHAR(20);
        DECLARE cur_sex VARCHAR(20);
        DECLARE cur_age VARCHAR(20);
        DECLARE cur_classno VARCHAR(20);

        DECLARE SUM INT DEFAULT 0;
        DECLARE i INT DEFAULT 0;
        #定义游标
        DECLARE student_cur1 CURSOR FOR SELECT sid,sname,sex,age,classno FROM student;
        #打开游标
        OPEN student_cur1;
        # 循环遍历游标
        # 统计有多少条数据
        SELECT COUNT(sid) INTO SUM FROM student;
        WHILE i<SUM DO
                #提取游标数据
            FETCH student_cur1 INTO cur_sid,cur_sname,cur_sex,cur_age,cur_classno;
            #输出游标数据
            SELECT cur_sid,cur_sname,cur_sex,cur_age,cur_classno;
               SET i=i+1;
        END WHILE;
        #关闭游标
        CLOSE student_cur1;
END$
DELIMITER;

使用 loop 遍历游标

-- 使用 loop 遍历游标
DELIMITER $
CREATE PROCEDURE proc3()
    BEGIN
        DECLARE cur_sid VARCHAR(20);
        DECLARE cur_sname VARCHAR(20);
        DECLARE cur_sex VARCHAR(20);
        DECLARE cur_age VARCHAR(20);
        DECLARE cur_classno VARCHAR(20);
        
        DECLARE state INT DEFAULT FALSE; -- 定义表示用于判断游标是否溢出
        #定义游标
        DECLARE student_cur1 CURSOR FOR SELECT sid,sname,sex,age,classno FROM student;
        #打开游标
        OPEN student_cur1;
        -- 3、 loop 遍历游标
        cur_loop:LOOP -- 循环开始
        -- 循环开始的时候提取一次
            FETCH student_cur1 INTO cur_sid,cur_sname,cur_sex,cur_age,cur_classno;
            SELECT cur_sid,cur_sname,cur_sex,cur_age,cur_classno;
            IF state THEN
                LEAVE cur_loop; 
            END IF; 
        END LOOP; -- 循环结束
        -- 4、
        CLOSE student_cur1;
    END$
DELIMITER;
-- 执行存储过程
CALL proc3()

fetch是获取游标当前指向的数据行，并将指针指向下一行，当游标已经指向最后一行时继续执行会造成游标溢出。

使用loop循环游标时，他本身是不会监控是否到最后一条数据了，像下面代码这种写法，就会造成死循环；

read_loop:loop  
    fetch cur into n,c;  
    set total = total+c;  
end loop;

在MySql中，造成游标溢出时会引发mysql预定义的NOT FOUND错误，所以在上面使用下面的代码指定了当引发not found错误时定义一个continue 的事件，指定这个事件发生时修改done变量的值。

declare continue HANDLER for not found set done = true;  
# 也有这样写的
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' set done = true; 
# 所以在循环时加上了下面这句代码：

判断游标的循环是否结束

if done then  
    leave read_loop;    --跳出游标循环  
end if;  
# 如果done的值是true，就结束循环。继续执行下面的代码。

https://blog.csdn.net/xushouwei/article/details/52198065?utm_medium=distribute.pc_relevant.none-task-blog-title-2&spm=1001.2101.3001.4242

https://blog.csdn.net/qq_33157666/article/details/87877246?utm_medium=distribute.pc_relevant.none-task-blog-BlogCommendFromBaidu-2.control&depth_1-utm_source=distribute.pc_relevant.none-task-blog-BlogCommendFromBaidu-2.control

https://blog.csdn.net/yhl_jxy/article/details/52296546

https://www.cnblogs.com/weibanggang/p/9678312.html

posted @ 2020-11-23 15:25 jingdy 阅读(291) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

jingdy