1、递归
select zxdept
from (select d.id, d.zxdept, d.RANK, d.fatherId
from web_dept d
start with d.id = --获取18022部门层级
and d.SHOWFLAG =
connect by PRIOR fatherId = id) temp
where temp.rank <=
order by rank asc
结果：
1 分析及投后管理
2 财务部
3 财务部
4 管理中心
2、列拼接成行 wm_concat（）
select replace(wm_concat(zxdept), ',', '/') as 部门名称
from (select zxdept
from (select d.id, d.zxdept, d.RANK, d.fatherId
from web_dept d
start with d.id = 18022--获取18022部门层级
and d.SHOWFLAG = 1--可用
connect by PRIOR fatherId = id) temp
where temp.rank <= 4--四级以内
and temp.rank >= 1--大于等于一级
order by rank asc)
结果：管理中心/财务部/财务部/分析及投后管理
3、获取最新分组内所有一条记录
select *
from ( --以 orderno 分组,查找分组内最新一条工作流日志
select t.*,
row_number() over(partition by orderno order by FORTIME desc) rn
from (select *
from WORKFLOW_INSTANCE_LOG
where orderno in (select to_char(ID) as id
from HR_DIMISSION
where USERCODE = 'B143130'
and (FLAGSAVE = 2 or FLAGSAVE = 9) -- 离职单
union all
select to_char(ID) as id
from HR_ConFirm
where USERCODE = 'B143130'
and FLAGSAVE = 2 -- 转正单
union all
select to_char(ID) as id
from HR_Transposal
where USERCODE = 'B143130'
and FLAGSAVE = 2 -- 调岗单
)) t)
where rn = 1
4、insert into select union all ORA-02287
insert into OA_VOTE_QUESTIONNAIRE
(ID, TYPE, FATHERID, SERIALNUMBER, ITEMTITLE, QUESTIONTYPE)
select OA_VOTE_QuestionnaireID.Nextval,
2,
target.FATHERID,
target.SERIALNUMBER,
target.ITEMTITLE,
target.QUESTIONTYPE
from (select 0 FATHERID,
1 SERIALNUMBER,
'您离职的主要原因是：' ITEMTITLE,
4 QUESTIONTYPE
from dual --第一大题
union all
select (select max(temp.id)
from OA_VOTE_QUESTIONNAIRE temp
where temp.type = 2
and temp.fatherid = 0) FATHERID,
1 SERIALNUMBER,
'薪酬福利较低' ITEMTITLE,
4 QUESTIONTYPE
from dual) target
5、转换成NVARCHAR2 类型：
Translate('ssdsdsd' USING NCHAR_CS);
6、insert when EXISTS then into：
insert when EXISTS
(select *
from view_Rep_holiday
where id = 'QJ151012_003'
and holidaytype = 13) then into timecard
(cardno, username, thisdate, entryexit, clientid)
select '', username, dateend, '', ''
from view_Rep_holiday
where id = 'QJ151012_003'
union all
select '', username, datestart, '', ''
from view_Rep_holiday
where id = 'QJ151012_003';
7、oracle ORA-00911: 无效字符
语句放在plsql中执行没问题 ，但是程序始终报错，解决办法：去掉sql结尾的分号(;)。
8、获得时间差：ROUND(TO_NUMBER(END_DATE - START_DATE)) --默认相减的结果以天为单位，如果需要精确到小时、分、秒，则计算下即可；
eg:ROUND(TO_NUMBER(END_DATE - START_DATE)*24*60)
9、ORacle 迁移:
(1)安装 ORAcle server版本，安装完成，登录http://172.0.0.1:1158/em ，添加表空间（表空间和老数据库相同，避免不必要的麻烦），添加用户，并为用户赋予角色（conect,DBA,resource）和权限(这个根据需求，太 多就不一一列，建议先全部添加，然后移除);
(2)导入数据库中的队列、再导入备份。
导入备份如果有报错则继续下面步骤：
(3)依次导入导入类型、导入trigger,导入view，导入fun和proc;
ps:本人在实际中碰到字段如果是这个类型—>BINARY_FLOAT，系统会提示没有导入成功（详细可以查看导入日志），此时需要对先导入标结果然后根据下面方法插入数据。
如果操作失败，数据混乱，可以把库删除重来，但是不需要卸载oracle，这样会更快写修复问题。
第一步、创建dblink：
create public database link Link_ltcs
connect to 用户名 identified by 密码
using '(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 服务器地址)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = 数据库实例名称)
)
)';
eg:
create public database link Link_09
connect to ltg identified by longtugame123
using '(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.200.9)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = OADB)
)
)';
第二步、导数据：
insert into 本地表 select * from 远端数据库表@link_ltcs.regress.rdbms.dev.us.oracle.com
10、类型转换导致查处数据被截断
PAYEEACCOUNT NVARCHAR2(100)
原始值是 ：测试人
经过 cast(PAYEEACCOUNT as NVARCHAR2(100) )的结果为：测
11、oracle表空间使用情况查询(转载的)
select a.tablespace_name, total, free, total-free as used, substr(free/total * 100, 1, 5) as "FREE%", substr((total - free)/total * 100, 1, 5) as "USED%" from
(select tablespace_name, sum(bytes)/1024/1024 as total from dba_data_files group by tablespace_name) a,
(select tablespace_name, sum(bytes)/1024/1024 as free from dba_free_space group by tablespace_name) b
where a.tablespace_name = b.tablespace_name
order by a.tablespace_name;
12、oracle10g 定期导出数据
1、
/*设置导出路径并赋予权限*/
create or replace directory exp as 'D:\OA\DataBaseBack';
grant write on directory exp to public;
grant read on directory exp to public;
2、创建 文件名.bat
@echo off
Set OrclSid=数据库
Set ExpUser=登录名
Set ExpPass=密码
Set SysDate=%date:~0,4%-%date:~5,2%-%date:~8,2%.%time:~0,2%%time:~3,2% @echo * * * * * * * * * * * * * * * * * * * *
@echo * * * * * * * * * * * * * * * * * * * *
@echo * * * * * ORACLE 数据库导出 * * * * * *
@echo * * * * * * * * * * * * * * * * * * * *
@echo * * * * * * * * * * * * * * * * * * * * @echo 服务名 = %OrclSid%
@echo 用户名 = %ExpUser%
@echo 密 码 = %ExpPass%
@echo 时 间 = %SysDate% expdp %ExpUser%/%ExpPass%@%OrclSid% directory=exp dumpfile=%ExpUser%~%OrclSid%_%SysDate%.dmp logfile=%ExpUser%~%OrclSid%_%SysDate%.log /*倒入*/
IMPDP USERID='用户/密码@sid' schemas=倒入用户 directory=exp table_exists_action=replace dumpfile=2017-07-24.DMP
3、添加系统系统计划；
13、oracle 中使用order等关键字作为列操作：
insert into Api_beisen_postInformation(OId,OIdTalentCriterion,Name,OIdResourceSet,StartDate,StopDate,\"Order\",OIdJobLevel,EstablishDate,Description,Score,JobPostKey,JobPostSecret,JobRequirements,Code,OIdJobGradeLow,OIdJobGradeHigh,OIdJobSequence,OIdProfessionalLine) values(:OId,:OIdTalentCriterion,:Name,:OIdResourceSet,:StartDate,:StopDate,:\"ORDER\",:OIdJobLevel,:EstablishDate,:Description,:Score,:JobPostKey,:JobPostSecret,:JobRequirements,:Code,:OIdJobGradeLow,:OIdJobGradeHigh,:OIdJobSequence,:OIdProfessionalLine)
注意：参数必须大写：:\"ORDER\"
14、Function中拼接sql写逻辑：
CREATE OR REPLACE FUNCTION FN_DEPTstaffcount (dept_ number , dq number) return number is stffs int;
sql_str VARCHAR2(4000);
begin
sql_str := 'select count(1) from web_admin wa join (select temp.id from web_dept temp where temp.showflag=1 start with temp.id='||dept_||' connect by prior temp.id=temp.fatherid) temp1
on wa.zxdept=temp1.id and wa.isvirtual=0 join web_admin_work temp2 on wa.usercode=temp2.usercode where wa.isvirtual=0 and temp2.rank>0';
if dq>0 then
sql_str:=sql_str||' and exists(select 1 From web_admin_work waw where wa.usercode=waw.usercode and waw.adress=to_char('||dq||'))';
end if;
execute immediate sql_str into stffs ;
return stffs;
end;