浅谈单行函数

单行函数
描述可在SQL中使用的各类函数;SELECT语句中使用字符，日期，数值类型的单行函数;常用的转换函数及用途
ORACLE有很多内置的函数,可以在SYS下的STANDARD包里查到所有的函数
函数是ORACLE的一类对象,同类型的还有包,存储过程,触发器。
函数和存储过程差不多,函数有返回值 .
函数作用：进行数据计算、修改单个数据项、处理成组行的输出、指定日期和数字的显示格式、转换列数据类型。
由于函数有输入输出,那输入数据是单条或多条来区分我们的函数类型。
函数类型分别有：单行函数和多行函数
单行函数只处理单个行，并且每行返回一个结果，它的主要类型有：字符函数、数字函数、日期函数、转换函数。
单行函数的用途如下：
用于维护数据操作、获取参数并返回一个特定功能输出的值、函数是在每行上操作、每行有一行返回值、可以修改数据类型、
能嵌套、可以接受多个参数
(1)字符函数
select lower('SQL Course') from dual;    结果为sql course 作用为转换小写函数
select upper('SQL Course') from dual;    结果为SQL COURSE 作用为转换大写函数
select initcap('SQL Course') from dual;    结果为Sql Course 作用为转换首字母为大写，其它为小写函数

(2)字符串处理函数
select concat('Hello','World') from dual;   结果为HelloWorld 作用为值联结
select substr('Helloworld',1,5) from dual;  结果为Hello      作用为抽取确定长度的字符串
select length('HelloWorld') from dual;      结果为10         作用为字符串长度
select instr('HelloWorld','W') from dual;   结果为6          作用为查找指定字符的数字位置
select lpad(sal,10,'*') from dual;          结果为           作用为右对齐填充字符值
select rpad(sal,10,'*') from dual;          结果为           作用为左对齐填充字符值
select trim('H' FROM 'HelloWorld') from dual;结果为elloWorld 作用为
备注：问题？
concat函数连接和 || 连接哪个好？
比较如下：结果都为cle
select substr('oracle',4) from dual;
SELECT substr('oracle',4,3)FROM dual;
SELECT substr('oracle',-3)FROM dual;
SELECT substr('oracle',-3,3)FROM dual;
第三个参数是可以默认的,默认知道最后一个
SELECT substr('oracle',4,7)FROM dual;

(3)数值函数
select round(45.926,2) from dual; //45.93
select trunc(45.926,2) from dual; //45.92
select mod(1600,300) from dual;   //100

ROUND(column|expression, n)是一个四舍五入的函数
select round(45.926,2),round(45.926,0),round(45.926,-1) from dual; //  45.93          46           50
上面用得最多的是trunc
select trunc(sysdate) from dual;
select trunc(sysdate, 'mm') from dual --返回当月第一天
select trunc(sysdate,'yy') from dual -- 返回当年第一天
select trunc(sysdate,'dd') from dual -- 返回当前年月日
select trunc(sysdate,'yyyy') from dual --返回当年第一天
select trunc(sysdate,'d') from dual -- (星期天)返回当前星期的第一天
select trunc(sysdate, 'hh') from dual  --当前时间
备注：
select trunc(123.458) from dual --123 默认n是0
select trunc(123.458,0) from dual --123
select trunc(123.458,1) from dual --123.4
select trunc(123.458,-1) from dual --120

dual表属于用户SYS,但是所有用户可以访问它
它包含一个DUMMY列和一个值为X的行

(4)日期函数
由ORACLE参数NLS_DATE_FORMAT决定
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'
SYSDATE是获取数据库服务器上的时间
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;

增加1个小时时需要转换成天,比如1/24(天)
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') a ,to_char(sysdate+1/24,'yyyy-mm-dd hh24:mi:ss') b from dual;

日期单行函数
MONTHS_BETWEEN是查两个日期间相差的月数
select months_between(to_date('2013-01-01','yyyy-MM-dd'),to_date('2012-01-01','yyyy-MM-dd')) from dual;

ADD_MONTHS是当前时间上加月份
numtoyminterval
select sysdate,sysdate+numtoyminterval(1,'month') from dual;
select sysdate,sysdate+numtoyminterval(1,'year')  from dual;

函数增加1天
select sysdate,sysdate+numtodsinterval(1,'day')   from dual;


函数增加1小时
select sysdate,sysdate+numtodsinterval(1,'hour')   from dual;

增加一分钟
select sysdate,sysdate+numtodsinterval(1,'minute')  from dual;

可以增加1秒
select sysdate,sysdate+numtodsinterval(1,'second')   from dual;

LAST_DAY是可以计算这个月的最后一天
select NEXT_DAY (SYSDATE,2) from dual;
select LAST_DAY(SYSDATE) from dual;

备注：
隐式转换
explain plan for select * from scott.emp where empno = '7788';
select * from table(dbms_xplan.display);

这里
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 2949544139

--------------------------------------------------------------------------------------
| Id  | Operation            | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |         |       1 |      38 |       1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |       1 |      38 |       1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN        | PK_EMP |       1 |         |       0   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

    2 - access("EMPNO"=7788)

14 rows selected.

access("EMPNO"=7788)发生了隐式转换，因为我们给empno是字符串类型,单oracle转换为了数字类型。

再进行试验
explain plan for select * from scott.emp where JOB = 1;
select * from table(dbms_xplan.display);

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id  | Operation      | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |     |     1 |    38 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP  |     1 |    38 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(TO_NUMBER("JOB")=1)

13 rows selected.

这里也发生了隐式转换( 1 - filter(TO_NUMBER("JOB")=1))
to_number 是什么意思 是不是把字符转换成数字了
隐式转换都发生在字符->别的类型
隐士转换缺点：费资源肯定的 因为它要懂脑子去转换

如果job有索引，会隐士转换吗？大家思考认证一下。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/31403979/viewspace-2126873/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/31403979/viewspace-2126873/