在數據庫中，某些表的字段很長，而且是自增的，但是由于有前綴，不能直接使用INT類型。
我們可以通過編寫存儲過程來實現自動生成Id的功能：
一、首先建立數據庫和表，并向其中插入一條數據：
?1?Use?Master
?2?GO
?3?
?4?--?=============================================
?5?--?Basic?Create?Database?Template
?6?--?=============================================
?7?IF?EXISTS?(SELECT?*?
?8????????FROM???master..sysdatabases?
?9????????WHERE??name?=?N'test_db')
10?????DROP?DATABASE?test_db
11?GO
12?
13?CREATE?DATABASE?test_db
14?GO
15?
16?
17?Use?test_db;
18?GO
19?
20?--?=============================================
21?--?Create?table?basic?template
22?--?=============================================
23?IF?EXISTS(SELECT?name?
24???????FROM??????sysobjects?
25???????WHERE??name?=?N'T_User'?
26???????AND??????type?=?'U')
27?????DROP?TABLE?test_table
28?GO
29?
30?CREATE?TABLE?T_User?(
31?userId?VARCHAR(50)?PRIMARY?KEY,?
32?userName?VARCHAR(50))
33?GO
34?
35?INSERT?INTO?T_User?VALUES('User0001',?'xulin')
36?GO
二、編寫自動生成Id的存儲過程：
?1?IF?EXISTS?(SELECT?name?
?2??????FROM???sysobjects?
?3??????WHERE??name?=?N'proc_getNewID'?
?4??????AND???????type?=?'P')
?5????DROP?PROCEDURE?proc_getNewID
?6?GO
?7?
?8?CREATE?PROCEDURE?proc_getNewID
?9?????@maxId?????????????VARCHAR(20),???????????????????? ?--?傳人的最大Id值
10?????@prefix?????????????VARCHAR(8),????????????????????????--?前綴
11?????@suffix_len????? INT,????????????????????????????????????????? --?后綴長度
12?????@newId????????????VARCHAR(20)?output??????? ?--?輸出參數
13?AS?
14?????DECLARE?@num??varchar(20)
15?????SET?@num?=?right(@maxID,@suffix_len)????--?得到后綴
16?
17?????IF?(@num?IS?null)????????--?如果沒有傳入MaxId，則生成第一個newId
18?????????BEGIN????
19?????????????SET?@newID?=?@prefix?+?REPLICATE('0',@suffix_len-1)+'1'
20?????????????--?REPLICATE()以指定的次數重復字符表達式。然后加1
21?????????END
22?????ELSE
23?????????BEGIN?
24?????????????SET?@num?=?@num?+?1
25?????????????SET?@newID?=?@prefix?+?REPLICATE('0',?@suffix_len-len(@num))?+?@num
26?????????END?
27?????RETURN?
28?
29?go
編寫思路：
?????????通過接收三個參數來生成新的Id，傳入參數為：最大ID， 前綴， 后綴大小。
?????????如果輸入的ID為空，這生成第一個ID：
1?--?測試代碼：
2?declare?@num?varchar(20)
3?exec?proc_getNewID?null,'PERM',4,@num?output
4?select?@num
5?--?輸出：PERM0001
6?
?????????如果輸入不為空，這生成最大Id的后一個Id：
?????????
1?--?測試代碼：
2?declare?@num?varchar(20)
3?exec?proc_getNewID?'PERM000011',?'PERM',?6,?@num?output
4?select?@num
5?
6?--輸出：PERM000012
7?
三、在某張表上應用該存儲過程：
?1?--?=============================================
?2?--?Create?procedure?insertAssetBudgetState
?3?--?創建?插入用戶信息?的存儲過程
?4?--?=============================================
?5?IF?EXISTS?(SELECT?name?
?6????????FROM???sysobjects?
?7????????WHERE??name?=?N'proc_insertUser'?
?8????????AND???????type?=?'P')
?9?????DROP?PROCEDURE?proc_insertUser
10?GO
11?
12?CREATE?PROCEDURE?proc_insertUser?@userName?varchar(20)
13?AS
14???IF?exists?(select?*?from?T_User?
15?????????where?UserName=@userName)
16?????return?1
17?ELSE
18????declare?@maxID?varchar(20)
19????declare?@newId?varchar(20)
20????select?@maxId=Max(UserId)?from?T_User
21????exec?proc_getNewID?@maxId,????'User',?4,?@newId?OUTPUT
22?????????--?最大ID，?前綴，?后綴大小，?新的ID（輸出）
23????INSERT?into?T_User?values(@newId,?@userName)
24?????return?0
25?
26?GO
27?
28?EXEC?proc_insertUser?'zhangke'
29?EXEC?proc_insertUser?'chenwenbin'
30?EXEC?proc_insertUser?'wangchao'
31?EXEC?proc_insertUser?'yangyan'
32?
33?SELECT?*?FROM?T_User
輸出結果：
四、數據類型的自動轉換：
在編寫自動生成Id的存儲過程中使用了自動轉換：@num=@num+1，這里的@nun為varchar，最后得到int型。
Transact-SQL 參考：
?1?+（加）
?2?兩個數相加。這個加法算術運算符也可以將一個以天為單位的數字加到日期中。
?3?
?4?語法
?5?expression?+?expression
?6?
?7?參數
?8?expression
?9?
10?是數字分類中任何數據類型（bit?數據類型除外）的任何有效?Microsoft??SQL?Server??表達式。?
11?
12?結果類型
13?返回優先級較高的參數的數據類型。有關更多信息，請參見數據類型的優先順序。?
14?
?1?C.?將字符和整型數據類型相加
?2?本示例通過將字符數據類型轉換為?int，將?int?數據類型值與字符值相加。如果在?char?字符串中有無效的字符，則?SQL?Server?將返回錯誤。
?3?
?4?DECLARE?@addvalue?int
?5?SET?@addvalue?=?15
?6?SELECT?'125127'?+?@addvalue
?7?
?8?下面是結果集：
?9?
10?-----------------------?
11?125142??????????????????
12?
13?(1?row(s)?affected)
14?
15?
?1?數據類型的優先順序
?2?當兩個不同數據類型的表達式用運算符組合后，數據類型的優先順序規則確定哪種數據類型要向另一種轉換。優先順序低的數據類型向優先順序高的數據類型轉換。如果此轉換不是所支持的固有轉換，則返回錯誤。當兩個操作數表達式有相同的數據類型時，運算的結果就為那種數據類型。
?3?
?4?下面是?Microsoft??SQL?Server??2000?數據類型的優先順序：?
?5?
?6?sql_variant（最高）
?7?
?8?
?9?datetime
10?
11?
12?smalldatetime
13?
14?
15?float
16?
17?
18?real
19?
20?
21?decimal
22?
23?
24?money
25?
26?
27?smallmoney
28?
29?
30?bigint
31?
32?
33?int
34?
35?
36?smallint
37?
38?
39?tinyint
40?
41?
42?bit
43?
44?
45?ntext
46?
47?
48?text
49?
50?
51?image
52?
53?
54?timestamp
55?
56?
57?uniqueidentifier
58?
59?
60?nvarchar
61?
62?
63?nchar
64?
65?
66?varchar
67?
68?
69?char
70?
71?
72?varbinary
73?
74?
75?binary（最低）?
76?
在數據類型的優先順序中，int?比 varchar 高，計算時回得到int結果。
?
posted on 2006-09-26 11:39
CoderDream 閱讀(1436)
評論(0) 編輯 收藏 所屬分類:
數據庫