Oracle ³£Óú¯Êý
001¡¢×Ö·û
length/lengthb ×Ö·ûÊý(1¸öºº×Ö1¸ö×Ö·û) / ×Ö½ÚÊý(1¸öºº×Ö2¸ö×Ö½Ú)
ltrim/rtrim/trim ɾ³ý¿Õ¸ñ
lower/upper ´óСдת»»
select length('abc') from dual;
select substr(ename, 1, 3) from emp; ´ÓµÚÒ»¸ö×Ö·û¿ªÊ¼½Ø£¬Ò»¹²½Ø3¸ö×Ö·û
substr('abcdefg',2,3) => bcd ´ÓµÚ2¸öλÖÃÈ¡3¸ö
substr(str,length(str)-n+1,n); ÓÒÈ¡´®
select chr(65) from dual; //ASCIIÂëת»»³É×Ö·û
select ascii('A') from dual; //×Ö·ûת»»³ÉASCIIÂë
002¡¢ÈÕÆÚ
select sysdate from dual;
select current_date from dual;
select next_day(sysdate,'ÐÇÆÚÒ»') from dual; ÏÂÒ»¸öÐÇÆÚÒ»ÊÇÄÄÒ»Ìì
alter SESSION SET NLS_DATE_FORMAT='yyyy-mm-dd hh24:mi:ss';
003¡¢to_char
ÊýÖµ¸ñʽ
9´ú±íһλÊý×Ö£¬Ã»ÓоͲ»ÏÔʾ£¬Ð¡ÊýµãºóµÄÊý×Ö±ØÐëÏÔʾ
select to_char(sal, '$99,999.9999') from emp;
0´ú±íһλÊý×Ö£¬¸ÃλûÓÐÊý×Ö£¬ÔòÏÔʾ0
select to_char(sal, '$00,000.0000') from emp;
»õ±Ò¸ñʽ£¬LÖ¸±¾µØ»õ±Ò
select to_char(sal, 'L99,999.9999') from emp;
ÈÕÆÚ¸ñʽ
select to_char(hiredate, 'yyyy-mm-dd hh:mi:ss') from emp;
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
select to_char(sysdate,'yyyy-mom-dd hh:mi:ss') from dual;
004¡¢to_date
select to_date('2008-12ÔÂ-19') from dual;
select to_date('2008-12-19','yyyy-mm-dd') from dual;
Êä³ö1981Äê2ÔÂ20ÈÕºóÈëÖ°µÄÊý¾Ý
select ename, hiredate from emp where hiredate > to_date('1981-2-20', 'yyyy-mm-dd');
005¡¢to_number
select to_number('2008') from dual;
нˮ´óÓÚ$1,250.00µÄÔ±¹¤
select sal from emp where sal > to_number('$1,250.00', '$9,999.99');
006¡¢round
ËÄÉáÎåÈë
select round(23.6552) from dual;
ËÄÉáÎåÈ뵽СÊýµãºó2λ
select round(23.6552, 2) from dual;
ËÄÉáÎåÈ뵽ʮλÊý£º20
select round(23.6552, -1) from dual;
007¡¢nvl
Èç¹ûcommµÄÖµÊÇ¿ÕÖµÓÃ0Ìæ´ú£¬·ñÔòÖ±½ÓÊä³ö
select ename, sal*12 + nvl(comm, 0) from emp;
008¡¢||
Á¬½Ó×Ö·û´®
select 'Oracle '||'10g'||'R2' from dual;
009¡¢decode
²éѯÐÕ±ðΪ'ÄÐ'ºÍ'Å®'µÄ·Ö±ðÓжàÉÙÈË£º
select sum(decode(sex,'ÄÐ',1,0)) man,sum(decode(sex,'Å®',1,0)) woman from staff;
010¡¢¾Û¼¯º¯Êý
sum,avg,
Ïà¹ØÎĵµ£º
½ñÌì×öÁË1¸öÊý¾Ýµ¼ÈëÐèÇó£¬Îı¾¼Ç¼ÓÐ12Íò¶àÌõ£¬TXTÎļþ´óС6M¶à£¬ÒòΪÒÔÇ°¶¼ÊÇÓÃoracleµÄtext import·½Ê½µ¼È룬¸ÕÊÔÑéÁËһϣ¬²»ÁéÑ飬µ±µ¼Èëµ½2Íò¶àÌõµÄʱºò£¬PLSQL¾Íµ±µôÁË£¬×ß²»¶¯ÁË¡£
ÒòΪ¿¼Âǵ½1´Îµ¼È룬ËùÓоö¶¨²ÉÓÃSQLLOADERµÄ·½Ê½¡£ ......
ÕâÒ»ÕÂÖУ¬ÎÒÃǽ«·ÖÎö¹¹³ÉÊý¾Ý¿âºÍʵÀýµÄ8 ÖÖÎļþÀàÐÍ¡£
ÓëʵÀýÏà¹ØµÄÎļþÖ»ÓУº
²ÎÊýÎļþ£¨parameter file£©£ºÕâЩÎļþ¸æËßOracle ʵÀýÔÚÄÄÀï¿ÉÒÔÕÒµ½¿ØÖÆÎļþ£¬²¢ÇÒÖ¸
¶¨Ä³Ð©³õʼ»¯²ÎÊý£¬ÕâЩ²ÎÊý¶¨ÒåÁËijÖÖÄÚ´æ½á¹¹Óжà´óµÈÉèÖá£ÎÒÃÇ»¹»á½éÉÜ´æ´¢Êý¾Ý¿â²Î
ÊýÎļþµÄÁ½ÖÖÑ¡Ôñ¡£
¸ú×ÙÎļþ£¨trace file£ ......
Oracle 3 ¸öÖ÷ÒªµÄÄÚ´æ½á¹¹£º
ϵͳȫ¾ÖÇø£¨System Global Area£¬SGA£©£ºÕâÊÇÒ»¸öºÜ´óµÄ¹²ÏíÄÚ´æ¶Î£¬¼¸ºõËùÓÐOracle
½ø³Ì¶¼Òª·ÃÎÊÕâ¸öÇøÖеÄijһµã¡£
½ø³ÌÈ«¾ÖÇø£¨Process Global Area£¬PGA£©£ºÕâÊÇÒ»¸ö½ø³Ì»òÏß³ÌרÓõÄÄڴ棬ÆäËû½ø³Ì/
Ï̲߳»ÄÜ·ÃÎÊ¡£
Óû§È«¾ÖÇø£¨User Global Area£¬UGA£©£ºÕâ¸öÄÚ´æÇøÓë ......
--JOBS
DECLARE
X NUMBER;
BEGIN
SYS.DBMS_JOB.SUBMIT(job => X,
what => 'sp_test();',
next_date => to_date('28-04-2009 11:58:00',
'dd/mm/yyyy hh24:mi:ss'),
interval => 'SYSDATE+1');
END;
/
commit;
select to_char(sysdate-90,'yyyymmdd') from dual; --Ñ¡ÔñÈÕÆÚ
select * from ......
×î½üÔÚÏîÄ¿±¨±íÖÐÐèÒªÒ»¸ö²éѯÓï¾ä£¬ÓÃÀ´Í³¼Æ·ûºÏijһÁÐÌõ¼þµÄÆäËü¼¸ÁеĸöÊý
±ÈÈçÓÐÏÂÃæÒ»¸ö±í½á¹¹£º
ÐèÒªÔÚÁÐDºóÃæÔö¼ÓÒ»ÁУ¬Í³¼ÆÔÚÁÐA²»Îª¿Õ£¬²¢ÇÒÁÐB¡¢C¡¢D²»Îª¿ÕµÄ¸öÊý
¾¹ý×Ô¼ºÊÔÑ飬²éÕÒ°ïÖú£¬×ÜËãʵÏÖÁËÉÏÃæµÄ²éѯ¡£
&nb ......