实验6 游标、存储过程和触发器
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)如果视图引用任何非确定性函数,则不能在视图上创建聚集索引
实验6 游标与存储过程
实验6：游标与存储过程6.1 实验目的与要求(1) 掌握游标的定义和使用方法。
(2) 掌握存储过程的定义、执行和调用方法。
(3) 掌握游标和存储过程的综合应用方法。
6.2 实验案例下面以简单实例介绍游标的具体用法。
[例6.1] 利用游标选取业务科员工的编号、姓名、性别、部门和薪水字段，并逐行显示游标中的信息。
DECLARE SCROLL cur_emp CURSOR FORSELECT employeeno, employeename, sex, department, salaryFROM employeeWHERE department='业务科'ORDER BY employeeno /*定义游标*/OPEN cur_emp /*打开游标*/SELECT 'CURSOR内数据条数'=@@cursor_rows /*显示游标内记录的个数*/FETCH NEXT FROM cur_emp /*逐行提取游标中的记录*/WHILE (@@FETCH_status<>-1) /*判断FETCH语句是否执行成功*/BEGINSELECT 'cursor读取状态'=@@FETCH_status/*显示游标的读取状态*/FETCH NEXT FROM cur_emp/*提取游标下一行信息*/ENDCLOSE cur_emp/*关闭游标*/DEALLOCATE cur_emp/*释放游标*/本例中，@@cursor_rows是返回连接上最后打开的游标中当前存在的合格行的数量。
具体参数信息见表6-1所示。
@@FETCH_status是返回被FETCH语句执行的最后，而不是任何当前被连接打开的游标的状态。
具体参数见表6-2所示。
表6-2 @@FETCH_status参数返回值的描述表[例6.2] 利用游标选取业务科员工的编号、姓名、性别、部门和薪水字段，并以格式化的方式输出游标中的信息。
数据库SQL ServerSQLite教程课件：存储过程、触发器和游标
存储过程、触发器和游标
⒉ 使用 SSMS 创建存储过程 操作步骤如下： (1) 在“对象资源管理器”中展开“数据库”节点，展 开要创建存储过程的数据库。 (2) 展开“可编程性”节点，选择“存储过程”选项， 右击鼠标弹出快捷菜单，选择“新建存储过程”命令，打开 创建存储过程对话框，如图 8-3 所示。
存储过程、触发器和游标
8.1.2 创建存储过程 1. 使用 create procedure 语句创建存储过程 1) 语法格式
存储过程、触发器和游标
参数说明： (1) procedure_name 是要创建的存储过程的名称，它后 面跟一个可选项 number，是一个整数，用来区别一组同名 的存储过程，如 proc1、proc2 等。 (2) @parameter 用来声明存储过程的形式参数。在 create procedure 语句中，可以声明一个或多个参数。 (3) data_type 是参数的数据类型。 (4) varying 指定由 output 参数支持的结果集，仅应用于 游标型参数。
exec xp_logininfo;
存储过程、触发器和游标 例 8-3 查看有关 D:\sq 文件夹的文件信息。显示结果如
图 8-2 所示。代码如下： exec xp_cmdshell 'dir D:\sq\';
图 8-2 扩展存储过程
存储过程、触发器和游标
3) 用户自定义存储过程 用户自定义存储过程 (User-Defined Stored Procedure) 是 由用户设计的存储过程。其名称可以是任意组合 SQL Server 命令规则的字符组合，通常以“usp_”开头，避免以“sp_” 或“xp_”开头，以免造成混淆。自定义的存储过程会被添 加到所属数据库的存储过程中，并以对象的形式保存。
存储过程触发器游标
SQL Server实用教程
2021/8/29
Page 25
创建和应用DML触发器
1．AFTER触发器 这类触发器是在记录已经改变之后，才会被激活
触发器是一类特殊的存储过程，它是在执行某些 特定的T-SQL语句时可以自动执行的一种存储过 程。
触发器的功能 SQL Server2005提供了两种方法来保证数据
的有效性和完整性：约束和触发器。 触发器的常用功能如下。 （1）完成更复杂的数据约束：触发器可以实现比
约束更为复杂的数据约束。 （2）检查SQL所做的操作是否允许：触发器可以
CREATE PROCEDURE stu_cj1 @name char(10),@cname char(16)
AS SELECT student.sno,sname,cname,grade
FROM student INNER JOIN sc ON student.sno=sc.sno INNER JOIN
（5）返回自定义的错误信息：约束只能通过标准的系统错 误信息来传递错误信息，如果应用程序要求使用自定义信 息和较为复杂的错误处理，则必须使用触发器。
（6）防止数据表结构更改或数据表被删除：为了保护已经 建立好的数据表，触发器可以在接收到以DROP或 ALTER开头的语句后，不对数据表的结构做任何操作。
2．在执行存储过程时设定 通过在执行存储过程时设定重新编译，可以让SQL Server
在执行存储过程时重新编译该存储过程，这一次执行完成后 ，新的执行计划又被保存在缓存中。这样用户就可以根据需 要进行重新编译。
实验六管理存储过程11页word
实验六存储过程与触发器一、存储过程【创建存储过程】：CREATE PROCEDURE [OWNER].[PROCEDURE NAME]AS <SQL块>如：Use pubsGoCreate procedure author_informationAsselect au_lname,au_fname,title,pub_namefrom authors ajoin titleauthor taon a.au_id=ta.au_idjoin titles ton t.title_id=ta.title_idjoin publishers pon t.pub_id=p.pub_idGo【管理存储过程】：➢可以使用sp_helptext命令查看创建存储过程的文本信息。
Use pubsGoSp_helptext author_informationGo➢可以用sp_help查看存储过程的一般信息。
Use pubsGoSp_help author_informationGo➢可以使用系统存储过程sp_rename修改存储过程的名字。
Use pubsGoSp_rename author_information ,authors_informationGo➢也可以使用企业管理浏览存储过程的信息，具体方法是：✧从树型结构上选中存储过程所在的数据库节点，展开该节点；✧选中数据库节点下的〖存储过程〗节点，则右边的列表列出了数据库中目前所有的存储过程；✧选中存储过程，右击，执行〖属性〗命令，则系统将弹出如图所示对话框。
✧可以在对话框中修改存储过程内容，并保存修改。
✧如果想知道某个表被存储过程引用的情况，可以使用sp_depends，Sp_depends authors✧如果想知道某个存储过程引用表的情况，则可以使用Sp_depends procedure_name【执行存储过程】：对于存储过程的调用，应使用EXECUTE或EXEC关键字。
数据库技术实验六
课程名称数据库技术实验成绩实验名称存储过程和触发器的使用学号XX 班级日期实验目的：1.掌握存储过程的使用方法；2.掌握触发器的实现方法；实验平台：利用RDBMS（SQL Server 2008）与其交互查询工具（查询分析器）来操作T-SQL语言；实验内容：1.存储过程（1）创建存储过程，要求当一个员工的工作年份大于6年时将其转到经理办公室工作。
alterprocedure yuangong_infolEmployeeID char(6),name nchar(20)asbegindeclare year intset year=(select WorkYearfrom Employeeswhere EmployeeID=EmployeeID)declare DepartmentID char(3)set DepartmentID=(select DepartmentIDfrom Departmentswhere DepartmentName=name)if (year>6)update Employeesset DepartmentID=DepartmentIDwhere EmployeeID=EmployeeIDEndexec dbo.yuangong_infol'000000','经理办公室'（2）创建存储过程，根据每个员工的学历将收入提高500元。
alterproc SA_INenu char(6)asbeginupdate Salaryset InCome=InCome+500from Salary,Employeeswhere Employees.EmployeeID=Salary.EmployeeID and Education=enuendselect InComefrom Salary,Employeeswhere Salary.EmployeeID=Employees.EmployeeID and Education='本科'goexec dbo.sa_in'本科'goselect InComefrom Salary,Employeeswhere Salary.EmployeeID=Employees.EmployeeID and Education='本科'select InComefrom Salary,Employeeswhere Salary.EmployeeID=Employees.EmployeeID and Education='本科'（3）创建存储过程，使用游标计算本科与以上学历的员工在总员工数中的比例。
数据库中的游标存储过程和触发器
数据库中的游标存储过程和触发器游标、存储过程和触发器是数据库中常用的三种特殊对象。
游标用于在数据库管理系统中对查询结果进行逐行处理，存储过程是一组预定义的SQL语句集合，可以被重复调用执行，而触发器则是在数据库中的特定事件发生时自动执行的一段代码。
首先，我们来了解一下游标。
游标是一个数据库概念，它可以被看作是一个指向查询结果集的指针。
通过游标，我们可以在数据库内部对查询结果集进行逐行处理，从而实现对数据的操作。
游标的使用可以有效地减少数据库服务器的负担，提高数据库性能。
在一些需要对批量数据进行处理的场景下，游标可以发挥重要作用。
例如，当需要对查询结果逐行进行计算、更新或者删除时，可以使用游标定位到每一条记录，并对其进行操作。
接下来，我们了解一下存储过程。
存储过程是一组预定义的SQL语句的集合，它们一起执行一些特定的任务。
存储过程可以包含流程控制、循环结构、条件判断等逻辑，还可以接受参数并返回结果。
存储过程的好处在于可以实现代码复用，提高数据库的性能和可维护性。
通过存储过程，我们可以将常用的SQL操作封装起来，减少了网络传输的开销，提高了数据访问的效率。
另外，存储过程还可以实现权限控制，通过调用存储过程来间接访问数据库，可以避免直接在应用程序中操作数据库，增强了数据的安全性。
最后，我们来了解一下触发器。
触发器是在数据库中特定的事件发生时自动执行的一段代码。
这些事件可以是INSERT、UPDATE或者DELETE操作。
触发器通常被用来在数据库表的数据发生变化时执行相应的操作。
它可以用来保证数据库的数据一致性和完整性，触发器能够在数据被修改之前或之后自动执行，并且可以在代码中加入逻辑判断和业务处理。
例如，在一个订单表中，我们可以定义一个触发器，在插入一条新订单数据时，自动计算订单总金额并更新到订单的总金额字段中。
总结一下，游标、存储过程和触发器是对数据库进行处理和控制的重要工具。
游标可以让我们逐行处理查询结果集，存储过程可以定义逻辑处理、实现代码的复用，而触发器则可以在数据库表的特定事件发生时自动执行一段代码。
存储过程和触发器
实验存储过程和触发器实验一存储过程的创建和使用【实验目的】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语句创建一个DELETE触发器，完成的功能是当在Categories表中删除记录时，检测Products表中是否存在相关记录，如果存在，则给出提示信息“不能删除该条记录”；如果不存在，则删除该条记录。
2.基于Sales表创建一个触发器，针对INSERT、DELETE、UPDATE操作。
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
实验六游标、存储过程与触发器
一、实验目的
掌握使用T-SQL实现游标、存储过程和触发器的创建，使用方法。
二、实验内容
在实验一、实验二创建的表中用T-SQL语句完成以下内容：
1.使用游标实现将及格的选课信息输出。
2.使用游标将SPJ表中的偶数行输出。
3.创建存储过程，查询赵永亮所修课程的课程信息，将课程号和课程名输出。
4．创建存储过程，统计指定学生修课的平均成绩和选课门数，将统计的结果用输出参数返回。
5．创建存储过程，在学生表Student中插入一条完整的元组。
6．创建存储过程，根据用户指定的供应商号删除SPJ表中相应的供货信息。
7．创建存储过程，将指定零件的重量增加指定的值。
8．用触发器实现约束：如果是北京的供应商，供应任何零件的数量不能少于300，如果少于则自动改为300。
9．在SC关系中增加新属性列Status，用来记录课程成绩的等级，0－59分为“不合格”，60－69为“合格”，70－89为“良好”，90以上为“优秀”。
要求status属性列的值由用户在插入、更新选课成绩时系统自动填写和更新。