MySQL 切换 Oracle 问题整理

MySQL 通常小写，Oracle 默认大写 ，查询过程中需加双引号,或者直接将MySQL 字段转换成大写

Springboot 配置 oracle连接

spring:
  datasource:
    url: jdbc:oracle:thin:@1127.0.0.1:1521:chdb
    username: root
    password: root
    driver-class-name: oracle.jdbc.driver.OracleDriver

设置 oracle insert 为 null

mybatis:
  configuration:
    jdbc-type-for-null: 'null'

行转列。例： 将字符串 1,2,3 转为数组。涉及中文乱码问题，需用 TO_CHAR

SELECT
	(
	SELECT
		listagg ( TO_CHAR( "role_name" ), ',' ) within GROUP ( ORDER BY "id" ) AS "roleName" 
	FROM
		"t_role" 
	WHERE
		"id" IN (
		SELECT
			REGEXP_SUBSTR( "role_id", '[^,]+', 1, ROWNUM ) 
		FROM
			"t_user" CONNECT BY ROWNUM <= LENGTH( "role_id" ) - LENGTH( regexp_replace( "role_id", ',', '' ) ) + 1 
		) 
	) "name" 
FROM
	"t_user"

Oracle 取第一条数据，mysql是 limit 1

SELECT
	* 
FROM
	"t_multi_report" 
WHERE
	"report_year" = 2022 
	AND ROWNUM = 1

Oracle mybatis 批量添加

 insert into "t_org_related_prod"
        ("pid", "reg_code", "orignal_org", "orignal_org_name", "product_name", "product_reg_no")
        <foreach collection="list" item="item" separator="UNION ALL">
            SELECT #{item.pid}, #{item.regCode}, #{item.orignalOrg},
            #{item.orignalOrgName}, #{item.productName},#{item.productRegNo}
            FROM dual
        </foreach>

ORA-00904: "ORIGNAL_ORG": 标识符无效

1、字段如果是小写，需加双引号
2、在mybatis 里 sql 结尾不能加分号 ；

建表添加主键

CREATE TABLE TEST ( 
	ID NUMBER ( 32 ) NOT NULL, 
	constraint PK primary key ( ID )  -- 主键名称需唯一 ，PK 为主键名称
	)

给字段添加注解

COMMENT ON COLUMN TEST.ID IS '主键主键';

ORA-02264: 名称已被一现有约束条件占用

建表添加主键时，主键名称需唯一

Oracle group by 分组排序 取 第一个

根据 report_org_code 分组，pid 排序 ，first_value 函数 取第一个
SELECT DISTINCT first_value("pid") over(partition by "report_org_code" order by "pid" DESC) as "pid" from  "t_multi_hi_report" 

Oracle group by 分组求和

SELECT DISTINCT "pid" ,SUM("base_capital_balance" )  over(partition by "report_id") "base_capital_balance"  from  "t_multi_hi_report" 

修改 CLOB 类型

需要先删除，在添加，直接修改失败

ORA-00918: 未明确定义列

查询存在重复字段

获取当前时间

MySQL 是 now()

Oracle SELECT SYSDATE FROM dual  

Oracle实现 group_concat

SELECT
	LISTAGG ( TO_CHAR( RELATED_NAME ), ',' ) WITHIN GROUP ( ORDER BY PID ) AS NAME 
FROM
	T_MULTI_RELATED_TRANS_TGT 
WHERE
	TRANS_NUM = 'Q1000120230208145539181261960' 
	AND IS_DELETED = 'N'

Oracle实现 group by 部分字段

SELECT U.PID,
       U.REGCODE,
       U.ORG_NAME,
       ROW_NUMBER() over (PARTITION BY U.UNISC_CODE ORDER BY U.CREATE_TIME) ROW_NUM
FROM UAC_ORG_MAIN U