Oracle常用函数及使用案例(珍藏版)

Oracle常用函数及使用案例（珍藏版）一：sql函数：lower（char）:将字符串转化为小写的格式。

upper（char）:将字符串转化为大写的格式。

length（char）：返回字符串的长度。

substr（char，m，n）：取字符串的字串。

案例1.将所有员工的名字按小写的方式显示select lower(ename),sal from emp;案例2.将所有员工的名字按大写的方式显示。

select upper(ename),sal from emp;案例3.显示正好为五个字符的的员工的姓名。

select * from emp where length(ename)=5;案例4.显示所有员工姓名的前三个字符。

select substr(ename,1,3) from emp;//从名字的第一个字符开始取，向后取三个字符。

案例5.以首字母为大写的方式显示所有员工的姓名。

（1）首字母大写：select upper(substr(ename,1,1)) from emp;（2）完成后面字母小写。

select lower(substr(ename,2,length(ename)-1)) from emp;(3)合并select upper(substr(ename,1,1))||lower(substr(ename,2,length(ename)-1)) from emp;案例6.以首字母为小写的方式显示所有员工的姓名。

（需要有较高的灵活度，细心分析和清晰思路）（1）首字母小写：select upper(substr(ename,1,1)) from emp;（2）完成后面字母大写。

select lower(substr(ename,2,length(ename)-1)) from emp;(3)合并select lower(substr(ename,1,1))||upper(substr(ename,2,length(ename)-1)) from emp;案例7.函数（替换）：replace(char1,search_string,replace_string);显示所有员工的姓名，用“我要替换A”替代所有“A”。

select replace(ename,'A','我是老鼠')from emp;案例8.以首字母为小写的方式显示所有员工的姓名。

select replace(ename,substr(ename,1,1),lower(substr(ename,1,1)))from emp;案例9.以首字母为大写的方式显示所有员工的姓名。

Selectreplace(ename,substr(ename,2,length(ename)-1),lower(substr(ename,2,length(ename) -1)))from emp;二：数学函数：(在财务中用的比较多)ronud（sal）用于四舍五默认取整；ronud（sal，1）用于四舍五留一位小数。

trunc（sal）取整，忽略小数。

截去小数部分。

trunc（sal，1）截取；小数点留一位，之后的右边的省去。

trunc（sal，-1）截取；只留整数，个位数取零。

floor（sal）向下最接近取整；比如1.1值为1.ceil（sal）向上最接近取整。

比如1.1值为2.做oracle测试时可以用dual表；mod(m,n)取模即取余。

// mod(10,2)=0;select mod(10,2) from dual；显示在一个月为30天的情况下所有员工的日薪金，忽略余数。

select trunc（sal/30）from emp;select floor（sal/30）from emp;返回绝对值：select abs(-13) from dual;power(m,n)m的n次幂。

三：日期函数：日期函数用来处理date类型的数据。

默认情况下日期格式时dd-mon-yy，即12-7月-78sysdate该函数返回系统时间。

select sysdate from dual；add_months(d,n)，再原有的时间上增加月份。

last_day(d)，返回指定日期所在月份的最后一天。

案例1：查找已经入职八个月多的员工select * from emp where sysdate>add_months(hiredate,8);系统时间大于雇用时间再加上八个月的时间。

那就是要八个月前入职的才满足这样的条件。

即八个月之前入职的案例2：.显示满十年服务年限的员工的姓名和受雇日期。

select * from emp where sysdate>=add_months(hiredate,12*10);案例3：对于每个员工，显示其加入公司的天数。

select sysdate - hiredate "入职天数" from emp；select trunc(sysdate - hiredate) "入职天数" from emp;案例4：.last_day(d):返回指定日期所在月份的最后一天。

找出各月倒数第三天受雇的所有员工。

求思路：（1）select hiredate，last_day（hiredate）from emp ；正解：（2）select hiredate,ename from emp where hiredate=last_day（严谨。

(hiredate)-2）四：转换函数：转换函数是用于将数据类型从一种转换为另一种。

在某些情况下，oracle server 允许的数值类型和实际的不一样，这时oracle server会隐含的转化数据类型。

例如：1.create table t1（id int）；2.insert into t1 values（‘10’）//这样oracle会自动将‘10’转换为数字10/，如果是‘ab’会报错。

注：插入数据时，如果是整数，就不要带单引号。

如果为字符串类型，就要带单引号。

3.create table t2（id varchar2（10））；insert into t2 values（1）；//这样oracle就会自动将1转化为‘1’。

尽管oracle可以进行隐含的数据类型的转换，但是它并不适应所有的情况，为了提高程序的可靠性，我们应该使用转换函数进行转换。

4．to_charselect ename,hiredate,sal from emp where deptNO=10;显示信息，可是在某些情况下，这个并不能满足你的需求。

日期可以显示时/分/秒（1）insert into emp values（8881，‘test用户’，‘MANAGER’，7702，sysdate，23，23,10）；（2）select ename，to_char(hiredate,'yyy-mm-dd hh24:mi:ss') from emp;5.薪水是否可以显示指定的货币符号：$在数字前加美元。

L在数字前加本地货币符号。

(1)select ename，to_char(hiredate,'yyy-mm-dd hh24:mi:ss')，to_char（sal，‘L99999.99’）from emp;(2)9：显示数字，并忽略前面0；0：显示数字，如位数不足，则用0不齐。

.在指定位置家小数点；，在指定位置显示逗号.L99999.99显示五位整数，两位小数点。

跟number（7,2）对应满足使用。

10．递进式查询：（1）显示所有雇员的入职日期（以常用格式显示）：select ename，to_char(hiredate,'yyyy-mm-dd hh24:mi:ss')from emp;（2）显示1980年入职的所有员工。

select * from emp where to_char(hiredate,'yyyy')=1980;（3）显示所有12月份入职的员工。

select * from emp where to_char(hiredate,'mm')=12;to_char可以只显示时间的年，月，小时，分钟或者是秒钟。

11.to_date函数用于将字符串转换成date类型的数据。

insert into emp values (8888,'ok','MANAGER',7782,to_date('1988-12-10','yyyy-mm- dd'),34.34,23.23,10);五．系统函数terminal：当前会话客户所对应的终端的标示符。

language:语言:当前数据库名称nls_date_format:当前会话客户所对应的日期格式session_user:当前会话客户所对应的数据库用户名current_schema:当前会话客户所对应的默认方案名。

host：返回数据库所在主机的名称。

案例1：通过该函数，可以查询一些重要信息，比如你正在使用那个数据库？select sys_context('userenv','db_name') from dual;（1）当前用语言：select sys_context('userenv','language') from dual;（2）当前用户：select sys_context('userenv','session_user') from dual;（3）当前使用的方案：select sys_context('userenv',' current_schema ') from dual; scott--》方案scott（有表，右视图，有触发器，有存储过程）。