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

60.AVG(DISTINCT|ALL)

all表示對所有的值求平均值,distinct只對不同的值求平均值

SQLWKS> create table table3(xm varchar(8),sal number(7,2));

語句已處理。

SQLWKS>? insert into table3 values('gao',1111.11);

SQLWKS>? insert into table3 values('gao',1111.11);

SQLWKS>? insert into table3 values('zhu',5555.55);

SQLWKS> commit;

SQL> select avg(distinct sal) from gao.table3;

AVG(DISTINCTSAL)

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

3333.33

SQL> select avg(all sal) from gao.table3;

AVG(ALLSAL)

-----------

2592.59

61.MAX(DISTINCT|ALL)

求最大值,ALL表示對所有的值求最大值,DISTINCT表示對不同的值求最大值,相同的只取一次

SQL> select max(distinct sal) from scott.emp;

MAX(DISTINCTSAL)

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

5000

62.MIN(DISTINCT|ALL)

求最小值,ALL表示對所有的值求最小值,DISTINCT表示對不同的值求最小值,相同的只取一次

SQL> select min(all sal) from gao.table3;

MIN(ALLSAL)

-----------

1111.11

63.STDDEV(distinct|all)

求標準差,ALL表示對所有的值求標準差,DISTINCT表示只對不同的值求標準差

SQL> select stddev(sal) from scott.emp;

STDDEV(SAL)

-----------

1182.5032

SQL> select stddev(distinct sal) from scott.emp;

STDDEV(DISTINCTSAL)

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

1229.951

64.VARIANCE(DISTINCT|ALL) 求協方差

SQL> select variance(sal) from scott.emp;

VARIANCE(SAL)

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

1398313.9

65.GROUP BY 主要用來對一組數進行統計

SQL> select deptno,count(*),sum(sal) from scott.emp group by deptno;

DEPTNO? COUNT(*)? SUM(SAL)

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

10???????? 3????? 8750

20???????? 5???? 10875

30???????? 6????? 9400

66.HAVING? 對分組統計再加限制條件

SQL> select deptno,count(*),sum(sal) from scott.emp group by deptno having nt(*)>=5;

DEPTNO? COUNT(*)? SUM(SAL)

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

20???????? 5???? 10875

30???????? 6????? 9400

SQL> select deptno,count(*),sum(sal) from scott.emp having count(*)>=5 group by tno ;

DEPTNO? COUNT(*)? SUM(SAL)

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

20???????? 5???? 10875

30???????? 6????? 9400

67.ORDER BY? 用于對查詢到的結果進行排序輸出

SQL> select deptno,ename,sal from scott.emp order by deptno,sal desc;

DEPTNO ENAME??????????? SAL

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

10 KING??????????? 5000

10 CLARK?????????? 2450

10 MILLER????????? 1300

20 SCOTT?????????? 3000

20 FORD??????????? 3000

20 JONES?????????? 2975

20 ADAMS?????????? 1100

20 SMITH??????????? 800

30 BLAKE?????????? 2850

30 ALLEN?????????? 1600

30 TURNER????????? 1500

30 WARD??????????? 1250

30 MARTIN????????? 1250

30 JAMES??????????? 950