SQLÊý¾Ý¿â
1. ´æ´¢¹ý³Ì(¶¨Òå&±àд)
l ´´½¨´æ´¢¹ý³Ì
CREATE PROCEDURE storedproc1
AS
SELECT *
from tb_project
WHERE Ô¤¼Æ¹¤ÆÚ<= 90
ORDER BY Ô¤¼Æ¹¤ÆÚ DESC
GO
exec storedproc1
GO
l Ð޸Ĵ洢¹ý³Ì
ALTER PROCEDURE storedproc1
AS
SELECT ÏîÄ¿Ãû³Æ,Ô¤¼Æ¹¤ÆÚ
from tb_project
WHERE Ô¤¼Æ¹¤ÆÚ>=90
ORDER BY Ô¤¼Æ¹¤ÆÚ DESC
GO
exec storedproc1
GO
CREATE PROCEDURE store7
@name varchar(10),@avgpbiaodi int OUTPUT
AS
DECLARE @errorsave int
SET @errorsave=0
SELECT @avgpbiaodi=AVG(Ô¤¼Æ¹¤ÆÚ)
from tb_project AS p INNER JOIN tb_employee AS e
ON p.¿Í»§±àºÅ= e.±àºÅ
WHERE e.ÐÕÃû=@name
IF(@@error<>0)
SET @errorsave=@@error
RETUREN @errorsave
GO
DECLARE @returnvalue int,@avg int
exec @returnvalue=store7 'ËïСÀö',@avg OUTPUT
PRINT 'Ö´ÐеĽá¹û'
PRINT '·µ»ØÖµ='+CAST(@returnvalue AS char(2))
PRINT 'sun¸ºÔðÏîÄ¿µÄƽ¾ù¹¤ÆÚ£º' +CAST(@avg AS char(10))
GO
2. EXEC &GO(ʹÓô洢¹ý³Ì)
l ²é¿´´æ´¢¹ý³Ì
EXEC sp_helptext storedproc1
EXEC sp_depends storedproc1
EXEC sp_help storedproc1 ---ÔÚµ±Ç°Êý¾Ý¿âÖвéÕÒ¶ÔÏó
ʵÀý239//////////////////////////////
USE sml
GO
CREATE VIEW ÊÓͼ
AS
SELECT *
from tb_employee
WHERE ¹¤×Ê=600
EXEC sp_helptext 'ÊÓͼ' --ÏÔʾ¸Ã¶ÔÏóµÄ¶¨ÒåÐÅÏ¢,¶ÔÏó±ØÐëÔÚµ±Ç°Êý¾Ý¿âÖÐ
USE sml
EXEC sp_depends 'ÊÓͼ' --±»¼ì²éÏà¹ØÐеÄÊý¾Ý¿â¶ÔÏó,¶ÔÏó¿ÉÒÔÊDZí,ÊÓͼ,´æ´¢¹ý³Ì,»ò´¥·¢Æ÷,¶ÔÏóµÄ--Êý¾ÝÀàÐÍΪvarchar(766).ÈôÒ»¸ö¶ÔÏóÒýÓÃÁíÒ»¸ö¶ÔÏó,ÔòÈËȨÚÀǰÕßÒÀÀµºóÕß,ͨ¹ý¼ì²ésysdepends±í--È·¶¨Ïà¹ØÐÔ
GO
//////////////////////////////////
EXEC sp_rename'ÈËÔ±±í','ÈËÔ±ÐÅÏ¢±í'
EXEC sp_rename'ÈËÔ±ÐÅÏ¢±í.µç»°','ÁªÏµµç»°','COLUMN'
EXEC sp_detach_db @dbname = 'pubs'
EXEC sp_attach_single_file_db @dbname = 'pubs',
@physname = 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs.mdf'
////////////////////
USE master
EXEC sp_addextendedproc xp_hello, 'xp
Ïà¹ØÎĵµ£º
Èç¹ûÄã¾³£Óöµ½ÏÂÃæµÄÎÊÌ⣬Äã¾ÍÒª¿¼ÂÇʹÓÃSQL ServerµÄÄ£°åÀ´Ð´¹æ·¶µÄSQLÓï¾äÁË£º
SQL³õѧÕß¡£
¾³£Íü¼Ç³£ÓõÄDML»òÊÇDDL SQL Óï¾ä¡£
ÔÚ¶àÈË¿ª·¢Î¬»¤µÄSQLÖУ¬Ã¿¸öÈ˶¼ÓÐ×Ô¼ºµÄSQLϰ¹ß£¬Ã»ÓÐÒ»Ì×ͳһµÄ¹æ·¶¡£
ÔÚSQL Server Management StudioÖУ¬ÒѾ¸ø´ó¼ÒÌṩÁ˺ܶೣÓõÄÏÖ³ÉSQL¹æ·¶Ä£°å¡£
SQL Server Management ......
Ò»¡¢Êý¾Ý¿âÉè¼Æ·½Ãæ
1¡¢×Ö¶ÎÀàÐÍ¡£
varchar(max)\nvarchar(max)ÀàÐ͵ÄÒýÈë´ó´óµÄÌá¸ßÁ˱à³ÌµÄЧÂÊ£¬¿ÉÒÔʹÓÃ×Ö·û´®º¯Êý¶ÔCLOBÀàÐͽøÐвÙ×÷£¬ÕâÊÇÒ»¸öÁÁµã¡£µ«ÊÇÕâ¾ÍÒý·¢Á˶ÔvarcharºÍcharЧÂÊÌÖÂÛµÄÀÏÎÊÌâ¡£µ½µ×ÈçºÎ·ÖÅävarcharµÄÊý¾Ý£¬ÊÇ·ñ»á³öÏÖ´ó¹æÄ£µÄË鯬£¿ÊÇ·ñË鯬»áÒý·¢Ð§ÂÊÎÊÌ⣿Õâ¶¼ÊÇÐèÒª½øÒ»²½Ì½ÌֵĶ«Î÷¡£
v ......
ÎÊÌâ
¡¡¡¡µ±sql serverÆô¶¯Ê±£¬ÎÒºÜÏëÔËÐÐÒ»¸ö´æ´¢¹ý³Ì¡£ÓÐûÓÐÒ»ÖÖ·½·¨¿ÉÒÔÔÚÿ´ÎSQL Server·þÎñÆô¶¯Ê±¶¼»á×Ô¶¯Ö´ÐÐÕâ¸ö´æ´¢¹ý³ÌÄØ?
¡¡¡¡×¨¼Ò½â´ð
¡¡¡¡sql ServerÌṩÁËϵͳ´æ´¢¹ý³Ìsp_procoption£¬Õâ¸ö´æ´¢¹ý³Ì¿ÉÒÔÓÃÓÚµ±SQL Server·þÎñÆô¶¯Ê±Ö¸ÅÉÒ»¸ö»òÕß¶à¸ö´æ´¢¹ý³Ì×Ô¶¯Ö´ÐС£ÕâÊÇÒ»¸öºÜ²»´íµÄÑ¡Ôñ£¬Ëü¿ÉÒÔÓÃÓÚ¶àÖÖ ......
ÕýÈ·µÄÐ޸ķ½·¨£ºsqlÅúÁ¿ÐÞ¸Ä×Ö¶ÎÄÚÈݵÄÓï¾ä
1¡¢Ã»ÊÔ¹ý£¬ÍøÉÏËÑË÷µÄ
ÒýÓÃ
update '±íÃû' set ÒªÐÞ¸Ä×Ö¶ÎÃû = replace (ÒªÐÞ¸Ä×Ö¶ÎÃû,'±»Ìæ»»µÄÌØ¶¨×Ö·û','Ìæ»»³ÉµÄ×Ö·û')
2¡¢×Ô¼º°´ÕÕÒ»¸öÒ»¸öÐÞ¸ÄÊÇ¿´Êý¾Ý±í×ܽáµÄ
ÀýÈçżÐÞ¸Ä×Ô¼º²©¿Íboblog_replies±íÖеÄadminrepidºÍadminreplier×Ö¶ÎÖµ£º
UPDATE `xxx`.`boblog_ ......
CURSOR
==================================
l SQL ÓαêCURSORµÄʹÓÃ
ʹÓÃÆðÀ´ºÜ¼òµ¥£¬Ïȶ¨Ò壬Ȼºó¸³¸öÖµ£¬´ò¿ª£¬Í¨¹ýWhile Loop Ò»¸öÒ»¸ö¶ÁÏÂÈ¥£¬×îºó¹Ø±Õ£¬ÊÍ·ÅÄÚ´æ¡£»ù±¾Ì×·ÈçÏ£º
DECLARE MyCursor cursor /* ÉùÃ÷Óα꣬ĬÈÏΪµ¥´¿ÏòǰµÄÓαꡣÈç¹ûÏëҪǰºóÌøÀ´ÌøÈ¥µÄ£¬Ð´³ÉScroll Cursor¼ ......