42. SQL -- 存储过程（3）

lixiaolong

　　查看、修改和删除存储过程
　　A、查看存储过程
　　存储过程被创建以后，它的名字存放在当前数据库的系统表sysobjects中，创建它的源代码存放在syscomments系统表中。可以通过SQL Server 2000提供的几个系统存储过程来查看用户存储过程的有关信息。
　　○1 .用T-SQL语句查看存储过程
　　⑴ 查看存储过程的定义
　　系统存储过程sp_helptext可查看未加密的存储过程的定义脚本，也可用于查看规则、默认值、用户定义函数、触发器或视图的定义脚本。使用其语法如下：
　　sp_helptext [@objname=]‘name‘
　　其中：[@objname=]‘name‘ 是对象的名称，要查看的对象必须在当前数据库中。这里为存储过程名。例如，执行sp_helptext proc_Add，可显示存储过程proc_Add的定义脚本。
　　DEMO:
　　sp_helptextproc_add
　　⑵ 查看有关存储过程的信息
　　使用系统存储过程sp_help可查看有关存储过程的信息。具体语法形式如下：
　　sp_help proc_name
　　其中：proc_name：要查看的存储过程名。
　　执行上面的语句后，系统将返回指定存储过程的名称、拥有者、类型和创建时间，并且返回这个存储过程所有参数的名称、类型、宽度、精度和默认值等信息。
　　DEMO:
　　sp_helpproc_add
　　○2 . 使用SQL ServerManagement Studio查看存储过程
　　(1)展开服务器。
　　(2)分别展开―数据库‖、―可编程性‖、―存储过程‖。
　　(3)右击需要查看的存储过程，然后单击―属性‖命令，系统将弹出如下图的―存储过程属性‖窗口。
　　(4)点击―常规‖，可以查看到该存储过程属于哪个数据库，创建日期，属于哪个数据库用户等信息。
　　(5)点击―权限‖，可以为该存储过程添加用户并授予其权限。
　　修改存储过程
　　○1 . 使用T-SQL语句修改存储过程
　　Transact-SQL中提供了ALTER PROCEDURE语句来更改已经创建的存储过程，它不会更改权限，也不影响相关的存储过程或触发器。它的语法如下所示：
　　ALTER PROC [ EDURE ] procedure_name
　　[ {@parameter data_type }
　　[ VARYING ] [ = default ] [ OUTPUT ]
　　] [,...n ]
　　[ WITH
　　{ RECOMPILE |ENCRYPTION
　　| RECOMPILE , ENCRYPTION
　　}
　　]
　　[ FOR REPLICATION ]
　　AS
　　sql_statement[ ...n ]
　　DEMO:修改之前创建的存储过程 proc_add，使该存储过程为加密状态
　　alterPROCEDURE proc_Add
　　@Value1INT,
　　@Value2INT,
　　@ResultValueINT OUTPUT
　　withencryption
　　AS
　　SELECT@ResultValue = @Value1 + @Value2
　　GO
　　○2 、使用SQL ServerManagement Studio修改存储过程
　　在SQL Server Management Studio中修改存储过程的步骤如下：
　　(1)展开服务器。
　　(2)分别展开―数据库‖、―JWGL‖、―可编程性‖、―存储过程‖。
　　(3)右击需要修改的存储过程，然后单击―修改‖命令。
　　(4)进行存储过程的修改。
　　删除存储过程
　　对于不再需要的存储过程，可将其删除。可以使用SQL语句，也可以使用SQL ServerManagement Studio。
　　○1 . 使用SQL语句删除存储过程
　　从当前数据库中删除一个或多个存储过程的T-SQL语句是 DROP PROCEDURE。语法：
　　DROP PROCEDURE {procedure} [,…n]
　　其中参数：
　　● procedure：是要删除的存储过程的名称。
　　● n：表示可以指定多个存储过程。
　　例如，要将proc_Add存储过程删除，则可以执行
　　DROP PROC proc_Add
　　○2 、使用SQL ServerManagement Studio删除存储过程
　　使用SQL Server Management Studio中删除存储过程的步骤如下：
　　（1）展开服务器。
　　（2）分别展开―数据库‖、―JWGL‖、―可编程性‖、―存储过程‖。
　　（3）右击需要删除的存储过程，然后单击―删除‖命令。
　　（4）在系统弹出的―删除对象‖对话框中点击―确定‖即可删除该存储过程。
　　注册扩展存储过程：
　　○1 . 使用T-SQL语句注册扩展存储过程
　　SQLServer 的系统管理员通过系统存储过程sp_addextendedproc来添加一个扩展存储过程到系统的master数据库中。这个操作称为注册扩展存储过程。
　　sp_addextendedproc存储过程的具体语法：
　　sp_addextendedproc[@functname=] 'procedure', [@dllname=] 'dll'
　　其中：
　　● [@functname=] 'procedure'：是在动态链接库(DLL)内调用的函数名称。
　　● [ @dllname = ] 'dll'：包含该函数的DLL名称。
　　sp_addextendedproc将新的条目（函数名称）添加到sysobjects中，在SQL
　　Server注册新扩展存储过程名称，同时在syscomments表中添加一个条目。
　　DEMO：假设已经编写好了一个名为xp_hello.dll的动态链接库，其中有一个函数叫xp_hello，我们使用以下的语句来把xp_hello添加到SQL Server中。
　　USEmaster
　　EXECsp_addextendedprocxp_hello，xp_hello.dll‘
　　○2 、使用SQL ServerManagement Studio注册扩展存储过程
　　在SQL Server Management Studio中，注册扩展存储过程的步骤如下：
　　（1）展开服务器组，然后展开服务器。
　　（2）展开―数据库‖文件夹，再展开master数据库。
　　（3）右击―扩展存储过程‖，然后单击―新建扩展存储过程‖命令。
　　（4）在―名称‖框中输入扩展存储过程的名称。
　　（5）在―路径‖中，输入包含此扩展存储过程的动态链接库的路径。单击（―...‖）按钮，查找包含此扩展存储过程的 DLL。
　　创建存储过程
　　
　　创建存储过程实现密码验证：
　　select* from orders
　　原始表格记录：
　　docnodocdate cust carrency rate
　　p022 0 1 1 - 0 8 - 1 6 00:00:00.000 microsoft CH 6000.0000
　　p032 0 1 1 - 0 8 - 1 6 00:00:00.000 bcUSA7000.0000
　　p042 0 1 1 - 0 8 - 1 6 00:00:00.000 microsoft USA 6000.0000
　　--1、在orders 表中增加一列[userpasswd] [varbinary](256) NULL,
　　ALTERTABLE dbo.orders ADD
　　userpasswordvarbinary(256) NOT NULL
　　CONSTRAINTDF_orders_userpasswordDEFAULT convert(varbinary(256),
　　pwdencrypt('passw0rd'))
　　GO
　　--2、设置一个初始密码
　　updateorders
　　set userpassword = convert(varbinary(256), pwdencrypt('passw0rd'))
　　--3、用SELECT验证一下密码,使用微软未公开的解密函数pwdcompare，表示失败，表示验证通过
　　selectpwdcompare('passward', userpassword),* from orders
　　selectpwdcompare('passw0rd', userpassword),* from orders
　　--4、创建一个存储过程，若密码成功则print wellcome否则The user name or password is incorrect.
　　if exists
　　(select *
　　fromsys.sysobjects
　　whereid=object_id(N'pr_checkpassword')
　　andxtype='P')
　　dropproc pr_checkpassword
　　go
　　CREATEPROCEDURE pr_checkpassword
　　(@userid varchar(10) , @userpassword nvarchar(256) )
　　withencryption
　　as
　　if (isnull(@userid,'')='' or isnull(@userpassword,'')='')
　　begin
　　print'请输入用户名或密码'
　　return-1000
　　end
　　if exists (select 1
　　fromorders
　　wheredocno=@userid
　　andpwdcompare(@userpassword, userpassword)=1)
　　begin
　　print'密码验证成功！'
　　return-1000
　　end
　　else
　　begin
　　print'密码验证失败！'
　　return-1000
　　end
　　go
　　--执行查看效果
　　execpr_checkpassword'A01','0' --密码验证失败！
　　execpr_checkpassword'','' --请输入用户名或密码
　　execpr_checkpassword'p02','passw0rd' --密码验证成功！
　　带return 的存储过程
　　use mydb
　　go
　　select* from orders
　　--1验证是否存在名称为pr_checkpassword存储过程，若有，则删除此存储过程
　　if exists
　　(select *
　　fromsys.sysobjects
　　whereid=object_id(N'pr_checkpassword')
　　andxtype='P')
　　dropproc pr_checkpassword
　　go
　　--2创建存储过程
　　CREATEPROCEDURE pr_checkpassword
　　(@userid varchar(10) , @userpassword nvarchar(256) )
　　withencryption
　　AS
　　declare@rtn int
　　select@rtn = 1
　　if not exists
　　(select *
　　fromorders
　　wheredocno = @userid)
　　begin
　　select@rtn = -1000
　　select@rtn
　　return@rtn
　　end
　　if not exists
　　(select *
　　fromorders
　　wheredocno = @userid
　　and(pwdcompare(@userpassword, userpassword) = 1))
　　begin
　　select@rtn = -1000
　　select@rtn
　　return@rtn
　　end
　　select@rtn
　　return@rtn
　　GO
　　--3执行查看效果
　　executedbo.pr_checkpassword @userid = 'p01', @userpassword ='passw0rd'
　　go-- 输入正确的userid 及userpassword
　　返回结果：
　　1
　　executedbo.pr_checkpassword @userid = 'A01', @userpassword ='passw0rd'
　　go-- 输入错误的userid 及userpassword
　　返回结果：
　　-1000
　　添加事务的存储过程
　　--1验证是否存在名称为pr_procedure_name存储过程，若有，则 .除此存储 .程
　　if exists
　　(select *
　　fromsysobjects
　　whereid = object_id('dbo.pr_procedure_name'))
　　dropprocedure dbo.pr_procedure_name
　　go
　　--2创建存储过程
　　createprocedure dbo.pr_procedure_name
　　@v_spartvarchar(30),
　　@v_epartvarchar(30)
　　as
　　declare@l_trancnt int
　　学习是不断积累及重复学习的过程，当学习变成一种习惯的时候，就真正进入了学习的殿堂！
　　第 280 页 共 588 页
　　select@l_trancnt = @@trancount
　　if @l_trancnt = 0
　　begintran tran_pr_procedure_name
　　else
　　savetran tran_pr_procedure_name
　　--此处输入要执行的语句，若报错则回滚
　　if @@error  0
　　begin
　　rollbacktran tran_pr_procedure_name
　　return99999
　　end
　　--执行下一条语句，若报错则回滚
　　if @@error  0
　　begin
　　rollbacktran tran_pr_procedure_name
　　return99999
　　end
　　if @l_trancnt = 0
　　committran
　　return0
　　go
　　事务处理:
　　bgeintran
　　rollbacktran
　　cummittran
　　rollback tran:在事务处理中，当一个逻辑出错，就全部rollback（回滚）
　　当执行以下语句：
　　begingtran tran_name
　　updateemployee set address = 'address'
　　执行时虽然提示执行成功，但实际上并未提交语句，若此时进行查询，则查询不出来（锁定了），等待上一条语句的成功执行。若使用select * from employee (nolock)，允许读取未提交的数据则即使未提交，也可读取，但读取的资料是上一条语句更后的数据rollback tran tran_name
　　回滚事务，回滚到未更新前状态cummit trantran_name
　　提交事务
　　带参数的存储过程
　　--1验证是否存在名称为proc_time存储过程，若有，则删除此存储过程
　　if exists
　　(select *
　　fromsysobjects
　　whereid = object_id('dbo.proc_time'))
　　dropprocedure dbo.proc_time
　　go
　　--2创建带参数的存储过程，当输入参数满足where中条件时，返回查询结果
　　createproc proc_time
　　@emp_idvarchar(20),@sign_time datetime
　　as
　　selectclock_id,emp_id,sign_time
　　fromTimeRecords
　　whereemp_id = @emp_id
　　andconvert(char(10),sign_time,121) = @sign_time
　　--3执行存储过程
　　返回emp_id= P1103891，打卡日期为2011-06-10的打卡记录
　　execproc_time 'P1103891','2011-06-10'
　　返回：