ORACLE °ó¶¨±äÁ¿Ó÷¨×ܽá
֮ǰ¶ÔORACLEÖеıäÁ¿Ò»Ö±Ã»¸öÌ«Çå³þµÄÈÏʶ£¬±ÈÈç˵ʹÓ㺡¢&¡¢&&¡¢DEIFINE¡¢VARIABLE……µÈµÈ¡£½ñÌìÕýºÃÏÐÏÂÀ´£¬ÉÏÍøËÑÁËËÑÏà¹ØµÄÎÄÕ£¬»ã×ÜÁËһϣ¬ÌùÔÚÕâÀ·½±ãѧϰ¡£
==================================================================================
ÔÚoracle ÖУ¬¶ÔÓÚÒ»¸öÌá½»µÄsqlÓï¾ä,´æÔÚÁ½ÖÖ¿ÉÑ¡µÄ½âÎö¹ý³Ì, Ò»ÖÖ½Ð×öÓ²½âÎö,Ò»ÖÖ½Ð×öÈí½âÎö.
Ò»¸öÓ²½âÎöÐèÒª¾½âÎö,Öƶ¨Ö´Ðз¾¶,ÓÅ»¯·ÃÎʼƻ®µÈÐí¶àµÄ²½Öè.Ó²½âÊͲ»½ö½öºÄ·Ñ´óÁ¿µÄcpu£¬¸üÖØÒªµÄÊÇ»áÕ¼¾ÝÖØÒªµÄÃÇãÅ£¨latch£©×ÊÔ´£¬ÑÏÖصÄÓ°ÏìϵͳµÄ¹æÄ£µÄÀ©´ó£¨¼´ÏÞÖÆÁËϵͳµÄ²¢·¢ÐУ©£¬¶øÇÒÒýÆðµÄÎÊÌâ²»ÄÜͨ¹ýÔö¼ÓÄÚ´æÌõºÍcpuµÄÊýÁ¿À´½â¾ö¡£Ö®ËùÒÔÕâÑùÊÇÒòΪÃÅãÅÊÇΪÁË˳Ðò·ÃÎÊÒÔ¼°ÐÞ¸ÄһЩÄÚ´æÇøÓò¶øÉèÖõģ¬ÕâЩÄÚ´æÇøÓòÊDz»Äܱ»Í¬Ê±Ð޸ġ£µ±Ò»¸ösqlÓï¾äÌá½»ºó£¬oracle»áÊ×Ïȼì²éһϹ²Ïí»º³å³Ø£¨shared pool£©ÀïÓÐûÓÐÓëÖ®ÍêÈ«ÏàͬµÄÓï¾ä£¬Èç¹ûÓеĻ°Ö»ÐëÖ´ÐÐÈí·ÖÎö¼´¿É£¬·ñÔò¾ÍµÃ½øÐÐÓ²·ÖÎö¡£
¶øΨһʹµÃoracle Äܹ»Öظ´ÀûÓÃÖ´Ðмƻ®µÄ·½·¨¾ÍÊDzÉÓð󶨱äÁ¿¡£°ó¶¨±äÁ¿µÄʵÖʾÍÊÇÓÃÓÚÌæ´úsqlÓï¾äÖеij£Á¿µÄÌæ´ú±äÁ¿¡£°ó¶¨±äÁ¿Äܹ»Ê¹µÃÿ´ÎÌá½»µÄsqlÓï¾ä¶¼ÍêÈ«Ò»Ñù¡£
1.
sqlplusÖÐÈçºÎʹÓð󶨱äÁ¿£¬¿ÉÒÔͨ¹ývariableÀ´¶¨Òå
SQL> select * from tt where id=1;
ID NAME
---------- ----------------------------------------
1 test
SQL> select * from tt where id=2;
ID NAME
---------- ----------------------------------------
2 test
SQL> variable i number;
SQL> exec :i :=1;
PL/SQL ¹ý³ÌÒѳɹ¦Íê³É¡£
SQL> select *from tt where id=:i;
ID NAME
---------- ----------------------------------------
1 test
SQL> exec :i :=2;
PL/SQL ¹ý³ÌÒѳɹ¦Íê³É¡£
SQL> select *from tt where id=:i;
ID NAME
---------- ----------------------------------------
2 test
SQL> print i;
I
----------
2
SQL> select sql_text,parse_calls from v$sql where sql_text like 'select * from t
t where id=%';
SQL_TEXT PARSE_CALLS
------------------------------------------------------------ -----------
select * from tt where id=2 1
select * from tt where id=1 1
select * from tt where id=:i 2
SQL>
´ÓÉÏÃæÊÔÑé·¢Ïְ󶨱äÁ¿iµÄʹÓÃʹ²éѯid=1ºÍi
Ïà¹ØÎĵµ£º
˵µ½Èí½âÎö£¨soft prase£©ºÍÓ²½âÎö£¨hard prase£©£¬¾Í²»Äܲ»ËµÒ»ÏÂOracle¶ÔsqlµÄ´¦Àí¹ý³Ì¡£µ±Äã·¢³öÒ»ÌõsqlÓï¾ä½»¸¶Oracle£¬ÔÚÖ´ÐкͻñÈ¡½á¹ûÇ°£¬Oracle¶Ô´Ësql½«½øÐм¸¸ö²½ÖèµÄ´¦Àí¹ý³Ì£º
1¡¢Óï·¨¼ì²é£¨syntax check£©
¼ì²é´ËsqlµÄƴдÊÇ·ñÓï·¨¡£
2¡¢ÓïÒå¼ì²é£¨semantic check£©
ÖîÈç¼ì²ésqlÓï¾äÖеķÃÎÊ¶Ô ......
oracleÊý¾Ý¿âͬ²½¼¼Êõ
¸ß¼¶¸´ÖÆ
ʲôÊǸ´ÖÆ£¿¼òµ¥µØ˵¸´ÖƾÍÊÇÔÚÓÉÁ½¸ö»òÕ߶à¸öÊý¾Ý¿âϵͳ¹¹³ÉµÄÒ»¸ö·Ö²¼Ê½Êý¾Ý¿â»·¾³Öп½±´Êý¾ÝµÄ¹ý³Ì¡£
¸ß¼¶¸´ÖÆ£¬ÊÇÔÚ×é³É·Ö²¼Ê½Êý¾Ý¿âϵͳµÄ¶à¸öÊý¾Ý¿âÖи´ÖƺÍά»¤Êý¾Ý¿â¶ÔÏóµÄ¹ý³Ì¡£ Oracle ¸ß¼¶¸´ÖÆÔÊÐíÓ¦ÓóÌÐò¸üÐÂÊý¾Ý¿âµÄÈκθ±±¾ ......
¼ÇµÃÒÔÇ°ÔÚÂÛ̳Àï¿´µ½inthirtiesÓõ½¹ýWITH ASÕâ¸ö×ÖÑÛ£¬µ±Ê±Ã»ÌرðÔÚÒâ¡£½ñÌìÔÚÒ»¸öÌû×ÓÀïÓÖ¿´µ½ÓÐÈËÓÃÕâ¸ö£¬ËùÒÔ¾ÍÈ¥ÍøÉÏËÑÁËËÑÏà¹ØÄÚÈÝ£¬×Ô¼ºÐ¡ÊÔÁËÒ»°Ñ£¬Ð´ÏÂÀ´£¬·½±ãÒÔºóÍüÁ˵Ļ°Ñ§Ï°¡£
===================================================================================
ÏȾٸöÀý×Ó°É£º
ÓÐÁ½ÕÅ±í£¬·Ö±ðΪA¡¢B ......
to_date() function
1.ÈÕÆÚ¸ñʽ²ÎÊý º¬Òå˵Ã÷
D Ò»ÖÜÖеÄÐÇÆÚ¼¸
DAY ÌìµÄÃû×Ö£¬Ê¹ÓÿոñÌî³äµ½9¸ö×Ö·û
DD ÔÂÖеĵڼ¸Ìì
DDD ÄêÖеĵڼ¸Ìì
DY ÌìµÄ¼òдÃû
IW ISO±ê×¼µÄÄêÖеĵڼ¸ÖÜ
IYYY ISO±ê×¼µÄËÄλÄê·Ý
YYYY ËÄλÄê·Ý
YYY,YY,Y Äê·ÝµÄ×îºóÈýÎ ......