存储过程各类情况分析

《存储过程各类情况分析》由会员分享，可在线阅读，更多相关《存储过程各类情况分析（12页珍藏版）》请在金锄头文库上搜索。

1、存储过程 sql 语句执行的时候要先编译，然后执行。存储过程就是编译好了的一些 sql 语句。应用程 序需要用的时候直接调用就可以了，所以效率会高。 存储过程介绍 存储过程是由流控制和 SQL 语句书写的过程，这个过程经编译和优化后存储在数据库服务 器中，应用程序使用时只要调用即可。在 ORACLE 中，若干个有联系的过程可以组合在一 起构成程序包。 使用存储过程有以下的优点： * 存储过程的能力大大增强了 SQL 语言的功能和灵活性。存储过程可以用流控制语句编写， 有很强的灵活性，可以完成复杂的判断和较复杂的 运算。 * 可保证数据的安全性和完整性。 # 通过存储过程可以使没有权限的用户在控

2、制之下间接地存取数据库，从而保证数据的安 全。 # 通过存储过程可以使相关的动作在一起发生，从而可以维护数据库的完整性。 * 再运行存储过程前，数据库已对其进行了语法和句法分析，并给出了优化执行方案。这 种已经编译好的过程可极大地改善 SQL 语句的性能。 由于执行 SQL 语句的大部分工作已 经完成，所以存储过程能以极快的速度执行。 * 可以降低网络的通信量。 * 使体现企业规则的运算程序放入数据库服务器中，以便： # 集中控制。 # 当企业规则发生变化时在服务器中改变存储过程即可，无须修改任何应用程序。企业规 则的特点是要经常变化，如果把体现企业规则的运算程序放入应用程序中，则当企业规则

3、发生变化时，就需要修改应用程序工作量非常之大（修改、发行和安装应用程序） 。如果把 体现企业规则的 运算放入存储过程中，则当企业规则发生变化时，只要修改存储过程就可 以了，应用程序无须任何变化。 数据库存储过程的实质就是部署在数据库端的一组定义代码以及 SQL。 利用 SQL 的语言可以编写对于数据库访问的存储过程，其语法如下： CREATE PROCEDURE procedure_name ;number parameter data_type VARYING = default OUTPUT ,.n WITH RECOMPILE | ENCRYPTION | RECOMPILE, ENCR

4、YPTION 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 开始找到最上层的父亲结点，这种经常用到的过程

5、可以由存 储过程来担当，在网页中重复使用达到共享。 空：表示该结点为顶层结点 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=co

6、nvert(int,fjdid) from jtdw where id=dwidold set result=rtrim(dwmc) if dwid=0 set stop=0 while (stop=1) and (dwid 50END首先来看看在这个程序中使用的 JOIN 语句。该语句将 sys.dm_tran_locks 表和 sys.dm_exec_sessions 连接起来，以检索关于当前服务器会话的锁定信息。JOIN 语句使我们 能够将会话的详细信息和与该会话相应的锁的详细信息进行关联。而后，程序在子查询中使用了 LEFT JOIN 语句，用来检索与目前正在执行的语句执行过 程相关的

7、信息。使用 LEFT JOIN 子句是因为服务器中很可能有会话持有目前没有执行的某种 特定类型的锁。如果有查询执行数据当然是最好的了，如果没有，那也不用担心，因为有 LEFT JOIN。注意在子查询中使用了带有 CROSS APPLY 操作符的 sys.dm_exec_sql_text 函数。这样我 们就可以使用存储在 sys.dm_exec_requests 视图中的 sql_handle 字段来确定正在执行的语 句。sql_handle 包含当前正在执行的 SQL 语句的哈希值，如果你想要解决出现的问题，这 就是最有用的信息之一了。需要注意的是，强烈建议不要使用“*”从一个查询中返回所有的

8、行(尤其是在一个生 产数据库中)，这里只是为了举例而使用的。该查询剩下的部分用来返回从视图和在连接中使用子查询所获得的详细信息。在 WHERE 语句中，过滤掉任何小于等于 50 的数据库会话，以消除任何系统会话的影响。在 这个系统过程中，只需要关注用户会话。可以以视图形式实施以上的过程而不需要用存储 过程标记一个系统存储过程我们可以把自己创建的存储过程标记为系统存储过程，这样就可以在任何数据库环境 中运行该存储过程，并检索该数据库的特定信息。主数据库里创建一个对象，算完成了将 一个对象标记为系统存储过程的第一步。一旦在主数据库中有了该存储过程，接下来就要 运行另外一个系统存储过程来标记该对象。

9、调用另外一个系统存储过程来将自定义的存储 过程标记为系统存储过程如下：USE MASTER EXECUTE sp_ms_marksystemobject sp_Lock_Detail 这样我们就可以在自己的 SQL Server 实例下的任何数据库环境中执行 sp_Lock_Detail 存储过程，并返回该数据库的锁定信息。这比在每个用户数据库里都创建一个相同的存储 过程容易多了。接下来我们就来看看如何使用这个新的系统存储过程。首先，创建一个表并向表中载 入一些数据。CREATE TABLE LockMyData ( IDCol INT IDENTITY(1,1) PRIMARY KEY, N

10、umberField VARCHAR(5) ) GO DECLARE I INT SET I = 5000 WHILE i 0 BEGIN INSERT INTO LockMyData( NumberField) SELECT datepart(ms, getdate() + I SET I = I - 1 END 接下来更新表中某事务的记录并使该事务保持开放状态。在更新过程中，该事务将持 有对这些记录的锁定。然后添加 WITH(HOLDLOCK)锁提示，确保该数据库的事务隔离水平 不会影响到我们的测试。BEGIN TRANSACTION UPDATE TOP(2000) LockMyData

11、 WITH(HOLDLOCK) SET NumberField = NumberField + IDCol另起一个独立的查询会话，执行我们之前创建的 sp_Lock_Detail 存储过程。我们将在 与之前发布的 UPDATE 语句相同的数据库环境下执行该存储过程。EXECUTE sp_Lock_Detail我们的返回结果超过了 2000 行，表明发布的 UPDATE 指令使用了行级锁(KEY)来发布该 指令。该存储过程生成了很多有用的数据。我们可以立刻就能看到谁发布了这些指令、哪些 程序被用来发布这些指令、锁的类型等等信息。如果我们在原始 UPDATE 指令运行的时候 运行这条指令，那么我们

12、就能够看到引起锁定的确切指令了。总结系统存储过程是很有用的工具，使我们能够在一个数据库中编写存储过程，然后在其 他数据库环境下运行该过程。本文的 sp_Lock_Detail 只是一个例子，告诉我们如何编写自 定义的系统存储过程来监控自己的数据库。系统存储过程的用途还有很多，包括获取存储 在数据库中所有表的大小分配，同时生成用来存储 SQL 语句以节省编写代码的时间等等。 有兴趣的读者可以自己尝试，相信您一定会有所收获的。-实现跨多个表格的数据进行组合的 SQL 语句在对跨多个表格的数据进行组合时，有时很难搞清楚要使用哪一个 SQL 句法。我将在这里 对将多个表格中的查询合并至单一声明中的常用

13、方式进行阐述。 在这篇文章中的样本查询符合 SQL92 ISO 标准。不是所有的数据库生产商都遵循这项标准， 而且很多厂商采取的提升措施会带来一些意料不到的后果。如果你不确定你的数据库是不 是支持这些标准，你可以参看生产厂商的有关资料。 SELECT 一个简单的 SELECT 声明就是查询多个表格的最基本的方式。你可以在 FROM 子句中调用多 个表格来组合来自多个表格的结果。这里是一个它如何工作的实例： 以下为引用的内容： SELECT table1.column1, table2.column2 FROM table1, table2 WHERE table1.column1 = tabl

14、e2.column1; 这个实例中，我使用点号(table1.column1)来指定专栏来自哪一个表格。如果所涉及的专栏 只在一个参考的表格中出现，你就不需要加入完整的名称，但是加入完整名称会对可读性 起到帮助。 在 FROM 子句中表格之间由逗号来分隔，你可以加入所需的任意多的表格，尽管一些数据 库有一个在引入正式的 JOIN 声明之前他们可以有效地处理的内容这方面的限制，这个将在 下面谈到。 这个句法是一个简单的 INNER JOIN。一些数据库将它看成与一个外部的 JOIN 是等同的。 WHERE 子句告知数据库哪一个区域要做关联，而且它返回结果时，就像列出的表格在给定 的条件下组合成一

15、个单独的表格一样。值得注意的是，你的比较条件并不需要与你作为结 果组返回的专栏相同。在上面的例子中，table1.column1 和 table2.column1 用来组合表格， 但是返回的却是 table2.column2。 你可以在 WHERE 子句中使用 AND 关键字来将这个功能扩展至多于两个的表格。你还可以 使用这样的表格组合来限制你的结果而不用实际地从每个表格返回专栏。在下面的例子中， table3 与 table1 匹配，但是我没有从 table3 返回任何东西来显示。我只是确保来自 table1 的有关专栏存在于 table3 之中。注意此例中 table3 需要在 FROM

16、子句中被引用。 以下为引用的内容： SELECT table1.column1, table2.column2 FROM table1, table2, table3 WHERE table1.column1 = table2.column1 AND table1.column1 = table3.column1; 然而，要注意的是，这个查询多个表格的方式是一个暗指的 JOIN。你的数据库可能对事物 进行不同的处理，这取决于它所使用的优化引擎。而且，忽略对与 WHERE 子句的相关特性 的定义将会给你带来不愿看到的结果，例如从余下的查询中返回与每一个可能的结果相关 的专栏的 rogue 域，就像在 CROSS JOIN 之中一样。 如果你习惯于你的数据库处理这种类型的声明的方式，且你只对两个或是少数几个表格进 行组合，一个简单的 SELECT 声明就可以达