.sysdate
用来得到系统的当前日期
sql> select to_char(sysdate,dd-mm-yyyy day) from dual;
to_char(sysdate,
-----------------
09-05-2004星期日
trunc(date,fmt)按照给出的要求将日期截断,如果fmt=mi表示保留分,截断秒sql> select to_char(trunc(sysdate,hh),yyyy.mm.dd hh24:mi:ss) hh, 2 to_char(trunc(sysdate,mi),yyyy.mm.dd hh24:mi:ss) hhmm from dual;
hh hhmm
------------------- -------------------
2004.05.09 11:00:00 2004.05.09 11:17:00
42.chartorowid
将字符数据类型转换为rowid类型
sql> select rowid,rowidtochar(rowid),ename from scott.emp;
rowid rowidtochar(rowid) ename
------------------ ------------------ ----------
aaaafkaacaaaaeqaaa aaaafkaacaaaaeqaaa smith
aaaafkaacaaaaeqaab aaaafkaacaaaaeqaab allen
aaaafkaacaaaaeqaac aaaafkaacaaaaeqaac ward
aaaafkaacaaaaeqaad aaaafkaacaaaaeqaad jones
43.convert(c,dset,sset)
将源字符串sset从一个语言字符集转换到另一个目的dset字符集
sql> select convert(strutz,we8hp,f7dec) conversion from dual;
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# usernam
e 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