《网络数据库--存储过程》由会员分享，可在线阅读，更多相关《网络数据库--存储过程（16页珍藏版）》请在金锄头文库上搜索。
1、存储过程,本章内容：存储过程的概念、创建存储过程、执行存储过程、修改存储过程和删除存储过程 重点：创建存储过程和修改存储过程 难点：创建存储过程,存储过程,一存储过程的概念 存储过程（stored procedure）是一组事先编译好的Transact-SQL代码。存储过程作为一个独立的数据库对象，可以作为一个单元被用户的应用程序调用。由于存储过程是已经编译好的代码，所以执行的时候不必再次进行编译，从而提高了程序的运行效率。 SQL Server的存储过程类似于其他编程语言里的过程（procedure），具体体现在： （1）存储过程可以接收参数，并以输出参数的形式返回多个参数给调用存储过程的过
2、程或批处理。 （2）存储过程也可以容纳存储过程，可以在对数据库进行查询、修改的编程语句中调用其他的存储过程。 （3）可以返回执行存储过程的状态值以反映存储过程的执行情况。,存储过程,使用存储过程的好处： （1）执行速度快。存储过程在创建时就经过了语法检查和性能优化，因此在执行时不必再重复这些步骤。存储过程在经过第一次调用之后，就驻留在内存中，不必再经过编译和优化，所以执行速度快。在有大量批处理的Transact-SQL语句要重复执行的时候，使用存储过程可以极大地提高运行效率。 （2）模块化的程序设计。存储过程经过了一次创建以后，可以被调用无数次。用户可以独立于应用程序而对存储过程进行修改。可以
3、按照独特的功能模式设计不同的存储过程以供使用。 （3）减少网络通信量。存储过程中可以包含大量的Transact-SQL语句，但存储过程作为一个独立的单元来使用。在进行调用时，只需要使用一个语句就可以实现，所以大大减少了网络上数据的传输。 （4）保证系统的安全性。可以设置用户通过存储过程对某些关键数据进行访问，但不允许用户直接使用T-SQL或企业管理器对数据进行访问。,存储过程,二创建存储过程 1使用T-SQL语句创建存储过程 创建存储过程使用CREATE PROC EDURE语句。 语法格式如下： CREATE PROCEDURE 存储过程名 WITH RECOMPILE|ENCRYPTION
4、|RECOMPILE, ENCRYPTION AS SQL语句 其中： RECOMPILE：在执行完存储过程以后不在高速缓存里保留存储过程的备份，所以每次执行存储过程时都需要对存储过程进行重新编译和优化。ENCRYPTION：存储过程作为数据库对象将在syscomments表中留下完整的代码等信息。使用了ENCRYPTION参数以后，将对访问这些数据的入口进行加密。 例如：,use Xkgo create procedure p_student as select stuno,stuname,student.classno,classname,departno from student joi
5、n class on student.classno =class.classno go,存储过程,2使用企业管理器创建存储过程 可以使用企业管理器进行存储过程的创建，步骤如下： （1）从树型结构上选中要建立存储过程的数据库节点，展开该节点。 （2）选中数据库节点下的存储过程节点，则右边的列表列出了数据库中目前所有的存储过程，如图7-1所示。,图7-1 数据库里的存储过程,存储过程,（3）右击鼠标，从弹出的快捷菜单中选择“新建存储过程”命令，则系统弹出如图7-2所示的对话框。 （4）在Text文本框中输入正确的T语句。 （5）单击“语法检查”按钮，检查语法错误。 （6）单击“确定”按钮完成存储
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数据库中创建一
7、个名为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,stu
8、name,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,执
9、行存储过程,EXECute =|,执行存储过程,三执行存储过程 如果对存储过程的调用是批处理的第一条语句，则可以直接使用存储过程的名字调用该存储过程。例如： Use Xk go P_studentpara go 但是，下面的语句将会返回错误的信息 use Xk P_studentpara go 因为在这个批处理中对存储过程的调用不是第一个语句。对这种情况的处理，应该使用EXECUTE或EXEC关键字。 下面是正确地调用存储过程的方法： use Xk exec p_studentpara go,存储过程,带输出参数的存储过程 如果需要从存储过程中返回一个或多个值，可以通过在创建存储过程的语句中定
10、义输出参数来实现，为了使用输出参数，需要在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=clas
11、sno) 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
12、,修改存储过程,四修改存储过程 SQL Server提供了在不改变存储过程使用许可和名字的情况下，对存储过程进行修改的语句。 语法格式为： ALTER PROCEDURE 存储过程名WITH RECOMPILEENCRYPTIONRECOMPILE,ENCRYPTION ASSQL语句 当然，用户也可以使用企业管理器进行系统存储过程的修改。 例：修改存储过程p_studentpara，使其能根据用户提供的班级名称，而不是班级代码进行模糊查询，并要求加密。 Alter procedure p_studentpara classname varchar(20) With encryption As
13、 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）选择“全部除去”按钮即完成操作。,
