sql数据库技术与应用学习笔记（数据库大师成长日记）

朋友们，当我们对SQL语法熟悉到一定程度之后，我们可能就会写一些存储过程。存储过程是预编译的SQL语句集合，可以传入传出参数，在存储过程体中，可以根据需要做任何自己相对依附数据库想做的操作。

比如在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 --释放游标

朋友们，有了这些基础知识，如果您对存储过程有兴趣还没有用过，您可以考虑尝试写一下了。