oracle deul,Oracle PL/SQL 操作（三）Oracle函数

(1)SYSDATE

该函数返回当前的日期和时间。返回的是Oracle服务器的当前日期和时间。

select

sysdate

from

dual;

insert

into

purchase

values

(‘Small Widget’,’SH’,sysdate, 10);

insert

into

purchase

values

查看最近30天的所有销售记录，使用如下命令：

select

*

from

purchase

where

purchase_date

between

(sysdate-30)

and

(2)USER

查看用户名。

select

user

from

(3)USERENV

查看用户环境的各种资料。

select

userenv(‘TERMINAL’)

FROM

2.数值函数

(1)ROUND四舍五入函数

ROUND(数值，保留位数)

select

round(3.1415,3)

from

deul;

select

product_name,round(product_price,0) price

from

(2)TRUNC从数中截去小数部分

TRUNC(数值，截断小数点n位后的数)

select

trunc(3.145159,3)

from

dual;

select

trunc(123456.45,-1)

from

dual;

select

trunc(123456.45)

from

dual;

select

product_name,trunc(product_price) price

from

3.文本函数

(1)UPPER、LOWER和INITCAP

这三个函数更改提供给它们的文体的大小写。

select

upper

(product_name)

from

product;

select

lower

(product_name)

from

product;

select

initcap(product_name)

from

函数INITCAP能够整理杂乱的文本，如下：

select

initcap(‘this

TEXT

hAd UNpredictABLE

caSE

’)

from

(2)LENGTH

求数据库列中的数据所占的长度。

select

product_name,length(product_name) name_length

from

product

order

by

(3)SUBSTR

取子串，格式为：

SUBSTR(源字符串，起始位置，子串长度)；

create

table

item_test(item_id

char

(20),item_desc

char

(25));

insert

into

item_test

values

(‘LA-101’,’Can, Small’);

insert

into

item_test

values

(‘LA-102’,’Bottle, Small’);

insert

into

item_test

values

取编号：

select

substr(item_id,4,3) item_num,item_desc

from

(4)INSTR

确定子串在字符串中的位置，格式如下：

INSTR(源字符串，要查找的字符串，查找起始位置)

select

instr(‘this

is

line one’,’line’,1)

from

其返回值为子串在源字符串中从起始位置开始第一次出现的位置。上面例子的返回值为9。

select

item_desc , instr(item_desc,’,’,1)

from

(5)LTRIM、RTRIM和TRIM

去除字符串左边的空格、去除字符串右边的空格、去除字符串左右两边的空格。

select

ltrim(‘ abc def ‘)

from

4.日期函数

(1)SYSDATE和TRUNC

这两个函数前面已经出现过，即取Oracle服务器系统的日期和截掉小数部分的功能。观察以下操作：

create

table

test_date (name

varchar

2(20), p_date

date

);

insert

into

test_date

values

(‘name1’,sysdate);

select

*

from

test_date;

select

*

from

test_date

where

p_date=’25-10月-05‘;

select

*

from

test_date

where

Oracle系统中用SYSDATE取得的不仅包含日期而且还包含的有时间信息，时间信息实际上就是表示儒略日数据中的小数部分。

(2)ADD_MONTHS

该函数返回一个具有与所提供日期相差月份的日期，函数中给出了未来或以前的月份数。语法如下：

ADD_MONTHS(起始日期，增减月数)

select

add_months(’26-10月-05‘,2)

from

dual;

select

add_months(’26-10月-05‘,-2)

from

(3)LAST_DAY

返回包含给定日期的那个月的最后一天。语法为：

LAST_DAY(日期)

select

last_day(’21-2月-80‘)

from

(4)MONTHS_BETWEEN

返回两个日期间的月份。语法为：

MONTHS_BETWEEN(较晚日期，较早日期)

select

months_between(’12-10月-05‘,’12-9月-03‘)

from

5.数据转换函数

(1)TO_CHAR

该函数将日期、时间和数值转换为文本。它的主要价值是提供对日期、时间和数的显示控制；该函数的用法非常灵活，使用较复杂，在此我们仅对经常使用的转换举例说明。

1)格式化日期和时间值

TO_CHAR(日期数据，格式编码)

select

to_char(sysdate,’YYYY-MM-DD HH24:MI:SS’)

from

dual;

select

to_char(sysdate,’HH24:MI:SS’)

from

dual;

select

to_char(sysdate,‘YYYY’)||‘年’|| to_char(sysdate,'MM')||'月'|| to_char(sysdate,'DD')||'日' 日期

from

2)格式化数值

select

to_char(5764.12345,'99,999.9999')

from

dual;

select

(2)TO_DATE

将文本转换为实际的Oracle日期/时间值。格式：

TO_DATE(文本，日期格式)

select

to_date(‘2005-10-10 11’,’YYYY-MM-DD HH24’)

from

dual;

insert

into

item_test

values

6.其它函数

NVL函数完成一个简单但有用的功能。任何时候给它一个空值，它都返回一个你所选择的值。格式如下：

NAL(输入值，如果输入值为空要返回的值)

select

nvl(

null

,’new_value’)

from

dual;

select

name,nvl(p_date,sysdate)

from

注意：函数中‘输入值’和‘如果输入值为空要返回的值’的类型必须一致。

7.常用的分组函数

(1)SUM

该函数累加值并返回总数。

select

sum

(quantity)

from

purchase;

select

sum

(sal)

from

(2)COUNT

该函数对记录进行统计。

select

count

(*)

from

这个命令有缺点，一般不应该使用。如果一个表有数十万条记录，强制进行全表扫描将会使计数结果的返回非常慢，会降低其他业务工作的处理效率。因些COUNT函数对指定进行单列统计效果会好得多。

select

count

(ename)

from

emp;

select

count

(1)

from

(3)AVG

该函数返回指定列中值的平均数。使用这个函数必须给出相应的列名，且相应列应为数值类型。

select

avg

(sal)

from

(4)MIN

该函数返回作为参数给出的某列中的最小值。

select

min

(sal)

from

(5)MAX 用法与MIN相似

select

max

(sal)

from

8.利用GROUP BY子句分组数据

完成下列操作：

create

table

tb1( name

varchar

2(10),empno number(3),sal number(4) );

insert

into

tb1

values

(‘name-a’,1,1200);

insert

into

tb1

values

(‘name-b’,1,1500);

insert

into

tb1

values

(‘name-c’,1,2100);

insert

into

tb1

values

(‘name-d’,2,900);

insert

into

tb1

values

(‘name-e’,2,1300);

select

empno,

sum

(sal)

from

tb1

group

by

empno;

select

empno,

avg

(sal),

min

(sal),

max

(sal),

count

(empno)

from

tb1

group

by

9.利用HAVING子句过滤分组的行

select

empno,

sum

(sal),

avg

(sal),

min

(sal),

max

(sal),

count

(empno)

from

test

group

by

empno

having

sum