mysql和oracle的sql语法

最新推荐文章于 2025-05-18 10:10:30 发布

原创最新推荐文章于 2025-05-18 10:10:30 发布 · 372 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#sql

mysql 专栏收录该内容

11 篇文章

订阅专栏

本文介绍了MyBatis框架在Oracle数据库环境下的一些高级使用技巧，包括利用序列进行插入操作、实现模糊查询以及复杂的子查询应用。通过具体示例展示了如何在不同场景下有效地运用这些技巧。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1.插入语句：

1）oracle

<insert id="add" parameterType="Object">
        <selectKey  resultType="java.lang.Integer" order="BEFORE" keyProperty="id">
            SELECT T_TASK_SEQ.NEXTVAL as id from DUAL
        </selectKey>
        insert into
        t_task(id,branch_no,branch_name,task_no,task_name,branch_total,task_type,start_date,end_date)
        values(#{id},#{branchNo},#{branchName},#{taskNo},#{taskName},#{branchTotal},#{taskType},#{startDate},#{endDate})
    </insert>

oracle进行插入时要进行序列

2.模糊匹配

oracle:

<sql id="baseWhereClause">
        where 1=1
        <trim suffixOverrides=",">
            <if test="taskName != null and taskName != ''">
                and task_name like concat(concat('%','${taskName}'),'%')
            </if>
            <if test="taskType != null and taskType != ''">
                and task_type=#{taskType}
            </if>
            <if test="branchNo != null and branchNo != ''">
                and branch_no=#{branchNo}
            </if>
        </trim>
    </sql>

模糊匹配建议用‘$’美元符号，不要用‘#’符号，这样oracle和mysql都能兼容。

mysql:

<if test="driver != null and driver != ''">
                and (t.driverNo=#{driver} OR t.driver_name LIKE concat(concat('%',#{driver}),'%'))
            </if>

3.子查询

oracle 提供了with as 函数方便多表查询

WITH diff AS ( -- 差异化
        SELECT
        s.STORE_ID AS ID,
        s.STORE_NAME AS NAME,
        "SUM" (A .xsje) AS salesAmount,
        "SUM" (A .xssl) AS salesNum
        FROM
        rtrycxf A,
        bm c
        RIGHT JOIN H_STORE s ON c.DZBM01 = s.STORE_ID
        WHERE
        A .cxflx = 1
        AND A .jzrq = TRUNC (SYSDATE, 'dd')
        AND A .bm01 = c.bm01
        <include refid="StoreQuery" />
        GROUP BY
        s.STORE_ID,
        s.STORE_NAME
        ),
        total AS (  -- 销售总额
        SELECT
        s.STORE_ID AS ID,
        s.STORE_NAME AS NAME,
        "SUM" (A .xsje) AS salesAmount,
        "SUM" (A .xssl) AS salesNum
        FROM
        rtrycxf A,
        bm c
        RIGHT JOIN H_STORE s ON c.DZBM01 = s.STORE_ID
        WHERE
        A .jzrq = TRUNC (SYSDATE, 'dd')
        AND A .bm01 = c.bm01
        <include refid="StoreQuery" />
        GROUP BY
        s.STORE_ID,
        s.STORE_NAME
        ) SELECT
        ROW_NUMBER () OVER (

        ORDER BY
        diff.salesAmount DESC
        ) RANK,
        diff. ID ID,
        diff. NAME NAME,
        diff.salesAmount salesAmount,
        diff.salesNum salesNum,
        "DECODE"(TOTAL.SALESAMOUNT, 0, 0,diff.salesAmount / total.salesAmount) rate
        FROM
        diff,
        total
        WHERE
        diff. ID = total. ID