oracle_function_guinness.mht

ORACLE函数大全--用于数据库的常用函数

      to_multi_byte('=B8=DF') from dual;</P>
      <P>TO<BR>--<BR>=B8=DF<BR></P>
      =
<P>50.TO_NUMBER<BR>=BD=AB=B8=F8=B3=F6=B5=C4=D7=D6=B7=FB=D7=AA=BB=BB=CE=AA=
=CA=FD=D7=D6<BR>SQL&gt; select to_number('1999') year=20
      from dual;</P>
      <P>YEAR<BR>---------<BR>1999<BR></P>
      =
<P>51.BFILENAME(dir,file)<BR>=D6=B8=B6=A8=D2=BB=B8=F6=CD=E2=B2=BF=B6=FE=BD=
=F8=D6=C6=CE=C4=BC=FE<BR>SQL&gt;insert into file_tb1=20
      values(bfilename('lob_dir1','image1.gif'));<BR></P>
      =
<P>52.CONVERT('x','desc','source')<BR>=BD=ABx=D7=D6=B6=CE=BB=F2=B1=E4=C1=BF=
=B5=C4=D4=B4source=D7=AA=BB=BB=CE=AAdesc<BR>SQL&gt;=20
      select sid,serial#,username,decode(command,<BR>2 0,'none',<BR>3=20
      2,'insert',<BR>4 3,<BR>5 'select',<BR>6 6,'update',<BR>7 =
7,'delete',<BR>8=20
      8,'drop',<BR>9 'other') cmd from v$session where =
type!=3D'background';</P>
      <P>SID SERIAL# USERNAME CMD<BR>--------- ---------=20
      ------------------------------ ------<BR>1 1 none<BR>2 1 none<BR>3 =
1=20
      none<BR>4 1 none<BR>5 1 none<BR>6 1 none<BR>7 1275 none<BR>8 1275=20
      none<BR>9 20 GAO select<BR>10 40 GAO none<BR></P>
      =
<P>53.DUMP(s,fmt,start,length)<BR>DUMP=BA=AF=CA=FD=D2=D4fmt=D6=B8=B6=A8=B5=
=C4=C4=DA=B2=BF=CA=FD=D7=D6=B8=F1=CA=BD=B7=B5=BB=D8=D2=BB=B8=F6VARCHAR2=C0=
=E0=D0=CD=B5=C4=D6=B5<BR>SQL&gt;=20
      col global_name for a30<BR>SQL&gt; col dump_string for =
a50<BR>SQL&gt; set=20
      lin 200<BR>SQL&gt; select global_name,dump(global_name,1017,8,5)=20
      dump_string from global_name;</P>
      <P>GLOBAL_NAME DUMP_STRING<BR>------------------------------=20
      --------------------------------------------------<BR>ORACLE.WORLD =
Typ=3D1=20
      Len=3D12 CharacterSet=3DZHS16GBK: W,O,R,L,D<BR></P>
      =
<P>54.EMPTY_BLOB()=BA=CDEMPTY_CLOB()<BR>=D5=E2=C1=BD=B8=F6=BA=AF=CA=FD=B6=
=BC=CA=C7=D3=C3=C0=B4=B6=D4=B4=F3=CA=FD=BE=DD=C0=E0=D0=CD=D7=D6=B6=CE=BD=F8=
=D0=D0=B3=F5=CA=BC=BB=AF=B2=D9=D7=F7=B5=C4=BA=AF=CA=FD<BR></P>
      =
<P>55.GREATEST<BR>=B7=B5=BB=D8=D2=BB=D7=E9=B1=ED=B4=EF=CA=BD=D6=D0=B5=C4=D7=
=EE=B4=F3=D6=B5,=BC=B4=B1=C8=BD=CF=D7=D6=B7=FB=B5=C4=B1=E0=C2=EB=B4=F3=D0=
=A1.<BR>SQL&gt; select=20
      greatest('AA','AB','AC') from dual;</P>
      <P>GR<BR>--<BR>AC<BR>SQL&gt; select =
greatest('=B0=A1','=B0=B2','=CC=EC') from dual;</P>
      <P>GR<BR>--<BR>=CC=EC<BR></P>
      =
<P>56.LEAST<BR>=B7=B5=BB=D8=D2=BB=D7=E9=B1=ED=B4=EF=CA=BD=D6=D0=B5=C4=D7=EE=
=D0=A1=D6=B5 <BR>SQL&gt; select least('=B0=A1','=B0=B2','=CC=EC') from=20
      dual;</P>
      <P>LE<BR>--<BR>=B0=A1<BR></P>
      =
<P>57.UID<BR>=B7=B5=BB=D8=B1=EA=CA=B6=B5=B1=C7=B0=D3=C3=BB=A7=B5=C4=CE=A8=
=D2=BB=D5=FB=CA=FD<BR>SQL&gt; show user<BR>USER =CE=AA"GAO"<BR>SQL&gt;=20
      select username,user_id from dba_users where user_id=3Duid;</P>
      <P>USERNAME USER_ID<BR>------------------------------ =
---------<BR>GAO=20
      25</P>
      <P></P>
      =
<P>58.USER<BR>=B7=B5=BB=D8=B5=B1=C7=B0=D3=C3=BB=A7=B5=C4=C3=FB=D7=D6<BR>S=
QL&gt; select user from dual;</P>
      <P>USER<BR>------------------------------<BR>GAO<BR></P>
      =
<P>59.USEREVN<BR>=B7=B5=BB=D8=B5=B1=C7=B0=D3=C3=BB=A7=BB=B7=BE=B3=B5=C4=D0=
=C5=CF=A2,opt=BF=C9=D2=D4=CA=C7:<BR>ENTRYID,SESSIONID,TERMINAL,ISDBA,LABL=
E,LANGUAGE,CLIENT_INFO,LANG,VSIZE<BR>ISDBA=20
      =
=B2=E9=BF=B4=B5=B1=C7=B0=D3=C3=BB=A7=CA=C7=B7=F1=CA=C7DBA=C8=E7=B9=FB=CA=C7=
=D4=F2=B7=B5=BB=D8true<BR>SQL&gt; select userenv('isdba') from dual;</P>
      <P>USEREN<BR>------<BR>FALSE<BR>SQL&gt; select userenv('isdba') =
from=20
      dual;</P>
      =
<P>USEREN<BR>------<BR>TRUE<BR>SESSION<BR>=B7=B5=BB=D8=BB=E1=BB=B0=B1=EA=D6=
=BE<BR>SQL&gt; select=20
      userenv('sessionid') from dual;</P>
      =
<P>USERENV('SESSIONID')<BR>--------------------<BR>152<BR>ENTRYID<BR>=B7=B5=
=BB=D8=BB=E1=BB=B0=C8=CB=BF=DA=B1=EA=D6=BE<BR>SQL&gt;=20
      select userenv('entryid') from dual;</P>
      =
<P>USERENV('ENTRYID')<BR>------------------<BR>0<BR>INSTANCE<BR>=B7=B5=BB=
=D8=B5=B1=C7=B0INSTANCE=B5=C4=B1=EA=D6=BE<BR>SQL&gt;=20
      select userenv('instance') from dual;</P>
      =
<P>USERENV('INSTANCE')<BR>-------------------<BR>1<BR>LANGUAGE<BR>=B7=B5=BB=
=D8=B5=B1=C7=B0=BB=B7=BE=B3=B1=E4=C1=BF<BR>SQL&gt;=20
      select userenv('language') from dual;</P>
      =
<P>USERENV('LANGUAGE')<BR>-----------------------------------------------=
-----<BR>SIMPLIFIED=20
      =
CHINESE_CHINA.ZHS16GBK<BR>LANG<BR>=B7=B5=BB=D8=B5=B1=C7=B0=BB=B7=BE=B3=B5=
=C4=D3=EF=D1=D4=B5=C4=CB=F5=D0=B4<BR>SQL&gt; select=20
      userenv('lang') from dual;</P>
      =
<P>USERENV('LANG')<BR>---------------------------------------------------=
-<BR>ZHS<BR>TERMINAL<BR>=B7=B5=BB=D8=D3=C3=BB=A7=B5=C4=D6=D5=B6=CB=BB=F2=BB=
=FA=C6=F7=B5=C4=B1=EA=D6=BE<BR>SQL&gt;=20
      select userenv('terminal') from dual;</P>
      =
<P>USERENV('TERMINA<BR>----------------<BR>GAO<BR>VSIZE(X)<BR>=B7=B5=BB=D8=
X=B5=C4=B4=F3=D0=A1(=D7=D6=BD=DA)=CA=FD<BR>SQL&gt;=20
      select vsize(user),user from dual;</P>
      <P>VSIZE(USER) USER<BR>----------- =
------------------------------<BR>6=20
      SYSTEM</P>
      <P></P>
      =
<P>60.AVG(DISTINCT|ALL)<BR>all=B1=ED=CA=BE=B6=D4=CB=F9=D3=D0=B5=C4=D6=B5=C7=
=F3=C6=BD=BE=F9=D6=B5,distinct=D6=BB=B6=D4=B2=BB=CD=AC=B5=C4=D6=B5=C7=F3=C6=
=BD=BE=F9=D6=B5<BR>SQLWKS&gt;=20
      create table table3(xm varchar(8),sal=20
      =
number(7,2));<BR>=D3=EF=BE=E4=D2=D1=B4=A6=C0=ED=A1=A3<BR>SQLWKS&gt; =
insert into table3=20
      values('gao',1111.11);<BR>SQLWKS&gt; insert into table3=20
      values('gao',1111.11);<BR>SQLWKS&gt; insert into table3=20
      values('zhu',5555.55);<BR>SQLWKS&gt; commit;</P>
      <P>SQL&gt; select avg(distinct sal) from gao.table3;</P>
      <P>AVG(DISTINCTSAL)<BR>----------------<BR>3333.33</P>
      <P>SQL&gt; select avg(all sal) from gao.table3;</P>
      <P>AVG(ALLSAL)<BR>-----------<BR>2592.59<BR></P>
      =
<P>61.MAX(DISTINCT|ALL)<BR>=C7=F3=D7=EE=B4=F3=D6=B5,ALL=B1=ED=CA=BE=B6=D4=
=CB=F9=D3=D0=B5=C4=D6=B5=C7=F3=D7=EE=B4=F3=D6=B5,DISTINCT=B1=ED=CA=BE=B6=D4=
=B2=BB=CD=AC=B5=C4=D6=B5=C7=F3=D7=EE=B4=F3=D6=B5,=CF=E0=CD=AC=B5=C4=D6=BB=
=C8=A1=D2=BB=B4=CE<BR>SQL&gt;=20
      select max(distinct sal) from scott.emp;</P>
      <P>MAX(DISTINCTSAL)<BR>----------------<BR>5000<BR></P>
      =
<P>62.MIN(DISTINCT|ALL)<BR>=C7=F3=D7=EE=D0=A1=D6=B5,ALL=B1=ED=CA=BE=B6=D4=
=CB=F9=D3=D0=B5=C4=D6=B5=C7=F3=D7=EE=D0=A1=D6=B5,DISTINCT=B1=ED=CA=BE=B6=D4=
=B2=BB=CD=AC=B5=C4=D6=B5=C7=F3=D7=EE=D0=A1=D6=B5,=CF=E0=CD=AC=B5=C4=D6=BB=
=C8=A1=D2=BB=B4=CE<BR>SQL&gt;=20
      select min(all sal) from gao.table3;</P>
      <P>MIN(ALLSAL)<BR>-----------<BR>1111.11<BR></P>
      =
<P>63.STDDEV(distinct|all)<BR>=C7=F3=B1=EA=D7=BC=B2=EE,ALL=B1=ED=CA=BE=B6=
=D4=CB=F9=D3=D0=B5=C4=D6=B5=C7=F3=B1=EA=D7=BC=B2=EE,DISTINCT=B1=ED=CA=BE=D6=
=BB=B6=D4=B2=BB=CD=AC=B5=C4=D6=B5=C7=F3=B1=EA=D7=BC=B2=EE<BR>SQL&gt;=20
      select stddev(sal) from scott.emp;</P>
      <P>STDDEV(SAL)<BR>-----------<BR>1182.5032</P>
      <P>SQL&gt; select stddev(distinct sal) from scott.emp;</P>
      <P>STDDEV(DISTINCTSAL)<BR>-------------------<BR>1229.951</P>
      <P></P>
      <P>64.VARIANCE(DISTINCT|ALL)<BR>=C7=F3=D0=AD=B7=BD=B2=EE </P>
      <P>SQL&gt; select variance(sal) from scott.emp;</P>
      <P>VARIANCE(SAL)<BR>-------------<BR>1398313.9<BR></P>
      <P>65.GROUP =
BY<BR>=D6=F7=D2=AA=D3=C3=C0=B4=B6=D4=D2=BB=D7=E9=CA=FD=BD=F8=D0=D0=CD=B3=BC=
=C6<BR>SQL&gt; select deptno,count(*),sum(sal)=20
      from scott.emp group by deptno;</P>
      <P>DEPTNO COUNT(*) SUM(SAL)<BR>--------- --------- ---------<BR>10 =
3=20
      8750<BR>20 5 10875<BR>30 6 9400</P>
      <P></P>
      =
<P>66.HAVING<BR>=B6=D4=B7=D6=D7=E9=CD=B3=BC=C6=D4=D9=BC=D3=CF=DE=D6=C6=CC=
=F5=BC=FE<BR>SQL&gt; select deptno,count(*),sum(sal)=20
      from scott.emp group by deptno having count(*)&gt;=3D5;</P>
      <P>DEPTNO COUNT(*) SUM(SAL)<BR>--------- --------- ---------<BR>20 =
5=20
      10875<BR>30 6 9400<BR>SQL&gt; select deptno,count(*),sum(sal) from =

      scott.emp having count(*)&gt;=3D5 group by deptno ;</P>
      <P>DEPTNO COUNT(*) SUM(SAL)<BR>--------- --------- ---------<BR>20 =
5=20
      10875<BR>30 6 9400<BR></P>
      <P>67.ORDER =
BY<BR>=D3=C3=D3=DA=B6=D4=B2=E9=D1=AF=B5=BD=B5=C4=BD=E1=B9=FB=BD=F8=D0=D0=C5=
=C5=D0=F2=CA=E4=B3=F6<BR>SQL&gt; select deptno,ename,sal from=20
      scott.emp order by deptno,sal desc;</P>
      <P>DEPTNO ENAME SAL<BR>--------- ---------- ---------<BR>10 KING=20
      5000<BR>10 CLARK 2450<BR>10 MILLER 1300<BR>20 SCOTT 3000<BR>20 =
FORD=20
      3000<BR>20 JONES 2975<BR>20 ADAMS 1100<BR>20 SMITH 800<BR>30 BLAKE =

      2850<BR>30 ALLEN 1600<BR>30 TURNER 1500<BR>30 WARD 1250<BR>30 =
MARTIN=20
      1250<BR>30 JAMES 950 </P></TD></TD></TR>
  <TR>
    <TD align=3Dright>=A1=BE <A onclick=3Dwindow.close();=20
      =
href=3D"http://www.cnse.cn/html/database/oracle/602/41905.html#">=B9=D8=B1=
=D5=B4=B0=BF=DA</A>=20
    =A1=BF</TD></TR></TBODY></TABLE>
<SCRIPT src=3D"http://www.cnse.cn/countDetail.php?id=3D41905"></SCRIPT>

<CENTER>
<SCRIPT src=3D"http://online.fasky.com:81/online.asp"></SCRIPT>

<SCRIPT=20
src=3D"http://vip1.1tong.com.cn/link/count.php?id=3D55&amp;show=3Dpic"></=
SCRIPT>

<DIV align=3Dcenter><A href=3D"http://www.fasky.com/" =
target=3D_blank>=CC=EC=BF=D5=D2=F4=C0=D6=CD=F8</A> <A=20
href=3D"http://cn.clyric.com/" =
target=3D_blank>=D3=A2=CE=C4=B8=E8=B4=CA=B5=DA=D2=BB=D5=BE</A> <A=20
href=3D"http://www.clyric.com/" target=3D_blank>clyric</A> <A=20
href=3D"http://www.9915.com/" =
target=3D_blank>=B5=E7=C4=D4=BC=BC=CA=F5=D7=CA=C1=CF</A> <A=20
href=3D"http://midi.fasky.com/" =
target=3D_blank>=CA=D6=BB=FA=BA=CD=CF=D2midi</A> <A=20
href=3D"http://www.eb.com.cn/" =
target=3D_blank>=D0=E9=C4=E2=D6=F7=BB=FA</A> <A=20
href=3D"http://www.blogger.cn/" target=3D_blank>=B2=A9=BF=CD</A> <A=20
style=3D"TEXT-DECORATION: none" href=3D"http://www.bozhai.cn/"=20
target=3D_blank>=B2=A9=D5=AA=D6=D0=B9=FA</A>&nbsp; <A =
href=3D"http://www.kakabook.net/"=20
target=3D_blank>=BF=A8=B2=A9=D1=A7=CF=B0=CD=F8</A> <A =
style=3D"TEXT-DECORATION: none"=20
href=3D"http://www.tiaoke.com/" =
target=3D_blank>=C5=E0=D1=B5=BF=CE=B3=CC=CB=D1=CB=F7</A>&nbsp; =
=C1=AA=CF=B5<IMG=20
src=3D"http://www.clyric.com/clyric/img/ZmFza3lAeWVhaC5uZXQ=3D?w=3D100&am=
p;h=3D16">=20
</DIV></CENTER></CENTER></CENTER></BODY></HTML>

------=_NextPart_000_0000_01C655EB.87825920
Content-Type: image/gif
Content-Transfer-Encoding: base64
Content-Location: http://union.eb.com.cn/logo/778_80.gif