第十五章-存储过程与函数+课后练习

最新推荐文章于 2023-02-27 11:44:46 发布
最新推荐文章于 2023-02-27 11:44:46 发布
阅读量708 收藏 2
点赞数 1
文章标签： mysql 数据库 database 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_45865571/article/details/125616633
版权

#创建数据库和表

CREATE DATABASE dbtest5;
USE dbtest5;
CREATE TABLE employees
AS
SELECT *FROM atguigudb.employees
CREATE TABLE departments
AS
SELECT *FROM atguigudb.departments;

#1.创建存储过程
#类型一 ： 无参数无返回值DELIMITER 指定字符为结束标记因为 存储过程体中要含有分号结尾，所以先设定其他的
#举例1：创建存储过程select_all_data() 查看 employees 表的所有数据

DELIMITER $
CREATE PROCEDURE select_all_date()
BEGIN    
     SELECT * FROM employees;
END $
DELIMITER ;

#2存储过程的调用CALL select_all_date();
#举例2：创建存储过avg_employee_salary()，返回所以员工的平均工资

DELIMITER $
CREATE PROCEDURE avg_employee_salary()
BEGIN    
     SELECT AVG(salary) FROM employees;
END $
DELIMITER ;

#调用
CALL avg_employee_salary();

#举例3：创建存储过程show_max_salary()，用来查看“emps”表的最高薪资值。

DELIMITER $
CREATE PROCEDURE show_max_salary()
BEGIN    
    SELECT MAX(salary) FROM employees;
END $
DELIMITER ;

#调用
CALL show_max_salary();

#类型二 ： 无参数有返回值（OUT）
#举例4：创建存储过程show_min_salary()，查看“emps”表的最低薪资值。并将最低薪资通过OUT参数“ms”输出

DELIMITER $
CREATE PROCEDURE show_min_salary(OUT ms DOUBLE)
BEGIN    
     SELECT MIN(salary) INTO ms    FROM employees;
END $
DELIMITER ;
#调用
CALL show_min_salary(@ms);
#查看变量值
SELECT @ms;

#类型三 有参数无返回值 （IN）
#举例5：创建存储过程show_someone_salary()，查看“emps”表的某个员工的薪资，并用IN参数empname输入员工姓名。

DELIMITER //
CREATE PROCEDURE show_someone_salary(IN empname VARCHAR(20))
BEGIN    
     SELECT salary FROM employees    WHERE last_name = empname;
END //
DELIMITER ;
#调用方式1
CALL show_someone_salary('Abel');
#调用方式2
SET @empname = 'Abel';
CALL show_someone_salary(@empname);

#类型四 有参数有返回值 （IN OUT）
#举例6：创建存储过程show_someone_salary2()，查看“emps”表的某个员工的薪资，
并用IN参数empname输入员工姓名，用OUT参数empsalary输出员工薪资。

DELIMITER //
CREATE PROCEDURE show_someone_salary2(IN empname VARCHAR(20),OUT empsalary DECIMAL(10,2))
BEGIN    
    SELECT salary INTO empsalary    
    FROM employees    
    WHERE last_name = empname;
END //
DELIMITER ;
#调用
SET @empname = 'Abel';
CALL show_someone_salary2('Abel',@empsalary);
SELECT @empsalary;

#类型五 INOUT
#举例7：创建存储过程show_mgr_name()，查询某个员工领导的姓名，并用INOUT参数“empname”输入员工姓名，输出领导的姓名。

DELIMITER //
CREATE PROCEDURE show_mgr_name(INOUT empname VARCHAR(25))
BEGIN    
     SELECT last_name INTO empname               
     FROM employees    
     WHERE employee_id = (                
             SELECT manager_id                
             FROM employees                
             WHERE last_name = empname                      
              );
END //
DELIMIT#aaDROP PROCEDURE show_mgr_name;
#调用
SET @empname = 'Abel';
CALL show_mgr_name(@empname);
SELECT @empname;

#2.1存储函数
#若在创建存储函数中报错“ you might want to use the less safe log_bin_trust_function_creators variable ”，
#有两种处理方法：
#方式1：加上必要的函数特性“[NOT] DETERMINISTIC”和“{CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA}”
#方式2：
SET GLOBAL log_bin_trust_function_creators = 1;
#举例1：创建存储函数，名称为email_by_name()，参数定义为空，该函数查询Abel的email，并返回，数据类型为字符串型。

DELIMITER //
CREATE FUNCTION email_by_name()RETURNS VARCHAR(25)
#需要加上一些条件    
DETERMINISTIC    
CONTAINS SQL    
READS SQL DATABEGIN    
RETURN(
       SELECT email 
       FROM employees 
       WHERE last_name = 'Abel');
END //
DELIMITER ;
#调用
SELECT email_by_name();
SELECT * FROM employees;
SELECT email,last_name
FROM employees
WHERE last_name = 'Abel';

#举例2：创建存储函数，名称为email_by_id()，参数传入emp_id，该函数查询emp_id的email，并返回，数据类型为字符串型。创建函数前执行此语句，保证函数的创建会成功

SET GLOBAL log_bin_trust_function_creators = 1;

DELIMITER //
CREATE FUNCTION email_by_id(emp_id INT)RETURNS VARCHAR(25)
BEGIN    
       RETURN(
              SELECT email 
              FROM employees 
              WHERE employee_id  = emp_id);
END //
DELIMITER ;
#调用
SELECT email_by_id(101);
SET @emp_id = 102;
SELECT email_by_id(@emp_id);

#举例3：创建存储函数count_by_id()，参数传入dept_id，该函数查询dept_id部门的员工人数，并返回，数据类型为整型。

DELIMITER //
CREATE FUNCTION count_by_id(dept_id INT)RETURNS INT
BEGIN
    RETURN(
         SELECT COUNT(*) 
         FROM employees 
         WHERE department_id = dept_id);
END //
DELIMITER ;
#调用
SET @dept_id  = 50;
SELECT count_by_id(@dept_id);

#对比 PROCEDURE 和 FUNCTION
#存储函数可以放在查询语句中使用，存储过程不行。
存储过程 PROCEDURE CALL存储过程() 理解为有0个或多个 一般用于更新存储函数 FUNCTION SELECT 函数() 只能是一个 一般用于查询结果为一个值并返回时

#3 存储过程和函数的查看
#3.1 使用 SHOW CREATE 语句查看存储过程和函数的创建信息

SHOW CREATE PROCEDURE show_mgr_name;
SHOW CREATE FUNCTION count_by_id;

#3.2 使用SHOW STATUS 语句查看存储过程和函数的状态信息会显示全部的内容，包含其他表
SHOW PROCEDURE STATUS;
#可以使用 LIKE 来获取自己想要输出的表

SHOW PROCEDURE STATUS LIKE 'show_%';
SHOW FUNCTION STATUS LIKE 'count_by%';

#3.3 从information_schema.Routines表中查看存储过程和函数的信息
#routine_type后必须为大写

SELECT * FROM information_schema.Routines
WHERE routine_name='email_by_id' 
AND routine_type = 'FUNCTION';

#4 存储过程和函数修改#修改存储过程或函数，不影响存储过程或函数功能，只是修改相关特性。使用ALTER语句实现。
#SQL SECURITY { DEFINER | INVOKER } ，指明谁有权限来执行。
#DEFINER ，表示只有定义者自己才能够执行。
#INVOKER ，表示调用者可以执行。
#COMMENT ‘string’ ，表示注释信息。

ALTER PROCEDURE show_max_salary
SQL SECURITY INVOKERCOMMENT '查询最高工资';

#4 存储过程和函数删除

DROP PROCEDURE IF EXISTS show_by_id();
DROP FUNCTION IF EXISTS count_by_id();

课后练习

存储过程

#0.准备工作

CREATE DATABASE test15_pro_func;USE test15_pro_func;

#1. 创建存储过程insert_user(),实现传入用户名和密码，插入到admin表中

CREATE TABLE ADMIN(
      id INT PRIMARY KEY AUTO_INCREMENT,
      user_name VARCHAR(15) NOT NULL,
      pwd VARCHAR(25) NOT NULL);

DELIMITER //
CREATE PROCEDURE insert_user(
        IN user_name VARCHAR(15),
        IN pwd VARCHAR(25))
BEGIN    
        INSERT INTO `admin`(user_name,pwd)
        VALUES(user_name,pwd);
END //
DELIMITER ;
#调用
CALL insert_user('Tom','ABC123');
SELECT * FROM ADMIN;

#2. 创建存储过程get_phone(),实现传入女神编号，返回女神姓名和女神电话

CREATE TABLE beauty(
         id INT PRIMARY KEY 
         AUTO_INCREMENT,
         NAME VARCHAR(15) NOT NULL,
         phone VARCHAR(15) UNIQUE,
         birth DATE);

INSERT INTO beauty(NAME,phone,birth)
VALUES 
('朱茵','13201233453','1982-02-12'),    
('孙燕姿','13501233653','1980-12-09'),   
('田馥甄','13651238755','1983-08-21'),    
('邓紫棋','17843283452','1991-11-12'),    
('刘若英','18635575464','1989-05-18'),    
('杨超越','13761238755','1994-05-11');

DELIMITER //
CREATE PROCEDURE get_phone(
        IN id INT,
        OUT NAME VARCHAR(15),
        OUT phone VARCHAR(15))
BEGIN    
        SELECT b.name,b.phone INTO NAME,phone    
        FROM beauty b    
        WHERE b.id = id;
END //
DELIMITER ;    
    
#调用
CALL get_phone(1,@name,@phone);
SELECT @name,@phone;    
SELECT * FROM beauty;

#3. 创建存储过程date_diff()，实现传入两个女神生日，返回日期间隔大小

DELIMITER //
CREATE PROCEDURE date_diff(
      IN date_u1 DATE,
      IN date_u2 DATE,
      OUT u1_u2 INT)
BEGIN    
      SELECT DATEDIFF(date_u1,date_u2) 
      INTO u1_u2;
END //
DELIMITER ;

DROP PROCEDURE date_diff;
#调用
SET @date_u1 =  '2001-04-21';
SET @date_u2 =  '2001-04-22';
CALL date_diff(@date_u2,@date_u1,@u1_u2);
SELECT @u1_u2;

#4. 创建存储过程format_date(),实现传入一个日期，格式化成xx年xx月xx日并返回

DELIMITER //
CREATE PROCEDURE fromat_date(
        IN date_u1 DATE, 
        OUT date_u2 VARCHAR(25))
BEGIN   
     SELECT DATE_FORMAT(date_u1,'%y年%m月%d日') 
     INTO date_u2;
END //
DELIMITER ;

#调用
SET @date_u1 = '2001-04-21';
CALL fromat_date(@date_u1,@date_u2);
SELECT @date_u2;

#5. 创建存储过程beauty_limit()，根据传入的起始索引和条目数，查询女神表的记录 #创建带inout模式参数的存储过程

DELIMITER //
CREATE PROCEDURE beauty_limit(
       IN index_1 INT,
       IN sum_1 INT)
BEGIN    
       SELECT * FROM beauty 
       LIMIT index_1,sum_1;
END //
DELIMITER ;

#调用
SET @index_1 = 2;
SET @sum_1 = 4;
CALL beauty_limit(@index_1,@sum_1);

#6. 传入a和b两个值，最终a和b都翻倍并返回

DELIMITER //
CREATE PROCEDURE b_a(
       INOUT a INT ,
       INOUT b INT)
BEGIN    
       SET a = a * 2;
       SET b = b * 2;
END //
DELIMITER ;

#调用
SET @a = 2;
SET @b = 4;
CALL b_a(@a,@b);
SELECT @a,@b;

#7. 删除题目的存储过程

DROP PROCEDURE IF EXISTS beauty_limit;

#8. 查看题目中存储过程的信息

SHOW CREATE PROCEDURE b_a;
SHOW PROCEDURE STATUS;
SHOW PROCEDURE STATUS LIKE 'b%';
SELECT * FROM information_schema.Routines
WHERE routine_name='b-a' 
AND routine_type = 'PROCEDURE';

#存储函数
#0. 准备工作

USE test15_pro_func;
CREATE TABLE employees
AS
SELECT *FROM atguigudb.`employees`;

CREATE TABLE departments
AS
SELECT *FROM atguigudb.`departments`;

#无参有返回
SET GLOBAL log_bin_trust_function_creators = 1;
#1. 创建函数get_count(),返回公司的员工个数 #有参有返回

DELIMITER //
CREATE FUNCTION get_count()RETURNS INT
BEGIN   
    RETURN(SELECT COUNT(*) FROM employees);
END //
DELIMITER ;

#调用
SELECT get_count();

#2. 创建函数ename_salary(),根据员工姓名，返回它的工资

DELIMITER //
CREATE FUNCTION ename_salary(lname VARCHAR(25))RETURNS DOUBLE
BEGIN
    RETURN(
          SELECT salary
          FROM employees 
          WHERE last_name = lname);
END //
DELIMITER ;

#调用
SET @lname = 'Abel';
SELECT ename_salary(@lname);

#3. 创建函数dept_sal() ,根据部门名，返回该部门的平均工资

DELIMITER //
CREATE FUNCTION dept_sal(dept_id INT)RETURNS DOUBLE
BEGIN
    RETURN(SELECT AVG(salary) 
    FROM employees 
    WHERE department_id = dept_id 
    GROUP BY department_id);
END //
DELIMITER ;

DROP FUNCTION dept_sal;

#调用
SET @dept_id = 50;
SELECT dept_sal(@dept_id);

#4. 创建函数add_float()，实现传入两个float，返回二者之和

DELIMITER //
CREATE FUNCTION add_float(
       float_1 FLOAT(10,2),
       float_2 FLOAT(10,2))
       RETURNS FLOAT(10,2)
BEGIN         
       RETURN(SELECT float_1 + float_2 );
END //
DELIMITER ;

DROP FUNCTION add_float;

#调用
SET @float_1 = 534654.4564654;
SET @float_2 = 231453.4687;
SELECT add_float(@float_1,@float_2);
花欲逢春露
关注
MySQL（8）存储过程和函数
weixin_45626468的博客
01-22 405
字段名 数据类型 主键 外键 非空 唯一 自增 id INT 是 否 是 是 否 name VARCHAR(50) 否 否 是 否 否 glass VARCHAR(50) 否 否 是 否 否 mysql> create table sch ( -> id int primary key not null unique, -> name varchar(50) not null, -&gt...
第八章指针课本例题+课后练习题
m0_56803509的博客
08-31 849
接下来两题的核心知识点是指针作为函数的参数时能够返回多个值 课本例题一： #include <stdio.h> void sum_diff(double op1,double op2,double *psum,double *pdiff); int main(){ double op1,op2,sum,diff; scanf("%d %d",&op1,&op2); sum_diff(op1,op2,&sum,&diff);//把sum和diff
存储过程与存储函数
m0_51351504的博客
05-05 187
一. 存储过程 1. 含义: 存储过程的英文是 Stored Procedure 。它的思想很简单，就是一组经过 预先编译 的 SQL 语句的封装。 2.执行过程： 存储过程预先存储在 MySQL 服务器上，需要执行的时候，客户端只需要向服务器端发出调用存储过程的命令，服务器端就可以把预先存储好的这一系列 SQL 语句全部执行。 1.1 创建存储过程 语法: CREATE PROCEDURE 存储过程名(IN|OUT|INOUT 参数名 参数类型,...) [char..
存储过程与存储函数.作业
weixin_46269481的博客
07-16 469
素材 CREATE TABLE `emp` ( `empno` int(4) NOT NULL, `ename` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `job` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `mgr` int(4) NULL DEFAULT NULL,
第五章、存储过程及函数作业
m0_55788811的博客
04-22 453
第五章、存储过程及函数作业第五章作业：习题六第八章作业：习题二 第五章作业：习题六 6、假没有下面两个关系模式: 职工(职工号，姓名，年龄，职务，工资，部门号)，其中职工号为主码； 部门(部门号，名称，经理名，电话)，其中部门号为主码。 用SOL语言定义这两个关系模式，要求在模式中完成以下完整性约束条件的定义: (1)定义每个模式的主码; (2) 定义参照完整性; (3) 定义职工年龄不得超过60岁。 建立职工表：(需提前建立部门表) DROP TABLE IF EXISTS STAFF; CREATE
第十五章 mysql存储过程与存储函数课后练习
m0_46914845的博客
08-09 1233
第十五章 mysql存储过程与存储函数课后练习
第15章_存储过程与存储函数的课后练习.sql
10-10
第15章_存储过程与存储函数的课后练习.sql
06-python章节练习-第六章-文件和数据格式化.doc
最新发布
06-11
15. **文件操作函数**： - `writeline()`不是Python的标准文件操作函数，应该是`write()`。 16. **文件打开模式组合**： - 'r', 'a', 和 'w'都是合法的模式，但单独的'+'不是一个完整的模式，必须与其它模式结合...
php典型模块与项目实战大全-明日科技 11-15章代码+其他内容
01-11
5. **第15章：PHP框架基础** - 框架概念：理解框架的目的和优势，如简化开发、提高代码复用。 - 常见PHP框架介绍：如Laravel、Symfony、CodeIgniter等。 - MVC模式：了解模型-视图-控制器架构，以及它如何改善...
Python第四章课后练习
m0_47897678的博客
11-01 2259
练习4-1：比萨想出至少三种你喜欢的比萨，将其名称存储在一个列表中，再使用for循环将每种比萨的名称打印出来。修改这个for循环，使其打印包含比萨名称的句子，而不仅仅是比萨的名称。对于每种比萨，都显示一行输出，下面是一个例子。 Ilikepepperonipizza. 在程序末尾添加一行代码，它不在for循环中，指出你有多喜欢比萨。输出应包含针对每种比萨的消息，还有一个总结性句子，下面是一个例子。 Ireallylovepizza! pizzas = ['a','b','c'...
存储过程练习
06-20
DECLARE @birSex bit IF @sex=’男’ SET @bitSex=1 ELSE SET @bitSex=0
MySQL存储过程 & 函数（简介 & 练习）
JohnnyG2000的博客
05-06 1247
MySQL存储过程 & 函数（简介 & 练习）1、存储过程1）存储过程简介2）存储过程特性3）存储过程使用说明4）存储过程的创建与调用5）查看存储过程6）删除存储过程7）存储过程使用示例①无参数②输入参数（IN）③输出参数（OUT）④输入参数 & 输出参数（IN & OUT）【同时使用】⑤输入输出参数（INOUT）2、函数1）函数简介2）函数的创建与调用3）函数创建说明4）查看存储函数5）修改存储函数6）删除存储函数7）函数使用示例①无参有返回值②有参有返回值3、存储过程 &
存储函数简单示例
weixin_46572329的博客
04-12 146
CREATE DEFINER=`root`@`localhost` FUNCTION `getAuditDeptFillStatus`(`taskId` varchar(2000),`deptId` varchar(2000),`aduitDeptId` varchar(2000)) RETURNS int READS SQL DATA BEGIN DECLARE quotaNum INTEGER; # 指标数量 DECLARE fillQuotaNum INTEGER;
存储过程和存储函数
qq_45383691的博客
04-19 306
存储过程和函数概述 存储过程和函数是事先经过编译存储在数据库中的一段SQL语句的集合，调用存储过程和函数可以简化应用开发人员的工作量，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的。 存储过程和和函数的区别在于，函数必须有返回值，而存储过程没有。 创建和调用存储过程 delimiter：用来声明分隔符 mysql> delimiter $ mysql> sel...
Mysql8.0习题系列（七）:存储函数（一篇学会存储函数，超详细）
阿里云专家博主、数据科学领域优质创作者、统计er在读
03-30 3157
Mysql8.0习题系列 软件下载地址 提取码：7v7u 数据下载地址 提取码：e6p9 文章目录Mysql8.0习题系列准备工作1. 创建函数get_count(),返回公司的员工个数2. 创建函数ename_salary(),根据员工姓名，返回它的工资3. 创建函数dept_sal() ,根据部门名，返回该部门的平均工资4. 创建函数add_float()，实现传入两个float，返回二者之和 mysql创建函数基本语法如下： delimiter $ create function 函数名(参数)
MySQL数据库--存储过程和函数
MINGZHEFENG的博客
06-22 221
存储过程和函数 存储过程 存储过程和函数：类似于方法， 好处: ​ 提高代码的重用性， 简化操作 ​ 减少了编译次数并且减少了和数据库服务器的连接次数，提高了效率 存储过程:一组预先编译号的sql语句的集合，理解成批处理语言 注意: ​ 参数列表包含三部分:参数模式，参数名，参数类型 ​ 参数模式: ​ in:该参数修饰的参数可以作为输入，需要传值 ​ out:该参数可以作为输出，也就是该参数可以作为返回值 ​ inout:该参数既可以作为输入，又可以作为输出，也就是既需传入，又可返回值。 ​ 若存储过程仅
Oracle学习——存储过程和存储函数普通用户调试授权
曦阳的博客
01-05 690
存储过程和存储函数普通用户调试授权问题。 打开命令行，查看是否在超级管理员下面 以scott用户为例 键入：grant debug connect session , debug any procedure to scott; 回车即可scott授权调试权限。 完成后，数据库连接必须从新连接才有效。 ...
MySQL基础-存储过程
weixin_64930209的博客
02-27 91
MySQL基础-存储过程
数据库的存储过程
weixin_45385056的博客
10-03 318
1.存储过程 1.概述：一条或多条SQl语句的集合 2.创建方法： CREATE PROCEDURE sp_name ([proc_parameter]) [characteristics....] routine_body 注释：CREATE PROCEDURE :创建存储函数的关键字 sp_name : 存储过程名称 proc_parameter:指定存储过程的参数列表...
C++ Primer课后习题第1-18章解答概览：从基础到高级
12. **第十五章面向对象编程** - 深入探讨封装、继承和多态等面向对象的核心特性。 13. **第十六章选做习题** - 提供了额外的挑战题目，旨在增强学生的独立思考和解决问题的能力。 14. **第十七章与第十八章** ...
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值