Kevin的技術(shù)家園

conver

------

strutz

44.HEXTORAW 將一個十六進(jìn)制構(gòu)成的字符串轉(zhuǎn)換為二進(jìn)制

45.RAWTOHEXT 將一個二進(jìn)制構(gòu)成的字符串轉(zhuǎn)換為十六進(jìn)制

46.ROWIDTOCHAR 將ROWID數(shù)據(jù)類型轉(zhuǎn)換為字符類型

47.TO_CHAR(date,'format')

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

TO_CHAR(SYSDATE,'YY

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

2004/05/09 21:14:41

48.TO_DATE(string,'format') 將字符串轉(zhuǎn)化為ORACLE中的一個日期

49.TO_MULTI_BYTE? 將字符串中的單字節(jié)字符轉(zhuǎn)化為多字節(jié)字符

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

TO

--

高

50.TO_NUMBER

將給出的字符轉(zhuǎn)換為數(shù)字

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

YEAR

---------

1999

51.BFILENAME(dir,file)指定一個外部二進(jìn)制文件

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

52.CONVERT('x','desc','source') 將x字段或變量的源source轉(zhuǎn)換為desc

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

2? 0,'none',

3? 2,'insert',

4? 3,

5? 'select',

6? 6,'update',

7? 7,'delete',

8? 8,'drop',

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??????????????????????????? select

10??????? 40 GAO??????????????????????????? none

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

DUMP函數(shù)以fmt指定的內(nèi)部數(shù)字格式返回一個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

54.EMPTY_BLOB()和EMPTY_CLOB()

這兩個函數(shù)都是用來對大數(shù)據(jù)類型字段進(jìn)行初始化操作的函數(shù)

55.GREATEST

返回一組表達(dá)式中的最大值,即比較字符的編碼大小.

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

GR

--

AC

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

GR

--

天

56.LEAST

返回一組表達(dá)式中的最小值

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

LE

--

啊

57.UID

返回標(biāo)識當(dāng)前用戶的唯一整數(shù)

SQL> show user

USER 為"GAO"

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

USERNAME???????????????????????? USER_ID

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

GAO?????????????????????????????????? 25

58.USER

返回當(dāng)前用戶的名字

SQL> select user from? dual;

USER

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

GAO

59.USEREVN

返回當(dāng)前用戶環(huán)境的信息,opt可以是:

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

ISDBA? 查看當(dāng)前用戶是否是DBA如果是則返回true

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

USEREN

------

FALSE

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

USEREN

------

TRUE

SESSION

返回會話標(biāo)志

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

USERENV('SESSIONID')

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

152

ENTRYID

返回會話人口標(biāo)志

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

USERENV('ENTRYID')

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

0

INSTANCE

返回當(dāng)前INSTANCE的標(biāo)志

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

USERENV('INSTANCE')

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

1

LANGUAGE

返回當(dāng)前環(huán)境變量

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

USERENV('LANGUAGE')

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

SIMPLIFIED CHINESE_CHINA.ZHS16GBK

LANG

返回當(dāng)前環(huán)境的語言的縮寫

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

USERENV('LANG')

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

ZHS

TERMINAL

返回用戶的終端或機(jī)器的標(biāo)志

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

USERENV('TERMINA

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

GAO

VSIZE(X)

返回X的大小(字節(jié))數(shù)

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

VSIZE(USER) USER

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

6 SYSTEM