Ò׽ؽØÍ¼Èí¼þ¡¢µ¥Îļþ¡¢Ãâ°²×°¡¢´¿ÂÌÉ«¡¢½ö160KB

Oracle ·ÖÎöº¯Êý


   ·ÖÎöº¯ÊýÊÇoracle816ÒýÈëµÄÒ»¸öȫеĸÅÄî,ΪÎÒÃÇ·ÖÎöÊý¾ÝÌṩÁËÒ»ÖÖ¼òµ¥¸ßЧµÄ´¦Àí·½Ê½.ÔÚ·ÖÎöº¯Êý³öÏÖÒÔǰ,ÎÒÃDZØÐëʹÓÃ×ÔÁª²éѯ,×Ó²éѯ»òÕßÄÚÁªÊÓͼ,ÉõÖÁ¸´ÔӵĴ洢¹ý³ÌʵÏÖµÄÓï¾ä,ÏÖÔÚÖ»ÒªÒ»Ìõ¼òµ¥µÄsqlÓï¾ä¾Í¿ÉÒÔʵÏÖÁË,¶øÇÒÔÚÖ´ÐÐЧÂÊ·½ÃæÒ²ÓÐÏ൱´óµÄÌá¸ß.
ÏÂÃæÖ÷Òª½éÉÜÒ»ÏÂÒÔϼ¸¸öº¯ÊýµÄʹÓ÷½·¨
1.   Over() ¿ª´°º¯Êý
2.    Nvl()º¯Êý
3.  Rollup,Cube×Ô¶¯»ã×ܺ¯Êý
4.  Rank,Dense_rank,Row_numberº¯Êý
5.  Lag , Leadº¯Êý
6.  Sum,Avg, Count, Maxº¯Êý
7.  Ratio_to_report±¨±í´¦Àíº¯Êý
8.  First,Last£¬First_value£¬Last_valueÈ¡»ùÊýµÄ·ÖÎöº¯Êý
9.   Greatest, Least º¯Êý
10.  Trunc, Round,Decode, Substrº¯Êý
Ò»£® Over() ¿ª´°º¯Êý
Over() ¿ª´°º¯ÊýÊÇOracleµÄ·ÖÎöº¯Êý£¬ÆäÓï·¨ÈçÏ£º
º¯ÊýÃû([ ²ÎÊý ]) over( [ ·ÖÇø×Ó¾ä ] [ ÅÅÐò×Ó¾ä [ »¬¶¯´°¿Ú×Ó¾ä ] ])
·ÖÇø×Ó¾äÀàËÆÓÚ¾Û×麯ÊýËùÐèÒªµÄgroup by£¬ÅÅÐò×Ó¾ä¿É¿´³ÉÊÇSQLÓï¾äÖеÄorder by£¬Ö»²»¹ýÔÚ´ËÓï¾äÖл¹¿ÉÖ¸¶¨nullÖµÅÅǰ£¨nulls first£©»¹ÊÇÅźó£¨nulls last£©¡£
¿ª´°º¯ÊýÖ¸¶¨ÁË·ÖÎöº¯Êý¹¤×÷µÄÊý¾Ý´°¿Ú´óС£¬Õâ¸öÊý¾Ý´°¿Ú´óС¿ÉÄÜ»áËæ×ÅÐеı仯¶ø±ä»¯£¬¾ÙÀýÈçÏ£º
over£¨order by salary£© °´ÕÕsalaryÅÅÐò½øÐÐÀۼƣ¬order byÊǸöĬÈϵĿª´°º¯Êý
over£¨partition by deptno£©°´ÕÕ²¿ÃÅ·ÖÇø
over£¨order by salary range between 50 preceding and 150 following£©
ÿÐжÔÓ¦µÄÊý¾Ý´°¿ÚÊÇ֮ǰÐзù¶ÈÖµ²»³¬¹ý50£¬Ö®ºóÐзù¶ÈÖµ²»³¬¹ý150
over£¨order by salary rows between 50 preceding and 150 following£©
ÿÐжÔÓ¦µÄÊý¾Ý´°¿ÚÊÇ֮ǰ50ÐУ¬Ö®ºó150ÐÐ
over£¨order by salary rows between unbounded preceding and unbounded following£©
ÿÐжÔÓ¦µÄÊý¾Ý´°¿ÚÊÇ´ÓµÚÒ»Ðе½×îºóÒ»ÐУ¬µÈЧ£º
over£¨order by salary range between unbounded preceding&n


Ïà¹ØÎĵµ£º

SQLÓÅ»¯¹ý³ÌÖг£¼ûOracle HINTµÄÓ÷¨

 ÔÚSQLÓï¾äÓÅ»¯¹ý³ÌÖУ¬ÎÒÃǾ­³£»áÓõ½hint,ÏÖ×ܽáÒ»ÏÂÔÚSQLÓÅ»¯¹ý³ÌÖг£¼ûOracle HINTµÄÓ÷¨£º
1. /*+ALL_ROWS*/
±íÃ÷¶ÔÓï¾ä¿éÑ¡Ôñ»ùÓÚ¿ªÏúµÄÓÅ»¯·½·¨,²¢»ñµÃ×î¼ÑÍÌÍÂÁ¿,ʹ×ÊÔ´ÏûºÄ×îС»¯.
ÀýÈç:
SELECT /*+ALL+_ROWS*/ EMP_NO,EMP_NAM,DAT_IN from BSEMPMS WHERE EMP_NO='SCOTT';
2. /*+FIRST_ROWS*/
±í ......

OracleÓû§¹ÜÀí³£ÓòÙ×÷

 
ÒÔÏÂÁгöµÄÊÇOracle
Óû§¹ÜÀí¹ý³ÌÖг£ÓõÄһЩָÁÒÔ¹©´ó¼Ò²Î¿¼¡£
Oracle
Óû§¹ÜÀíÖ®Ò»¡¢´´½¨profile
Îļþ¡£

1.          
SQL>Create
profile
ÎļþÃû limit
2.          
   ......

oracle ÐèҪעÒâµÄµØ·½

INSERT /*+APPEND */ INTO employee (empno,ename,sal,deptno)
SELECT empno,ename,sal,deptno from emp
WHERE deptno=20;
µÚ¶þÌõÓï¾äʹÓÃ/*+APPEND */À´±íʾ²ÉÓÃÖ±½Ó×°ÔØ·½Ê½£¬µ±Òª×°ÔØ´óÅúÁ¿Êý¾Ýʱ£¬²ÉÓõڶþÖÖ·½·¨×°ÔØÊý¾ÝµÄËÙ¶ÈÒªÔ¶Ô¶ÓÅÓÚµÚÒ»ÖÖ·½·¨¡£
ʹÓöà±í²åÈëÊý¾Ý
ʹÓÃALL²Ù×÷·ûÖ´Ðжà±í²åÈëʱ£¬ÔÚÿ¸öÌõ¼þ× ......

ORACLE º¯Êý¼¯½õ

Ò»¡¢ ³£ÓÃÈÕÆÚÊý¾Ý¸ñʽ
1.Y»òYY»òYYY ÄêµÄ×îºóһ룬Á½Î»»òÈýλ
SQL> Select to_char(sysdate,'Y') from dual;
TO_CHAR(SYSDATE,'Y')
--------------------
7
SQL> Select to_char(sysdate,'YY') from dual;
TO_CHAR(SYSDATE,'YY')
---------------------
07
SQL> Select to_char(sysdate,'YYY') from ......
© 2009 ej38.com All Rights Reserved. ¹ØÓÚE½¡ÍøÁªÏµÎÒÃÇ | Õ¾µãµØÍ¼ | ¸ÓICP±¸09004571ºÅ