Oracle迁移MySQL 8特殊SQL处理 顶

create table nayi_180328_connect_test(
	dept_id varchar2(50), 
	parent_id varchar2(50), 
	dept_name varchar2(100), 
	dept_rank varchar2(2000), 
	val number);

insert into nayi_180328_connect_test
	select 'root', '', '全国', '', 0 from dual
	union all
	select 'root_1', 'root', '北京市', '', 2000 from dual
	union all
	select 'ln_root', 'root', '辽宁省', '', 200 from dual
	union all
	select 'ln_ys', 'ln_root', '辽宁省沈阳市', '', 1000 from dual
	union all
	select 'ln_sy_hp', 'ln_ys', '辽宁省沈阳和平区', '', 500 from dual
	union all
	select 'ln_ys_dd', 'ln_ys', '辽宁省沈阳大东区', '', 600 from dual
	union all
	select 'jl_root', 'root', '吉林省', '', 0 from dual
	union all
	select 'jl_jl', 'jl_root', '吉林省吉林市', '', 200 from dual
	union all
	select 'jl_cc', 'jl_root', '吉林省长春市', '', 500 from dual
	;

	select t1.*,CONNECT_BY_ROOT(dept_name) root_name
	  from nayi_180328_connect_test t1
	 where 1=1
	 connect by prior t1.dept_id = t1.parent_id
	 start with t1.dept_id = 'root'
	;

create table nayi_180328_connect_test(
	dept_id varchar(50), 
	parent_id varchar(50), 
	dept_name varchar(100), 
	dept_rank varchar(2000), 
	val int,
	PRIMARY key	(dept_id)
	) 

	insert into nayi_180328_connect_test
	select 'root', '', '全国', '', 0 from dual
	union all
	select 'root_1', 'root', '北京市', '', 2000 from dual
	union all
	select 'ln_root', 'root', '辽宁省', '', 200 from dual
	union all
	select 'ln_ys', 'ln_root', '辽宁省沈阳市', '', 1000 from dual
	union all
	select 'ln_sy_hp', 'ln_ys', '辽宁省沈阳和平区', '', 500 from dual
	union all
	select 'ln_ys_dd', 'ln_ys', '辽宁省沈阳大东区', '', 600 from dual
	union all
	select 'jl_root', 'root', '吉林省', '', 0 from dual
	union all
	select 'jl_jl', 'jl_root', '吉林省吉林市', '', 200 from dual
	union all
	select 'jl_cc', 'jl_root', '吉林省长春市', '', 500 from dual

	with recursive t1(dept_id,parent_id,dept_name,dept_rank,val,root_name,rownum, order_str) as (
			select t0.*,t0.dept_name,@rownum := 1 rn, cast(@rownum as char) sdfsf from nayi_180328_connect_test t0 where t0.dept_id='root'
			union all
			select t2.*,t1.root_name,@rownum := @rownum + 1 rn, concat(t1.order_str, '-', @rownum) st  from nayi_180328_connect_test t2,t1
			where t2.parent_id = t1.dept_id
	)
	select * from t1 order by order_str

select to_char(sysdate,'yyyy-mm-dd') from dual

	select date_format(now(),'%Y-%m-%d') from dual

select to_date('2020-01-01','yyyy-mm-dd') from dual

select str_to_date('2020-01-01','%Y-%m-%d %h:%i:%s') from dual

select nvl(parent_id,'boot') from nayi_180328_connect_test where dept_id='root'

select ifnull(parent_id,'boot') from nayi_180328_connect_test where dept_id='root'

select decode(parent_id,null,'全国','root','省市','地区') from nayi_180328_connect_test

select case when parent_id is null then '全国' when parent_id='root' then '省市' else '地区' end from nayi_180328_connect_test

WITH TEMP AS(  
SELECT 'CHINA' NATION ,'GUANGZHOU' CITY FROM DUAL UNION ALL  
SELECT 'CHINA' NATION ,'SHANGHAI' CITY FROM DUAL UNION ALL  
SELECT 'CHINA' NATION ,'BEIJING' CITY FROM DUAL UNION ALL  
SELECT 'USA' NATION ,'NEW YORK' CITY FROM DUAL UNION ALL  
SELECT 'USA' NATION ,'BOSTOM' CITY FROM DUAL UNION ALL  
SELECT 'JAPAN' NATION ,'TOKYO' CITY FROM DUAL   
)  
SELECT 
NATION,LISTAGG(CITY,',') WITHIN GROUP (ORDER BY CITY)  AS CITIES
FROM TEMP  
GROUP BY NATION

WITH TEMP AS(  
SELECT 'CHINA' NATION ,'GUANGZHOU' CITY FROM DUAL UNION ALL  
SELECT 'CHINA' NATION ,'SHANGHAI' CITY FROM DUAL UNION ALL  
SELECT 'CHINA' NATION ,'BEIJING' CITY FROM DUAL UNION ALL  
SELECT 'USA' NATION ,'NEW YORK' CITY FROM DUAL UNION ALL  
SELECT 'USA' NATION ,'BOSTOM' CITY FROM DUAL UNION ALL  
SELECT 'JAPAN' NATION ,'TOKYO' CITY FROM DUAL   
)  
SELECT 
NATION,GROUP_CONCAT(city order by city) cities
FROM TEMP  
GROUP BY NATION

select substr('HelloWorld',0,3) value from dual;
select substr('HelloWorld',1,3) value from dual;

	select substr('HelloWorld',1,3) value from dual;

select to_char(12345678.657,'999,999,999,999.99')  from dual;

select format(12345678.657,2)  from dual;

select 'ABC' || 'EFG' from dual

select concat('ABC','EFG') from dual

create tablespace CUS_TS01 datafile '/home/oracle/app/oracle/oradata/helowin/cus01.dbf' size 20m;
create tablespace CUS_TS02 datafile '/home/oracle/app/oracle/oradata/helowin/cus02.dbf' size 20m;

select * from dba_tablespaces;

CREATE TABLE CUSTOMER
(
    CUSTOMER_ID NUMBER NOT NULL PRIMARY KEY,
    FIRST_NAME  VARCHAR2(30) NOT NULL,
    LAST_NAME   VARCHAR2(30) NOT NULL,
    PHONE        VARCHAR2(15) NOT NULL,
    EMAIL        VARCHAR2(80),
    STATUS       CHAR(1)
)
PARTITION BY RANGE (CUSTOMER_ID)
(
    PARTITION CUS_PART1 VALUES LESS THAN (100000) TABLESPACE CUS_TS01,
    PARTITION CUS_PART2 VALUES LESS THAN (200000) TABLESPACE CUS_TS02
)

insert into customer values (1,'Li','xiaoping','123456987','lixiaoping@123.com','1')

select * from customer partition(CUS_PART1)

select * from customer partition(CUS_PART2)

CREATE TABLE CUSTOMER
(
    CUSTOMER_ID int NOT NULL,
    FIRST_NAME  VARCHAR(30) NOT NULL,
    LAST_NAME   VARCHAR(30) NOT NULL,
    PHONE        VARCHAR(15) NOT NULL,
    EMAIL        VARCHAR(80),
    STATUS       CHAR(1),
		primary key (CUSTOMER_ID)
)
PARTITION BY RANGE (CUSTOMER_ID)
(
    PARTITION CUS_PART1 VALUES LESS THAN (100000),
    PARTITION CUS_PART2 VALUES LESS THAN (200000)
)

insert into CUSTOMER values (1,'Li','xiaoping','123456987','lixiaoping@123.com','1')

select * from CUSTOMER partition(CUS_PART1)

select * from CUSTOMER partition(CUS_PART2)

select sysdate from dual

select trunc(sysdate) from dual

select trunc(sysdate ,'dd') from dual ; 

select now() from dual

select str_to_date(now(),'%Y-%m-%d')-str_to_date('2020-05-01','%Y-%m-%d') from dual

select TRUNC(SYSDATE, 'yyyy') from dual

SELECT DATE_SUB(CURDATE(),INTERVAL dayofyear(now())-1 DAY) from dual

DATE_SUB(date,INTERVAL expr type)

select curdate() from dual

select dayofyear('2020-05-13') from dual

select ADD_MONTHS (to_date('2020-01-01','yyyy-mm-dd') , 3) from dual union all
select ADD_MONTHS (to_date('2020-01-01','yyyy-mm-dd') , -3) from dual

SELECT ADDDATE('2020-01-01', INTERVAL 3 MONTH) from dual union all
SELECT ADDDATE('2020-01-01', INTERVAL -3 MONTH) from dual