项目七 存储过程与函数

任务1 查看存储过程与函数

一、存储过程与函数简介

存储过程的用途十分广泛，任何使用SQL 语句的场合都可以编译成存储过程，例如：

1.向用户返回查询数据

2.向表中插入或修改数据

3.在单个存储过程中执行一系列SQL语句，完成一个完整性功能

二、存储过程与函数优点

存储过程和函数的优点

（1）执行效率高，存储过程在服务器创建时是经过预编译的，执行时不必再次进行编译，因而执行速度快。

（2）模块化使用。将复杂的工作程序写成存储过程或函数，以后可通过不同的参数或不带参数重复调用，大大方便了用户的使用。

（3）降低网络流量。存储过程和函数是存储在服务器端并在服务器端执行，调用执行时用一个带参数的存储过程或函数名，代替所包含的大量SQL语句的传输，大大降低了网络流量。

（4）提高安全性。当数据表需要保密时，可以利用存储过程或函数作为数据存储取和访问的管道，来控制用户对数据库信息访问的权限。

三、查看存储过程与函数

MYSQL系统的存储过程与函数建立后的信息都存储在系统数据库的information_schema的routines表中

1.查看详细信息

第一步：建立一个简单的无参数存储过程

delimiter //  #改变mysql delimiter为 // ，即改变命令提交执行的标志符号，默认情况下，delimiter是分号  ；
CREATEPROCEDURE p_goods_sel()
BEGIN
SELECT*FROM goods;
END //
delimiter;#改回默认的mysql delimiter  ；

第二步：通过information_schema.routines查看存储过程或函数信息。

SELECT*FROM information_shcema.routinesWHERE ROUTINE_NAME LIKE'%GOODS%';

2.查看存储过程与函数状态

SHOW ... SELECT 语句用于查看存储过程或函数状态。 执行HELP SHOW 查看帮助文档

①查看名称含有“search”字符串的存储过程状态信息

SHOWPROCEDURESTATUSLIKE'%search%';

②查看名称含有“format”字符串的函数状态信息

SHOWFUNCTIONSTATUSLIKE'%format%';

③查看名称含有“item”的函数状态信息

SHOWFUNCTIONSTATUSLIKE'%item%';

3.查询存储过程与函数定义

①查看db_shop数据库中存储过程p_goods_sel的定义

SHOWCREATEPROCEDURE p_goods_sel;

②查看db_shop数据库中存储函数item_count的定义

SHOWCREATEFUNCTION item_count;

四、任务实施

(1)选择db_shop数据库

USE db_shop;

(2)通过information_schema.routines查看含有“goods”字符串的存储过程或函数信息

SELECT*FROM information_schema.routinesWHERE ROUTINE_NAME LIKE'%goods%';

(3)查看名称含有“search”字符串的存储过程状态信息

SHOWPROCEDURESTATUSLIKE'%search%';

(4)查看名称含有“format”字符串的函数状态信息

SHOWFUNCTIONSTATUSLIKE'%format%';

任务2 创建和调用存储过程

一、存储过程创建语句

语句体以BEGIN...END括住，每个语句都要用分号“ ; ”结尾

由于存储过程内部语句要以分号结束，因此在定义存储过程前，需要用delimiter关键字定义其他字符来作为语句结束，命令发送执行符号。

二、存储过程调用语句

mysql使用CALL命令来调用执行一个已定义的存储过程

语法：

CALL sp_name([parameter[....]])
CALL sp_name[()]

说明：sp_name是调用的存储过程名；parameter是传递参数的列表，参数为空时调用可以不带括号。

三、创建不带参数的存储过程

不带参数的存储过程一般用于执行固定不变的检索、统计等模块操作，不带参数的存储过程没有带参数的存储过程灵活

在数据库db_shop中定义一个存储过程，能够查看各部门员工一览表并调用该存储过程

USE db_shop;

delimiter //   #改变mysql delimiter为// 即改变命令提交执行的标志符号
CREATEPROCEDURE p_dept_staff()
BEGIN
SELECT dept_name,staff_name,sex,birthday,phone,salary 
FROM staffer
INNERJOIN department ON staffer.dept_id=department.id;
END //
delimiter;#改回默认值的 ;

CALL p_dept_staff();

四、创建带输入（in）参数的存储过程

①在数据库db_shop中建立一个存储过程，能够通过员工编号检索员工信息并调用该存储过程

USE db_shop;
delimiter //
CREATE PROCEDURE p_staferSearch(IN sid INT)
BEGIN
SELECT * FROM staffer WHERE id=sid;
END //
delimiter ;

CALL p_staferSearch(6);

②建立一个存储过程，完成部门记录的添加

分析：将添加的字段值全部以输入参数传入。

delimiter //
CREATEPROCEDURE p_dept_in(d_name varchar(20),d_phone char(13),d_memo varchar(100))
BEGIN
INSERTINTO department(dept_name,dept_phone,dept_memo)VALUES(d_name,d_phone,d_memo);
END //
delimiter;

CALL p_dept_in('防损部','020-38490126',NULL);

五、创建带输入（IN）参数和输出（OUT）参数的存储过程

若需要从存储过程中返回一个或多个检索或统计的值，则可以使用带OUT关键字定义的输出参数，将返回值传回调用环境。

在数据库db_shop中建立一个存储过程，能够通过部门编号统计该部门员工人数，返回统计值，并调用该存储过程。

分析：部门编号是输入参数，统计的员工人数是输出参数

delimiter //
CREATEPROCEDURE p_count(IN id INT,OUT n INT)
BEGIN
SELECTCOUNT(*)AS n FROM staffer WHERE dept_id=id;
END //
delimiter;

CALL p_count(1,@a)

SELECT@aAS'1号部门员工数';

六、创建带输入输出（INOUT）参数的存储过程

在存储过程中，即需要从外部将参数值传入到存储过程内执行，又需要在存储过程中改变参数值后由存储过程传出。对于这样的参数，在MYSQL中可以使用INOUT关键字将其定义为输入输出参数

统计销售订单中某个商品销售额按某个比例促销的费用

分析：订单号作为输入参数，可以将促销比例和促销费用使用同一个参数作为输入输出参数，以节省内存开销。

DELIMITER //
CREATEPROCEDURE item_promotion(IN id INT,INOUT x FLOAT)
BEGIN
DECLARE s FLOAT;
SELECTSUM(total_price)INTO s FROM item WHERE goods_id=id;
SET x=s*x;
END //
DELIMITER;

SET@x=0.1;

CALL item_promotion(1,@x);

SELECT @x;

七、任务实施

(1)选择db_shop数据库

USE db_shop;

(2)建立按部门名称查看部门信息的存储过程，并调用测试。

delimiter //  --改变MySQL delimiter为“//”
CREATEPROCEDURE p_dept(IN dname VARCHAR(20))
BEGIN
SELECT*FROM department WHERE dept_name=dname;
END //
delimiter;  --改回默认的“;”
CALL p_dept('销售部');

(3)建立一个存储过程，可实现按部门编号修改所有部门信息。

USE db_shop；
delimiter //
CREATEPROCEDURE p_dept_up(d_id int,d_name varchar(20),d_phone char(13),d_memo varchar(100))
BEGIN
UPDATE department
SET dept_name=d_name,dept_phome=d_phone,dept_memo=d_mome
WHERE id=d_id;
END //
delimiter;
CALL p_dept_up(6,'外联部','13250897234','对外联系')

(4)建立一个存储过程，可按订购号查看其订购额，把订购额通过输出参数输出并调用。

DELIMITER //
CREATEPROCEDURE p_orders(IN Ord_id INT,OUT x FLOAT)
BEGIN
SELECT amout_money INTO x FROM orders WHERE id=Ord_id;
END//
DELIMITER;
CALL item_promotion(1,@x);
SELECT @x;

任务3 创建和调用函数

一、函数创建语句

二、函数调用语句

函数定义好后，就可以像其他语言一样作为表达式调用

语法：

赋值：

SET @var_name=func_name(parameter[....]);

查询显示：

SELECT func_name(parameter[...]);

三、应用举例

1.编写一个函数，用来统计订单详细表的商品销售笔数

分析：创建函数时，MYSQL默认开启了bin-log，因含有SQL语句会出错，则需要参数log_bin_trust_function_creators 进行设置。

set global log_bin_trust_function_creators=1; 
USE db_shop;
DELIMITER //
CREATE FUNCTION item_count(id INT)
RETURNS INT #指定函数返回值类型，注意RETURNS带S
BEGIN 
DECLARE n int; #声明变量
SELECT COUNT(*) INTO n FROM item WHERE goods_id=id;
RETURN n #注意RETURN不带S
END //
DELIMITER ;
SET @n=item_count(1);
SELECT @n;

注意代码需要分开执行。

2.编写一个函数，可按职员编号查询员工姓名。

USE db_shop;
set global log_bin_trust_function_creators=1; 
DELIMITER //
CREATE FUNCTION staffer_search(sid INT)
RETURNS VARCHAR(10)
BEGIN
DECLARE sname VARCHAR(10);
SELECT staff_name INTO sname FROM staffer WHERE id=sid;
IF ISNULL(sname) THEN
RETURN'无人';
ELSE 
RETURN sname;
END IF;
END //
DELIMITER ;
SET @sname=staffer_search(6);
SELECT @sname,staffer_search(4);

四、任务实施

(1)选择db_shop数据库

USE db_shop;

(2)建立一个函数，可按订购号查看其订购额、返回订购额，并调用测试

DELIMITER //
CREATEFUNCTION f_orders(Ord_id INT)
RETURNSINT
BEGIN
DECLARE x INT;
SELECT amount_money INTO x FROM orders WHERE id=Ord_id;
RETURN x;
END //
DELIMITER;
SET@x=f_orders(1);
SELECT@x;

(3)建立一个函数，可按部门名称和员工姓名设置参数、查找返回员工电话，并调用测试

DELIMITER //
CREATEFUNCTION f_staff_phone(dname VARCHAR(20),sname VARCHAR(10))
RETURNSINT
BEGIN
DECLARE ph CHAR(11);
SELECT phone INTO ph FROM staffer
INNERJOIN department ON department.id=staffer.dept_id
WHERE dept_name=dname AND staff_name=sname;
RETURN ph ;
END //
DELIMITER;
SET@phone=p_orders('销售部','张一楠');
SELECT@phone;

任务4 修改和删除存储过程与函数

一、修改存储过程与函数

MYSQL中修改过程与函数主要是修改存储过程与函数特性。ALTER PROCEDURE语句用来修改存储过程的特性，ALTER FUNCTION 语句用来修改函数的特性。

1.将读写权限改为MODIFIES SQL DATA，并指明调用者可以执行

USE db_shop;
ALTER PROCEDURE p_staferSearch
MODIFIES SQL DATA
SQL SECURITY INVOKER;
说明：可以通过information_schema.routines查看修改结果
SELECT * FROM information_schema.routines
WHERE ROUNTINE_NAME LIKE '%search%'

2.将函数staffer_search的读写权限改为READS SQL DATA,并加上注释信息'FIND Staffer'

USE db_shop;
ALTER FUNCTION staffer_search
READS SQL DATA
COMMENT 'FINF Staffer';
说明：可以通过information_schema.routines查看修改结果
SELECT * FROM information_schema.routines
WHERE ROUNTINE_NAME LIKE '%search%'

二、删除存储过程与函数

DROP PROCEDURE 语句用来删除存储过程，DROP FUNCTION 语句用来删除函数

1.删除存储过程p_goods2

分析：为了能看到删除存储过程的效果，可以先查看这个存储过程是否存在，再执行删除语句

USE db_shop;
SHOW PROCEDURE STATUS LIKE'%goods%';
DROP PROCEDURE p_goods2;

说明：删除存储过程后，可以使用查看存储过程状态语句SHOW PROCEDURE LIKE '%goods%'查看存储过程p_goods2是否存在。

2.删除db_shop数据库中的函数staffer_search

USE db_shop;
SHOW FUNCTION STATUS LIKE '%search%';
DROP FUNCTION staffer_search;

三、任务实施

任务 2 应用MYSQL程序设计

一、常量

1.字符串常量

2.数值常量

数值常量不需要用引号括住。

Int 常量：由不含小数点的数字0~9组成，例如2009

decimal常量：可以是带小数点的数值，例如 123.6

float常量和real常量：可以用小数点方式和科学技术法表示，如28.9、123E6

3.日期时间常量

日期时间常量是以MySQL可识别的格式表示的常量，用单引号括住，如:'2009-08-09'

二、变量

变量用于存储临时数据，构成表达式最基本的存储单元

1.mysql变量的类型

MySQL变量又包括局部变量、用户变量和系统变量(系统变量又分全局变量和会话变量)。

(1)局部变量：没有前缀，一般用于存储过程或函数中，作用域仅局限于定义它的语句块。在语句块执行完毕后，局部变量就会被释放。存储过程或函数的参数也属于局部变量。局部变量需要先用DECLARE声明定义，再使用。

(2)用户变量:带有前缀@,只能被定义它的用户使用,作用于当前用户整个连接，当前连接一旦断开，所定义的用户变量全部被释放。用户变量的作用域比局部变量要广。用户变量不用声明定义，可直接使用。

(3)系统变量：MySQL内置了许多系统变量，是由系统定义的，变量名称带有前缀@@,包含全局变量和会话变量。

全局变量：影响整个服务器的参数，在MySQL启动时由服务器的my. ini自动初始化默认值。用户不能定义全局变量，但可以通过my. ini文件来修改。修改全局变量，必须具有SUPER权限。

会话变量： 每当建立一个新连接时，由MySQL服务器将当前所有全局变量值复制一份给会话变量完成初始化，它只影响当前用户的数据库连接。设置会话变量不需要特殊权限，但客户端只能更改自己的会话变量，不能更改其他客户端的会话变量。会话变量的作用域与用户变量一样，仅限于当前连接，当前连接断开后，其设置的所有会话变量均失效。

2.局部变量的定义

说明：定义局部变量，说明局部变量的存储值类型

3.变量赋值语句

SET 或者 SELECT 语句用来给变量赋值

4.变量应用举例

(1)局部变量：一般在存储过程或函数中使用

编写一个带参数的存储过程，用于查找统计按商品编号和达到某一数量的详细订单笔数

分析：参数也是局部变量，只不过参数定义时不需要DECLARE说明

DELIMITER //
CREATEPROCEDURE item_n(IN id INT,IN s INT)
BEGIN
DECLARE n INT;
SET n=0;
SELECTCOUNT(*)INTO n FROM item WHERE goods_id AND quantity>s;
SELECT id,s,n;
END // 
DELIMITER;
CALL item_n(1,2);

(2)用户变量：不用声明定义，可直接使用

通过用户变量的值查找统计相应商品达到销售数量条件的详细订单笔数

USE db_shop;
SET@id=1,@s=2;
SELECTCOUNT(*)INTO@nFROM item WHERE goods_id=@idand quantity>@s;
SELECT@id,@s,@n;

(3)系统变量：利用系统内置的全局变量直接获取值。

①查看全局变量@@global.sort_buffer_size

分析：使用SELECT语句或者SHOW GLOBAL VARIABLES语句进行查看

这两种语句执行如下

SELECT@@global.sort_buffer_size;
SHOWGLOBAL VARIABKES like"%.sort_buffer%";

②查看会话变量@@error_count和@@sort_buffer_size的值

分析：会话变量的域标签为session，如果不带这个标签，也会默认为会话变量，则可以使用SELECT或SHOW SESSION VARIABLES

SELECT@@error_count,@@session.error_count;
SELECT @@sort_buffer_size,@@session.sort_size;
SHOW SESSION VARIABLES LIKE '%sort_buffer%';

三、运算符

运算符用于基本的算数运算、字符运算、比较运算、逻辑运算等各种运算。

四、函数

1.数值函数

在数据库db_shop中查询员工的薪水，按四舍五入保留两位小数显示。

USE db_shop;
SELECT dept_id,staff_name,ROUND(salary,2) FROM staffer;

2.字符串函数

在db_shop数据库中查询电话前三位为“135”的员工信息。

USE db_shop;
SELECT * FROM staffer WHERE SUBSTRING(phone,1,3)='135';

3.日期与时间函数

在db_shop数据库中显示出各个顾客的姓名和年龄信息。

USE db_shop;
SELECT customer_name,TIMESTAMPDIFF(YEAR,birthday,CURDATE())AS 年龄 FROM customer;

4.条件判断函数

在db_shop数据库中，按顾客消费额显示顾客姓名和级别：消费额大于6000级别为“VIP”，否则级别为“General”。

USE db_shop;
SELECT customer_name AS 姓名, IF(consumption_amount>=6000,"VIP","GENERAL") AS 级别 FROM customer;

在db_shop数据库中，如果顾客的hobby不为NULL，则显示其值，否则显示0

USE db_shop;
SELECT customer_name,IFNULL(hobby,0)FROM customer;

5.JSON函数

在db_shop数据库中为customer表添加2个顾客记录，地址（JSON类型）采用JSON对象的键值对方式添加，对比语法的区别应用

USE db_shop;
INSERTINTO customer(username,password,customer_name,address)VALUES('zhangs','123','张三','{"city":"广州","road":"天河北","room":5}');
​
INSERTINTO customer(username,password,customer_name,address)VALUES('LiS','123','李四',JSON_OBJECT("city","广州","road","林和街","room",10));

6.其他函数

五、IF语句

1.IF 语句

2.IF语句的嵌套应用

六、CASE语句

七、循环语句

八、异常处理

1.MySQL出现的错误信息

2.异常处理方式

3.异常处理语句

4.自定义异常

5.抛出异常

九、游标

在存储过程和函数中，查询语句可能返回多条记录，使用游标可以实现逐条读取结果集中的记录。

游标的使用分为四个步骤：声明游标、打开游标、使用游标和关闭游标。

十、任务实施

课后习题

---

/***
 *             ,%%%%%%%%,
 *           ,%%/\%%%%/\%%
 *          ,%%%\c "" J/%%%
 * %.       %%%%/ o  o \%%%
 * `%%.     %%%%    _  |%%%
 *  `%%     `%%%%(__Y__)%%'
 *  //       ;%%%%`\-/%%%'
 * ((       /  `%%%%%%%'
 *  \\    .'          |
 *   \\  /       \  | |
 *    \\/         ) | |
 *     \         /_ | |__
 *     (___________))))))) 攻城湿
 */

​