技巧:ORACLE应用常见傻瓜问题1000问(六)
INSERT INTO FILE_TAB VALUES(BFILENAME('LOB_DIR','IMAGE1.GIF'));
266. GREATEST(EXPR,EXPR,...)
GREATEST返回参数的最大值.
SELECT GREATEST('HARRY','HARRIOT','HAROLD') 'SAMPLE' FROM TABLE1;
267. LEAST(EXPR,EXPR,...)
LEAST返回参数的最小值.
SELECT LEAST('HARRY','HARRIOT','HAROLD') 'SAMPLE' FROM TABLE1;
268. NLS_CHARSET_DECL_LEN(BYTECNT,CSID)
返回一个NCHAR列的宽度.
SELECT NLS_CHARSET_DECL_LEN(200,NLS_CHARSET_ID('JA16EEFDFDF')) FROM TABLE1;
269. NLS_CHARSET_ID(TEXT)
返回相应于NLS字符集名字的NLS字符集ID数.
SELECT NLS_CHARSET_D('JADFDFFDF') FROM TABLE1;
270. NLS_CHARSET_NAME(N)
返回相应于ID数N的NLS字符集名称.
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
返回唯一标识当前数据库用户的整数.
SELECT UID FROM TABLE1;
273. USER
用VARCHAR2数据类型返回当前ORACLE用户的名称.
SELECT USER,UID FROM TABLE1;
274. USERENV(OPTION)
返回当前的会话信息.
OPTION='ISDBA'若当前是DBA角色,则为TRUE,否则FALSE.
OPTION='LANGUAGE'返回数据库的字符集.
OPTION='SESSIONID'为当前会话标识符.
OPTION='ENTRYID'返回可审计的会话标识符.
OPTION='LANG'返回会话语言名称的ISO简记.
OPTION='INSTANCE'返回当前的实例.
SELECT USERENV('LANGUAGE') FROM DUAL;
275. VSIZE(EXPR)
返回EXPR内部表示的字节数.
SELECT NAME,VSIZE(NAME) FROM TABLE1;
276. DEREF(E)
返回参数E的对象引用.
SELECT DEREF(C2) FROM TABLE1;
277. REFTOHEX(R)
将参数R转换为16进制.
SELECT REFTOHEX(C2) FROM TABLE1;
278. MAKE_REF(TABLE,KEY,KEY...)
通过把给定的键作为主键来创建给定视图对象中一行的引用.
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给出一组行值的标准差.
SELECT STDDEV(SALARY) AS EXAMPLE FROM EMPLOYEE;
280. VARIANCE(DISTINCT|ALL X)
VARIANCE返回一组行中所有VALUE的方差.
SELECT VARIANCE(SALARY) AS EXAMPLE FROM EMPLOYEE。