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
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 = 数据库实例名称) ) )';
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;
15、split用法
select '$'|| (select COLUMN_VALUE from table(split(tblworkingerror.errormsgcode,'$')) where COLUMN_VALUE is not null and rownum=1) col from tblworkingerror
16、误删数据
select * from TBLITEMOPTIMECONTROL as of timestamp to_timestamp('2021-12-20 14:00:00','yyyy-mm-dd hh24:mi:ss')
17、链接占用情况
select a.MACHINE,a.PROGRAM,a.CLIENT_INFO,count(1) from v$session a group by a.MACHINE,a.PROGRAM,a.CLIENT_INFO order by count(1)desc;
16、误删数据