¶¨Ò壺

½«³£ÓõĻòºÜ¸´ÔӵŤ×÷£¬Ô¤ÏÈÓÃSQLÓï¾äдºÃ²¢ÓÃÒ»¸öÖ¸¶¨µÄÃû³Æ´æ´¢ÆðÀ´, ÄÇôÒÔºóÒª½ÐÊý¾Ý¿âÌṩÓëÒѶ¨ÒåºÃµÄ´æ´¢¹ý³ÌµÄ¹¦ÄÜÏàͬµÄ·þÎñʱ,Ö»Ðèµ÷ÓÃexecute,¼´¿É×Ô¶¯Íê³ÉÃüÁî¡£

½²µ½ÕâÀï,¿ÉÄÜÓÐÈËÒªÎÊ£ºÕâô˵´æ´¢¹ý³Ì¾ÍÊÇÒ»¶ÑSQLÓï¾ä¶øÒÑ°¡£¿

Microsoft¹«Ë¾ÎªÊ²Ã´»¹ÒªÌí¼ÓÕâ¸ö¼¼ÊõÄØ?

ÄÇô´æ´¢¹ý³ÌÓëÒ»°ãµÄSQLÓï¾äÓÐʲôÇø±ðÄØ?

´æ´¢¹ý³ÌµÄÓŵ㣺

1.´æ´¢¹ý³ÌÖ»ÔÚ´´Ôìʱ½øÐбàÒ룬ÒÔºóÿ´ÎÖ´Ðд洢¹ý³Ì¶¼²»ÐèÔÙÖØбàÒ룬¶øÒ»°ãSQLÓï¾äÿִÐÐÒ»´Î¾Í±àÒëÒ»´Î,ËùÒÔʹÓô洢¹ý³Ì¿ÉÌá¸ßÊý¾Ý¿âÖ´ÐÐËٶȡ£

2.µ±¶ÔÊý¾Ý¿â½øÐи´ÔÓ²Ù×÷ʱ(Èç¶Ô¶à¸ö±í½øÐÐUpdate,Insert,Query,Deleteʱ£©£¬¿É½«´Ë¸´ÔÓ²Ù×÷Óô洢¹ý³Ì·â×°ÆðÀ´ÓëÊý¾Ý¿âÌṩµÄÊÂÎñ´¦Àí½áºÏÒ»ÆðʹÓá£

3.´æ´¢¹ý³Ì¿ÉÒÔÖظ´Ê¹ÓÃ,¿É¼õÉÙÊý¾Ý¿â¿ª·¢ÈËÔ±µÄ¹¤×÷Á¿

4.°²È«ÐÔ¸ß,¿ÉÉ趨ֻÓÐij´ËÓû§²Å¾ßÓжÔÖ¸¶¨´æ´¢¹ý³ÌµÄʹÓÃȨ

´æ´¢¹ý³ÌµÄÖÖÀࣺ

1.ϵͳ´æ´¢¹ý³Ì£ºÒÔsp_¿ªÍ·,ÓÃÀ´½øÐÐϵͳµÄ¸÷ÏîÉ趨.È¡µÃÐÅÏ¢.Ïà¹Ø¹ÜÀí¹¤×÷,

Èç sp_help¾ÍÊÇÈ¡µÃÖ¸¶¨¶ÔÏóµÄÏà¹ØÐÅÏ¢

2.À©Õ¹´æ´¢¹ý³Ì ÒÔXP_¿ªÍ·,ÓÃÀ´µ÷ÓòÙ×÷ϵͳÌṩµÄ¹¦ÄÜ

exec master..xp_cmdshell ping 10.8.16.1

3.Óû§×Ô¶¨ÒåµÄ´æ´¢¹ý³Ì,ÕâÊÇÎÒÃÇËùÖ¸µÄ´æ´¢¹ý³Ì

³£Óøñʽ

Create procedure procedue_name

[@parameter data_type][output]

[with]{recompile|encryption}

as

sql_statement

½âÊÍ:

output£º±íʾ´Ë²ÎÊýÊÇ¿É´«»ØµÄ

with {recompile|encryption}

recompile:±íʾÿ´ÎÖ´Ðд˴洢¹ý³Ìʱ¶¼ÖØбàÒëÒ»´Î

encryption:Ëù´´½¨µÄ´æ´¢¹ý³ÌµÄÄÚÈݻᱻ¼ÓÃÜ

Èç:

±íbookµÄÄÚÈÝÈçÏÂ

±àºÅ ÊéÃû ¼Û¸ñ

001 CÓïÑÔÈëÃÅ $30

002 PowerBuilder±¨±í¿ª·¢ $52

ʵÀý1:²éѯ±íBookµÄÄÚÈݵĴ洢¹ý³Ì

create proc query_book

as

select * from book

go

exec query_book

ʵÀý2:¼ÓÈëÒ»±Ê¼Ç¼µ½±íbook,²¢²éѯ´Ë±íÖÐËùÓÐÊé¼®µÄ×ܽð¶î

Create proc insert_book

@param1 char(10),@param2 varchar(20),@param3 money,@param4 money output

with encryption ---------¼ÓÃÜ

as

insert book(±àºÅ,ÊéÃû£¬¼Û¸ñ£© Values(@param1,@param2,@param3)
select @param4=sum(¼Û¸ñ) from book
go

Ö´ÐÐÀý×Ó:
declare @total_price money
exec insert_book 003,Delphi ¿Ø¼þ¿ª·¢Ö¸ÄÏ,$100,@total_price
print ×ܽð¶îΪ+convert(varchar,@total_price)
go
´æ´¢¹ý³ÌµÄ3ÖÖ´«»ØÖµ:
1.ÒÔReturn´«»ØÕûÊý
2.ÒÔoutput¸ñʽ´«»Ø²ÎÊý
3.Recordset
´«»ØÖµµÄÇø±ð:
outputºÍreturn¶¼¿ÉÔÚÅú´Î³ÌʽÖÐÓñäÁ¿½ÓÊÕ,¶ørecordsetÔò´«»Øµ½Ö´ÐÐÅú´ÎµÄ¿Í»§¶ËÖÐ

ʵÀý3£ºÉèÓÐÁ½¸ö±íΪProduct,Order,Æä±íÄÚÈÝÈçÏ£º
Product
²úÆ·±àºÅ ²úÆ·Ãû³Æ ¿Í»§¶©Êý
001 ¸Ö±Ê 30
002 ë±Ê 50
003 Ǧ±Ê 100
Order
²úÆ·±àºÅ ¿Í»§Ãû ¿Í»§¶©½ð
001 ÄÏɽÇø $30
002 ÂÞºþÇø $50
003 ±¦°²Çø $4
ÇëʵÏÖ°´±àºÅΪÁ¬½ÓÌõ¼þ,½«Á½¸ö±íÁ¬½Ó³ÉÒ»¸öÁÙʱ±í,¸Ã±íÖ»º¬±àºÅ.²úÆ·Ãû.¿Í»§Ãû.¶©½ð.×ܽð¶î,
×ܽð¶î=¶©½ð*¶©Êý,ÁÙʱ±í·ÅÔÚ´æ´¢¹ý³ÌÖÐ

´úÂëÈçÏÂ:
Create proc temp_sale
as
select a.²úÆ·±àºÅ,a.²úÆ·Ãû³Æ,b.¿Í»§Ãû,b.¿Í»§¶©½ð,a.¿Í»§¶©Êý* b.¿Í»§¶©½ð as×ܽð¶î
into #temptable from Product a inner join Order b on a.²úÆ·±àºÅ=b.²úÆ·±àºÅ
if @@error=0
print Good
else
print Fail
go

--------------------------------------------------------------------------------
Ïà¹ØÎÄÕÂ
ÈçºÎÄÜͨ¹ý´°Ìå·ÃÎÊ±í£¬µ«²»ÄÜÖ±½Ó¶ÁÈ¡±í£¿ 2004-8-27 21:58:17
Êý¾Ý¿âÉè¼ÆÖеÄÃô½Ý·½·¨ 2004-2-9 11:19:16
µ÷ÓÃoutlookÉú³É²¢·¢ËÍÓʼþµÄ´úÂë 2004-1-30 10:32:07
SQL SERVER»ù±¾¸ÅÄîѧϰӡÏó 2004-1-1 21:23:13
Ò»×éSQL ServerÉí·ÝÑéÖ¤¹ÜÀí×Ó³ÌÐò 2003-12-31 16:29:54
Access Êý¾Ý¿âºÍ Access ÏîÄ¿Ö®¼äµÄÊý¾ÝÀàÐ͵ıȽϻòÓ³Éä 2003-12-26 14:20:19
´óÐÍÊý¾Ý¿âÉè¼ÆÔ­Ôò 2003-12-15 12:49:18
ERwin--¼ò»¯µÄÊý¾Ý¿âÉè¼Æ¹¤¾ß 2003-12-15 12:41:40
ERwinÈí¼þ¼ò½é 2003-12-15 12:39:55
¾­µä×Ó²éѯӦÓà 2003-11-17 8:54:51
Á˽â Microsoft Access °²È«ÐÔ 2003-11-13 11:21:08
²»µ±±àдSQLÓï¾äµ¼ÖÂϵͳ²»°²È« 2003-11-13 10:55:26
±ÜÃâAccessºÍSQL ServerµÄ¿ÕÖµ³åÍ» 2003-10-17 20:03:19
±í´ïʽÉú³ÉÆ÷Öеļ¸ÖÖ³£Ó÷ûºÅ 2003-10-17 19:55:56
´´½¨ÍêÃÀ±¨±í 2003-10-17 19:37:17
£Ó£Ñ£Ì Óï·¨²Î¿¼ÊÖ²á 2003-10-17 19:12:40
SQL SERVER ÓëACCESS¡¢EXCELµÄÊý¾Ýת»» 2003-10-17 19:12:00
MS Jet SQL for Access 2000Öм¶Æª(IV) 2003-10-17 18:52:20
MS Jet SQL for Access 2000Öм¶Æª (III) 2003-10-17 18:51:47
MS Jet SQL for Access 2000Öм¶Æª (II) 2003-10-17 18:51:01
MS Jet SQL for Access 2000Öм¶Æª (I) 2003-10-17 18:47:38
ʹÓÃADOÁ¬½Óµ½·À»ðǽºóµÄSQL Server 2003-10-17 18:43:57
ADOÁ¬½ÓÊý¾Ý¿â×Ö·û´®´óÈ« 2003-10-17 18:40:47
ADOÈý´ó¶ÔÏóµÄÊôÐÔ¡¢·½·¨¡¢Ê¼þ¼°³£Êý£¨¶þ£© 2003-10-14 8:45:33
ADOÈý´ó¶ÔÏóµÄÊôÐÔ¡¢·½·¨¡¢Ê¼þ¼°³£Êý£¨Èý£© 2003-10-14 8:44:20
ADOÈý´ó¶ÔÏóµÄÊôÐÔ¡¢·½·¨¡¢Ê¼þ¼°³£Êý£¨Ò»£© 2003-10-14 8:43:26
ACCESSÊý¾Ý·ÃÎÊÒ³ÅäÖÃʵÀý 2003-10-14 8:36:00
ÌáÉýÇ°ºǫ́ģʽ³ÌÐòµÄËÙ¶È 2003-10-10 10:17:08
AccessÆô¶¯ÃüÁîÐÐÑ¡Ïî´óÈ« 2003-10-10 10:02:00
Ò»¸ö¼òµ¥µÄAdoÓ÷¨Ê¾Àý 2003-10-6 16:24:38
Access 2002 ÖеÄÃû³Æ×Ô¶¯¸üÕý˵Ã÷ 2003-10-6 15:16:24
Êý¾Ý¿âÓ¦ÓóÌÐòÐè×¢ÒâµÄÎÊÌâ 2003-10-6 11:11:06