SQL（10） 命令行的存储过程和函数

第一部分的内容：

SQL（2）DQL语言 条件查询+排序查询+常见函数+分组查询+连接查询

https://blog.csdn.net/Lee_Yu_Rui/article/details/107527594

SQL（3）DQL语言 子查询+分页查询+联合查询

https://blog.csdn.net/Lee_Yu_Rui/article/details/107566293

SQL（4）DML语言 增删改

https://blog.csdn.net/Lee_Yu_Rui/article/details/107584843

SQL（5） DDL语言 库和表的创建，修改和删除

https://blog.csdn.net/Lee_Yu_Rui/article/details/107598414

SQL（6） 数据的常见约束和标识项

https://blog.csdn.net/Lee_Yu_Rui/article/details/107603411

SQL（7）TCL 事务控制语言 特点+演示+隔离级别+save point

https://blog.csdn.net/Lee_Yu_Rui/article/details/107603412

SQL（8）视图的创建+修改+删除+更新

https://blog.csdn.net/Lee_Yu_Rui/article/details/107603444

SQL（9）变量：系统变量和自定义变量

https://blog.csdn.net/Lee_Yu_Rui/article/details/107619291

感谢以下链接的教学分享，很好的视频和资源，笔记记录在这里，便于日后查看

新i版-链接: https://pan.baidu.com/s/1GDhRzV_3lUQaETsH4CM8zQ 提取码: rpte
 

存储过程和函数

/*
(需要在命令行操作 >mysql -uroot -p7539510 )
好处：
    提高代码重用性
    简化操作
*/

存储过程

/*
一组预先编译好的sql语句的集合，理解成批处理语句
3. 减少了编译次数和数据库服务器的连结次数，提高了效率
*/

 一 创建语法

CREATE PROCEDURE 存储过程名(参数列表)
BEGIN
    存储过程体
END
注意：
1. 参数列表包含三部分
    参数模式   参数名   参数类型
    举例
    IN stuname vachar(20)

    模式:
    IN:该参数可以作为输入，也就是该参数需要调用方传入值
    OUT：该参数可以作为返回值
    INOUT：都可
2.加入存储过程体只有一句话，BEGIN END 可以省略
3.存储过程体的每条语句都要加分号，存储过程结果可以使用DELIMITER 重新设置
语法： DELIMITER 结束标记（自己设定）

二 调用语法

CALL 存储过程名（实参列表）;

#1.空参列表(需要在命令行操作 >mysql -uroot -p7539510 )
#① `girls`案例 插入到admin表中五条记录
SELECT * FROM `admin`

DELIMITER $
CREATE PROCEDURE myp1()
BEGIN
    INSERT INTO admin(username,`password`) 
    VALUES('john1','000'),('lily','000'),('rose','000'),('jack','000'),('tom','000');
END $

CALL myp1()$

#2.创建in模式的参数存储过程
#① 创建存储过程实现根据女生名 查询对应的男神信息
SELECT * FROM `admin`

DELIMITER $
CREATE PROCEDURE myp2(IN beatuyName VARCHAR(20))
BEGIN
    SELECT bo.*
    FROM boys bo
    RIGHT JOIN beauty b ON bo.id = b.`boyfriend_id`
    WHERE b.`name` = beatuyName;
END $

CALL myp2('柳岩')$

#② 创建存储过程实现用户是否登陆成功

DELIMITER $
CREATE PROCEDURE myp5(IN username VARCHAR(20),IN PASSWORD VARCHAR(20))
BEGIN
    DECLARE result INT DEFAULT 0;
    
    
    SELECT COUNT(*) INTO result
    FROM `admin` a
        WHERE a.username = username
        AND a.PASSWORD = PASSWORD;
        
        SELECT IF(result>0,'登陆成功','登陆失败');
END $

CALL myp5('john','6666')$
SELECT * FROM `admin`

3.创建out模式的参数存储过程
#案例1 根据女神名返回男神名
DELIMITER $
CREATE PROCEDURE myp6(IN beatuyName VARCHAR(20),OUT boyName VARCHAR(20))
BEGIN

    SELECT bo.boyName INTO boyName
    FROM `boys` bo
    LEFT JOIN `beauty` b
    ON b.`boyfriend_id` = bo.`id`
    WHERE b.`name` = beatuyName;

END $
#定义用户变量
SET @bName$
CALL myp6('小昭',@bName)$
#显示变量
SELECT @bName$

#案例2 根据女神名返回男神名 和魅力值
CREATE PROCEDURE myp7(IN beatuyName VARCHAR(20),OUT boyName VARCHAR(20), OUT usercp INT)
BEGIN

    SELECT bo.boyName ,bo.`userCP`INTO boyName,usercp
    FROM `boys` bo
    LEFT JOIN `beauty` b
    ON b.`boyfriend_id` = bo.`id`
    WHERE b.`name` = beatuyName;

END $

CALL myp7('小昭',@bName,@ucp)$
SELECT @bName,@ucp$

#4.创建Inout模式的存储过程
# 案例1 传入a,b两个值，并翻倍返回

CREATE PROCEDURE myp8(INOUT a INT,INOUT b INT)
BEGIN

    #select 2*a,2*b into a,b;
    SET a = a*2;
    SET b = 2*b;

END $
#调用
SET @num1 = 10$
SET @num2 = 22$
CALL myp8(@num1,@num2)$

 

三.删除存储过程

DROP PROCEDURE 存储过程名 （一次只能删除一个）

DROP PROCEDURE myp1

四.查看存储过程

SHOW CREATE PROCEDURE myp2;

函数

/*
和存储过程的区别
    存储过程：可以0个或者多个返回，适合做批量插入，更新
    函    数：只能一个返回，适合处理数据后返回一个结果
*/

一. 函数创建语法

CREATE FUNCTION 函数名(参数列表) RETURNS 返回类型
BEGIN
    函数体
END 
/*
注意：
参数列表 ：参数名 参数类型
函数体：肯定有return语句  return 值
*/

二 调用语法

SELECT 函数名（参数列表）

# 案例1.无参有返回  返回公司员工个数
CREATE FUNCTION myf1() RETURNS INT
BEGIN  
    DECLARE result INT DEFAULT 0;
    SELECT COUNT(*) INTO result
    FROM `employees`;
    RETURN result;
    
END $
SELECT myf1()$

# 案例2.有参有返回 根据员工名，返回它的工资
CREATE FUNCTION myf2(empname VARCHAR(20)) RETURNS DOUBLE
BEGIN  
    DECLARE salary DOUBLE DEFAULT 0.0;
    SELECT e.`salary` INTO salary
    FROM `employees` e
    WHERE e.`last_name` = empname;    
    RETURN salary;
END $
SELECT myf2('Kochhar')$

# 案例3.有参有返回 根据部门号，返回该部门的平均工资
CREATE FUNCTION myf3(deptname VARCHAR(20)) RETURNS DOUBLE
BEGIN  
    DECLARE avg_salary DOUBLE DEFAULT 0.0;
    SELECT AVG(e.`salary`) INTO avg_salary
    FROM `employees` e
    INNER JOIN `departments` d
    ON e.`department_id` = d.`department_id`
    WHERE d.`department_name` = deptname;
    RETURN avg_salary;
END $
SELECT myf3('Shi')$

三.删除函数

DROP FUNCTION 函数过程名 （一次只能删除一个）

DROP FUNCTION myf3

四.查看函数

SHOW CREATE FUNCTION myf3;