淺談資料庫中的儲存過程

一、儲存過程與函式的區別：

　　1.一般來說，儲存過程實現的功能要複雜一點，而函式的實現的功能針對性比較強。

　　2.對於儲存過程來說可以返回引數(output)，而函式只能返回值或者表物件。

　　3.儲存過程一般是作為一個獨立的部分來執行，而函式可以作為查詢語句的一個部分來呼叫，由於函式可以返回一個表物件，因此它可以在查詢語句中位於FROM關鍵字的後面。

二、儲存過程的優點：

　　1.執行速度更快 – 在資料庫中儲存的儲存過程語句都是編譯過的

　　2.允許模組化程式設計 – 類似方法的複用

　　3.提高系統安全性 – 防止SQL隱碼攻擊

　　4.減少網路流通量 – 只要傳輸儲存過程的名稱

系統儲存過程一般以sp開頭，使用者自定義的儲存過程一般以usp開頭

三、定義儲存過程語法，"[" 裡面的內容表示可選項

　　create proc 儲存過程名

　　@引數1 資料型別 [=預設值] [output],

　　@引數2 資料型別 [=預設值] [output],

　　...

　　as

　　SQL語句

四、簡單的一個例子

　　定義儲存過程：

　　create proc usp_StudentByGenderAge

　　@gender nvarchar(10) [='男'],

　　@age int [=30]

　　as

　　select * from MyStudent where FGender=@gender and FAge=@age

　　執行儲存過程：

Situation One(呼叫預設的引數):

　　exec usp_StudentByGenderAge

Situation Two(呼叫自己指定的引數):

　　exec usp_StudentByGenderAge '女',50

或者指定變數名 exec usp_StudentByGenderAge @age=50,@gender='女'

　　對儲存過程進行修改

　　alter proc usp_StudentByGenderAge

　　@gender nvarchar(10) [='男'],

　　@age int [=30],

--加output表示該引數是需要在儲存過程中賦值並返回的

　　@recorderCount int output

　　as

　　select * from MyStudent where FGender=@gender and FAge=@age

　　set @recorderCount=(select count(*) from MyStudent where FGender=@gender and FAge=@age)

--output引數的目的，就是呼叫者需要傳遞一個變數進來，然後在儲存過程中為該變數完成賦值工作，儲存過程執行完成以後，將執行的對應結果返回給傳遞進來的變數。(與C#中的out原理一模一樣)

呼叫(記住這裡的語法！)因為該儲存過程前面還有其他引數，所以要把 @recorderCount寫上，該儲存過程執行後，相當與完成了以上的查詢工作，同時將查詢結果得到的條數賦值給了@count變數。(@count是當做引數傳給usp_StudentByGenderAge，當儲存過程執行完畢以後，將得到的條數返回給@count)

　　declare @count int

　　exec usp_StudentByGenderAge @recorderCount=@count output

　　print @count

五、使用儲存過程完成分頁

1、儲存過程程式碼

　 create proc usp_page

　　@page int,  ---一頁顯示多少條記錄

　 @number int, ---使用者選擇了第幾頁資料

as

　　begin

　　select * from

　　--小括號裡面內容是專門得到排列好的序號

　　(

　　　　select ROW_NUMBER() over(order by(Fid)) as number

　　　　from MyStudent

　　) as t

　　where t.number>= (@number-1)*@page+1 and t.number<=@number*@page

　　 end

2、實現分頁效果對應的ADO.NET程式碼：

1privatevoid button1_Click(object sender, EventArgs e)

　 {

2string connStr = @"server=.\sqlexpress;database=MyDB;integrated security=true";

3using (SqlConnection conn = new SqlConnection(connStr))

4 　　{

5//開啟資料庫連線

6　　　　 conn.Open();

7//用儲存過程名作為Command處理的物件

8string usp = "usp_page";

9using (SqlCommand cmd = new SqlCommand(usp, conn))

10 　　　　{

11//執行的是儲存過程語句

12 　　　　　　cmd.CommandType = CommandType.StoredProcedure;

　//textBox1.Text是指顯示多少條記錄

13 　　　　　　cmd.Parameters.AddWithValue("@page", textBox1.Text.Trim());

14//textBox.Text是指使用者選擇了第幾頁

15 　　　　　　cmd.Parameters.AddWithValue("@number", textBox2.Text.Trim());

16//用list作為資料來源來實現

17 　　　　　　List p = new List();

18using (SqlDataReader reader = cmd.ExecuteReader())

19 　　　　　　{

20if (reader.HasRows)

21 　　　　　　　　{

22while (reader.Read())

24 　　　　　　　　　　{

25 　　　　　　　　　　　　Person p1 = new Person();

26 　　　　　　　　　　　　p1.FName = reader.GetString(1);

27 　　　　　　　　　　　　p1.FAge = reader.GetInt32(2);

28 　　　　　　　　　　　　p1.FGender = reader.GetString(3);

29　　　　　　　　　　　　 p1.FMath = reader.GetInt32(4);

30 　　　　　　　　　　　　p1.FEnglish = reader.GetInt32(5);

31 　　　　　　　　　　　　p.Add(p1);

32 　　　　　　　　　　}

33 　　　　　　　　}

34 　　　　　　}

35 　　　　　　dataGridView1.DataSource = p;

36 　　　　}

37 　　}

38 }

下面是自定義的Person類

轉自淺談資料庫中的儲存過程 - 秋恨雪 - 部落格園