Kevin的技術家園

conver

------

strutz

44.HEXTORAW 將一個十六進制構成的字符串轉換為二進制

45.RAWTOHEXT 將一個二進制構成的字符串轉換為十六進制

46.ROWIDTOCHAR 將ROWID數據類型轉換為字符類型

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') 將字符串轉化為ORACLE中的一個日期

49.TO_MULTI_BYTE? 將字符串中的單字節字符轉化為多字節字符

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

TO

--

高

50.TO_NUMBER

將給出的字符轉換為數字

SQL> select 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> 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函數以fmt指定的內部數字格式返回一個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()

這兩個函數都是用來對大數據類型字段進行初始化操作的函數

55.GREATEST

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

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

GR

--

AC

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

GR

--

天

56.LEAST

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

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

LE

--

啊

57.UID

返回標識當前用戶的唯一整數

SQL> show user

USER 為"GAO"

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

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

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

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

58.USER

返回當前用戶的名字

SQL> select user from? dual;

USER

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

GAO

59.USEREVN

返回當前用戶環境的信息,opt可以是:

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

ISDBA? 查看當前用戶是否是DBA如果是則返回true

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

USEREN

------

FALSE

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

USEREN

------

TRUE

SESSION

返回會話標志

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

USERENV('SESSIONID')

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

152

ENTRYID

返回會話人口標志

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

USERENV('ENTRYID')

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

0

INSTANCE

返回當前INSTANCE的標志

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

USERENV('INSTANCE')

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

1

LANGUAGE

返回當前環境變量

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

USERENV('LANGUAGE')

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

SIMPLIFIED CHINESE_CHINA.ZHS16GBK

LANG

返回當前環境的語言的縮寫

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

USERENV('LANG')

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

ZHS

TERMINAL

返回用戶的終端或機器的標志

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

USERENV('TERMINA

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

GAO

VSIZE(X)

返回X的大小(字節)數

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

VSIZE(USER) USER

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

6 SYSTEM