1.TRIM函数,去掉字段中的0,如下所示
select employee_id,to_char(trim(leading 0 from hire_date)) from employees where department_id=60;
select employee_id,to_char(trim(trailing 0 from hire_date)) from employees where department_id=60;
select employee_id,to_char(trim(both 0 from hire_date)) from employees where department_id=60;
2.SUBSTR函数,截取字符串
SQL> SELECT SUBSTR('ABCDEFG',3,4) "Substring"
2 FROM DUAL;
Subs
----
CDEF
SQL> SELECT SUBSTR('ABCDEFG',-5,4) "Substring"
2 FROM DUAL;
Subs
----
CDEF
SQL> SELECT SUBSTRB('ABCDEFG',5,4.2) "Substring with bytes"
2 FROM DUAL;
Sub
---
EFG
3.exit和return的用法
exit用来跳出循环,用在循环中:
loop
IF A>0 THEN
EXIT;
END IF;
end loop;
return跳出存储过程,结束执行当前的过程
IF A>0 THEN
return;
END IF;
4.instr函数:计算子串在主串中的位置,其中,最后2位是可选的,如下例中的每一个,如果3,2这2个参数没有,代表的意思就是,从第一个字符开始查找第一个'OR'的位置。
SELECT INSTR('CORPORATE FLOOR','OR', 3, 2)
"Instring" FROM DUAL;
SELECT INSTR('CORPORATE FLOOR','OR', -3, 2)
"Reversed Instring"
FROM DUAL;
SELECT INSTRB('CORPORATE FLOOR','OR',5,2) "Instring in bytes"
FROM DUAL;
5.TRUNC(number|date)
如下:对于日期,如果要求到year,那么相当于为本年的1月1日开始,如果要求到mon,那么相当于为本月的1日开始;而对于数据而言,是分正负的,正数表示向小数点右边取,负数表示向小数点左边取数。
SELECT TRUNC(TO_DATE('27-OCT-92','DD-MON-YY'), 'YEAR') "New Year" FROM DUAL;
New Year
---------
01-JAN-92
SELECT TRUNC(TO_DATE('27-OCT-92','DD-MON-YY'), 'MON') "New Year" FROM DUAL;
New Year
---------
01-OCT-92
select TRUNC(89.985,2) from dual;
TRUNC(89.985,2)
---------------
89.98
SQL> select TRUNC(89.985) from dual;
TRUNC(89.985)
-------------
89
SQL> select TRUNC(89.985,0) from dual;
TRUNC(89.985,0)
---------------
89
SQL> select TRUNC(89.985,-1) from dual;
TRUNC(89.985,-1)
----------------
80
6.power(a,b),求a的b次方,须注意,负数也是可以求N次方的。
SELECT POWER(3,2) "Raised" FROM DUAL;
Raised
----------
9
select power(10,-2) from dual;
POWER(10,-2)
------------
.01
阅读(1121) | 评论(0) | 转发(0) |