Äú¿ÉÒÔ¾èÖú£¬Ö§³ÖÎÒÃǵĹ«ÒæÊÂÒµ¡£

1Ôª 10Ôª 50Ôª





ÈÏÖ¤Â룺  ÑéÖ¤Âë,¿´²»Çå³þ?Çëµã»÷Ë¢ÐÂÑéÖ¤Âë ±ØÌî



  ÇóÖª ÎÄÕ ÎÄ¿â Lib ÊÓÆµ iPerson ¿Î³Ì ÈÏÖ¤ ×Éѯ ¹¤¾ß ½²×ù Modeler   Code  
»áÔ±   
 
   
 
 
     
   
 ¶©ÔÄ
  ¾èÖú
SQL Server´æ´¢¹ý³Ì±àд
 
×÷ÕߣºÅ©´åµÄÎÒ À´Ô´£º51Testing ·¢²¼ÓÚ£º2015-03-03
  2184  次浏览      27
 

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);
   
2184 ´Îä¯ÀÀ       27
Ïà¹ØÎÄÕÂ

»ùÓÚEAµÄÊý¾Ý¿â½¨Ä£
Êý¾ÝÁ÷½¨Ä££¨EAÖ¸ÄÏ£©
¡°Êý¾Ýºþ¡±£º¸ÅÄî¡¢ÌØÕ÷¡¢¼Ü¹¹Óë°¸Àý
ÔÚÏßÉ̳ÇÊý¾Ý¿âϵͳÉè¼Æ ˼·+Ч¹û
 
Ïà¹ØÎĵµ

GreenplumÊý¾Ý¿â»ù´¡Åàѵ
MySQL5.1ÐÔÄÜÓÅ»¯·½°¸
ijµçÉÌÊý¾ÝÖÐ̨¼Ü¹¹Êµ¼ù
MySQL¸ßÀ©Õ¹¼Ü¹¹Éè¼Æ
Ïà¹Ø¿Î³Ì

Êý¾ÝÖÎÀí¡¢Êý¾Ý¼Ü¹¹¼°Êý¾Ý±ê×¼
MongoDBʵս¿Î³Ì
²¢·¢¡¢´óÈÝÁ¿¡¢¸ßÐÔÄÜÊý¾Ý¿âÉè¼ÆÓëÓÅ»¯
PostgreSQLÊý¾Ý¿âʵսÅàѵ
×îл¼Æ»®
DeepSeekÔÚÈí¼þ²âÊÔÓ¦ÓÃʵ¼ù 4-12[ÔÚÏß]
DeepSeek´óÄ£ÐÍÓ¦Óÿª·¢Êµ¼ù 4-19[ÔÚÏß]
UAF¼Ü¹¹ÌåϵÓëʵ¼ù 4-11[±±¾©]
AIÖÇÄÜ»¯Èí¼þ²âÊÔ·½·¨Óëʵ¼ù 5-23[ÉϺ£]
»ùÓÚ UML ºÍEA½øÐзÖÎöÉè¼Æ 4-26[±±¾©]
ÒµÎñ¼Ü¹¹Éè¼ÆÓ뽨ģ 4-18[±±¾©]

MySQLË÷Òý±³ºóµÄÊý¾Ý½á¹¹
MySQLÐÔÄܵ÷ÓÅÓë¼Ü¹¹Éè¼Æ
SQL ServerÊý¾Ý¿â±¸·ÝÓë»Ö¸´
ÈÃÊý¾Ý¿â·ÉÆðÀ´ 10´óDB2ÓÅ»¯
oracleµÄÁÙʱ±í¿Õ¼äдÂú´ÅÅÌ
Êý¾Ý¿âµÄ¿çƽ̨Éè¼Æ


²¢·¢¡¢´óÈÝÁ¿¡¢¸ßÐÔÄÜÊý¾Ý¿â
¸ß¼¶Êý¾Ý¿â¼Ü¹¹Éè¼ÆÊ¦
HadoopÔ­ÀíÓëʵ¼ù
Oracle Êý¾Ý²Ö¿â
Êý¾Ý²Ö¿âºÍÊý¾ÝÍÚ¾ò
OracleÊý¾Ý¿â¿ª·¢Óë¹ÜÀí


GE Çø¿éÁ´¼¼ÊõÓëʵÏÖÅàѵ
º½Ìì¿Æ¹¤Ä³×Ó¹«Ë¾ Nodejs¸ß¼¶Ó¦Óÿª·¢
ÖÐÊ¢Òæ»ª ׿Խ¹ÜÀíÕß±ØÐë¾ß±¸µÄÎåÏîÄÜÁ¦
ijÐÅÏ¢¼¼Êõ¹«Ë¾ PythonÅàѵ
ij²©²ÊITϵͳ³§ÉÌ Ò×ÓÃÐÔ²âÊÔÓëÆÀ¹À
ÖйúÓÊ´¢ÒøÐÐ ²âÊÔ³ÉÊì¶ÈÄ£Ðͼ¯³É(TMMI)
ÖÐÎïÔº ²úÆ·¾­ÀíÓë²úÆ·¹ÜÀí