¹Ø¼ü´Ê:
°ó¶¨±äÁ¿(binding variable),¹²Ïí³Ø(shared buffer pool), SGA(system global area);
ÔÚ¿ª·¢Ò»¸öÊý¾Ý¿â
ϵͳǰ,ÓÐ˶ÔOracle ϵͳÁ˽âºÜ¶à,ÓÈÆäÊÇËüµÄÌØÐÔ,ºÃÏóºÜÉÙ°É;¶Ô³õѧÕßÀ´½²,Õâ¸üÊDz»¿ÉÄܵÄÊÂÇé;½ö½ö¼òµ¥ÕÆÎÕÁËSQL
µÄд·¨,¾Í¿ªÊ¼ÁËÊý¾Ý¿âµÄ¿ª·¢,Æä½á¹ûÖ»ÄÜÊÇ¿ª·¢Ò»¸öûÓÐЧÂÊ,ҲûÓпÉÀ©Õ¹µÄϵͳ;
j8m*uA$_G*T[`u0
Òò´Ë,ÎÒдÕâ¸öÖ÷ÌâÒ²ÊÇÏ£ÍûÈôó¼Ò¸ü¶àµØÕÆÎÕOracleÊý¾Ý¿âµÄÌØÐÔ,´Ó¶øÔڼܹ¹Ò»¸öÐÂϵͳʱ,ÄÜ¿¼ÂÇϵͳµÄ¿ÉÀ©Õ¹,¿ÉÉìËõÐÔ,Ò²¼æ¹ËϵͳµÄЧÂʺÍÎȶ¨;ITPUB¸öÈË¿Õ¼ä'm/jSI'B
\'^H
vm
e#u!V/[0
ʹÓð󶨱äÁ¿ÊÇOracleÊý¾Ý¿âµÄÌØÐÔÖ®Ò»;ÓÚÊÇ´ó¼ÒÒªÎÊ,ΪʲôʹÓÃ,ÔõÑùʹÓÃ,ËüµÄʹÓÃÏÞÖÆÌõ¼þÊÇʲô?ÎһᰴÕÕÕâÑùµÄÏ뷨ȥ½â´ð´ó¼ÒµÄÒÉÎÊ,ÎÒÒ²»áÒÔ¾ÙÀý×ӵķ½Ê½À´»Ø´ðÕâЩÎÊÌâ;
1. ΪʲôʹÓð󶨱äÁ¿?ITPUB¸öÈË¿Õ¼ä
Mh,tMh%@ V/f
ÕâÊǽâ¾öOracleÓ¦ÓóÌÐò¿ÉÉìËõÐÔµÄÒ»¸ö¹Ø¼ü»·½Ú;¶øOracleµÄ¹²Ïí³Ø¾Í¾ö¶¨ÁË¿ª·¢ÈËÔ±±ØÐëʹÓð󶨱äÁ¿;Èç¹ûÏëÒªOracle ÔËÐмõÂý,ÉõÖÁÍêÈ«ÖÕÖ¹,ÄǾͿÉÒÔ²»Óð󶨱äÁ¿;ITPUB¸öÈË¿Õ¼äAo\}4E&Qu]
ÕâÀï¾ÙÀý˵Ã÷ÉÏÊöÎÊÌâ;ITPUB¸öÈË¿Õ¼ä t:UG)Y]
ΪÁ˲éѯһ¸öÔ±¹¤´úºÅÊÇ123,Äã¿ÉÒÔÕâÑù²éѯ:
aSB$C&Gx DR,K0
select * from emp where empno=’123’;ITPUB¸öÈË¿Õ¼ä9K9KI^0s w
ÄãÒ²¿ÉÒÔÕâÑù²éѯ:ITPUB¸öÈË¿Õ¼ägu'Pp8M @j5E{v O
select * from emp where empno=:empno;
Ïó
ÎÒÃÇÍù³£Ò»Ñù,Äã²éѯԱ¹¤’123’Ò»´ÎÒÔºó,ÓпÉÄÜÔÙÒ²²»ÓÃ;½Ó×ÅÄãÓпÉÄܲéѯԱ¹¤’456’,È»ºó²éѯ’789’µÈµÈ;Èç¹û²éѯʹÓÃÏóµÚÒ»¸ö²éѯÓï
¾ä,Äãÿ´Î²éѯ¶¼ÊÇÒ»¸öеIJéѯ(ÎÒÃǽÐËüÓ²±àÂëµÄ²éѯ·½·¨);Òò´Ë,Oracleÿ´Î±ØÐë·ÖÎö,½âÎö,°²È«¼ì²é, ÓÅ»¯µÈµÈ;ITPUB¸öÈË¿Õ¼ä3U%^,cD2Mm�XD
]
µÚ¶þ¸ö²éѯÓï¾äÌṩÁ˰󶨱äÁ¿:empno,ËüµÄÖµÔÚ²éѯִÐÐʱÌṩ,²éѯ¾¹ýÒ»´Î±àÒëºó,²éѯ·½°¸´æ´¢ÔÚ¹²Ïí³ØÖÐ,¿ÉÒÔÓÃÀ´¼ìË÷ºÍÖØÓÃ;ÔÚÐÔÄܺÍÉìËõÐÔ·½Ãæ,ÕâÁ½ÕߵIJîÒìÊǾ޴óµÄ,ÉõÖÁÊǾªÈ˵Ä;ͨË׵㽲,¾Í²»ÊÇÒ»¸ö¼¶±ð;
T.sEeLPd0
µÚ
Ò»¸ö²éѯʹÓõÄƵÂÊÔ½¸ß,ËùÏûºÄµÄϵͳӲ¼þ×ÊÔ´Ô½´ó,´Ó¶ø½µµÍÁËÓû§µÄʹÓÃÊýÁ¿;ËüÒ²»á°ÑÓÅ»¯ºÃµÄÆäËü²éѯÓï¾ä´Ó¹²Ïí³ØÖÐÌß³ö;¾ÍÏóÒ»¸öÀÏÊó»µÁËÒ»¹øÌÀËÆ
µÄ,ϵͳµÄÕûÌåÐÔÄܽµµÍ;
¶øÖ´Ðа󶨱äÁ¿,Ìá½»Ïàͬ¶ÔÏóµÄÍêÈ«ÏàͬµÄ²éѯµÄÓû§(Õâ¾ä»°,´ó¼ÒÌýÆðÀ´±È½ÏÄÑÀí½â,ËæºóÎÒ»á¸ø³öÏêϸµÄ½âÊÍ),Ò»´ÎÐÔʹÓþͿÉÖØ
×î½üʼÖÕ±»Êý¾Ý¿âÎÊÌâÀ§ÈÅ£¬³öÓÚÏîÄ¿Ó¦ÓÃÔÒò£¬¶¨ÒåÁËһЩoracle×Ô¶¨Ò庯Êý(User Defined function),ÓÃÓÚÔÚʵ¼Ê²éѯSQLÓï¾äÖе÷Óã¬Ä¿µÄÊÇΪÁ˼ò»¯SQLÓï¾äµÄ¸´ÔӶȡ£Êµ¼ÊʹÓÃÖз¢ÏÖ£¬ÕâЩº¯Êý»á³öÏÖÒ»¸öĪÃûÆæÃîµÄÎÊÌ⣬ÏÈÉÏʵÀý:
CREATE OR REPLACE FUNCTION "GETSINGLEUSER" --·µ»ØµÚÒ»¸ö×Ö·û´®
(sStr in nvarchar2)
r ......