-
2019-09-03 11:13:56
存储过程
(Stored Procedure)和存储函数
(Stored Function)是在数据库中定义的一些完成特定功能的SQL语句集合,其经编译后存储在数据库中。存储过程和存储函数中可包含流程控制语句及各种SQL语句。它们可以接受参数、输出参数、返回单个或者多个结果。在MySQL中使用存储过程,而不是使用存储在客户端计算机本地的SQL程序的优点如下:
-
存储过程增加了SQL的功能和灵活性
-
存储过程允许模块化程序设计
-
存储过程能实现较快的执行速度
-
存储过程能够减少网络流量
-
存储过程可作为一种安全机制来充分利用
创建存储过程
在MySQL中,创建存储过程和存储函数必须具有CREATE ROUTINE权限,并且ALTER ROUTINE和EXECUTE权限被自动授予它的创建者。
# 创建存储过程 CREATE PROCEDURE procedure_name([proc_parameter[,...]]) [characteristic[,...]] Routine_body procedure_name: [IN|OUT|INOUT]param_name type characteristic: LANGUAGE SQL、[NOT]DETERMINISTIC、{CONTAINS SQL|NO SQL|READS SQL DATA|MODIFIES SQL DATA}、 SQL SECURITY{DEFINER|INVOKER}、COMMENT'string' # 调用存储过程 CALL [dbname.]sp_name([parameter[,...]]);
在创建存储过程时,系统默认指定CONTAINS SQL,表示存储过程中使用了SQL语句。但是,如果存储过程中没有使用SQL语句,最好设置为NO SQL。而且,最好在存储过程的COMMENT部分对存储过程进行简单的注释,以便以后在阅读存储过程的代码时理解代码。
mysql> use gradem; Database changed mysql> delimiter // mysql> create procedure proc_sc() -> reads sql data -> begin -> select student.sno, sname, sum(degree)/count(degree) from student inner join sc on student.sno=sc.sno; -> end // Query OK, 0 rows affected (0.09 sec) mysql> delimiter; mysql>
MySQL中默认的语句结束符为分号(?。存储过程中的SQL语句需要分号来结束。为了避免冲突,首先用“DELIMITER //”将MySQL的结束符设置为//,最后用“DELIMITER ;”将结束符恢复成分号。
创建存储函数
在MySQL中,存储函数的使用方法与MySQL内部函数的使用方法一样。两者唯一的区别在于,存储函数是用户自己定义的,而内部函数是MySQL开发者定义的。
# 创建存储函数 CREATE FUNCTION func_name([func_parameter[,...]]) RETURES type [characteristic[,...]] Routine_body # 调用存储函数 SELECT [dbname.]func_name([parameter[,...]]);
PROCEDURE可以指定IN、OUT或INOUT类型的参数,而FUNCTION的参数类型默认为IN。RETURNS子句只能包含在FUNCTION中,它用来指定函数的返回类型,而且函数体必须包含一个RETURN value语句。
查看存储过程和存储函数
# 查看存储过程和存储函数的状态 SHOW {PROCEDURE|FUNCTION} STATUS [LIKE 'pattern']; 或 SHOW CREATE {PROCEDURE|FUNCTION} sp_name; 或 SELECT * FROM information_schema.Routines WHERE ROUTINE_NAME='sp_name';
SHOW STATUS语句只能查看存储过程或存储函数是操作哪一个数据库,以及存储过程或存储函数的名称、类型、谁定义的、创建和修改时间、字符编码等信息。但是,这个语句不能查询存储过程或存储函数的具体定义。如果就需要查看详细定义,就需要使用SHOW CREATE语句。
删除存储过程和存储函数
DROP {PROCEDURE|FUNCTION} [IF EXISTS] sp_name;
IF EXISTS子句是MySQL的扩展,如果存储过程或存储函数不存在,则它可以防止发生错误,产生一个用SHOW WARNINGS查看的警告。如果另一个存储过程调用某个已被删除的存储过程,则MySQL将在执行调用进程时显示一条错误消息。
(最近更新:2019年09月03日)
更多相关内容 -
-
MySQL实验报告5(存储过程与函数)(1)(1).pdf
2021-09-30 18:03:15MySQL实验报告5(存储过程与函数)(1)(1).pdf -
实 验 五 存储过程的建立与使用
2017-06-02 16:02:00了解触发器和存储过程的概念和作用,掌握创建和使用触发器、存储过程的方法,为后继学习和开发程序作准备。 -
MySQL数据库应用实战教程 第5章教案 MySQL函数和存储过程.docx
2020-03-17 13:42:28PAGE PAGE 1 第5章MySQL函数和存储过程 课程名称 MySQL数据库应用实战教程慕课版 项目名称 MySQL函数和存储过程 任务名称 MySQL函数和存储过程 课时 4 项目性质 演示性 验证性 设计性 综合性 授课班级 授课日期 授课... -
MySQL存储过程与函数的创建管理实验
2021-01-27 03:16:43实验6:存储过程与函数的创建管理实验一、实验目的:理解存储过程和函数的概念。掌握创建存储过程和函数的方法。掌握执行存储过程和函数的方法。掌握游标的定义、使用方法。二、验证性实验1.某超市的食品管理的...实验6:存储过程与函数的创建管理实验
一、实验目的:
理解存储过程和函数的概念。
掌握创建存储过程和函数的方法。
掌握执行存储过程和函数的方法。
掌握游标的定义、使用方法。
二、验证性实验
1.某超市的食品管理的数据库的Food表,Food表的定义如表所示,
各列有如下数据:
‘QQ饼干’,‘QQ饼干厂’,2.5,‘2008’,3,‘北京’
‘MN牛奶’,‘MN牛奶厂’,3.5,‘2009’,1,‘河北’
‘EE果冻’,‘EE果冻厂’,1.5,‘2007’,2,‘北京’
‘FF咖啡’,‘FF咖啡厂’,20,‘2002’,5,‘天津’
‘GG奶糖’,‘GG奶糖厂’,14,‘2003’,3,‘广东’
create database foodinfo;
use foodinfo;
create table food(
foodid int(4) primary key not null unique auto_increment,
name varchar(20) not null,
company varchar(30) not null,
price float not null,
product_time year,
validity_time int(4),
address varchar(50)
);
desc food;
//机房
insert into food(name,company,price,product_time,validity_time,address)
values
('QQbingban','QQbingbanchang',2.5,'2008',3,'Beijing'),
('MNniunai','MNniunaichang',3.5,'2009',1,'Hebei'),
('EEguodong','EEguodongchang',1.5,'2007',2,'Beijing'),
('FFkafei','FFkafeichang',20,'2002',5,'Tianjin'),
('GGnaitang','GGnaitangchang',14,'2003',3,'Guangdong');
select * from food;
insert into food(name,company,price,product_time,validity_time,address)
values
('QQ饼干','QQ饼干厂',2.5,'2008',3,'北京'),
('MN牛奶','MN牛奶厂',3.5,'2009',1,'河北'),
('EE果冻','EE果冻厂',1.5,'2007',2,'北京'),
('FF咖啡','FF咖啡厂',20,'2002',5,'天津'),
('GG奶糖','GG奶糖厂',14,'2003',3,'广东');
select * from food;
(1)在food表上创建名为Pfood_price_count的存储过程。其中存储过程Pfood_price_count有3个参数。输入参数为price_info1和price_info2,输出参数为count。存储过程的满足:查询food表中食品单价高于price_info1且低于price_info2的食品种数,然后由count参数来输出,并且计算满足条件的单价的总和。
代码如下:
delimiter &&
create procedure pfood_price_count(in price_info1 float,in price_info2 float,out count int)
reads sql data
begin
declare temp float;
declare match_price cursor for select price from food;
declare exit handler for not found close match_price;
set @sum=0;
select count(*) into count from food
where price>price_info1 and price
open match_price;
repeat
fetch match_price into temp;
if temp>price_info1 and temp
then set @sum=@sum+temp;
end if;
until 0 end repeat;
close match_price;
end &&
delimiter ;
(2)使用CALL语句来调用存储过程。查询价格在2至18之间的食品种数。代码如下:
call pfood_price_count(2,18,@count);
(3)使用SELECT语句查看结果。代码如下:
select @count,@sum;
其中,count是存储过程的输出结果:sum是存储过程中的变量,sum中的值满足足条件的单价的总和。
(4)使用DROP语句删除存储过程Pfood_price_count。代码如下:
drop procedure pfood_price_count;
(5)使用存储函数来实现(1)的要求。存储函数的代码如下:
delimiter &&
create function pfood_price_count1(price_info1 float,price_info2 float)
returns int reads sql data
begin
return(select count(*) from food
where price>price_info1 and price
end &&
delimiter ;
(6)调用存储函数
select pfood_price_count1(2,18);
(7)删除存储函数
drop function pfood_price_count1;
注:存储函数只能返回一个值,所以只实现了计算满足条件的食品种数。使用RETURN来将计算的食品种数返回回来。调用存储函数与调用MySQL内部函数的方式是一样的。
三、设计性实验
学校教师管理数据库中的teacherInfo表,其表的定义如下表所示,请完成如下操作。
向teacherInfo表中插入记录:
1001,‘张龙’,‘男’,‘1984-11-08’,‘北京市昌平区’
1002,‘李梅’,‘女’,‘1970-01-21’,‘北京市海淀区’
1003,‘王一丰’,‘男’,‘1976-10-30’,‘北京市昌平区’
1004,‘赵六’,‘男’,‘1980-06-05’,‘北京市顺义区’
create database teacher;
use teacher;
create table teacherinfo(
num int(10) primary key not null unique,
name varchar(20) not null,
sex varchar(4) not null,
birthday datetime,
address varchar(50)
);
desc teacherinfo;
//机房
insert into teacherinfo(num,name,sex,birthday,address)
values
(1001,'ZL','M','1984-11-08','BeijingChangping'),
(1002,'LM','W','1970-01-21','BeijingHaiding'),
(1003,'WYF','M','1976-10-30','BeijingChangping'),
(1004,'ZN','M','1980-06-05','BeijingShunyi');
select * from teacherinfo;
insert into teacherinfo(num,name,sex,birthday,address)
values
(1001,'张龙','男','1984-11-08','北京市昌平区'),
(1002,'李梅','女','1970-01-21','北京市海淀区'),
(1003,'王一丰','男','1976-10-30','北京市昌平区'),
(1004,'赵六','男','1980-06-05','北京市顺义区');
select * from teacherinfo;
(1)创建名为teachernfo1的存储过程。要求:存储过程teachernfo1有3个参数。输入参数为teacherid和type,输出参数为info。满足:根据编号(teacherid)来查询teachernfo表中的记录。如果type的值为1时,将姓名(name)传给输出参数info;如果type的值为2时,将年龄传给输出参数info;如果type为其他值,则返回字符串“Error”。
//使用“DELIMITER &&”将SQL语句的结束符号变成&&
delimiter &&
create procedure teachernfo1(in teacherid int,in type int,out info varchar(20))
reads sql data
begin
case type
when 1 then
select name into info from teacherinfo where num=teacherid;
when 2 then
select year(now())-year(birthday) into info from teacher where num=teacherid;
else
select 'Error' into info;
end case;
end &&
delimiter ;
(2)调用存储过程,参数值teacherid为1001,type为1。
call teacherinfo1(1001,1,@info);
select @info;
(3)使用DROP PRODECURE语句来删除存储过程
drop procedure teacherinfo1;
(4) 创建名为teacherinfo2的存储函数。要求:存储过程teacherinfo2有两个参数:teacher id和type。满足:根据编号(teacher id)来查询teacher表中的记录。如果type的值是1时,则返回姓名(name)值;如果type的值是2时,则返回年龄;如果type为其他值,则返回字符串“Error”。
delimiter &&
create function teacherinfo2(teacherid int,type int)
returns varchar(20) reads sql data
begin
declare temp varchar(20);
if type=1
then
select name into temp from teacherinfo where num=teacherid;
elseif type=2
then
select year(now())-year(birthday) into temp from teacherinfo where num=teacherid;
else
set temp='Error';
end if;
return temp;
end &&
delimiter ;
(5)使用SELECT语句调用teacherinfo2存储函数。
select teacherinfo2(1002,1);
select teacherinfo2(1002,2);
select teacherinfo2(1002,3);
(6)使用DROP FUNCTION语句来删除teacherinfo2存储函数。
drop function teacherinfo2;
四、观察与思考
(1) 什么时候适合通过创建存储过程来实现?
答:函数限制比较多,如不能用临时表,只能用表变量等,而存储过程的限制相对就比较少。
(2)功能相同的存储过程和存储函数的不同点有哪些?
答: 存储过程:可以使得对的管理、以及显示关于及其用户信息的工作容易得多。存储过程是 SQL 语句和可选控制流语句的预编译集合,以一个名称存储并作为一个单元处理。存储过程存储在数据库内,可由应用程序通过一个调用执行,而且允许用户声明变量、有条件执行以及其它强大的编程功能。存储过程可包含程序流、逻辑以及对数据库的查询。它们可以接受参数、输出参数、返回单个或多个结果集以及返回值。
(3)使用游标对于数据检索的好处有哪些?
答:在数据库中,游标是一个十分重要的概念。游标提供了一种对从表中检索出的数据进行操作的灵活手段,就本质而言,游标实际上是一种能从包括多条数据记录的结果集中每次提取一条记录的机制。游标总是与一条SQL 选择语句相关联因为游标由结果集(可以是零条、一条或由相关的选择语句检索出的多条记录)和结果集中指向特定记录的游标位置组成。当决定对结果集进行处理时,必须声明一个指向该结果集的游标。如果曾经用 C 语言写过对文件进行处理的程序,那么游标就像您打开文件所得到的文件句柄一样,只要文件打开成功,该文件句柄就可代表该文件。对于游标而言,其道理是相同的。可见游标能够实现按与传统程序读取平面文件类似的方式处理来自基础表的结果集,从而把表中数据以平面文件的形式呈现给程序。
我们知道关系数据库管理系统实质是面向集合的,在MS SQL SERVER 中并没有一种描述表中单一记录的表达形式,除非使用where 子句来限制只有一条记录被选中。因此我们必须借助于游标来进行面向单条记录的数据处理。由此可见,游标允许应用程序对查询语句select 返回的行结果集中每一行进行相同或不同的操作,而不是一次对整个结果集进行同一种操作;它还提供对基于游标位置而对表中数据进行删除或更新的能力;而且,正是游标把作为面向集合的数据库管理系统和面向行的程序设计两者联系起来,使两个数据处理方式能够进行沟通。
-
MySQL实验报告5存储过程与函数11
2021-04-20 15:29:420*0 s e 3)创建一个存储过程,通过调用存储函数的方法来获取表sch中的记录数与sch表中id的与。 delimiter $$ create PROCEDURE coun t_sch2(out s_a in t,out s_id int) BEGIN select coun t(*) into s_a from sch...四、实验内容及程序代码
1、sch数据表的表结构如表7、1所示,sch表的数据如表7、2
表7、1 sch 表结构
字段名
数据类型
主键
外键
非空
唯一
自增
id
INT(10)
就是
否
就是
就是
否
n ame
VARCHAR(50)
否
否
就是
否
否
class
VARCHAR(50)
否
否
就是
否
否
表7、2 sch表的内容
id
n ame
class
1
李明
C1
2
小梅
C2
1)建表sch并插入数据。
LMLdDdse uidriyeu
mysql> 匚reate table sch(
> i d i nt(10) pri mary key,
-> neme varchar(50) not null,
-> class varchar(50) not nul1);
Query OK, 0 rows affected (0.42 sec)
mysql> i nsert into sch values (1, ' 明"「匚1?儿〔2「小梅',rc2 r); Query OK, 2 rows affected CO. 10 sec^)
Records: 2 Duplicates: 0 Warnings: 0
创建一个存储函数,用来统计表sch中的记录数。
delimiter $$
create PROCEDURE coun t_sch5(out size int)
BEGIN
select coun t(*) into size from sch;
END
$$
delimiter;
TOC \o "1-5" \h \z tzmTI 1 rz <1 li rTt_£ <_ ki>
Qu ery OK w IL ir-ow aLiF"Fe
my s q 1 > sdaut住;
*—— *
I〒2已 I
鲁+
2 I
1 row i n 乞已七 €0. 0*0 s e
3)创建一个存储过程,通过调用存储函数的方法来获取表sch中的记录数与sch表中id的与。
delimiter $$
create PROCEDURE coun t_sch2(out s_a in t,out s_id int) BEGIN
select coun t(*) into s_a from sch;
select sum(id) as s_id from sch;
END
$$
delimiter;
++
I s_id I
44
I 3 I
1 row in set 〔0.01 sec)
2、创建一存储过程 in sert_stude nt_con dition_user,利用自定义错误触发条件定义
当插入学生的性别不就是“男”或“女”时结束存储过程,并提示“学生性别不正确”
表7、3 student表结构
字段名
数据类型
主键
外键
非空
唯一
自增
sno
Char(10)
就是
否
就是
就是
否
sn ame
VARCHAR(20)
否
否
就是
否
否
ssex
CHAR(2)
否
否
就是
否
否
Sage
smalli nt
否
否
就是
否
否
Sdept
VARCHAR(30)
否
否
就是
否
否
en terdate
datetime
否
否
就是
否
否
delimiter $$
create PROCEDURE insert_student_condition_user(insnoint,insname
VARCHAR(20),
in ssex varchar(2),i n sage in t,i n sdept varchar(30))
BEGIN
DECLARE continue han dler for sqlstate '42S02' select '学生性别不正确 ';
if ssex=' 男'or ssex=' 女'THEN
insert into stude nt values(s no,sn ame,ssex,sage,sdept);
end if;
end $$ delimiter;
她',20,'C1');call in sert_stude nt_con diti on _user(1,'pqw','
她',20,'C1');
drop PROCEDURE in sert_stude nt_co ndition_user;
信息
结栗1
畴兄
孚圭宓KIM
卜学主性別KU确
3、创建一存储过程update_student_borthyear,在学生表(表结构如表 7、3)中添加
字段” borthyear ”(出生年份),在存储过程中利用游标,通过学生年龄计算出出生年
份并修改表中对应字段。
create procedure update_stude nt_borthyear(i n s_no char(10))
BEGIN
declare s_age char(10);
declare state char(20);
declare cur cursor for select sage from stude nt;
declare exit han dler for 1329 set state='error';
ope n cur;
REPEAT
fetch cur into s_age;
update stude nt set borthyear=2018-s_age where sno=s_no;
un til state='error'
end repeat;
close cur;
END
srosnamessexugesdept
srosnamessex
uge
sdept
borthyea*
?
201818 pqwS
18
Ci
call update_stude nt_borthyear(201818);
mysql语句对于大小写不敏感,所以在写的时候要注意区分,整整找了半个小时的错误。
-
数据库原理实验五——触发器与存储过程(函数)
2021-10-28 20:06:55熟练掌握存储过程/函数的创建和执行方法。 熟练掌握触发器的创建和执行方法。 实验内容 针对SPJ_MNG数据库,创建并执行如下存储过程。(共计40分) (1) 创建一个没有参数的存储过程—jsearch1。该存储过程的作用...实验目的
- 熟练掌握存储过程/函数的创建和执行方法。
- 熟练掌握触发器的创建和执行方法。
实验内容
- 针对SPJ_MNG数据库,创建并执行如下存储过程。(共计40分)
(1) 创建一个没有参数的存储过程—jsearch1。该存储过程的作用是:当执行该存储过程时,将返回S表中北京供应商的所有信息。调用该存储过程并验证结果。(5分)
DELIMITER $$ CREATE PROCEDURE jsearch1() BEGIN SELECT * FROM S WHERE CITY = '北京'; END $$
验证
CALL jsearch1;
(2) 创建带输入参数的存储过程—jsearch2。该存储过程的作用是:当输入一个供应商所在城市名时(如北京),将返回该供应商的所有信息。调用存储过程并验证结果。(5分)
DELIMITER $$ CREATE PROCEDURE jsearch2(in city_in CHAR(45)) BEGIN SELECT * FROM S WHERE CITY = city_in; END $$
验证
CALL jsearch2('北京');
(3) 创建带输入参数和输出参数的存储过程(函数)—jsearch3。该存储过程的作用是:当输入一个供应商编号(输入参数SNO)时,将返回该供应商的名称(输出参数SNAME)。调用存储过程并验证结果。(5分)
DELIMITER $$ CREATE PROCEDURE jsearch3(in sno_in CHAR(45),out sname_out CHAR(45)) BEGIN SELECT SNAME INTO sname_out FROM S WHERE SNO = sno_in; END $$
验证
CALL jsearch3('S1',@name); SELECT @name;
(4) 创建一个使用游标的存储过程jsearch4,创建成功后调用该存储过程并验证结果。该存储过程的功能:当输入一个工程号JNO时,将返回供应该工程零件的所有供应商的名称(SNAME),这些供应商名拼接成一个字符串,并用逗号’,’分隔。
DELIMITER $$ CREATE PROCEDURE jsearch4(in jno_in CHAR(45)) BEGIN DECLARE TEMP CHAR(100) DEFAULT ''''; DECLARE NAME CHAR(10); DECLARE DONE INT DEFAULT 0; DECLARE C_NAME CURSOR FOR SELECT DISTINCT S.SNAME FROM S,SPJ WHERE S.SNO = SPJ.SNO AND SPJ.JNO = jno_in; DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET DONE = 1; OPEN C_NAME; FETCH C_NAME INTO NAME; REPEAT SET TEMP =CONCAT(TEMP,NAME); SET TEMP =CONCAT(TEMP,','); FETCH C_NAME INTO NAME; UNTIL DONE END REPEAT; SET TEMP =CONCAT(TEMP,''''); CLOSE C_NAME; SELECT TEMP; END $$
验证
CALL jsearch4('J2');
(5) 查看存储过程jsearch1和jsearch2的文本信息。(5分)
show create procedure jsearch1 \G; show create procedure jsearch2 \G;
(6) 查看存储过程jsearch1基本状态信息。(5分)
show procedure status like 'jsearch1';
(7) 删除jsearch1存储过程。(5分)
DROP PROCEDURE jsearch1;
- 针对Student数据库,创建和执行如下的触发器:(共计40分)
(1) 删除SC表上的外键约束,针对SC表创建一个名为insert_s的INSERT触发器。该触发器的功能:当用户向SC表中插入记录时,如果插入的cno值不是C表中Cno的已有值,则提示用户“不能插入C表中没有的数据”,并阻止该数据的插入;如果插入的sno值不是S表中的sno的已有值,则提示用户“不能插入S表中没有的数据”,并阻止该数据的插入。触发器创建成功之后,向SC表插入记录,验证触发器是否正常工作。(5分)
DELIMITER $$ CREATE TRIGGER insert_s BEFORE INSERT ON SC FOR EACH ROW BEGIN IF NEW.SNO NOT IN (SELECT DISTINCT(SNO) FROM S) THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '不能插入S表没有的数据'; END IF; IF NEW.CNO NOT IN (SELECT DISTINCT(CNO) FROM S) THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '不能插入C表没有的数据'; END IF; END $$
验证
INSERT INTO SC(ID,SNO,CNO) VALUES(10000,'100001','5'); INSERT INTO SC(ID,SNO,CNO) VALUES(10000,'100','7');
(2) 为S表创建一个名为dele_s1的DELETE触发器,该触发器的作用是提示用户“不能删除该表中的数据”并阻止用户删除S表中的数据。触发器创建成功之后,删除S表中记录,验证触发器是否正常工作。(5分)
DELIMITER $$ CREATE TRIGGER dele_s1 BEFORE DELETE ON S FOR EACH ROW BEGIN SIGNAL SQLSTATE '03100' SET MESSAGE_TEXT = '不能删除该表中的数据'; END $$
验证:
DELETE FROM s WHERE SNO = '10001';
(3) 为S表创建一个名为dele_s2的DELETE触发器,该触发器的作用是删除S表中的记录时删除SC表中该学生的选课纪录。触发器创建成功之后,删除S表中的记录,验证触发器是否正常工作(确认S表和SC表相关数据是否被删除)。(5分)
DELIMITER $$ CREATE TRIGGER dele_s2 AFTER DELETE ON S FOR EACH ROW BEGIN SET @SNO = OLD.SNO; DELETE FROM SC WHERE SNO = @SNO; END $$
验证:
DELETE FROM S WHERE SNO = '10001';
(4) 为S表创建一个名为update_s的UPDATE触发器,该触发器的作用是禁止更新S表中“sdept”字段的内容(更新不成功,并且提示“不能更新sdept字段”)。触发器创建成功之后,更新S表中“sdept”字段的内容,验证触发器是否正常工作。(5分)
DELIMITER $$ CREATE TRIGGER update_s BEFORE UPDATE ON S FOR EACH ROW BEGIN IF NEW.SDEPT != OLD.SDEPT THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '不能更新sdept字段'; END IF; END $$
验证:
UPDATE S SET SDEPT = 'CS' WHERE SNO = '1';
(5) 删除update_s触发器。(5分)
DROP TRIGGER update_s;
(6) 自己设计一个before update触发器和after update触发器,比较before 和after触发器的区别。(5分)
before触发器DELIMITER $$ CREATE TRIGGER update_s_before BEFORE UPDATE ON S FOR EACH ROW BEGIN IF NEW.SDEPT = 'CS' THEN SET NEW.SDEPT = 'IS'; END IF; END $$
after触发器
DELIMITER $$ CREATE TRIGGER update_s_after after UPDATE ON S FOR EACH ROW BEGIN IF NEW.SDEPT = 'CS' THEN SET NEW.SDEPT = 'IS'; END IF; END $$
after触发器在事务执行之后生效,上述代码中将学生表的系修改为IS,对于一个已经执行了update操作的事务,再将其修改回来系统会报错。故这种情况下的after触发器不能被创建。
验证:UPDATE S SET SDEPT = 'CS' WHERE SNO = '1';
(7) 创建一个新的课程成绩统计表 CAvgGrade(Cno, Snum, examSNum, avgGrade),分别表示课号,选该课程的学生人数,参加考试人数,该门课程的平均成绩。利用触发器实现如下的功能:当SC表中插入、删除或者更新某个人的成绩时,自动更新表CAvgGrade。注意SC表中的grade为NULL时表明该学生还未参加考试,计算平均成绩时不需要计算该成绩,但是grade为0即考试成绩为0时,计算平均成绩需要计算该学生成绩。(10分)
首先设计一个公共的存储过程,这个存储过程可供三个触发器调用,实现数据的插入CAvgGrade表中。
DELIMITER $$ CREATE PROCEDURE update_cavggrade(in in_cno INT) BEGIN DECLARE e_snum INT; DECLARE e_examsnum INT; DECLARE e_avggrade INT; SELECT COUNT(*) FROM sc WHERE cno = in_cno INTO e_snum; SELECT count(*) FROM sc WHERE cno = in_cno AND grade is not null INTO e_examsnum; SELECT AVG(grade) FROM sc WHERE cno = in_cno AND grade is not null INTO e_avggrade; IF in_cno IN (SELECT DISTINCT cno FROM cavggrade) THEN UPDATE cavggrade SET Snum = e_snum,examSNum = e_examsnum,avgGrade = e_avggrade WHERE cno = in_cno; ELSE INSERT INTO cavggrade VALUES(in_cno,e_snum,e_examsnum,e_avggrade); end IF; END$$
插入触发器:
DELIMITER $$ CREATE TRIGGER insert_sc AFTER INSERT ON SC FOR EACH ROW BEGIN call update_cavggrade(new.cno); END$$
更新触发器:
DELIMITER $$ CREATE TRIGGER update_sc AFTER UPDATE ON SC FOR EACH ROW BEGIN call update_cavggrade(new.cno); call update_cavggrade(old.cno); END$$
删除触发器
DELIMITER $$ CREATE TRIGGER delete_sc AFTER DELETE ON SC FOR EACH ROW BEGIN call update_cavggrade(old.cno); END$
- 创建一个员工表employee(eID, eName, salary),假设该表中有1000条员工数据,完成下列要求。(总计20分,每题10分)
(1) 为了协助本题自动生成1000条员工数据,创建一个自动生成员工ID的用户自定义函数generateEID。其中员工ID要求是一个8位的数字,前四位表示插入员工数据的当前年份,后四位按照从0001到9999的顺序增长。例如2015年插入的第一条数据是20150001,所有1000条员工ID分别是20150001-20151000。调用该函数实现自动插入1000条数据。(注意插入数据的时候员工姓名可以为任意值,工资是2000-5000之间的数字)
DELIMITER $$ CREATE PROCEDURE generateEID(in n int) BEGIN SET @x = 0; REPEAT SET @x = @x + 1; INSERT INTO employee VALUES(20210000 + @x,concat('ename',cast(@x as char(10))),floor(2000 + 3000*rand())); UNTIL @x >= n END REPEAT; END$$
执行
1. call generateEID(1000);
(2) 该公司计划为员工按照一定的规则涨工资,请使用游标创建一个存储过程,执行该存储过程完成本次工资调整。工资增长规则如下:
- 工资在3000元以下,每月涨300元;
- 工资在3000-4000元之间,每月涨200元;
- 工资大于或者等于4000元,每月涨50元;
DELIMITER $$ CREATE PROCEDURE salary_increase () BEGIN DECLARE e_salary int; DECLARE e_eid int; DECLARE done int; DECLARE c_name cursor for SELECT eID,salary from employee; DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1; OPEN c_name; FETCH c_name INTO e_eid,e_salary; REPEAT CASE WHEN e_salary < 3000 THEN UPDATE employee SET salary = salary + 300 WHERE eid = e_eid; WHEN e_salary >= 3000 and e_salary < 4000 THEN UPDATE employee SET salary = salary + 200 where eid = e_eid; ELSE UPDATE employee SET salary = salary + 50 where eid = e_eid; END CASE; FETCH c_name INTO e_eid,e_salary; UNTIL done END REPEAT; CLOSE c_name; END$$
执行
call salary_increase();
-
存储过程的创建和使用,以及存储过程和存储函数的区别
2021-06-20 15:28:21简单存储过程的创建和使用 --简单存储过程的创建和使用 create or replace procedure test(ids account.id%type) as begin update account set money = money + 100 where id = ids; commit; end; --查询一下 ... -
数据库实验5---存储过程和存储函数
2022-05-03 22:47:48存储过程和存储函数实验内容实验步骤及处理结果思考体会参考资料 实验内容 输入以下代码,创建存储过程stu_info,执行时通过输入姓名,可以查询该姓名的学生的各科成绩。 DELIMITER @@ CREATE PROCEDURE stu_info... -
实验5.1 存储过程的建立与使用
2014-05-16 14:24:49实验5.1 存储过程的建立与使用 一、实验目的 理解存储过程的概念、作用、建立和调用方法。 二、实验原理 使用CREATE PROCEDURE语句创建存储过程,ALTER PROCEDURE语句修改存储过程,DROP PROCEDURE语句删除... -
VBScript-Library:该存储库包含一些VBscript函数和过程。 它们可能会提高您的开发速度,因为您将(仅)使用...
2021-05-17 04:36:51VBScript_Library 该存储库包含一些VBscript函数和过程。 它们可能会提高您的开发速度,因为您将(仅)使用“构建基块”。 不要犹豫,提出新功能! -
Oracle经典自定义创建函数和存储过程
2010-07-30 10:51:05自定义函数不能被直接在Java中调用,可以通过存储过程调用函数的方法。本文采用实例的方法,介绍如何在Oracle数据库客户端PL/SQL中创建自定义函数,然后创建相应的存储过程调用该函数,最终实现一个实例计算。 -
Mysql之存储过程及函数
2022-02-10 21:38:07mysql 存储过程 -
netlify-functions-example:演示如何使用依赖项预先构建函数
2021-03-08 09:24:42此存储库用作示例,说明如何依赖于Netlify来压缩lambda函数。 完整的博客文章在上。 奇数 Netlify Dev(CLI)无法在纯静态站点上提供功能,因此我提供了一个极其基本的eleventy生成器 Netlify Dev(CLI)不允许访问... -
在ASP.NET中调用存储过程方法新解
2021-01-20 07:15:55在使用.NET的过程中,数据库访问是一个很重要的部分,特别是在B/S系统的构建过程中,数据库...与应用程序中的函数过程类似,存储过程可以通过名字来调用,而且它们同样有输入参数和输出参数。 根据返回值类型的不同,我 -
Node.js-and-Azure-Functions:由Packt发布的用于使用Node.js和Azure函数构建无服务器应用程序的代码存储库
2021-05-27 22:54:07使用Node.js和Azure功能构建无服务器应用程序 由Packt发布的用于使用Node.js和Azure函数构建无服务器应用程序的代码存储库 -
aws-sam-test:小型存储库,用于测试使用SAM创建Lambda函数
2021-04-04 07:31:13aws-sam-test 一个小型仓库,用于测试使用SAM CLI创建Lambda函数。 lambda_handler.py文件包含lambda函数。 template.yml文件是用于创建lambda函数的SAM模板文件。 requirements.txt文件是一个空的Python要求文件,... -
RuntimeCompiledCudafy:PoC 表明您可以在运行时构建 Cudafy 函数,从而允许用户定义函数的 GPU 加速
2021-06-14 13:56:18用法该存储库包含一个简单的示例,该示例使用 CodeDOM 来描述 Cudafy 函数,然后在运行时在 GPU 上组装和执行该函数。 鉴于您安装了适当的库和硬件,程序应该编译和运行而不会遇到任何大麻烦。 作为参考,这是从 ... -
Python-函数、文件与模块.doc
2022-05-07 11:13:54本文档主要讲述的是Python 函数、文件与模块,本文构造一个完整...引入了 Python 函数和模块,并展示了构建一个 Python 程序、将其存储在文件中以及通过命令行运行该程序的方法;希望本文档会给有需要的朋友带来帮助; -
使用Swift定义Amazon Lambda函数和Alexa自定义技能-Swift开发
2021-05-27 06:13:45Swift在Lambda上的内容此存储库包含以下两个示例:使用Swift定义Amazon Lambda函数,尤其是使用它来定义实现简单的“ Hello Swift On Lambda”的Amazon Lambda函数。此存储库包含一个示例有两件事:使用Swift定义... -
【程序员的自我修养 -- 链接、装载与库(三)】函数调用与栈(this指针、返回值传递&临时对象构建&栈、运行...
2022-01-23 12:19:34函数调用与栈(this指针、返回值传递&临时对象构建&栈、运行库与多线程、_main函数、系统调用与中断向量表、Win32、可变参数、大小端 -
【StyleGAN代码学习】StyleGAN损失函数与训练过程
2019-12-10 19:35:03完整StyleGAN笔记:...————————————————————————————————— 第二章 StyleGAN代码解读(下) 2.3 损失函数代码解读 StyleGAN的损失函数写在training/loss.p... -
MySQL 查询树结构、循环查询、查看函数、视图、存储过程
2021-01-18 19:44:22构建函数构建树查询函数:查询父级节点函数--在mysql中完成节点下的所有节点或节点上的所有父节点的查询--根据传入id查询所有父节点的idDROP FUNCTION IF EXISTS`getParList`;delimiter//CREATE FUNCTION `... -
DB2存储过程-基础教程
2013-04-23 17:23:47在本教程中,您学习了用于编写过程、用户定义函数和触发器的SQL Procedural Language。您学习了SQL Procedure Language 的所有基本要素,包括变量声明和赋值、语法和使用以及用于控制过程逻辑的流程的条件语句和迭代... -
gaussdb200 存储过程
2021-06-10 14:34:39声明部分:声明PL/SQL用到的变量,类型及游标,以及局部的存储过程和函数。 DECLARE 执行部分:过程及SQL语句,程序的主要部分。必选。 BEGIN 执行异常部分:错误处理。可选。 EXCEPTION 结束 END; / ... -
【MySQL】使用存储过程计算1到100的累计和
2019-07-24 11:38:05create procedure kaka(out count int) begin declare v1 int(11) default 1; declare v2 int(11) default 0; while v1 < 100 do set v2 = v2 + v1; set v1 = v1 + 1; end while;...... -
透彻理解高斯核函数背后的哲学思想与数学思想
2019-02-03 10:27:51仅仅计算测试数据与支持向量的点积,支持向量由SVM优化算法选择的特殊数据点。在此,作一个类比如下:一个人看过湖泊,河流,溪流,浅滩等,但从未见过大海。你怎么向这个人解释大海是什么?也许可以通过将海水中的... -
MYSQL数据库实验(存储过程与触发器)
2018-12-29 11:47:19个人配置说明:5.7.24 MySQL Community Server (GPL),环境CentOS 7 1.MYSQL 不支持语句触发(for each statement),只支持行触发(for each row,...理解,实现并逐渐熟悉存储过程的使用 存储过程(Stored Procedure)是... -
python中 map函数 的使用
2022-05-09 17:11:07python中 map函数 的使用