oracle tips
Exist的用法:
select gw.ndocid from
(select ndocid from wf_doc_gw_shouwen union select ndocid from wf_doc_gw_fawen) gw
where
not exists (select null from wf_doc_gw_sn sn where sn.ndocid=gw.ndocid)
2。把GW表和SN表里相同的NDOCID显示出来
select gw.ndocid from
(select ndocid from wf_doc_gw_shouwen union select ndocid from wf_doc_gw_fawen) gw
where
exists (select null from wf_doc_gw_sn sn where sn.ndocid=gw.ndocid)
DECODE的语法:DECODE(value,if1,then1,if2,then2,if3,then3,...,else),
e.g.:
select decode(max(documentid), null, 1, (max(documentid)+1))) from document;
results:
if null, display 1
else display max(documentid)+1
1. export database
cmd
%oracle_home%/bin/exp carmot/carmot@132.159.178.236_igrp2 file=d:/a.dmp
2. oracle enterprise console
connect as sysdba
安全性,右键,建立用户carmot_hunan_1,密码用carmot
给dba权限。
3. import database
cmd
%oracle_home%/bin/imp carmot_hunan_1/carmot@132.159.178.236_igrp2 file=d:/a.dmp fromuser=carmot
** remarks:
carmot_hunan_1/carmot@132.159.178.236_igrp2
用户名/密码@配置在TOAD中的数据库名称
例如: TOAD中数据库的名称是: IGRP2_132.159.178.236, 则为: carmot_hunan_1/carmot@igrp2_132.159.178.236
Example for complicated select:
select distinct first_value(ndocid) over (partition by ndocid order by lv desc) as ndocid,
--first_value(curtitle) over (partition by ndocid order by lv desc) as
curtitle,
first_value(realname) over (partition by ndocid order by lv desc) as realname
from(
select ndocid,curtitle, sys_connect_by_path(realname,' ') realname, level lv from
(select ndocid, curtitle,realname, lag(realname,1,null) over (partition by ndocid order by realname) realname_1
from (select g.ndocid,g.curtitle, u.realname
from wf_doc_gw g, tbuser u
where instr(','||g.cprocuserlist||',',','||u.userid||',')>0)) connect by prior realname=realname_1) order by ndocid
e.g.:
select g.ndocid, g.cprocuserlist from wf_doc_gw g;
result:
101 1 1001,1002,1003
102 2 101,1004
select u.userid, u.realname from tbuser
1001 a1
1002 a2
1003 a3
1004 a4
select g.ndocid,g.curtitle, u.realname
from wf_doc_gw g, tbuser u
where instr(','||g.cprocuserlist||',',','||u.userid||',')>0)
result:
101 1 a1
101 1 a2
101 1 a3
102 2 a1
102 2 a4
select distinct first_value(ndocid) over (partition by ndocid order by lv desc) as ndocid,
--first_value(curtitle) over (partition by ndocid order by lv desc) as
curtitle,
first_value(realname) over (partition by ndocid order by lv desc) as realname
from(
select ndocid,curtitle, sys_connect_by_path(realname,' ') realname, level lv from
(select ndocid, curtitle,realname, lag(realname,1,null) over (partition by ndocid order by realname) realname_1
from (select g.ndocid,g.curtitle, u.realname
from wf_doc_gw g, tbuser u
where instr(','||g.cprocuserlist||',',','||u.userid||',')>0)) connect by prior realname=realname_1) order by ndocid
result:
101 1 a1 a2 a3
102 2 a1 a4