存储过程和函数

存储过程和函数

存储过程和函数：类似于java中的方法
存储过程
含义：一组预先编译好的sql语句的集合，理解成批处理语句
好处：
    1. 提高代码的重用性
    2. 简化操作
    3. 减少了编译次数并且减少了和数据库服务器的连接次数，提高效率

创建语法

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

--注意
-- 1. 参数列表包含三部分
-- 参数模式 参数名 参数类型

-- 举例
IN stuname VARCHAR(20)

--参数模式
IN:     该参数可以作为输入 
OUT：   该参数可以作为输出，（作为返回值）
INOUT:  该参数既可以作为输入，也可以返回值

-- 2. 存储过程体仅仅只有一句话的时候，BEGIN END 可以省略
-- 存储过程体中的每条sql语句的结尾都要求必须加分号
-- 存储过程的结尾可以使用DELIMITER重新设置
-- 语法：
    DELIMITER 结束标记
-- 案例
DELIMITER $

调用语法

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

案例

插入到admin表中的五条记录

DELIMITER $    --这里声明以 $ 结尾

CREATE PROCEDURE test()
BEGIN
    INSERT INTO b_user(`name`,`password`)
    VALUES('N1','P1'),('N2','P2'),('N3','P3'),('N4','P4'),('N5','P5'),('N6','P6');
END $

#调用
CALL test() $

创建带in模式的存储过程

根据学生姓名，查询所在的班级信息

DELIMITER $

CREATE PROCEDURE test2(IN `in_name` VARCHAR(20))
BEGIN
    SELECT *
    FROM user
    WHERE `name` = in_name;
END $

CREATE PROCEDURE login(IN `name` VARCHAR(20), IN `password` VARCHAR(20))
BEGIN
    DECLARE result VARCHAR(20) DEFAULT '';#声明并初始化

    SELECT COUNT(*) INTO result #赋值
    FROM b_user u
    WHERE u.name = `name` AND u.password = `password`;

    SELECT result;
END $

#显示登录成功或失败
CREATE PROCEDURE login2(IN `name` VARCHAR(20), IN `password` VARCHAR(20))
BEGIN
    DECLARE result INT DEFAULT 0;#声明并初始化

    SELECT COUNT(*) INTO result #赋值
    FROM b_user u
    WHERE u.name = `name` AND u.password = `password`;

    SELECT IF(result>0, '登录成功', '登录失败') 登录状态;
END $

创建带out模式的存储过程

--根据文章的title获得文章的类型  文章的类型在b_category表中
DELIMITER $

CREATE PROCEDURE `get_category`(in `title` varchar(20), out category varchar(20))
begin
	SELECT `name` into category
	from b_category
	WHERE id = (SELECT b_article.c_id from b_article WHERE b_article.title = title);
END$

#调用
call get_category('go闭包', @category)$
SELECT @category$



-- 根据学生姓名获得班级的名称和年级
CREATE PROCEDURE `get_info`(IN stu_name VARCHAR(20), OUT class_grade VARCHAR(20), OUT class_name VARCHAR(20))
BEGIN
    SELECT class.grade, class.name INTO class_grade, class_name
    FROM class
    WHERE class.id = (
        SELECT class_id
        FROM stu
        WHERE stu.name = stu_name
    );
END$

CALL get_info('张三', @class_grade, @class_name)$
SELECT @class_grade, @class_name$

创建带inout模式参数的存储过程

--传入a和b两个值，最终a和b都翻倍返回
DELIMITER $

CREATE PROCEDURE test3(INOUT A INT, INOUT B INT)
BEGIN
    SET A = A*2;
    SET B = B*2;
END$

SET @A = 1$
SET @B = 2$
CALL test3(@A, @B)$
select @A, @B$

删除存储过程

#一次只能删一条
DROP PROCEDURE test1;

查看存储过程的信息

SHOW CREATE PROCEDURE test2;

不能够修改存储过程中的逻辑语句

函数

存储过程和函数：类似于java中的方法
函数
含义：一组预先编译好的sql语句的集合，理解成批处理语句
好处：
    1. 提高代码的重用性
    2. 简化操作
    3. 减少了编译次数并且减少了和数据库服务器的连接次数，提高效率

<!-- 与存储过程的区别 -->
存储过程: 可以有0个或者多个返回, 适合做批量插入,批量更新  增删改
函数:有且仅由一个返回, 适合做处理数据后返回一个结果

创建语法

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

#调用
SELECT 函数名(参数列表);

案例

无参数有返回、

DELIMITER $

CREATE FUNCTION test1() RETURNS INT
BEGIN
        DECLARE c INT DEFAULT 0;
        SELECT COUNT(*) INTO c
        FROM stu;
        RETURN c;
END

select test1();

有参数，有返回值

-- 根据学生姓名返回手机号
CREATE FUNCTION test2(stu_name VARCHAR(20)) RETURNS VARCHAR(11)
BEGIN
    SET @phone = '';
    SELECT stu.phone INTO @phone
    from stu
    WHERE `name` = stu_name;
    RETURN @stu_name;
END

SELECT test('xk')$

流程控制语句

SELECT IF(1>2, 2, 3);

CASE --参考之前写过 的

CREATE PROCEDURE test_case(IN score INT)
BEGIN
        CASE
        WHILE score >= 90 AND score<=100 THEN select 'A';
        WHILE score >= 80 THEN SELECT 'B';
        WHILE score >= 60 THEN SELECT 'C';
        ELSE SELECT 'D';
        END CASE;
END $



--if结构
CREATE FUNCTION test_if(score INT) RETURNS CHAR
BEGIN
        IF score >= 90 AND score <= 100 THEN RETURN 'A';
        ELSEIF score >= 80 THEN RETURN 'B';
        ELSEIF score >= 60 THEN RETURN 'C';
        ELSE RETURN 'D';
        END IF;
END $

-- 循环结构

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

--while

lebel:while loop_condition
do
    loop_list
end while label;


-- repeat   do wile
label:repeat
    loop_list
until end_condition
end repeat label;


-- loop  死循环

label:loop
    loop_list
end loop label;



####经典案例
--已知表stringcontent
--字段  id 自增
--content varchar(20)
--向该表中插入指定个数的随机字符串
DELIMITER $

CREATE PROCEDURE test_randstr_insert(IN num INT)
BEGIN
        DECLARE i INT DEFAULT 1;#定义一个变量i表示插入次数
        DECLARE str VARCHAR(26) DEFAULT 'abcdefghijklmnopqrstuvwxyz';
        DECLARE startIndex INT DEFAULT 1; #字符串起始索引
        DECLARE len INT DEFAULT 1;#截取字符串的长度

        WHILE i <= num 
        DO
            SET len = FLOOR(RAND() * (20 - startIndex + 1) +1)；#产生一个随机的整数，代表截取长度
            SET startIndex = FLOOR(RAND() * 26 + 1);#产生一个随机整数，代表起始索引1-26
            INSERT INTO stringcontent(content) VALUES(SUBSTR(str, startIndex, len));
            SET i = i + 1;
        END WHILE:
END $