oracle系统函数

来源：互联网发布：kindle软件破解版编辑：程序博客网时间：2024/06/06 04:30

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

--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的长度

实例：

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

select lower(ename) name,job from emp;

NAME JOB

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

smith CLERK

allen SALESMAN

ward SALESMAN

...

14 rows selected

?将所有员工的名字按大写的方式显示：
select upper(ename) from emp order by ename;

UPPER(ENAME)

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

ADAMS

ALLEN

BLAKE

...

14 rows selected

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

select ename,job,sal from emp where length(ename)=5;

ENAME JOB SAL

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

SMITH CLERK 800.00

ALLEN SALESMAN 1600.00

JONES MANAGER 2975.00

...

8 rows selected

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

select substr(ename,1,3) 姓名 from emp;

姓名

------

SMI

ALL

WAR

...

14 rows selected

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

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

姓名

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

Smith

Allen

Ward

...

14 rows selected

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

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

姓名

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

sMITH

aLLEN

wARD

...

14 rows selected

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

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

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

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

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

select replace(ename,'A','我是大啊') 替换后的姓名 from emp;

替换后的姓名

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

SMITH

我是大啊LLEN

W我是大啊RD

M我是大啊RTIN

BL我是大啊KE

SCOTT

我是大啊D我是大啊MS

J我是大啊MES

...

14 rows selected

♢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位前。

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

select ename ,(round(sal)+round(comm))*13 年薪,sal from emp where comm is not null;

ENAME 年薪 SAL

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

ALLEN 24700 1600.00

WARD 22750 1250.00

MARTIN 34450 1250.00

TURNER 19500 1500.00

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

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

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

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

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

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

select trunc(sal/30),ename from emp;

TRUNC(SAL/30) ENAME

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

26 SMITH

53 ALLEN

41 WARD

...

14 rows selected

select floor(sal/30),ename from emp;

结果与上面的相同

其他数学函数：

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

介绍

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

1.sysdate：返回系统时间

2.add_months(d,n):

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

select ename,job,sal from emp where sysdate>add_months(hiredate,300)

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

select ename,job,sal from emp where sysdate>add_months(hiredate,240)

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

select trunc(sysdate-hiredate) 入职天数,ename 姓名 from emp;

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

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

select hiredate,ename from emp where last_day(hiredate)-2=hiredate;

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

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

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

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

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

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0

Connected as scott

居然连接成功了！

SQL> show user

User is "scott"

再次验证：

select * from emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

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

7369 SMITH CLERK 7902 1980-12-17 800.00 20

7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30

.......

14 rows selected

四．转换函数

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

例如：

create table t1（id int）;

insert into t1 values（'10'）--这样oracle会自动的将'10'转换为10；

create table t2（id varchar2(10)）;

insert into t2 values（1）--这样oracle就会自动的将1转换为'1';

1.to_char函数

to_char

插入一条数据：

SQL> insert into emp values(8881,'test用户','MANAGER',7782,sysdate,23,23,10);

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

SQL> select ename,to_char(hiredate,'yyyy-mm-dd hh24:mi:ss') from emp;

ENAME TO_CHAR(HIREDATE,'YYYY-MM-DDHH

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

test用户 2010-12-21 16:03:33

SMITH 1980-12-17 00:00:00

ALLEN 1981-02-20 00:00:00

......

15 rows selected

这里说一下日期格式：

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> select ename,to_char(hiredate,'yyyy-mm-dd hh24:mi:ss'),to_char(sal,'L99,999.99') from emp;

ENAME TO_CHAR(HIREDATE,'YYYY-MM-DDHH TO_CHAR(SAL,'L99,999.99')

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

test用户 2010-12-21 16:03:33 ￥23.00

SMITH 1980-12-17 00:00:00 ￥800.00

ALLEN 1981-02-20 00:00:00 ￥1,600.00

......

15 rows selected

？显示1980年入职的员工

SQL> select * from emp where to_char(hiredate,'yyyy')=1980;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

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

7369 SMITH CLERK 7902 1980-12-17 800.00 20

2.to_date函数

to_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> select sys_context('userenv','language') from dual;

SYS_CONTEXT('USERENV','LANGUAG

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

SIMPLIFIED CHINESE_CHINA.ZHS16GBK

实例这里有

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