OracleϵÁУºRecordºÍPL/SQL±í
Ò»£¬Ê²Ã´ÊǼǼRecordºÍPL/SQL±í£¿
¼Ç¼Record£ºÓɵ¥ÐжàÁеıêÁ¿ÀàÐ͹¹³ÉµÄÁÙʱ¼Ç¼¶ÔÏóÀàÐÍ¡£ÀàËÆÓÚ¶àάÊý×é¡£
PL/SQL±í£ºÓɶàÐе¥ÁеÄË÷ÒýÁкͿÉÓÃÁй¹³ÉµÄÁÙʱË÷Òý±í¶ÔÏóÀàÐÍ¡£ÀàËÆÓÚһάÊý×éºÍ¼üÖµ¶Ô¡£
¶¼ÊÇÓû§×Ô¶¨ÒåÊý¾ÝÀàÐÍ¡£
¶þ£¬Record + PL/SQL±í ÓÃ;ÊÇʲô£¿
Record + PL/SQL±í¿ÉÒÔ½øÐÐÊý¾ÝµÄ¶àÐжàÁд洢¡£ÕâÑùÎÒÃǾͿÉʹÓÃRecord + PL/SQL±íÔÚÐèҪʱ·â×°Ò»¸öÁÙʱµÄ±í¶ÔÏ󣬽øÐд«µÝºÍ²Ù×÷¡£
ͨ¹ýRecord×Ô¶¨Òå±í½á¹¹£¬·â×°Ò»Ìõ¼Ç¼¡£PL/SQL±íÉùÃ÷ ¿ÉÓÃÁÐ ÀàÐÍ ÎªRecordÀàÐÍ£¨½«¿ÉÓÃÁÐÖ¸ÏòRecordÀàÐͱäÁ¿£©£¬Ã¿¸öË÷Òý¶ÔÓ¦Ò»¸öRecordÀàÐͱäÁ¿¡£
Èý£¬Ê¹ÓÃRecord + PL/SQL±í½øÐÐÊý¾ÝµÄ¶àÐжàÁд洢
¢ÙÉùÃ÷RecordÀàÐͺÍPL/SQL±í£¬
ÆäÖÐPL/SQL±íµÄË÷ÒýÁÐΪÖ÷¼üÔ¼ÊøºÍΨһԼÊøÁлò×ÔÔöInteger¡£¿ÉÓÃÁÐΪRecordÀàÐÍ»ò%RowTypeÀàÐÍ¡£
¢ÚÌî³äPL/SQL±í¿ÉÓÃÁУ¨RecordÀàÐÍ£©£ºÍ¨¹ýË÷ÒýÖ¸ÏòRecord£¬Ê¹ÓÃRecord·ÃÎʼǼ³ÉÔ±¡£
Óï·¨£º
PL/SQL±íÃû(Ë÷ÒýÁÐÖµ).¼Ç¼³ÉÔ± := ¼Ç¼³ÉÔ±ÀàÐÍÖµ£»
&nbs ......
OracleϵÁУºRecordºÍPL/SQL±í
Ò»£¬Ê²Ã´ÊǼǼRecordºÍPL/SQL±í£¿
¼Ç¼Record£ºÓɵ¥ÐжàÁеıêÁ¿ÀàÐ͹¹³ÉµÄÁÙʱ¼Ç¼¶ÔÏóÀàÐÍ¡£ÀàËÆÓÚ¶àάÊý×é¡£
PL/SQL±í£ºÓɶàÐе¥ÁеÄË÷ÒýÁкͿÉÓÃÁй¹³ÉµÄÁÙʱË÷Òý±í¶ÔÏóÀàÐÍ¡£ÀàËÆÓÚһάÊý×éºÍ¼üÖµ¶Ô¡£
¶¼ÊÇÓû§×Ô¶¨ÒåÊý¾ÝÀàÐÍ¡£
¶þ£¬Record + PL/SQL±í ÓÃ;ÊÇʲô£¿
Record + PL/SQL±í¿ÉÒÔ½øÐÐÊý¾ÝµÄ¶àÐжàÁд洢¡£ÕâÑùÎÒÃǾͿÉʹÓÃRecord + PL/SQL±íÔÚÐèҪʱ·â×°Ò»¸öÁÙʱµÄ±í¶ÔÏ󣬽øÐд«µÝºÍ²Ù×÷¡£
ͨ¹ýRecord×Ô¶¨Òå±í½á¹¹£¬·â×°Ò»Ìõ¼Ç¼¡£PL/SQL±íÉùÃ÷ ¿ÉÓÃÁÐ ÀàÐÍ ÎªRecordÀàÐÍ£¨½«¿ÉÓÃÁÐÖ¸ÏòRecordÀàÐͱäÁ¿£©£¬Ã¿¸öË÷Òý¶ÔÓ¦Ò»¸öRecordÀàÐͱäÁ¿¡£
Èý£¬Ê¹ÓÃRecord + PL/SQL±í½øÐÐÊý¾ÝµÄ¶àÐжàÁд洢
¢ÙÉùÃ÷RecordÀàÐͺÍPL/SQL±í£¬
ÆäÖÐPL/SQL±íµÄË÷ÒýÁÐΪÖ÷¼üÔ¼ÊøºÍΨһԼÊøÁлò×ÔÔöInteger¡£¿ÉÓÃÁÐΪRecordÀàÐÍ»ò%RowTypeÀàÐÍ¡£
¢ÚÌî³äPL/SQL±í¿ÉÓÃÁУ¨RecordÀàÐÍ£©£ºÍ¨¹ýË÷ÒýÖ¸ÏòRecord£¬Ê¹ÓÃRecord·ÃÎʼǼ³ÉÔ±¡£
Óï·¨£º
PL/SQL±íÃû(Ë÷ÒýÁÐÖµ).¼Ç¼³ÉÔ± := ¼Ç¼³ÉÔ±ÀàÐÍÖµ£»
&nbs ......
Oracle ϵÁУº PL/SQL±í
Ò»£¬Ê²Ã´ÊÇPL/SQL±í£¿
Ê×ÏÈPL/SQL±íºÍ¼Ç¼(Record)Ò»Ñù£¬¶¼ÊǸ´ºÏÊý¾ÝÀàÐÍ¡£¿ÉÒÔ¿´×öÊÇÒ»ÖÖÓû§×Ô¶¨ÒåÊý¾ÝÀàÐÍ¡£
PL/SQL±íÓɶàÁе¥ÐеıêÁ¿¹¹³ÉµÄÁÙʱË÷Òý±í¶ÔÏó¡£×é³ÉÀàËÆÓÚһάÊý×é¡£
Çø±ðºÍÁªÏµ¼Ç¼(Record)µÄµ¥ÐжàÁкÍÎïÀí´æ´¢Êý¾ÝµÄ±í¡£
Record + PL/SQL±í¿ÉÒÔ½øÐÐÊý¾ÝµÄ¶àÐжàÁд洢¡£
ÉúÃüÖÜÆÚÔÚ³ÌÐò¿éÄÚ¡£
¶þ£¬PL/SQL±íµÄ×é³É½á¹¹
PL/SQL±íÖ»ÓÐÁ½ÁÐ,ÆäÖеÚÒ»ÁÐΪË÷ÒýÁÐΪIntegerÀàÐÍË÷ÒýÁУ¬9iºó¿ÉÒÔÖ§³Ö×Ö·ûË÷Òý¡£µÚ¶þÁÐΪÓû§×Ô¶¨ÒåÁУ¬ÉùÃ÷Óû§¿ÉÓÃÁÐÀàÐÍ.
ͨ¹ýË÷ÒýÁÐÖеÄË÷ÒýÖµÀ´²Ù×÷PL/SQL±íÖжÔÓ¦µÄÓû§×Ô¶¨ÒåÁС£ÀàËÆÓÚ¼üÖµ¶Ô¡£
È·±£Ë÷ÒýÖµµÄΨһÐÔ¡£Èç¹ûË÷ÒýÖµÖظ´²»»á±¨´í£¬»á¸²¸ÇÇ°ÃæµÄÖµ¡£
Èý£¬ÈçºÎ´´½¨ºÍʹÓÃPL/SQL±í£¿
¢ÙÉùÃ÷PL/SQL±íÀàÐͶÔÏó
Óï·¨£º
TYPE PL/SQL±íÃû IS TABLE OF ¿ÉÓÃÁÐÀàÐÍ [NOT NULL]
INDEX BY BINARY_INTEGER;
¿ÉÓÃÁÐÀàÐÍ¿ÉÒÔΪOracleµÄÊý¾ÝÀàÀàÐÍÒÔ¼°Óû§×Ô¶¨ÒåÀàÐÍ;
ÊôÐÔ·½·¨:
count --·µ»Øpl/sql±íµÄ× ......
Oracle ϵÁУº PL/SQL±í
Ò»£¬Ê²Ã´ÊÇPL/SQL±í£¿
Ê×ÏÈPL/SQL±íºÍ¼Ç¼(Record)Ò»Ñù£¬¶¼ÊǸ´ºÏÊý¾ÝÀàÐÍ¡£¿ÉÒÔ¿´×öÊÇÒ»ÖÖÓû§×Ô¶¨ÒåÊý¾ÝÀàÐÍ¡£
PL/SQL±íÓɶàÁе¥ÐеıêÁ¿¹¹³ÉµÄÁÙʱË÷Òý±í¶ÔÏó¡£×é³ÉÀàËÆÓÚһάÊý×é¡£
Çø±ðºÍÁªÏµ¼Ç¼(Record)µÄµ¥ÐжàÁкÍÎïÀí´æ´¢Êý¾ÝµÄ±í¡£
Record + PL/SQL±í¿ÉÒÔ½øÐÐÊý¾ÝµÄ¶àÐжàÁд洢¡£
ÉúÃüÖÜÆÚÔÚ³ÌÐò¿éÄÚ¡£
¶þ£¬PL/SQL±íµÄ×é³É½á¹¹
PL/SQL±íÖ»ÓÐÁ½ÁÐ,ÆäÖеÚÒ»ÁÐΪË÷ÒýÁÐΪIntegerÀàÐÍË÷ÒýÁУ¬9iºó¿ÉÒÔÖ§³Ö×Ö·ûË÷Òý¡£µÚ¶þÁÐΪÓû§×Ô¶¨ÒåÁУ¬ÉùÃ÷Óû§¿ÉÓÃÁÐÀàÐÍ.
ͨ¹ýË÷ÒýÁÐÖеÄË÷ÒýÖµÀ´²Ù×÷PL/SQL±íÖжÔÓ¦µÄÓû§×Ô¶¨ÒåÁС£ÀàËÆÓÚ¼üÖµ¶Ô¡£
È·±£Ë÷ÒýÖµµÄΨһÐÔ¡£Èç¹ûË÷ÒýÖµÖظ´²»»á±¨´í£¬»á¸²¸ÇÇ°ÃæµÄÖµ¡£
Èý£¬ÈçºÎ´´½¨ºÍʹÓÃPL/SQL±í£¿
¢ÙÉùÃ÷PL/SQL±íÀàÐͶÔÏó
Óï·¨£º
TYPE PL/SQL±íÃû IS TABLE OF ¿ÉÓÃÁÐÀàÐÍ [NOT NULL]
INDEX BY BINARY_INTEGER;
¿ÉÓÃÁÐÀàÐÍ¿ÉÒÔΪOracleµÄÊý¾ÝÀàÀàÐÍÒÔ¼°Óû§×Ô¶¨ÒåÀàÐÍ;
ÊôÐÔ·½·¨:
count --·µ»Øpl/sql±íµÄ× ......
µÚÒ»²½£¬ÔÚÒµÎñ¸ß·åÆÚץȡÑù±¾Êý¾Ý£¨2¸öСʱ×óÓÒ£©¡£²ÉÓõŤ¾ßÊÇsqlserver×Ô´øµÄprofiler£¬Ò²½Ðʼþ̽²éÆ÷£¬ÈçÏÂͼ£º
½øÈëºó£¬µã»÷×î×óÃæµÄ°´Å¥£¬½¨Á¢Ò»¸öеĸú×Ù£º
µÇ¼ÐèÒªÓÃDBOȨÏÞ£¬ËùÒÔ¿ÉÒÔÓÃsaµÇ¼£¬Ò²¿ÉÒÔÓÃwindows¼¯³ÉÑéÖ¤·½Ê½£¨Èç¹ûµ±Ç°µÇ¼µÄ¾ÍÊÇsqlserverµÄ»°£©
н¨¸ú×Ù£¬Ò»¹²ÓÐ4¸ötabÒ³½øÐÐÅäÖã¬Ê×ÏÈ¿´µÚÒ»¸ö¡£¸ú×ÙÃû³Æ²»Óøü¸Ä£¬Ä¬Èϵļ´¿É¡£±£´æÒ»¹²ÓÐÁ½ÖÖ·½Ê½£¬Ò»ÊÇÎļþ£¬À©Õ¹ÃûÊÇ.trc£¨ÕâÖÖ·½Ê½·½±ãÄã°Ñ¿Í»§ÄÇÀïµÄ¸ú×Ù½á¹û·¢¸øÄ㣩£¬Æä¶þÊÇÊý¾Ý¿âÖÐµÄ±í¡£
ΪÁË·ÖÎö·½±ã£¬ÎÒÃÇ°ÑËüÁí´æΪ±í¡£´ËʱsqlÌáʾÄãÖØнøÐеǼ£¬ÕâÀïÎÒÃÇ°Ñ±í±£´æµ½masterÖÐ
¼ÙÉè±íÃû×Ö½Ð×öjq£¨Èç¹ûÓÐÖظ´µÄ£¬ÏµÍ³»áÌáʾÊÇ·ñ¸²¸Ç£©
È·¶¨ºó»Øµ½Á˸ղŵĵÚÒ»¸ötabÒ³ÖУº
È»ºóÇл»µ½µÚ¶þ¸öÑ¡ÏÖУº
×óÃæÁгöÁ˸÷ÖÖʼþÀࣨEvent Class£©£¬ÓÒÃæÊǵ±Ç°ÒÑÓеÄʼþÀà¡£¶ÔÓÚÐÔÄܵ÷ÓÅ£¬ÎÒÃDz»ÐèÒª°²È«ÉóºË¡¢»á»°ÐÅÏ¢£¬µã»÷ɾ³ý°´Å¥¼´¿É£º
¼ÌÐøÇл»µ½µÚÈý¸ötabÒ³ÉÏ£¬ÕâÀïµÄÊý¾ÝÁÐĬÈϾ͹»ÁË£¬µ±È»£¬Èç¹ûÄã¿´×Ų»Ë³ÑÛ£¬¿ÉÒÔ°ÑAppname/NT usernameµÈ¶¼É¾³ý¡£
×îºóÒ»¸ötabÒ³ÉÏ£¬ÎÒÃÇÐèÒª°Ñϵͳ×Ô¼º²úÉúµÄʼþIDÆÁ±Îµô£º
°ÑÄǸöÅųýÏµÍ ......
Ò»¡¢°´Ö¸¶¨·ûºÅ·Ö¸î×Ö·û´®£¬·µ»Ø·Ö¸îºóµÄÔªËظöÊý£¬·½·¨ºÜ¼òµ¥£¬¾ÍÊÇ¿´×Ö·û´®ÖдæÔÚ¶àÉÙ¸ö·Ö¸ô·ûºÅ£¬È»ºóÔÙ¼ÓÒ»£¬¾ÍÊÇÒªÇóµÄ½á¹û¡£
CREATE function Get_StrArrayLength
(
@str varchar(1024), --Òª·Ö¸îµÄ×Ö·û´®
@split varchar(10) --·Ö¸ô·ûºÅ
)
returns int
as
begin
declare @location int
declare @start int
declare @length int
set @str=ltrim(rtrim(@str))
set @location=charindex(@split,@str)
set @length=1
while @location<>0
begin
set @start=@location+1
set @location=charindex(@split,@str,@start)
set @length=@length+1
end
return @length
end
µ÷ÓÃʾÀý£ºselect dbo.Get_StrArrayLength('78,1,2,3',',')
·µ»ØÖµ£º4
¶þ¡¢°´Ö¸¶¨·ûºÅ·Ö¸î×Ö·û´®£¬·µ»Ø·Ö ......
50ÖÖÇÉÃîÓÅ»¯SQL ServerÊý¾Ý¿âµÄ·½·¨
×÷Õߣº²»Ïê ³ö´¦£º²»Ïê
²éѯËÙ¶ÈÂýµÄÔÒòºÜ¶à£¬³£¼ûÈçϼ¸ÖÖ£º
ûÓÐË÷Òý»òÕßûÓÐÓõ½Ë÷Òý(ÕâÊDzéѯÂý×î³£¼ûµÄÎÊÌ⣬ÊdzÌÐòÉè¼ÆµÄȱÏÝ)¡£
I/OÍÌÍÂÁ¿Ð¡£¬ÐγÉÁËÆ¿¾±Ð§Ó¦¡£
ûÓд´½¨¼ÆËãÁе¼Ö²éѯ²»ÓÅ»¯¡£
ÄÚ´æ²»×ã¡£
ÍøÂçËÙ¶ÈÂý¡£
²éѯ³öµÄÊý¾ÝÁ¿¹ý´ó(¿ÉÒÔ²ÉÓöà´Î²éѯ£¬ÆäËûµÄ·½·¨½µµÍÊý¾ÝÁ¿)¡£
Ëø»òÕßËÀËø(ÕâÒ²ÊDzéѯÂý×î³£¼ûµÄÎÊÌ⣬ÊdzÌÐòÉè¼ÆµÄȱÏÝ)¡£
sp_lock,sp_who,»î¶¯µÄÓû§²é¿´,ÔÒòÊǶÁд¾ºÕù×ÊÔ´¡£
·µ»ØÁ˲»±ØÒªµÄÐкÍÁС£
²éѯÓï¾ä²»ºÃ£¬Ã»ÓÐÓÅ»¯¡£
¿ÉÒÔͨ¹ýÈçÏ·½·¨À´ÓÅ»¯²éѯ :
1¡¢°ÑÊý¾Ý¡¢ÈÕÖ¾¡¢Ë÷Òý·Åµ½²»Í¬µÄI/OÉ豸ÉÏ£¬Ôö¼Ó¶ÁÈ¡Ëٶȣ¬ÒÔÇ°¿ÉÒÔ½«TempdbÓ¦·ÅÔÚRAID0ÉÏ£¬SQL2000²»ÔÚÖ§³Ö¡£Êý¾ÝÁ¿(³ß´ç)Ô½´ó£¬Ìá¸ßI/OÔ½ÖØÒª¡£
2¡¢×ÝÏò¡¢ºáÏò·Ö¸î±í£¬¼õÉÙ±íµÄ³ß´ç(sp_spaceuse)¡£
3¡¢Éý¼¶Ó²¼þ¡£
4¡¢¸ù¾Ý²éѯÌõ¼þ,½¨Á¢Ë÷Òý,ÓÅ»¯Ë÷Òý¡¢ÓÅ»¯·ÃÎÊ·½Ê½£¬ÏÞÖƽá¹û¼¯µÄÊý¾ÝÁ¿¡£×¢ÒâÌî³äÒò×ÓÒªÊʵ±(×îºÃÊÇʹÓÃĬÈÏÖµ0)¡£Ë÷ÒýÓ¦¸Ã¾¡Á¿Ð¡£¬Ê¹ÓÃ×Ö½ÚÊýСµÄÁн¨Ë÷ÒýºÃ(²ÎÕÕË÷ÒýµÄ´´½¨),²»Òª¶ÔÓÐÏ޵ļ¸¸öÖµµÄ×ֶν¨µ¥Ò»Ë÷ÒýÈçÐÔ±ð×ֶΡ£
5¡¢Ìá¸ßÍøËÙ¡£
6¡¢À©´ó·þÎñÆ÷µÄÄÚ´æ,Windows 2000ºÍSQL server 200 ......
Student(S#,Sname,Sage,Ssex) ѧÉú±í
Course(C#,Cname,T#) ¿Î³Ì±í
SC(S#,C#,score) ³É¼¨±í
Teacher(T#,Tname) ½Ìʦ±í
ÎÊÌ⣺
1¡¢²éѯ“”¿Î³Ì±È“”¿Î³Ì³É¼¨¸ßµÄËùÓÐѧÉúµÄѧºÅ£»
SELECT a.S# from (SELECT s#,score from SC WHERE C#='001') a,
(SELECT s#,score from SC WHERE C#='002') b
WHERE a.score>b.score AND a.s#=b.s#;
2¡¢²éѯƽ¾ù³É¼¨´óÓÚ·ÖµÄͬѧµÄѧºÅºÍƽ¾ù³É¼¨£»
SELECT S#,avg(score)
from sc
GROUP BY S# having avg(score) >60;
3¡¢²éѯËùÓÐͬѧµÄѧºÅ¡¢ÐÕÃû¡¢Ñ¡¿ÎÊý¡¢×ܳɼ¨£»
SELECT Student.S#,Student.Sname,count(SC.C#),sum(score)
from Student left Outer JOIN SC on Student.S#=SC.S#
GROUP BY Student.S#,Sname
4¡¢²éѯÐÕ“ÀÄÀÏʦµÄ¸öÊý£»
SELECT count(distinct(Tname))
from Teacher
WHERE Tname like 'Àî%';
5¡¢²éѯûѧ¹ý“Ҷƽ&rdqu ......