對(duì)ORACLE高手來(lái)說(shuō)是不用看的.
ORACLE內(nèi)部函數(shù)篇
255. CHARTOROWID(CHAR)
將包含外部語(yǔ)法ROWID的CHAR或VARCHAR2數(shù)值轉(zhuǎn)換為內(nèi)部的二進(jìn)制語(yǔ)法,參數(shù)CHAR必須是包含外部語(yǔ)法的ROWID的18字符的字符串.
SELECT NAME FROM BSEMPMS WHERE ROWID=CHARTOROWID('AAAAfZAABAAACp8AAO');
NAME : LEIXUE
256. CONVERT(CHAR,DEST_CHAR_SET,SOURCE_CHAR_SET)
CONVERT將字符串CHAR中的字符從SOURCE_CHAR_SET標(biāo)識(shí)的字符集轉(zhuǎn)換為由DEST_CHAR_SET標(biāo)識(shí)的字符集
SELECT CONVERT('GroB','US7ASCII','WE8HP') 'CONVERSION' FROM PUBS;
CONVERSION: Gross
257. HEXTORAW(CHAR)
將包含十六進(jìn)制的CHAR轉(zhuǎn)換為一個(gè)RAW數(shù)值.
INSERT INTO BSEMPMS(RAW_COLUMN) SELECT HEXTORAW('7D') FROM TEST;
258. RAWTOHEX(RAW)
將RAW數(shù)值轉(zhuǎn)換為一個(gè)包含十六進(jìn)制的CHAR值.
SELECT RAWTOHEX(RAW_COLUMN) 'CONVERSION' FROM BSEMPMS;
CONVERSION: 7D
259. ROWIDTOCHAR(ROWID)
將一個(gè)ROWID數(shù)值轉(zhuǎn)換為VARCHAR2數(shù)據(jù)類(lèi)型.
SELECT ROWID FROM BSEMPMS WHERE ROWIDTOCHAR(ROWID) LIKE '%BR1AAB%';
260. TO_MULTI_BYTE(CHAR)
將CHAR中的單字節(jié)轉(zhuǎn)換為等價(jià)的多字節(jié)字符.
SELECT TO_MULTI_BYTE('ASFDFD') FROM TEST;
261. TO_SINGLE_BYTE(CHAR)
將CHAR中的多字節(jié)轉(zhuǎn)換為等價(jià)的單字節(jié)字符.
SELECT TO_SINGLE_BYTE('ASFDFD') FROM TEST;
262. TRANSLATE USING(TEXT USING {CHAR_CS|NCHAR_CS})
將文本TEXT按照指定的轉(zhuǎn)換方式轉(zhuǎn)換成數(shù)據(jù)庫(kù)字符集和民族字符集.
其中TEXT是待轉(zhuǎn)換的.
USING CHAR_CS參數(shù)轉(zhuǎn)換TEXT為數(shù)據(jù)庫(kù)字符集,輸出數(shù)據(jù)類(lèi)型是VARCHAR2.
USING NCHAR_CS參數(shù)轉(zhuǎn)換TEXT為數(shù)據(jù)庫(kù)字符集,輸出數(shù)據(jù)類(lèi)型是NVARCHAR2.
CREATE TABLE TEST(CHAR_COL CHAR(20),NCHAR_COL NCHAR(20));
INSERT INTO TEST VALUES('HI,N'BYE');
SELECT * FROM TEST;
263. DUMP(EXPR,RETURN_FORMAT,START_POSITION,LENGTH)
返回一個(gè)包含數(shù)據(jù)類(lèi)型代碼,字節(jié)長(zhǎng)度等內(nèi)部表示信息的VARCHAR2值.返回結(jié)果是當(dāng)前數(shù)據(jù)庫(kù)字符集,數(shù)據(jù)類(lèi)型按照下面規(guī)定的內(nèi)部數(shù)據(jù)類(lèi)型的編碼作為一個(gè)數(shù)字進(jìn)行返回:
代碼 數(shù)據(jù)類(lèi)型
0 VARCHAR2
1 NUMBER
8 LONG
12 DATE
23 RAW
24 LONG RAW
69 ROWID
96 CHAR
106 MSSLABEL
參數(shù)RETUEN_FORMAT指定按照下面的基數(shù)表示返回的數(shù)值.
RETURN_FORMAT RESULT
8 8進(jìn)制
10 10進(jìn)制
16 16進(jìn)制
17 單字符表示
如果參數(shù)RETURN_FORMAT沒(méi)有指定,則按十進(jìn)制表示返回.
如果參數(shù)START_POSITION和LENGTH被指定,則從START_POSITION開(kāi)始的長(zhǎng)為L(zhǎng)ENGTH的字節(jié)將被返回,缺省是返回整數(shù)表示.
SELECT DUMP('ABC',1016) FROM TEST;
select dump(ename,8,3,2) 'example' from emp where name='ccbzzp';
264. empty_b|clob()
返回一個(gè)空的LOB定位符,用在初始化LOB變量,或用在INSERT及UPDATE聲明去初始化LOB列或?qū)⑵鋵傩灾脼榭?
INSERT INTO TABLE1 VALUES(EMPTY_BLOB());
UPDATE TABLE1 SET CLOB_COL=EMPTY_BLOB();
265. BFILENAME('DIRECTORY','FILENAME')
返回一個(gè)BFILE定位符,相關(guān)的二進(jìn)制LOB物理文件在服務(wù)器的文件系統(tǒng)上.目錄DIRECTORY是指在服務(wù)器的文件系統(tǒng)上實(shí)際搜索路徑全名的別名. FILENAME是指服務(wù)器的文件系統(tǒng)的文件名.
INSERT INTO FILE_TAB VALUES(BFILENAME('LOB_DIR','IMAGE1.GIF'));
266. GREATEST(EXPR,EXPR,...)
GREATEST返回參數(shù)的最大值.
SELECT GREATEST('HARRY','HARRIOT','HAROLD') 'SAMPLE' FROM TABLE1;
267. LEAST(EXPR,EXPR,...)
LEAST返回參數(shù)的最小值.
SELECT LEAST('HARRY','HARRIOT','HAROLD') 'SAMPLE' FROM TABLE1;
268. NLS_CHARSET_DECL_LEN(BYTECNT,CSID)
返回一個(gè)NCHAR列的寬度.
SELECT NLS_CHARSET_DECL_LEN(200,NLS_CHARSET_ID('JA16EEFDFDF')) FROM TABLE1;
269. NLS_CHARSET_ID(TEXT)
返回相應(yīng)于NLS字符集名字的NLS字符集ID數(shù).
SELECT NLS_CHARSET_D('JADFDFFDF') FROM TABLE1;
270. NLS_CHARSET_NAME(N)
返回相應(yīng)于ID數(shù)N的NLS字符集名稱(chēng).
SELECT NLS_CHARSET_NAME(2) FROM TABLE1;
271. NVL(EXPR1,EXPR2)
若EXPR1是NULL,則返回EXPR2,否則返回EXPR1.
SELECT NAME,NVL(TO_CHAR(COMM),'NOT APPLICATION') FROM TABLE1;
272. UID
返回唯一標(biāo)識(shí)當(dāng)前數(shù)據(jù)庫(kù)用戶(hù)的整數(shù).
SELECT UID FROM TABLE1;
273. USER
用VARCHAR2數(shù)據(jù)類(lèi)型返回當(dāng)前ORACLE用戶(hù)的名稱(chēng).
SELECT USER,UID FROM TABLE1;
274. USERENV(OPTION)
返回當(dāng)前的會(huì)話(huà)信息.
OPTION='ISDBA'若當(dāng)前是DBA角色,則為T(mén)RUE,否則FALSE.
OPTION='LANGUAGE'返回?cái)?shù)據(jù)庫(kù)的字符集.
OPTION='SESSIONID'為當(dāng)前會(huì)話(huà)標(biāo)識(shí)符.
OPTION='ENTRYID'返回可審計(jì)的會(huì)話(huà)標(biāo)識(shí)符.
OPTION='LANG'返回會(huì)話(huà)語(yǔ)言名稱(chēng)的ISO簡(jiǎn)記.
OPTION='INSTANCE'返回當(dāng)前的實(shí)例.
SELECT USERENV('LANGUAGE') FROM DUAL;
275. VSIZE(EXPR)
返回EXPR內(nèi)部表示的字節(jié)數(shù).
SELECT NAME,VSIZE(NAME) FROM TABLE1;
276. DEREF(E)
返回參數(shù)E的對(duì)象引用.
SELECT DEREF(C2) FROM TABLE1;
277. REFTOHEX(R)
將參數(shù)R轉(zhuǎn)換為16進(jìn)制.
SELECT REFTOHEX(C2) FROM TABLE1;
278. MAKE_REF(TABLE,KEY,KEY...)
通過(guò)把給定的鍵作為主鍵來(lái)創(chuàng)建給定視圖對(duì)象中一行的引用.
CREATE TYPE T1 AS OBJECT(A NUMBER,B NUMBER);
CREATE TABLE TB1(C1 NUMBER,C2 NUMBER,PRIMARY KEY(C1,C2));
CREATE VIEW V1 OF T1 WITH OBJECT OID(A,B) AS SELECT * FROM TB1;
SELECT MAKE_REF(V1,1,3) FROM PUBS;
279. STDDEV(DISTINCT|ALL X)
STDDEV給出一組行值的標(biāo)準(zhǔn)差.
SELECT STDDEV(SALARY) AS EXAMPLE FROM EMPLOYEE;
280. VARIANCE(DISTINCT|ALL X)
VARIANCE返回一組行中所有VALUE的方差.
SELECT VARIANCE(SALARY) AS EXAMPLE FROM EMPLOYEE;
待續(xù)...
