Oracle SQL 內置函數大全

<<Oracle SQL 內置函數大全>>幻灯片:http://www.cnblogs.com/Files/Dicky/Oracle_SQL_Function.rar

SQL中的单记录函数

1.ASCII

返回与指定的字符对应的十进制数;

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

A A ZERO SPACE

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

65 97 48 32

2.CHR

给出整数,返回对应的字符;

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

ZH C

-- -

赵 A

3.CONCAT

连接两个字符串;

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

高乾竞电话

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

010-88888888转23

4.INITCAP

返回字符串并将字符串的第一个字母变为大写;

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

UPP

-----

Smith

5.INSTR(C1,C2,I,J)

在一个字符串中搜索指定的字符,返回发现指定的字符的位置;

C1 被搜索的字符串

C2 希望搜索的字符串

I 搜索的开始位置,默认为1

J 出现的位置,默认为1

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

INSTRING

---------

6.LENGTH

返回字符串的长度;

SQL> select name,length(name),addr,length(addr),sal,length(to_char(sal)) from gao.nchar_tst;

NAME LENGTH(NAME) ADDR LENGTH(ADDR) SAL LENGTH(TO_CHAR(SAL))

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

高乾竞 3 北京市海锭区 6 9999.99 7

7.LOWER

返回字符串,并将所有的字符小写

SQL> select lower('AaBbCcDd')AaBbCcDd from dual;

AABBCCDD

--------

aabbccdd

8.UPPER

返回字符串,并将所有的字符大写

SQL> select upper('AaBbCcDd') upper from dual;

UPPER

--------

AABBCCDD

9.RPAD和LPAD(粘贴字符)

RPAD 在列的右边粘贴字符

LPAD 在列的左边粘贴字符

SQL> select lpad(rpad('gao',10,'*'),17,'*')from dual;

LPAD(RPAD('GAO',1

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

*******gao*******

不够字符则用*来填满

10.LTRIM和RTRIM

LTRIM 删除左边出现的字符串

RTRIM 删除右边出现的字符串

SQL> select ltrim(rtrim(' gao qian jing ',' '),' ') from dual;

LTRIM(RTRIM('

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

gao qian jing

11.SUBSTR(string,start,count)

100

取子字符串,从start开始,取count个

101

SQL> select substr('13088888888',3,8) from dual;

102

103

SUBSTR('

104

--------

105

08888888

106

107

108

12.REPLACE('string','s1','s2')

109

string 希望被替换的字符或变量

110

s1 被替换的字符串

111

s2 要替换的字符串

112

SQL> select replace('he love you','he','i') from dual;

113

114

REPLACE('H

115

----------

116

i love you

117

118

119

13.SOUNDEX

120

返回一个与给定的字符串读音相同的字符串

121

SQL> create table table1(xm varchar(8));

122

SQL> insert into table1 values('weather');

123

SQL> insert into table1 values('wether');

124

SQL> insert into table1 values('gao');

125

126

SQL> select xm from table1 where soundex(xm)=soundex('weather');

127

128

129

--------

130

weather

131

wether

132

133

134

14.TRIM('s' from 'string')

135

LEADING 剪掉前面的字符

136

TRAILING 剪掉后面的字符

137

如果不指定,默认为空格符

138

139

15.ABS

140

返回指定值的绝对值

141

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

142

143

ABS(100) ABS(-100)

144

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

145

100 100

146

147

148

16.ACOS

149

给出反余弦的值

150

SQL> select acos(-1) from dual;

151

152

ACOS(-1)

153

---------

154

3.1415927

155

156

157

17.ASIN

158

给出反正弦的值

159

SQL> select asin(0.5) from dual;

160

161

ASIN(0.5)

162

---------

163

.52359878

164

165

166

18.ATAN

167

返回一个数字的反正切值

168

SQL> select atan(1) from dual;

169

170

ATAN(1)

171

---------

172

.78539816

173

174

175

19.CEIL

176

返回大于或等于给出数字的最小整数

177

SQL> select ceil(3.1415927) from dual;

178

179

CEIL(3.1415927)

180

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

181

182

183

184

20.COS

185

返回一个给定数字的余弦

186

SQL> select cos(-3.1415927) from dual;

187

188

COS(-3.1415927)

189

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

190

-1

191

192

193

21.COSH

194

返回一个数字反余弦值

195

SQL> select cosh(20) from dual;

196

197

COSH(20)

198

---------

199

242582598

200

201

202

22.EXP

203

返回一个数字e的n次方根

204

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

205

206

EXP(2) EXP(1)

207

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

208

7.3890561 2.7182818

209

210

211

23.FLOOR

212

对给定的数字取整数

213

SQL> select floor(2345.67) from dual;

214

215

FLOOR(2345.67)

216

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

217

2345

218

219

220

24.LN

221

返回一个数字的对数值

222

SQL> select ln(1),ln(2),ln(2.7182818) from dual;

223

224

LN(1) LN(2) LN(2.7182818)

225

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

226

0 .69314718 .99999999

227

228

229

25.LOG(n1,n2)

230

返回一个以n1为底n2的对数

231

SQL> select log(2,1),log(2,4) from dual;

232

233

LOG(2,1) LOG(2,4)

234

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

235

0 2

236

237

238

26.MOD(n1,n2)

239

返回一个n1除以n2的余数

240

SQL> select mod(10,3),mod(3,3),mod(2,3) from dual;

241

242

MOD(10,3) MOD(3,3) MOD(2,3)

243

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

244

1 0 2

245

246

247

27.POWER

248

返回n1的n2次方根

249

SQL> select power(2,10),power(3,3) from dual;

250

251

POWER(2,10) POWER(3,3)

252

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

253

1024 27

254

255

256

28.ROUND和TRUNC

257

按照指定的精度进行舍入

258

SQL> select round(55.5),round(-55.4),trunc(55.5),trunc(-55.5) from dual;

259

260

ROUND(55.5) ROUND(-55.4) TRUNC(55.5) TRUNC(-55.5)

261

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

262

56 -55 55 -55

263

264

265

29.SIGN

266

取数字n的符号,大于0返回1,小于0返回-1,等于0返回0

267

SQL> select sign(123),sign(-100),sign(0) from dual;

268

269

SIGN(123) SIGN(-100) SIGN(0)

270

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

271

1 -1 0

272

273

274

30.SIN

275

返回一个数字的正弦值

276

SQL> select sin(1.57079) from dual;

277

278

SIN(1.57079)

279

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

280

281

282

283

31.SIGH

284

返回双曲正弦的值

285

SQL> select sin(20),sinh(20) from dual;

286

287

SIN(20) SINH(20)

288

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

289

.91294525 242582598

290

291

292

32.SQRT

293

返回数字n的根

294

SQL> select sqrt(64),sqrt(10) from dual;

295

296

SQRT(64) SQRT(10)

297

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

298

8 3.1622777

299

300

301

33.TAN

302

返回数字的正切值

303

SQL> select tan(20),tan(10) from dual;

304

305

TAN(20) TAN(10)

306

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

307

2.2371609 .64836083

308

309

310

34.TANH

311

返回数字n的双曲正切值

312

SQL> select tanh(20),tan(20) from dual;

313

314

TANH(20) TAN(20)

315

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

316

1 2.2371609

317

318

319

320

35.TRUNC

321

按照指定的精度截取一个数

322

SQL> select trunc(124.1666,-2) trunc1,trunc(124.16666,2) from dual;

323

324

TRUNC1 TRUNC(124.16666,2)

325

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

326

100 124.16

327

328

329

330

36.ADD_MONTHS

331

增加或减去月份

332

SQL> select to_char(add_months(to_date('199912','yyyymm'),2),'yyyymm') from dual;

333

334

TO_CHA

335

------

336

200002

337

SQL> select to_char(add_months(to_date('199912','yyyymm'),-2),'yyyymm') from dual;

338

339

TO_CHA

340

------

341

199910

342

343

344

37.LAST_DAY

345

返回日期的最后一天

346

SQL> select to_char(sysdate,'yyyy.mm.dd'),to_char((sysdate)+1,'yyyy.mm.dd') from dual;

347

348

TO_CHAR(SY TO_CHAR((S

349

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

350

2004.05.09 2004.05.10

351

SQL> select last_day(sysdate) from dual;

352

353

LAST_DAY(S

354

----------

355

31-5月 -04

356

357

358

38.MONTHS_BETWEEN(date2,date1)

359

给出date2-date1的月份

360

SQL> select months_between('19-12月-1999','19-3月-1999') mon_between from dual;

361

362

MON_BETWEEN

363

-----------

364

365

SQL>selectmonths_between(to_date('2000.05.20','yyyy.mm.dd'),to_date('2005.05.20','yyyy.mm.dd')) mon_betw from dual;

366

367

MON_BETW

368

---------

369

-60

370

371

372

39.NEW_TIME(date,'this','that')

373

给出在this时区=other时区的日期和时间

374

SQL> select to_char(sysdate,'yyyy.mm.dd hh24:mi:ss') bj_time,to_char(new_time

375

2 (sysdate,'PDT','GMT'),'yyyy.mm.dd hh24:mi:ss') los_angles from dual;

376

377

BJ_TIME LOS_ANGLES

378

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

379

2004.05.09 11:05:32 2004.05.09 18:05:32

380

381

382

40.NEXT_DAY(date,'day')

383

给出日期date和星期x之后计算下一个星期的日期

384

SQL> select next_day('18-5月-2001','星期五') next_day from dual;

385

386

NEXT_DAY

387

----------

388

25-5月 -01

389

390

391

392

41.SYSDATE

393

用来得到系统的当前日期

394

SQL> select to_char(sysdate,'dd-mm-yyyy day') from dual;

395

396

TO_CHAR(SYSDATE,'

397

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

398

09-05-2004 星期日

399

trunc(date,fmt)按照给出的要求将日期截断,如果fmt='mi'表示保留分,截断秒

400

SQL> select to_char(trunc(sysdate,'hh'),'yyyy.mm.dd hh24:mi:ss') hh,

401

2 to_char(trunc(sysdate,'mi'),'yyyy.mm.dd hh24:mi:ss') hhmm from dual;

402

403

HH HHMM

404

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

405

2004.05.09 11:00:00 2004.05.09 11:17:00

406

407

408

409

42.CHARTOROWID

410

将字符数据类型转换为ROWID类型

411

SQL> select rowid,rowidtochar(rowid),ename from scott.emp;

412

413

ROWID ROWIDTOCHAR(ROWID) ENAME

414

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

415

AAAAfKAACAAAAEqAAA AAAAfKAACAAAAEqAAA SMITH

416

AAAAfKAACAAAAEqAAB AAAAfKAACAAAAEqAAB ALLEN

417

AAAAfKAACAAAAEqAAC AAAAfKAACAAAAEqAAC WARD

418

AAAAfKAACAAAAEqAAD AAAAfKAACAAAAEqAAD JONES

419

420

421

43.CONVERT(c,dset,sset)

422

将源字符串 sset从一个语言字符集转换到另一个目的dset字符集

423

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

424

425

conver

426

------

427

strutz

428

429

430

44.HEXTORAW

431

将一个十六进制构成的字符串转换为二进制

432

433

434

45.RAWTOHEXT

435

将一个二进制构成的字符串转换为十六进制

436

437

438

439

46.ROWIDTOCHAR

440

将ROWID数据类型转换为字符类型

441

442

443

444

47.TO_CHAR(date,'format')

445

SQL> select to_char(sysdate,'yyyy/mm/dd hh24:mi:ss') from dual;

446

447

TO_CHAR(SYSDATE,'YY

448

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

449

2004/05/09 21:14:41

450

451

452

453

48.TO_DATE(string,'format')

454

将字符串转化为ORACLE中的一个日期

455

456

457

49.TO_MULTI_BYTE

458

将字符串中的单字节字符转化为多字节字符

459

SQL> select to_multi_byte('高') from dual;

460

461

462

463

高

464

465

466

50.TO_NUMBER

467

将给出的字符转换为数字

468

SQL> select to_number('1999') year from dual;

469

470

YEAR

471

---------

472

1999

473

474

475

51.BFILENAME(dir,file)

476

指定一个外部二进制文件

477

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

478

479

480

52.CONVERT('x','desc','source')

481

将x字段或变量的源source转换为desc

482

SQL> select sid,serial#,username,decode(command,

483

2 0,'none',

484

3 2,'insert',

485

4 3,

486

5 'select',

487

6 6,'update',

488

7 7,'delete',

489

8 8,'drop',

490

9 'other') cmd from v$session where type!='background';

491

492

SID SERIAL# USERNAME CMD

493

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

494

1 1 none

495

2 1 none

496

3 1 none

497

4 1 none

498

5 1 none

499

6 1 none

500

7 1275 none

501

8 1275 none

502

9 20 GAO select

503

10 40 GAO none

504

505

506

53.DUMP(s,fmt,start,length)

507

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

508

SQL> col global_name for a30

509

SQL> col dump_string for a50

510

SQL> set lin 200

511

SQL> select global_name,dump(global_name,1017,8,5) dump_string from global_name;

512

513

GLOBAL_NAME DUMP_STRING

514

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

515

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

516

517

518

54.EMPTY_BLOB()和EMPTY_CLOB()

519

这两个函数都是用来对大数据类型字段进行初始化操作的函数

520

521

522

55.GREATEST

523

返回一组表达式中的最大值,即比较字符的编码大小.

524

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

525

526

527

528

529

SQL> select greatest('啊','安','天') from dual;

530

531

532

533

天

534

535

536

56.LEAST

537

返回一组表达式中的最小值

538

SQL> select least('啊','安','天') from dual;

539

540

541

542

啊

543

544

545

57.UID

546

返回标识当前用户的唯一整数

547

SQL> show user

548

USER 为"GAO"

549

SQL> select username,user_id from dba_users where user_id=uid;

550

551

USERNAME USER_ID

552

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

553

GAO 25

554

555

556

557

58.USER

558

返回当前用户的名字

559

SQL> select user from dual;

560

561

USER

562

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

563

GAO

564

565

566

59.USEREVN

567

返回当前用户环境的信息,opt可以是:

568

ENTRYID,SESSIONID,TERMINAL,ISDBA,LABLE,LANGUAGE,CLIENT_INFO,LANG,VSIZE

569

ISDBA 查看当前用户是否是DBA如果是则返回true

570

SQL> select userenv('isdba') from dual;

571

572

USEREN

573

------

574

FALSE

575

SQL> select userenv('isdba') from dual;

576

577

USEREN

578

------

579

TRUE

580

SESSION

581

返回会话标志

582

SQL> select userenv('sessionid') from dual;

583

584

USERENV('SESSIONID')

585

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

586

152

587

ENTRYID

588

返回会话人口标志

589

SQL> select userenv('entryid') from dual;

590

591

USERENV('ENTRYID')

592

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

593

594

INSTANCE

595

返回当前INSTANCE的标志

596

SQL> select userenv('instance') from dual;

597

598

USERENV('INSTANCE')

599

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

600

601

LANGUAGE

602

返回当前环境变量

603

SQL> select userenv('language') from dual;

604

605

USERENV('LANGUAGE')

606

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

607

SIMPLIFIED CHINESE_CHINA.ZHS16GBK

608

LANG

609

返回当前环境的语言的缩写

610

SQL> select userenv('lang') from dual;

611

612

USERENV('LANG')

613

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

614

ZHS

615

TERMINAL

616

返回用户的终端或机器的标志

617

SQL> select userenv('terminal') from dual;

618

619

USERENV('TERMINA

620

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

621

GAO

622

VSIZE(X)

623

返回X的大小(字节)数

624

SQL> select vsize(user),user from dual;

625

626

VSIZE(USER) USER

627

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

628

6 SYSTEM

629

630

631

632

60.AVG(DISTINCT|ALL)

633

all表示对所有的值求平均值,distinct只对不同的值求平均值

634

SQLWKS> create table table3(xm varchar(8),sal number(7,2));

635

语句已处理。

636

SQLWKS> insert into table3 values('gao',1111.11);

637

SQLWKS> insert into table3 values('gao',1111.11);

638

SQLWKS> insert into table3 values('zhu',5555.55);

639

SQLWKS> commit;

640

641

SQL> select avg(distinct sal) from gao.table3;

642

643

AVG(DISTINCTSAL)

644

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

645

3333.33

646

647

SQL> select avg(all sal) from gao.table3;

648

649

AVG(ALLSAL)

650

-----------

651

2592.59

652

653

654

61.MAX(DISTINCT|ALL)

655

求最大值,ALL表示对所有的值求最大值,DISTINCT表示对不同的值求最大值,相同的只取一次

656

SQL> select max(distinct sal) from scott.emp;

657

658

MAX(DISTINCTSAL)

659

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

660

5000

661

662

663

62.MIN(DISTINCT|ALL)

664

求最小值,ALL表示对所有的值求最小值,DISTINCT表示对不同的值求最小值,相同的只取一次

665

SQL> select min(all sal) from gao.table3;

666

667

MIN(ALLSAL)

668

-----------

669

1111.11

670

671

672

63.STDDEV(distinct|all)

673

求标准差,ALL表示对所有的值求标准差,DISTINCT表示只对不同的值求标准差

674

SQL> select stddev(sal) from scott.emp;

675

676

STDDEV(SAL)

677

-----------

678

1182.5032

679

680

SQL> select stddev(distinct sal) from scott.emp;

681

682

STDDEV(DISTINCTSAL)

683

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

684

1229.951

685

686

687

688

64.VARIANCE(DISTINCT|ALL)

689

求协方差

690

691

SQL> select variance(sal) from scott.emp;

692

693

VARIANCE(SAL)

694

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

695

1398313.9

696

697

698

65.GROUP BY

699

主要用来对一组数进行统计

700

SQL> select deptno,count(*),sum(sal) from scott.emp group by deptno;

701

702

DEPTNO COUNT(*) SUM(SAL)

703

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

704

10 3 8750

705

20 5 10875

706

30 6 9400

707

708

709

710

66.HAVING

711

对分组统计再加限制条件

712

SQL> select deptno,count(*),sum(sal) from scott.emp group by deptno having count(*)>=5;

713

714

DEPTNO COUNT(*) SUM(SAL)

715

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

716

20 5 10875

717

30 6 9400

718

SQL> select deptno,count(*),sum(sal) from scott.emp having count(*)>=5 group by deptno ;

719

720

DEPTNO COUNT(*) SUM(SAL)

721

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

722

20 5 10875

723

30 6 9400

724

725

726

67.ORDER BY

727

用于对查询到的结果进行排序输出

728

SQL> select deptno,ename,sal from scott.emp order by deptno,sal desc;

729

730

DEPTNO ENAME SAL

731

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

732

10 KING 5000

733

10 CLARK 2450

734

10 MILLER 1300

735

20 SCOTT 3000

736

20 FORD 3000

737

20 JONES 2975

738

20 ADAMS 1100

739

20 SMITH 800

740

30 BLAKE 2850

741

30 ALLEN 1600

742

30 TURNER 1500

743

30 WARD 1250

744

30 MARTIN 1250

745

30 JAMES 950