Oracle 方法

1、递归

 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
  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

View Code

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 '0', username, dateend, '1', '19'
    from view_Rep_holiday
   where id = 'QJ151012_003'
  union all
  select '0', username, datestart, '1', '19'
    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 = 数据库实例名称)
                            )
                          )';

View Code

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:OADataBaseBack';
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;