存储过程各类情况分析.docx

存储过程各类情况分析.docx

《存储过程各类情况分析.docx》由会员分享，可在线阅读，更多相关《存储过程各类情况分析.docx（15页珍藏版）》请在冰豆网上搜索。

存储过程各类情况分析

存储过程

sql 语句执行的时候要先编译，然后执行。

存储过程就是编译好了的一些 sql 语句。

应用程

序需要用的时候直接调用就可以了，所以效率会高。

存储过程介绍

存储过程是由流控制和 SQL 语句书写的过程，这个过程经编译和优化后存储在数据库服务

器中，应用程序使用时只要调用即可。

在 ORACLE 中，若干个有联系的过程可以组合在一

起构成程序包。

使用存储过程有以下的优点：

* 存储过程的能力大大增强了 SQL 语言的功能和灵活性。

存储过程可以用流控制语句编写，

有很强的灵活性，可以完成复杂的判断和较复杂的 运算。

* 可保证数据的安全性和完整性。

# 通过存储过程可以使没有权限的用户在控制之下间接地存取数据库，从而保证数据的安

全。

# 通过存储过程可以使相关的动作在一起发生，从而可以维护数据库的完整性。

* 再运行存储过程前，数据库已对其进行了语法和句法分析，并给出了优化执行方案。

这

种已经编译好的过程可极大地改善 SQL 语句的性能。

由于执行 SQL 语句的大部分工作已

经完成，所以存储过程能以极快的速度执行。

* 可以降低网络的通信量。

* 使体现企业规则的运算程序放入数据库服务器中，以便：

# 集中控制。

# 当企业规则发生变化时在服务器中改变存储过程即可，无须修改任何应用程序。

企业规

则的特点是要经常变化，如果把体现企业规则的运算程序放入应用程序中，则当企业规则

发生变化时，就需要修改应用程序工作量非常之大（修改、发行和安装应用程序）。

如果把

体现企业规则的 运算放入存储过程中，则当企业规则发生变化时，只要修改存储过程就可

以了，应用程序无须任何变化。

数据库存储过程的实质就是部署在数据库端的一组定义代码以及 SQL。

利用 SQL 的语言可以编写对于数据库访问的存储过程，其语法如下：

CREATE PROC[EDURE] procedure_name [;number]

[

{@parameter data_type} ][VARYING] [= default] [OUTPUT]

]

[,...n]

[WITH　

{

RECOMPILE　

| ENCRYPTION　

| RECOMPILE, ENCRYPTION

}

]

[FOR REPLICATION]

AS

sql_statement [...n]

[ ]内的内容是可选项，而（）内的内容是必选项，

例：

 若用户想建立一个删除表 tmp 中的记录的存储过程 Select_delete 可写为：

Create Proc select_del As　

Delete tmp　

例：

用户想查询 tmp 表中某年的数据的存储过程

create proc select_query @year int as

select * from tmp where year=@year

在这里@year 是存储过程的参数

例：

该存储过程是从某结点 n 开始找到最上层的父亲结点，这种经常用到的过程可以由存

储过程来担当，在网页中重复使用达到共享。

空：

表示该结点为顶层结点

fjdid（父结点编号）　

结点 n 非空：

表示该结点的父亲结点号

dwmc（单位名称）

CREATE proc search_dwmc @dwidold int,@dwmcresult varchar（100） output

as　

declare @stop int

declare @result varchar（80）

declare @dwmc varchar（80）

declare @dwid int

set nocount on

set @stop=1

set @dwmc=""

select @dwmc=dwmc,@dwid=convert（int,fjdid） from jtdw where id=@dwidold　

set @result=rtrim（@dwmc）

if @dwid=0　

set @stop=0

while （@stop=1） and （@dwid<>0）

begin

set @dwidold=@dwid

select @dwmc=dwmc,@dwid=convert（int,fjdid） from jtdw where id=@dwidold

if @@rowcount=0　

set @dwmc=""

else

set @result=@dwmc+@result

if （@dwid=0） or （@@rowcount=0）　

set @stop=0

else

continue

end

set @dwmcresult=rtrim（@result）

使用 exec pro-name [pram1 pram2.....]

----------------master 数据库中两个非常有用的存储过程

本文中主要介绍了 master 数据库中两个非常有用但在 SQL Server 在线教科书中没有提到的

存储过程。

这些系统过程对于处理以下任务非常方便，如判断使用的存储空间大小、行数、用户表索

引等等。

第一个过程 sp_MSForEachDB 对于感兴趣的服务器上的每个数据库执行三条命令。

◆@command1：

第一个执行的命令

◆@replacechar：

用另一个占位赋替换“?

”

◆@command2：

第二个执行的命令

◆@command3：

第三个执行的命令

◆@precommand：

进入循环前执行的命令

◆@postcommand：

循环结束后执行的命令

每个命令集（即使该集合只含有一条命令）作为一个批处理对每个数据库执行，所以当我们

要将捕获的结果输出到文本而不是标准结果集表时，这将非常有用。

为了实现这一要求，选择菜单中的查询按钮|输出结果|输出到文本或者按快捷键[Ctrl]T。

下面的代码返回服务器上每个数据库中用户数据表的数目：

exec sp_MSForEachDB@command1 = "use ?

 exec sp_SpaceUsed"

The abbreviated output looks like this:

简短输出可能如下：

数据库名数据库大小未分配空间大小

master 5.25 MB1.26 MB

reserved data index_size unused

2808 KB 1144 KB 1080 KB 584 KB

第二个过程 sp_MSForEachTable 接受 7 个参数：

◆@command1：

第一个执行的命令

◆@replacechar：

用另一个占位符替换“?

”

◆@command2：

第二个执行的命令

◆@command3：

第三个执行的命令

◆@whereand：

Where 条件语句 （或 Order By 语句）

◆@precommand：

进入循环前执行的命令

◆@postcommand：

循环结束后执行的命令

通过对要传递的参数命名，可以跳过传递空值的要求。

当要执行的命令中含有一个问号时，

参数@replacechar 十分有用。

@whereand 参数的实现可以根据过滤器缩小输出的范围。

你还可以加入一个 ORDER BY 语句。

下面的例子返回 AdventureWorks 数据库中每个数据表

的行数，并按照数据表明对它们排序：

exec sp_MSForEachTable@command1 = "Print '?

'",

@command2 = "select count（*） from ?

",

@whereand = "ORDER BY 1"

一些输出的结果：

[HumanResources].[Department]-----------

16[HumanResources].[Employee]-----------290

[HumanResources].[EmployeeAddress]-----------290[HumanResources].

[EmployeeDepartmentHistory]-----------296

--------------------在 SQL Server 2005 中编写 sp_lock 系统存储过程

做为系统存储过程，sp_lock 可以用来了解服务器的运行情况，通过查看系统的锁定信息诊

断 SQL Server 可能出现的问题。

不过系统存储过程 sp_lock 本身存在一些缺陷。

对于数据库

管理新手来说，其返回的结果不够直白，花费了大量的工作来显示系统中哪个会话造成了

最多锁定，却并没有提供多少关于这些对象或会话的相应详细信息。

虽然我们可以创建自

定义的脚本来查看这些信息，但是返回的结果往往过于复杂，而充其量能返回一些质量低

下的信息。

也有其他的一些系统表可以用来查看锁定信息，例如 syslockinfo，但信息的细

节同样不够明了。

此外，sp_lock 和 syslockinfo 还有一个更大的问题，那就是他们都是“不

建议使用的特性”，所以将来的 SQL Server 版本中可能不再包含这些特性。

SQL Server 2005

提供的新的动态管理视图包含了大量锁定细节，并使我们能够将锁定信息关联起来，看起

来可以更一目了然。

　　sys.dm_tran_locks

　　新的动态视图 sys.dm_tran_locks 能够返回系统中当前活动的锁管理器资源信息。

这个

视图返回的信息类型和 sp_lock 一样，但提供了更多细节。

关键是这是一个视图，允许数据

库管理员轻松的将其连接到其他表。

　　自定义 sp_lock 例子

USE MASTER

GO

CREATE PROCEDURE [dbo].[sp_Lock_Detail]

AS

BEGIN

SELECT

SessionID = s.Session_id,

resource_type,

DatabaseName = DB_NAME（resource_database_id）,

request_mode,

request_type,

login_time,

host_name,

program_name,

client_interface_name,

login_name,

nt_domain,

nt_user_name,

s.status,

last_request_start_time,

last_request_end_time,

s.logical_reads,

s.reads,

request_status,

request_owner_type,

objectid,

dbid,

a.number,

a.encrypted ,

a.blocking_session_id,

a.text

FROM

sys.dm_tran_locks l

JOIN sys.dm_exec_sessions s ON l.request_session_id = s.session_id

LEFT JOIN

（

SELECT *

FROMsys.dm_exec_requests r

CROSS APPLY

sys.dm_exec_sql_text（sql_handle）

） a ON s.session_id = a.session_id

WHERE

s.session