发表于 2009-1-5 19:48
| 
ASP寮鍙戜腑瀛樺偍杩囩▼搴旂敤鍏ㄦ帴瑙
鍙戝竷: 2008-6-27 11:51 | 浣滆: admin | 鏌ョ湅: 4娆
1 2 3
銆銆ASP涓庡瓨鍌ㄨ繃绋(Stored Procedures)鐨勬枃绔犱笉灏戯紝浣嗘槸鎴戞鐤戜綔鑰呬滑鏄惁鐪熸瀹炶返杩囥傛垜鍦ㄥ垵瀛︽椂鏌ラ槄杩囧ぇ閲忕浉鍏宠祫鏂欙紝鍙戠幇鍏朵腑鎻愪緵鐨勫緢澶氭柟娉曞疄闄呮搷浣滆捣鏉ュ苟涓嶆槸閭ｄ箞鍥炰簨銆傚浜庣畝鍗曠殑搴旂敤锛岃繖浜涜祫鏂欎篃璁告槸鏈夊府鍔╃殑锛屼絾浠呴檺浜庢锛屽洜涓哄畠浠牴鏈氨鏄崈绡囦竴寰嬶紝浜掔浉鎶勮锛岀◢寰鏉傜偣鐨勫簲鐢紝灏卞叏閮借鐒変笉璇︿簡銆
銆銆鐜板湪锛屾垜鍩烘湰涓婇氳繃璋冪敤瀛樺偍杩囩▼璁块棶SQL Server锛屼互涓嬬殑鏂囧瓧閮芥槸瀹炶返鐨勬荤粨锛屽笇鏈涘澶у鑳芥湁甯姪銆
銆銆瀛樺偍杩囩▼灏辨槸浣滀负鍙墽琛屽璞″瓨鏀惧湪鏁版嵁搴撲腑鐨勪竴涓垨澶氫釜SQL鍛戒护銆
銆銆瀹氫箟鎬绘槸寰堟娊璞°傚瓨鍌ㄨ繃绋嬪叾瀹炲氨鏄兘瀹屾垚涓瀹氭搷浣滅殑涓缁凷QL璇彞锛屽彧涓嶈繃杩欑粍璇彞鏄斁鍦ㄦ暟鎹簱涓殑(杩欓噷鎴戜滑鍙皥SQL Server)銆傚鏋滄垜浠氳繃鍒涘缓瀛樺偍杩囩▼浠ュ強鍦ˋSP涓皟鐢ㄥ瓨鍌ㄨ繃绋嬶紝灏卞彲浠ラ伩鍏嶅皢SQL璇彞鍚孉SP浠ｇ爜娣锋潅鍦ㄤ竴璧枫傝繖鏍峰仛鐨勫ソ澶勮嚦灏戞湁涓変釜锛
銆銆绗竴銆佸ぇ澶ф彁楂樻晥鐜囥傚瓨鍌ㄨ繃绋嬫湰韬殑鎵ц閫熷害闈炲父蹇紝鑰屼笖锛岃皟鐢ㄥ瓨鍌ㄨ繃绋嬪彲浠ュぇ澶у噺灏戝悓鏁版嵁搴撶殑浜や簰娆℃暟銆
銆銆绗簩銆佹彁楂樺畨鍏ㄦс傚亣濡傚皢SQL璇彞娣峰悎鍦ˋSP浠ｇ爜涓紝涓鏃︿唬鐮佸け瀵嗭紝鍚屾椂涔熷氨鎰忓懗鐫搴撶粨鏋勫け瀵嗐
銆銆绗笁銆佹湁鍒╀簬SQL璇彞鐨勯噸鐢ㄣ
銆銆鍦ˋSP涓紝涓鑸氳繃command瀵硅薄璋冪敤瀛樺偍杩囩▼锛屾牴鎹笉鍚屾儏鍐碉紝鏈枃涔熶粙缁嶅叾瀹冭皟鐢ㄦ柟娉曘備负浜嗘柟渚胯鏄庯紝鏍规嵁瀛樺偍杩囩▼鐨勮緭鍏ヨ緭鍑猴紝浣滀互涓嬬畝鍗曞垎绫伙細
銆銆1. 鍙繑鍥炲崟涓璁板綍闆嗙殑瀛樺偍杩囩▼
銆銆鍋囪鏈変互涓嬪瓨鍌ㄨ繃绋(鏈枃鐨勭洰鐨勪笉鍦ㄤ簬璁茶堪T-SQL璇硶锛屾墍浠ュ瓨鍌ㄨ繃绋嬪彧缁欏嚭浠ｇ爜锛屼笉浣滆鏄)锛
/*SP1*/
CREATE PROCEDURE dbo.getUserList
as
set nocount on
begin
select * from dbo.[userinfo]
end
go
銆銆浠ヤ笂瀛樺偍杩囩▼鍙栧緱userinfo琛ㄤ腑鐨勬墍鏈夎褰曪紝杩斿洖涓涓褰曢泦銆傞氳繃command瀵硅薄璋冪敤璇ュ瓨鍌ㄨ繃绋嬬殑ASP浠ｇ爜濡備笅:
'**閫氳繃Command瀵硅薄璋冪敤瀛樺偍杩囩▼**
DIM MyComm,MyRst
Set MyComm = Server.CreateObject("ADODB.Command")
MyComm.ActiveConnection = MyConStr 'MyConStr鏄暟鎹簱杩炴帴瀛椾覆
MyComm.CommandText = "getUserList" '鎸囧畾瀛樺偍杩囩▼鍚
MyComm.CommandType = 4 '琛ㄦ槑杩欐槸涓涓瓨鍌ㄨ繃绋
MyComm.Prepared = true '瑕佹眰灏哠QL鍛戒护鍏堣缂栬瘧
Set MyRst = MyComm.Execute
Set MyComm = Nothing
銆銆瀛樺偍杩囩▼鍙栧緱鐨勮褰曢泦璧嬬粰MyRst锛屾帴涓嬫潵锛屽彲浠ュMyRst杩涜鎿嶄綔銆
銆銆鍦ㄤ互涓婁唬鐮佷腑锛孋ommandType灞炴ц〃鏄庤姹傜殑绫诲瀷锛屽彇鍊煎強璇存槑濡備笅锛
銆銆-1 琛ㄦ槑CommandText鍙傛暟鐨勭被鍨嬫棤娉曠‘瀹
銆銆1 琛ㄦ槑CommandText鏄竴鑸殑鍛戒护绫诲瀷
銆銆2 琛ㄦ槑CommandText鍙傛暟鏄竴涓瓨鍦ㄧ殑琛ㄥ悕绉
銆銆4 琛ㄦ槑CommandText鍙傛暟鏄竴涓瓨鍌ㄨ繃绋嬬殑鍚嶇О
銆銆杩樺彲浠ラ氳繃Connection瀵硅薄鎴朢ecordset瀵硅薄璋冪敤瀛樺偍杩囩▼锛屾柟娉曞垎鍒涓嬶細
'**閫氳繃Connection瀵硅薄璋冪敤瀛樺偍杩囩▼**
DIM MyConn,MyRst
Set MyConn = Server.CreateObject("ADODB.Connection")
MyConn.open MyConStr 'MyConStr鏄暟鎹簱杩炴帴瀛椾覆
Set MyRst = MyConn.Execute("getUserList",0,4) '鏈鍚庝竴涓弬鏂惈涔夊悓CommandType
Set MyConn = Nothing
'**閫氳繃Recordset瀵硅薄璋冪敤瀛樺偍杩囩▼**
DIM MyRst
Set MyRst = Server.CreateObject("ADODB.Recordset")
MyRst.open "getUserList",MyConStr,0,1,4
'MyConStr鏄暟鎹簱杩炴帴瀛椾覆,鏈鍚庝竴涓弬鏂惈涔変笌CommandType鐩稿悓
銆銆2. 娌℃湁杈撳叆杈撳嚭鐨勫瓨鍌ㄨ繃绋
銆銆璇风湅浠ヤ笅瀛樺偍杩囩▼锛
/*SP2*/
CREATE PROCEDURE dbo.delUserAll
as
set nocount on
begin
delete from dbo.[userinfo]
end
go
銆銆璇ュ瓨鍌ㄨ繃绋嬪垹鍘籾serinfo琛ㄤ腑鐨勬墍鏈夎褰曪紝娌℃湁浠讳綍杈撳叆鍙婅緭鍑猴紝璋冪敤鏂规硶涓庝笂闈㈣杩囩殑鍩烘湰鐩稿悓锛屽彧鏄笉鐢ㄥ彇寰楄褰曢泦锛
'**閫氳繃Command瀵硅薄璋冪敤瀛樺偍杩囩▼**
DIM MyComm
Set MyComm = Server.CreateObject("ADODB.Command")
MyComm.ActiveConnection = MyConStr 'MyConStr鏄暟鎹簱杩炴帴瀛椾覆
MyComm.CommandText = "delUserAll" '鎸囧畾瀛樺偍杩囩▼鍚
MyComm.CommandType = 4 '琛ㄦ槑杩欐槸涓涓瓨鍌ㄨ繃绋
MyComm.Prepared = true '瑕佹眰灏哠QL鍛戒护鍏堣缂栬瘧
MyComm.Execute '姝ゅ涓嶅繀鍐嶅彇寰楄褰曢泦
Set MyComm = Nothing
銆銆褰撶劧涔熷彲閫氳繃Connection瀵硅薄鎴朢ecordset瀵硅薄璋冪敤姝ょ被瀛樺偍杩囩▼锛屼笉杩囧缓绔婻ecordset瀵硅薄鏄负浜嗗彇寰楄褰曢泦锛屽湪娌℃湁杩斿洖璁板綍闆嗙殑鎯呭喌涓嬶紝杩樻槸鍒╃敤Command瀵硅薄鍚с
