第6章_存储过程与触发器练习题
《数据库原理与应用》实验存储过程和触发器（部分答案）
实验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_printASDECLARE@count intSET@count=0WHILE@count<26BEGINPRINT CHAR(ASCII('a')+@count)SET@count=@count+1ENDexec letters_print（2）输入以下代码，创建存储过程stu_info，执行时通过输入姓名，可以查询该姓名对应的学生的各科成绩。
语句：create proc stu_info@name char(10)asbeginSELECT姓名,g.课程编号,分数FROM dbo.student_info s JOIN grade gON s.学号=g.学号WHERE s.姓名=@nameEndexec stu_info'马东'（3）使用系统存储过程sp_rename将存储过程stu_grade更名为stu_g。
储存过程(答案)
单选题
1、将具有特定功能的一段SQL语句(多于一条)在数据库服务器上进行预先定义并编译，以
供应用程序调用，该段SQL程序可被定义为（D）
A、事物
B、触发器
C、视图
D、存储过程
2、以下对存储过程的叙述中，不正确的是（C）
A、存储过程可以定义变量
B、存储过程是一组为了完成特定功能的SQL语句组成的程序
C、存储过程不能嵌套调用
D、存储过程可以一次编译，多次执行
3、关于存储过程说法错误的是（C）
A、方便用户完成某些功能
B、用户存储过程方便用户批量执行T-SQL命令
C、用户存储过程不能调用系统存储过程
D、应用程序可以调用用户存储过程
4、存储过程的修改不能采用（D）。
A、界面方式修改命令方式创建的存储过程
B、ALTER PROCEDURE
C、先删除再创建
D、C REATE PROCEDURE
5、关于存储过程的描述正确的一项是(C)。
A、存储过程存在于内存中，每次重新启动DBMS时，便，会自动消失
B、存储过程在每次调用时都会被编译-次
C、存储过程可以包含输入和输出参数，增加了调用时的灵活性
D.执行一次存储过程所花的时间，比执行相同的SQL批处理要长
判断题
6、存储过程未存在服务器注册（错）
7、相较于用户自定义存储过程，系统存储过程更加的自由（错）
8、用户定义的存储过程是指由数据库管理系统所提供的存储过程（错）
9、存储过程可以带参数，从而增加调用灵活性（对）
10、存储过程一旦创建，其脚本就被加密，不能再次编辑修改（错）。
6、视图、存储过程、函数、游标与触发器
--创建带输入参数的存储过程 if exists(select name from sysobjects where name='pro_name' and type='p') drop procedure pro_name Go create procedure pro_name @vempno int as declare @v_name varchar(10),@v_sal decimal(10,2) begin begin try select @v_name=ename,@v_sal=sal from emp where empno=@vempno if @v_sal<2500 print '工资超过2500' else print '工资少于2500' end try begin catch print '错误号:'+cast(@@error as varchar(10)) print '错误内容:'+error_message() end catch end ----使用存储过程 pro_name 7369
2.2,存储过程的分类
用户自定义的存储过程:最主要的存储过 程 系统存储过程:sp_前缀,系统预定义 扩展存储过程:保存在DLL动态链接库中并 从动态链接库中执行的C++程序代码,用于 扩展SQLSERVER2005性能,以字符xp_开 头,通常与其它系统存储过程一起使用通 过程序集调用.
2.3,存储过程的设计规则
1.2.2,索引视图
--创建各部门人数的视图 drop view v_countOfDept go create view v_countOfDept WITH SCHEMABINDING as SELECT EMP.deptno,count_big(*) empcount FROM dbo.EMP group by emp.deptno --创建聚合索引 CREATE UNIQUE CLUSTERED INDEX i_v_countOfDept_deptno ON v_countOfDept(deptno) 注意: (1)创建索引视图,必须拥有唯一聚合索引,如果创建聚合索引,带有聚合函数的基础视 图必须使用WITH SCHEMABINDING ,group by以及count_big函数 (2)使用索引视图能提高数据库效率 (3)如果视图引用任何非确定性函数,则不能在视图上创建聚集索引
SQL Server存储过程与触发器课堂练习及答案
1.创建一个存储过程，显示所有价格在15美元以下的书的书名，类型，价格。
CREATE PROCEDURE show_titleASSELECT title,type,priceFROM titlesWHERE price < 15GOEXEC show_title2.把价格作为参数，创建一个能显示在某两个指定价格之间的书的书名，类型，价格。
CREATE PROCEDURE show_title2@price1 money,@price2 moneyASSELECT title,type,priceFROM titlesWHERE price between @price1 and @price2GOshow_title2 12,203.使用OUTPUT参数，创建一个计算圆柱体体积的存储过程。
并执行它。
CREATE PROCEDURE comp_area@r smallint,@h smallint,@result decimal(10,2) OUTPUTASSET @result = PI()*SQUARE(@r)* @hGODECLARE @answer decimal(10,2)EXECUTE comp_area 2,3, @answer OUTPUTSELECT 'The result is: ', @answer4.A) 建立price_change表，准备用来存放书的价格变化信息，有以下几列：title_id,type, old_price, new_price, change_date, operator。
B) 建立一个更新触发器，一旦titles表发生更新，立即把相关信息存放到price_change 表中。
create table price_change(title_id varchar(20),type varchar(20),old_price money,new_price money,change_date datetime,operator varchar(20))gocreate trigger tri_priceon titlesfor updateasinsert into price_changeselect ,,,,getdate(),user_name()from deleted o JOIN inserted nON =goupdate titles set price = price*5.修改练习4，使得只有当price列被更新时，才会触发触发器。
存储过程、函数与触发器操作答案
《存储过程、函数与触发器操作》实验一、实验目的与要求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，并定义两个个变量存储执行返回代码。
第6章 Transact-SQL简介、存储过程和触发器
4、保证系统的安全性。
6.2.1存储过程的创建
1．使用T-SQL语句创建存储过程
创建存储过程使用CREATE PROC [EDURE]语 句。 语法格式如下：
CREATE PROCEDURE 存储过程名 [{@参数名 数据类型}[=default][OUTPUT] ][,…n] AS SQL语句
1 BEGIN…END语句块 BEGIN和END用来定义语句块，必须成 对出现。它将多个SQL语句括起来，相 当于一个单一语句，其语法格式如下。
BEGIN 语句1或语句块1 语句2或语句块2 … END
2 IF...ELSE语句 IF…ELSE语句用来实现选择结构，其 语法格式如下。 IF 布尔表达式 {语句1或语句块1 } [ELSE {语句2或语句块2} ]
PRINT @x PRINT @y PRINT @z
执行结果为 40 30 10
例：计算1+2+3+…+100的和
DECLARE @i int，@sum int SET @i=1 SET @sum=0 WHILE @i<=100 BEGIN SET @sum= @sum+ @i SET @i= @i +1 END PRINT @sum
3 WHILE语句 WHILE语句用来实现循环结构，其语法 格式如下：
WHILE 逻辑表达式 语句块
当逻辑表达式为真时，执行循环体，直 到逻辑表达式为假。 BREAK语句退出WHILE循环， CONTINUE语句跳过语句块中的所有其 他语句，开始下一次循环。
例：若IF条件为真或为假时要执行的语句只有一条 （默认时，一条语句就是一个语句块），则可以 不使用BEGIN…END。 DECLARE @x int,@y int,@z int SET @x=40 SET @y=30 IF(@x > @y) SET @z= @x - @y ELSE 执行结果为 SET @z= @y - @x 0 SET @x=0 30 PRINT @x PRINT @y 10 PRINT @z
数据库存储过程练习附答案
存储过程、触发器练习1、在学生选课数据库中，创建一存储过程deptmale，查询指定系的男生人数，其中系为输入参数，人数为输出参数。
create proc p_dept@dept char(20),@mannum int outputasselect @allcre=count(sno) from studentwhere sdept=@dept and ssex='男'declare @num intexec p_dept '计算机系',@num outputprint @num2、在s_c数据库中，创建一个存储过程totalcredit,根据输入的学生姓名，计算其总学分。
(使用输出参数)。
并执行该存储过程。
create proc p_cou@name char(10),@allcre int outputasselect @allcre=sum(ccredit)from student,course,scwhere student.sno=sc.sno and o=oand sname=@name group by sc.snodeclare @asum intexec p_cou '刘晨',@asum outputprint @asum3、创建一更新触发器upd_grade，设置sc表的grade字段不能被更新，并显示信息“学生成绩不能被修改，请与教务处联系”。
CREATE TRIGGER mes_scON scFOR UPDATEASIF UPDATE（grade）BEGINROLLBACK TRANPRINT '学生成绩不能被修改，请与教务处联系'END4、创建一个insert触发器uninsertstu，当在student表中插入一条新纪录时，如果是“计算机系”的学生，则撤销该插入操作，并返回“此系人数已满，不能再添加”信息。
触发器练习
触发器练习（一）1、画出图题5-1所示的RS 触发器输出端Q 、Q 端的波形，输入端S 与R 的波形如图所示。
（设Q 初始状态为0）S RSRSRQQ....图题5-12、画出图题5-2所示的RS 触发器输出端Q 、Q 端的波形，输入端S 与R 的波形如图所示。
（设Q 初始状态为0）S RS RQQ...SR....图题5-23、画出图题5-3所示的同步RS 触发器输出端Q 、Q 端的波形，输入端S 、R 与CLK 的波形如图所示。
（设Q 初始状态为0）C1S RS RQQ....CLKS RCLK...图题5-34、画出图题5-4所示的同步D 触发器输出Q 端的波形，输入端D 与CLK 的波形如图所示。
（设Q 初始状态为0）C1DDQQ....CLKDCLK..图题5-45、若在图5-5电路中的CP 、S 、R 输入端，加入如图4.27所示波形的信号，试画出其 Q 和Q端波形，设初态Q =0。
SRCP触发器练习（二）1、画出图题5-6所示的同步JK 触发器输出Q 端的波形，输入端J 、K 与CLK 的波形如图所示。
（设Q 初始状态为0）J KQQ..CLKJKCLK ......C11J 1K..图题5-62、画出图题5-6所示的边沿触发D 触发器输出端Q 端的波形，输入端D 与CLK的波形如图所示。
（设Q 初始状态为0）C11D D QQ....CLKDCLK...D QQ....CLKDCLK...C11D （1）（2）3、画出图题5-7所示的边沿D 触发器输出Q 端的波形，CLK 的波形如图所示。
（设Q 初始状态为0）C11D Q 1CLK....CLK.1C11D Q 2CLK .CLK .图题5-74、画出图题5-8所示的JK 触发器输出Q 端的波形，输入端J 、K 与CLK 的波形如图所示。
（设Q 初始状态为0）J KQQ....CLKJ KCLK ...C11J 1KJ KCLK ......图题5-85、试画出图题5-9所示T 触发器输出Q 端的波形，输入端CLK 的波形如图所示。
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
有教师表(教师号，教师名，职称，基本工资)，其中基本工资的取值与教师职称有关。
实现这个约束的可行方案是( )。
A 在教师表上定义一个视图B 在教师表上定义一个存储过程C 在教师表上定义插入和修改操作的触发器D 在教师表上定义一个标量函数参考答案C在SQL SERVER中，执行带参数的过程，正确的方法为（）。
A 过程名参数B 过程名（参数）C 过程名=参数D ABC均可参考答案A在SQL SERVER服务器上，存储过程是一组预先定义并（）的Transact-SQL语句。
A 保存B 解释C 编译D 编写参考答案C在SQL Server中，触发器不具有（）类型。
A INSERT触发器B UPDATE触发器C DELETE触发器D SELECT触发器参考答案D（）允许用户定义一组操作，这些操作通过对指定的表进行删除、插入和更新命令来执行或触发。
A 存储过程B 规则C 触发器D 索引参考答案C为了使用输出参数，需要在CREATE PROCEDURE语句中指定关键字( )。
A OPTIONB OUTPUTC CHECKD DEFAULT参考答案B下列( )语句用于创建触发器。
A CREATE PROCEDUREB CREATE TRIGGERC ALTER TRIGGERD DROP TRIGGER参考答案B下列( )语句用于删除触发器。
A CREATE PROCEDUREB CREATE TRIGGERC ALTER TRIGGERD DROP TRIGGER参考答案D下列( )语句用于删除存储过程。
A CREATE PROCEDUREB CREATE TABLEC DROP PROCEDURED 其他参考答案C下列( )语句用于创建存储过程。
A CREATE PROCEDUREB CREATE TABLEC DROP PROCEDURED 其他参考答案Asp_help属于哪一种存储过程（）？A 系统存储过程B 用户定义存储过程C 扩展存储过程D 其他参考答案A以下语句创建的触发器是当对表A进行（）操作时触发。
CREATE TRIGGER ABC ON 表AFOR INSERT, UPDATE, DELETEAS……A 只是修改B 只是插入C 只是删除D 修改，插入，删除参考答案D（）允许用户定义一组操作，这些操作通过对指定的表进行删除、插入和更新命令来执行或触发。
A 存储过程B 视图C 触发器D 索引参考答案C下列可以查看表的行数以及表使用的存储空间信息的系统存储过程是（）。
A sq_spaceusedB sq_dependsC sq_helpD sq_rename参考答案A以下哪个不是存储过程的优点（）A 实现模块化编程，一个存储过程可以被多个用户共享和重用。
B 可以加快程序的运行速度。
C 可以增加网络的流量。
D 可以提高数据库的安全性。
参考答案C以下哪个不是存储过程的优点（）A 实现模块化编程，一个存储过程可以被多个用户共享和重用。
B 可以加快程序的运行速度。
C 可以增加网络的流量。
D 可以提高数据库的安全性。
参考答案C替代触发器（instead of）是在触发触发器的修改操作（）执行。
A 执行后B 之前C 停止执行时D 同时参考答案B以下不属于存储过程特点的是（）。
A 在删除所涉及表时，同时被删除B 代码执行效率高C 数据查询效率高D 模块化编程参考答案A以下不属于触发器特点的是（）。
A 基于一个表创建，可以针对多个表进行操作B 被触发自动执行C 可以带参数执行D 可以实施更复杂的数据完整性约束参考答案C以下（）不是触发触发器的操作。
A SELECTB UPDATEC DELETED INSERT参考答案A关于存储过程和触发器的说法，正确的是（）。
A 都是SQL Server数据库对象B 都可以为用户直接调用C 都可以带参数D 删除表时，都被自动删除参考答案A如果需要在插入表的记录时自动执行一些操作，常用的是（）。
A 存储过程B 函数C 触发器D 存储过程与函数参考答案C对SQL Server中的存储过程，下列说法中正确的是：存储过程（）。
A 不能有输入参数B 没有返回值C 可以自动被执行D 可以嵌套使用参考答案D存储过程经过了一次创建以后，可以被调用（）次。
A 1B 2C 255D 无数参考答案D以下（）不是存储过程的优点。
A 执行速度快B 模块化的设计C 会自动被触发D 保证系统的安全性参考答案C下面关于触发器的描述，错误的是( )。
A 触发器是一种特殊的存储过程，用户可以直接调用B 触发器表和deleted 表没有共同记录C 触发器可以用来定义比CHECK 约束更复杂的规则D 删除触发器可以使用DROP TRIGGER命令，也可以使用企业管理器参考答案ASQL Server为每个触发器创建了两个临时表，它们是（）。
A Updated 和DeletedB Inserted 和DeletedC Updated 和InsertedD Updated 和Selected参考答案B在SQL语言中，建立存储过程的命令是（）。
A CREATE PROCEDUREB CREATE RULEC CREATE DURED CREATE FILE参考答案A已定义存储过程AB，带有一个参数@stname varchar(20)，正确的执行方法为( )。
A EXEC AB ˈ吴小雨ˈB EXEC AB =ˈ吴小雨ˈC EXEC AB (吴小雨)D 其他3种都可以参考答案A对于下面的存储过程：CREATE PROCEDURE Myp1 @p IntASSELECT St_name, AgeFROM StudentsWHERE Age=@p假如要在Students表中查找年龄是20岁的学生，（）可以正确的调用这个存储过程。
A EXEC Myp1 @p=´20´B EXEC Myp1 @p=20C EXEC Myp1=´20´D EXEC Myp1=20参考答案当某个表被删除后，该表上的触发器被自动删除。
[参考答案]正确SQL Server为每个触发器创建了两个临时表,它们是updated和deleted。
[参考答案]错误存储过程是存储在服务器上的一组预编译的Transact-SQL语句。
[参考答案]正确在SQL SERVER中，触发器的执行是在数据的插入、更新或删除之前执行的。
[参考答案]错误若要修改一个存储过程，可以先删除该存储过程，再重新创建。
[参考答案]正确触发器与表紧密相连，可以看作表定义的一部分。
[参考答案]正确临时存储过程总是在master数据库中创建。
通常分为局部临时存储过程和全局临时存储过程。
[参考答案]错误某个表被删除后，该表上的所有触发器将自动被删除。
[参考答案]正确SQL Server 中的存储过程具有5种类型。
[参考答案]正确存储过程的输出结果可以传递给一个变量。
[参考答案]正确触发器是可在程序中被调用执行。
[参考答案]错误存储过程是存储在服务器上的一组预编译的Transcat-SQL语句。
[参考答案]正确创建触发器的时候可以不是表的所有者或数据库的所有者。
[参考答案]错误每个存储过程向调用方返回一个整数返回代码。
如果存储过程没有显式设置返回代码的值，则返回代码为0，表示成功。
[参考答案]正确在SQL Server中，触发器的执行通过EXECUTE命令实现。
[参考答案]错误在SQL SERVER中，替代触发器的执行是在数据变动之前被触发，对于每个触发操作只能定义一个替代触发器。
[参考答案]正确使用存储过程可以减少网络流量。
[考答案]正确存储过程使代码具有重用性。
[参考答案]正确触发器不能被调用，它可以自动执行。
[参考答案]正确在SQL SERVER中，触发器的执行是在数据的插入、更新或删除之前执行的。
[参考答案]错误创建存储过程的命令关键字CREATE PROCEDURE不可以缩写。
[参考答案]错误触发器可以在视图上定义。
[参考答案]错误触发器被激活后自动创建deleted 和inserted 两个临时表。
[参考答案]错误ROLLBACK TRANSACTION的意思是回滚事务。
[参考答案]正确触发器用来定义比CHECK约束更复杂的规则或要求。
[参考答案]正确通过语句ALTER PROCEDURE修改存储过程与删除存储过程然后再新建一个同名存储过程的效果是相同的。
[参考答案]错误某SQL Server 2000数据库中两张表：商品表（商品号，商品名，商品类型，价格）和销售表（商品号，销售时间，销售数量，销售单价）。
用户需统计指定年份每类商品的销售总数量和销售总利润，要求只列出销售总利润最多的前三类商品的商品类别、销售总数量和销售总利润。
为了完成该统计操作，请按要求将下面的存储过程补充完整。
CREATE PROC p_Sum @year INTASSELECT (__1__ ) 商品类别，SUM(销售数量) AS 销售总数量，(__2__ ) AS 销售总利润FROM 商品表JOIN 销售表ON 商品表. 商品号=销售表.商品号WHERE year(销售时间) = @yearGROUP BY 商品类别ORDER BY 销售总利润(__3__)参考答案1：TOP 32：(销售单价-成本价)*SUM(销售数量)3：DESC(降序)在SQL Server 2000中，设有顾客表(顾客号, 顾客名, 所在地区, 年龄)，应用系统中需统计指定地区的顾客人数和平均年龄。
请补全下列存储过程代码以完成该功能。
CREATE PROC P_Count @area varchar(20)ASSELECT COUNT(*) AS 人数, ( __1__ ) AS 平均年龄 FROM 顾客表WHERE 所在地区=@area参考答案1：AVG(年龄)以下代码创建和执行存储过程proc_score，查询S_C_Info表中C_No 为“0002”的St_ID, Score等信息。
CREATE PROC pro_scoreASSELECT St_ID, __1__FROM S_C_InfoWHERE C_No=‘0002’执行存储过程proc_score命令为：EXEC __2__参考答案1：Score2：proc_score在student_db数据库中创建一个名为“tr_P1”的存储过程，实现根据学生学号，查询该学生的选修课程情况，其中包括该学生的学号、姓名、课程名、课程类型、成绩。
CREATE __1__ tr_P1 @stID varchar(10)ASSELECT St_Info.St_ID, St_Info.St_Name, C_Info.C_Name, C_Info.C_Type, S_C_Info.ScoreFROM St_Info, S_C_Info, C_InfoWHERE St_Info.St_ID = S_C_Info.St_ID AND S_C_Info.C_No =C_Info.C_No调用该存储过程查询“0403060111”学生的选修课程情况。