资源预览内容
第1页 / 共16页
第2页 / 共16页
第3页 / 共16页
第4页 / 共16页
第5页 / 共16页
第6页 / 共16页
第7页 / 共16页
第8页 / 共16页
第9页 / 共16页
第10页 / 共16页
亲,该文档总共16页,到这儿已超出免费预览范围,如果喜欢就下载吧!
资源描述
存储过程,本章内容:存储过程的概念、创建存储过程、执行存储过程、修改存储过程和删除存储过程 重点:创建存储过程和修改存储过程 难点:创建存储过程,存储过程,一存储过程的概念 存储过程(stored procedure)是一组事先编译好的Transact-SQL代码。存储过程作为一个独立的数据库对象,可以作为一个单元被用户的应用程序调用。由于存储过程是已经编译好的代码,所以执行的时候不必再次进行编译,从而提高了程序的运行效率。 SQL Server的存储过程类似于其他编程语言里的过程(procedure),具体体现在: (1)存储过程可以接收参数,并以输出参数的形式返回多个参数给调用存储过程的过程或批处理。 (2)存储过程也可以容纳存储过程,可以在对数据库进行查询、修改的编程语句中调用其他的存储过程。 (3)可以返回执行存储过程的状态值以反映存储过程的执行情况。,存储过程,使用存储过程的好处: (1)执行速度快。存储过程在创建时就经过了语法检查和性能优化,因此在执行时不必再重复这些步骤。存储过程在经过第一次调用之后,就驻留在内存中,不必再经过编译和优化,所以执行速度快。在有大量批处理的Transact-SQL语句要重复执行的时候,使用存储过程可以极大地提高运行效率。 (2)模块化的程序设计。存储过程经过了一次创建以后,可以被调用无数次。用户可以独立于应用程序而对存储过程进行修改。可以按照独特的功能模式设计不同的存储过程以供使用。 (3)减少网络通信量。存储过程中可以包含大量的Transact-SQL语句,但存储过程作为一个独立的单元来使用。在进行调用时,只需要使用一个语句就可以实现,所以大大减少了网络上数据的传输。 (4)保证系统的安全性。可以设置用户通过存储过程对某些关键数据进行访问,但不允许用户直接使用T-SQL或企业管理器对数据进行访问。,存储过程,二创建存储过程 1使用T-SQL语句创建存储过程 创建存储过程使用CREATE PROC EDURE语句。 语法格式如下: CREATE PROCEDURE 存储过程名 WITH RECOMPILE|ENCRYPTION|RECOMPILE, ENCRYPTION AS SQL语句 其中: RECOMPILE:在执行完存储过程以后不在高速缓存里保留存储过程的备份,所以每次执行存储过程时都需要对存储过程进行重新编译和优化。ENCRYPTION:存储过程作为数据库对象将在syscomments表中留下完整的代码等信息。使用了ENCRYPTION参数以后,将对访问这些数据的入口进行加密。 例如:,use Xkgo create procedure p_student as select stuno,stuname,student.classno,classname,departno from student join class on student.classno =class.classno go,存储过程,2使用企业管理器创建存储过程 可以使用企业管理器进行存储过程的创建,步骤如下: (1)从树型结构上选中要建立存储过程的数据库节点,展开该节点。 (2)选中数据库节点下的存储过程节点,则右边的列表列出了数据库中目前所有的存储过程,如图7-1所示。,图7-1 数据库里的存储过程,存储过程,(3)右击鼠标,从弹出的快捷菜单中选择“新建存储过程”命令,则系统弹出如图7-2所示的对话框。 (4)在Text文本框中输入正确的T语句。 (5)单击“语法检查”按钮,检查语法错误。 (6)单击“确定”按钮完成存储过程的创建。,存储过程,带参数的存储过程: CREATE PROCedure procedure_name ; number parameter data_type = default , AS sql_statement s procedure_name:给出存储过程名; s number:对同名的存储过程指定一个序号; s parameter:给出参数名; s data_type:指出参数的数据类型; s = default:给出参数的默认值; s sql_statement:存储过程所要执行的SQL语句,它可以是一组SQL语句,可以包含流程控制语句等。,存储过程,例:在Xk数据库中创建一个名为p_StudentPara的存储过程。该存储过程能根据指定的班级返回该班级代码对应的Student表中的记录。 Create procedure p_StudentPara ClassNo varchar(8) As Select * from Student where ClassNo=ClassNo,在已经建好的lfj数据库的表lf中,创建一个成批插入记录的存储过程。,use lfj go create proc insertlf no char(5), name char(10), lish float, asum float as insert into lf(stuno,stuname,english,addsum) values(no,name,lish,asum),exec insertlf no=001, name=tiankong, lish=80, asum=80,执行存储过程:,exec p_studentpara classno=20010002,存储过程,存储过程一般用来完成数据查询和数据处理操作,所以在存储过程中不可以使用创建数据库对象的语句,即在存储过程中一般不能含有以下语句: CREATE TABLE CREATE VIEW CREATE DEFAULT CREATE RULE CREATE TRIGGER CREATE PROCEDURE,执行存储过程,EXECute =|,执行存储过程,三执行存储过程 如果对存储过程的调用是批处理的第一条语句,则可以直接使用存储过程的名字调用该存储过程。例如: Use Xk go P_studentpara go 但是,下面的语句将会返回错误的信息 use Xk P_studentpara go 因为在这个批处理中对存储过程的调用不是第一个语句。对这种情况的处理,应该使用EXECUTE或EXEC关键字。 下面是正确地调用存储过程的方法: use Xk exec p_studentpara go,存储过程,带输出参数的存储过程 如果需要从存储过程中返回一个或多个值,可以通过在创建存储过程的语句中定义输出参数来实现,为了使用输出参数,需要在create procedure语句中指定output关键字。 声明输出参数的语法如下:parameter_name datatype=default output 例:创建存储过程p_classnum,要求能够根据用户给定的班级代码,统计该班的人数,并将人数以输出变量返回给用户。 Create procedure p_classnum classno varchar(8),classnum smallint output As Set classnum= (select count(*) from student Where classno=classno) Print classnum,执行存储过程,例:执行以上存储过程。 Declare classno varchar(8),classnum smallint Set classno=20000002 Exec p_classnum classno,classnum,另外一种执行方法:,declare classno varchar(8),classnum smallint exec p_classnum 20010001,classnum output,declare classnum smallint exec p_classnum 20010001,classnum output,修改存储过程,四修改存储过程 SQL Server提供了在不改变存储过程使用许可和名字的情况下,对存储过程进行修改的语句。 语法格式为: ALTER PROCEDURE 存储过程名WITH RECOMPILEENCRYPTIONRECOMPILE,ENCRYPTION ASSQL语句 当然,用户也可以使用企业管理器进行系统存储过程的修改。 例:修改存储过程p_studentpara,使其能根据用户提供的班级名称,而不是班级代码进行模糊查询,并要求加密。 Alter procedure p_studentpara classname varchar(20) With encryption As Select classname,stuno,stuname,pwd from student,class Where student.classno=class.classno and classname like %+classname+%,删除存储过程,五删除存储过程 1使用T-SQL语句删除存储过程 删除存储过程使用DROP PROCEDURE语句。 语法格式为: DROP PROCEDURE 存储过程名 ,n 例如: Use Xk godrop procedure p_studentpara go 2使用企业管理器删除存储过程 我们也可以使用企业管理器删除存储过程,方法是: (1)从树型结构上选中存储过程所在的数据库节点,展开该节点。 (2)选中数据库节点下的存储过程节点,则右边的列表列出了数据库中目前所有的存储过程,如图7-1所示。,删除存储过程,(3)选中要删除的存储过程,右击鼠标,在弹出的快捷菜单中选择“删除”命令,则系统弹出如图7-4所示的对话框。,图7-4 删除存储过程 (4)选择“全部除去”按钮即完成操作。,
网站客服QQ:2055934822
金锄头文库版权所有
经营许可证:蜀ICP备13022795号 | 川公网安备 51140202000112号