sqlserver移植为Oracle笔记（更新，新增字段名；批量新增记录；日期查询；截取字串函数）

下面是这两天在项目要sqlserver和oracle兼容的改造中测试出来的笔记：

--sqlserver
--更改主键字段名'ID'为'ID_'
sp_rename   'tb_doc_cat_statistic.ID','ID_','column'

--新增字段cat_code
alter table tb_doc_cat_statistic add cat_code varchar(100)

 

--oracle
--更改主键字段名'ID'为'ID_'
ALTER TABLE tb_doc_cat_statistic Rename column ID TO ID_

--新增字段cat_code
alter table tb_doc_cat_statistic add cat_code varchar(100)

 

--oracle查询某一天的记录
select * from tb_doc_cat_statistic where to_char(modify_date,'yyyy-mm-dd')='2009-09-17'

--sqlserver查询某一天的记录
select * from tb_doc_cat_statistic where YEAR(modify_date) = YEAR('2009-09-17') AND  MONTH(modify_date) = MONTH('2009-09-17') AND  DAY(modify_date) = DAY('2009-09-17')

--oracle中substr函数（字符串，从1开始计数从第几个开始，往后截取几个）
select c.cat_name as resultcat_,resultcout_,knowledgeCode
from (select sum(count_num) as resultcout_,substr(cat_code,1,4) as knowledgeCode from tb_doc_cat_statistic a  left join wf_org_department dept on dept.ID_=a.dept_code  where 1=1  and to_char(modify_date,'yyyy-mm-dd')='2009-09-22' and dept.id_='4028808322a53dad0122abf17c770dac' group by substr(cat_code,1,4)) t
left join tb_doc_catalog c on c.cat_code=t.knowledgeCode  

 

 

--sqlserver中的SUBSTRING函数（字符串，从1开始计数从第几个开始，往后截取几个）
select c.cat_name as resultcat_,resultcout_,knowledgeCode
from (select sum(count_num) as resultcout_,SUBSTRING(cat_code,1,4) as knowledgeCode from tb_doc_cat_statistic a  left join wf_org_department dept on dept.ID_=a.dept_code  where 1=1  and YEAR(modify_date) = YEAR('2009-09-22') AND  MONTH(modify_date) = MONTH('2009-09-22') AND  DAY(modify_date) = DAY('2009-09-22') group by SUBSTRING(cat_code,1,4)) t
left join tb_doc_catalog c on c.cat_code=t.knowledgeCode 

 

 

--sqlserver批量插入一个表的记录到另一个表

insert into tb_doc_cat_statistic (COUNT_NUM,CAT_ID,DEPT_CODE,MODIFY_DATE,CAT_CODE) select count(*) as count_Num,cat_id,dept.ID_ as dept_code,GETDATE() as MODIFY_DATE,cat_code from tb_document  doc left join wf_org_department  dept on (dept.ID_=doc.deptName) where doc.knowledge_Code !='1000' group by cat_id,ID_,cat_code

在数据库中设置表主键字段的标识即可

 

--oracle批量插入一个表的记录到另一个表

insert into tb_doc_cat_statistic (COUNT_NUM,CAT_ID,DEPT_CODE,MODIFY_DATE,CAT_CODE) select count(*) as count_Num,cat_id,dept.ID_ as dept_code, SYSDATE as MODIFY_DATE,cat_code from tb_document  doc left join wf_org_department  dept on (dept.ID_=doc.deptName) where doc.knowledge_Code !='1000' group by cat_id,ID_,cat_code

 

--由于这里主键是自动递增生成，所以需写一个序列并且还需要一个触发器；还有就是两个系统中默认生成当前日期的函数也不一样。

--批量新增主键自增序列与触发器
-- Create sequence
create sequence S_TB_DOC_CAT_STATISTIC
minvalue 1
maxvalue 999999999999999999999999999
start with 1
increment by 1
cache 20;

 

--create trigger

create trigger docCatStatistic before
insert on tb_doc_cat_statistic for each row
begin
select S_TB_DOC_CAT_STATISTIC.NEXTVAL into:new.id_ from dual;
end;

alter trigger docCatStatistic enable