在數(shù)據(jù)庫中，某些表的字段很長，而且是自增的，但是由于有前綴，不能直接使用INT類型。
我們可以通過編寫存儲過程來實現(xiàn)自動生成Id的功能：
一、首先建立數(shù)據(jù)庫和表，并向其中插入一條數(shù)據(jù)：
?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??????? ?--?輸出參數(shù)
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()以指定的次數(shù)重復字符表達式。然后加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
編寫思路：
?????????通過接收三個參數(shù)來生成新的Id，傳入?yún)?shù)為：最大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?--?創(chuàng)建?插入用戶信息?的存儲過程
?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
輸出結果：
四、數(shù)據(jù)類型的自動轉換：
在編寫自動生成Id的存儲過程中使用了自動轉換：@num=@num+1，這里的@nun為varchar，最后得到int型。
Transact-SQL 參考：
?1?+（加）
?2?兩個數(shù)相加。這個加法算術運算符也可以將一個以天為單位的數(shù)字加到日期中。
?3?
?4?語法
?5?expression?+?expression
?6?
?7?參數(shù)
?8?expression
?9?
10?是數(shù)字分類中任何數(shù)據(jù)類型（bit?數(shù)據(jù)類型除外）的任何有效?Microsoft??SQL?Server??表達式。?
11?
12?結果類型
13?返回優(yōu)先級較高的參數(shù)的數(shù)據(jù)類型。有關更多信息，請參見數(shù)據(jù)類型的優(yōu)先順序。?
14?
?1?C.?將字符和整型數(shù)據(jù)類型相加
?2?本示例通過將字符數(shù)據(jù)類型轉換為?int，將?int?數(shù)據(jù)類型值與字符值相加。如果在?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?數(shù)據(jù)類型的優(yōu)先順序
?2?當兩個不同數(shù)據(jù)類型的表達式用運算符組合后，數(shù)據(jù)類型的優(yōu)先順序規(guī)則確定哪種數(shù)據(jù)類型要向另一種轉換。優(yōu)先順序低的數(shù)據(jù)類型向優(yōu)先順序高的數(shù)據(jù)類型轉換。如果此轉換不是所支持的固有轉換，則返回錯誤。當兩個操作數(shù)表達式有相同的數(shù)據(jù)類型時，運算的結果就為那種數(shù)據(jù)類型。
?3?
?4?下面是?Microsoft??SQL?Server??2000?數(shù)據(jù)類型的優(yōu)先順序：?
?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?
在數(shù)據(jù)類型的優(yōu)先順序中，int?比 varchar 高，計算時回得到int結果。
?
posted on 2006-09-26 11:39
CoderDream 閱讀(1444)
評論(0) 編輯 收藏 所屬分類:
數(shù)據(jù)庫