第十章存储过程触发器及自定义函数

MySQL基于实例sales创建⾃定义函数、视图、存储过程及触发器实例：数据库sales1.客户表（Customer）客户编号(CusNo)姓名(CusName)地址(Address)电话(Tel)C001杨婷北京010-*******C002李和平上海021-********C003叶新成都024-*******C004冯⾠诚上海021-********2.产品表(Product)产品编号(ProNo)品名(ProName)单价(price)库存数量(Stocks)P0001液晶电视5600.00800P0002空调2390.00460P0003洗⾐机3700.00600P0004电热⽔器890.001203.销售表(ProOut)销售⽇期(SaleDate)客户编号(CusNo)产品编号(ProNo)销售数量(Quantity)2007-10-27C001P000132007-11-06C004P0003402007-12-27C001P000352008-3-15C002P0002122008-05-02C003P0002212008-05-02C003P000192008-09-21C004P0001302008-11-21C004P000173⼀、创建⼀⾃定义函数sumMoney，要求能够利⽤该函数计算出销售⾦额，并进⾏测试，利⽤该函数计算出每种产品(ProNo)的销售⾦额。

1 DELIMITER $$2 CREATE FUNCTION sumMoney( pno VARCHAR(10)) -- 输⼊产品编号3 RETURNS DOUBLE(10,2) -- 返回⾦额数据类型4 BEGIN -- 函数体（返回销售⾦额=产品单价*销售数）5 RETURN6 (SELECT SUM(quantity) FROM proout po,product pr WHERE po.prono=pr.prono AND pr.prono=pno GROUP BY po.prono) --销售数7 *(SELECT pr.price FROM product pr WHERE pr.prono=pno ); --单价8 END$$9 DELIMITER ;1011 测试：SELECT sumMoney('P0001');⼆、创建视图viewPro，要求显⽰每种产品的销售量和销售⾦额。

北京理工大学数据库实验4主讲老师：赵晓林学生：李经2012/11/21实验 4：存储过程、触发器 (1)4.1 作业声明 (1)4.2 实验目的 (1)4.2.1 建立“学籍与成绩管理系统”表格 (1)4.3 实验过程 (1)4.3.1 在“学籍与成绩管理系统”表格中输入不符合系统要求的数据 (1)4.3.2 建立存储过程，计算学生的总学分、总成绩，并保存在另一张表中 (2)4.3.3 查询总成绩表，并进行排序 (3)4.3.4 在相关的表上建立触发器，实现主外键的功能 (3)4.3.5 讨论触发器与主外键的异同 (4)4.3.6 在表上建立触发器实现对数据录入、修改的限制 (4)4.3.7 讨论视图、存储过程、触发器、用户自定义函数的使用范围及优缺点 (4)4.4 实验结论 (5)4.5 实验体会 (5)实验 4：存储过程、触发器4.1 作业声明本次作业全部由本人完成，若存在抄袭或雷同现象，本人愿意接受老师相应的处理4.2 实验目的4.2.1建立“学籍与成绩管理系统”表格•不建立表之间的参照关系•输入数据，以便在表上进行各种操作4.3 实验过程建立“学籍与成绩管理系统”表格操作，输入数据操作同实验三4.3.1在“学籍与成绩管理系统”表格中输入不符合系统要求的数据（如学生学籍表中学号重复），建立适当的存储过程，分别查找和删除这些不合法的数据4.3.1.1 sql代码insert into xs values('孙悟空','2120100030','05','08111005','1990-5-1','男'); --插入一行非法数据，假设学号合法范围为~1120100099create proc check_xh@start char(10),@end char(10)asbegindelete from xswhere xs.xh<@startor xs.xh>@endendselect*from xswhere xs.xh<'1120100001'or xs.xh>'1120100099'exec check_xh'1120100001','1120100099' --调用存储过程select*from xswhere xs.xh<'1120100001'or xs.xh>'1120100099'4.3.1.2 结果调用前后对比，非法数据已经被删除4.3.2建立存储过程，计算学生的总学分、总成绩，并保存在另一张表中4.3.2.1 sql代码create table cjd(xm varchar(8)not null,xf decimal(5,1),cj decimal(5,1),)create proc cjd_show@start char(10),@end char(10)asbegininsert into cjdselect xs.xm,sum(kc.xf),sum(xk.cj)from xs,kc,xkwhere xs.xh>=@start and xs.xh<=@endand xs.xh=xk.xhand xk.kcbh=kc.kcbhgroup by xs.xmEndexec cjd_show'1120100001','1120100099'select*from cjd4.3.2.2 结果4.3.3查询总成绩表，并进行排序4.3.3.1 sql代码select*from cjd order by cj4.3.3.2 结果成绩表按照总成绩升序排列4.3.4在相关的表上建立触发器，实现主外键的功能4.3.4.1 Sql代码create trigger xdh_pk on xb--模拟主键after insertasif (select COUNT(*)from xb,inserted where xb.xdh=inserted.xdh)>1beginprint'插入数据重复'rollback transactionendcreate trigger xdh_fk on js--模拟外键after insertasif (select COUNT(*)from xb,inserted where xb.xdh=inserted.xdh)=0beginprint'插入数据不合法'rollback transactionEndinsert into xb values('05','德语'); --插入重复数据insert into js values('姜子牙','9920100005','一级','09'); --非法数据，xdh09不存在于系表中4.3.4.2 结果插入以上两行数据时，结果分别为，系统显示：“插入数据重复消息3609，级别16，状态1，第1 行事务在触发器中结束。

实验5 存储过程、函数、触发器操作
已知一个已经建好的数据库factory,现在该数据库上存在三个表
1.职工表worker,其结构为 (职工号 numnber ,姓名 char(8), 性别 char(2) ,
出生日期 date, 党员否 char, 参加工作时间 date , 部门号 number ),其中职工号为主键
2.部门表 department 结构为 ( 部门号 number , 部门名称 varchar(20) ) ,
其中部门号为主键. 通常的部门信息有人事部,市场部,财务处等等
3.职工工资表 salary 其结构为 ( 职工号 number ,姓名 char(10) , 日期
date , 工资 number(10,2) ) . 其中职工号和日期为关键字
在以上的数据库上完成如下操作：
1.创建一个为worker表添加职工记录的存储过程addworker
2.创建一个为给定职工号,查询职工信息及部门名称的存储过程query_worker
3.创建一个为给定职工号,删除worker表中记录的存储过程delete_worker
4.显示存储过程
5.创建一个函数，完成给定职工号返回职工所在部门的名称的函数
6.创建一个函数，完成给定部门号返回该部门的最大年龄的函数
7.在表department上创建一个触发器deaprt_update，当更改部门号时同步更
改worker表中对应的部门号
8.在表worker上创建一个触发器worker_delete，当删除职工记录同步删除对
应职工的工资记录
9.删除两个刚刚建立触发器
以上内容请书写实验报告
余下时间请完成数据库的预备试验。

《存储过程、函数与触发器操作》实验一、实验目的与要求1、掌握存储过程的使用。

2、掌握函数的使用。

3、掌握触发器操作。

三、实验内容一、存储过程1、在“教务管理系统”数据库中创建一个名为ProcStudentInfo的存储过程，它返回学生的学号、姓名、性别、班级编号、年级和籍贯信息。

CREATE PROCEDURE Proc_StudentInfoASSELECT 学号,姓名,性别,班级编号,年级,籍贯FROM 学生信息2、用EXECUTE执行Proc_StudentInfo存储过程。

EXECUTE Proc_StudentInfo3、创建一个存储过程，用于返回“教务管理系统”数据库上某个班级中所有学生的信息。

CREATE PROCEDURE Proc_GetClassStudent1@ClassID varchar(14)ASSELECT 学号,姓名,性别,班级编号,年级,籍贯FROM 学生信息WHERE 班级编号=@ClassID4、创建一个存储过程，用于返回“教务管理系统”数据库上某个班级中所有学生的信息，默认班级编号为'20031340000102' 。

CREATE PROCEDURE Proc_GetClassStudent2@ClassID varchar(14)= '20031340000102'ASSELECT 学号,姓名,性别,班级编号,年级,籍贯FROM 学生信息WHERE 班级编号=@ClassIDEXECUTE Proc_GetClassStudent2 '20031340000103'5、创建一个返回执行代码为100的存储过程。

CREATE PROCEDURE Proc_GetClassStudent4@ClassID varchar(14)ASBEGINSELECT 学号,姓名,性别,班级编号,年级,籍贯FROM 学生信息WHERE 班级编号=@ClassIDRETURN 100END6、执行存储过程Proc_GetClassStudent2和Proc_GetClassStudent4，并定义两个个变量存储执行返回代码。

sql 存储过程中调用自定义函数自定义函数在SQL存储过程中的调用SQL存储过程是一段预定义的SQL代码集合，可以在数据库中进行重复使用。

而自定义函数是一段可重用的SQL代码，用于执行特定功能并返回一个值。

在SQL存储过程中，我们可以调用自定义函数来实现更加复杂的逻辑和计算。

我们需要创建一个自定义函数。

在SQL中，可以使用CREATE FUNCTION语句来定义一个函数，指定函数的名称、参数和返回值的数据类型，以及函数的主体逻辑。

例如，我们可以创建一个自定义函数来计算两个数的和：```CREATE FUNCTION calculate_sum(a INT, b INT)RETURNS INTBEGINDECLARE result INT;SET result = a + b;RETURN result;END;```在上述代码中，我们定义了一个名为calculate_sum的函数，它接受两个整数参数a和b，并返回一个整数类型的结果。

函数的主体逻辑是将a和b相加，并将结果赋值给变量result，然后通过RETURN语句返回结果。

接下来，我们可以在SQL存储过程中调用这个自定义函数。

在存储过程中，可以使用SELECT语句来调用函数并获取返回值。

例如，我们可以创建一个存储过程来计算两个数的和并输出结果：```CREATE PROCEDURE calculate_and_output_sum(a INT, b INT) BEGINDECLARE sum_result INT;SET sum_result = (SELECT calculate_sum(a, b));SELECT 'The sum of ' || a || ' and ' || b || ' is ' || sum_result; END;```在上述代码中，我们定义了一个名为calculate_and_output_sum 的存储过程，它接受两个整数参数a和b。

触发器、存储过程和函数三者有何区别四什么时候用存储过程？存储过程就是程序，它是经过语法检查和编译的SQL语句，所以运行特别快。

触发器是特殊的存储过程，存储过程需要程序调用，而触发器会自动执行；你所说的函数是自定义函数吧，函数是根据输入产生输出，自定义只不过输入输出的关系由用户来定义。

在什么时候用触发器？要求系统根据某些操作自动完成相关任务，比如，根据买掉的产品的输入数量自动扣除该产品的库存量。

什么时候用存储过程？存储过程就是程序，它是经过语法检查和编译的SQL语句，所以运行特别快。

存储过程和用户自定义函数具体的区别先看定义：存储过程存储过程可以使得对数据库的管理、以及显示关于数据库及其用户信息的工作容易得多。

存储过程是SQL 语句和可选控制流语句的预编译集合，以一个名称存储并作为一个单元处理。

存储过程存储在数据库内，可由应用程序通过一个调用执行，而且允许用户声明变量、有条件执行以及其它强大的编程功能。

存储过程可包含程序流、逻辑以及对数据库的查询。

它们可以接受参数、输出参数、返回单个或多个结果集以及返回值。

可以出于任何使用SQL 语句的目的来使用存储过程，它具有以下优点：·可以在单个存储过程中执行一系列SQL 语句。

·可以从自己的存储过程内引用其它存储过程，这可以简化一系列复杂语句。

·存储过程在创建时即在服务器上进行编译，所以执行起来比单个SQL 语句快。

用户定义函数函数是由一个或多个Transact-SQL 语句组成的子程序，可用于封装代码以便重新使用。

Microsoft? SQL Server? 2000 并不将用户限制在定义为Transact-SQL 语言一部分的内置函数上，而是允许用户创建自己的用户定义函数。

可使用CREATE FUNCTION 语句创建、使用ALTER FUNCTION 语句修改、以及使用DROP FUNCTION 语句除去用户定义函数。

每个完全合法的用户定义函数名(database_name.owner_name.function_name) 必须唯一。

触发器与存储过程触发器（Trigger）是数据库中的一种特殊对象，它与一些特定的数据库事件相关联，并且当这个事件发生时，触发器可以在自动执行的过程中被触发。

而存储过程（Stored Procedure）则是一段预先编译好的SQL 语句的集合，它可以被保存在数据库服务器端，通过调用来执行。

触发器和存储过程都是数据库中的重要组件，它们都可以用于实现数据的自动化处理和一些复杂的业务逻辑。

但是它们在功能和使用方法上有一些不同之处。

首先，触发器的触发条件是事先设置好的，当该条件满足时才会被触发执行，而存储过程是主动调用执行的。

触发器通常与数据库中的表相关联，并且在表上的插入、更新或删除等事件发生时触发。

存储过程可以在任何时候被调用执行，无论是否有其他数据库事件发生。

其次，触发器通常用于实现数据的自动化处理，比如在插入新纪录时通过触发器自动计算一些字段的值，或者在删除记录时触发器做一些相关操作。

而存储过程则更倾向于实现业务逻辑的封装和复用，比如在一个库存管理系统中，可以使用存储过程来实现添加商品、修改商品信息、删除商品等操作。

此外，触发器由数据库引擎直接管理，它是与数据库表密切相关的一种对象，所以当表被删除或者修改时，相关联的触发器也会相应地被删除或修改。

而存储过程则是作为独立于表的对象存在，当数据库表被删除或修改时，存储过程不受影响。

在使用上，触发器和存储过程都可以用于实现一些相同的功能，但触发器更适合于在特定的数据库事件发生时执行自动化的操作，而存储过程更适合于实现复杂的业务逻辑和一些需要主动调用的场景。

总之，触发器和存储过程都是数据库中的重要组件，它们可以用于实现一些自动化的处理和复杂的业务逻辑。

它们在功能和使用方法上有一些不同之处，需要根据具体的需求来选择和使用。

MySQL中的自定义函数与存储过程的开发与调试1. 引言在数据库开发中，自定义函数和存储过程是非常重要的工具。

它们可以简化复杂的业务逻辑，提高查询和处理数据的效率。

本文将通过介绍MySQL中自定义函数和存储过程的开发与调试，帮助读者深入了解和掌握这两个功能的使用方法。

2. 自定义函数的开发与调试自定义函数是MySQL中的一个强大特性，它允许开发者自己定义并使用自己的函数。

在开发过程中，我们可以使用一些内置函数，如COUNT、SUM等，但有时候我们需要实现一些特定的功能，这时自定义函数就派上用场了。

在MySQL中，自定义函数的开发主要包括以下几个步骤：2.1 创建函数在MySQL中，可以使用CREATE FUNCTION语句来创建函数。

例如，我们希望实现一个函数，计算两个整数的和，可以使用以下语句： CREATE FUNCTION addTwoIntegers(a INT, b INT) RETURNS INTBEGINDECLARE result INT;SET result = a + b;RETURN result;END;在上述代码中，我们创建了一个名为addTwoIntegers的函数，它接受两个整数类型的参数，并返回一个整数类型的值。

函数体部分采用BEGIN...END包围起来，其中使用DECLARE语句声明了一个变量result，并使用SET语句给result 赋值。

最后，使用RETURN语句返回计算结果。

2.2 调用函数完成函数的创建后，我们可以通过SELECT语句来调用这个函数。

例如，我们想计算两个整数10和20的和，可以使用以下语句：SELECT addTwoIntegers(10, 20);执行以上语句后，MySQL会返回计算结果30。

2.3 调试函数在函数开发的过程中，我们可能会遇到一些错误或逻辑问题。

为了方便调试，MySQL提供了一些调试工具和技巧。

例如，我们可以使用SELECT语句打印中间变量的值，以检查函数的执行过程。

存储过程、触发器和用户自定义函数实验实验内容一练习教材中存储过程、触发器和用户自定义函数的例子。

教材中的BookSales数据库，在群共享中，文件名为BookSales.bak。

实验内容二针对附件1中的教学活动数据库，完成下面的实验内容。

1、存储过程（1）创建一个存储过程，该存储过程统计“高等数学”的成绩分布情况，即按照各分数段统计人数。

执行存储过程：exec countpeople（2）创建一个存储过程，该存储过程有一个参数用来接收课程号，该存储过程统计给定课程的平均成绩。

执行存储过程：exec avg_score'C602'（3）创建一个存储过程，该存储过程将学生选课成绩从百分制改为等级制（即A、B、C、D、E）。

执行存储过程：exec alterscore（4）创建一个存储过程，该存储过程有一个参数用来接收学生姓名，该存储过程查询该学生的学号以及选修课程的门数。

执行存储过程：exec select_courses'李强'（5）创建一个存储过程，该存储过程有两个输入参数用来接收学号和课程号，一个输出参数用于获取相应学号和课程号对应的成绩。

执行存储过程:declare@score smallintexec select_socre'98601','C602',@score outputprint'成绩是'+convert(char(2),@score)+'分'2、触发器（1）为study表创建一个UPDATE触发器，当更新成绩时，要求更新后的成绩不能低于原来的成绩。

创建完触发器尝试进行更新数据：update study set score=60 where sno='98601'and cno='C601'执行完之后查询结果发现成绩仍然是90select score from study where sno='98601'and cno='C601'再更新一个高于90分的成绩则可以成功update study set score=91 where sno='98601'and cno='C601'select score from study where sno='98601'and cno='C601'（2）为study表创建一个DELETE触发器，要求一次只能从study表中删除一条记录。

mysql的存储过程和函数MySQL的存储过程和函数是数据库中非常重要的两个概念，它们可以帮助我们更加高效地管理和操作数据库。

在本文中，我们将详细介绍MySQL的存储过程和函数，包括它们的定义、使用方法以及优缺点等方面。

一、MySQL的存储过程1. 定义MySQL的存储过程是一组预编译的SQL语句，它们被存储在数据库中，并可以被多次调用。

存储过程可以接受参数，并且可以返回结果集或者输出参数。

2. 使用方法创建存储过程的语法如下：CREATE PROCEDURE procedure_name ([IN|OUT|INOUT] parameter_name data_type [, ...])BEGIN-- 存储过程的SQL语句END;其中，procedure_name是存储过程的名称，parameter_name是存储过程的参数名称，data_type是参数的数据类型。

IN表示输入参数，OUT表示输出参数，INOUT表示既是输入参数又是输出参数。

调用存储过程的语法如下：CALL procedure_name ([parameter_value, ...]);其中，procedure_name是存储过程的名称，parameter_value是存储过程的参数值。

3. 优缺点存储过程的优点在于：（1）提高了数据库的性能，因为存储过程是预编译的，可以减少SQL语句的解析和编译时间。

（2）提高了数据库的安全性，因为存储过程可以控制对数据库的访问权限。

（3）提高了代码的可维护性，因为存储过程可以被多次调用，可以减少代码的重复性。

存储过程的缺点在于：（1）需要学习存储过程的语法和使用方法。

（2）存储过程的调试和测试比较困难。

二、MySQL的函数1. 定义MySQL的函数是一段预编译的代码，它们可以接受参数，并且可以返回一个值。

函数可以被多次调用，并且可以嵌套使用。

2. 使用方法创建函数的语法如下：CREATE FUNCTION function_name ([parameter_name data_type [, ...]])RETURNS return_typeBEGIN-- 函数的SQL语句END;其中，function_name是函数的名称，parameter_name是函数的参数名称，data_type是参数的数据类型，return_type是函数的返回值类型。

数据库的⼀些基本概念（视图，存储过程，函数，触发器）⼀、视图视图定义视图是从⼀个或⼏个基本表（或视图）中导出的虚拟的可视化的表。

在系统的数据字典中仅存放了视图的定义，不存放视图对应的数据。

视图特点安全：有的数据是需要保密的，如果直接把表给出来进⾏操作会造成泄密，那么可以通过创建视图把相应视图的权限给出来即可保证数据的安全。

⾼效：复杂的连接查询，每次执⾏时效率⽐较低，可以考虑新建视图，每次从视图中获取，将会提⾼效率。

定制数据：将常⽤的字段放置在视图中。

使⽤视图不会加快数据查询速度。

⼆、存储过程存储过程（Stored Procedure）是在⼤型数据库系统中，⼀组为了完成特定功能的SQL 语句集，存储在数据库中，经过第⼀次编译后调⽤不需要再次编译，⽤户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执⾏它。

存储过程是数据库中的⼀个重要对象。

优点存储过程的能⼒⼤⼤增强了SQL语⾔的功能和灵活性。

可保证数据的安全性和完整性。

通过存储过程可以使没有权限的⽤户在控制之下间接地存取数据库，从⽽保证数据的安全。

存储过程可以使相关的动作在⼀起发⽣，从⽽可以维护数据库的完整性。

在运⾏存储过程前，数据库已对其进⾏了语法和句法分析，并给出了优化执⾏⽅案。

这种已经编译好的过程可极⼤地改善SQL语句的性能。

可以降低⽹络的通信量。

使体现企业规则的运算程序放⼊数据库服务器中，以便集中控制。

三、函数在数据库中都有函数,这些函数属于系统函。

除此之外⽤户也可以编写⽤户⾃定义函数。

⽤户定义函数是存储在数据库中的代码块，可以把值返回到调⽤程序。

调⽤时如同系统函数⼀样，如max(value)函数，其value被称为参数。

函数⼀般功能⽐较简单，对于mysql函数只有传⼊参数，不像存储过程⼀样，有输⼊输出参数。

数据库函数特点如下：存储函数将向调⽤者返回⼀个且仅返回⼀个结果值。

存储函数嵌⼊在sql中使⽤的,可以在select中调⽤，就像内建函数⼀样，⽐如cos()、hex()。

1.利用企业管理器创建一个存储过程stu_info，根据传入的编号，查询某学生的基本信息。

代码如下：create procedure stu_info@stunum char(10)asif not exists(select * from s where sno=@stunum)RAISERROR ('没有该学号对应信息.' , 16, 1)elsebeginselect sno as 编号,sname as 姓名,ssex as 性别,birthday as 出生日期,polity as 政治面貌from swhere sno=@stunumend测试存储过程：EXECUTE stu_info '1111111'2.创建一个存储过程stu_grade,根据输入的学生编号，返回其选课及其成绩。

Create procedure stu_grade@stunum char(10)asIf not exists(select * from s where sno=@stunum)print '查无此人!'elsebeginif not exists(select * from s,sc where s.sno=sc.sno)print '无此学生的选课信息!'elseselect sname,cname,gradefrom s,c,scwhere s.sno=sc.sno and o=oendEXECUTE stu_grade '11111111'1．创建一个insert触发器,当在pupil表中插入一条新记录时，给出‘你已经插入了一条新记录！！！’的提示信息。

步骤：程序代码如下：create trigger stuinsert on sfor insertasdeclare @msg char(50)set @msg=' 你已经插入了一条新记录！！'print @msg测试：insert into s values ('33333333','王五','男','2003-1-1','团员')2.创建一个insert触发器,当在pupil表中插入一条新记录时，不允许在学号中出现重复的编号或出现空值。