SQL Server與MySQL儲存過程學習記錄之一

SQL語句執行的時候要先編譯，然後執行。

儲存過程（Stored Procedure）是一組為了完成特定功能的SQL語句集，經編譯後儲存在資料庫中。使用者通過指定儲存過程的名字並給出引數（如果該儲存過程帶有引數）來執行它。儲存過程是資料庫中的一個重要物件，任何一個設計良好的資料庫應用程式都應該用到儲存過程。

　　SQL Server的儲存過程在設計BNU校園百科的時候就有使用，因為儲存過程不僅能提高SQL語言的執行效率，同時引數化的呼叫比每次都寫一條SQL語句來得方便。

【SQL Server儲存過程的建立】

　　在企業管理器新建一個StoreProcedure會自動生成相應導航程式碼，方便使用者編寫函式。

-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:   <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName> 
-- Add the parameters for the stored procedure here
<@Param1, sysname, @p1> <Datatype_For_Param1, , int> = <Default_Value_For_Param1, , 0>, 
<@Param2, sysname, @p2> <Datatype_For_Param2, , int> = <Default_Value_For_Param2, , 0>
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

    -- Insert statements for procedure here
SELECT <@Param1, sysname, @p1>, <@Param2, sysname, @p2>
END
GO

　　我們現在設計一個帶引數的儲存過程，比如我們經常需要使用這樣的一類查詢：包含keyword的所有結果集，這個時候為該查詢實現一個儲存過程如下

CREATE PROCEDURE [dbo].[p_SearchByKeyWord]
@keyword nvarchar(200)
AS
BEGIN
SELECT * From [myTable] where [myWordColumn] like '%'+@keyword+'%'
END
GO

　　在SQL Server中呼叫儲存過程的方法是使用EXEC命令，比如我們要查包含“北京師範大學”的所有記錄，我們執行如下T-SQL命令

EXEC p_SearchByKeyWord  @keyword = '北京師範大學'

　　就能得到結果集如下：如果有多個引數，需要用逗號隔開分別傳入，即

EXEC myProcedureName  @parameter1='×××',@parameter2='×××',......

　　如果想要修改已經存在的儲存過程，只要把CREATE換成ALTER即可，比如我們限定查詢數量為100條記錄，修改已經建立的p_SearchByKeyWord

ALTER PROCEDURE  [dbo].[p_SearchByKeyWord]
@keyword nvarchar(200)
AS
BEGIN
SELECT TOP 100 * From [myTable] where [myWordColumn] like '%'+@keyword+'%'
END
GO

　　刪除儲存過程就更簡單了，直接使用DROP命令，比如

DROP PROCEDURE [dbo].[p_SearchByKeyWord]

【MySQL儲存過程的建立】

　　MySQL儲存過車的建立、修改、刪除語法與SQL Server類似，但是引數定義和呼叫方式都不一樣，所以需要花時間看官方的手冊！

　　MYSQL沒有提供微軟企業管理那樣的強大前端，當然就沒有建立儲存過程的嚮導，但是一旦熟悉了儲存過程建立的方法，就覺得自己手寫要方便得多。即使有很多第三方的MySQL前端，如Navicat提供了嚮導式的儲存過程建立，但是為了瞭解底層的語法細節，手動編寫每一步是很重要的。下面來看MySQL的儲存過程建立方式。

　　官方說明有這樣一段

CREATE
    [DEFINER = { user | CURRENT_USER }]
    PROCEDURE sp_name ([proc_parameter[,...]])
    [characteristic ...] routine_body
    
proc_parameter:
    [ IN | OUT | INOUT ] param_name type

type:
    Any valid MySQL data type

characteristic:
    LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'

routine_body:
    Valid SQL procedure statement

　　很好理解，比如我們要建立一個簡單的不帶引數的儲存過程p_Test，只需如下極短的程式碼

CREATE p_Test()
BEGIN
Select * FROM [myTable] LIMIT 100;
END;

　　其中BEGIN和END塊是儲存過程的核心，這個與SQL Server相同。另外，需要注意的是，不管有沒有引數，儲存過程名稱後面需要有一個括號，這個與SQL Server是不相同的（有引數和沒有引數均不需要任何括號），所以不要忘了。

　　現在來建立一個帶有引數的查詢，記住，所有引數都要包含在剛才所說的括號中。比如我們需要統計在給定兩個時間點的所有訪問記錄數，於是每次呼叫需要傳入起始時間和結束時間，於是需要指定兩個引數，如下 _starttime 和 _endtime ，這兩個變數之前的 IN 用來修飾引數，表示是輸入變數，也可以忽略（預設即輸入），也可以顯示指定為OUT（輸出變數），INOUT（既作為輸入，也作為輸出，在某些複雜的程式中可能會遇到）

CREATE PROCEDURE p_GetVisitCountByTime(
IN _starttime datetime,
IN _endtime datetime
)
BEGIN
SELECT COUNT(VISIT_ID) AS total FROM myTable
             WHERE VISIT_TIME>=_starttime and 
             VISIT_TIME<_endtime and;
END;

　　建立好之後就能呼叫了，注意MySQL呼叫儲存過程的方法和SQL Server是不一樣的，需要使用語法CALL，然後引數全部包含在括號裡面，比如

CALL p_GetVisitCountByTime('2009-12-01 00:00:00','2009-12-02 00:00:00');

　　這句話查詢在09年12月1號到12月2號的總訪問人數，清晰明瞭。

　　最後說明一些要注意的地方，也是SQL Server與MySQL差異的地方，在編寫儲存過程的時候，SQL Server並不需要在每個塊結束後使用分號，但是MySQL需要在每個塊結束後使用分號標識，比如

CREATE p_Test()
BEGIN
Select * FROM [myTable] LIMIT 100;
END--這裡沒有分號

　　那麼會提示編譯錯誤，同樣，如果是這樣

CREATE p_Test()
BEGIN
Select * FROM [myTable] LIMIT 100--這裡沒有分號
END;

　　那麼也會提示編譯錯誤，所以在MySQL裡一定不要忘了分號，當然SQL Server則不需要分號！

　　更多關於儲存過程裡迴圈、條件、定義內部變數等高階的應用和使用上的差異會在學習記錄之二再談，其實也就是SQL Server和MySQL的程式設計語法！

　　總結一下，由於儲存過程是資料庫的一個重要部分，因此不同的資料庫都有起實現方式，但是思想是一致的，在學習不同資料庫相應操作的時候，應該進行類比學習，這樣在做特定應用的時候就能更加順手！我也是現在經常遇到基於MySQL的專案才開始關注MySQL的各種細節！

　　可以繼續閱讀學習記錄之二

在學習記錄一中，討論了SQL Server與MySQL各自建立儲存過程的方法和過程，並簡單做了一下對比。在實際應用中，查詢往往是很複雜的，不可能用一簡單的一行SQL語言就能滿足我們的需求，這個時候，就需要使用SQL程式設計。

　　實現SQL程式設計有兩種方式，第一種就是用高階語言進行反覆呼叫，因為我們熟悉一門或多門高階程式語言，比如C，C++，C#，PHP，Java等等，這些都是能呼叫資料庫操作的。比如在C#這樣的物件導向程式語言裡，萬物皆物件，在建立一個資料庫呼叫程式時，首先需要新建一個連線，同時建立一條SQL操作指令，然後開啟連線，執行該指令，最後返回結果，關閉連線。

　　首先以C#呼叫SQL Server為例，比如我們要執行一條沒有返回結果的SQL語句並賦值給sqlStr字串變數，那麼用簡單的幾行程式就能直接運算元據庫了。（需要System.Data和System.Data.SqlClient名稱空間）

SqlConnection sqlConn = new SqlConnection(connectStr);
SqlCommand sqlCmd = new SqlCommand(sqlStr, sqlConn);
sqlConn.Open();
sqlCmd.ExecuteNonQuery();
sqlConn.Close();

　　於是，要進行復雜的SQL操作時，只要保持資料庫的連線開啟，不斷改變SqlCommand命令來實現不同的操作，如動態生成sqlStr。

　　同樣的，使用C#連線MySQL資料庫時，需要下載MySQL Connector NET，這是一個封裝好的用來連線MySQL的類庫，所有命令和連線SQL Server毫無差別，只需把Sql換成MySql即可，如下。（需要MySql.Data和MySql.Data.MySqlClient名稱空間）

MySqlConnection sqlConn = new MySqlConnection(connectStr);
MySqlCommand sqlCmd = new MySqlCommand(sqlStr, sqlConn);
sqlConn.Open();
sqlCmd.ExecuteNonQuery();
sqlConn.Close();

　　但是如果要批量執行SQL語言，通過高階語言迴圈方式傳入不免帶來效率上的劣勢，因為每次傳入的SQL語句都要先編譯，然後執行，同時在傳送SQL命令的時候也有時間代價（尤其是遠端資料庫），為何不只傳送一條命令，然後在資料庫裡進行批量執行呢？這也就是儲存過程的好處，可以把儲存過程看成資料庫操作的一個巨集，或者批處理。當然，這要資料庫支援才行。高興的是，目前的所有企業級資料庫都很好的支援了SQL程式設計，只是實現細節有差別。下面具體談談SQL Server與MySQL的SQL程式設計方法。

【SQL Server簡單程式設計】

該部分轉自http://www.cnblogs.com/Niyowong/archive/2007/08/20/862169.html，如果要學習更高階的內容可以購買人民郵電出版社的《SQL Server 2008基礎教程》

1.區域性變數

宣告單個區域性變數

declare @num int

宣告多個區域性變數

declare   @FirstName nvarchar(20)
                @LastName nvarchar(20)
                @Age int

區域性變數賦值
被賦值的區域性變數必須是已經宣告的。

a.簡單賦值方法

declare @UserName varchar(10)
set @UserName = 'Niyo Wong'

b.使用select語句賦值

delcare @NoOfRecords int
set @NoOfRecords = (select count(*) from tableName)
select @NoOfRecords = 20
declare @UserName varchar(20)
declare @UserId varchar(10)
select @UserName = userName from tbl_User where userId = '123401'
select @UserId = max(UserId) from tbl_User

注意:如果查詢返回了多個值時，那麼只有最後一個值賦給了變數。

c.使用update語句賦值

declare @qyt tinying
update tableName set @qty = fieldName where id = '1'
注意：update無法象select語句一樣魏資料提供一些常用的轉換，所以在使用update進行賦值時，
最好嚴格匹配資料型別，否則會產生錯誤。

2.全域性變數

下面列舉幾個我們在程式設計中常用的全域性變數

a. @@CURSOR_ROWS

返回本次伺服器連線中，開啟遊標取回的資料行的數目。如：

select @@CURSOR_ROWS
declare employee_cursor cursor for
select emplid from tbl_Employee
open employee_cursor
fetch next from employee_cursor
select @@CURSOR_ROWS
close employee_cursor
deallocate employee_cursor

b. @@ERROR

返回上一條語句返回的錯誤號，執行成功返回0，
一般在insert,update,delete語句之後使用(常結合事務處理)。

c. @@FETCH_STATUS

返回上一次使用遊標FETCH操作所返回的狀態值。返回值為0表示操作成功，
為-1表示操作失敗或者已經超過了遊標所能操作的資料行的範圍，當到了最後一行資料後，
還要接著取下一列資料，返回-2，表示返回值已經丟失。

d. @@ROWSCOUNT

返回上一條SQL語句所影響到的資料行的資料。常用於檢查操作是否達到了目的，
當執行的SQL語句不影響資料庫資料時，該變數返回0

e. @@VERSION

返回版本號

3.結構語句

a.條件結構
if.... else ...如：

if((select count(*) from table1) > 0)
begin
declare @num int
set @num = (select max(no) from tabl2)
if(@num >(select count(*) from table1))
begin
print '@num >(select count(*) from table1)'
end
else
if(@num = (select count(*) from table1))
begin
   print '@num = (select count(*) from table1)'
end
end
else
begin
print 'No of record below zero'
end

b.迴圈結構
while 語句。如：

declare @count int
set @count = 0
while(@count < 10)
begin
print @count
set @count = @count + 1 
end

在迴圈中常用的語句有break和continue,
break為跳出while,而continue為跳出當前迴圈，進入下一迴圈。
有時候也用到return和goto語句，下面我們將講這兩個語句。

c.case語句
case語句又叫條件分支語句。如：

select case userType 
when '1' then 'admin' 
when '2' then 'general user' 
else 'other' end 'userType' 
from tbl_user

或者

select 'userType' = case
when USERiD = '1' then 'admin'
when userName = 'Lucy' then 'admin'
when userType = '1' then 'admin'
when userType = '2' then 'general user'
else 'other' end
from tbl_user

注意：case語句中when匹配成功後，就到end,不會匹配下一個when,
所以如果有一條記錄，userid = '1' 並且usertype = '2'，
則返回uertype是‘admin'而不是’general user'

d.return語句
立即退出程式,return後面的語句將不執行。return 後常跟一個整形表示式作為返回值。

e.goto語句（最好不用goto語法）
跳轉到跟在goto後面的標籤位置。如

declare @count int
              @value int
set @count = (select count(*) from table)
if(@count = 0)
begin
    set @value = 0
    goto Flag
end
set @value = @count + 10
Flag:
print @value

【MySQL簡單程式設計】

　　MySQL程式設計與SQL Server類似，也要包含在所謂的區塊BEGIN和END裡，同時END後面必須要有分號（SQL Server不需要分號）。下面具體講解變數定義、條件、迴圈的基本語法。

1、變數定義

declare varname vartype;

比如 declare a int--定義一個整型變數a，也可以用default來宣告預設賦值，如 declare a int default 5

2、結構語句

a.定義區塊

BEGIN
--sqlStr
END;

或自定義區塊名稱

LABEL:BEGIN
--sqlStr
END LABEL;
這樣就可以用LEAVE LABEL;跳出區塊，執行區塊以後的程式碼

b.條件語句

if 條件 then
--my statement
else
--my statement
end if;

c.迴圈語句

[label:] WHILE expression DO
--sqlStr
END WHILE [label] ;

或

[label:] LOOP
--sqlStr
END LOOP [label];

或

[label:] REPEAT
--sqlStr
UNTIL expression
END REPEAT [label];

下面寫一個綜合的程式如下

create procedrue p_test()
begin
declare a int;
set a=0;
while a<10 do
set a = a+1;
end while;
select a;
end;

　　可以看到，使用MySQL程式設計和SQL Server基本思想是一致的，語法有較大的區別。SQL Server不管是條件、迴圈等區塊仍然要使用begin與end作為起始和結束的標識，而MySQL則直接用END something（如while，if，loop），當然，由於自己接觸MySQL並不長，沒有使用SQL Server那麼熟練，所以寫這些也是非常基礎的部分，而在實際問題中會千變萬化，所以還是需要更多的實踐來不斷提高。