OracleSQL内置函数大全之三

文章作者 100test 发表时间 2007:03:14 13:51:06
来源 100Test.Com百考试题网

48.to_date(string,format) 将字符串转化为oracle中的一个日期

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

sql> 0select to_multi_byte(高) from dual.

to

--

高

50.to_number

将给出的字符转换为数字

sql> 0select to_number(1999) year from dual.

year

---------

1999

51.bfilename(dir,file)指定一个外部二进制文件

sql>insert into file_tb1 values(bfilename(lob_dir1,image1.gif)).

52.convert(x,desc,source) 将x字段或变量的源source转换为desc

sql> 0select sid,serial#,username,decode(command,

2 0,none,

3 2,insert,

4 3,

5 0select,

6 6,0update,

7 7,0delete,

8 8,0drop,

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

sid serial# username cmd

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

1 1 none

2 1 none

3 1 none

4 1 none

5 1 none

6 1 none

7 1275 none

8 1275 none

9 20 gao 0select

10 40 gao none

53.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> 0select 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

54.empty_blob()和empty_clob()

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

55.greatest

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

sql> 0select greatest(aa,ab,ac) from dual.

gr

--

ac

sql> 0select greatest(啊,安,天) from dual.

gr

--

天

56.least

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

sql> 0select least(啊,安,天) from dual.

le

--

啊

57.uid

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

sql> show user

user 为"gao"

sql> 0select username,user_id from dba_users where user_id=uid.

username user_id

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

gao 25

58.user

返回当前用户的名字

sql> 0select user from dual.

user

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

gao

59.userevn

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

entryid,sessionid,terminal,isdba,lable,language,client_info,lang,vsize

isdba 查看当前用户是否是dba如果是则返回true

sql> 0select userenv(isdba) from dual.

useren

------

false

sql> 0select userenv(isdba) from dual.

useren

------

true

session

返回会话标志

sql> 0select userenv(sessionid) from dual.

userenv(sessionid)

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

152

entryid

返回会话人口标志

sql> 0select userenv(entryid) from dual.

userenv(entryid)

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

0

instance

返回当前instance的标志

sql> 0select userenv(instance) from dual.

userenv(instance)

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

1

language

返回当前环境变量

sql> 0select userenv(language) from dual.

userenv(language)

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

simplified chinese_china.zhs16gbk

lang

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

sql> 0select userenv(lang) from dual.

userenv(lang)

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

zhs

terminal

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

sql> 0select userenv(terminal) from dual.

userenv(termina

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

gao

vsize(x)

返回x的大小(字节)数

sql> 0select vsize(user),user from dual.

vsize(user) user

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

6 system