SQL
1.觸發器TRIGGER
兩個臨時表：Deleted Inserted
虚拟表Inserted 虚拟表Deleted
在表记录新增时 存放新增的记录 不存储记录
修改时 存放用来更新的新记录 存放更新前的记录
删除时 不存储记录 存放被删除的记录
修改記錄
View Code
USE [TECOSFIS] GO /****** Object: Trigger [dbo].[TestTecoSfis] Script Date: 06/13/2012 14:55:42 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: <Author,,Name> -- Create date: <Create Date,,> -- Description: <Description,,> -- ============================================= ALTER TRIGGER [dbo].[TestTecoSfis] ON [dbo].[BOM] AFTER Update AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; if Update(BOMCode) begin Update dbo.Table_1 set barcode=i.BOMCode from Table_1 br,Deleted d ,Inserted i Where br.barcode=d.BOMCode end -- Insert statements for trigger here END
新增記錄
View Code
ALTER TRIGGER [dbo].[TestTecoSfis] ON [dbo].[BOM] AFTER insert AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; insert into dbo.Table_1( barcode,bedate) select BOMCode,getdate() from Inserted i -- Insert statements for trigger here END
刪除記錄
View Code
ALTER TRIGGER [dbo].[TestTecoSfis] ON [dbo].[BOM] AFTER delete AS BEGIN SET NOCOUNT ON; delete dbo.Table_1 from Table_1 m,deleted i where m.barcode=i.BOMCode END
2.存儲過程 PROCEDURE
2.1 定義參數 定義變量
@pDocID VARCHAR(4)，前面是參數名，後面是變量類型。
下面是存儲過程中用到的變量的定義
DECLARE @SysDate DATETIME
DECLARE @Seq DECIMAL(4, 0)
2.2 定義賦值
SET @oMsgNo = ''用set語句
2.3 在存儲過程中執行sql
EXEC( @SqlCountStmt )
2.4 在存儲過程中推薦使用公用表表达式（CTE），比臨時表快的多。
EXEC( '
WITH TempTCE AS (' + @SqlStmt + ')
SELECT *
FROM TempTCE
WHERE RowNumber BETWEEN ' + @strStartRowNumber + ' AND '+ @strEndRowNumber + '
'
分頁存儲過程
View Code
USE [Myufull] GO /****** Object: StoredProcedure [dbo].[sp_Paging_RowNumber] Script Date: 06/14/2012 14:28:39 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[sp_Paging_RowNumber] @SqlStmt nvarchar(max), @PageIndex int = 0, @PageSize int = 10, @SqlCountStmt nvarchar(max) AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; /*Default Page Number*/ IF @PageIndex < 0 AND @PageSize < 0 BEGIN /*Execute dynamic query*/ EXEC( ' WITH TempTCE AS (' + @SqlStmt + ') SELECT * FROM TempTCE ' ) END ELSE BEGIN IF @PageIndex < 0 SET @PageIndex = 0 DECLARE @strStartRowNumber varchar(50) DECLARE @strEndRowNumber varchar(50) SET @strStartRowNumber = CAST( ((@PageIndex)*@PageSize+1) AS varchar(50)) SET @strEndRowNumber = CAST( (@PageIndex + 1)* @PageSize AS varchar(50)) /*Execute dynamic query*/ EXEC( ' WITH TempTCE AS (' + @SqlStmt + ') SELECT * FROM TempTCE WHERE RowNumber BETWEEN ' + @strStartRowNumber + ' AND '+ @strEndRowNumber + ' ' ) END EXEC( @SqlCountStmt ) END
庫存處理存儲過程：
View Code
USE [Myufull] GO /****** Object: StoredProcedure [dbo].[StockChange] Script Date: 06/14/2012 14:39:49 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[StockChange] -- Add the parameters for the stored procedure here @pDOC_NO VARCHAR (20), -- 單據編號 @pDOC_ID VARCHAR (4), -- 單據代號 @pBOOK_YM VARCHAR (6), -- 記帳年月 @pDOC_DATE DATETIME, -- 單據日期 @pDEPT_ID VARCHAR (10), -- 單位代號 @pDOC_SEQ DECIMAL (4,0), -- 單據項次 @pTYPE VARCHAR (2), -- 異動類型(系代MM030:I入、O出、IC入庫作廢、OC出庫作廢) @pWH_ID VARCHAR (10), -- 倉庫代號 @pITEM_CODE VARCHAR (20), -- 商品代號 @pQTY DECIMAL (8,2), -- 數量 @pSTOCK_AMT DECIMAL (12,2), -- 存貨金額 @pSALE_AMT DECIMAL (12,2), -- 銷售金額(含稅) @pMEMO NVARCHAR(255), -- 備註 @pBATCH_NUMBER VARCHAR (20), -- 批號 @pUSER_ID VARCHAR (10), -- 執行者帳號 @pUSER_NAME NVARCHAR(20), -- 執行者姓名 @oMSG_NO VARCHAR (6) OUTPUT -- 錯誤訊息代號 AS DECLARE @I_QTY DECIMAL(8, 2) DECLARE @I_STOCK_AMT DECIMAL(12, 2) DECLARE @O_QTY DECIMAL(8, 2) DECLARE @O_STOCK_AMT DECIMAL(12, 2) DECLARE @IC_QTY DECIMAL(8, 2) DECLARE @IC_STOCK_AMT DECIMAL(12, 2) DECLARE @OC_QTY DECIMAL(8, 2) DECLARE @OC_STOCK_AMT DECIMAL(12, 2) DECLARE @STOCK_QTY DECIMAL(8, 2) DECLARE @STOCK_QTY_T DECIMAL(8, 2) DECLARE @STOCK_AMT_NOW DECIMAL(12, 2) DECLARE @STOCK_AMT_NOW_T DECIMAL(12, 2) DECLARE @STOCK_AMT_DIFF DECIMAL(12, 2) SET @I_QTY = 0 SET @I_STOCK_AMT = 0 SET @O_QTY = 0 SET @O_STOCK_AMT = 0 SET @IC_QTY = 0 SET @IC_STOCK_AMT = 0 SET @OC_QTY = 0 SET @OC_STOCK_AMT = 0 SET @STOCK_QTY = 0 SET @STOCK_QTY_T = 0 SET @STOCK_AMT_NOW = 0 SET @STOCK_AMT_NOW_T = 0 SET @STOCK_AMT_DIFF = 0 BEGIN SET NOCOUNT ON; BEGIN -- INPUT CHECK -- 檢查傳入參數是否空白或不存在 IF ISNULL(@pDOC_NO, '') = '' SET @oMSG_NO = 'E20053' ELSE IF @pDOC_SEQ IS NULL SET @oMSG_NO = 'E20054' ELSE IF ISNULL(@pTYPE, '') = '' OR @pTYPE NOT IN (SELECT CODE_NO FROM SYS07M WHERE CLASS_NO = 'MM030') SET @oMSG_NO = 'E20055' ELSE IF ISNULL(@pWH_ID, '') = '' SET @oMSG_NO = 'E20056' ELSE IF ISNULL(@pDEPT_ID, '') = '' SET @oMSG_NO = 'E20072' ELSE IF ISNULL(@pITEM_CODE, '') = '' SET @oMSG_NO = 'E20057' ELSE IF @pQTY IS NULL OR @pQTY <= 0 SET @oMSG_NO = 'E20058' ELSE IF @pSTOCK_AMT IS NULL OR @pSTOCK_AMT < 0 SET @oMSG_NO = 'E20059' ELSE IF ISNULL(@pDOC_ID, '') = '' OR @pDOC_ID NOT IN (SELECT DOC_ID FROM SYS15M) SET @oMSG_NO = 'E20062' IF @oMSG_NO <> '' GOTO ERR -- 檢查傳入單位是否存在 SELECT * FROM SYS14M WHERE DEPT_ID = @pDEPT_ID IF @@ROWCOUNT = 0 BEGIN SET @oMSG_NO = 'E20073' GOTO ERR END -- 檢查傳入倉庫是否存在 SELECT * FROM MMA05M WHERE WH_ID = @pWH_ID AND DEPT_ID = @pDEPT_ID IF @@ROWCOUNT = 0 BEGIN SET @oMSG_NO = 'E20060' GOTO ERR END -- 檢查傳入商品代號是否存在 SELECT * FROM MMA01M WHERE ITEM_CODE = @pITEM_CODE IF @@ROWCOUNT = 0 BEGIN SET @oMSG_NO = 'E20061' GOTO ERR END -- 給定入出數量金額 IF @pTYPE = 'I' BEGIN SET @I_QTY = @pQTY SET @I_STOCK_AMT = @pSTOCK_AMT END ELSE IF @pTYPE = 'O' BEGIN SET @O_QTY = @pQTY SET @O_STOCK_AMT = @pSTOCK_AMT END ELSE IF @pTYPE = 'IC' BEGIN SET @IC_QTY = @pQTY SET @IC_STOCK_AMT = @pSTOCK_AMT END ELSE IF @pTYPE = 'OC' BEGIN SET @OC_QTY = @pQTY SET @OC_STOCK_AMT = @pSTOCK_AMT END ELSE BEGIN SET @oMSG_NO = 'E20055' GOTO ERR END -- 若為出庫或入出庫作廢，則檢查傳入倉庫之商品是否庫存足夠 IF @pTYPE IN ('O', 'IC', 'OC') BEGIN SELECT * FROM MMB01M WHERE ITEM_CODE = @pITEM_CODE AND WH_ID = @pWH_ID IF @@ROWCOUNT = 0 --沒有庫存紀錄卻執行出庫或入出庫作廢 BEGIN SET @oMSG_NO = 'E20070' GOTO ERR END ELSE BEGIN SELECT @STOCK_QTY = STOCK_QTY , @STOCK_AMT_NOW = STOCK_AMT FROM MMB01M WHERE ITEM_CODE = @pITEM_CODE AND WH_ID = @pWH_ID -- 計算庫存異動數量 IF @STOCK_QTY + @I_QTY - @O_QTY - @IC_QTY + @OC_QTY < 0 BEGIN SET @oMSG_NO = 'E20071' GOTO ERR END -- 若出庫存貨金額大於目前存貨金額 IF @STOCK_AMT_NOW - @O_STOCK_AMT < 0 BEGIN SET @oMSG_NO = 'E20094' GOTO ERR END END END -- 若進貨作廢存貨金額大於目前存貨金額 SELECT @STOCK_QTY_T = SUM(STOCK_QTY) , @STOCK_AMT_NOW_T = SUM(STOCK_AMT) FROM MMB01M WHERE ITEM_CODE = @pITEM_CODE IF @@ROWCOUNT = 0 IF @STOCK_AMT_NOW_T - @IC_STOCK_AMT < 0 BEGIN SET @STOCK_AMT_DIFF = @IC_STOCK_AMT - @STOCK_AMT_NOW_T SET @IC_STOCK_AMT = @STOCK_AMT_NOW_T END -- END END -- 更新庫存主檔(MMB01M) BEGIN TRY SELECT * FROM MMB01M WHERE ITEM_CODE = @pITEM_CODE AND WH_ID = @pWH_ID IF @@ROWCOUNT = 0 BEGIN INSERT INTO MMB01M (WH_ID ,ITEM_CODE ,STOCK_QTY ,STOCK_AMT ,INS_USER_ID ,INS_USER_NAME ,INS_DATE) VALUES (@pWH_ID ,@pITEM_CODE ,@I_QTY ,@pSTOCK_AMT ,@pUSER_ID ,@pUSER_NAME ,GETDATE()) END ELSE BEGIN UPDATE MMB01M SET STOCK_QTY = STOCK_QTY + @I_QTY - @O_QTY - @IC_QTY + @OC_QTY, STOCK_AMT = STOCK_AMT + @I_STOCK_AMT - @O_STOCK_AMT - @IC_STOCK_AMT + @OC_STOCK_AMT, UPD_USER_ID = @pUSER_ID, UPD_USER_NAME = @pUSER_NAME, UPD_DATE = GETDATE() WHERE ITEM_CODE = @pITEM_CODE AND WH_ID = @pWH_ID END END TRY BEGIN CATCH SET @oMSG_NO = 'E20064' GOTO ERR END CATCH BEGIN TRY INSERT INTO MMB02M (DOC_NO ,DOC_ID ,BOOK_YM ,DOC_DATE ,DEPT_ID ,DOC_SEQ ,TYPE ,WH_ID ,ITEM_CODE ,QTY ,STOCK_AMT ,STOCK_AMT_DIFF ,SALE_AMT ,MEMO ,BATCH_NUMBER ,INS_USER_ID, INS_USER_NAME, INS_DATE) VALUES (@pDOC_NO ,@pDOC_ID ,@pBOOK_YM ,@pDOC_DATE ,@pDEPT_ID ,@pDOC_SEQ ,@pTYPE ,@pWH_ID ,@pITEM_CODE ,@pQTY ,@pSTOCK_AMT ,@STOCK_AMT_DIFF ,@pSALE_AMT ,@pMEMO ,@pBATCH_NUMBER ,@pUSER_ID, @pUSER_NAME, GETDATE()) END TRY BEGIN CATCH SET @oMSG_NO = 'E20063' GOTO ERR END CATCH ERR: RETURN -1 END
2.5 生成臨時表 #accountinfo_up
select a.* into #accountinfo_up from ocsweb.dbo.accountinfo a
2.6 遊標
游标允许应用程序对查询语句select 返回的行结果集中每一行进行相同或不同的操作，而不是一次对整个结果集进行同一种操作；它还提供对基于游标位置而对表中数据进行删除或更新的能力；而且， 正是游标把作为面向集合的数据库管理系统和面向行的程序设计两者联系起来，使两个数据处理方式能够进行沟通。
使用遊標的注意事項：
使用游标时应注意的问题：
(1) 尽管使用游标比较灵活，可以实现对数据集中单行数据的直接操作，但游标会在下面几个方面影响系统的性能：
-使用游标会导致页锁与表锁的增加
-导致网络通信量的增加
-增加了服务器处理相应指令的额外开销
(2) 使用游标时的优化问题：
-明确指出游标的用途：for read only或for update
-在for update后指定被修改的列
2.6.1 定義遊標
declare mycursor cursor for select*from yuangong
2.6.2 打開遊標
open mycursor
2.6.3 提取數據
View Code
Fetch next From Cur Into @ID,@DEVICEID ,@NAME ,@WORKGROUP ,@USERDOMAIN ,@OSNAME ,@OSVERSION ,@OSCOMMENTS ,@PROCESSORT ,@PROCESSORS ,@PROCESSORN ,@MEMORY ,@SWAP ,@IPADDR ,@DNS ,@DEFAULTGATEWAY ,@ETIME ,@LASTDATE ,@LASTCOME ,@QUALITY ,@FIDELITY ,@USERID ,@TYPE ,@DESCRIPTION ,@WINCOMPANY ,@WINOWNER ,@WINPRODID ,@WINPRODKEY ,@USERAGENT ,@CHECKSUM ,@SSTATE ,@IPSRC ,@UUID
執行提取數據以後的操作
While @@fetch_status=0 //如果有數據 Begin 。。。
最好加 再次提取數據 END
2.6.4 關閉遊標
Close Cur
2.6.5 釋放遊標
Deallocate Cur
2.7 OPENQUERY的使用
对给定的链接服务器执行指定的传递查询。该服务器是 OLE DB 数据源。
2.7.1 建立连接在db中，在server object 中，添加一个连接，用ole db 可以连接到任何一个 db。这样 就可以直接对这个连接进行查询了。
比如：连接的名称是OCS，db是ocsweb。然后读取ocsweb的语句 可以这样写。
SELECT a.* FROM OPENQUERY (OCS ,'select * from modems '）
其中modems表是ocsweb的db中的表。这样可以实现在 db中操作另一个db中的数据。
2.8 关于并发 ，设置时间戳 timestamp
更新使用下面的代码：
View Code
System.Data.SqlClient.SqlConnection conn =new System.Data.SqlClient.SqlConnection(); conn.ConnectionString = "Data Source=ITTSGSW-PC;Initial Catalog=ittsgsw;User ID=sa;Password=itts"; conn.Open(); string strSql = "SELECT * FROM [USER] WHERE id=@id"; System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand(strSql,conn); command.Parameters.AddWithValue("@id", 1); System.Data.SqlClient.SqlDataAdapter ad = new System.Data.SqlClient.SqlDataAdapter(); ad.SelectCommand = command; DataTable dataTable = new DataTable(); ad.Fill(dataTable); byte[] bytes = (System.Byte[])dataTable.Rows[0]["mytime"]; strSql = "UPDATE [USER] SET name=@name WHERE id=@id and mytime=@mytime"; command = new System.Data.SqlClient.SqlCommand(strSql, conn); command.Parameters.AddWithValue("@id", 1); command.Parameters.AddWithValue("@name", 777); command.Parameters.AddWithValue("@mytime", bytes); int m= command.ExecuteNonQuery(); conn.Close();
读取的timestamp 这个字段，是byte【】数组，用byte【】数组保存内容。然后可以作为查询条件。进行更新。
select * from [user] where mytime=0x0000000000001779
update [user] set name='888' where mytime=0x0000000000001779
