上篇大致說明了MySQL的Stored Procedure與Trigger, 現在來談談Microsoft的SQL server (以SQL 2000為例, 2003/2005則大同小異)
雖然都是database的stored procedure與trigger,但是語法卻不同,另人頗為頭痛
Microsoft SQL的stored procedure主要以Transaction SQL語法 (T-SQL),除了procedure語法差異外, SQL語法本身也略有不同 …
MS SQL使用 SELECT TOP 20 * FROM MY_TABLE
MySQL使用 SELECT * FROM MY_TABLE LIMIT 0,20
以上都是挑選資料中的前20筆資料
MS SQL使用LCASE,UCASE,INSTR,MID等函數來處理字串, 但MySQL與Oracle則使用LOWER, UPPER, LOCATE, SUBSTRING … (MySQL string function reference, MySQL 5.0 reference)
Microsft走的路線以T-SQL及VB語法的方式, 因此只要T-SQL/VB熟悉, SQL server就易如反掌了 … 因此筆者都以ANSI-SQL, T-SQL, VB, PHP這些概念將MySQL, Oracle, MS SQL一次通通學習起來
(1)Stored Procedure
建立SQL Server的stored procedure首先打開SQL Server Enterprise Manager, 選取預存程序, 按下滑鼠右鑑 ->新增預存程序 (畫面如下)
我們來建立一個簡單的例子:
CREATE PROCEDURE TEST_ADD
@emp_no varchar(10)
AS
SET NOCOUNT ON
BEGIN TRAN
INSERT testtable (emp_no) VALUES (@emp_no);
COMMIT TRAN
GO
以上紅色的字是依需要修改
有了這個stored procedure後就可以執行
EXEC TEST_ADD(‘0000000001’)
SET NOCOUNT ON主要是希望不需回覆筆數
也就是”停止在部份結果集中傳回顯示Transact-SQL 陳述式或預存程序所影響之資料列數的訊息”
但是如果你希望執行本機stored procedure去操作遠端機器database
就必須改成
CREATE PROCEDURE TEST_ADD
@emp_no varchar(10)
AS
SET NOCOUNT ON
SET ANSI_NULLS ON
SET ANSI_WARNINGS ON
SET XACT_ABORT ON
BEGIN TRAN
INSERT LinkedServerName.master.dbo.testtable (emp_no) VALUES (@emp_no);
COMMIT TRAN
GO
並且不能在SQL Server Enterprise Manager下去新增stored Procedured
而要在Query Analyzer (前文提過)
以下是使用到Linked Server必須宣告的:
SET ANSI_NULLS ON
SET ANSI_WARNINGS ON
SET XACT_ABORT ON
(2)Trigger
建立SQL Server的Trigger首先打開SQL Server Enterprise Manager, 選取要建立trigger的table, 按下滑鼠右鑑 ->所有工作 -> 管理觸發程序 (畫面如下)
然後鍵入
CREATE TRIGGER trigger_name ON tableName
FOR INSERT
AS
SET NOCOUNT ON
DECLARE @newSeq_id VARCHAR(10)
SELECT @newSeq_id = (SELECT seq_id FROM Inserted)
UPDATE TableName2 set counter=counter+1 where seq_id = @newSeq_id
以上就是做: 當表格有任何資料插入, 則去另一表格對應欄位去累加
如果如同上面說的, 要運作到遠端的Linked Server, 除了要建立linked server外, 程序要改成:
CREATE TRIGGER trigger_name ON tableName
FOR INSERT
AS
SET ANSI_NULLS ON
SET ANSI_WARNINGS ON
SET NOCOUNT ON
SET XACT_ABORT ON
DECLARE @newSeq_id VARCHAR(10)
SELECT @newSeq_id = (SELECT seq_id FROM Inserted)
UPDATE LinkedServerName.master.dbo.TableName2 set counter=counter+1 where seq_id = @newSeq_id
以上只是大略敘述使用方式, 至於UPDATE/INSERT/DELETE以及更深的T-SQL就要自己再研究啦 …