PL/SQLѧϰ±Ê¼Ç¶þ
1.³£Á¿
¶¨ÒåÓï·¨¸ñʽ£º
³£Á¿Ãû constant ÀàÐͱêʶ·û [not null]:=Öµ;
È磺PI constant number(9):=3.1415;
2.»ù±¾Êý¾ÝÀàÐͱäÁ¿
»ù±¾Êý¾ÝÀàÐÍ
number Êý×ÖÐÍ
int ÕûÊýÐÍ
pls_integer ÕûÊýÐÍ£¬²úÉúÒç³öʱ³öÏÖ´íÎó
binary_integer ÕûÊýÐÍ£¬±íʾ´ø·ûºÅµÄÕûÊý
char ¶¨³¤×Ö·ûÐÍ£¬×î´ó255¸ö×Ö·û
varchar2 ±ä³¤×Ö·ûÐÍ£¬×î´ó2000¸ö×Ö·û
long ±ä³¤×Ö·ûÐÍ£¬×î´ó2GB
date ÈÕÆÚÐÍ
boolean ²¼¶ûÐÍ£¨true,false,nullÈýÕßÖ®Ò»£©
»ù±¾Êý¾ÝÀàÐͱäÁ¿µÄ¶¨ÒåÓï·¨¸ñʽ
±äÁ¿Ãû ÀàÐͱêʶ·û [not null]:=Öµ;
È磺myvar varchar2(10):='right';
3.¸´ºÏÊý¾ÝÀàÐͱäÁ¿
3.1 ±í×Ö¶ÎÀàÐͱäÁ¿(ʹÓÃ%type¶¨Òå)
±äÁ¿µÄÀàÐÍÓëÊý¾Ý±íÖеÄ×ֶεÄÊý¾ÝÀàÐÍÒ»Ö¡£µ±Êý¾Ý¿â±íµÄ×Ö¶ÎÀàÐÍÐ޸ĺó£¬ÏàÓ¦µÄ±äÁ¿µÄÀàÐÍÒ²×Ô¶¯Ð޸ġ£
¶¨ÒåÓï·¨¸ñʽ£º±äÁ¿Ãû ±í×Ö¶ÎÃû%type;
È磺mydate tempuser.testtable.currentdate%type ¶¨ÒåÁËÃûΪmydateµÄ±äÁ¿£¬ÆäÀàÐÍÓëtempuser.testtable±íÖеÄcurrentdate×Ö¶ÎÀàÐÍÒ»Ö¡£
3.2 ¼Ç¼ÀàÐͱäÁ¿
¼Ç¼ÀàÐͶ¨ÒåÓï·¨¸ñʽ£º
type Êý¾ÝÀàÐÍÃû is record(
×Ö¶Î1 ÀàÐͱêʶ·û1£¬
×Ö¶Î2 ÀàÐͱêʶ·û2£¬
...
);
¼Ç¼ÀàÐͱäÁ¿¶¨Ò壺
±äÁ¿Ãû Êý¾ÝÀàÐÍÃû;
È綨Òå¼Ç¼ÀàÐÍ£º
type myrecord is record(myrecordnumber int, mycurrentdate date);
¶¨ÒåmyrecordÀàÐͱäÁ¿srecord£º
srecord myrecord;
·ÃÎÊmyrecordnumber×Ö¶ÎÖµ£º
srecord.myrecordnumber
×¢Ò⣺×Ö¶Î1£¬×Ö¶Î2µÈÒ²¿ÉÒÔÊǸ´ºÏÊý¾ÝÀàÐÍ¡£
3.3 ±í¼Ç¼ÐͱäÁ¿£¨Ê¹ÓÃ%rowtype¶¨Ò壩
¶¨ÒåÓï·¨¸ñʽ£º±äÁ¿Ãû ±íÃû%rowtype
±äÁ¿¿ÉÒÔ»ñµÃÕû¸ö±í¼Ç¼µÄÊý¾ÝÀàÐÍ£¬Ï൱ÓÚÏȶ¨ÒåÁËÒ»¸ö¼Ç¼ÀàÐÍ£¬Æä¸÷×Ö¶ÎÀàÐÍ·Ö±ð¶ÔÓ¦±íµÄ¸÷×Ö¶ÎÀàÐÍ£»È»ºóÔÙ¶¨ÒåÕâÖּǼÀàÐ͵ıäÁ¿
3.4 һά±íÀàÐͱäÁ¿
¶¨ÒåÓï·¨¸ñʽ£º
type ±íÀàÐÍ is table of ÀàÐÍ index by binary_integer;
±íÀàÐͱäÁ¿Ãû ±íÀàÐÍ;
Ï൱¶¨ÒåһάÊý×飬ÕâÀïµÄÀàÐÍ¿ÉÒÔÊÇÇ°ÃæµÄÀàÐͶ¨Ò壬index by binary_integer×Ó¾ä´ú±íÒÔ·ûºÅÕûÊýΪË÷Òý£¬ÕâÑù·ÃÎʱíÀàÐͱäÁ¿ÖеÄÊý¾Ý·½·¨¾ÍÊÇ“±í±äÁ¿Ãû(Ë÷Òý·ûºÅÕûÊý)“¡£
ʾÀý£º
declare
type tabletype1 is table of varchar2(4) index by binary_integer; --¶¨Òåһά±íÀàÐÍtabletype1
type tabletype2 is table of tempuser.testtable."recordnumber"%type index by binary_integer; --
Ïà¹ØÎĵµ£º
½ñÌìºÜÓôÃÆ,ʵÑéÊҵķþÎñÆ÷¶«Î÷¶¼Ã»ÓÐÁË,¶øÎÒµÄÊý¾Ý¿âÓÖÔÚÄÄÀï,ÎÒÓÖûÓб¸·Ý,°ÑÎÒÒÖÓôµÄÒªÃü,ÐÒ¿÷ÎÒÓõÄlinq to sql.×ÐϸÏëÏë,ËûµÄdbmlÎļþ»ù±¾°üº¬ÁËÊý¾Ý¿âµÄËùÓÐÄÚÈÝ,ËùÒÔ,Ò»¶¨Óа취´ÓdbmlÎļþÖд´½¨Êý¾Ý¿â!
Ñо¿ÁËÒ»¶ÎÊ ......
--´´½¨Á´½Ó·þÎñÆ÷
exec sp_addlinkedserver 'ITSV ', ' ', 'SQLOLEDB ', 'Ô¶³Ì·þÎñÆ÷Ãû»òipµØÖ· '
exec sp_addlinkedsrvlogin 'ITSV ', 'false ',null, 'Óû§Ãû ', 'ÃÜÂë '
--²éѯʾÀý
select * from ITSV.Êý¾Ý¿âÃû.dbo.±íÃû
--µ¼ÈëʾÀý
select * into ±í from ITSV.Êý¾Ý¿âÃû.dbo.±íÃû
--ÒÔºó²» ......
select Convert( varchar(20) , ʱ¼ä×ֶΠ, ¸ñʽ ) from ±í È磺select Convert(varchar(20),LOGIN_DATE,112) from dbo.C_PARTY_CLIENT 100£ºJun 22 2009 12:00AM 101£º06/22/2009 102£º2009.06.22 103£º22/06/2009 104£º22.06.2009 105£º22-06-2009 106£º22
select Convert(varchar(20),<ʱ¼ä×Ö¶Î>,<¸ñʽ>) f ......
1¡¢ISNULL ( check_expression , replacement_value )
²ÎÊý
check_expression
½«±»¼ì²éÊÇ·ñΪ NULLµÄ±í´ïʽ¡£check_expression ¿ÉÒÔÊÇÈκÎÀàÐ͵ġ£
replacement_valueÔÚ check_expressionΪNULLʱ½«·µ»ØµÄ±í´ïʽ¡£replacement_value ±ØÐëÓë check_expresssion ¾ßÓÐÏàͬµÄÀàÐÍ¡£
·µ»ØÀàÐÍ
·µ»ØÓë check_expression Ï ......
°ÑOldÊý¾Ý¿â¸ÄΪNewÊý¾Ý¿â
1.´ò¿ª“ÆóÒµ¹ÜÀíÆ÷”ÕÒµ½ OldÊý¾Ý¿â££¡·ÓÒ¼ü££¡·ËùÓÐÈÎÎñ££¡··ÖÀëÊý¾Ý¿â
2.µ½C:\Program Files\Microsoft SQL Server\MSSQL\DataÏÂÕÒµ½Old_Data.mdfºÍOld_log.ldf°ÑOld_Data»»³ÉÄãÏëÒªµÄÃû×ÖÀýÈçNew.mdf¡¢New.ldf
3.µ½ÆóÒµ¹ÜÀíÆ÷£¬ÕÒµ½ Êý¾Ý¿â ££¡·ÓÒ¼ü££¡·ËùÓÐÈÎÎñ££¡ ......