oracle常用函数

1、ABS函数
语法：select abs(n) from dual;
解释：取n的绝对值。
select abs(-100) from dual; 值为：100。

2、SIGN函数
语法：select sign(n) from dual;
解释：当n > 0 时，值为1；
当n < 0 时，值为-1；
当n = 0 时，值为0.

3、CONCAT函数
语法：select concat(‘slobo’,‘Svoboda’) from dual;
解释：字符串拼接，与||效果相同。

4、INITCAP函数
语法：select initcap(‘qwer’,‘asdf’) from dual;
解释：将单词的首字母以大写的方式返回，其他小写。单词由空格、控制字符、标点符号限制。

5、INSTR 函数
语法：instr( string1, string2 [, start_position [, nth_appearance ] ] )
解释：查找l字符,首个l位于第3个位置。
select instr(‘hello,java world’, ‘l’) from dual; 3
查找l字符,从第4个位置开始。
select instr(‘hello,java world’, ‘l’, 4) from dual; 4
查找l字符,从第1个位置开始的第3个
select instr(‘hello,java world’, ‘l’, 1, 3) from dual; 15
查找l字符,从右边第1个位置开始，从右往左查找第3个（也即是从左到右的第1个）
select instr(‘hello,java world’, ‘l’, -1, 3) from dual; 3
找不到返回0
select instr(‘hello,java world’, ‘MM’) from dual; 0
INSTRB 函数
与INSTR（）函数一样，只是他返回的是字节，对于单字节 INSTRB()等于 INSTR() 。

6、LENGTH LENGTHB函数
LENGTH 返回字符串的长度。
LENGTHB 返回字节的长度，

相同的函数为：VSIZE() ；

7、LPAD 函数
语法：lpad(string,padded_length,[pad_string])
解释：string 准备填充的字符串；
padded_length 填充之后的字符串长度，也就是该函数返回的字符串长度，如果这个数量比原字符串的长度要短，lpad函数将会把字符串截取成从左到右的n个字符;
pad_string 填充字符串，是个可选参数，这个字符串是要粘贴到string的左边，如果这个 参数未写，lpad函数将会在string的左边粘贴空格。
示例：select lpad(‘abcde’,10,‘oq’) from dual; 结果：oqoqoabcde
select lpad(‘abcde’,2) from dual; 结果：ab
RPAD函数从右边对字符串使用指定的字符进行填充，语法格式与lpad格式相同：
例如： rpad(‘tech’, 7); 将返回’tech ’
rpad(‘tech’, 2); 将返回’te’
rpad(‘tech’, 8, ‘0’); 将返回’tech0000’
rpad(‘tech on the net’, 15, ‘z’); 将返回 ‘tech on the net’
rpad(‘tech on the net’, 16, ‘z’); 将返回 ‘tech on the netz’

8、TRIM函数
trim() 函数移除字符串两侧的空白字符或其他预定义字符。
ltrim() 移除字符串左侧的空白字符或其他预定义字符。
rtrim() 移除字符串右侧的空白字符或其他预定义字符。
trim()函数的用法有,leading 开头字符，trailing 结尾字符，both 开头和结尾字符，如下：
trim(leading || trailing || both ‘将要被替换掉的字符" from “将要被替换的字符串")
1、trim函数去除指定的开头字符
select trim(leading ‘x’ from ‘xday’) from dual;
2、trim函数去除指定的结尾字符
select trim(trailing ‘x’ from ‘dayx’) from dual;
3、trim函数去除指定的首部和尾部字符
select trim(both ‘x’ from ‘xdayx’) from dual;
4、默认情况下，trim函数会去除首部和尾部，被指定的字符
select trim(‘x’ from ‘xdayx’) from dual;
5、如果没有指定被移除的字符，则会默认去除首部和尾部的空格
select trim(’ day ') from dual;

9、REPLACE 函数
语法：replace（‘将要更改的字符串’,‘被替换掉的字符串’,’[替换字符串]’）
示例：select replace (‘111222333444’,‘222’,‘888’) from dual; 结果：111888333444
replace(‘123123tech’, ‘123’); 结果：‘tech’
replace(‘123tech123’, ‘123’); 结果： ‘tech’
replace(‘222tech’, ‘2’, ‘3’); 结果： ‘333tech’
replace(‘0000123’, ‘0’); 结果： ‘123’
replace(‘0000123’, ‘0’, ’ ‘); 结果： ’ 123’
相同效果的函数 TRANSLATE
select TRANSLATE(‘fumble’,‘uf’,‘ar’) from dual; 结果：ramble

10、TRUNC函数
语法：TRUNC（NUMBER）表示截断数字，TRUNC（date）表示截断日期。
(1)截断数字：
格式：TRUNC（n1,n2），n1表示被截断的数字，n2表示要截断到那一位。n2可以是负数，表示截断小数点前。注意，TRUNC截断不是四舍五入。
select TRUNC(15.79) from dual; 15
select TRUNC(15.79,1) from dual; 15.7
select trunc(15.79,-1) from dual; 10
(2)截断日期：
1.select trunc(sysdate) from dual --2011-3-18 今天的日期为2011-3-18
2.select trunc(sysdate,‘mm’) from dual --2011-3-1 返回当月第一天.
3.select trunc(sysdate,‘yy’) from dual --2011-1-1 返回当年第一天
4.select trunc(sysdate,‘dd’) from dual --2011-3-18 返回当前年月日
5.select trunc(sysdate,‘yyyy’) from dual --2011-1-1 返回当年第一天
6.select trunc(sysdate,‘d’) from dual --2011-3-13 返回当前星期的第一天(星期天)
7.select trunc(sysdate, ‘hh’) from dual --2011-3-18 14:00:00 截取到小时
8.select trunc(sysdate, ‘mi’) from dual --2011-3-18 14:41:00 截取到分钟
9.select trunc(add_months(SYSDATE,-1),‘MM’) from dual; --获取上月第一天

11、Oracle关于时间/日期的操作
　（1）.日期时间间隔操作
　　当前时间减去7分钟的时间
　　select sysdate,sysdate - interval ‘7’ MINUTE from dual
　　当前时间减去7小时的时间
　　select sysdate - interval ‘7’ hour from dual
　　当前时间减去7天的时间
　　select sysdate - interval ‘7’ day from dual
　　当前时间减去7月的时间
　　select sysdate,sysdate - interval ‘7’ month from dual
　　当前时间减去7年的时间
　　select sysdate,sysdate - interval ‘7’ year from dual
　　时间间隔乘以一个数字
　　select sysdate,sysdate - 8 *interval ‘2’ hour from dual

（2）.日期到字符操作
　　select sysdate,to_char(sysdate,‘yyyy-mm-dd hh24:mi:ss’) from dual
　　select sysdate,to_char(sysdate,‘yyyy-mm-dd hh:mi:ss’) from dual
　　select sysdate,to_char(sysdate,‘yyyy-ddd hh:mi:ss’) from dual
　　select sysdate,to_char(sysdate,‘yyyy-mm iw-d hh:mi:ss’) from dual

（3）. 字符到日期操作
　　select to_date(‘2003-10-17 21:15:37’,‘yyyy-mm-dd hh24:mi:ss’) from dual
　　具体用法和上面的to_char差不多。

（4）. trunk/ ROUND函数的使用
　　select trunc(sysdate ,‘YEAR’) from dual
　　select trunc(sysdate ) from dual
　　select to_char(trunc(sysdate ,‘YYYY’),‘YYYY’) from dual

（5）.oracle有毫秒级的数据类型
　　--返回当前时间 年月日小时分秒毫秒
　　select to_char(current_timestamp(5),‘DD-MON-YYYY HH24:MI:SSxFF’) from dual;
　　--返回当前 时间的秒毫秒，可以指定秒后面的精度(最大=9)
　　select to_char(current_timestamp(9),‘MI:SSxFF’) from dual;

12、USERENV() 函数
select userenv(‘language’) from dual;
select userenv(‘lang’) from dual;
select userenv(‘sessionid’) from dual;
select userenv(‘entryid’) from dual;
select userenv(‘TERMINAL’) from dual;

13、SUBSTR()函数
语法:substr(字符串,截取开始位置,截取长度)
示例：
substr(‘Hello World’,0,1) //返回结果为 ‘H’ *从字符串第一个字符开始截取长度为1的字符串
substr(‘Hello World’,1,1) //返回结果为 ‘H’ *0和1都是表示截取的开始位置为第一个字符
substr(‘Hello World’,2,4) //返回结果为 ‘ello’
substr(‘Hello World’,-3,3)//返回结果为 ‘rld’ *负数(-i)表示截取的开始位置为字符串右端向左数第i个字符。

14、 USERENV()函数
select user from dual
select vsize(‘qwe’) from dual;
select userenv(‘language’) from dual
select userenv(‘lang’) from dual;
select userenv(‘sessionid’) from dual;
select userenv(‘entryid’) from dual;
select userenv(‘TERMINAL’) from dual;

15、在 XML 中，有 5 个预定义的实体引用：
< < less than
> > greater than
& & ampersand
’ ’ apostrophe
" " quotation mark

16、oracle行转列、列转行
（1）行转列（i大小写不敏感）
select regexp_substr(‘17,20,23’, ‘[^,]+’, 1, level, ‘i’) from dual connect by level <= 10;
select regexp_substr(‘17,20,23’, ‘[^,]+’, 1, level, ‘i’) from dual
connect by level <= length(‘17,20,23’) - length(replace(‘17,20,23’, ‘,’, ‘’)) + 1
（2）列转行
SELECT listagg(t.contract_number, ‘,’) within group(ORDER BY t.contract_number) AS contract_number
from (SELECT distinct cch.contract_number, cs.source_document_id
FROM con_contract_headers cch, con_document_flows cs
WHERE cch.contract_header_id = cs.document_id
AND cs.document_type = ‘CON_CONTRACT’
AND cs.source_document_type = ‘EXP_REQUISITION_LINES’) t
where t.source_document_id(+) = s.exp_requisition_header_id

17、 查询数据结果中指定行的数据
select
(select b.AMOUNT from (SELECT ROWNUM rn,
t1.AMOUNT AMOUNT
FROM CON_PAYMENT_SCHEDULES t1CONTRACT_HEADER_ID
WHERE t1.CONTRACT_HEADER_ID =4 )b where b.rn=1)||’+’||
(select b.AMOUNT from (SELECT ROWNUM rn,
t1.AMOUNT AMOUNT
FROM CON_PAYMENT_SCHEDULES t1
WHERE t1.CONTRACT_HEADER_ID =4 )b where b.rn=2)||’+’||
(select nvl(b.AMOUNT,0) from (SELECT ROWNUM rn,
t1.AMOUNT AMOUNT
FROM CON_PAYMENT_SCHEDULES t1
WHERE t1.CONTRACT_HEADER_ID =4 )b where b.rn=3)
from dual;

18、 将查询结果按指定的字段进行拼接：
同一个id的多行金额数据，将金额列转行显示：
replace( (select wm_concat(t1.AMOUNT)
from CON_PAYMENT_SCHEDULES t1
where t1.CONTRACT_HEADER_ID =t.CONTRACT_HEADER_ID) ,’,’,’+’）

select t.CONTRACT_HEADER_ID,
wm_concat(t.AMOUNT)
from CON_PAYMENT_SCHEDULES t
where t.CONTRACT_HEADER_ID =377
group by t.CONTRACT_HEADER_ID

19、bm文件后台匹配模糊查询：
msg_title like '%' || ${@msg_title} || '%'

20、EABD8CE9404507AA8C22714D3F5EADA9 更改系统密码，此密码为‘1’
select * from sys_user;
update sys_user set encrypted_foundation_password =‘EABD8CE9404507AA8C22714D3F5EADA9’ where user_name =‘why’;
update sys_user set encrypted_user_password =‘EABD8CE9404507AA8C22714D3F5EADA9’ where user_name =‘why’;

21、dbms_output.put_line函数输出只能是字符串，因此利用to_char函数将数值型结果转换为字符型。

21、PRAGMA AUTONOMOUS_TRANSACTION语法：

PRAGMA AUTONOMOUS_TRANSACTION 中文翻译过来叫“自治事务”，对于定义成自治事务的Procedure，实际上相当于一段独立运行的程序段，这段程序不依赖于主程序，也不干涉主程序。

特点：
第一、这段程序不依赖于原有Main程序，比如Main程序中有未提交的数据，那么在自治事务中是查找不到的。
第二、在自治事务中，commit或者rollback只会提交或回滚当前自治事务中的DML，不会影响到Main程序中的DML。