Thought of Genius

SQL 2005中有個CTE (Common Table Expression)功能十分強大，可以用來做遞迴式的參考，藉以達成複雜的查詢邏輯並簡化查詢指令，是 SQL2005的一個新的重要功能。在使用 MySQL 時，由於沒有查詢端的存取模型好用，所以分頁通常都由程式自己來，在查詢指令下 limit m，n後，即可將要指定的分頁資料傳回，不會浪費 Network IO 在傳遞資料上。在 SQL Server 2000之前要達成這樣的功能，通常是配合temp table並配合 identity 欄位達成，但是仍需要將所有資料從第一筆到第 n 筆寫入 temp table 後，再取 top 的方式來操作，效能仍舊不理想。

所以我們可以使用SQL Server 2005中的CTE (Common Table Expression) 搭配 行號函數 ROW_NUMBER()來達到目的。與 ADO.NET 的分頁做比較，不僅可以降低 Network IO 外，更能在複雜查詢時提升效能，減少不必要的資源浪費，對於網頁分頁使用時有很大的幫助。

例如：查詢訂單資料表(orders)，依訂單金額由大而小排序，取出第301到第400筆資料:

<br />-- MySQL:<br />select * from orders order by orderamount desc limit 301,400<br /><br />-- SQL Server 2005:<br />with cteorder as (<br />select row_number() over (order by orderamount desc) as sn, * from orders )<br />select * from cteorder where sn between 301 and 400<br />

範例程式碼如下：
<br />-- Create Test Table<br />Create Table Demo(<br /> [DemoNo] [int] IDENTITY(1,1) NOT NULL,<br /> [RegDate] [datetime] NOT NULL CONSTRAINT [DF_Demo_RegDate] DEFAULT (getdate()),<br /> [Name] [nvarchar](50) NOT NULL,<br /> [Description] [nvarchar](50) NOT NULL,<br /> [OrderColum] [float] NOT NULL,<br /> CONSTRAINT [PK_Demo] PRIMARY KEY CLUSTERED ([DemoNo])<br />);<br /><br />-- Insert Into Data<br />Declare @i int<br />set @i = 1<br />While @i <= 30000<br />Begin<br /> insert into Demo([Name],[Description],[OrderColum]) values ('names', 'descriiption', @i * rand())<br /> set @i = @i +1<br />End<br /><br />-- Create Procedure Separate Result Set<br />Create Procedure usp_SeparateSet<br /> @sqlcmd nvarchar(max),<br /> @ordercmd nvarchar(max),<br /> @startnum int, @endnum int<br />AS<br /> Declare @ctecmd nvarchar(max)<br /> Select @ctecmd = '<br /> with ctequery as (<br /> select row_number() over ( ' + @ordercmd + ' ) as num, '+replace(@sqlcmd,'select ',' ')+' )<br /> select * from ctequery where num between ' + convert(nvarchar,@startnum) + ' and ' + convert(nvarchar,@endnum)<br /> Exec sp_executesql @ctecmd<br />GO<br /><br />-- Execute Procedure<br />Exec usp_SeparateSet 'select * from Demo', 'order by DemoNo', 1201,2000<br />-- Clean up test environment<br />Drop Table Demo<br />Drop Procedure<br /><br />-- Clean up test environment<br />Drop Table Demo<br />Drop Procedure usp_SeparateSet<br />

許多專案主導者都會要求程式設計師不要在程式中組裝 SQL 語句，而改用 Stored Procedure，並使用 Parameter 來傳遞參數。但是這樣就真的能夠對 SQL Injection 完全免疫了嗎? 其實不然，SQL Injection 是由組裝式 SQL 語句而引發的，得注意有任何有【組裝式 SQL】發生的程式碼，當然也包含了 Stored Procedure。
假設今天我們有一個需求，是需要組合多個條件的查詢，我們應該在預存程序裡面再使用一次傳遞參數方式，來避免有任何的組裝式 SQL 的程式碼。

<br />Use Northwind<br />GO<br /><br />-- If store procedure exists, drop store procedure<br />IF (EXISTS (SELECT * FROM sys.procedures WHERE name = N'usp_ComplexQuery')) DROP PROCEDURE usp_ComplexQuery<br />GO<br /><br />-- Create store procedure<br />CREATE PROCEDURE usp_ComplexQuery<br /> @CustomerID nchar(5),<br /> @CompanyName nvarchar(40),<br /> @City nvarchar (15),<br /> @Country nvarchar(15),<br /> @STR nvarchar(1024) OUTPUT<br />AS<br /> DECLARE @WK nvarchar(512)<br /> SET @STR = 'SELECT * FROM Customers'<br /> SET @WK = ''<br /> <br /> IF not (@CustomerID is null or @CustomerID ='')<br /> BEGIN<br /> SET @WK = @WK + ' CustomerID = @pCustomerID and '<br /> END<br /> <br /> IF not (@CompanyName is null or @CompanyName = '')<br /> BEGIN<br /> SET @WK = @WK + ' CompanyName like @pCompanyName and '<br /> SET @CompanyName = '%' + @CompanyName + '%'<br /> END<br /> <br /> IF not (@City is null or @City = '')<br /> BEGIN<br /> SET @WK = @WK + ' City like @pCity and '<br /> SET @City = '%' + @City + '%'<br /> END<br /> <br /> IF not (@Country is null or @Country = '')<br /> BEGIN<br /> SET @WK = @WK + ' Country like @pCountry and '<br /> SET @Country = '%' + @Country + '%'<br /> END<br /> <br /> IF LEN(@STR) > 0<br /> BEGIN<br /> IF LEN(@WK) > 0<br /> BEGIN<br /> SET @STR = @STR + ' WHERE ' + SUBSTRING(@WK,0,LEN(@WK)-3)<br /> EXEC sp_executesql @STR,<br /> N'@pCustomerID nchar(5),@pCompanyName nvarchar(40),@pCity nvarchar (15),@pCountry nvarchar(15)',<br /> @pCustomerID=@CustomerID,@pCompanyName=@CompanyName,@pCity=@City,@pCountry=@Country<br /> END<br /> ELSE<br /> BEGIN<br /> EXEC sp_executesql @STR<br /> END<br /> END<br /> RETURN @@rowcount<br />GO<br /><br />-- Execute procedure<br />DECLARE @return_value int, @STR nvarchar(1024)<br />EXEC @return_value = [dbo].[usp_ComplexQuery]<br /> @CustomerID = NULL,<br /> @CompanyName = NULL,<br /> @City = NULL,<br /> @Country = NULL,<br /> @STR = @STR OUTPUT<br />SELECT 'Return Value' = @return_value, @STR as N'@STR'<br />GO<br />

SQL Server 2000 的 SQL Mail 提供從 Microsoft SQL Server 傳送和讀取電子郵件的簡易方式，但是 SQL Mail 屬於 MAPI 應用程式，所以伺服器上必須出現 MAPI 子系統。如果要使用 SQL Mail，就必須安裝 MAPI 用戶端，例如 ：Microsoft Outlook 或是 Exchange Server。本文件所提及之技術，伺服器可以不必安裝MAPI 子系統，即可發送電子郵件。

我們可以利用 SQL Server OLE Automation 系統預存程序，來呼叫 CDOSYS 物件模型，並搭配任何支援 SMTP 郵件的郵件伺服器，作為自己的遠端 SMTP 郵件伺服器，來達到寄送電子郵件的功能。

<br />-- If this store procedure exists, drop this store procedure<br />IF (EXISTS (SELECT * FROM sys.procedures WHERE name = N'usp_cdosys_sendmail')) DROP PROCEDURE usp_cdosys_sendmail<br />GO<br />-- Create store procedure<br />CREATE PROCEDURE usp_cdosys_sendmail<br /> @From varchar(100) ,<br /> @To varchar(100) ,<br /> @Subject varchar(100)=" ",<br /> @Body varchar(4000) =" "<br />AS<br /> DECLARE @iMsg int<br /> DECLARE @SMTPServer varchar(100)<br /> DECLARE @SendUserName varchar(100)<br /> DECLARE @SendPassword varchar(100)<br /> DECLARE @hr int<br /> DECLARE @source varchar(255)<br /> DECLARE @description varchar(500)<br /> DECLARE @output varchar(1000)<br /> -- This is a sample. Please input your smtp server name or IP address<br /> SET @SMTPServer='mail.com.tw'<br /> -- If smtp server uses authentication, please input your email address and email password. If does not, pass this option.<br /> SET @SendUserName='admin@mail.com.tw '<br /> SET @SendPassword='Password'<br /><br /> -- Create the CDO.Message Object<br /> EXEC sp_OACreate 'CDO.Message', @iMsg OUT<br /> -- Configure a remote SMTP server.<br /> EXEC sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").Value','2'<br /> -- Configure the Server Name or IP address.<br /> EXEC sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value',@SMTPServer<br /> -- SMTP Authentication<br /> IF NOT (@SendUserName='' and @SendPassword='')<br /> BEGIN<br /> EXEC sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate").Value','1'<br /> EXEC sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusername").Value',@SendUserName<br /> EXEC sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendpassword").Value',@SendPassword<br /> END<br /> -- Save the configurations to the message object<br /> EXEC sp_OAMethod @iMsg, 'Configuration.Fields.Update', null<br /> -- Set the e-mail parameters<br /> EXEC sp_OASetProperty @iMsg, 'From', @From<br /> EXEC sp_OASetProperty @iMsg, 'To', @To<br /> EXEC sp_OASetProperty @iMsg, 'Subject', @Subject<br /> EXEC sp_OASetProperty @iMsg, 'TextBody', @Body<br /> -- Send mail and do error handling<br /> EXEC @hr = sp_OAMethod @iMsg, 'Send', NULL<br /> IF @hr <> 0<br /> BEGIN<br /> EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT<br /> IF @hr = 0<br /> BEGIN<br /> SELECT @output = ' Description: ' + @description<br /> PRINT @output<br /> END<br /> END<br /> -- Clean up the objects created.<br /> EXEC sp_OADestroy @iMsg<br />GO<br /><br />-- Execute procedure<br />DECLARE @Body varchar(4000)<br />SELECT @Body = 'Your application is rejected. Please contact the system manager.'<br />EXEC usp_cdosys_sendmail 'admin@mail.com.tw','ada@mail.com.tw','System Message',@Body<br /><br />

假設現在我們有4個需求分別是：
1. 新增訂單之後，將庫存量減去訂單的訂購產品數量。
2. 修改產品單價的時後，不得超過原始價值的10%。
3. 如果訂單被刪除了，系統可以寄Mail通知管理人員。
4. 新增訂單時，訂單流水號能夠依照日期所產生，例如:200803270123。
我們要如何建立觸發程序，並達到我們的需求 ?

觸發程序簡易說明：
Trigger Definition：
1. 觸發程序是依附資料表或檢視存在，本身是整個交易的延伸。
2. 從應用程式進行資料處理異動作業開始到觸發程序執行完畢，都屬於同一個交易的範圍。
DML Trigger：
(1) After Trigger：在執行Insert、Update、Delete陳述式的動作之後執行。
(2) Instead of Trigger：取代原先的Insert、Update、Delete的動作。

我們先建立兩個測試用的資料表，並依照需求順序建立對應的觸發程序。
<br />-- Create table and input data<br />CREATE TABLE Product(<br /> [ProductNo] int NOT NULL,<br /> [ProductName] nvarchar(10)NOT NULL,<br /> [Price] money,<br /> [Stock] int,<br /> CONSTRAINT [PK_Product] PRIMARY KEY CLUSTERED (ProductNo)<br />);<br />CREATE TABLE OrderDetail(<br /> [OrderNo] nvarchar(12) NOT NULL,<br /> [ProductNo] int NOT NULL,<br /> [Quantity] int NOT NULL,<br /> [OrderDate] datetime,<br /> CONSTRAINT [PK_OrderDetail] PRIMARY KEY CLUSTERED (OrderNo),<br /> CONSTRAINT [FK_Product_OrderDetail] FOREIGN KEY (ProductNo) REFERENCES Product (ProductNo)<br />);<br />INSERT INTO Product VALUES (1,'A', 100, 30);<br />INSERT INTO Product VALUES (2,'B', 150, 20);<br />INSERT INTO Product VALUES (3,'C', 200, 10);<br />INSERT INTO OrderDetail VALUES (200801170001, 1, 3, '2008-01-17 12:54:46.587');<br />INSERT INTO OrderDetail VALUES (200801170002, 2, 5, '2008-01-17 14:32:19.153');<br />INSERT INTO OrderDetail VALUES (200802020001, 3, 7, '2008-02-02 13:24:35.273');<br />GO<br /><br /><br /><br />-- AFTER INSERT<br />CREATE TRIGGER Tri_AfterInsert ON OrderDetail AFTER INSERT<br />AS<br /> DECLARE @Pid int<br /> DECLARE @Qty int<br /> SELECT @Pid=ProductNo, @Qty=Quantity FROM INSERTED;<br /> UPDATE Product SET Stock=Stock-@Qty WHERE ProductNo = @Pid;<br />GO<br /> <br />-- AFTER UPDATE<br />CREATE TRIGGER Tri_AfterUpdate ON Product AFTER UPDATE<br />AS<br /> DECLARE @PriceBefore money<br /> DECLARE @PriceAfter money<br /> DECLARE @Difference money<br /> IF UPDATE(Price)<br /> BEGIN<br /> SET @PriceBefore = (SELECT Price FROM DELETED)<br /> SET @PriceAfter = (SELECT Price FROM INSERTED)<br /> SET @Difference = (@PriceBefore-@PriceAfter)/@PriceBefore<br /> IF (@Difference > 0.1) OR (@Difference < -0.1)<br /> BEGIN<br /> ROLLBACK<br /> END<br /> END<br />GO<br /><br />-- AFTER DELETE<br />CREATE TRIGGER Tri_AfterDelete ON OrderDetail AFTER DELETE<br />AS<br /> IF (SELECT COUNT(*) FROM DELETED) > 0<br /> BEGIN<br /> DECLARE @MSGsubject nvarchar(max)<br /> DECLARE @MSGbody nvarchar(max)<br /> SET @MSGsubject = (SELECT N'Order form deletion notice. Deleted by ' + SUSER_SNAME())<br /> SET @MSGbody = (SELECT * FROM DELETED FOR XML AUTO, ELEMENTS, ROOT('Data'))<br /> EXEC MSDB.dbo.sp_send_dbmail @profile_name='DBA Mail Notice', @recipients = 'eric@mail.dsic.com.tw',<br /> @subject = @MSGsubject, @body = @MSGbody, @body_format=TEXT<br /> END<br />GO<br /><br />-- INSTEAD OF INSERT<br />CREATE TRIGGER Tri_InsteadOfInsert ON OrderDetail INSTEAD OF INSERT<br />AS<br /> DECLARE @oid bigint<br /> DECLARE @today smalldatetime<br /> DECLARE @count int<br /> SELECT @today = CONVERT(datetime,CONVERT(char(10),GETDATE(),111))<br /> SELECT @count = COUNT(*)+1 FROM OrderDetail WHERE OrderDate >= @today AND OrderDate < DateAdd(DAY, 1, @today)<br /> SELECT @oid = CONVERT(bigint, CONVERT(nvarchar(10), @today,112))*10000 + @count <br /> INSERT INTO OrderDetail<br /> SELECT @oid, ProductNo, Quantity, OrderDate FROM INSERTED<br />GO<br /><br /><br /><br />-- Test trigger<br />SELECT * FROM Product<br />SELECT * FROM OrderDetail<br />INSERT INTO OrderDetail (ProductNo, Quantity, OrderDate) VALUES (1, 5,GETDATE())<br />UPDATE Product SET Price = Price*30 WHERE ProductNo = 1<br />Delete OrderDetail<br /><br /><br /><br />--Cleaning up environment<br />DROP TABLE OrderDetail<br />DROP TABLE Product<br />