Oracle一.字符函数--大小写转换函数1.LOWER (strexp) 返回字符串,并将所有的字符小写. select lower('ABCDE') from dual--输出empbai8o表的姓名（小写）select lower(ename) from emp;2.UPPER (strexp) 返回字符串,并将所有的字符大写.select upper('abcdf') from dual3.INITCAP(strexp) 将字符串的(每个单词的)第一个字母变为大写,后面的小写; select initcap(' source fore') from dualselect initcap(' SOURCE FORE') from dual -- Source Fore(如果都是大写会自动将第一个字母变为大写其他的小写)4.CONCAT(strexp, strexp): 连接两个字符串 --利用CONCAT函数输出员工的姓名select concat(first_name,last_name) from employees5.substr(str,start_index,length) :从指定的位置截取指定长度的字符串 select substr('abcdefg',2,3) from dual6.LENGTH(strexp):返回字符串的长度 select length('abcdef') from dual--输出员工表中first_name的长度select length(first_name) from employees7.INSTR(C1,C2,I,J):在一个字符串中搜索指定的字符,返回发现指定的字符的位置;c1:搜索的字符c2:要搜索的字符I:表示从哪个位置开始查找J:查找第几次出现select instr('aborcdoryuklhorp','or',1,2) from dual ---7select instr('aborcdoryuklhorp','or',6,2) from dual ---14 8.LPAD( string1, padded_length, [ pad_string ] ) 在列的左边粘贴字符 select lpad('acd',8) from dual 9.RPAD(粘贴字符) RPAD? 在列的右边粘贴字符select rpad('acd',8,'') from dualselect rpad('acdefghijklm',8,'') from dual10.trim(str):截取字符串两端特殊字符select ' abd ' from dualselect trim(' abd ') from dual 11.replace(str,search_str[,replace_str]):将每次在str中出现的search_str用replace_str替换select replace('HELOVEYOU','HE','I') from dual二.数学函数1.round：传回一个数值，该数值是按照指定的小数位数进行四舍五入运算的结果。select round(3000.926) from dual --3001--小数部分是两位select round(3000.926,2) from dual --3000.932.TRUNC函数返回处理后的数值，其工作机制与ROUND函数极为类似，--只是该函数不对指定小数前或后的部分做相应舍入选择处理，而统统截去。select trunc(3000.926) from dual --3000select trunc(3000.926,2) from dual --3000.923.mod（number1，number2）两个数值相除并返回其余数。运算符执行 number1 除以 number2 操作 select mod(1600,300) from dual --100三.转化函数 1.TO_CHAR(date,'fmt') ：是字符类型的函数，转化日期为字符 格式('fmt') 1、必须用单引号括起来,并且是大小写敏感 2、可包含任何有效的日期格式 3、fmt值的宽度正好能容纳所有的有效数字 2.修改当前的语言环境为中文alter session set nls_language='SIMPLIFIED CHINESE'--输入当前的系统日期,格式为 yyyy-mm-ddselect sysdate from dualselect to_char(sysdate,'yyyy-mm-dd') from dual--输入当前的系统日期,格式为 yyyy-mon-ddselect to_char(sysdate,'yyyy-mon-dd') from dual--输入当前的系统日期,格式为 yyyy-month-ddselect to_char(sysdate,'yyyy-month-dd') from dual--输入当前的系统日期,格式为 yyyy-mon-dd-dayselect to_char(sysdate,'yyyy-mon-dd day') from dual--输入当前的系统日期,格式为 yyyy-mon-dd-dyselect to_char(sysdate,'yyyy-mon-dd-dy') from dual--输入当前的系统日期,格式为 yyyy-mon-dd-dy-dselect to_char(sysdate,'yyyy/mon/dd/dy/d') from dual--ddspth 日期的英文显示--输入当前的系统日期,格式为 yyyy-mon-ddspthselect to_char(sysdate,'yyyy-mon-ddspth') from dual --2010-4月 -seventeenth3.修改为英文环境(此语句只对当前的窗口有效,当窗口关闭虚重新设置)alter session set nls_language=AMERICAN;--输入当前的系统日期,格式为 yyyy-mm-ddselect to_char(sysdate,'yyyy-mm-dd') from dual--输入当前的系统日期,格式为 yyyy-mon-ddselect to_char(sysdate,'yyyy-mon-dd') from dual --2010-apr-17--输入当前的系统日期,格式为 YYYY-MON-DDselect to_char(sysdate,'yyyy-MON-dd') from dual --2010-APR-17--输入当前的系统日期,格式为 YYYY-MONTH-DDselect to_char(sysdate,'YYYY-MONTH-DD') from dual--输入当前的系统日期,格式为 yyyy-mon-dd-dayselect to_char(sysdate,'yyyy-mon-dd-day') from dualselect to_char(sysdate,'yyyy-mon-dd-DAY') from dual --2010-apr-17-SATURDAY--输入当前的系统日期,格式为 yyyy-mon-dd-dyselect to_char(sysdate,'yyyy-mon-dd-dy') from dual --2010-apr-17-satselect to_char(sysdate,'yyyy-mon-dd-DY') from dual --2010-apr-17-SAT4.ddspth 不分中英文环境 日期的英文显示--输入当前的系统日期,格式为 yyyy-mon-ddspthselect to_char(sysdate,'yyyy-mon-ddspth') from dual --2010-apr-seventeenth--小时 分 秒--输入当前的系统日期,格式为 yyyy-mon-dd HH24:MI:SSselect to_char(sysdate,'yyyy-mon-dd HH24:MI:SS') from dual----输入当前的系统日期,格式为 dd-mon-yyyy SS:HH24:MI(格式没有顺序)--注意:使用to_char函数转化日期--->字符 格式没有顺序 select to_char(sysdate,'dd-mon-yyyy SS:HH24:MI') from dual--输入当前的系统日期,格式为 yyyy-mon-dd HH24:MI:SS AM(PM)--增加AM PM --在这个格式中AM PM没有区别 ，表达的含义是一致的 select to_char(sysdate,'yyyy-mon-dd HH24:MI:SS AM') from dual select to_char(sysdate,'yyyy-mon-dd HH24:MI:SS PM') from dual select to_char(sysdate,'yyyy-mon-dd HH24:MI:SS PM') from dual --输入当前的系统日期,格式为 dd of month (19 of 1月)--可以在格式中增加字符串,字符串用""引起来select to_char(sysdate,'dd of month ') from dual5.to_date(char,fmt) 转化字符为日期 第一个参数:符合日期格式的字符 第二个参数:格式(同to_char函数转化日期的格式)alter session set nls_language='SIMPLIFIED CHINESE'alter session set nls_language=AMERICAN;--转化2008-04月-18这个字符为日期select to_date('2008-4月-18','yyyy-mon-dd') from dual;select to_date('4月-18-2008','mon-dd-yyyy') from dual;--使用逆向思维: --字符的日期 2008-ari-15 select to_char(sysdate,'yyyy-mon-dd') from dual --2008-may-19 select to_date('2009-may-19','yyyy-mon-dd') from dual--开发中 --转化日期为字符输出 select to_char(sysdate,'yyyy-mm-dd') from dual --转化字符为日期 select to_date('2008-12-12','yyyy-mm-dd') from dual 6.TO_CHAR(number,'fmt')：是字符类型的函数，转化数字为字符 --问题 3000.45转化这个数字为字符 --使用 9 输出如下格式 3000.4500 select to_char(3000.45,'99999999999.9999') from dual --使用0 输出如下格式 0003000.4500 select to_char(3000.45,'000000000.000000') from dual --增加美元符号 输出如下格式 $0003000.4500 select to_char(3000.45,'$0000000.0000') from dual --增加本地货币符号 输出如下格式 ￥0003000.4500 select to_char(3000.45,'L0000000.0000') from dual select to_char(3000.45,'L99999999999.9999') from dual --增加千分位符号 select to_char(3000.45,'99,999,999,999.9999') from dual select to_char(3000.45,'00,000,000,0000.0000') from dual --使用to_number('字符'，格式) --将字符转化为数字 符合数字格式的字符 转化的格式,等同to_char转化数字为字符的格式 问题:'3000.45'--转化为数字 select to_number('3000.45','0000.00') from dual 问题:'$3000.45'--转化为数字 select to_number('$3000.45','$0000.00') from dual --3000.45四.日期函数: --计算2008-4月-1日和2008-8月-10日相差多少个月 --使用months_between(date1,date2)函数 select months_between(to_date('2008-4月-01','yyyy-mon-dd'),to_date('2008-8月-10','yyyy-mon-dd')) from dual select months_between(to_date('2008-8月-10','yyyy-mon-dd'),to_date('2008-4月-01','yyyy-mon-dd')) from dual --相差多少个月四舍五入到整数 select round(months_between(to_date('2008-8月-10','yyyy-mon-dd'),to_date('2008-4月-01','yyyy-mon-dd'))) from dual --给出日期2008-4月-01 计算六个月后的日期 --使用Add_months(date1,number) select add_months(to_date('2008-4月-01','yyyy-mon-dd'),6) from dual --给出日期date和星期x之后计算下一个星期的日期 --使用NEXT_DAY(date,'day') select next_day(to_date('2010-4月-01','yyyy-mon-dd'),'星期三') from dual --计算指定日期所在月份的最后一天的日期 --使用last_day(date) select last_day(to_date('2010-3月-01','yyyy-mon-dd')) from dual 五.其他函数1.使用nvl函数实现:输出员工的姓名 月薪 提成 月收入 select first_name,salary,commission_pct,salary(1+nvl(commission_pct,0)) from employees2.NVL2(expr0,expr1,expr2) 如果expr0不为Null,返回expr1, 为Null,返回expr2. expr0可以为任何数据类型--使用nvl2函数实现:输出员工的姓名 月薪 提成 月收入select first_name,salary,commission_pct,nvl2(commission_pct,salary(1+commission_pct),salary) from employees3.NULLIF(expr1,expr2) 比较两个表达式，如果相等返回空值，如果不等返回第一个表达式--使用NULLIF函数实现:比较first_name last_name的长度 select first_name,last_name,nullif(length(first_name),length(last_name)) from employees4.COALESCE(expr1,expr2,...,exprn) 返回表达式列表里的第一个非空表达式的值，表达式列表可以包含很多表达式,当第一个满足值非空时，返回这个表达式的值。 expr1,expr2,...,exprn的数据类型必须一致--使用COALESCE函数:输出first_name,last_name,job_id和三个字段中非空表达式的值select first_name,last_name,job_id,COALESCE(first_name,last_name,job_id) from employees--CASE expr WHEN comparison_expr1 THEN return_expr1 [WHEN comparison_expr2 THEN return_expr2 WHEN comparison_exprn THEN return_exprn ELSE else_expr] END需求:输出first_name,job_id,salary, 涨后的工资 如果是'人力经理' 工资涨1% 如果是'计划部经理' 工资涨2% 如果是'秘书' 工资涨3% 如果是'保安部经理' 工资涨4% 其他的涨 5% select first_name,job_id,salary,case job_id when '人力经理' then salary(1+0.01) when '计划部经理' then salary(1+0.02) when '秘书' then salary(1+0.03) when '保安部经理' then salary(1+0.04) else salary(1+0.05) end "涨后的工资"from employees --5.DECODE函数类似于一系列CASE 或 IF-THEN-ELSE 语句 DECODE(col/expression, search1, result1 [, search2, result2,...,] [, default])需求:输出first_name,job_id,salary, 涨后的工资 如果是'人力经理' 工资涨1% 如果是'计划部经理' 工资涨2% 如果是'秘书' 工资涨3% 如果是'保安部经理' 工资涨4% 其他的涨 5% select first_name,job_id,salary,decode(job_id, '人力经理',salary(1+0.01), '计划部经理',salary(1+0.02), '秘书',salary(1+0.03), '保安部经理',salary(1+0.04), salary(1+0.05) ) "涨后的工资" from employees
