ORALCE 数据库字符串处理、常用函数

1.字符串转日期：
to_date(paramStr,'YYYYMMDDHH24MISS')
to_date(paramStr,'yyyy-MM-DD')
to_date(paramStr,'yyyy/mm/dd hh24:mi:ss')
to_date(paramStr,'yyyy-MM-dd hh24:mi:ss')

2.日期转字符串
to_char(paramDate,'HH24:MI:SS')
to_char(paramDate,'yyyy-MM-DD HH24:MI:SS')
to_char(paramDate,'YYYY-MM-DD')
to_char(paramDate,'YYYYMMDD')

3.字符串函数
nvl(paramStr,specStr)--其中参数specStr 为指定要转成的具体字符或数字

4.字符串截取函数
substr(paramStr,beginIndex,len)--从指定索引位置beginIndex（不支持下标从0开始，不可缺省），截取指定长度len的字符串

5.左侧补充特定字符串函数
lpad(paramStr, padded_length, [ pad_string ])

6.支持字符串连接关键字 || （不允许使用+进行字符串连接）

7.select @allFeee=(select sum(CONVERT(numeric(19,4),price)) from (select price from queuedetail t where t.appointsid =@p_APPOINTSID) temp)

日期函数

SELECT ROUND(DATE'2005-5-10', 'MONTH'), 
ROUND(DATE'2005-6-16', 'MONTH') 
FROM dual; 
SELECT NEXT_DAY(SYSDATE,'星期二') FROM dual; 
SELECT TRUNC(SYSDATE, 'YEAR') FROM dual; 
SELECT TRUNC(SYSDATE,'DAY') FROM dual; 
SELECT TRUNC(SYSDATE) FROM dual; 
SELECT EXTRACT(YEAR FROM SYSDATE) FROM dual;
字符函数
select initcap('hello') from dual; 
select lower('FUN') from dual; 
select upper('sun') from dual; 
select ltrim( 'xyzadams','xyz') from dual; 
select rtrim('xyzadams','ams') from dual; 
select translate('jack','abcd' ,'1234') from dual; 
select replace('jack and jue' ,'j','bl') from dual; 
select instr ('worldwide','d') from dual; 
select substr('abcdefg',3,2) from dual; 
select concat ('Hello',' world') from dual; 
SELECT CHR(67) FROM dual; 
SELECT LPAD('function',15,'=') FROM dual;
SELECT TRIM (LEADING 9 FROM 99998769789999) FROM dual;
SELECT TRIM (TRAILING 9 FROM 99998769789999) FROM dual; 
SELECT TRIM (9 FROM 9999876789999) FROM dual; 
SELECT LENGTH('frances') FROM dual; 
SELECT orderno, DECODE(ostatus,'p','准备处理','c','已完成') 
FROM order_master;

数字函数
select abs(-15) from dual; 
select ceil(44.778) from dual; 
select sin(1.571) from dual; 
select cos(0) from dual; 
select sign(-32) from dual; 
select floor(100.2) from dual; 
select power(4,2) from dual; 
select mod(10,3) from dual; 
select round(100.256,2) from dual; 
select trunc(100.256,2) from dual; 
select sqrt(4) from dual;
SELECT TO_CHAR(sysdate,'YYYY"年"fmMM"月"fmDD"日" HH24:MI:SS') FROM dual; 
SELECT TO_CHAR(itemrate,'C99999') FROM itemfile; 
SELECT TO_DATE('2005-12-06' , 'yyyy-mm-dd') FROM dual; 
SELECT SQRT(TO_NUMBER('100')) FROM dual; 
SELECT itemdesc, NVL(re_level,0) FROM itemfile; 
SELECT itemdesc,NVL2(re_level,re_level,max_level) 
FROM itemfile; 
SELECT itemdesc,NULLIF(re_level,max_level) 
FROM itemfile;

分组函数
SELECT AVG (re_level) FROM itemfile 
WHERE p_category='accessories'; 
SELECT MIN(max_level) FROM itemfile 
WHERE p_category='spares'; 
SELECT SUM(itemrate*max_level) FROM itemfile; 
SELECT * FROM itemfile; 
SELECT COUNT(*) FROM itemfile; 
SELECT COUNT(itemrate) FROM itemfile; 
SELECT COUNT(DISTINCT p_category) FROM itemfile;
SELECT p_category,MAX(itemrate) FROM itemfile 
GROUP BY p_category; 
SELECT p_category,MAX(itemrate) FROM itemfile 
GROUP BY p_category 
HAVING p_category NOT IN ('accessories');
分析函数
SELECT ename, job, deptno, sal, ROW_NUMBER() OVER 
(ORDER BY sal DESC) AS SAL_RANK 
FROM SCOTT.EMP; 
SELECT ename, job, deptno, sal, ROW_NUMBER() OVER 
(PARTITION BY deptno ORDER BY sal DESC) AS SAL_RANK 
FROM SCOTT.EMP; 
SELECT ename, sal, comm, deptno, 
RANK() OVER (PARTITION BY deptno 
ORDER BY sal DESC, comm) RANK 
FROM scott.emp; 
SELECT d.dname, e.ename, e.sal, DENSE_RANK()
OVER (PARTITION BY e.deptno ORDER BY e.sal DESC) DENSERANK 
FROM emp e, dept d 
WHERE e.deptno = d.deptno;