Oracle functions

最新推荐文章于 2025-09-30 10:59:01 发布

最新推荐文章于 2025-09-30 10:59:01 发布 · 155 阅读

文章标签：

#Oracle

DataBase 专栏收录该内容

14 篇文章

订阅专栏

本文提供了 Oracle 9i 数据库中多种常用函数的实际应用案例，包括字符串处理、数学运算、日期操作等，有助于理解这些函数的具体用法。

Examples of function in oracle 9i,pls check the defination of them if not clear about it.

1.select ascii('A') A,ascii('a') a,ascii(0) zero,ascii(' ') space from dual

65	97	48	32

2.select chr(54740) zhao,chr(65) chr65 from dual

赵	A

3.select concat('0557-','88888888')||'-1234' tel from dual

0557-88888888-1234

4.select initcap('smith') upp from dual

Smith

5.select instr('oracle traning','ra',1,1) instring1,instr('oracle traning','ra',1,2) instring2 from dual

2	9

6.select length('length') from dual

6

7.select lower('ABcCdD') from dual

abccdd

8.select upper('ABcCdD') from dual

ABCCDD

9.select lpad(rpad('gao',10,'*'),17,'&') from dual

&&&&&&&gao*******

10.select rtrim('oracleleleylele','le') from dual

oracleleley

select ltrim('leeleleoracle','le') from dual

oracle

11.select substr('oracle',2,2) from dual

ra

12.select replace('No one love you','No one','I') from dual

I love you

13.select trim('  a bc d d ') from dual

a bc d d

14.select abs(-1) from dual

1

15.select acos(-1) from dual

3.14159265358979

16.select ceil(7.8) from dual

8

17.select exp(1),exp(2) from dual

2.71828182845905	7.38905609893065

18.select floor(7.8) from dual

7

19.select log(2,1),log(2,8) from dual

0       3

20.select mod(2,1),mod(2,8),mod(5,3) from dual

1	0	2	2

21.select power(2,10) from dual

1024

22.select trunc(55.4), trunc(55.5), trunc(-5.4), trunc(-5.5) from dual

55	55	-5	-5

select trunc(123.123,-2),trunc(123.123,2) from dual

100	123.12

select round(55.4), round(55.5), round(-5.4), round(-5.5) from dual

55	56	-5	-6

23.select sign(10),sign(-10),sign(0) from dual

1	-1	0

24.select sqrt(10),sqrt(9),sqrt(0) from dual

3.16227766016838	3	0

25.select to_char(add_months(to_date('200910','yyyymm'),2),'yyyymm') from dual

200912

26.select last_day(sysdate) from dual

2009-12-31 10:27:38

27.select months_between(to_date('19990202','yyyymmdd'),to_date('19990902','yyyymmdd')) from dual

-7

28.select next_day(to_date('20091218','yyyymmdd'),'星期五') from dual

2009-12-25

29.select rowid,rowidtochar(rowid),a.log_content from t_log a

AAAHYnAAJAAAAD4AAA	AAAHYnAAJAAAAD4AAA	haha
AAAHYnAAJAAAAD4AAB	AAAHYnAAJAAAAD4AAB	hahaho

30.select to_multi_byte('gao') from dual

ｇａｏ

31.select to_number('2012') from dual

2012

32.select greatest('2012','2000','2011') from dual

2012

33.select least('2012','2000','2011') from dual

2000

34.select user,uid from dual

JIL	59

35.select userenv('language') from dual

SIMPLIFIED CHINESE_CHINA.ZHS16GBK

36.select (case when 1=2 then 0 else 1 end) from dual

1

37.select nvl(null,2) from dual

2

select nvl(1,2) from dual

1

38.select decode(sign(1+2),-1,2,1,0) from dual

0

select decode(sign(1-2),-1,2,1,0) from dual

2