Oracle 函數 (轉)

資料來源 http://www.blogjava.net/Jkallen/archive/2006/07/28/60508.html

1>字符处理
upper(str)---将字符串str全部转换成大写
lower(str)---将字符串str全部转换成小写
initcap(str)---将字符串中每个单词的首字母大写
concat(str1,str2)---将字符串str1与str2连接起来(也可以通过'||'号直接相连)
substr(str,a,b)---取字符串str中的指定字符，从位置a开始取长度为b的字符串，假如a为正则从左边开始，否则从右边开始
instr(str,'z')---取得str字符串中从左边开始每一次出现z字符的下标位置(下标从1开始)
lpad(str,12,'*')---左填充，即将字符串str长度填充到12，假如其不足12位则在左边以*号填充
rpad(str,12,'*')---右填充，同上
length(str)---计算字符串str的长度

2>数字函数
round(45.926,2)---将前一数保留指定的小数位，并四舍五入(45.93)，假如指定位是负数则意为在小数点左边保留指定位,如round(45.923,-1)=50,rount(45.923,0)=46,round(45.93,-2)=0,round(55.93,-2)=100
trunc(45.926,2)---同上，得不四舍五入(45.92)
mod(1600,300)---求余(100)

3>日期型函数
oracle中默认的格式是：DD-MON-RR。
oracle中有个到当前系统时间--sysdate，如：
select sysdate from dual

可对日期进行自述运算：
select (sysdate-mybirthday)/7 from person

months_between('01-sep-95','11-jan-94')---取得二个日期之间的间隔月数(19.6774194)
add_months('11-jan-94',6)---给指定日期加上指定的月份后得到一个新的日期(11-jul-94)
next_day('01-sep-85','friday')---取得当前日期中下个周五的日期(01-jul-95)
last_day('01-feb-95')---取得当前日期中月份的最后一天(28-feb-95)

round进行四舍五入，trunc则否,以下是我的操作结果：
sysdate为：
SYSDATE
----------
28-7月 -06

 select
 round(sysdate,'month') RM,
 round(sysdate,'year') RY,
 trunc(sysdate,'month') TM,
 trunc(sysdate,'year') TY
 from dual;

RM         RY         TM         TY
---------- ---------- ---------- ----------
01-8月 -06 01-1月 -07 01-7月 -06 01-1月 -06

4>转换函数
隐式转换:系统自动转换,如：
varchar2 or char to number
varchar2 or char to date
number to varchar2
date to varchar2

显式转换：人为以函数加以转换
日期，字符，数据三者之间可以相互转换:日期<-->字符<-->数据
日期格式:YYYY

日期-->字符
select to_char(sysdate,'yyyy-mm-dd') ch from dual

CH
----------
2006-07-28

数字-->字符to_char(number,'format_model'),oramat_model有如下：
9---用对应数字表示
0---强制用０表示
$---加一$符号
L---前加本地货币单位表示
.---十进制点
,---千进制点

select to_char(0917,'l9999.99') local from dual
LOCAL
------------------
         RMB917.00

字符-->日期
tselect to_date('19830917','yyyy-mm-dd') bir from dual
BIR
----------
17-9月 -83

字符-->数字
select to_number('19821217','999999999') mybr from dual
      MYBR
----------
  19821217

注：所有函数均可以嵌套使用

5>通用函数
nvl(expr1,expr2)---expr1为空则显示expr2,否则显示expr1
nvl2(expr1,expr2,expr3)---expr1为空则显示expr2,否则显示expr3
nullif(expr1,expr2)---二个相等则显示空符,否则显示expr1
coalesce(expr1,expr2,...,exprn)---从expr1开始依次找到不为空的expr，找到就显示，直到最后，否则显

示exprn
case表达式，如下图:

6>多表查询
对普通的多表查询，也就是不加where条件的时候实际上查询结果是各表的笛卡尔集
外连接：oracle实现外连接时在=号的二边加+号就OK，当+在左边时称为右连接，反之为左连接,它常常用来当要求未受限制对象的表数据也要求显示时，如下：
select * from student

ID NAME                 ADDRESS
-------------------- --------------
1 zhangshan            zhejiang
3 lishi                hangzhou
7 lily                 guangzhou

select * from person

ID NAME                 ADDRESS
- -------------------- -----------
1 zhangshan            zhejiang
3 lishi                hangzhou
7 lily                 guangzhou

select p.id,p.sex,s.id,s.name,s.address from person p ,student s where p.id(+) = s.id

ID SEX          ID NAME                 ADDRESS
-- ---- ---------- -------------------- ------------
 1 boy           1 zhangshan            zhejiang
 3 girl          3 lishi                hangzhou
                 7 lily                 guangzhou

select p.id,p.sex,s.id,s.name,s.address from person p ,student s where p.id = s.id(+)

ID SEX          ID NAME                 ADDRESS
-- ---- ---------- -------------------- --------------
 1 boy           1 zhangshan            zhejiang
 2 girl
 3 girl          3 lishi                hangzhou
 4 boy
 5 girl

self-join，就是同一张表连接，用自连接的时候要注意排除重复的记录（自身，循环重复等），比如说找出student表中所有住在同一个地方的人。
select * from student

 ID NAME                 ADDRESS
--- -------------------- ---------
  1 zhangshan            zhejiang
  3 lishi                hangzhou
  7 lily                 guangzhou
  2 name2                guangzhou
  4 name4                guangzhou
  5 name5                hangzhou
  6 name6                shanghai
  8 name8                shanghai

没有排除重复记录时的结果：
 select t1.name,t2.name
 from student t1, student t2
 where t1.address = t2.address

NAME                 NAME
-------------------- --------------
lily                 lily
name2                lily
name4                lily
lily                 name2
name2                name2
name4                name2
lily                 name4
name2                name4
name4                name4
lishi                lishi
name5                lishi

NAME                 NAME
-------------------- --------------
lishi                name5
name5                name5
name6                name6
name8                name6
name6                name8
name8                name8
zhangshan            zhangshan

已选择18行

排除重复与循环记录之后：
select t1.name,t2.name,t1.address
 from student t1, student t2
 where t1.address = t2.address
and t1.id > t2.id

NAME                 NAME                 ADDRESS
-------------------- -------------------- -------------------
lily                 name2                guangzhou
name4                name2                guangzhou
lily                 name4                guangzhou
name5                lishi                hangzhou
name8                name6                shanghai

cross join,无条件连接，实际上跟不带where时一样得到的是笛卡尔集

natural join（也可以直接记作join），根据表中同名栏位来连接记录,若表中可能出现多个同名栏位，则用using(col_name)来指定所栏位。colname不能指定表的别名！限制条件用on来指定
select id,p.sex,id,s.name from person p join student s using(id);

  ID SEX          ID NAME
---- ---- ---------- --------------------
   1 boy           1 zhangshan
   2 girl          2 name2
   3 girl          3 lishi
   4 boy           4 name4
   5 girl          5 name5

在sql标准语法中,左(右)连接用left(right) out join,限制条件用on就可以了。

full out join完全外连接,顾名思义就是左外连接与右外连接都有
select id,p.sex,id,s.name from person p full join student s using(id);

  ID SEX          ID NAME
---- ---- ---------- ------------------
   1 boy           1 zhangshan
   3 girl          3 lishi
   2 girl          2 name2
   4 boy           4 name4
   5 girl          5 name5
   8               8 name8
   6               6 name6
   7               7 lily