在數(shù)據(jù)庫(kù)中，某些表的字段很長(zhǎng)，而且是自增的，但是由于有前綴，不能直接使用INT類型。
我們可以通過(guò)編寫存儲(chǔ)過(guò)程來(lái)實(shí)現(xiàn)自動(dòng)生成Id的功能：
一、首先建立數(shù)據(jù)庫(kù)和表，并向其中插入一條數(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
二、編寫自動(dòng)生成Id的存儲(chǔ)過(guò)程：
?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,????????????????????????????????????????? --?后綴長(zhǎng)度
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)????????--?如果沒(méi)有傳入MaxId，則生成第一個(gè)newId
18?????????BEGIN????
19?????????????SET?@newID?=?@prefix?+?REPLICATE('0',@suffix_len-1)+'1'
20?????????????--?REPLICATE()以指定的次數(shù)重復(fù)字符表達(dá)式。然后加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
編寫思路：
?????????通過(guò)接收三個(gè)參數(shù)來(lái)生成新的Id，傳入?yún)?shù)為：最大ID， 前綴， 后綴大小。
?????????如果輸入的ID為空，這生成第一個(gè)ID：
1?--?測(cè)試代碼：
2?declare?@num?varchar(20)
3?exec?proc_getNewID?null,'PERM',4,@num?output
4?select?@num
5?--?輸出：PERM0001
6?
?????????如果輸入不為空，這生成最大Id的后一個(gè)Id：
?????????
1?--?測(cè)試代碼：
2?declare?@num?varchar(20)
3?exec?proc_getNewID?'PERM000011',?'PERM',?6,?@num?output
4?select?@num
5?
6?--輸出：PERM000012
7?
三、在某張表上應(yīng)用該存儲(chǔ)過(guò)程：
?1?--?=============================================
?2?--?Create?procedure?insertAssetBudgetState
?3?--?創(chuàng)建?插入用戶信息?的存儲(chǔ)過(guò)程
?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
輸出結(jié)果：
四、數(shù)據(jù)類型的自動(dòng)轉(zhuǎn)換：
在編寫自動(dòng)生成Id的存儲(chǔ)過(guò)程中使用了自動(dòng)轉(zhuǎn)換：@num=@num+1，這里的@nun為varchar，最后得到int型。
Transact-SQL 參考：
?1?+（加）
?2?兩個(gè)數(shù)相加。這個(gè)加法算術(shù)運(yùn)算符也可以將一個(gè)以天為單位的數(shù)字加到日期中。
?3?
?4?語(yǔ)法
?5?expression?+?expression
?6?
?7?參數(shù)
?8?expression
?9?
10?是數(shù)字分類中任何數(shù)據(jù)類型（bit?數(shù)據(jù)類型除外）的任何有效?Microsoft??SQL?Server??表達(dá)式。?
11?
12?結(jié)果類型
13?返回優(yōu)先級(jí)較高的參數(shù)的數(shù)據(jù)類型。有關(guān)更多信息，請(qǐng)參見(jiàn)數(shù)據(jù)類型的優(yōu)先順序。?
14?
?1?C.?將字符和整型數(shù)據(jù)類型相加
?2?本示例通過(guò)將字符數(shù)據(jù)類型轉(zhuǎn)換為?int，將?int?數(shù)據(jù)類型值與字符值相加。如果在?char?字符串中有無(wú)效的字符，則?SQL?Server?將返回錯(cuò)誤。
?3?
?4?DECLARE?@addvalue?int
?5?SET?@addvalue?=?15
?6?SELECT?'125127'?+?@addvalue
?7?
?8?下面是結(jié)果集：
?9?
10?-----------------------?
11?125142??????????????????
12?
13?(1?row(s)?affected)
14?
15?
?1?數(shù)據(jù)類型的優(yōu)先順序
?2?當(dāng)兩個(gè)不同數(shù)據(jù)類型的表達(dá)式用運(yùn)算符組合后，數(shù)據(jù)類型的優(yōu)先順序規(guī)則確定哪種數(shù)據(jù)類型要向另一種轉(zhuǎn)換。優(yōu)先順序低的數(shù)據(jù)類型向優(yōu)先順序高的數(shù)據(jù)類型轉(zhuǎn)換。如果此轉(zhuǎn)換不是所支持的固有轉(zhuǎn)換，則返回錯(cuò)誤。當(dāng)兩個(gè)操作數(shù)表達(dá)式有相同的數(shù)據(jù)類型時(shí)，運(yùn)算的結(jié)果就為那種數(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 高，計(jì)算時(shí)回得到int結(jié)果。
?
posted on 2006-09-26 11:39
CoderDream 閱讀(1432)
評(píng)論(0) 編輯 收藏 所屬分類:
數(shù)據(jù)庫(kù)