一、存储过程入门
比如在ERP系统中我们要审核一张单据。如果在程序中实现，我们可能需要对单据数据进行各种合法性判断、对单据关联的上下流程单据进行各种合法性判断，还包括审核过程的处理。此外还存在事务提交一致性的问题，可能做了一半出错了，前面的也退不回来了。
如果写在存储过程中，我们只需要传入一个单据内码和其它必要信息，所有的判断和处理都在存储过程中执行，把执行的结果返回即可。这样耗用的网络流量是不是就小多了呢？
还有很重要的一点，存储过程是编译过的，第一次执行时会慢一些，因为编译过的存储过程装载到内存中，之后调用的时候，就会非常快。同时存储过程是在服务器执行的，客户端不需要太多承载，如果服务器配置好点，那系统的运行就会顺畅很多。
有朋友说存储过程会不会泄露自己的代码，这个担心也是多余的，因为存储过程是可以加密的。对于越大的系统，使用存储过程的好处越明显，非常有利于模块化。
比如笔者就特别喜欢写存储过程，上万行的存储过程像玩一样，不信您看下图：
SQL中的存储过程语法有点像我们平时写结构化程序，可以定义变量、初始化变量，可以有循环、分支等等。存储过程有自己的一套逻辑，下面我们从基础开始对存储过程做一个梳理。
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[存储过程名称]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[存储过程名称]
GO
CREATE procedure 存储过程名称
传入参数1 int,
传入参数2 nvarchar(255),
传入参数3 varchar(255),
传出参数1 varchar(500) output
with Encryption --如果需要加密就加上这句
as
begin
--这里是存储过程处理的SQL集合
end
GO
基本每种编程语言都支持调用存储过程，我这里就不讲了。在SQL Server内部，调用
declare @传出参数1 varchar(500)
exec 存储过程名称 @传入参数1, @传入参数2, ..., @传出参数1 output, ...
定义变量使用declare @变量名称 变量类型 的格式，一个declare可连续定义多个变量。SQL Server内置变量一般是@@开头，我们自己定义的变量则必须是@开头。
declare @变量名称 变量类型
eg.
declare @MyName nvarchar(20)
declare @MyAge smallint
declare @MyHeight decimal(28,10)
declare @MyBirth smalldatetime
declare @MyDad nvarchar(20), @MyMum nvarchar(20), @MySon nvarchar(20), @MyWork int
有一种变量叫表变量，非常实用，您可以当作是内存表，内部使用、自动释放，比大家常用的临时表好多了，设置表变量可以作为参数使用。举例：
declare @MyTable table(FSerial int identity(1,1), FBillID int...)
变量赋值很简单，set 变量=值 或 select 变量=值。使用select有可能会导致返回到程序接口中，一般没特殊需要还是用set好点。
set 变量=值 或 select 变量=值
eg.
set @MyName='网云技术'
set @MyAge=2
set @MyHeight=null
if是存储过程常用的分支语句，格式如下：
if 条件 单条语句
if 条件 begin 多条语句 end
if 条件 begin 多条语句 end else if 条件 begin 多条语句 end ...
eg.
if @MyAge=2 set @MyHeight=100 --单条语句
if @MyAge=2 begin set @MyHeigh=100 set @MyHeight=@MyHeight+10 end --多条语句
if @MyAge=2 begin ... end else if @MyAge=3 begin ... end ...
while是存储过程支持的唯一的循环语句，不支持for的，别想了。格式如下：
while 逻辑条件 begin
--循环内部处理
end
eg.
while @MyAge<1000 begin
set @MyHeight=@MyHeight+10
end
什么是游标，游标就是存储过程中的查询数据集，可以通过while循环对游标进行遍历和修改。举例如下：
if cursor_status('global','MyCursor')<>-3 or cursor_status('local','MyCursor')<>-3 deallocate MyCursor --如果游标存在先释放
declare MyCursor cursor read_only fast_forward for --创建只读 向前的游标
select FName, FAge, FHeight, FBirth from MyTable
open MyCursor --打开游标
fetch next from MyCursor into @MyName,@MyAge,@MyHeight,@MyBirth --先读取一行
while @@fetch_status=0 begin --表示游标还没有结束遍历
update MyTable set @MyHeight=isnull(FHeight,0)+10 --要做的事情
fetch next from MyCursor into @MyName,@MyAge,@MyHeight,@MyBirth --读取下一行
end
close MyCursor --关闭游标
deallocate MyCursor --释放游标二、巧用SQL语句查看SQLServer的结构信息
常用SQLServer的朋友们，绝大部分情况下，我们写SQL脚本主要用来处理我们自己的数据，比如读取、插入数据等。但在有些情况下，我们可能要查看我们定义的一些元素，比如我们定义的表都有哪些、表结构如何、存储过程的源码等等。这时候，我们学习的常规的SQL脚本可能就无法处理了。
比如您要写一个管理数据库的程序，就要知道数据库中有什么表、表结构如何，以及视图、存储过程的脚本源码等。下面我就说说如何获取数据库的结构信息。
1、获取数据库配置信息
exec sp_server_info
2、返回所有数据库
exec sp_databases
有三种方法可以使用，分别是：
1、利用sysobjects系统表或sys.objects目录视图
select * from sysobjects where xtype='U'
select * from sys.objects where type='U'
2、利用sys.tables目录视图
select * from sys.tables
3、利用存储过程sp_tables
exec sp_tables
有两种方法可以使用，分别是：
1、利用sysobjects系统表或sys.objects目录视图
select * from sysobjects where xtype='V'
select * from sys.objects where type='V'
2、利用sys.views目录视图
select * from sys.views
有两种方法可以使用，分别是：
1、利用sysobjects系统表或sys.objects目录视图
select * from sysobjects where xtype='P'
select * from sys.objects where type='P'
2、利用sys.procedures目录视图
select * from sys.procedures
要用到系统视图sys.all_objects和sys.sql_modules，写法如下：
select a.name,a.[type],b.[definition]
from sys.all_objects a,sys.sql_modules b
where a.is_ms_shipped=0 and a.object_id = b.object_id and a.[type] in ('P','V')
order by a.[name] asc
其中 sys.all_objects 是 sql server 2012 版本中的系统视图，在 更早期的 sql server 版本中，应该用 sys.objects，同时，sys.objects 在 2012 中也是可以用的，只不过考虑到后续兼容性，在新版本中，用新的 name 还是比较好。
sys.all_objects.type字段常用的类型有： P = 存储过程、V = 视图、U = 表（用户定义类型）、TT = 表类型 、AF = 聚合函数等。
sys.sql_modules.definition返回的就是定义存储过程或视图的脚本。
1、如果单纯获取字段名称，只需要用到系统表syscolumns。
select * from syscolumns where id=object_id('表名')
2、如果要获取字段和对应的数据类型，需要用到系统表syscolumns和systypes。
select a.name as [column],b.name as type
from syscolumns a,systypes b
where a.id=object_id('表名') and a.xtype=b.xtype
3、获取更详细的表结构信息，调用[INFORMATION_SCHEMA].[COLUMNS]。
SELECT c.TABLE_SCHEMA ,
c.TABLE_NAME ,
c.COLUMN_NAME ,
c.DATA_TYPE ,
c.CHARACTER_MAXIMUM_LENGTH ,
c.COLUMN_DEFAULT ,
c.IS_NULLABLE ,
c.NUMERIC_PRECISION ,
c.NUMERIC_SCALE
FROM [INFORMATION_SCHEMA].[COLUMNS] c
WHERE TABLE_NAME = '表名'
如果您能够活用这些系统内置的表、视图和存储过程，您总可以找到方法方便的查到想要知道的数据元素信息。
| 留言与评论（共有 0 条评论） “” |
