ORACLE·ÖÎöº¯ÊýµÄÓ÷¨
¿ª´°º¯ÊýµÄµÄÀí½â£º
¿ª´°º¯ÊýÖ¸¶¨ÁË·ÖÎöº¯Êý¹¤×÷µÄÊý¾Ý´°¿Ú´óС£¬Õâ¸öÊý¾Ý´°¿Ú´óС¿ÉÄÜ»áËæ×ÅÐеı仯¶ø±ä»¯£¬¾ÙÀýÈçÏ£º
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 and unbounded following£©
Ö÷Òª²Î¿¼×ÊÁÏ£º¡¶expert one-on-one¡· Tom Kyte ¡¶Oracle9i SQL Reference¡·µÚ6ÕÂ
AVG
¹¦ÄÜÃèÊö£ºÓÃÓÚ¼ÆËãÒ»¸ö×éºÍÊý¾Ý´°¿ÚÄÚ±í´ïʽµÄƽ¾ùÖµ¡£
SAMPLE£ºÏÂÃæµÄÀý×ÓÖÐÁÐc_mavg¼ÆËãÔ±¹¤±íÖÐÿ¸öÔ±¹¤µÄƽ¾ùнˮ±¨¸æ£¬¸Ãƽ¾ùÖµÓɵ±Ç°Ô±¹¤ºÍÓëÖ®¾ßÓÐÏàͬ¾ÀíµÄÇ°Ò»¸öºÍºóÒ»¸öÈýÕßµÄƽ¾ùÊýµÃÀ´£»
SELECT manager_id, last_name, hire_date, salary,
AVG(salary) OVER (PARTITION BY manager_id ORDER BY hire_date
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS c_mavg
from employees;
MANAGER
Ïà¹ØÎĵµ£º
ѧϰOracle DBAÒ²°ë¸ö¶àѧÆÚÁË£¬½ñÌìÃÍÈ»²Å·¢ÏÖ£¬ÔÀ´ÎÒµÄÊ黹ÊǺÜеģ¬ÉϿβÙ×÷ʱºòÒ²Ö»ÊÇÖªµÀ´ó¸ÅÔõô×ö£¬µ«ÊÇÒªÕæµÄÈ«²¿×Ô¼º×ö£¬¶ø²»È¥·Ê黹ÊÇÓÐÒ»¶¨µÄÄѶȵģ¬ËùÒÔÄØ£¬½ñÌ쿪ʼ½«DBA´ÓÍ·¸´Ï°Ò»±é£¬Í¬Ê±ÔÙ²Ù×÷Ò»±é¡£
µÚÒ»Õ£¬Ñ§µÄÊÇOracleµÄÌåϵ½á¹¹£ ......
OracleÒì³£·ÖΪ3ÖÖ:
(1)Ô¤¶¨ÒåÒì³£:no_data_foundµÈ,ÊÇOracleϵͳ¶¨ÒåµÄÒì³£.
declare
s_test varchar2
begin
select id into s_test from test; --´Ëʱtest±íÎÞÊý¾Ý
exception
when no_data_found then
raise_application_error(-20001, 'ûÓÐÊý¾Ý');
end;
(2)×Ô¶ ......
×öÊý¾Ý¿â¿ª·¢»ò¹ÜÀíµÄÈ˾³£Òª´´½¨´óÁ¿µÄ²âÊÔÊý¾Ý£¬¶¯²»¶¯¾ÍÐèÒªÉÏÍòÌõ£¬Èç¹ûÒ»ÌõÒ»ÌõµÄ¼È룬ÄÇ»áÀË·Ñ´óÁ¿µÄʱ¼ä£¬±¾ÎĽéÉÜÁËOracleÖÐÈçºÎͨ¹ýÒ»ÌõSQL¿ìËÙÉú³É´óÁ¿µÄ²âÊÔÊý¾ÝµÄ·½·¨¡£
²úÉú²âÊÔÊý¾ÝµÄSQLÈçÏ£º
SQL> select rownum as id,
2 &nbs ......
ÔÚ¿ª·¢Ó¦ÓõÄʱºò£¬°ÑÊý¾Ý°´ÕÕÒ»¶¨µÄ¹æÔòÅÅÐòºóÔÙÈ¡Ç°¼¸ÌõÊý¾ÝÕâÖÖ²Ù×÷ÊǺÜƽ³£µÄÊÂÇé¡£ÎÒÃÇÔÚOracleÖг£ÓõľÍÊÇorder by£¬È»ºóÈ¡µÃrownumСÓÚ¶àÉÙµÄÊý¾ÝÕâÖÖ·½·¨¡£²»¹ýÈç¹û¶ÔOracle²»ÊìϤ£¬Ò²Ðí¾Í»á·¢ÏÖÄãдµÄSQLÓï¾ä¼ìË÷³öÀ´µÄÖµ²»ÕýÈ·£¬Õâ¸öÊÇΪʲôÄØ¡£ ÒòΪOracleÔÚ¼ìË÷µÄʱºò£¬»áÊ×ÏÈ°ÑÊý¾Ý¶¼¼ìË÷³öÀ´£¬È»ºóÔÚÅÅÐò¶Î ......
ORACLEµÄlpadº¯Êý¡¢rpadº¯ÊýÓ÷¨
Óï·¨£ºselect lpad('12345678',1,'0') from dual;
·µ»Ø×Ö·û´®‘12345678’µÄ´Ó×ó±ß³¤¶ÈΪ1µÄ×Ö·û´®£¬Èç¹û²»¹»£¬×ó±ß²¹‘0’
Óï·¨£ºselect rpad('12345678',1,'0') from dual;
·µ»Ø×Ö·û´®‘12345678’µÄ´Ó×ó±ß³¤¶ÈΪ1µÄ×Ö·û´®£¬Èç¹û²»¹»£¬Óұ߲¹&ls ......