oracle系统函数

<!-- [if gte mso 9]><xml><w:WordDocument><w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel><w:DisplayHorizontalDrawingGridEvery>0</w:DisplayHorizontalDrawingGridEvery><w:DisplayVerticalDrawingGridEvery>2</w:DisplayVerticalDrawingGridEvery><w:DocumentKind>DocumentNotSpecified</w:DocumentKind><w:DrawingGridVerticalSpacing>7.8</w:DrawingGridVerticalSpacing><w:View>Normal</w:View><w:Compatibility></w:Compatibility><w:Zoom>0</w:Zoom></w:WordDocument></xml><![endif]-->

--===============

--author：yeeXun

--date：发表于 @ 2010年12月21日　17:03:00

--address:Jau 17-304

--===============

一． 字符函数

♢lower （ char ）：将字符串转化为小写格式

♢upper （ char ）：将字符串转化为大写格式

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

♢substr （ char ， m ， n ）：在 char 字符串中从 m 开始，截取 n 个字符

❤如果省略了n ，那么默认长度是 m 位置以后剩下的 char 的长度

实例：

？将所有员工的名字按小写方式显示：

selectlower(ename)name,jobfromemp;

NAMEJOB

-------------------

smithCLERK

allenSALESMAN

wardSALESMAN

...

14rowsselected

?将所有员工的名字按大写的方式显示：
selectupper(ename)fromemporderbyename;

UPPER(ENAME)

------------

ADAMS

ALLEN

BLAKE

...

14rowsselected

？显示正好为5个字符的员工的姓名：

selectename,job,salfromempwherelength(ename)=5;

ENAMEJOBSAL

----------------------------

SMITHCLERK800.00

ALLENSALESMAN1600.00

JONESMANAGER2975.00

...

8rowsselected

？显示所有员工姓名的前三个字符：

selectsubstr(ename,1,3)姓名fromemp;

姓名

------

SMI

ALL

WAR

...

14rowsselected

？以首字母大写其他字母小写的方式显示所有员工的姓名：

select upper(substr(ename,1,1))||lower(substr(ename,2 ,length(ename)-1 ) )姓名 fromemp;

姓名

--------------------

Smith

Allen

Ward

...

14rowsselected

?将首字母小写其他字符大写的方式显示员工姓名：

s elect lower(substr(ename,1,1))||upper(substr(ename,2 ,length(ename ) -1) 姓名 fromemp;

姓名

--------------------

sMITH

aLLEN

wARD

...

14rowsselected

/*郁闷啊，突然的断电，我写的东西都没了，登录 PL/SQLDeveloper 说没有监听 程序，难道是监听程序自动关闭了？但是由oracle 附带的 oracleSQL*Plus 登录， 能成功登录。

我的电脑-> 管理 -> 服务和应用程序 -> 服务

查看，原来真的这突然的断电导致了oracle 监听进程 OracleOraDb10g_home1TNSListener 关闭，手动启动；再次登录 PL/SQLDeveloper ，还说没有监听进程，再次郁闷；重新启动，登录 PL/SQLDeveloper ，成功！原来啊，刚刚才的监听进程启动没生效！

*/

♢replace(char1,search_string,replace_string): 在 char1 里面用 replace_string来替换 search_string 。

？显示所有员工的姓名，用" 我是 A" 替换所有 "A" ：

selectreplace(ename,'A','我是大啊')替换后的姓名fromemp;

替换后的姓名

-------------------------------------------------------------- ------------------

SMITH

我是大啊LLEN

W我是大啊RD

M我是大啊RTIN

BL我是大啊KE

SCOTT

我是大啊D我是大啊MS

J我是大啊MES

...

14rowsselected

♢instr （ char1,char2,[,n[,m]] ） : 取子串在字符串的位置

二． 数学函数

数学函数的输入参数和返回值的数据类型都是数字类型的，数学函数有：cos,cosh,exp,ln,log,sin,sinh,sqrt,tan,tanh,acos,asin,atan,round,... 常用的有：

♢round(n,[m]) ：用于执行四舍五入，如果省略了 m ，则四舍五入到整数，如果 m 为正数，则四舍五入到小数的m 位后，如果 m 为负数，则四舍五入到小数点的 m 位前。

？计算奖金不为空的员工的年薪

selectename,(round(sal)+round(comm))*13年薪,salfromempwhere commisnotnull;

ENAME年薪SAL

-----------------------------

ALLEN247001600.00

WARD227501250.00

MARTIN344501250.00

TURNER195001500.00

♢ trunc(n,[m]):用于截取数字，如果省略掉 m ，就截取小数部分，如果 m 是正数， 就截取到小数点的m 位后，如果 m 为负数，则截取到小数点的前 m 位

♢mod(m,n) ：求 m 和 n 的余数

♢floor(n) ：返回小于或等于 n 的最大整数

♢ceil(n) ：返回大于会等于 n 的最小整数

dual表：在做 oracle 测试时，可以使用此表，主要用于做测试，虚拟。

？显示在一个月为30 天的情况下所有员工的日薪金，忽略余数：

selecttrunc(sal/30),enamefromemp;

TRUNC(SAL/30)ENAME

-----------------------

26SMITH

53ALLEN

41WARD

...

14rowsselected

selectfloor(sal/30),enamefromemp;

结果与上面的相同

其他数学函数：

Abs(n)：返回 n 的绝对值

Acos(n):返回 n 的反余弦值

Asin(n):返回 n 的反正弦值

Atan(n):返回 n 的反正切值

Cos(n):

Exp(n)：返回 e 的 n 次幂

Log(m,n)：取对数

Power(m,n)：返回 m 的 n 次幂

三． 日期函数

/*

当打开PL/SQLDeveloper 进行登录时，弹出一个警告窗口：（ ORA-12560:TNS: 协议适配器错误），之后无法登录。我的电脑-> 属性 -> 管理 -> 服务 , 发现我的 oracle 监听进程自动关闭了，当我启动时，弹出这样的警告信息：（在本地计算机上的 OracleOraDb10g_home1TNSListener服务启动后又停止。一些服务自动停止，如 果它们没有什么可做的，例如" 性能日志和警报 " 服务）。也就是启动失败，但是用 oracle自动附带的 OracleSQL*Plus 登录，成功了，这是为什么呢？ */

介绍

日期函数用于处理date 类型的数据，默认情况下日期格式为 dd-mm-yy ，例如 25-7-1987。

1. sysdate：返回系统时间

2. add_months(d,n):

?查找已经入职 8 各月多的员工

selectename,job,salfromempwhere sysdate>add_months(hiredate,300)

？显示满10 年服务年限的员工的姓名和雇佣日期

selectename,job,salfromempwhere sysdate>add_months(hiredate,240)

？对于每个员工，显示加入公司的天数

selecttrunc(sysdate-hiredate)入职天数 ,ename 姓名 fromemp;

3.last_day(d):返回指定日期所在月份的最后一天

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

selecthiredate,enamefromempwhere last_day(hiredate)-2=hiredate;

-----------------------------------------

以上内容是在两周之前写下的

-----------------------------------------

/*课程设计过去了一个周，紧接着是一课程期末考试，和 6 级考试，哎！紧张的日子 过去了，现在有时间来继续我的 oracle 学习了。

两周之前，我的PL/SQLDevloper 出了点问题，一直未去解决。今天打开它时，居 然已经过了 30 天的试用期，郁闷，卸载了重新安装吧！可是找不到源文件了，于是上 找了一个 PL/SQLDevloper8.0 版本的，试用期也是 30 天。刚刚安装好，用户 scott 登录，居然连接超时，没有出现前两周出现的情况。但是提示未登录，于是建 一个 commandwindow ，在里面输入： connectscott/tiger

ConnectedtoOracleDatabase10gEnterpriseEditionRelease10.2.0.1.0

Connectedasscott

居然连接成功了！

SQL>showuser

Useris"scott"

再次验证：

select*fromemp;

EMPNOENAMEJOBMGRHIREDATESALCOMMDEPTNO

----------------------------------------------------------------

7369SMITHCLERK79021980-12-17800.0020

7499ALLENSALESMAN76981981-2-201600.00300.0030

.......

14rowsselected

*/

四． 转换函数

转换函数用于将数据类型从一种转换为另一种。在某些情况下，oracleserver 允许值的数据类型和实际的不一样，这时 oracleserver 会隐式的转化数据类型。

例如：

createtablet1（ idint ） ;

insertintot1values（ '10' ） -- 这样 oracle 会自动的将 '10' 转换为 10 ；

createtablet2（ idvarchar2(10) ） ;

insertintot2values（ 1 ） -- 这样 oracle 就会自动的将 1 转换为 '1';

1.to_char函数

to_char

插入一条数据：

SQL>insertintoempvalues(8881,'test用户 ','MANAGER',7782,sysdate,23,23,10);

将emp 表中的雇佣日期转换为字符串的形式显示出来：

SQL>selectename,to_char(hiredate,'yyyy-mm-ddhh24:mi:ss')fromemp;

ENAMETO_CHAR(HIREDATE,'YYYY-MM-DDHH

----------------------------------------

test用户2010-12-2116:03:33

SMITH1980-12-1700:00:00

ALLEN1981-02-2000:00:00

......

15rowsselected

这里说一下日期格式：

yy：两位数字的年份2010-->10

yyyy：四位数字的年份2010

mm：两位数字的月份2月-->02

dd：两位数字的天5号-->05

hh24：时间的24小时制

hh12：时间的12小时制度

mi、ss：显示分钟/秒

9:显示数字，忽略前面的 0

0：显示数字，如位数不足，则用 0 补齐

·：在指定位置显示小数点

，：在指定位置显示逗号

$：在数字前加美元符号

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

C：在数字前加国际货币符号

G：在指定位置显示组分隔符

D：在指定位置显示小数点符号 (.)

SQL>selectename,to_char(hiredate,'yyyy-mm-ddhh24:mi:ss'),to_char(sal,'L99,999.99')fromemp;

ENAMETO_CHAR(HIREDATE,'YYYY-MM-DDHHTO_CHAR(SAL,'L99,999.99')

-----------------------------------------------------------------

test用户2010-12-2116:03:33￥23.00

SMITH1980-12-1700:00:00￥800.00

ALLEN1981-02-2000:00:00￥1,600.00

......

15rowsselected

？显示 1980年入职的员工

SQL>select*fromempwhereto_char(hiredate,'yyyy')=1980;

EMPNOENAMEJOBMGRHIREDATESALCOMMDEPTNO

----------------------------------------------------------------

7369SMITHCLERK79021980-12-17800.0020

2. t o_date函数

t o_date函数用于将字符串转换为 date 类型的数据。

3.sys_context

每个用户对应一个方案，方案名和用户名相同。用户一旦被创建后，就为其创建了一个方案。

1) .terminal：当前会话客户所对应的终端的标识符

2) .language：语言

3) .db_name：当前数据库名称

4) .mls_date_format：当前会话客户所对应的日期格式

5) .session_user：当前会话客户所对应的数据库用户名

6) .current_schema：当前会话客户所对应的默认方案名

7) .host：返回数据库所在主机名称

SQL>selectsys_context('userenv','language')fromdual;

SYS_CONTEXT('USERENV','LANGUAG

----------------------------------------------------------

SIMPLIFIEDCHINESE_CHINA.ZHS16GBK

实例这里有

http://xsb.itpub.net/post/419/51694