42. SQL 存儲過程(3)

查看、修改和删除存储过程

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

[email protected] = @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

[email protected]

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

[email protected] int

[email protected] = 1

if not exists

(select *

fromorders

wheredocno = @userid)

begin

[email protected] = -1000

[email protected]

[email protected]

end

if not exists

(select *

fromorders

wheredocno = @userid

and(pwdcompare(@userpassword, userpassword) = 1))

begin

[email protected] = -1000

[email protected]

[email protected]

end

[email protected]

[email protected]

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

[email protected]_trancnt int

学习是不断积累及重复学习的过程,当学习变成一种习惯的时候,就真正进入了学习的殿堂!

第 280 页 共 588 页

[email protected]_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'

返回:

本文出自 “Ricky's Blog” 博客,请务必保留此出处http://57388.blog.51cto.com/47388/1629008

更多相关文章
  • 存储过程 存储过程的定义 存储过程(StoredProcedure)是一组为了完成特定功能的SQL 语句集,是利用SQLServer 所提供的Transact-SQL语言所编写的程序.功能是将常用或复杂的工作,预先用
  • Java EE 學習第29天PL/SQL存儲過程存儲函數觸發器
    一.PL/SQL简介 1.概念:PL/SQL语言是Oracle数据库专用的一种高级程序设计语言,是对标准SQL语言进行了过程化扩展的语言. 2.功能:既能够实现对数据库的操作,也能够通过过程化语言中的复杂逻辑结构完成复杂的业务逻辑. 3.特点 (1)与SQL语言紧密集成,所有的SQL语句在PL/SQ ...
  • 今天尝试着做之前用webMethods没做出来的SQL存储过程的使用,因为本来对于数据库的存储过程也是一知半解,所以先在http://www.cnblogs.com/hoojo/archive/2011/07/19/
  • 选中所在数据库 执行创建存储过程的sql CREATE proc [dbo].[spGenInsertSQL] (@tablename nvarchar(256),@sqlwhere varchar(4)) as begin declare @sql varchar(max) declare @sq
  • SQL 存儲過程中QUOTED_IDENTIFIER on/off
    http://huihai.iteye.com/blog/1005144 在存储过程中经常
  • 创建存储过程: 创建不带参数的存储过程 语法: CREATE PROC[EDURE] 存储过程名 AS SQL 和C语言的函数一样,参数可选 参数分为输入参数.输出参数 输入参数允许有默认值 创建步骤: ① 编写SQ
  • 创建表格并添加300万数据 use Stored CREATE TABLE UserInfo( --创建表 id int IDENTITY(1,1) PRIMARY KEY not null,--添加主键和标识列 U
  • 粗俗易懂的SQL存儲過程在.NET中的實例運用
    整理了一下存储过程在项目中的运用,防止遗忘,便记录于此!存储过程(Stored Proce
一周排行
  • 


    		    bootloaderbootloader啓動過程分析
    Boot Loader启动过程分析 文章转出处 http://liucw.blog.51c
  • 最近发现一奇怪现象,网站一些链接被百度收录,失效后一直无法去除,经过一番研究找到一些端倪. 首先用谷歌浏览器的firebug工具测试404,发现提示302错误.302错误表示被请求的资源暂时转移(Moved temp ...
  • 在Windows sever 2008_r2_安裝VS2008出現提示在_角色管理工具_按裝或配置問題的解決辦法
    在计算机右键“管理”,如下图   再点“功能”如下图:   在“.NET Framewor ...
  • LINUX提供了强大的防火墙iptables ,但是由于iptables语法比较繁琐,所以不利于部署和维护,shorewall是第三方免费的防火墙生成器,相当于对iptables又进行了一次集合和优化.介绍一下简单的
  • 唉,周末休息了两天,本周第一天上班就遇到两个问题....到现在还没解决... 今天计划是完成两个小项目,第一个是把之前搭建的VPN服务器和FTP服务器更改身份认证方式,与RSA Securid管理服务器做关联,使之用
  • 1,关于版本的回退和向前 git log 查看修改版本库的记录 ,可以知道每次的修改的版本ID号 commit id git reset --hard HEAD^  使当前版本成为上一次的提交的版本        
  • demo1: /// <summary> /// /// </summary> /// <param name="str"></param> /// ...
  • CSS3實現8種Loading效果二
    今晚吃完饭回宿舍又捣鼓了另外几种Loading效果,老规矩,直接“上菜“…… 注:gif图 ...
  • 好牛b的思路 题意:一系列物品,用二辆车运送,求运送完所需的最小次数,两辆车必须一起走 解法为状态压缩DP+背包,本题的解题思路是先枚举选择若干个时的状态,总状态量为1<<n,判断这些状态集合里的那些物品 ...
  • 今天小白正式开始学习sliverlight 的内容,但是在软件安装的过程中就遇到了问题,查了一下,需要安装对应版本的sdk跟tools,因为在新建项目的时候,可以选择sliverlght,因此,我断定肯定是vs201