PL/SQL 基本函数

数值型函数

/** 
 * 1. MOD(n1,n2) 返回n1除n2的余数，如果n2=0则返回n1的值。
 */
select mod(5, 3) from dual;                           -- result = 2
select mod(5, 0) from dual;                           -- result = 5
select mod(5, 1.1) from dual;                         -- result = 0.6
select mod(5.2, 1.1) from dual;                       -- result = 0.8
select mod(-5.2, 1) from dual;                        -- result = -0.2
select mod(-5.2, -1) from dual;                       -- result = -0.2

/** 
 * 2. ROUND(n1[,n2]) 返回四舍五入小数点右边n2位后n1的值，n2缺省值为0，
 * 如果n2为负数就舍入到小数点左边相应的位上
 */
select round(1.215, 2) from dual;                     -- result = 1.22
select round(1.501) from dual;                        -- result = 2
select round(11.501, -2) from dual;                   -- result = 0

/** 
 * 3. TRUNC(n1[,n2]) 返回截尾到n2位小数的n1的值，n2缺省设置为0，
 * 当n2为缺省设置时会将n1截尾为整数，如果n2为负值，就截尾在小数点左边相应的位上
 */
select trunc(111.111) from dual;                      -- result = 111
select trunc(111.999) from dual;                      -- result = 111
select trunc(111.111, 1) from dual;                   -- result = 111.1
select trunc(1.111, 2) from dual;                     -- result = 1.11
select trunc(111.111, -1) from dual;                  -- result = 110

字符型函数

/** 
 * LOWER(c)
 */
select lower('O') from dual;                          -- result = 'o'
select lower('ORACLE') from dual;                     -- result = 'oracle'

/** 
 * UPPER(c)
 */
select upper('o') from dual;                          -- result = 'O'
select upper('oracle') from dual;                     -- result = 'ORACLE'

/** 
 * LPAD(c1,n[,c2]) 返回指定长度=n的字符串
 */
select lpad('Oracle', 3) from dual;                   -- result = 'Ora'
select lpad('Oracle', 10) from dual;                  -- result = '    Oracle'
select lpad('Oracle', 10, '-') from dual;             -- result = '----Oracle'

/**
 *  RPAD(c1,n[,c2]) 返回指定长度=n的字符串，基本与上同，填充字符是从右向左
 */
select rpad('Oracle', 3) from dual;                   -- result = 'Ora'
select rpad('Oracle', 10) from dual;                  -- result = 'Oracle    '
select rpad('Oracle', 10, '-') from dual;             -- result = 'Oracle----'

/**
 * TRIM([[LEADING||TRAILING||BOTH] c2 FROM] c1) 哈哈，被俺无敌的形容方式搞晕头了吧，这个地方还是看图更明了一些.
 */
select trim(' ORACLE ') from dual;                    -- result = 'ORACLE'
select trim('-' from '--ORACLE--') from dual;         -- result = 'ORACLE'
select trim(leading '-' from '--ORACLE--') from dual; -- result = 'ORACLE--'
select trim(trailing '-' from '--ORACLE--') from dual;-- result = '--ORACLE'
select trim(both '-' from '--ORACLE--') from dual;    -- result = 'ORACLE'

/** 
 * LTRIM(c1[,c2]) 从字符串c1左侧截取掉与指定字符串c2相同的字符并返回。如果c2为空则默认截取空格
 */ 
select ltrim('123123ORACLE123', '123') from dual;     -- result = 'ORACLE123'

/**
 * RTRIM(c1,c2)与上同，不过方向相反 
 */
select rtrim('123123ORACLE123', '123') from dual;     -- result = '123123ORACLE'

/** 
 * REPLACE(c1,c2[,c3]) 将c1字符串中的c2替换为c3，如果c3为空，则从c1中删除所有c2。 
 */
select replace('--BEA--', 'BEA', 'ORACLE') from dual; -- result = '--ORACLE--'

/** 
 * SUBSTR(c1,n1[,n2]) 截取指定长度的字符串。稍不注意就可能充满了陷阱的函数。 
 */
select substr('--ORACLE--', 3, 3) from dual;          -- result = 'ORA'
select substr('--ORACLE--', 3) from dual;             -- result = 'ORACLE--'
select substr('--ORACLE--', -8, 6) from dual;         -- result = 'ORACLE'
select substr('--ORACLE--', -3) from dual;            -- result = 'E--'

/** 
 * LOWER(c)
 */
select lower('O') from dual;                          -- result = 'o'
select lower('ORACLE') from dual;                     -- result = 'oracle'

/**
 * INSTR(c1,c2[,n1[,n2]]) 返回c2在c1中位置 
 * c1:原字符串 
 * c2:要寻找的字符串 
 * n1:查询起始位置，正值表示从左到右，负值表示从右到左. 
 * n2:第几个匹配项。大于0 
 */
select instr('abcabcabc', 'abc') from dual;           -- result = 1
select instr('abcabcabc', 'abc', 2) from dual;        -- result = 4
select instr('abcabcabc', 'abc', 2, 2) from dual;     -- result = 7
select instr('abcabcabc', 'abc', -2, 2) from dual;    -- result = 4

/**
 * LENGTH(c) 返回指定字符串的长度。如果
 */
select length('abc') from dual;
select length('12abc') from dual;
select length('abc') from dual;
select length('') from dual;

日期函数

-- 日期函数

/**
 * ADD_MONTHS() 返回指定日期月份+n之后的值，n可以为任何整数。
 */
select sysdate, add_months(sysdate, 12) from dual;    -- result = '2009-3-5 19:41:30' '2010-3-5 19:41:30'
select sysdate, add_months(sysdate, -12) from dual;   -- result = '2009-3-5 19:41:30' '2008-3-5 19:41:30'

/**
 * LAST_DAY(d) 返回指定时间所在月的最后一天
 */
select sysdate, last_day(sysdate) from dual;          -- result = '2009-3-5 19:54:39'	'2009-3-31 19:54:39'

/**
 * NEXT_DAY(d,n) 返回指定日期后第一个n的日期，n为一周中的某一天
 */

select sysdate, next_day(sysdate, 1), next_day(sysdate, 2) from dual;

/**
 * MONTHS_BETWEEN(d1,d2) 返回d1与d2间的月份差，视d1,d2的值大小，结果可正可负，当然也有可能为0
 */
select months_between(sysdate, sysdate) from dual;                 -- result = 0
select months_between(SYSDATE, add_months(sysdate, -1)) from dual; -- result = 1
select months_between(SYSDATE, add_months(sysdate, 1)) from dual;  -- result = -1

/**
 * ROUND(d[,fmt]) 前面讲数值型函数的时候介绍过ROUND，此处与上功能基本相似，不过此处操作的是日期。
 */
select sysdate, round(sysdate) from dual;

/**
 * TRUNC(d[,fmt]) 与前面介绍的数值型TRUNC原理相同，不过此处也是操作的日期型
 */
select trunc(SYSDATE,'HH24') from dual;
select to_date(sysdate, 'YYYY-mm-dd HH24:MI:SS') from dual;

辅助函数

/**
 * DECODE(exp,s1,r1,s2,r2..s,r[,def]) 可以把它理解成一个增强型的if else，
 */
select decode('a1','a1','true1','a2','true2','default') from dual; -- result = 'true1'
select decode('a2','a1','true1','a2','true2','default') from dual; -- result = 'true2'
select decode('a3','a1','true1','a2','true2','default') from dual; -- result = 'default'

/**
 * GREATEST(n1,n2,...n) 返回序列中的最大值 
 */
select greatest(15,5,75,8) FROM dual;                              -- result = 75

/**
 * LEAST(n1,n2....n) 返回序列中的最小值 
 */
select least(15,5,75,8) from dual;                                 -- result = 5


/**
 * NULLIF(c1,c2) 
 */  
select nullif('a', 'b') from dual;                                 -- result = 'a'
select nullif('a', 'a') from dual;                                 -- result = null
select nullif(12, 1) from dual;                                    -- result = 12

/**
 * NVL(c1,c2) 
 */
select nvl(null, '12') from dual;
select nvl('a', '12') from dual;


/**
 * NVL2(c1,c2,c3)
 */
select nvl2('a', 'b', 'c') from dual;                              -- result = 'b'
select nvl2(null, 'b', 'c') from dual;                             -- result = 'c'

/**
 * SYS_CONTEXT(c1,c2[,n]) 将指定命名空间c1的指定参数c2的值按照指定长度n截取后返回。 
 */
select sys_context('USERENV', 'SESSION_USER') from dual;
select sys_context('USERENV', 'CURRENT_SCHEMA') from dual;
select sys_context('USERENV', 'IP_ADDRESS') from dual;
select sys_context('USERENV', 'OS_USER') from dual;

数值函数

/**
 * ABS(n) 返回数字的绝对值 
 */
select abs(12) from dual;
select abs(-12) from dual;

/**
 * BITAND(n1,n2) 位与运算
 */
select bitand(3, 9) from dual;

/**
 * CEIL(n) 返回大于或等于n的最小的整数值
 */
select ceil(1.1) from dual;
select ceil(-1.1) from dual;

/**
 * FLOOR(n) 返回小于等于n的最大整数值
 */
select floor(2.2) from dual;
select floor(-2.2) from dual;

/**
 * POWER(n1,n2) 返回n1的n2次方。n1,n2可以为任意数值，不过如果m是负数，则n必须为整数
 */
select power(2,2) from dual;

/**
 * SIGN(n) 如果n<0返回-1,如果n>0返回1，如果n=0返回0.
 */
select sign(1) from dual;                           -- result = 1
select sign(-1) from dual;                          -- result = -1
select sign(0) from dual;                           -- result = 0

/**
 * SQRT(n) 返回n的平方根,n为弧度。n>=0
 */
select sqrt(9) from dual;

/**
 * CHR(N) 返回指定数值在当前字符集中对应的字符
 */
select chr(97) from dual;                                           -- result = 'a'

/**
 * CONCAT(c1,c2) 连接字符串，等同于||
 */
select concat('a', 'b') from dual;                                  -- result = 'ab'
select 'a' || 'b' from dual;                                        -- result = 'ab'

/**
 * INITCAP(c) 将字符串中单词的第一个字母转换为大写，其它则转换为小写
 */ 
select initcap('oracle') from dual;                                 -- result = 'Oracle'

/**
 * NLS_INITCAP(c) 返回指定字符串，并将字符串中第一个字母变大写，其它字母变小写
 */
select nls_initcap('中华zhonghua') from dual;                       -- result = '中华Zhonghua'

/**
 * ASCII(c) 与chr函数的用途刚刚相反，本函数返回指定字符在当前字符集下对应的数值。
 */
select ascii('a') from dual;                                        -- result = 97

/**
 * DBTIMEZONE 返回数据库的当前时区
 */ 
select dbtimezone from dual;                                        -- result = '+08:00'

/**
 * UID 返回一个唯一标识当前数据库用户的整数。
 */ 
select uid from dual; 

/**
 * USER 返回当前session用户
 */
select user from dual;