預存程序的活用

我自己感覺學程式是有個逐步的過程的，剛開始學和.net與資料庫互動是在c#中的command命令中寫一些資料庫操作的代碼，如：

select * from student where studentID = @studentID

 

update student set studentName=@StudentName where studentID = @studentID

 

insert into student (studentID,studentName) values(@studentID,@StudentName )

 

再後來知道sql語句有注入等功能，而且為了保護資料的安全性，以及資料分層架構更加清晰，讓程式做程式的事，資料庫處理資料相關的東西，知道了應用預存程序——create PROCEDURE ，但是這個時候儲存過程還只能實現單一的功能，比如說更新，比如說插入

 

再到最後認真思考思考發現對一張表的插入和刪除其實可以用一個預存程序來解決，可以對一些sql語句和預存程序進行最佳化。如：

通過一個ID號來判斷是需要插入新記錄還是修改記錄

 ALTER PROCEDURE [dbo].[newstudentEdit]<br />@studentID char(20),<br />@studentName varchar(50)<br />AS<br />if @studentID=''<br />begin<br />set @studentID=dbo.f_getNewID(getdate(),rand())<br />Insert Into student(studentID,studentName)<br />Values (@studentID,@studentName)<br />end<br />else<br />begin<br />Update student Set<br />studentName=@studentName<br />where studentID=@studentID<br />end

 

往往我們對於表中資料的擷取是不定的，有時候需要前三個欄位，有時候需要後面幾個欄位，這時候沒有必要每一種需求方式都寫一個預存程序，直接用一個預存程序讀出所有可能需要的欄位，然後在程式中根據需要做綁定。

還有就是多查詢的實現，如何去實現那？下面例子中是兩個查詢條件，如果有多個話，只要增加參數就可以了。

create ROCEDURE [dbo].[StudentList]<br />@studentIDchar(20)='',<br />@studentName varchar(50)='',<br />AS<br />declare @sqlWhere varchar(6000)<br />set @sqlWhere=' 1=1 '<br />if @studentID<>''set @sqlWhere=@sqlWhere+' and studentID='''+@studentID+''''<br />if @studentName<>''set @sqlWhere=@sqlWhere+' and studentName='''+@studentName+''''</p><p>exec sp_getPage @tableName='student',<br />@keyField='studnetID',<br />@strOrder='',<br />@strSelect=' * ',<br />@strINNER='',<br />@strWhere=@sqlWhere,

 

如果我有時候需要對studentID排序，有時候又需要對studentName排序，是不是要寫兩個預存程序啊？其實不需要，我們可以加一個排序欄位作為變數，動態指定排序的欄位。下面的例子中預設情況下用studentID做排序欄位。

CREATE PROCEDURE [dbo].[StudentList]<br />@studentIDchar(20)='',<br />@studentName varchar(50)='',<br />@orderNamevarchar(20)='',<br />@orderModeint=0<br />AS</p><p>declare @sqlWhere varchar(6000),@keyField varchar(50)<br />set @sqlWhere=' 1=1 '<br />if @studentID<>''set @sqlWhere=@sqlWhere+' and studentID='''+@studentID+''''<br />if @studentName<>''set @sqlWhere=@sqlWhere+' and studentName='''+@studentName+''''</p><p>set @keyField='studentID'<br />if @orderName='' set @orderName=@keyField<br />exec sp_getPage @tableName='student',<br />@keyField=@keyField,<br />@strOrder=@orderName,<br /> @orderMode=@orderMode,<br />@strSelect=' * ',<br />@strINNER='',<br />@strWhere=@sqlWhere

 

 我們經常在網站或者其他地方看到cridview控制項中每一列都有一個checkbox按鈕，然後可以選擇多行一起刪除，也就是大量刪除功能。首先我們把要刪除的列的所有值儲存在一個串中，用“|”符號去分隔，用迴圈去讀取。