SQL Server中如何写存储过程
来源:互联网 发布:windows 10 壁纸 编辑:程序博客网 时间:2024/06/02 19:51
1、设计存储过程
几乎任何可写成批处理的 Transact-SQL 代码都可用于创建存储过程。
2、存储过程的设计规则
存储过程的设计规则包括:
在存储过程内部,如果用于诸如 SELECT 或 INSERT 这样的语句的对象名没有限定用户,那么用户将默认为该存储过程的所有者。在存储过程内部,如果创建存储过程的用户没有限定 SELECT、INSERT、UPDATE 或 DELETE 语句中引用的表名,那么通过该存储过程对这些表进行的访问将默认地受到该过程的创建者权限的限制。
4、加密过程定义
如果有其他用户要使用存储过程,则用于语句 ALTER TABLE、CREATE TABLE、DROP TABLE、TRUNCATE TABLE、CREATE INDEX、DROP INDEX、UPDATE STATISTICS 和 DBCC 的对象名必须用该对象所有者的名称限定。例如,Mary 拥有表 marytab,如果她希望其他用户能够执行使用该表的存储过程,必须在该表用于上述某一条语句时对其表名进行限定。
此规则是必需的,因为运行存储过程时将解析对象的名称。如果未限定 marytab,而 John 试图执行该过程,SQL Server 将查找 John 所拥有的名为 marytab 的表。
如果要创建存储过程,并且希望确保其他用户无法查看该过程的定义,那么可以使用 WITH ENCRYPTION 子句。这样,过程定义将以不可读的形式存储。 存储过程一旦加密其定义即无法解密,任何人(包括存储过程的所有者或系统管理员)都将无法查看存储过程定义。 当 ODBC 应用程序与 SQL Server 连接时,服务器将自动设置会话的下列选项: 这些设置将提高 ODBC 应用程序的可移植性。由于基于 DB-Library 的应用程序通常不设置这些选项,所以应在上述所列 SET 选项打开和关闭的情况下都对存储过程进行测试。这样可确保存储过程始终能正确工作,而不管特定的连接在唤醒调用该存储过程时可能设置的选项。需要特别设置其中一个选项的存储过程,应在开始该存储过程时发出一条 SET 语句。此 SET 语句将只对该存储过程的执行保持有效,当该存储过程结束时,将恢复原设置。
A. 创建使用参数的存储过程
下例创建一个在 pubs 数据库中很有用的存储过程。给出一个作者的姓和名,该存储过程将显示该作者的每本书的标题和出版商。
AS
SELECT au_lname, au_fname, title, pub_name
FROM authors INNER JOIN titleauthor ON authors.au_id = titleauthor.au_id
JOIN titles ON titleauthor.title_id = titles.title_id
JOIN publishers ON titles.pub_id = publishers.pub_id
WHERE au_fname = @firstname
AND au_lname = @lastname
GO
将出现一条说明该命令未返回任何数据也未返回任何行的消息,这表示已创建该存储过程。
现在执行 au_info 存储过程:
EXECUTE au_info Ringer, AnneGO
下面是结果集:
au_lnameau_fnametitlepub_name-------------------------------------------------------RingerAnneThe Gourmet MicrowaveBinnet & HardleyRingerAnneIs Anger the Enemy?New Moon Books
(2 row(s) affected)
B. 创建使用参数默认值的存储过程
下例创建一个存储过程 pub_info2,该存储过程显示作为参数给出的出版商所出版的某本书的作者姓名。如果未提供出版商的名称,该存储过程将显示由 Algodata Infosystems 出版的书籍的作者。
CREATE PROC pub_info2 @pubname varchar(40) = 'Algodata Infosystems'
AS
SELECT au_lname, au_fname, pub_name
FROM authors a INNER JOIN titleauthor ta ON a.au_id = ta.au_id
JOIN titles t ON ta.title_id = t.title_id
JOIN publishers p ON t.pub_id = p.pub_id
WHERE @pubname = p.pub_name
执行未指定参数的 pub_info2:
EXECUTE pub_info2GO
下面是结果集:
au_lnameau_fnamepub_name----------------------------------------------------GreenMarjorieAlgodata InfosystemsBennetAbrahamAlgodata InfosystemsO'LearyMichaelAlgodata InfosystemsMacFeatherStearnsAlgodata InfosystemsStraightDeanAlgodata InfosystemsCarsonCherylAlgodata InfosystemsDullAnnAlgodata InfosystemsHunterSherylAlgodata InfosystemsLocksleyCharleneAlgodata Infosystems
(9 row(s) affected)
C. 执行用显式值替代参数默认值的存储过程
在下例中,存储过程 showind2 的 @table 参数默认值是 titles。
CREATE PROC showind2 @table varchar(30) = 'titles'
AS
SELECT TABLE_NAME = sysobjects.name,
INDEX_NAME = sysindexes.name, INDEX_ID = indid
FROM sysindexes INNER JOIN sysobjects ON sysobjects.id = sysindexes.id
WHERE sysobjects.name = @table
列标题(例如,TABLE_NAME)可使结果更具可读性。下面是该存储过程显示的 authors 表的情况:
EXECUTE showind2 authorsGO
TABLE_NAMEINDEX_NAMEINDEX_ID------------------------------authorsUPKCL_auidind1authorsaunmind2
(2 row(s) affected)
如果用户未提供值,则 SQL Server 将使用默认表 titles:
EXECUTE showind2GO
下面是结果集:
TABLE_NAMEINDEX_NAMEINDEX_ID------------------------------titlesUPKCL_titleidind1titlestitleind2
(2 row(s) affected)
D. 使用参数默认值 NULL 创建存储过程
参数默认值可以是 NULL 值。在这种情况下,如果未提供参数,则 SQL Server 将根据存储过程的其它语句执行存储过程。不会显示错误信息。
过程定义还可指定当不给出参数时要采取的其它某种措施。例如:
CREATE PROC showind3 @table varchar(30) = NULL
AS IF @table IS NULL
PRINT 'Give a table name'
ELSE
SELECT TABLE_NAME = sysobjects.name,
INDEX_NAME = sysindexes.name, INDEX_ID = indid
FROM sysindexes INNER JOIN sysobjects
ON sysobjects.id = sysindexes.id
WHERE sysobjects.name = @table
E. 使用包含通配符的参数默认值创建存储过程
如果存储过程将参数用于 LIKE 关键字,那么默认值可包括通配符(%、_、[] 和 [^])。例如,可将 showind 修改为当不提供参数时显示有关系统表的信息:
CREATE PROC showind4 @table varchar(30) = 'sys%'
AS SELECT TABLE_NAME = sysobjects.name,
INDEX_NAME = sysindexes.name, INDEX_ID = indid
FROM sysindexes INNER JOIN sysobjects
ON sysobjects.id = sysindexes.id
WHERE sysobjects.name LIKE @table
在存储过程 au_info 的下列变化形式中,两个参数都有带通配符的默认值:
CREATE PROC au_info2 @lastname varchar(30) = 'D%',如果执行 au_info2 时不指定参数,将显示姓以字母 D 开头的所有作者:
@firstname varchar(18) = '%'
AS
SELECT au_lname, au_fname, title, pub_name
FROM authors INNER JOIN titleauthor ON authors.au_id = titleauthor.au_id
JOIN titles ON titleauthor.title_id = titles.title_id
JOIN publishers ON titles.pub_id = publishers.pub_id
WHERE au_fname LIKE @firstname
AND au_lname LIKE @lastname
EXECUTE au_info2GO
下面是结果集:
au_lnameau_fnametitlepub_name--------------------------------------------------------DullAnnSecrets of Silicon ValAlgodata Infosystemsdel CastilloInnesSilicon Val GastronoBinnet & HardleyDeFranceMichelThe Gourmet MicrowaveBinnet & Hardley
(3 row(s) affected)
下例在两个参数的默认值已定义的情况下,省略了第二个参数,因此可找到姓为 Ringer 的所有作者的书和出版商:
EXECUTE au_info2 RingerGO
au_lnameau_fnametitlepub_name--------------------------------------------------------RingerAnneThe Gourmet MicrowaveBinnet & HardleyRingerAnneIs Anger the Enemy?New Moon BooksRingerAlbertIs Anger the Enemy?New Moon BooksRingerAlbertLife Without FearNew Moon Books
(4 row(s) affected)
- SQL Server中如何写存储过程
- SQL Server中如何写存储过程
- SQL Server中如何写存储过程
- SQL Server中如何写存储过程
- sql server写存储过程
- sql server中存储过程
- 如何在SQL Server存储过程中执行DTS包
- 如何在SQL Server存储过程中执行DTS包
- 如何在SQL Server存储过程中执行DTS包
- 如何在SQL Server存储过程中执行DTS包
- 在SQL Server 2005中如何列出所有存储过程
- 如何在C#中使用存储过程(SQL Server 2000)
- SQL Server存储过程中如何使用数组参数
- 如何调试SQL Server存储过程
- 如何调试SQL Server存储过程
- Sql Server 2008 如何调试存储过程
- 多层开发中,经常写SQL SERVER存储过程的朋友看过来!
- 技巧:SQL Server中写存储过程需注意的几点事项
- lazycode安全类
- Error inserting new pass entry into PassGroupRenderableMap
- 电脑配件的中英文对照
- 6 Command Line Tools for Linux Performance Monitoring
- 常见逻辑谬误摘要
- SQL Server中如何写存储过程
- Ubuntu10.04 下的英汉词典stratdict星际译王
- javascript导出excel并弹出保存框(only IE)
- Android中获取TextView和EditText中的文本
- 诸葛亮给儿子的一封信
- 人的一生最后悔什么?——曾获各界名流都认同的忠告!
- VS2008 引用程序集 没有强名称 解决办法
- 业界资讯:AIR apps for Android
- KeServiceDescriptorTable 与 KeServiceDescriptorTableShadow