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 )°´²¿ÃÅ·Ö×é¶Ô¹¤×ʽøÐÐÅÅÐò
Ïà¹ØÎĵµ£º
ÊÕ²Ø×ÔijЩ¸ßÊÖµÄ×ܽᣬ²»¸Ò¶ÀÏí£¬ÓëÓÐÔµÀ´´ËÕß¹²Ïí¡£
-----------------------------------------------------------------------------------
Oracle×Ö·û¼¯ÎÊÌâ×ܽá
¾³£ÓÐͬÊÂ×ÉѯoracleÊý¾Ý¿â×Ö·û¼¯Ïà¹ØµÄÎÊÌ⣬ÈçÔÚ²»Í¬Êý¾Ý¿â×öÊý¾ÝǨÒÆ¡¢Í¬ÆäËüϵͳ½»»»Êý¾ÝµÈ£¬³£³£ÒòΪ×Ö·û¼¯²»Í¬¶øµ¼ÖÂǨÒÆʧ°Ü»òÊý¾Ý¿âÄÚÊý¾Ý±ä³ ......
ORACLE º¯Êý¿ª·¢
1¡¢½¨Á¢º¯ÊýµÄÓï·¨£º
CREATE [OR REPLACE] FUNCTION º¯ÊýÃû
[ (arg1 [mode] datatype[,........])] --µ±Ö¸¶¨²ÎÊýÊý¾ÝÀàÐÍʱ£¬²»ÄÜÖ¸¶¨Æ䳤¶È¡£modeÖµ£ºIN¡¢OUT¡¢IN OUT£¬µ±mode δָ¶¨Ê±£¬±íʾΪIN
RETURN datatype ......
ºÃ³¤Ê±¼äû䶫¶«ÁË£¬½ñÌìдÁËÒ»¸öoracleµÄ·ÖÒ³´æ´¢¹ý³Ì£¬Ìù³öÀ´Óë´ó¼Ò·ÖÏí¡£
ÓÉÓÚ¡£net¿ò¼ÜÖеÄgridviewµÈ¿Ø¼þµÄ·ÖÒ³¹¦ÄÜÌ«Èõ£¬ËùÒԴ󲿷ֳÌÐòÔ±¶¼»á×Ô¼ºÐ´·ÖÒ³¹¦ÄÜ£¬¶øµ÷Ó÷ÖÒ³´æ´¢¹ý³ÌÊDZØÐëµÄ¡£
ǰ̨aspxÒ³Ãæ²»¹ÜÊÇÓÃÏÂÔصÄdll»¹ÊÇÓÃ×Ô¼ºÐ´µÄÓû§¿Ø¼þ£¬¶¼ÄÜʵÏÖ×Ô¼ºµÄ¹¦ÄÜ£¬È»¶ø·ÖÒ³µÄ´æ´¢¹ý³Ì¾ÍÐèÒªºÃºÃµÄ¿¼ÂÇÁË£¬Ï ......
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 ÈëÃÅ
´ò¿ª·þÎñÆ÷
net start oracleserviceORCL
´ò¿ª¼àÌýÆ÷
lsnrctl start
¹Ø±Õ·þÎñÆ÷
net stop oracleserviceORCL
¹Ø±Õ¼àÌýÆ÷
lsnrctl stop
-----------------------------
1.´´½¨±í
Create table ±íÃû
(×Ö¶ÎÃû Êý¾Ý ......