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

Oracleº¯Êý

º¯Êý:
 ×Ö·ûº¯Êý 
  ×ª»¯³ÉСдLOWER(<C>)  ת»¯³É´óдUPPER(<C>) select lower('aAbBcC') from dual;
 
 --------
 ÈÕÆÚº¯Êý
  add_months(D,<I>)·µ»ØÈÕÆÚD¼ÓÉÏi¸öÔºóµÄ½á¹û
   select add_month(sysdate,3)from dual;
  last_day(D)º¯Êý·µ»Ø°üº¬ÈÕÆÚDµÄÔ·ݵÄ×îºóÒ»Ìì
   select last_day(sysdate)from dual;
  .........
 -------
 ·Ö×麯Êý
  AVG()·µ»ØÆ½¾ùÖµ
  count(*)·µ»Ø²éѯÖÐÐеÄÊýÄ¿ 
  max()·µ»ØÑ¡ÔñÁбíÏîÄ¿µÄ×î´óÖµ
  min()·µ»ØÑ¡ÔñÁбíÏîÄ¿µÄ×îСֵ
  Select avg(sal),avg(distinct sal),max(sal),min(sal),
  sum(sal),count(*),count(sal),count(distinct sal),
  count(distinct comm),count(comm)
  from emp where deptno=30;
  --µ¥ÁзÖ×é
  --ÏÔʾÿ¸ö²¿Ãŵį½¾ù¹¤×ʺÍ×î¸ß¹¤×Ê
  Select deptno,avg(sal),max(sal) from emp
  group by deptno;
  --¶àÁзÖ×é
  --ÏÔʾÿ¸ö²¿ÃÅ¡¢Ã¿ÖÖ¸ÚλµÄƽ¾ù¹¤×ʺÍ×î¸ß¹¤×Ê
  Select deptno,job,avg(sal),max(sal) from emp
  group by deptno,job;
  having×Ó¾ä
  --ÏÔʾƽ¾ù¹¤×ʸßÓÚ2000ÔªµÄ²¿ÃűàºÅ¡¢Æ½¾ù¹¤×ʺÍ×î¸ß¹¤×Ê
  select deptno,avg(sal),max(sal) from emp group by deptno having avg(sal)>2000;
  --×¢Òâ·Ö×麯Êý²»ÄܳöÏÖÔÚwhere×Ó¾äÖÐ
  select deptno,avg(sal),max(sal) from emp  where avg(sal)>2000 group by deptno;
 -----
 ÅÅÐòº¯Êý
  row_numberº¯Êý(˳ÐòÅÅÐòº¯Êý)
  rankº¯Êý(ÅÅÐò´æÔÚÏàֵͬ¸ø³öÏàͬÐòºÅ£¬Í¬Ê±¿Õ³öÔ¤ÁôÐòºÅ)
  dense_rankº¯Êý(ÅÅÐò´æÔÚÏàֵͬ¸ø³öÏàͬÐòºÅ£¬µ«²»¿Õ³öÔ¤ÁôÐòºÅ)
 ------
 ´°¿Úº¯Êý
  over(partition by ·Ö×é×Ö¶ÎÃû order by ÅÅÐò×Ö¶ÎÃû |[desc])
  over(partition by deptno order by sal )°´²¿ÃÅ·Ö×é¶Ô¹¤×ʽøÐÐÅÅÐò


Ïà¹ØÎĵµ£º

Oracle·ÖÇø(Partition)

ÏîÄ¿ÖÐÓõ½µÄpartition£¬×ܽáÕûÀíÒ»ÏÂÏà¹ØÖªÊ¶¡££¨ÍøÉϲɼ¯£¬ÈçÓÐÇÖȨ£¬Ç뼰ʱÁªÏµ¡££©
 
Ò»¡¢Oracle·ÖÇø¼ò½é
ORACLEµÄ·ÖÇøÊÇÒ»ÖÖ´¦Àí³¬´óÐÍ±í¡¢Ë÷ÒýµÈµÄ¼¼Êõ¡£·ÖÇøÊÇÒ»ÖÖ“·Ö¶øÖÎÖ®”µÄ¼¼Êõ£¬Í¨¹ý½«´ó±íºÍË÷Òý·Ö³É¿ÉÒÔ¹ÜÀíµÄС¿é£¬´Ó¶ø±ÜÃâÁ˶Ôÿ¸ö±í×÷Ϊһ¸ö´óµÄ¡¢µ¥¶ÀµÄ¶ÔÏó½øÐйÜÀí£¬Îª´óÁ¿Êý¾ÝÌṩ ......

oracleÖÐÈ¥ÖØ¸´¼Ç¼,²»ÓÃdistinct

ÓÃdistinct¹Ø¼ü×ÖÖ»ÄܹýÂ˲éѯ×Ö¶ÎÖÐËùÓмǼÏàͬµÄ£¨¼Ç¼¼¯Ïàͬ£©£¬¶øÈç¹ûÒªÖ¸¶¨Ò»¸ö×Ö¶ÎȴûÓÐЧ¹û£¬ÁíÍâdistinct¹Ø¼ü×Ö»áÅÅÐò£¬Ð§Âʺܵ͡£
select distinct name from t1 ÄÜÏû³ýÖØ¸´¼Ç¼£¬µ«Ö»ÄÜȡһ¸ö×ֶΣ¬ÏÖÔÚҪͬʱȡid,nameÕâ2¸ö×ֶεÄÖµ¡£
select distinct id,name from t1 ¿ÉÒÔÈ¡¶à¸ö×ֶΣ¬µ«Ö»ÄÜÏû³ýÕâ2¸ö×Ö¶ ......

oracle ´æ´¢¹ý³ÌµÄ»ù±¾Óï·¨

1.»ù±¾½á¹¹
CREATE OR REPLACE PROCEDURE ´æ´¢¹ý³ÌÃû×Ö
(
    ²ÎÊý1 IN NUMBER,
    ²ÎÊý2 IN NUMBER
) IS
±äÁ¿1 INTEGER :=0;
±äÁ¿2 DATE;
BEGIN

END ´æ´¢¹ý³ÌÃû×Ö
2.SELECT INTO STATEMENT
  ½«select²éѯµÄ½á¹û´æÈëµ½±äÁ¿ÖУ¬¿ÉÒÔͬʱ½«¶à¸öÁд洢¶à¸ö±äÁ¿ÖУ¬±ØÐëÓÐ ......

Oracle»ù´¡

---------------oracle ÈëÃÅ
 ´ò¿ª·þÎñÆ÷
 net start oracleserviceORCL
 ´ò¿ª¼àÌýÆ÷
 lsnrctl start
 ¹Ø±Õ·þÎñÆ÷
 net stop oracleserviceORCL
 ¹Ø±Õ¼àÌýÆ÷
 lsnrctl stop
-----------------------------
 1.´´½¨±í
 Create table ±íÃû
 (×Ö¶ÎÃû Êý¾Ý ......
© 2009 ej38.com All Rights Reserved. ¹ØÓÚE½¡ÍøÁªÏµÎÒÃÇ | Õ¾µãµØÍ¼ | ¸ÓICP±¸09004571ºÅ