存储过程基本步骤与实例

最新推荐文章于 2024-06-17 15:00:11 发布

laolaowhn

最新推荐文章于 2024-06-17 15:00:11 发布

阅读量951

点赞数

分类专栏： 1.sql 文章标签：存储 table sql scroll go comments

1.sql 专栏收录该内容

50 篇文章 0 订阅

订阅专栏

存储过程基本步骤与实例

寫SP的基本步驟:

1.先在查詢分析器中 Create procedure 程式名稱，例如:Create procedure SP_PSP_1001S01 AS(這裡是快速創建，可以不用寫參數)

--注：SP的命名要有一定的規范，一般情況是SP+稱式名稱，但當系統分模組時，模組名稱也最好加入

2.加入備注，說明等信息，這些是讓別的人員能很快的知道你的SP是作什麼用和怎麼用的，一般包含以下信息,格式如下:

/********************************************************************

'程式代號：(這裡接著填寫程式的代號)

'程式名稱：(這裡接著填寫程式的名稱)

'目　　的：(這裡填寫產生該存儲過程的作用，是為了實現什麼功能)

'參數說明：(這裡對所傳入的參數進行相關說明，例如：@s_MerNo 特店代號)

'依存　　：(這裡寫明存儲過程的相依存儲過程)

'傳回值　：(說面該存儲過程返回的是什麼類型值，例如：string,Table等)

'副作用　：(對數據庫有何不利影響)

'備　註　：(相關補充說面)

'範　例　：(可以運行的範例)

'版本變更：

'　xx. YYYY/MM/DD VER AUTHOR COMMENTS

' === ============ ==== ========== ==========

' 序號時間版本號作者作業類型(創建or修改) ********************************************************************/

3.sp分為引入參數和返回值部分

即在 ALTER PROCEDURE [dbo].[程式名稱] 和 AS之間的部分

返回值的聲明如：@i_Count int OUTPUT

引入參數聲明如：@i_Count int

每返回參數和引入參數之間要用","號隔開

--注：這裡參數的命名也要能很好的反映出基本的信息出來，如@i_Count 能知道它是一個INT型的變量，一般數值型用i，字符型用s，日期型用d等，及用第一個字母的小寫，可以和系統程式命名接合。

4.sp的正文(首先，sp能不用串字符串實現的盡量不用,所有關鍵字都要大寫)

a.在sp正文中，定義變量用Declare,例如：Declare @s_MerNo CHAR(10) 這樣就定義了一個CHAR型長度為10的變量

b.給變量名稱賦值用SET,例如：SET @s_MerNo='0123456789',也可以利用Select來賦值，但Select可以同時賦多個變量的值，例如:1.Select @s_MerNo=’ 1’ 、2.

Select @s_MerNo=MerNo,@s_CName=MerName From Mer_MerChant where MerNo='0123456789',這樣就分別給兩個變量賦了值,但Select出來的結果最好是能唯一對應出來，要不得到的結果肯定不會是你想要的，它都會對應到數據集的最後一條記錄，Select通常在單一主鍵的情況下使用比較好，當然不是一定要這樣。

c.捕獲異常用@@error，可以利用if來判斷，

例如：

if(@@error<>0)

begin

--注：這裡加入出錯時要處理的步驟

return 1

end

d.sp中通常用到的函數有：Convert(),getdate(),tostring(),count()等，這個可以參考Server2000，裡面分出了好多函數和用法

e.語句執行，一般是把Sql語句串成了字符串才會用到，用exec(@s_Sql) ,

例如:

declare @s_Sql varchar(8000)

set @s_Sql ='select * from mer_merchant'

exec(@s_Sql)

f.sp可以劃分為查詢和操作(刪除，修改，新增)兩快來處理,查詢時，返回的一般以Table為主，因此可以不用設置返回值的聲明，但操作的劃一般返回的是一個狀態，成功或失敗，因此就要聲明,當查詢返回的是值類型的話肯定也要聲明的。

g.sp中的事務用法:主要3個步驟，開始，回滾和執行

以下是一個向數据庫添加數据操作的存儲過程：

CREATE PROCEDURE dbo.p_my_DocumentAdd

(

@i_Flag as int out,

@DocumentID AS char(10),

@Title AS VarChar(100),

@Comment AS Text,

@Hits AS Int,

@Type AS VarChar(20),

@Gender AS TinyInt

)

set @i_Flag =0

BEGIN

BEGIN TRAN--開始事務

INSERT INTO Document (0

DocumentID,

Title,

Comment,

Hits,

Type,

Gender)

VALUES(

@DocumentID,

@Title,

@Comment,

@Hits,

@Type,

@Gender)

--出錯處理

IF (@@error <> 0)

BEGIN

ROLLBACK TRAN--回滾事務

set @i_Flag =1

End

COMMIT TRAN--如果沒有錯誤則提交事務

END

h.sp中游標的使用：定義、打開、存取、關閉、釋放游標一級游標的應用

什麼情況下使用游標：我們常常會遇到這樣的情況，即從一個結果集中逐一的讀取每一條記錄。如何解決這個問題呢？有兩種方法：1、使用游標，2、自己動手寫循環

a1.游標的種類，可分為3種：Transact_SQL游標、API游標和客戶游標。Transact_SQL游標主要用在服務器上，由從客戶端發送給服務器的Transact_SQL語句或批處理、存儲過程、觸發器的Transact_SQL進行管理，不支持提取數據塊和多行數據。API游標主要用在服務器上，支持OLE DB，ODBC以及DB_library中使用游標函數，它們都會將客戶請求傳送給服務器以對API游標進行處理。客戶游標主要是當在客戶機上緩存結果集時才使用，它僅支持靜態游標，通常作為服務器游標的輔助。(後台游標和前台游標)

b1.DECLARE 游標，OPEN游標，從一個游標中FETCH信息，CLOSE或DEALLOCATE游標

DECLARE cursor_name [INSENSITIVE][SCROLL] CURSOR

FOR SELECT_statement

[FOR {READ ONLY | UPDATE [OF column_name [,...n]]}]

其中:INSENSITIVE表示會將游標定義所選取出來的數據記錄存放在一臨時表內，SCROLL表明所有的操作都可用，SELECT_statement表示結果集，READ ONLY表示不允許游標內的數據被更新，UPDATE 表示可以被修改,LOCAL表示局部，GLOBAL表示全局

實例：修改(通過測試，可以直接放在server 2000的pubs下直接運行)

/************************************************************************/

SET QUOTED_IDENTIFIER ON

SET ANSI_NULLS ON

--exec SP_TESTCOU

ALTER PROCEDURE SP_TESTCOU

BEGIN

declare @au_id varchar(11),@au_lname varchar(40),@au_fname varchar(20)

declare authors_cur cursor

for

select au_id,au_lname,au_fname

from authors

for update of au_id,au_lname,au_fname

open authors_cur

fetch next from authors_cur into @au_id,@au_lname,@au_fname

while @@fetch_status=0

begin

if @au_id='172-32-1176'

update authors

set au_lname='klint',au_fname='kong'

where current of authors_cur

fetch next from authors_cur into @au_id,@au_lname,@au_fname

end

CLOSE authors_cur

deallocate authors_cur

END

SET QUOTED_IDENTIFIER OFF

SET ANSI_NULLS ON

/************************************************************************/

：刪除(可以參考上面修改的來實現)

declare authors_cur scroll cousor //聲明一個游標

for select * from authors //返回的結果集

for delete of au_lname,au_fname //可選的數據操作

delete from authors

where current of authors_cur

i.循環的利用,在很多情況下，經常會碰到要用循環的時候，例如循環插入，循環讀起資料等，下面舉個簡單的循環插入的例子

declare @i int

set @i=1

while @i<10

begin

insert into test (userid) values(@i)

set @i=@i+1

end

j.子查詢中的條件什麼情況下使用in和not in，在使用一個結果集的條件下，使用in或not in

例如：select * from mer_merchant where merno in('1','2','3')反之用not in

k. EXCEPT 運算符

EXCEPT 運算符通過包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重复行而派生出一個結果表。當ALL隨EXCEPT 一起使用時(EXCEPT ALL)，不消除重复行。

m. INTERSECT 運算符

INTERSECT 運算符通過只包括 TABLE1 和 TABLE2 中都有的行并消除所有重复行而派生出一個結果表。當ALL隨INTERSECT 一起使用時 (INTERSECT ALL)，不消除重复行。注：使用運算時的几個查詢結果行必須是一致的。

l.如果使用到了臨時表，在存儲過程的最后務必將所有的臨時表顯式刪除，先truncate table，然后drop table，這樣可以避免系統表的較常時間鎖定。

n.有時候，在用游標時，有想避免使用游標，這裡提供一條思路，如下：

首先，將要操作的資料插入一個臨時表(注意，當資料表中有多主鍵時，需要再創建臨時表把字段的屬性去掉，因為字段在Select中有繼承原來表中屬性的功能，而現在又要在臨時表中插入標簽，這樣就會又沖突)

/************************************************************************/

select A.*,IDENTITY(INT,1,1) AS FLAG INTO #臨時表名 FROM A WHERE ... --注：表和條件可以按自己隨便增加

--由於插入IDENTITY是從1開始的，因此循環也從一開始

declare @i_count int

declare @i_Flag int

set @i_Flag=1

select @i_count=count(*) from #臨時表名

while @i_Flag<=@i_count --注：在用<、>時，盡可能用<=或>=，不要單用<、>號

begin

--在這裡可以加入要循環處理的事情,例如判斷A表中是否有MerNo等於000000000000001

declare @s_MerNo char(15)

select @s_MerNo=MerNo From #臨時表名 where Flag=@i_Flag

IF @s_MerNo ='000000000000001'

begin

--可以加入處理的內容

end

set @i_Flag=@i_Flag+1

end

Drop Table #臨時表名

/************************************************************************/

o.呈現錯誤信息,要顯示自己所定義的內容時

IF((@DateE='')OR(@DateS='')) --如果兩個時間的中有一個為空

RAISERROR ('對不起，時間區間不能為空，請輸入!!',16,1) --?出異常信息

p.檢查數據庫中是否存在指定的系統表名:

IF EXISTS(SELECT * FROM tempdb..sysobjects WHERE name=指定表名) --判斷是否存在

EXEC('DROP TABLE ' + 指定表名) --存在則刪除

q.在串字符串語句時，要怎麼樣區分'','''','''''',的用法。

一般''號的話，是引入變量，如常見情況：@s_Sql=@s_Sql+' '+ @s_MerNo +'

而''''號的話，則是加如常量，如: CASE MerNo WHEN ''2'' THEN ''''

再就是''''''

5.一些基本的語法的使用

a.SELECT的使用

1,使用SELECT賦值,select @s_MerNo=’ 0000000’

    2. DISTINCT,消除重復,select DISTINCT au_id FROM titleauthor

3.BETWEEN…AND 選起區間

SELECT * FROM titleauthor

WHERE royaltyper BETWEEN 20 AND 30 ORDER BY royaltyper

(這些基本語法太多了，可以參考說明文檔，不一一說明)

6.一個寫SP的思路及過程：

1.每個SP的開始，都要創建它，這個時候只要注意命名規則即可(好的命名規范的養成是很有必要的)

CREATE PROCEDURE SP_TEST

運行(按F5):這樣，一個命名為SP_TEST的存儲過程就創建好了(當然，也可以直接寫出所有的再運行，這只是我感覺比較方便的方法而已)

2.接下來可以跑到相應的數據庫下的預存程序去找到你的存儲過程，右鍵點擊後選擇編輯，進行開始真正的CODE工作。

3.在編輯後的畫面中將會看到這樣的代碼：(注：接下來的F4是看代碼是否有錯誤，不會存檔，F5是執行，會存檔，一般先用F4調試完成後在執行，這樣比較不浪費時間)

SET QUOTED_IDENTIFIER ON –注：当 SET QUOTED_IDENTIFIER 为 ON 时，标识符

--可以由双引号分隔，而文字必须由单引号分隔。当 SET --QUOTED_IDENTIFIER 为 OFF 时，标识符不可加引号，

--且必须遵守所有 Transact-SQL 标识符规则。

SET ANSI_NULLS ON --注：指定在对空值使用等于 (=) 和不等于 (<>) 比较运算符时,當

--為off時表示不遵循該規則

ALTER PROCEDURE SP_TEST

SET QUOTED_IDENTIFIER OFF

SET ANSI_NULLS ON

4.首先，在ALTER PROCEDURE SP_TEST AS中間加入擇要說明,如下:

/********************************************************************

'程式代號：SP_TEST

'程式名稱：測試寫SP

'目　　的：一個SP的說明例子

'參數說明：無

'依存　　：無

'傳回值　：Table

'副作用　：無

'備　註　：這個沒有傳參數和返回參數，如過要看，上面有個事務的例子可

' 以參考

'範　例　：EXEC SP_TEST

'版本變更：

'　xx. YYYY/MM/DD VER AUTHOR COMMENTS

' === ============ ==== ========== ==========

' 1 2006/04/04 1.0.0 KLINT CERATE

********************************************************************/

這樣，擇要就寫好了

5．接下來就是實現邏輯了：

首先要注意的一點還是命名規范，命名要有意義，不要定義寫@i_i這樣的變量名稱，關鍵字要大寫(如：INT)，用過的系統臨時表要刪除，事務要執行或回滾調，游標要釋放調等，寫邏輯的時候，主次要分明，同一個臨時表不要重復創建，全局臨時表要產生成唯一的表名不要出現重復表明的現象;判斷是資料表是否有資料不要用COUNT(*) >=0等。下面就寫句簡單的查循表：

SELECT AU_ID,AU_LNAME,AU_FNAME ,PHONE,ADDRESS

FROM AUTHORS