Stored procedure trong SQL Server (version 2000)

Định nghĩa: Stored procedure là tập các câu lệnh SQL được lưu trữ và biên dịch thành các Database Object do Database server quản lý. Sử dụng strored procedure sẽ nâng cao tính dùng lại, tăng hiệu suất chương trình, giảm tải cho Application server hay Web server vì công việc tính toán được chuyển cho Database server.

Định dạng Stored Procedure: cấu trúc chính của stored procedure thường có 2 phần: header và body. Header bao gồm các khai báo tên stored procedure, các parameter input và output. Body là tập hợp các câu lệnh SQL thực hiện tính toán.

Tạo stored procedure:

CREATE PROCEDURE  procedure_name

[ {@parameter  datatype} [=default] [OUTPUT] ]  [,1..n]

AS

SQL statements [1..n]

-procedure_name tối đa 128 ký tự và duy nhất trong database

-số lượng input và output tối đa là 2100

-Một stored procedure có dung lương tối đa là 128 MB

-Gọi stored procedure trong SQL dùng câu lệnh sau:   EXECUTE  procedure_name  [parameters]

Giá trị trả về từ stored procedure: giá trị trả về từ stored procedure có thể nhận được bằng các cách sau:

-Result set: các câu lệnh select thường trả về tất cả record có được

-Output parameter: gán giá trị cần trả về cho output parameter

-Return value: sử dụng câu lệnh return để trả về giá trị. Tuy nhiên, return chỉ trả về giá trị thuộc kiểu integer.

Identifier trong Stored Procedure:  các identifier được tên theo qui tắc sau:

-từ 1 đến 128 ký tự

-ký tự đầu tiên phải là : uderscore, @, #

-các ký tự tiếp theo: có thể thêm ký tự chữ và ký tự số

-không được chứa các ký tự đặc biệt, ngoại trừ @,#,_

-nếu các identifier không theo luật trên phải đặt giữa dấu ” ” hay [ ]

Data type: sử dụng data type giống như SQL Server

Variable: có hai loại local variable và global variable

-Local variable: biến local bắt đầu bằng @. Biến local chỉ có giá trị trong tầm vực của một stored procedure. Khai báo biến local dùng cú pháp:  DECLARE @variable_name  datatype. Để gán giá trị cho local variable, dùng lệnh  SELECT @variable_name = value hay SET  @variable_name = value. Đối với SELECT có thể gán giá trị cho nhiều biến cùng một lệnh. SET chỉ gán giá trị cho 1 biến. Tuy nhiên SET được sử dụng nhiền hơn. Để hiển thị giá trị của biến dùng lệnh  SELECT  @variable_name  hay PRINT  @variable_name.

-Global variable: Biến Global thường được quản lý bởi server. Biến Global chứa các giá trị về server hay session của user hiện tại. Biến Global có thể truy cập ở bất cứ đâu. Các biến Global thường chỉ trả về một giá trị và coi là scalar function. Một số biến Global thông dụng: @@identity: giá trị identity cuối cùng được cấp phát bởi hệ thống trong session hiện tại. @@error chứa giá trị 0 là stored procedure thực thi thành công, khác 0 là giá trị lỗi trả về khi câu lệnh SQL trong stored procedure có lỗi. @@rowcount: số record bị ảnh hưởng bởi câu lệnh SQL cuối cùng trong session hiện tại.

Comment:

-Một hàng:  –

-Nhiều hàng:   /* … */

Statement block: các câu lệnh SQL có thể đặt trong các khối lệnh. Các lệnh trong khối lệnh được đặt giữa BEGIN và END.

IF statement:

     IF  boolean expression

            BEGIN

                       SQL statements

           END

    [ELSE

          BEGIN

                       SQL statements

          END

      ]

-Sử dụng IF và EXISTS

  IF  [NOT]  EXISTS  (subquery)

            BEGIN

                       SQL statements

           END

    [ELSE

          BEGIN

                       SQL statements

          END

      ]

Cursor: khi làm việc với cursor sử dụng các bước sau:

1.Định nghĩa cursor và xác định tính năng

2.Xác định định vị trí của cursor

3.Di chuyển cursor đến record cấn phải lấy về

4.Xử lý thông tin trên record đã lấy về

5.Lập lại bước 3,4

6.Đóng cursor và giải phóng tài nguyên

DECLARE cursor_name cursor

FOR select_statement

(SET cursor_name

FOR select_statement)

OPEN cursor_name

FETCH  [Next|Prior|First|Last|Absolute {n | @nvar} | Relative {n|@nvar}]

FROM [GLOBAL] cursor_name

INTO var_name [1,..,n]

WHILE (@@fetch_status = 0)

   BEGIN

      SQL statements

     FETCH  [Next|Prior|First|Last|Absolute {n | @nvar} | Relative {n|@nvar}]

     FROM [GLOBAL] cursor_name

     INTO var_name [1,..,n]

  END

CLOSE cursor_name

DEALLOCATE  cursor_name

Các biến Global liên quan:

@@fetch_status: 0 : thành công; -1: cuối recrod set hay Fetch fail; -2: record bị mất (đã bị xóa bởi process khác)

@@cursor_rows: số record trong block mà cursor chỉ tới

Các cursor nếu chưa DEALLOCATE thì có thể OPEN lại.

Lấy giá trị trả về từ stored procedure trong C#:

-Trường hợp trả về result set:

string connectionString = “– your connection string–”;

SqlConnection conn = new SqlConnection(connectionString);

SqlCommand cmd = new SqlCommand(“–stored procedure — name”, conn);

cmd.Parameters.AddWithValue(“@parameter_name”, parametervalue);

cmd.CommandType = CommandType.StoredProcedure;

conn.Open();

SqlDataReader reader = cmd.ExecuteReader();

while (reader.Read())

{

    // your code here

}

reader.Close();

reader.Dispose();

conn.Close();

-Lấy giá trị trả về từ câu lệnh RETURN trong stored procedure:

string connectionString = “– your connection string–”;

SqlConnection conn = new SqlConnection(connectionString);

SqlCommand cmd = new SqlCommand(“–stored procedure — name”, conn);

cmd.Parameters.AddWithValue(“@parameter_name”, parametervalue);

cmd.Parameters.Add(new SqlParameter(“@returnValue”), SqlType.Int);

cmd.Parameters["@returnValue"].Direction = ParameterDirection.ReturnValue;

cmd.CommandType = CommandType.StoredProcedure;

conn.Open();

cmd.ExecuteNoQuery();

int returnValue = (int) cmd.Parameters["@returnValue"].Value;

conn.Close();

-Lấy giá trị trả về từ OUTPUT parameter: vì RETURN statement chỉ trả về các giá trị thuộc kiểu int, nên khi cần trả về các kiểu khác, bạn có thể sử dụng OUTPUT parameter.

string connectionString = “– your connection string–”;

SqlConnection conn = new SqlConnection(connectionString);

SqlCommand cmd = new SqlCommand(“–stored procedure — name”, conn);

cmd.Parameters.AddWithValue(“@parameter_name”, parametervalue);

cmd.Parameters.Add(new SqlParameter(“@output”), SqlType.Int); // bạn có thể thay đổi output type

cmd.Parameters["@output"].Direction = ParameterDirection.Output;

cmd.CommandType = CommandType.StoredProcedure;

conn.Open();

cmd.ExecuteNoQuery();

int returnValue = (int) cmd.Parameters["@output"].Value;

conn.Close();

Ph.

Advertisement