Transact-SQLÖеĴ洢¹ý³Ì£¬·Ç³£ÀàËÆÓÚJavaÓïÑÔÖеķ½·¨£¬Ëü¿ÉÒÔÖØ¸´µ÷Óᣵ±´æ´¢¹ý³ÌÖ´ÐÐÒ»´Îºó£¬¿ÉÒÔ½«Óï¾ä»º´æÖУ¬ÕâÑùÏ´ÎÖ´ÐеÄʱºòÖ±½ÓʹÓûº´æÖеÄÓï¾ä¡£ÕâÑù¾Í¿ÉÒÔÌá¸ß´æ´¢¹ý³ÌµÄÐÔÄÜ¡£
¡¤´æ´¢¹ý³ÌµÄ¸ÅÄî
´æ´¢¹ý³ÌProcedureÊÇÒ»×éΪÁËÍê³ÉÌØ¶¨¹¦ÄܵÄSQLÓï¾ä¼¯ºÏ£¬¾±àÒëºó´æ´¢ÔÚÊý¾Ý¿âÖУ¬Óû§Í¨¹ýÖ¸¶¨´æ´¢¹ý³ÌµÄÃû³Æ²¢¸ø³ö²ÎÊýÀ´Ö´ÐС£
´æ´¢¹ý³ÌÖпÉÒÔ°üº¬Âß¼¿ØÖÆÓï¾äºÍÊý¾Ý²Ù×ÝÓï¾ä£¬Ëü¿ÉÒÔ½ÓÊܲÎÊý¡¢Êä³ö²ÎÊý¡¢·µ»Øµ¥¸ö»ò¶à¸ö½á¹û¼¯ÒÔ¼°·µ»ØÖµ¡£
ÓÉÓÚ´æ´¢¹ý³ÌÔÚ´´½¨Ê±¼´ÔÚÊý¾Ý¿â·þÎñÆ÷ÉϽøÐÐÁ˱àÒë²¢´æ´¢ÔÚÊý¾Ý¿âÖУ¬ËùÒÔ´æ´¢¹ý³ÌÔËÐÐÒª±Èµ¥¸öµÄSQLÓï¾ä¿éÒª¿ì¡£Í¬Ê±ÓÉÓÚÔÚµ÷ÓÃʱֻÐèÓÃÌṩ´æ´¢¹ý³ÌÃûºÍ±ØÒªµÄ²ÎÊýÐÅÏ¢£¬ËùÒÔÔÚÒ»¶¨³Ì¶ÈÉÏÒ²¿ÉÒÔ¼õÉÙÍøÂçÁ÷Á¿¡¢¼òµ¥ÍøÂ縺µ£¡£
1¡¢ ´æ´¢¹ý³ÌµÄÓŵã
A¡¢ ´æ´¢¹ý³ÌÔÊÐí±ê×¼×é¼þʽ±à³Ì
´æ´¢¹ý³Ì´´½¨ºó¿ÉÒÔÔÚ³ÌÐòÖб»¶à´Îµ÷ÓÃÖ´ÐУ¬¶ø²»±ØÖØÐ±àд¸Ã´æ´¢¹ý³ÌµÄSQLÓï¾ä¡£¶øÇÒÊý¾Ý¿âרҵÈËÔ±¿ÉÒÔËæÊ±¶Ô´æ´¢¹ý³Ì½øÐÐÐ޸쬵«¶ÔÓ¦ÓóÌÐòÔ´´úÂëÈ´ºÁÎÞÓ°Ï죬´Ó¶ø¼«´óµÄÌá¸ßÁ˳ÌÐòµÄ¿ÉÒÆÖ²ÐÔ¡£
B¡¢ ´æ´¢¹ý³ÌÄܹ»ÊµÏֽϿìµÄÖ´ÐÐËÙ¶È
Èç¹ûijһ²Ù×÷°üº¬´óÁ¿µÄT-SQLÓï¾ä´úÂ룬·Ö±ð±»¶à´ÎÖ´ÐУ¬ÄÇô´æ´¢¹ý³ÌÒª±ÈÅú´¦ÀíµÄÖ´ÐÐËÙ¶È¿ìµÃ¶à¡£ÒòΪ´æ´¢¹ý³ÌÊÇÔ¤±àÒëµÄ£¬ÔÚÊ×´ÎÔËÐÐÒ»¸ö´æ´¢¹ý³Ìʱ£¬²éѯÓÅ»¯Æ÷¶ÔÆä½øÐзÖÎö¡¢ÓÅ»¯£¬²¢¸ø³ö×îÖÕ±»´æÔÚϵͳ±íÖеĴ洢¼Æ»®¡£¶øÅú´¦ÀíµÄT-SQLÓï¾äÿ´ÎÔËÐж¼ÐèÒªÔ¤±àÒëºÍÓÅ»¯£¬ËùÒÔËٶȾÍÒªÂýһЩ¡£
C¡¢ ´æ´¢¹ý³Ì¼õÇáÍøÂçÁ÷Á¿
¶ÔÓÚͬһ¸öÕë¶ÔÊý¾Ý¿â¶ÔÏóµÄ²Ù×÷£¬Èç¹ûÕâÒ»²Ù×÷ËùÉæ¼°µ½µÄT-SQLÓï¾ä±»×éÖ¯³ÉÒ»´æ´¢¹ý³Ì£¬ÄÇôµ±ÔÚ¿Í»§»úÉϵ÷Óøô洢¹ý³Ìʱ£¬ÍøÂçÖд«µÝµÄÖ»ÊǸõ÷ÓÃÓï¾ä£¬·ñÔò½«»áÊǶàÌõSQLÓï¾ä¡£´Ó¶ø¼õÇáÁËÍøÂçÁ÷Á¿£¬½µµÍÁËÍøÂç¸ºÔØ¡£
D¡¢ ´æ´¢¹ý³Ì¿É±»×÷ΪһÖÖ°²È«»úÖÆÀ´³ä·ÖÀûÓÃ
ϵͳ¹ÜÀíÔ±¿ÉÒÔ¶ÔÖ´ÐеÄijһ¸ö´æ´¢¹ý³Ì½øÐÐȨÏÞÏÞÖÆ£¬´Ó¶øÄܹ»ÊµÏÖ¶ÔijЩÊý¾Ý·ÃÎʵÄÏÞÖÆ£¬±ÜÃâ·ÇÊÚȨÓû§¶ÔÊý¾ÝµÄ·ÃÎÊ£¬±£Ö¤Êý¾ÝµÄ°²È«¡£
¡¤ ϵͳ´æ´¢¹ý³Ì
ϵͳ´æ´¢¹ý³ÌÊÇϵͳ´´½¨µÄ´æ´¢¹ý³Ì£¬Ä¿µÄÔÚÓÚÄܹ»·½±ãµÄ´Óϵͳ±íÖвéѯÐÅÏ¢»òÍê³ÉÓë¸üÐÂÊý¾Ý¿â±íÏà¹ØµÄ¹ÜÀíÈÎÎñ»òÆäËûµÄϵͳ¹ÜÀíÈÎÎñ¡£ÏµÍ³´æ´¢¹ý³ÌÖ÷Òª´æ´¢ÔÚmasterÊý¾Ý¿âÖУ¬ÒÔ¡°sp¡±Ï»®Ïß¿ªÍ·µÄ´æ´¢¹ý³Ì¡£¾¡¹ÜÕâЩϵͳ´æ´¢¹ý³ÌÔÚmasterÊý¾Ý¿âÖУ¬µ«ÎÒÃÇÔÚÆäËûÊý¾Ý¿â»¹ÊÇ¿ÉÒÔµ÷ÓÃϵͳ´æ´¢¹ý³Ì¡£ÓÐһЩϵͳ´æ´¢¹ý³Ì»áÔÚ´´½¨ÐµÄÊý¾Ý¿âµÄʱºò±»×Ô¶¯´´½¨ÔÚµ±Ç°Êý¾Ý¿âÖС£
³£ÓÃϵͳ´æ´¢¹ý³ÌÓУº
exec sp_databases; --²é¿´Êý¾Ý¿â exec sp_tables; --²é¿´±í exec sp_columns student;--²é¿´ÁÐ exec sp_helpIndex student;--²é¿´Ë÷Òý exec sp_helpConstraint student;--Ô¼Êø exec sp_stored_procedures; exec sp_helptext 'sp_stored_procedures';--²é¿´´æ´¢¹ý³Ì´´½¨¡¢¶¨ÒåÓï¾ä exec sp_rename student, stuInfo;--ÐÞ¸Ä±í¡¢Ë÷Òý¡¢ÁеÄÃû³Æ exec sp_renamedb myTempDB, myDB;--¸ü¸ÄÊý¾Ý¿âÃû³Æ exec sp_defaultdb 'master', 'myDB';--¸ü¸ÄµÇ¼ÃûµÄĬÈÏÊý¾Ý¿â exec sp_helpdb;--Êý¾Ý¿â°ïÖú£¬²éѯÊý¾Ý¿âÐÅÏ¢ exec sp_helpdb master; |
ϵͳ´æ´¢¹ý³ÌʾÀý£º
--±íÖØÃüÃû exec sp_rename 'stu', 'stud'; select * from stud; --ÁÐÖØÃüÃû exec sp_rename 'stud.name', 'sName', 'column'; exec sp_help 'stud'; --ÖØÃüÃûË÷Òý exec sp_rename N'student.idx_cid', N'idx_cidd', N'index'; exec sp_help 'student'; --²éѯËùÓд洢¹ý³Ì select * from sys.objects where type = 'P'; select * from sys.objects where type_desc like '%pro%' and name like 'sp%'; ¡¤ Óû§×Ô¶¨Òå´æ´¢¹ý³Ì |
1¡¢ ´´½¨Óï·¨
create proc | procedure pro_name ¡¡¡¡[{@²ÎÊýÊý¾ÝÀàÐÍ} [=ĬÈÏÖµ] [output], ¡¡¡¡{@²ÎÊýÊý¾ÝÀàÐÍ} [=ĬÈÏÖµ] [output], ¡¡¡¡.... ¡¡¡¡] ¡¡¡¡as ¡¡¡¡SQL_statements |
2¡¢ ´´½¨²»´ø²ÎÊý´æ´¢¹ý³Ì
--´´½¨´æ´¢¹ý³Ì ¡¡¡¡if (exists (select * from sys.objects where name = 'proc_get_student')) ¡¡¡¡drop proc proc_get_student ¡¡¡¡go ¡¡¡¡create proc proc_get_student ¡¡¡¡as ¡¡¡¡select * from student; ¡¡¡¡--µ÷Óá¢Ö´Ðд洢¹ý³Ì ¡¡¡¡exec proc_get_student; |
3¡¢ Ð޸Ĵ洢¹ý³Ì
--Ð޸Ĵ洢¹ý³Ì ¡¡¡¡alter proc proc_get_student ¡¡¡¡as ¡¡¡¡select * from student; |
4¡¢ ´ø²Î´æ´¢¹ý³Ì
--´ø²Î´æ´¢¹ý³Ì ¡¡¡¡if (object_id('proc_find_stu', 'P') is not null) ¡¡¡¡drop proc proc_find_stu ¡¡¡¡go ¡¡¡¡create proc proc_find_stu(@startId int, @endId int) ¡¡¡¡as ¡¡¡¡select * from student where id between @startId and @endId ¡¡¡¡go ¡¡¡¡exec proc_find_stu 2, 4; |
5¡¢ ´øÍ¨Åä·û²ÎÊý´æ´¢¹ý³Ì
--´øÍ¨Åä·û²ÎÊý´æ´¢¹ý³Ì ¡¡¡¡if (object_id('proc_findStudentByName', 'P') is not null) ¡¡¡¡drop proc proc_findStudentByName ¡¡¡¡go ¡¡¡¡create proc proc_findStudentByName(@name varchar(20) = '%j%', @nextName varchar(20) = '%') ¡¡¡¡as ¡¡¡¡select * from student where name like @name and name like @nextName; ¡¡¡¡go ¡¡¡¡exec proc_findStudentByName; ¡¡¡¡exec proc_findStudentByName '%o%', 't%'; |
¡¡6¡¢ ´øÊä³ö²ÎÊý´æ´¢¹ý³Ì
if (object_id('proc_getStudentRecord', 'P') is not null) ¡¡¡¡drop proc proc_getStudentRecord ¡¡¡¡go ¡¡¡¡create proc proc_getStudentRecord( ¡¡¡¡@id int, --ĬÈÏÊäÈë²ÎÊý ¡¡¡¡@name varchar(20) out, --Êä³ö²ÎÊý ¡¡¡¡@age varchar(20) output--ÊäÈëÊä³ö²ÎÊý ¡¡¡¡) ¡¡¡¡as ¡¡¡¡select @name = name, @age = age from student where id = @id and sex = @age; ¡¡¡¡go ¡¡¡¡-- ¡¡¡¡declare @id int, ¡¡¡¡@name varchar(20), ¡¡¡¡@temp varchar(20); ¡¡¡¡set @id = 7; ¡¡¡¡set @temp = 1; ¡¡¡¡exec proc_getStudentRecord @id, @name out, @temp output; ¡¡¡¡select @name, @temp; ¡¡¡¡print @name + '#' + @temp; |
¡¡7¡¢ ²»»º´æ´æ´¢¹ý³Ì
--WITH RECOMPILE ²»»º´æ ¡¡¡¡if (object_id('proc_temp', 'P') is not null) ¡¡¡¡drop proc proc_temp ¡¡¡¡go ¡¡¡¡create proc proc_temp ¡¡¡¡with recompile ¡¡¡¡as ¡¡¡¡select * from student; ¡¡¡¡go ¡¡¡¡exec proc_temp; |
8¡¢ ¼ÓÃÜ´æ´¢¹ý³Ì
--¼ÓÃÜWITH ENCRYPTION ¡¡¡¡if (object_id('proc_temp_encryption', 'P') is not null) ¡¡¡¡drop proc proc_temp_encryption ¡¡¡¡go ¡¡¡¡create proc proc_temp_encryption ¡¡¡¡with encryption ¡¡¡¡as ¡¡¡¡select * from student; ¡¡¡¡go ¡¡¡¡exec proc_temp_encryption; ¡¡¡¡exec sp_helptext 'proc_temp'; ¡¡¡¡exec sp_helptext 'proc_temp_encryption'; |
¡¡9¡¢ ´øÓαê²ÎÊý´æ´¢¹ý³Ì
if (object_id('proc_cursor', 'P') is not null) drop proc proc_cursor go create proc proc_cursor @cur cursor varying output as set @cur = cursor forward_only static for select id, name, age from student; open @cur; go --µ÷Óà declare @exec_cur cursor; declare @id int, @name varchar(20), @age int; exec proc_cursor @cur = @exec_cur output;--µ÷Óô洢¹ý³Ì fetch next from @exec_cur into @id, @name, @age; while (@@fetch_status = 0) begin fetch next from @exec_cur into @id, @name, @age; print 'id: ' + convert(varchar, @id) + ', name: ' + @name + ', age: ' + convert(char, @age); end close @exec_cur; deallocate @exec_cur;--ɾ³ýÓαê |
10¡¢ ·ÖÒ³´æ´¢¹ý³Ì
---´æ´¢¹ý³Ì¡¢row_numberÍê³É·ÖÒ³ if (object_id('pro_page', 'P') is not null) drop proc proc_cursor go create proc pro_page @startIndex int, @endIndex int as select count(*) from product ; select * from ( select row_number() over(order by pid) as rowId, * from product ) temp where temp.rowId between @startIndex and @endIndex go --drop proc pro_page exec pro_page 1, 4 -- --·ÖÒ³´æ´¢¹ý³Ì if (object_id('pro_page', 'P') is not null) drop proc pro_stu go create procedure pro_stu( @pageIndex int, @pageSize int ) as declare @startRow int, @endRow int set @startRow = (@pageIndex - 1) * @pageSize +1 set @endRow = @startRow + @pageSize -1 select * from ( select *, row_number() over (order by id asc) as number from student ) t where t.number between @startRow and @endRow; exec pro_stu 2, 2; ¡¤ Raiserror |
Raiserror·µ»ØÓû§¶¨ÒåµÄ´íÎóÐÅÏ¢£¬¿ÉÒÔÖ¸¶¨ÑÏÖØ¼¶±ð£¬ÉèÖÃϵͳ±äÁ¿¼Ç¼Ëù·¢ÉúµÄ´íÎó¡£
Óï·¨ÈçÏ£º
Raiserror({msg_id | msg_str | @local_variable} ¡¡¡¡{, severity, state} ¡¡¡¡[,argument[,¡n]] ¡¡¡¡[with option[,¡n]] ¡¡¡¡) |
# msg_id:ÔÚsysmessagesϵͳ±íÖÐÖ¸¶¨µÄÓû§¶¨Òå´íÎóÐÅÏ¢
# msg_str:Óû§¶¨ÒåµÄÐÅÏ¢£¬ÐÅÏ¢×î´ó³¤¶ÈÔÚ2047¸ö×Ö·û¡£
# severity£ºÓû§¶¨ÒåÓë¸ÃÏûÏ¢¹ØÁªµÄÑÏÖØ¼¶±ð¡£µ±Ê¹ÓÃmsg_idÒý·¢Ê¹ÓÃsp_addmessage´´½¨µÄÓû§¶¨ÒåÏûϢʱ£¬raiserrorÉÏÖ¸¶¨ÑÏÖØÐÔ½«¸²¸Çsp_addmessageÖж¨ÒåµÄÑÏÖØÐÔ¡£
ÈκÎÓû§¿ÉÒÔÖ¸¶¨0-18Ö±½ÓµÄÑÏÖØ¼¶±ð¡£Ö»ÓÐsysadmin¹Ì¶¨·þÎñÆ÷½ÇÉ«³£Óûò¾ßÓÐalter
traceȨÏÞµÄÓû§²ÅÄÜÖ¸¶¨19-25Ö±½ÓµÄÑÏÖØ¼¶±ð¡£19-25Ö®¼äµÄ°²È«¼¶±ðÐèҪʹÓÃwith logÑ¡Ïî¡£
# state£º½éÓÚ1ÖÁ127Ö±½ÓµÄÈκÎÕûÊý¡£StateĬÈÏÖµÊÇ1¡£
raiserror('is error', 16, 1); ¡¡¡¡select * from sys.messages; |
--ʹÓÃsysmessagesÖж¨ÒåµÄÏûÏ¢
raiserror(33003, 16, 1); ¡¡¡¡raiserror(33006, 16, 1); |
|