資料表欄位預設值採用自訂 Function

本文內容與範例是以SQL Server 2008 R2所做的。

資料庫設計欄位預設值有以下方式

• 固定, 例如: 文字 ‘Y’ 或  數字 0, 1… 等
• 系統的Function, 如取系統時間-> GETDATE(), 或取一個GUID –> NEWID()
• 使用自訂的Function

以下這個範例是假設一個訂單主檔，有4個資料欄位, 其中3個欄位，分別採用不同的預設植

OrderNo	主鍵，使用自訂的Function: GetOrderNo
OrderDate	系統Function : GETDATE()
TotalAmt	固定值: 0

新增訂單主檔時上述3個欄位未設定, 則會自動填上預設值

 

有預設值的欄位當新增時有指定, 會按以指定值新增

完整 T-SQL 語法如下

/*卸除物件*/
if exists (select 1
            from  sysobjects
           where  id = object_id('OrderMaster')
            and   type = 'U')
   drop table OrderMaster
GO
DROP FUNCTION [dbo].[GetOrderNo]
GO


/*建立訂單資料表*/
create table OrderMaster(
	OrderNo           varchar(12) not null
	, OrderDate      datetime  null
	, CustomerName   nvarchar(50) null
	, TotalAmt    numeric(8,2)
	, constraint PK_OrderMaster primary key (OrderNo)
)
GO


/*------------------------------------------------ 
description:  建立自訂Function: 依日期產生訂單編號
author: Robin
date: 2011/08/02
testing code: 
-------------------------------------------------- 
PRINT dbo.GetOrderNo()
-------------------------------------------------- 
*/
CREATE function GetOrderNo( )
RETURNS varchar(12)
AS
BEGIN
	DECLARE @ReturnNo varchar(12)
	SET @ReturnNo = '0'

	/*訂單編號，西元年末4碼+月份2碼+流水號6碼*/
	DECLARE @yyyyMM  varchar(6)
	SET @yyyyMM = SUBSTRING(CONVERT(char(8),GETDATE(),112),1,6)
	SELECT @ReturnNo=MAX(OrderNo) FROM OrderMaster WHERE OrderNo LIKE @yyyyMM+'%' ;

	IF @ReturnNo IS NULL
	BEGIN
		SET @ReturnNo=@yyyyMM+'000001';
		RETURN @ReturnNo	
	END

	SET @ReturnNo=SUBSTRING(@ReturnNo, 7, 6)
	SET @ReturnNo='000000'+CAST(CAST(@ReturnNo AS NUMERIC(6,0))+1 AS VARCHAR(8));
	SET @ReturnNo=SUBSTRING(@ReturnNo, LEN(@ReturnNo)-5, 6)
	SET @ReturnNo=@yyyyMM+@ReturnNo
	RETURN @ReturnNo
END
GO

/*指定預設值*/
ALTER TABLE OrderMaster ADD  CONSTRAINT [DF_OrderNo_OrderNo]  DEFAULT ( dbo.GetOrderNo() ) FOR OrderNo
GO
ALTER TABLE OrderMaster ADD  DEFAULT (getdate()) FOR OrderDate
GO
ALTER TABLE OrderMaster ADD  DEFAULT (0) FOR TotalAmt
GO

/*新增第一筆訂單資料*/
INSERT INTO OrderMaster( CustomerName ) values ( '李小龍' )
GO

SELECT * FROM OrderMaster
GO


/*新增第二筆訂單資料*/
INSERT INTO OrderMaster(OrderNo, CustomerName ) 
values ('201107000002' , '李小龍' )
GO

SELECT * FROM OrderMaster
GO