# oralce函数大全连载（一）A-I

## ABS

SQL> select abs(100),abs(-100) from dual;

ABS(100) ABS(-100)
--------- ---------
100       100

## ACOS

SQL> select acos(-1) from dual;

ACOS(-1)
---------
3.1415927

TO_CHA
------
200002

TO_CHA
------
199910

## Ascii

SQL> select ascii('A') A,ascii('a') a,ascii('0') zero,ascii(' ') space from dual;

A         A      ZERO     SPACE
--------- --------- --------- ---------
65        97        48        32

## ASIN

SQL> select asin(0.5) from dual;

ASIN(0.5)
---------
.52359878

## ATAN

SQL> select atan(1) from dual;

ATAN(1)
---------
.78539816

## ATAN2

Atan2(n,m)：该函数用于返回数字n除以数字m的反正切值。输入值除了m不能为0外，可以是任意数字(m不能为0)，输出值的单位为弧度。

## BFILENAME(dir,file)

SQL>insert into file_tb1 values(bfilename('lob_dir1','image1.gif'));

## CEIL

SQL> select ceil(3.1415927) from dual;

CEIL(3.1415927)
---------------
4

## CHARTOROWID

SELECT test_id from test_case where rowid=CHARTORWID(''AAAA0SAACAAAALiAAA'')

## CHR

SQL> select chr(54740) zhao,chr(65) chr65 from dual;

ZH C

-- -

A

## CONCAT

SQL> select concat('010-','88888888')||'23'  高乾竞电话 from dual

----------------
010-88888888
23

## CONVERT(c,dset,sset)

SQL> select convert('strutz','we8hp','f7dec') "conversion" from dual;

conver
------
strutz

## COS

SQL> select cos(-3.1415927) from dual;

COS(-3.1415927)
---------------
-1

## COSH

select COSH(<1.4>) FROM dualCOSH(1.4)2.15089847

SQL> select cosh(20) from dual;

COSH(20)
---------
242582598

## COUNT

COUNT({*|DISTINCT|ALL} )

select area_id,min(area_description),count(area_id) from code_city

group by area_id

order by count(*) desc

SQL> select count(null) from emp;

COUNT(NULL)

-----------

0

SQL> select count('') from emp;

COUNT('')

----------

0

SQL> select count('1') from emp;

COUNT('1')

----------

7

Select count(*) ---slow

Count(1),--fast

Count(rowid)—fast

From emp;

## DECODE

x是一个表达式，m1是一个匹配表达式，xm1比较，如果m1等于x，那么返回r1,否则,xm2比较，依次类推m3,m4,m5....直到有返回结果。

## DUMP(s,fmt,start,length)

DUMP函数以fmt指定的内部数字格式返回一个VARCHAR2类型的值

SQL> col global_name for a30
SQL> col dump_string for a50
SQL> set lin 200
SQL> select global_name,dump(global_name,1017,8,5) dump_string from global_name;

GLOBAL_NAME                    DUMP_STRING
------------------------------ --------------------------------------------------
ORACLE.WORLD                   Typ=1 Len=12 CharacterSet=ZHS16GBK: W,O,R,L,D

## EXP

SQL> select exp(2),exp(1) from dual;

EXP(2)    EXP(1)
--------- ---------
7.3890561 2.7182818

## EXTRACT

Sql>select extract(year from sysdate) from dual;

## FLOOR

SQL> select floor(2345.67) from dual;

FLOOR(2345.67)
--------------
2345

## FROM_TZ

SQL>select  from_tz(timestamp'2003-03-28 08:00:00','3:00') from dual;

28-3 -03 08.00.00.000000000 上午 +03:00

## GREATEST

SQL> select greatest('AA','AB','AC') from dual;

GR
--
AC
SQL> select greatest('
','','') from dual;

GR
--

## INITCAP

SQL> select initcap('smith') upp from dual;

UPP
-----
Smith

## INSTR(C1,C2,I,J)

C1    被搜索的字符串
C2

I

J

SQL> select instr('oracle traning','ra',1,2) instring from dual;

INSTRING
---------
9

## INSTRB

INSTR（）函数一样，只是他返回的是字节，对于单字节INSTRB()等于INSTR()

• 博文量
39
• 访问量
86794