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,
Ïà¹ØÎĵµ£º
Oracle´´½¨±íʱ£¬³£Óöµ½ÏÈɾ³ýºó´´½¨µÄÇé¿ö£¬¶øËüÓÖûÓÐdrop table... if existsÓï·¨¡£Îª´Ë¿ÉÒÔʹÓÃuser_objectsÊý¾Ý×ÖµäºÍ¶¯Ì¬sqlÓï¾äʵÏÖÀàËƵŦÄÜ£¬ÈçÏÂËùʾ£º
create or replace procedure proc_dropifexists(
p_table in varchar2
) is
v_count number(10);
begin
&nbs ......
1.ÔÚOracle ÖУ¬Äã»áÁ˽⵽£º
ÊÂÎñÊÇÿ¸öÊý¾Ý¿âµÄºËÐÄ£¬ËüÃÇÊÇ“ºÃ¶«Î÷”¡£
Ó¦¸ÃÑÓ³Ùµ½Êʵ±µÄʱ¿Ì²ÅÌá½»¡£²»ÒªÌ«¿ìÌá½»£¬ÒÔ±ÜÃâ¶Ôϵͳ´øÀ´Ñ¹Á¦¡£ÕâÊÇÒòΪ£¬Èç¹û
ÊÂÎñºÜ³¤»òºÜ´ó£¬Ò»°ã²»»á¶ÔϵͳÓÐѹÁ¦¡£ÏàÓ¦µÄÔÔòÊÇ£ºÔÚ±ØҪʱ²ÅÌá½»£¬µ«ÊÇ´ËÇ°²»ÒªÌá
½»¡£ÊÂÎñµÄ´óСֻӦ¸Ã¸ù¾ÝÒµÎñÂß¼À´¶¨¡£
Ö»ÒªÐèÒ ......
ORACLE
CREATE OR REPLACE FUNCTION SETSTATE(OLDVALUE VARCHAR2, POS NUMBER, SVALUE VARCHAR2)
RETURN VARCHAR2
IS
RETURN_VALUE VARCHAR2 (20);
LEN NUMBER(8);
I NUMBER(8);
TEMP_VALUE VARCHAR2(1);
BEGIN
LEN := LENGTH(OLDVALUE);
IF POS > ......
1.Çó²¿ÃÅÖÐÄÄЩÈËнˮ×î¸ß£º
select ename,sal
from emp join
(
select max(sal) max_sal, deptno
from emp
group by deptno
) t
on (emp.sal = t.max_sal and emp.deptno = t.deptno);
2.Çó²¿ÃÅƽ¾ùнˮµÄµÈ¼¶£º
select deptno, avg_sal, grade ......