資料庫預存程序簡介與執行個體，資料庫預存程序執行個體

資料庫預存程序簡介與執行個體，資料庫預存程序執行個體

一、預存程序與函數的區別：

　　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代碼：

 1 private void button1_Click(object sender, EventArgs e) 
　 {
 2 　　string connStr = @"server=.\sqlexpress;database=MyDB;integrated security=true";
 3 　　using (SqlConnection conn = new SqlConnection(connStr))
 4 　　{
 5 　　　　//開啟資料庫連接
 6　　　　 conn.Open();
 7 　　　　//用預存程序名作為Command處理的對象
 8 　　　　string usp = "usp_page";
 9 　　　　using (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<Person> p = new List<Person>();
18 　　　　　　using (SqlDataReader reader = cmd.ExecuteReader())
19 　　　　　　{
20 　　　　　　　　if (reader.HasRows)
21 　　　　　　　　{
22 　　　　　　　　　　while (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類

1 class Person 
2 {
3 public string FName { get; set; }
4 public int FAge { get; set; }
5 public string FGender { get; set; }
6 public int FMath { get; set; }
7 public int FEnglish { get; set; }
8 }