åå¨è¿ç¨æ¯ä¸ç±»ç¼è¯å¥½çç¨åºï¼å¨å建æ¶è¿è¡ç¼è¯ï¼ä¹åéè¦çæ¶åå¼éè¦ä½¿ç¨ä½¿ç¨è°ç¨è¯å¥è°ç¨EXECã
å¨åè¿ç¨çåç±»ï¼
1. ç³»ç»åå¨è¿ç¨ï¼åæ¾å¨MASTERæ°æ®åºä¸ï¼ä»¥SP_åç¼ã
2. ç¨æ·èªå®ä¹åå¨è¿ç¨ã
å¨SQL SERVERä¸å建å¨åè¿ç¨è¯æ³ï¼
Create proc[edure] procedure_name[;number]
[{@parameter data_type}
[varying][=default][output]
][,â¦n]
[with {recompile|encryption|encrypton,recomption}]
[for enplication]
As
Sql_statement[,â¦n]
åæ°:
æ´ä¸ªè¯æ³ä¸åªæ两个åæ°æ¯å¿
é¡»çï¼procedure_nameåå¨è¿ç¨å称åsql_statementåå¨è¿ç¨å
容ã
procedure_nameï¼åå¨è¿ç¨å称ã
;numberï¼å¯¹åå¨è¿ç¨åç»ï¼âï¼æ°åâ表示æ¯ä¸ç»åå¨è¿ç¨ï¼ä¾¿äºæ¹éæä½ã
@parameterï¼åæ°å,å½åå¿
é¡»ç¨@å¼å¤´ï¼å±é¨åé以@å¼å¤´ï¼å
¨å±åé以@@å¼å¤´ã
data_typeï¼åæ°ç±»åã
varyingï¼æå®ä½ä¸ºè¾åºåæ°çç»æéã
Defaultï¼åæ°çé»è®¤å¼ã
Outputï¼è¡¨ç¤ºä¸ºè¾åºåæ°ï¼è¿åå¼ç»execute/exeã
[with {recompile|encryption|encrypton,recomption}][for enplication]ï¼è¿ä¸ªæè¿ä¸æç½ããã
Asï¼æå®å¨åè¿ç¨è¦æ§è¡çæä½ã
æ§è¡åå¨è¿ç¨è¯æ³ï¼
Exec[ute] åå¨è¿ç¨å åæ°
å®éªä¸ä¸ï¼ççææï¼
1.å建ä¸ç»åå¨è¿ç¨å¹¶ä¸æ§è¡
create procedure procedure_a;1
as
select * from student
go
create procedure procedure_a;2
as
select * from student
go
æ§è¡å建æææªå¾ï¼
å¾1å建ä¸ç»åå¨è¿ç¨
2.æ§è¡è¯å¥ï¼
Execute procedure_a
æææªå¾ï¼
å¾2æ§è¡åå¨è¿ç¨æææªå¾
3.å é¤è¯å¥ï¼
Drop procedure procedure_a
æææªå¾
å¾3å é¤ä¸ç»å¨åè¿ç¨æææªå¾
å建带è¾å
¥åæ°çåå¨è¿ç¨ï¼è¾å
¥åæ°æ¯ç¨åºå°åæ°å¼ä¼ éç»åå¨è¿ç¨ã
å建ä¸ä¸ªå¸¦è¾å
¥åæ°çåå¨è¿ç¨ï¼è®¾ç½®é»è®¤å¼ï¼å¹¶ä¸å¯¹è¾å
¥åæ°çå¼è¿è¡å¤æã
Create procedure pro_1
@pro_1_a varchar(20)=null
As
If @pro_1_a is null
Print'请è¾å
¥éè¦æ¥è¯¢çæå±é¢ç³»ï¼'
else
Select avg(æ绩) from student where æå±é¢ç³»=@pro_1_a
åå«æ§è¡ä¸ä¸ï¼
1.exec pro_1
æææªå¾ï¼
å¾4æ§è¡ä¸å¸¦è¾å
¥åæ°çè¯å¥
2.exec pro_1 â计ç®æºâ
æææªå¾ï¼
å¾5æ§è¡å¸¦è¾å
¥åæ°çè¯å¥
ååï¼verygood,æè¿æ¥åã
å建带è¾åºåæ°çåå¨è¿ç¨ï¼è¾åºåæ°æ¯åå¨è¿ç¨å°è¿åå¼ä¼ éç»ç¨åºã
1.è¾åºå½æ°éè¦æå®outputä½ä¸ºæ è¯
2.声æåééè¦ä½¿ç¨declare
3.ç»åéæå®å¼éè¦ä½¿ç¨select
æ¥ä¸ä¸ªä¾åï¼å¦æä¸ä¸ªæ°å¤§äº5ï¼å计ç®ä»çé¶ä¹ã
Create procedure pro_2
/*声æè¾å
¥è¾åºåæ°*/
@in_x int,
@out_y int output
as
/*声ææ éï¼å¹¶ä¸èµå¼ä¸º1*/
Declare @x int,@y int
Select @x=1,@y=1
/*ifè¯å¥å¤æè¾å
¥å¼æ¯å¦åæ³ï¼ä½¿ç¨while循ç¯è¯å¥è®¡ç®*/
If @in_x<=5
Print'请è¾å
¥å¤§äº5çæ°ï¼'
Else
While @x<=@in_x
Begin
Select @y=@y*@x
Select @x=@x+1
End
Select @out_y=@y
1.æ§è¡ä»¥ä¸å建代ç
å¾6å建带è¾å
¥è¾åºåæ°çåå¨è¿ç¨æªå¾
2.æä¾åæ°æ§è¡ä»£ç
Declare @out_sum int
Exec pro_2 6,@out_sum output
Select @out_sum as result
å¾7æä¾è¾å
¥è¾åºåæ°æ§è¡åå¨è¿ç¨æªå¾
å建å¤æ¡è¯å¥çåå¨è¿ç¨ï¼ä¸ä¸ªåå¨è¿ç¨ä¸å¯ä»¥åå
¥å¤æ¡SQLè¯å¥ï¼è¿äºè¯å¥å°è¢«ä¾æ¬¡æ§è¡ï¼å¯ä»¥å®ç°å¤ä¸ªåè½ã
å é¤åå¨è¿ç¨deleteåå¤æ对象æ¯å¦åå¨existsï¼
Delete procedure procedure_name
If exists(selectåæ¥è¯¢)ï¼å¦æexistsçåæ°æ¥è¯¢ä¸ºé空ï¼åexistsç»æ为true,å¦å为åã
åå¨è¿ç¨ä½ä¸ºä¸ä¸ªå¯¹è±¡å°è¢«ä¿åå¨æ°æ®åºçsysobjects表ä¸ï¼å¯ä»¥ä½¿ç¨è¯å¥å¤æè¿ä¸ªå¯¹è±¡æ¯å¦åå¨ï¼select * from sysobjects where name=âprocedure_nameâ
å 个系ç»åå¨è¿ç¨ç使ç¨ï¼
Sp_name:éå½å对象-- Sp_name object1,object2
Sp_depends:æ¾ç¤ºå¼ç¨å¯¹è±¡âsp_depends students
Sp_help:æ¾ç¤ºå¯¹è±¡ä¿¡æ¯
Sp_helptext:æ¾ç¤ºå¯¹è±¡çæºä»£ç
温馨提示:答案为网友推荐,仅供参考