oracle更新列为某函数,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