Oracle单行函数

答案：用户显然是不关心的，所以仅仅能由程序去处理用户输入的数据。（增加转换函数）

select * from emp where ename=upper(‘&str‘);
SQL> select * from emp where ename=upper(‘&str‘); 输入 str 的值: ?smith 原值 ? ?1: select * from emp where ename=upper(‘&str‘) 新值 ? ?1: select * from emp where ename=upper(‘smith‘)
? ? ?EMPNO ENAME ? ? ?JOB ? ? ? ? ? ? ?MGR HIREDATE ? ? ? ? ? ? ?SAL ? ? ? COMM ? ? DEPTNO ---------- ---------- --------- ---------- -------------- ---------- ---------- ---------- ? ? ? 7369 SMITH ? ? ?CLERK ? ? ? ? ? 7902 17-12月-80 ? ? ? ? ? ?800 ? ? ? ? ? ? ? ? ? ?20
注意：以上的‘&’属于替代变量的内容
2）转换小写：
例1：将所有的雇员姓名依照小写返回 select lower(ename) from emp
例2、将每一个雇员姓名的开头首字母大写。
select initcap(ename) from emp
3）长度函数
例3、查询出每一个雇员姓名的长度
select ename,length(ename) from emp
例4、要求查询出雇员姓名长度为5的雇员信息。
select ename,length(ename) from emp where length(ename)=5
4）替换函数
例5、使用‘_‘替换姓名中的所有字母‘A‘。
select replace(ename,‘A‘,‘_‘)from emp

5）字符串截取：
A：substr(字符串|列，開始点）：表示从開始点截取到结尾
select ename,substr(ename,3) from emp
B：substr(字符串|列，開始点。结束点）：表示从開始点截取到结束点：
select ename,substr(ename,0,3) from emp select ename,substr(ename,1,3) from emp
例1、要求截取每一个雇员姓名的后三个字符：
select ename,substr(ename,length(ename)-2) from emp
设置负数：表示从后向前截取： select ename,substr(ename,-3) from emp
面试题：substr()函数截取的下表从0还是1開始的？
回答：（1）：在oracle数据库中,从0还是1開始都是一样的
? ? (2):SUBSTR也能够设置负数。表示从由后指定開始的截取点。

3、数字函数：
数字函数共同拥有三个：
? ? ROUND(数字|列，[保留小数的位数]):四舍五入的操作。

? ? TRUNC(数字|列，[保留小数的位数]):舍弃指定位置的内容。
? ? MOD(数字1，数字2）：取模操作（取余数）

例1、验证round()函数：
select round(903.5)from dual
SQL> select round(903.5)from dual;
ROUND(903.5) ------------ ? ? ? ? ?904

SQL> select round(903.53567),round(-903.53567)from dual;
ROUND(903.53567) ROUND(-903.53567) ---------------- ----------------- ? ? ? ? ? ? ?904 ? ? ? ? ? ? ?-904

SQL> select round(903.53567),round(903.53567,2)from dual;
ROUND(903.53567) ROUND(903.53567,2) ---------------- ------------------ ? ? ? ? ? ? ?904 ? ? ? ? ? ? 903.54


SQL> select round(903.53567),round(903.53567,-1)from dual;
ROUND(903.53567) ROUND(903.53567,-1) ---------------- ------------------- ? ? ? ? ? ? ?904 ? ? ? ? ? ? ? ? 900

SQL> select round(903.53567),round(-903.53567),round(903.53567,-1),round(903.53567,2)from dual;
ROUND(903.53567) ROUND(-903.53567) ROUND(903.53567,-1) ROUND(903.53567,2) ---------------- ----------------- ------------------- ------------------ ? ? ? ? ? ? ?904 ? ? ? ? ? ? ?-904 ? ? ? ? ? ? ? ? 900 ? ? ? ? ? ? 903.54

例2、验证TRUNC()函数：

SQL> select trunc(903.53567),trunc(-903.53567),trunc(903.53567,-1),trunc(903.53567,2)from dual;
TRUNC(903.53567) TRUNC(-903.53567) TRUNC(903.53567,-1) TRUNC(903.53567,2) ---------------- ----------------- ------------------- ------------------ ? ? ? ? ? ? ?903 ? ? ? ? ? ? ?-903 ? ? ? ? ? ? ? ? 900 ? ? ? ? ? ? 903.53

例3、取余函数：
select mod(10,3)from dual
4、日期函数：
1）取得当前日期：用sysdate取得
SQL> select sysdate from dual;
SYSDATE -------------- 28-6月 -13
注意：在日期中也能够进行运算：
日期+数字=日期：表示若干天后的日期
SQL> select sysdate+3,sysdate+300 from dual;
SYSDATE+3 ? ? ?SYSDATE+300 -------------- -------------- 01-7月 -13 ? ? 24-4月 -14

日期-数字=日期：表示若干天前的日期

SQL> select sysdate-3,sysdate-300 from dual;
SYSDATE-3 ? ? ?SYSDATE-300 -------------- -------------- 25-6月 -13 ? ? 01-9月 -12
日期-日期=数字：表示日期间的间隔天数：但肯定是大日期-小日期。

---->求出每一个雇员到今天为止的雇佣天数：
SQL> select ename,hiredate,sysdate-hiredate from emp;
ENAME ? ? ?HIREDATE ? ? ? SYSDATE-HIREDATE ---------- -------------- ---------------- SMITH ? ? ?17-12月-80 ? ? ? ? ? 11881.4943 ALLEN ? ? ?20-2月 -81 ? ? ? ? ? 11816.4943 WARD ? ? ? 22-2月 -81 ? ? ? ? ? 11814.4943 JONES ? ? ?02-4月 -81 ? ? ? ? ? 11775.4943 MARTIN ? ? 28-9月 -81 ? ? ? ? ? 11596.4943 BLAKE ? ? ?01-5月 -81 ? ? ? ? ? 11746.4943 CLARK ? ? ?09-6月 -81 ? ? ? ? ? 11707.4943 SCOTT ? ? ?19-4月 -87 ? ? ? ? ? 9567.49432 KING ? ? ? 17-11月-81 ? ? ? ? ? 11546.4943 TURNER ? ? 08-9月 -81 ? ? ? ? ? 11616.4943 ADAMS ? ? ?23-5月 -87 ? ? ? ? ? 9533.49432
ENAME ? ? ?HIREDATE ? ? ? SYSDATE-HIREDATE ---------- -------------- ---------------- JAMES ? ? ?03-12月-81 ? ? ? ? ? 11530.4943 FORD ? ? ? 03-12月-81 ? ? ? ? ? 11530.4943 MILLER ? ? 23-1月 -82 ? ? ? ? ? 11479.4943
2）关于日期的另外四个操作函数：
LAST_DAY(日期）：求出指定日期的最后一天
--->求出本月的最后一天日期：

SQL> select last_day(sysdate) from dual;
LAST_DAY(SYSDA -------------- 30-6月 -13
NEXT_DAY(日期，星期数）：求出下一个指定星期x的日期。
SQL> select next_day(sysdate,‘星期一‘)from dual;
NEXT_DAY(SYSDA -------------- 01-7月 -13

ADD_MONTHS(日期，数字）：求出若干月之后的日期。

SQL> select add_months(sysdate,4)from dual;
ADD_MONTHS(SYS -------------- 28-10月-13
MONTHS_BETWEEN(日期1，日期2):求出两个日期之间经历的月份。

-->求出每一个雇员到今天为止的雇佣月份：
SQL> select ename,hiredate,trunc(months_between(sysdate,hiredate))from emp;
ENAME ? ? ?HIREDATE ? ? ? TRUNC(MONTHS_BETWEEN(SYSDATE,HIREDATE)) ---------- -------------- --------------------------------------- SMITH ? ? ?17-12月-80 ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? 390 ALLEN ? ? ?20-2月 -81 ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? 388 WARD ? ? ? 22-2月 -81 ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? 388 JONES ? ? ?02-4月 -81 ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? 386 MARTIN ? ? 28-9月 -81 ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? 381 BLAKE ? ? ?01-5月 -81 ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? 385 CLARK ? ? ?09-6月 -81 ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? 384 SCOTT ? ? ?19-4月 -87 ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? 314 KING ? ? ? 17-11月-81 ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? 379 TURNER ? ? 08-9月 -81 ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? 381 ADAMS ? ? ?23-5月 -87 ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? 313
ENAME ? ? ?HIREDATE ? ? ? TRUNC(MONTHS_BETWEEN(SYSDATE,HIREDATE)) ---------- -------------- --------------------------------------- JAMES ? ? ?03-12月-81 ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? 378 FORD ? ? ? 03-12月-81 ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? 378 MILLER ? ? 23-1月 -82 ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? 377

5、转换函数：
如今已经接触到了oracle数据库中的三种数据：数字（number）。字符串（varchar2),日期(date).转换函数的主要功能就是完毕这几种数据类型之间的相互转换。
TO_CHAR(字符串|列。格式字符串）：将日期或者数字变为字符串数据显示。
-->把日期格式化为字符串：

SQL> select to_char(sysdate,‘yyyy-mm-dd‘)from dual;
TO_CHAR(SY ----------

2013-06-28

SQL> select to_char(sysdate, ‘yyyy-mm-dd‘), ? 2 ? ? ? ? to_char(sysdate, ‘yyyy‘), ? 3 ? ? ? ? to_char(sysdate, ‘mm‘), ? 4 ? ? ? ? to_char(sysdate, ‘dd‘)from dual;
TO_CHAR(SY TO_C TO TO ---------- ---- -- -- 2013-06-28 2013 06 28
这个时候显示的数据会出现前导0，要想消除前导0，增加一个‘fm‘
SQL> select to_char(sysdate, ‘fmyyyy-mm-dd‘)from dual;
TO_CHAR(SY ---------- 2013-6-28

注意:在oracle中，date里面是包括了时间的，要想显示时间。这样写：
SQL> select to_char(sysdate, ‘fmyyyy-mm-dd hh:mi:ss‘)from dual;
TO_CHAR(SYSDATE,‘FM ------------------- 2013-6-28 12:17:30

SQL> select to_char(sysdate, ‘fmyyyy-mm-dd hh24:mi:ss‘)from dual;
TO_CHAR(SYSDATE,‘FM ------------------- 2013-6-28 12:18:32


TO_DAET(字符串。格式字符串）：将字符串变为DATE数据显示。
SQL> select to_date(‘1989-03-09‘,‘yyyy-mm-dd‘) from dual;
TO_DATE(‘1989- -------------- 09-3月 -89



TO_NUMBER(字符串）：将字符串变为数字显示。（基本不用）
SQL> select to_number(‘1‘)from dual;
TO_NUMBER(‘1‘) -------------- ? ? ? ? ? ? ?1

SQL> select to_number(‘1‘)+to_number(‘2‘)from dual;
TO_NUMBER(‘1‘)+TO_NUMBER(‘2‘) ----------------------------- ? ? ? ? ? ? ? ? ? ? ? ? ? ? 3


6、通用函数：
通用函数主要有两个：nvl()函数，decode().
(1)nvl()函数:处理Null
要求查询出每一个雇员的所有年薪：

SQL> select ename,sal,comm,(sal+comm)*12 ?from emp ;
ENAME ? ? ? ? ? ? SAL ? ? ? COMM (SAL+COMM)*12 ---------- ---------- ---------- ------------- SMITH ? ? ? ? ? ? 800 ALLEN ? ? ? ? ? ?1600 ? ? ? ?300 ? ? ? ? 22800 WARD ? ? ? ? ? ? 1250 ? ? ? ?500 ? ? ? ? 21000 JONES ? ? ? ? ? ?2975 MARTIN ? ? ? ? ? 1250 ? ? ? 1400 ? ? ? ? 31800 BLAKE ? ? ? ? ? ?2850 CLARK ? ? ? ? ? ?2450 SCOTT ? ? ? ? ? ?3000 KING ? ? ? ? ? ? 5000 TURNER ? ? ? ? ? 1500 ? ? ? ? ?0 ? ? ? ? 18000 ADAMS ? ? ? ? ? ?1100
ENAME ? ? ? ? ? ? SAL ? ? ? COMM (SAL+COMM)*12 ---------- ---------- ---------- ------------- JAMES ? ? ? ? ? ? 950 FORD ? ? ? ? ? ? 3000 MILLER ? ? ? ? ? 1300

注意：这个时候有雇员的年薪变为null。造成这个问题的关键在于该雇员的comm字段为null。要想解决问题，就必须做一种处理。将comm字段变为0,nvl函数能够实现。

SQL> select ename,sal,comm,(sal+comm)*12,nvl(comm,0)from emp ;
ENAME ? ? ? ? ? ? SAL ? ? ? COMM (SAL+COMM)*12 NVL(COMM,0) ---------- ---------- ---------- ------------- ----------- SMITH ? ? ? ? ? ? 800 ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?0 ALLEN ? ? ? ? ? ?1600 ? ? ? ?300 ? ? ? ? 22800 ? ? ? ? 300 WARD ? ? ? ? ? ? 1250 ? ? ? ?500 ? ? ? ? 21000 ? ? ? ? 500 JONES ? ? ? ? ? ?2975 ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?0 MARTIN ? ? ? ? ? 1250 ? ? ? 1400 ? ? ? ? 31800 ? ? ? ?1400 BLAKE ? ? ? ? ? ?2850 ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?0 CLARK ? ? ? ? ? ?2450 ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?0 SCOTT ? ? ? ? ? ?3000 ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?0 KING ? ? ? ? ? ? 5000 ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?0 TURNER ? ? ? ? ? 1500 ? ? ? ? ?0 ? ? ? ? 18000 ? ? ? ? ? 0 ADAMS ? ? ? ? ? ?1100 ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?0
ENAME ? ? ? ? ? ? SAL ? ? ? COMM (SAL+COMM)*12 NVL(COMM,0) ---------- ---------- ---------- ------------- ----------- JAMES ? ? ? ? ? ? 950 ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?0 FORD ? ? ? ? ? ? 3000 ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?0 MILLER ? ? ? ? ? 1300 ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?0

SQL> select ename,sal,comm,(sal+nvl(comm,0))*12 from emp ;
ENAME ? ? ? ? ? ? SAL ? ? ? COMM (SAL+NVL(COMM,0))*12 ---------- ---------- ---------- -------------------- SMITH ? ? ? ? ? ? 800 ? ? ? ? ? ? ? ? ? ? ? ? ? ?9600 ALLEN ? ? ? ? ? ?1600 ? ? ? ?300 ? ? ? ? ? ? ? ?22800 WARD ? ? ? ? ? ? 1250 ? ? ? ?500 ? ? ? ? ? ? ? ?21000 JONES ? ? ? ? ? ?2975 ? ? ? ? ? ? ? ? ? ? ? ? ? 35700 MARTIN ? ? ? ? ? 1250 ? ? ? 1400 ? ? ? ? ? ? ? ?31800 BLAKE ? ? ? ? ? ?2850 ? ? ? ? ? ? ? ? ? ? ? ? ? 34200 CLARK ? ? ? ? ? ?2450 ? ? ? ? ? ? ? ? ? ? ? ? ? 29400 SCOTT ? ? ? ? ? ?3000 ? ? ? ? ? ? ? ? ? ? ? ? ? 36000 KING ? ? ? ? ? ? 5000 ? ? ? ? ? ? ? ? ? ? ? ? ? 60000 TURNER ? ? ? ? ? 1500 ? ? ? ? ?0 ? ? ? ? ? ? ? ?18000 ADAMS ? ? ? ? ? ?1100 ? ? ? ? ? ? ? ? ? ? ? ? ? 13200
ENAME ? ? ? ? ? ? SAL ? ? ? COMM (SAL+NVL(COMM,0))*12 ---------- ---------- ---------- -------------------- JAMES ? ? ? ? ? ? 950 ? ? ? ? ? ? ? ? ? ? ? ? ? 11400 FORD ? ? ? ? ? ? 3000 ? ? ? ? ? ? ? ? ? ? ? ? ? 36000 MILLER ? ? ? ? ? 1300 ? ? ? ? ? ? ? ? ? ? ? ? ? 15600


（2）decode()函数：多数值推断：
decode函数很相似于程序中if/else语句，唯一不同的是decode函数推断的是数值。而不是逻辑条件。
比如：如今要求显示所有雇员的职位，可是这些职位要求替换为中文显示。
CLERK：办事员 ? SALESMAN：销售员 ? MANAGER：经理 ? ANALYST：分析员 PRESIDENT：总裁
这样的推断肯定是逐行推断。所以这个时候就必须採用decode()了。
decode函数语法：decode(数值|列。推断值1，显示值1，推断值2，显示值2.。

。。

）

select empno,ename,job, decode(job, ‘CLERK‘,‘办事员‘,‘SALESMAN‘,‘销售员‘,‘MANAGER‘,‘经理‘,‘ANALYST‘,‘分析员‘,‘PRESIDENT‘,‘总裁‘)as job from emp

? ? EMPNO ENAME ? ? ?JOB ? ? ? JOB --------- ---------- --------- ------ ? ? ?7369 SMITH ? ? ?CLERK ? ? 办事员 ? ? ?7499 ALLEN ? ? ?SALESMAN ?销售员 ? ? ?7521 WARD ? ? ? SALESMAN ?销售员 ? ? ?7566 JONES ? ? ?MANAGER ? 经理 ? ? ?7654 MARTIN ? ? SALESMAN ?销售员 ? ? ?7698 BLAKE ? ? ?MANAGER ? 经理 ? ? ?7782 CLARK ? ? ?MANAGER ? 经理 ? ? ?7788 SCOTT ? ? ?ANALYST ? 分析员 ? ? ?7839 KING ? ? ? PRESIDENT 总裁 ? ? ?7844 TURNER ? ? SALESMAN ?销售员 ? ? ?7876 ADAMS ? ? ?CLERK ? ? 办事员
? ? EMPNO ENAME ? ? ?JOB ? ? ? JOB --------- ---------- --------- ------ ? ? ?7900 JAMES ? ? ?CLERK ? ? 办事员 ? ? ?7902 FORD ? ? ? ANALYST ? 分析员 ? ? ?7934 MILLER ? ? CLERK ? ? 办事员

Oracle单行函数

标签：日期   替换函数   analyst   else   设置   nvl   strong   个数   转换函数