存储过程与触发器实验

实验6存储过程和触发器

1．实验目的

（1）掌握通过SQL Server管理平台和Transact-SQL语句CREATE PROCEDURE创建存储过程的方法和步骤。

（2）掌握使用Transact-SQL语句EXECUTE执行存储过程的方法。

（3）掌握通过SQL Server管理平台和Transact-SQL语句ALTER PROCEDURE修改存储过程的方法。

（4）掌握通过SQL Server管理平台和Transact-SQL语句DROP PROCEDURE删除存储过程的方法。

（5）掌握通过SQL Server管理平台和Transact-SQL语句CREATE TRIGGER创建触发器的方法和步骤。

（6）掌握引发触发器的方法。

（7）掌握使用SQL Server管理平台或Transact-SQL语句修改和删除触发器。

（8）掌握事务、命名事务的创建方法，了解不同类型的事务的处理情况。

2．实验内容及步骤

请先附加studentsdb数据库，然后完成以下实验。

（1）在查询设计器中输入以下代码，创建一个利用流控制语句的存储过程

letters_print，该存储过程能够显示个小写字母。

语句：

CREATE PROCEDURE letters_print

AS

DECLARE@count int

SET@count=0

WHILE@count<26

BEGIN

PRINT CHAR(ASCII('a')+@count)

SET@count=@count+1

END

exec letters_print

（2）输入以下代码，创建存储过程stu_info，执行时通过输入姓名，可以查询该姓名对应的学生的各科成绩。

云南大学软件学院实验报告

课程：数据库原理与实用技术实验学期：2014-2015学年第二学期任课教师：薛岗、朱艳萍专业：学号：姓名：成绩：

实验5 存储过程和触发器

一、实验目的

（1）理解存储过程的概念、了解存储过程的类型

（2）掌握创建存储过程的方法

（3）掌握执行存储过程的方法

（4）理解触发器的功能及工作原理。

（5）掌握创建、更改、删除触发器的方法。

二、实验内容

1、使用不带参数的存储过程

（1）创建一个存储过程，查询person表中所有不重复的职称。

（2）执行存储过程。

2、带输入参数的存储过程

（1）创建一个存储过程，按照姓名查询person表中的员工信息。

（2）执行存储过程，查询名为黎明的员工数据

3、带输入/输出参数的存储过程

（1）创建一个存储过程，使其能够根据员工姓名，查询员工工资。（考虑到员工不存在时给出提示信息）

（2）执行存储过程

4、使用触发器

（1）创建一个触发器trig_update，返回对person进行更新操作后，被更新的记录条数

（2）执行触发器

（3）修改触发器trig_update，除返回被更新的记录条数外，再返回学生的所有基本信息

5、使用触发器的两个特殊表：插入表（inserted）和删除表（deleted）。

（1）在person上创建触发器ins_del_sample，在插入、删除或更新操作后，分别从inserted表和deleted表中查询员工所有信息。

（请同学们在做删除操作时，注意备份）

（2）执行触发器。思考执行插入、删除和更新操作后返回的表有什么区别？

6、使用系统存储过程查看触发器（与存储过程的使用类似）（1）显示触发器trig_update的一般信息

实验六存储过程和触发器

实验六存储过程与触发器

⼀、⽬的与要求

1.掌握编写数据库存储过程的⽅法。

2.掌握建⽴数据库触发器的⽅法,通过实验观察触发器的作⽤与触发条件设置

等相关操作。

⼆、实验准备

1.了解编写存储过程与调⽤的T-SQL语法;

2.了解触发器的作⽤;

3.了解编写触发器的T-SQL语法。

三、实验内容

(⼀)存储过程

在studentdb数据库中建⽴存储过程getPractice,查询指定院系(名称)(作为存储过程的输⼊参数)中参与“实践”课程学习的所有学⽣学号、姓名、所学课程编号与课程名称,若院系不存在,返回提⽰信息。

提⽰:D_Info表中存储了院系代码D_ID,⽽St_Info表中学号字段St_ID的前两位与之对应,则D_Info表与St_Info表之间的联系通过这两个字段的运算构成连接条件。

1.分别执⾏存储过程getPractice,查询“法学院”与“材料科学与⼯程学院”

的学⽣中参与“实践”课程的所有学⽣学号、姓名、所学课程编号与课程名称。

create procedure getPractice

@D_Name varchar(30)

output

as

begin

if not exists

(select *

from D_Info

where D_Name= @D_Name

)

print '对不起,该院系不存在'

else

select st_info、St_ID,C_Info、C_No,C_Name

from s_c_info inner join st_info on st_info、St_ID=s_c_info、st_id

实验九存储过程和触发器

实验内容

在已建立的TSGL数据库的基础上，按如下要求对数据库进行操作，按同前的命名要求保存操作代码和截图。

1. 利用TSGL数据库中的TREADER表和TBOOK表和historytable表，编写一无参存储过程用于查询每个读者的借阅历史，然后调用该存储过程。

2. 编写一存储过程，根据TSGL数据库的三个表查询指定读者（指定借书证号或指定姓名等）当前的借书情况。

3. 利用TSGL数据库中的TREADER表、TBOOK表及historytable表创建一存储过程，查询指定图书（ISBN或书名）的借阅历史。该存储过程在参数中使用模糊查询，如果没有提供参数，则使用预设的默认值。

4. 编写一存储过程，统计指定图书在给定时间段内的借阅次数，存储过程中使用输入和输出参数。

5. 编写一存储过程，在TSGL数据库的TREADER表上声明并打开一个游标。通过游标读取所需信息。

6. 创建加密过程，使用sp_helptext系统存储过程获得关于加密的存储过程的信息，然后尝试直接从syscomment表中获取关于该过程的信息。

7. 对TSGL数据库中的三个表分别创建添加、修改、删除一条记录的存储过程。

8. 创建触发器，当向LEND表中插入一条记录时，将TREADER表中该学生的借书数加1，将TBOOK表中该书的库存量减1。

9. 创建触发器，当修改TREADER表中的借书证号时，同时也要将LEND表中的借书证号修改成相应的借书证号（假设TREADER表和LEND表之间没有定义外键约束）

实验五存储过程和触发器的使用

【目的要求】

1、了解存储过程的基本概念和类型。

2、了解创建存储过程的T-SQL语句的基本语法。

3、了解查看、执行、修改和删除存储过程的T-SQL命令的用法。

4、了解触发器的基本概念和类型。

5、了解创建触发器的T-SQL语句的基本语法。

6、了解查看、修改和删除存储过程的T-SQL命令的用法。

【实验内容】

内容一：存储过程的使用

一、数据需求分析

存储过程是一种数据库对象，为了实现某个特定任务，将一组预编译的SQL语句以一个存储单元的形式存储在服务器上，供用户调用，自动完成需要预先执行的任务。存储过程在第一次执行时进行编译，然后将编译好的代码保存在高速缓存中便于以后调用，提高了代码的执行效率。

二、内容要点分析

1、SQL SERVER支持五种类型的存储过程：系统存储过程、本地存储过程、临时存储过程、远程存储过程和扩展存储过程。其中，系统存储过程是由系统提供的存储过程，可以作为命令执行各种操作。系统存储过程定义在系统数据库master中，其前缀是sp_。本地存储过程是指在用户数据库中创建的存储过程，这种存储过程完成特定数据库操作任务，不能以sp_为前缀。

2、只能在当前数据库中创建存储过程。

3、创建存储过程时，应指定所有输入参数和向调用过程或批处理返回的输出参数、执行数据库操作的编程语句和返回至调用过程或批处理以表明成功或失败的状态值。

4、创建存储过程的T-SQL语句

CREATE PROC[EDURE] 存储过程名称

[{ @参数名称数据类型 }] [,…n]

[WITH

{ RECOMPILE|ENCRYPTION }]

存储过程与触发器实验报告

一、引言

存储过程和触发器是数据库中常用的高级功能，它们能够提高数据库的性能、数据一致性和安全性。本实验报告将详细介绍存储过程和触发器的概念、用途以及实际应用。

二、存储过程

2.1 概念

存储过程是一组预定义的SQL语句集合，它们被命名并存储在数据库中，可以作为一个单元来调用和执行。存储过程可以接受参数，并返回一个或多个结果集。存储过程可以在应用程序层面减少网络传输，提高数据库性能。

2.2 用途

存储过程的应用非常广泛，主要用于以下几个方面： 1. 数据库业务逻辑封装：将复杂的业务逻辑封装到存储过程中，使应用程序只需调用存储过程而不需要编写大量的SQL语句，简化应用程序的开发。 2. 数据库性能优化：通过存储过程可以减少网络传输，提高数据库性能。 3. 数据库安全性：通过存储过程，可以实现对数据库的访问权限控制，提高数据库的安全性。

2.3 示例

下面以一个简单的示例来说明存储过程的使用。

2.3.1 创建存储过程

CREATE PROCEDURE `get_employee_by_department` (IN department_id INT)

BEGIN

SELECT * FROM employee WHERE department_id = department_id;

END

2.3.2 调用存储过程

CALL `get_employee_by_department`(1);

2.4 优化技巧

为了进一步提高存储过程的性能，可以采用以下优化技巧： 1. 减少存储过程的参数：过多的参数会增加网络传输的负担，应尽量减少存储过程的参数数量。 2. 避免长时间占用资源：存储过程应尽量快速执行，避免长时间占用数据库资源。

实验七存储过程与触发器

实验七存储过程与触发器

一、创建一个名为“proc_1”的存储过程，用于查看学生表的所有信息。然后调用该存储过程。

二、创建一个名为“proc_2”的存储过程，用于向学生表的所有字段添加一条记录，记录

内容由调用时决定。然后调用该存储过程。

三、创建一个名为“proc_3”的存储过程，用于删除学生表中指定学号的记录，具体学号

由调用时决定。然后调用该存储过程。

四、修改存储过程“proc_3”，用于查询不小于指定成绩的学生的基本信息，具体成绩由

调用时决定。

五、选做题：

创建一个名为“proc_4”的存储过程，用于求一个3位整数的反序数。例如123的反序数为321。 create proc proc_4 @a int as

declare @b int,@c int,@t int,@s int begin

set @t=@a % 10

select @b=@a/10,@b=@b % 10 set @c=@a/100

set @s=@t*100+@b*10+@c select @s end go

exec proc_4 123

六、创建一个名为“trig_1”的触发器，当向学生表添加记录时，该触发器自动显示学生

表的所有信息。

七、修改名为“trig_1”触发器，当试图向学生表添加、修改或删除记录时，该触发器自

动显示如下信息：“对不起，你无权进行更新操作！”。

八、修改“trig_1”的触发器，当向学生表修改记录时，该触发器自动显示修改前(deleted)

和修改后(inserted)的记录。九、选做题：

实验8 存储过程和触发器

一、实验目的

1．掌握通过企业管理器和Transact —SQL语句和CREATE PRODCEDURE创建存储过程的方法和步骤。

2．掌握使用企业管理器和Transact —SQL语句和EXECUTE执行存储过程的方法。

3．掌握通过企业管理器和Transact —SQL语句CREATE TRIGGER创建触发器的方法和步骤。

4．掌握引发存储器的方法。

5．掌握使用系统存储过程管理存储过程和触发器的方法。

6．掌握事物，命名事物的创建方法，不同类型的事务的处理情况。

二、实验准备

1．了解存储过程的基本概念和类型。

2．了解创建存储过程的Transact —SQL语句的基本语法。

3．了解查看、执行、修改和删除存储过程的基本语法。

4．了解触发器的基本概念和类型。

5．了解创建触发器的Transact —SQL语句基本用法。

6．了解查看、修改和删除触发器的Transact —SQL语句的用法。

7．了解创建事务，处理事务的方法和过程。

8．了解锁机制。

三、实验内容和步骤

1．在查询分析器中输入以下代码，创建一个利用流控制语句的存储过程lletters_print,该存储器能显示26个小写字母。

单击查询分析器的“执行查询”按钮，查看studentsdb数据库的存储过程是否有letters_print.

使用EXECUTE命令执行letters_print存储过程。

CREATE PROCEDURE letter_print

AS

DECLARE @count int

SET @count=0

WHILE @count<26

实验存储过程和触发器

实验一存储过程的创建和使用

【实验目的】

1.掌握存储过程的概念，了解存储过程的类型

2.掌握创建各种存储过程的方法

3.掌握执行存储过程的方法。

4.掌握查看，修改，删除存储过程的方法

【实验内容】

1.在SSMS图形化界面下创建对表Customers进行插入，修改和删除的3个存储过程:insertCustomers、updateCustomers、deleteCustomers.

2.在查询分析其中创建一个存储过程，要求输入作者的姓和名，如果存在，则返回这个作者以及作者所出版的书的信息，否则给出相应的提示信息。

3.用系统存储过程查看刚创建的存储过程的信息。

4.删除存储过程

【实验主要步骤】

1.在SSMS图形化界面下创建对表Customers进行插入，修改和删除的3个存储过程:insertCustomers、updateCustomers、deleteCustomers.

2.在查询分析其中创建一个存储过程，要求输入作者的姓和名，如果存在，则返回这个作者以及作者所出版的书的信息，否则给出相应的提示信息。

3.用系统存储过程查看刚创建的存储过程的信息。

4.删除存储过程

实验二触发器的创建和使用【实验目的】

1.理解触发器的概念与类型。

2.理解触发器的功能及工作原理。

3.掌握创建、修改和删除触发器的方法。

4.掌握利用触发器维护数据完整性的方法。

【实验内容】

触发器是一种实施复杂数据完整性的特殊存储过程，在对表或视图执行UPDATE、INSERT、DELETE语句时自动触发执行，以防止对数据不正确、未授权或不一致的修改。

实验五创建存储过程和触发器

一、实验目的

1．通过对常用系统存储过程的使用，了解存储过程的类型；通过创建和执行存储过程，了解存储过程的基本概念，掌握使用企业管理器及查询分析器执行T-SQL语句创建存储过程。

2．通过创建触发器，了解触发器的基本概念，理解触发器的功能，掌握使用企业管理器及查询分析器执行T-SQL语句创建触发器。

二、实验要求

1．实验前做好上机实验的准备，针对实验内容，认真复习与本次实验有关的知识，完成实验内容的预习准备工作；

2．能认真独立完成实验内容；

3．实验后做好实验总结，根据实验情况完成总结报告。

三、实验学时

2学时

四、实验内容

1、使用sp_helptext查看byroyalty存储过程的文本，该存储过程在数据库pubs中。

2、在实验二创建的factory数据库中执行以下操作：

（1）创建一个为worker表添加职工记录的存储过程addworker。执行并验证存储过程的正确性。最后删除该存储过程。

（2）在depart表上创建一个触发器depart_update，当更改部门号时同步更改worker 表中对应的部门号。执行并验证触发器的正确性。最后删除该触发器。

（3）在worker表上创建一个触发器worker_delete，当删除职工记录时同步删除salary表中对应职工的工资记录。执行并验证触发器的正确性。最后删除该触发器。

实验7 存储过程和触发器

二、背景知识

（1）存储过程概述：

存储过程是SQL语句和可选控制流程语句的预编译集合，它以一个名称存储并作为一个单元处理。存储过程存储在数据库内，可由应用程序通过一个调用执行。存储过程可以接受参数，输出参数，返回单个或多个结果集，也可以返回一个值。

存储过程具有以下优点：

●可以在一个存储过程中，执行一系列SQL语句。

●存储过程可以嵌套调用，以简化语句的描述

●存储过程在创建时即在服务器上进行编译，所以执行起来比单个SQL语句快，并且能减少网络通信的负担。

●存储过程中的参数可以具有默认值，默认值必须为常量或者NULL。

（2）触发器概述：

触发器是一种特殊类型的存储过程，常用作数据完整性约束。一个系统有三类触发器，分别为：INSERT触发器、UPDATE触发器和DELETE触发器。通常INSERT、UPDATE触发器被用来检查插入或者修改后的数据是否满足要求。DELETE触发器一般用作级联删除或记录外键的删除操作。当对指定表执行INSERT、UPDATE、DELETE时，相应的触发器会自动执行。触发器可以包含复杂的T-SQL语句。一个表可以有多个触发器。

在触发器执行的时候，会产生两个临时表：INSERTed表和deleted 表。它们的结构和触发器所在的表的结构相同，SQLSERVER自动创建和管理这些表。

在对触发器的表进行操作时，系统执行过程如下：

●执行INSERT操作，插入到触发器表中的新行同时被插入到INSERTed表中。

●执行DELETE操作，从触发器表中删除的行同时被插入到deleted 表中。

存储过程与触发器实验报告

本实验旨在探究存储过程与触发器的概念、作用、使用范围和创建过程，并且通过编写相关的示例代码来展示它们的实际应用。

一、实验原理

1. 存储过程

存储过程是一组预定义好的 SQL 语句，可以重复使用并且可

以直接被调用。它类似于程序中的函数，可以接受参数、返回值、流程控制等。

2. 触发器

触发器是与数据库表相关的事件响应机制，可以在数据库表上定义一些触发条件，当满足这些条件时就会触发执行一些操作，比如插入、更新或删除数据。

二、实验步骤

1. 存储过程的创建与使用

（1）创建一个用于统计某个用户的订单数量的存储过程。

```

DELIMITER //

CREATE PROCEDURE `getOrderCount`(IN p_userid INT, OUT

p_count INT)

BEGIN

SELECT COUNT(*) INTO p_count FROM orders WHERE user_id = p_userid;

END//

DELIMITER ;

```

（2）调用这个存储过程，并输出结果。

```

CALL getOrderCount(123, @count);

SELECT @count AS 'order_count';

```

2. 触发器的创建与使用

（1）创建一个在用户表中插入新记录时自动生成一个账户记录的触发器。

```

DELIMITER //

CREATE TRIGGER `insert_user_account` AFTER INSERT ON `users` FOR EACH ROW

实验五触发器、存储过程操作实验

本实验需要2学时。

请大家先根据“触发器.doc”文档完成相关操作，再进行本次实验。

介绍完“存储过程”后，需上交本次实验报告。

一、实验目的

（1）掌握SQL Server中的触发器的使用方法；

（2）掌握存储过程的操作方法。

二、实验内容

1. 创建、查看、修改和删除触发器。

2. 创建、查看、修改和删除存储过程。

三、实验方法

1. 触发器的操作

（1）建立触发器

方法一：使用企业管理器

首先，打开企业管理器，定位数据库并找到要创建触发器的表；

然后，右击该表名，在弹出的快捷菜单中选择“设计表”，在打开的“设计表”窗口中单击按钮，打开如图1所示窗口。

图1 触发器属性窗口

最后，在触发器属性窗口中输入触发器的内容，并单击“确定”按钮。

方法二：使用CREATE TRIGGER语句

语法：

CREATE TRIGGER <触发器名>

ON { <表名> | <视图名>}

{ FOR | INSTEAD OF | AFTER}

{ [UPDATE] [,] [INSERT] [,] [DELETE]}

AS

<SQL 语句块>

（2）查看触发器

方法一：使用企业管理器

方法二：使用T-SQL语句

- sp_help <触发器名>

- sp_helptext <触发器名>

- sp_depends <触发器名>

（3）修改触发器

方法一：使用企业管理器

方法二：使用ALTER TRIGGER语句

语法：

ALTER TRIGGER <触发器名>

实验六存储过程和触发器

实验六存储过程和触发器

１．实验⽬的

(1) 掌握存储过程和触发器的基本概念和功能

(2) 掌握创建,管理存储过程的⽅法

(3) 掌握创建,管理触发器的⽅法

２．实验内容及步骤

(1) 利⽤SQL Server Management Studio创建⼀个存储过程ProcNum,查询每个班级中学⽣的⼈数,按班级号升序排序.

在查询编辑器的存储过程模板中输⼊如下创建存储过程的代码并执⾏.

USE teaching

GO

CREATE PROCEDURE ProcNum AS

SELECT classno,COUNT(*)AS number FROM student

GROUP BY classno ORDER BY classno ASC

GO

EXEC ProcNum

(2) 利⽤Transact-SQL语句创建⼀个带有参数的存储过程ProcInsert,向score 表插⼊⼀条选课记录,并查询该学⽣的姓名,选课的所有课程名称,平时成绩和期末成绩.

<1> 在查询编辑器输⼊如下创建存储过程的代码并执⾏.

USE teaching

GO

CREATE PROCEDURE ProcInsert(@sno NCHAR(10),@cno NCHAR(6),@usually NUMERIC(6,2),@final NUMERIC(6,2))AS

INSERT INTO score VALUES (@sno,@cno,@usually,@final)

SELECT sname,cname,usually,final

实验6 存储过程和触发器

一、实验目的

1、加深和巩固对存储过程和触发器概念的理解。

2、掌握触发器的简单应用。

3、掌握存储过程的简单应用。

二、实验容

一）存储过程：

1. 创建一存储过程，求l+2+3+…+n，并打印结果。

CREATE PROCEDURE addresult

AS

DECLARE n int=10,/*最后一个数*/

i int=0,

result int=0 /*结果*/

BEGIN

WHILE(i<=n)

BEGIN

SET result=result+i

SET i=i+1

END

PRINT'1+2+3+...+n的结果是:'

PRINT result

RETURN(result)

END

GO

2．调用上面的addresult存储过程，打印l十2+3+…+10的结果。EXEC addresult

3. 修改上述存储过程为addresult1,使得n为输入参数，其具体值由用户调用此存储过程时指定。

CREATE PROCEDURE addresult1

n int=10 /*最后一个数*/

AS

DECLARE i int=0,

result int=0 /*结果*/

BEGIN

WHILE(i<=n)

BEGIN

SET result=result+i

SET i=i+1

END

PRINT'1+2+3+...+n的结果是:'

PRINT result

RETURN(result)

END

GO

4. 调用上面修改后的addresult1存储过程，打印l+2+3+…+100的结果。

EXEC addresult1 100

实验六存储过程和触发器

实验六存储过程和触发器

一、目的与要求

1.掌握编写数据库存储过程的方法。

2.掌握建立数据库触发器的方法，通过实验观察触发器的作用和触发条件设置

等相关操作。

二、实验准备

1.了解编写存储过程和调用的T-SQL语法；

2.了解触发器的作用；

3.了解编写触发器的T-SQL语法。

三、实验内容

(一)存储过程

在studentdb数据库中建立存储过程getPractice，查询指定院系(名称)(作为存储过程的输入参数)中参与“实践”课程学习的所有学生学号、姓名、所学课程编号和课程名称，若院系不存在，返回提示信息。

提示：D_Info表中存储了院系代码D_ID，而St_Info表中学号字段St_ID的前两位与之对应，则D_Info表与St_Info表之间的联系通过这两个字段的运算构成连接条件。

1.分别执行存储过程getPractice，查询“法学院”和“材料科学与工程学院”

的学生中参与“实践”课程的所有学生学号、姓名、所学课程编号和课程

名称。

create procedure getPractice

@d_name varchar(30)

as

select

st_info.st_id,st_info.st_name,s_c_info.c_no,c_info.c_name from st_info,d_info,s_c_info,c_info

where d_info.d_name=@d_name and st_info.st_id=s_c_info.st_id

and d_info.d_id=left(st_info.st_id,2)