Oracle 常用函数

来源：互联网发布：查看java.exe占用端口编辑：程序博客网时间：2024/05/17 03:50

/*字符函数*//*变成大写*/select Upper('abcde') from dual;/*变成小写*/select lower('ADCSE') from dual;/*第一个字母变成大写*/select Initcap('dkdkdkdkd') from dual;

/*合并字符*/select concat('a', 'b') from dual；select 'a' || 'b' from dual； /*截取字符串*/select substr('abcde', length('abcde') - 2) from dual;/*从开始的位置截取要的个数字符串，-代表从后向前计算，+代表从前向后计算*/select substr('abcde', -5, 3) from dual;/*计算字符串长度 不区分半角全角*/select Length(t.membername) from MEMBERINFO t/*替换*/select replace('abcae', 'a', 'm') from dual;/*查询匹配字符串所在的位置 相当于 index of*/select Instr('Hello World', 'or') from dual;
/*左侧填充*/select Lpad('Smith', 10, '*') from dual
/*右侧填充*/select Rpad('Smith', 10, '*') from dual
/*去除左右空格*/select trim('  dfd  ') from dual;

/*数值函数*//*四舍五入*/select round(415, -1) from dual;select round(414.21, 1) from dual;/*取余*/select Mod(12, 11) from dual；       /*       TRUNC（number,num_digits）       Number 需要截尾取整的数字。       Num_digits 用于指定取整精度的数字。Num_digits 的默认值为 0。       TRUNC()函数截取时不进行四舍五入       */select trunc(123.458) from dual; --123select trunc(123.458, 0) from dual; --123select trunc(123.458, 1) from dual; --123.4select trunc(123.458, -1) from dual; --120select trunc(123.458, -4) from dual; --0select trunc(123.458, 4) from dual; --123.458select trunc(123) from dual; --123select trunc(123, 1) from dual; --123select trunc(123, -1) from dual; --120

/*日期函数*//*计算月份差*/select months_between(sysdate, t.createdate) from MEMBERINFO t；/*增加月份*/select add_months(sysdate, 1) from dual；/*得到下一个星期一的日期*/select next_day(sysdate, '星期一') from dual；/*本月最后一天*/select last_day(sysdate) from dual;

/*转换函数*/--获得当前年select to_char(sysdate, 'yyyy') from dual;--获得当前年月日select to_char(sysdate, 'fmyyyy-mm-dd') from dual;--格式化价格样式select to_char('11112222', 'L999,999,999') from dual; --￥11,112,222--返回在星期， 星期日为1，星期一为2，星期二为3.........星期六为7select to_char(sysdate, 'D') from dual;--转化成数字select to_number('13') + to_number('14') from dual;--转化成日期select to_date('20090210', 'yyyyMMdd') from dual;

/*通用函数*/-- 如为空 返回默认值select nvl(null, 0) from dual；-- 如果表达式 exp1 与 exp2 的值相等则返回null，否则返回exp1的值select nullif('aaa', 'aaa') from dual;--如果参数表达式expr1值为NULL，则NVL2()函数返回参数表达式expr3的值；如果参数表达式expr1值不为NULL，则NVL2()函数返回参数表达式expr2的值。select nvl2(null, '11' + '22', '22') total from dual;--返回表达式中第一个非空表达式select coalesce(null, 11, 0) from dual;

--case 表达式select case t.memberstate         when '10' then          '初建档未生效'         when '20' then          '待激活状态'         when '30' then          '已激活状态'         when '50' then          '停用'         when '60' then          '退费'         when '70' then          '删除'         else          '未知状态'       end 状态  from MEMBERINFO t;  --DECODE 函数 与case 表达式类似 decode() 函数也用于实现多路分支结构select decode(memberstate,              '10',              '初建档未生效',              '20',              '待激活状态',              '30',              '已激活状态',              '50',              '停用',              '60',              '退费',              '70',              '删除',              '未知状态') 部门  from MEMBERINFO;

/*分组函数*/--count 计算总数 没有数据显示0 select count(*) from dual;--平均值select avg(memberstate) from MEMBERINFO;--max 最大值select max(memberstate) from MEMBERINFO;--min 最小值select min(memberstate) from MEMBERINFO;--sum 求和select sum(memberstate) from MEMBERINFO;--Group by 子句 可以与分组函数一起使用select avg(memberid), memberstate from memberinfo t group by memberstate;--having 子句SELECT Customer,SUM(OrderPrice) FROM Orders GROUP BY Customer HAVING SUM(OrderPrice)<2000

0 0