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
Ïà¹ØÎĵµ£º
Ò»¡¢Ñ¡ÔñÌâ
¡¡¡¡
¡¡¡¡1.µ±ÄãÖ´ÐÐÒÔϲéѯÓï¾ä:
¡¡¡¡SELECT empno,ename
¡¡¡¡from emp
¡¡¡¡WHERE empno =7782 OR empno =7876;
¡¡¡¡ÔÚWHEREÓï¾äÖУ¬ÒÔÏÂÄĸö²Ù×÷·û¿ÉÒÔÈ¡´úOR£¿
¡¡¡¡A. IN
¡¡¡¡B. BETWEEN ……
¡¡¡¡C. LIKE
¡¡¡¡D. <=
¡¡¡¡E. >=
¡¡¡¡
¡¡¡¡2. ÄĸöʵÏÖ  ......
INSERT /*+APPEND */ INTO employee (empno,ename,sal,deptno)
SELECT empno,ename,sal,deptno from emp
WHERE deptno=20;
µÚ¶þÌõÓï¾äʹÓÃ/*+APPEND */À´±íʾ²ÉÓÃÖ±½Ó×°ÔØ·½Ê½£¬µ±Òª×°ÔØ´óÅúÁ¿Êý¾Ýʱ£¬²ÉÓõڶþÖÖ·½·¨×°ÔØÊý¾ÝµÄËÙ¶ÈÒªÔ¶Ô¶ÓÅÓÚµÚÒ»ÖÖ·½·¨¡£
ʹÓöà±í²åÈëÊý¾Ý
ʹÓÃALL²Ù×÷·ûÖ´Ðжà±í²åÈëʱ£¬ÔÚÿ¸öÌõ¼þ× ......
ѧϰOracleÊÇÒ»¸öÂþ³¤¼è
ÐÁµÄ¹ý³Ì¡£Èç¹ûûÓÐÐËȤ£¬Ö»ÊDZ»ÆÈѧϰ£¬ÄÇôÊǺÜÄÑѧºÃµÄ¡£Ñ§Ï°µ½Ò»¶¨³Ì¶ÈµÄʱºò£¬ÒªÏë½øÒ»²½Ìá¸ß£¬¾Í²»µÃ²»½Ó´¥ºÜ¶àOracleÖ®ÍâµÄ¶«Î÷£¬Èç
Unix£¬ÈçÍøÂç¡¢´æ´¢µÈ¡£Òò´Ë£¬ÒªÕæµÄ¾öÐÄѧºÃOracle£¬¾ÍÒ»¶¨ÒªÓÐÐËȤ¡£ÓÐÁËÐËȤ£¬¾Í»áÒ»ÇбäµÃ¼òµ¥¿ìÀÖÆðÀ´¡£¼òµ¥×ܽáһϣ¬ÄǾÍÊÇ£ºÐËȤ¡¢Ñ§
ϰ¡¢Êµ¼ù¡£ ......
create table emp2_log(
uname varchar2(20),
action varchar(10),
atime date
);
create or replace trigger trig
after insert or delete or update on emp2 for each row //°óÔÚÒ»ÕűíÉÏ,before after ¶¼¿É
ÒÔ,beforeÊÇ ²åÊý¾Ý֮ǰ£¬afterÊÇÖ®ºó
begin
&nb ......