Pages

SQL Default Value By Custom Function

Aug 5, 2011

使用資料庫欄位預設值好處,是新增資料時若省略某個欄位值時,則該欄位會自動採用預設值。預設值可以設定: 固定值、系統函數如:GETDATE(),其實也可以呼叫自己建立的Function,適用於表單的流水號。
本文內容與範例是以SQL Server 2008 R2所做的。

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

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

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

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

 

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

image_4 (1)

 

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

image_8

完整 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