Oracle SQL 內置函數大全

<<Oracle SQL 內置函數大全>>幻燈片

  1

SQL中的單記錄函數

  2

1

.

ASCII

  3

傳回與指定的字元對應的十進制數;

  4

SQL

>

select

ascii

(

'

A

'

) A,

ascii

(

'

a

'

) a,

ascii

(

'

'

) zero,

ascii

(

'

'

) 

space

from

 dual;

  5

  6

        A         A      ZERO     

SPACE

  7

--

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

  8

65

97

48

32

  9

 10

 11

2

.CHR

 12

給出整數,傳回對應的字元;

 13

SQL

>

select

 chr(

54740

) zhao,chr(

65

) chr65 

from

 dual;

 14

 15

ZH C

 16

--

 -

 17

趙 A

 18

 19

3

.CONCAT

 20

連接配接兩個字元串;

 21

SQL

>

select

 concat(

'

010-

'

,

'

88888888

'

)

||

'

轉23

'

  高乾競電話 

from

 dual;

 22

 23

高乾競電話

 24

--

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

 25

010

-

88888888轉23

 26

 27

4

.INITCAP

 28

傳回字元串并将字元串的第一個字母變為大寫;

 29

SQL

>

select

 initcap(

'

smith

'

) upp 

from

 dual;

 30

 31

UPP

 32

--

---

 33

Smith

 34

 35

 36

5

.INSTR(C1,C2,I,J)

 37

在一個字元串中搜尋指定的字元,傳回發現指定的字元的位置;

 38

C1    被搜尋的字元串

 39

C2    希望搜尋的字元串

 40

I     搜尋的開始位置,預設為1

 41

J     出現的位置,預設為1

 42

SQL

>

select

 instr(

'

oracle traning

'

,

'

ra

'

,

1

,

2

) instring 

from

 dual;

 43

 44

 INSTRING

 45

--

-------

 46

9

 47

 48

 49

6

.LENGTH

 50

傳回字元串的長度;

 51

SQL

>

select

 name,length(name),addr,length(addr),sal,length(to_char(sal)) 

from

 gao.nchar_tst;

 52

 53

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

 54

--

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

 55

高乾競            

3

 北京市海錠區                

6

9999.99

7

 56

 57

 58

 59

7

.

LOWER

 60

傳回字元串,并将所有的字元小寫

 61

SQL

>

select

lower

(

'

AaBbCcDd

'

)AaBbCcDd 

from

 dual;

 62

 63

AABBCCDD

 64

--

------

 65

aabbccdd

 66

 67

 68

8

.

UPPER

 69

傳回字元串,并将所有的字元大寫

 70

SQL

>

select

upper

(

'

AaBbCcDd

'

) 

upper

from

 dual;

 71

 72

UPPER

 73

--

------

 74

AABBCCDD

 75

 76

 77

 78

9

.RPAD和LPAD(粘貼字元)

 79

RPAD  在列的右邊粘貼字元

 80

LPAD  在列的左邊粘貼字元

 81

SQL

>

select

 lpad(rpad(

'

gao

'

,

10

,

'

*

'

),

17

,

'

*

'

)

from

 dual;

 82

 83

LPAD(RPAD(

'

GAO

'

,

1

 84

--

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

 85

*******

gao

*******

 86

不夠字元則用

*

來填滿

 87

 88

 89

10

.LTRIM和RTRIM

 90

LTRIM

  删除左邊出現的字元串

 91

RTRIM

  删除右邊出現的字元串

 92

SQL

>

select

ltrim

(

rtrim

(

'

   gao qian jing   

'

,

'

'

),

'

'

) 

from

 dual;

 93

 94

LTRIM

(

RTRIM

(

'

 95

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

 96

gao qian jing

 97

 98

 99

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

XM

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

              4

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

           1

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

          9

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

TO

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

GR

527

--

528

AC

529

SQL> select greatest(

'

啊

'

,

'

安

'

,

'

天

'

) from dual;

530

531

GR

532

--

533

天

534

535

536

56.LEAST

537

傳回一組表達式中的最小值 

538

SQL> select least(

'

啊

'

,

'

安

'

,

'

天

'

) from dual;

539

540

LE

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

                  1

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