SQLÖ®Óû§×Ô¶¨Ò庯Êý
Óû§×Ô¶¨Ò庯Êý£¨User Defined Functions£©ÊÇSQL Server µÄÊý¾Ý¿â¶ÔÏó£¬Ëü²»ÄÜÓÃÓÚÖ´ÐÐһϵÁиıäÊý¾Ý¿â״̬µÄ²Ù×÷£¬µ«Ëü¿ÉÒÔÏñϵͳº¯ÊýÒ»ÑùÔÚ²éѯ»ò´æ´¢¹ý³ÌµÈµÄ³ÌÐò¶ÎÖÐʹÓã¬Ò²¿ÉÒÔÏñ´æ´¢¹ý³ÌÒ»Ñùͨ¹ý EXECUTE ÃüÁîÀ´Ö´ÐС£Óû§×Ô¶¨Ò庯ÊýÖд洢ÁËÒ»¸öTransact-SQL Àý³Ì£¬¿ÉÒÔ·µ»ØÒ»¶¨µÄÖµ¡£
¡¡¡¡ÔÚSQL Server Öиù¾Ýº¯Êý·µ»ØÖµÐÎʽµÄ²»Í¬½«Óû§×Ô¶¨Ò庯Êý·ÖΪÈýÖÖÀàÐÍ£º
¡¡¡¡±êÁ¿Ðͺ¯Êý£¨Scalar functions£©
¡¡¡¡±êÁ¿Ðͺ¯Êý·µ»ØÒ»¸öÈ·¶¨ÀàÐ͵ıêÁ¿ÖµÆä·µ»ØÖµÀàÐÍΪ³ýTEXT¡¢ NTEXT¡¢ IMAGE¡¢CURSOR¡¢ TIMESTAMP ºÍTABLE ÀàÐÍÍâµÄÆäËüÊý¾ÝÀàÐÍ¡£º¯ÊýÌåÓï¾ä¶¨ÒåÔÚBEGIN-ENDÓï¾äÄÚ£¬ÆäÖаüº¬ÁË¿ÉÒÔ·µ»ØÖµµÄTransact-SQL ÃüÁî¡£
¡¡¡¡ÄÚÁª±íÖµÐͺ¯Êý£¨Inline table-valued functions£©
¡¡¡¡ÄÚÁª±íÖµÐͺ¯ÊýÒÔ±íµÄÐÎʽ·µ»ØÒ»¸ö·µ»ØÖµ£¬¼´Ëü·µ»ØµÄÊÇÒ»¸ö±íÄÚÁª±íÖµÐͺ¯ÊýûÓÐÓÉBEGIN-END Óï¾äÀ¨ÆðÀ´µÄº¯ÊýÌå¡£Æä·µ»ØµÄ±íÓÉÒ»¸öλÓÚRETURN ×Ó¾äÖеÄSELECT ÃüÁî¶Î´ÓÊý¾Ý¿âÖÐɸѡ³öÀ´¡£ÄÚÁª±íÖµÐͺ¯Êý¹¦ÄÜÏ൱ÓÚÒ»¸ö²ÎÊý»¯µÄÊÓͼ¡£
¡¡¡¡¶àÉùÃ÷±íÖµÐͺ¯Êý£¨Multi-statement table-valued functions£©
¡¡¡¡¶àÉùÃ÷±íÖµÐͺ¯Êý¿ÉÒÔ¿´×÷±êÁ¿ÐͺÍÄÚÁª±íÖµÐͺ¯ÊýµÄ½áºÏÌå¡£ËüµÄ·µ»ØÖµÊÇÒ»¸ö±í£¬µ«ËüºÍ±êÁ¿Ðͺ¯ÊýÒ»ÑùÓÐÒ»¸öÓÃBEGIN-END Óï¾äÀ¨ÆðÀ´µÄº¯ÊýÌ壬·µ»ØÖµµÄ±íÖеÄÊý¾ÝÊÇÓɺ¯ÊýÌåÖеÄÓï¾ä²åÈëµÄ¡£Óɴ˿ɼû£¬Ëü¿ÉÒÔ½øÐжà´Î²éѯ£¬¶ÔÊý¾Ý½øÐжà´ÎɸѡÓëºÏ²¢£¬ÃÖ²¹ÁËÄÚÁª±íÖµÐͺ¯ÊýµÄ²»×ã¡£ ¡¡
¡¡¡¡SQL Server ΪÈýÖÖÀàÐ͵ÄÓû§×Ô¶¨Ò庯ÊýÌṩÁ˲»Í¬µÄÃüÁî´´½¨¸ñʽ¡£
£¨1£© ´´½¨±êÁ¿ÐÍÓû§×Ô¶¨Ò庯Êý£¨Scalar functions£© ÆäÓï·¨ÈçÏ£º
¸÷²ÎÊý˵Ã÷ÈçÏ£º
¡¡¡¡owner_name £ºÖ¸¶¨Óû§×Ô¶¨Ò庯ÊýµÄËùÓÐÕß¡£¡¡¡¡function_name£ºÖ¸¶¨Óû§×Ô¶¨Ò庯ÊýµÄÃû³Æ¡£database_name.owner_name.function_name Ó¦ÊÇΩһµÄ¡£ ¡¡¡¡@parameter_name£º¶¨ÒåÒ»¸ö»ò¶à¸ö²ÎÊýµÄÃû³Æ¡£Ò»¸öº¯Êý×î¶à¿ÉÒÔ¶¨Òå1024 ¸ö²ÎÊýÿ¸ö²ÎÊýÇ°ÓÓ@”·ûºÅ±êÃ÷¡£²ÎÊýµÄ×÷Ó÷¶Î§ÊÇÕû¸öº¯Êý¡£²ÎÊýÖ»ÄÜÌæ´ú³£Á¿£¬²»ÄÜÌæ´ú±íÃû¡¢ÁÐÃû»òÆäËüÊý¾Ý¿â¶ÔÏóµÄÃû³Æ¡£Óû§×Ô¶¨Ò庯Êý²»Ö§³ÖÊä³ö²ÎÊý¡£¡¡¡¡¡¡scalar_parameter_data_type£ºÖ¸¶¨±êÁ¿ÐͲÎÊýµÄÊý¾ÝÀàÐÍ£¬¿ÉÒÔΪ³ýTEXT¡¢ NTEXT¡¢ IMAGE¡¢ CURSOR¡¢TIMESTAMP ºÍTABLE ÀàÐÍÍâµÄÆäËüÊý¾ÝÀàÐÍ¡£¡¡¡¡¡¡scalar_return_data_type£ºÖ¸¶¨±êÁ¿ÐÍ·µ»ØÖµµÄÊý¾ÝÀàÐÍ£¬¿ÉÒÔΪ³ýTEXT¡¢ NTEXT¡¢ IMAGE¡¢ CURSOR¡¢TIMESTAMP ºÍTABLE ÀàÐÍÍâµÄÆäËüÊ
Ïà¹ØÎĵµ£º
1. ˵Ã÷£º¸´ÖƱí(Ö»¸´Öƽṹ£¬Ô´±íÃû£ºa£¬Ð±íÃû£ºb)
SQL: select * into b from a where 1<>1;
2. ˵Ã÷£º¿½±´±í(¿½±´Êý¾Ý£¬Ô´±íÃû£ºa£¬Ä¿±ê±íÃû£ºb)
SQL: insert into b(a, b, c) select d, e, f from b;
3. ......
SQLÊý¾Ý»Ö¸´Èí¼þ Log Explorer for SQL Server v4.0
log explorerʹÓõļ¸¸öÎÊÌâ
¡¡¡¡1)¶ÔÊý¾Ý¿â×öÁËÍêÈ« ²îÒì ºÍÈÕÖ¾±¸·Ý
¡¡¡¡±¸·ÝʱѡÓÃÁËɾ³ýÊÂÎñÈÕÖ¾Öв»»î¶¯µÄÌõÄ¿
¡¡¡¡ÔÙÓÃLog explorer´òÊÔͼ¿´ÈÕ־ʱ
¡¡¡¡ÌáʾNo log recorders found that match the filter£¬would you like to view unfiltered ......
ÎÊÌâÃèÊö:
¡¡¡¡Îª¹ÜÀí¸ÚλҵÎñÅàѵÐÅÏ¢£¬½¨Á¢3¸ö±í:
¡¡¡¡S (S#,SN,SD,SA) S#,SN,SD,SA ·Ö±ð´ú±íѧºÅ¡¢Ñ§Ô±ÐÕÃû¡¢ËùÊôµ¥Î»¡¢Ñ§Ô±ÄêÁä
¡¡¡¡C (C#,CN ) C#,CN ·Ö±ð´ú±í¿Î³Ì±àºÅ¡¢¿Î³ÌÃû³Æ
¡¡¡¡SC ( S#,C#,G ) S#,C#,G ·Ö±ð´ú±íѧºÅ¡¢ËùÑ¡Ð޵Ŀγ̱àºÅ¡¢Ñ§Ï°³É¼¨
¡¡¡¡1. ʹÓñê×¼SQLǶÌ×Óï¾ä²éѯѡÐ޿γÌÃû³ÆΪ&rsquo ......
SqlÓï¾ä
1. ˵Ã÷£º¸´ÖƱí(Ö»¸´Öƽṹ£¬Ô´±íÃû£ºa£¬Ð±íÃû£ºb) SQL:select * into bfrom awhere 1<>1;
2. ˵Ã÷£º¿½±´± ......
Ó¦ÓÃʾÀý£º
-- ´´½¨2¸ö²âÊÔ±í
CREATE TABLE [dbo].[Table_2019]([Data] [nchar](2019) NOT NULL)
CREATE TABLE [dbo].[Table_2020]([Data] [nchar](2020) NOT NULL)
go
-- Ìî³äÊý¾Ý
declare @i int
set @i = 0
while(@i < 20)
begin
insert Table_2019(Data) values('')
  ......