資料表欄位預設值設計的方法，如何呼叫自訂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