mysql转oracle

mysql 建表语句转为 oracle 建表语句



单引号 ' 变双引号 "

comment 内容去掉

varchar 变 varchar2

double/int 之类的数字变 number

PRIMARY KEY  ("kc_no") 语句变 CONSTRAINT "kc_no" PRIMARY KEY  ("kc_no")



select jihua_no from zhou.px_kecheng_jihua 

where status<>-1 

and input_date between "0001-01-01" and "9999-12-31" 

order by input_date desc

mysql 语句转为 oracle 语句日期不再是字符串需要 to_date

select jihua_no from px_kecheng_jihua 

where status<>-1 

and input_date between to_date('0001-01-01','yyyy-mm-dd') and to_date('9999-12-31','yyyy-mm-dd')

order by input_date desc

```java
-- 		group_concat ( DISTINCT bfu.unit_no ) unitno,
		LISTAGG ( bfu.unit_no, ',' ) WITHIN GROUP ( ORDER BY bfu.unit_no ) AS unitno,
// 日期相减
-- date_format ( rc.end_date, '%Y-%m-%d' ) endDate,
to_char ( rc.end_date, 'yyyy-mm-dd' ) endDate,

//正则
-- contract_no_new REGEXP '^.+\[[0-9]{4}\][0-9]{4}$'
regexp_like(contract_no_new, '^.+\[[0-9]{4}\][0-9]{4}$')


// 字符串日期要转成date类型
      and lbfu.expiry_date >= to_date(#{param.requireDate},'SYYYY-MM-DD HH24:MI:SS' )and lbfu.effective_date <![CDATA[<=]]> to_date(#{param.requireDate},'SYYYY-MM-DD HH24:MI:SS')

// 日期转换
      DATE_FORMAT( bfu.updated_date, '%Y-%m-%d %H:%i:%s' ) updatedDate,
        to_char ( bfu.updated_date,  'SYYYY-MM-DD HH24:MI:SS' ) updatedDate,


--设置自增序列，名称为"seq_userinfo"，名字任意命名
-- 创建自增序列

create sequence seq_BUILDING_FLOOR_UNIT
 increment by 1		--每次+1	
 start with 362		--从1开始
 nomaxvalue			--不限最大值
 nominvalue			--不限最小值
 cache 20;			--设置取值缓存数为20
 
--  删除
drop  sequence seq_BUILDING_FLOOR_UNIT 
drop  trigger buildingFloorUnit 

--创建触发器，名称为"deptinfo_TRIGGER"，名字任意命名
create or replace trigger buildingFloorUnit
before insert on LMP_BUILDING_FLOOR_UNIT	--"userinfo"为表名称
for each row
begin
select seq_BUILDING_FLOOR_UNIT.nextval into :new.id from dual;	--1、"seq_userinfo"为自增序列名称 2、这里的id是你需要自增的序列
end buildingFloorUnit;	--"userinfo_TRIGGER"为触发器名称


            File file1 = new File("");
            String exportFilePath = file1.getCanonicalPath();

// group_concat 的等效替换，将分组后的值拼接成一个字符串，默认逗号分隔

SELECT
	group_concat ( DISTINCT lrc.building_id ) buildingId,
	lrc.tenant_id 
FROM
	lmp_rent_contract lrc 
WHERE
	lrc.contract_stage = '2' 
GROUP BY
	lrc.tenant_id
				
SELECT
	LISTAGG (lrc.building_id, ',' ) WITHIN GROUP ( ORDER BY lrc.building_id ) buildingId,
	count( lrc.building_id ) frf,
	lrc.tenant_id 
FROM
	( SELECT DISTINCT building_id, tenant_id FROM lmp_rent_contract WHERE contract_stage = '2') lrc 
GROUP BY
	lrc.tenant_id