Oracle 数据库常见操作技巧

小码农薛尧

发布于 2025-02-27 15:28:31

7330

文章被收录于专栏：小码农薛尧小码农薛尧

在企业级应用开发中，Oracle 数据库作为一款功能强大且广泛使用的关系型数据库管理系统，掌握其常见操作技巧对于提升数据处理效率和应用性能至关重要。本文将围绕一系列 Oracle 数据库操作，结合具体代码进行详细解析。

一、数据插入与更新操作

（一）批量插入操作

在实际业务中，当需要向数据库中批量插入数据时，我们可以使用以下 SQL 语句结合 MyBatis 的<foreach>标签来实现：

INSERT ALL
        <foreach collection="list" item="item"index="index">
            INTO TEST_QUESTION
            <trim prefix="(" suffix=")">
                <include refid="Base_List"></include>
            </trim>
            <trim prefix="VALUES (" suffix=")">
                #{item.qId,jdbcType=VARCHAR},
                #{item.infoId,jdbcType=VARCHAR},
                #{item.questionContent,jdbcType=VARCHAR},
                #{item.answerContent,jdbcType=VARCHAR},
                #{item.answerContentEn,jdbcType=VARCHAR},
                #{item.questionContentEn,jdbcType=VARCHAR},
                #{item.creator,jdbcType=VARCHAR},
                #{item.createTime,jdbcType=TIMESTAMP},
                #{item.updater,jdbcType=VARCHAR},
                #{item.updateTime,jdbcType=TIMESTAMP},
                #{item.optionCategory,jdbcType=VARCHAR}
            </trim>
        </foreach>
        SELECT1FROM DUAL

上述代码中，通过<foreach>循环遍历集合list，将每个元素item的数据插入到TEST_QUESTION表中。Base_List中定义了表的列名，通过<include>标签引入，实现了灵活的列定义复用。

（二）批量更新操作

批量更新数据时，使用 MyBatis 的<update>标签结合<foreach>标签：

<update id="updateBatch" parameterType="java.util.List">
  <foreach collection="list" item="item" index="index" open="begin" close=";end;" separator=";">
   update TEST_TABLE
   <set>
     tableName = #{item.tableName}
   </set>
   where tableId = item.tableId
  </foreach>
</update>

这段代码会对list集合中的每个item，根据tableId更新TEST_TABLE表中的tableName字段。

二、数据查询操作

（一）内连接去重

内连接多个表并去重查询结果：

SELECT DISTINCT r.id, r.parentId, r.resKey, r.resName, r.resType, r.component, r.path, r.permissions, r.icon, r.sortOrder, r.visibleFlag, r.target, r.redirectUrl, r.isDefault
FROM RESOURCE_INFO r
         INNER JOIN ROLE_RESOURCE_MAP rm ON r.ID = rm.RESOURCE_ID
         INNER JOIN ROLE_INFO ro ON rm.ROLE_ID = ro.ID
         INNER JOIN ROLE_USER_MAP rum ON rum.ROLE_ID = ro.ID;

该查询通过内连接RESOURCE_INFO、ROLE_RESOURCE_MAP、ROLE_INFO和ROLE_USER_MAP表，并使用DISTINCT关键字去除重复的行。

（二）递归查询

递归查询分为向下递归和向上递归：

-- 向下递归
SELECTID, USER_NAME
        FROM CUSTOMER_ACCOUNT
        STARTWITHID='123456'
        CONNECTBYPRIORID =  CREATED_BY 
-- 向上递归
UNIONSELECTID,USER_NAME
        FROM CUSTOMER_ACCOUNT
        STARTWITHID='123456'
        CONNECTBYPRIOR  CREATED_BY =  ID

向下递归从指定的ID开始，通过CONNECT BY PRIOR ID = CREATED_BY获取子节点数据；向上递归则通过CONNECT BY PRIOR CREATED_BY = ID获取父节点数据。

（三）判断日期区间

根据日期区间筛选数据：

SELECT ID, TITLE, TYPE, TIME_TYPE, START_DATE,
       END_DATE, RECEIVER
FROM ANNOUNCEMENT_INFO
WHERE (START_DATE IS NULL OR SYSDATE > START_DATE)
AND (END_DATE IS NULL OR END_DATE > SYSDATE)
ORDER BY CREATE_TIME DESC

该查询筛选出START_DATE为空或当前日期大于START_DATE，且END_DATE为空或END_DATE大于当前日期的记录。

（四）第一个查询无结果采用第二个查询结果

当第一个查询无结果时，采用第二个查询结果：

SELECT ID,USER_NAME,
NVL((SELECT FULL_NAME FROM EMPLOYEE_INFO WHERE ID = CREATED_BY), 
(SELECT USER_NAME FROM CUSTOMER_ACCOUNT WHERE ID = CREATED_BY)) AS name 
FROM CUSTOMER_ACCOUNT WHERE ID = '654321';

通过NVL函数，当第一个子查询有结果时返回其结果，否则返回第二个子查询的结果。

（五）分组并把相同 GROUP BY 变成一条数据

对数据进行分组并合并字段：

SELECT BASIC_INFO_ID, (LISTAGG(DEVICE_NAME,',') within group ( order by BASIC_INFO_ID)) AS DEVICE_NAMES
      FROM SERVICE_REQUEST
      GROUP BY BASIC_INFO_ID

使用LISTAGG函数将相同BASIC_INFO_ID的DEVICE_NAME合并成一个字符串。

（六）查询条件去重，去重条件是一个字段，显示是多个字段

根据指定字段去重并显示多个字段：

SELECT * FROM (SELECT ID_, PROC_INST_ID_, TASK_DEF_KEY_, row_number() over (partition by PROC_INST_ID_ order by ID_ DESC) AS ABC
      FROM TASK_INSTANCE_HISTORY) A WHERE A.ABC = 1;

通过row_number()函数和PARTITION BY子句，按照PROC_INST_ID_进行分组并排序，只取每组中ID_最大的记录。

三、视图相关操作

（一）创建视图

创建一个只读视图：

CREATE OR REPLACE VIEW USER_INFO_VIEW AS
SELECT ID, (NAME || '(' || EMPLOYEE_NUMBER || ')') AS DISPLAY_NAME, NAME
FROM EMPLOYEE_INFO
UNION
SELECT ID, USER_NAME, USER_NAME
FROM CUSTOMER_ACCOUNT
WITH READ ONLY

该视图合并了EMPLOYEE_INFO和CUSTOMER_ACCOUNT表的数据，并设置为只读。

（二）查看视图的创建语句

查看视图的创建语句：

select text from all_views where view_name ='CUSTOMER_INFO_VIEW'

通过查询all_views视图，可以获取指定视图的创建语句。

四、其他操作技巧

（一）获得最新数据 (根据指定字段排序并分组)

获取每个分组中最新的数据：

SELECT FI.*  FROM
    ( SELECT T.*, ROW_NUMBER ( ) OVER ( PARTITION BY T.DEVICE_NAME ORDER BY T.CREATE_TIME DESC ) RW FROM DEVICE_BASIC_INFO T ) FI 
WHERE
    FI.RW =1

使用ROW_NUMBER()函数和PARTITION BY子句，按照DEVICE_NAME分组并根据CREATE_TIME降序排序，只取每组中排序为 1 的记录。

（二）Oracle 数据库分页查询出现重复

当排序字段没有唯一性时，在排序中加上主键：

-- 原来的排序
ORDER BY CREATE_TIME;

-- 优化后的排序
ORDER BY CREATE_TIME, ID

这样可以确保分页查询结果的唯一性。

（三）ORACLE 行数据逗号分割转列

将行数据中逗号分割的字符串转换为列：

SELECT DISTINCT REGEXP_SUBSTR(MEMBERS, '[^,]+', 1, LEVEL, 'i')
FROM CUSTOMER_DETAIL
WHERE CUSTOMER_CODE = 'ABC'
CONNECT BY LEVEL <= LENGTH(MEMBERS) - LENGTH(REGEXP_REPLACE(MEMBERS, ',', '')) + 1;

通过REGEXP_SUBSTR函数和CONNECT BY子句，将MEMBERS字段中的字符串按逗号分割成多行。

（四）ORACLE 中 IN 长度大于 1000 时处理

当IN子句中的集合超过 1000 条时，进行如下处理：

    <if test="ids != null">
        t1.ID IN
        <!-- 处理in的集合超过1000条时Oracle不支持的情况 -->
        <trim suffixOverrides=" OR t1.ID IN()"><!-- 表示删除最后一个条件 -->
          <foreach collection="ids" item="id" index="index" open="(" close=")">
            <if test="index != 0">
              <choose>
                <when test="index % 1000 == 999">) OR t1.ID IN (</when>
                <otherwise>,</otherwise>
              </choose>
            </if>
            #{id}
          </foreach>
        </trim>
      </if>

通过循环将ids集合分割成多个IN子句，避免IN子句长度超过 1000 的限制。

（五）分组多个字段，合并某一个字段，并行转列

1、分组多个字段合并某一个字段

-- 分组多个字段，合并某一个字段
SELECT AREA_CODE, CUSTOMER_CODE, DEPARTMENT_CODE, (LISTAGG(MEMBERS,',') within group ( order by AREA_CODE, DEPARTMENT_CODE, CUSTOMER_CODE)) AS MEMBER_LIST
      FROM CUSTOMER_DETAIL
      GROUP BY AREA_CODE, DEPARTMENT_CODE, CUSTOMER_CODE

对多个字段进行分组，并使用LISTAGG函数合并MEMBERS字段。

2、内容行转列

SELECT t.CUSTOMER_CODE,
       MAX(CASE t.DEPARTMENT_CODE WHEN'DEPT1'THEN t.MEMBER ELSE''END)    AS DEPT1_MEMBERS,
       MAX(CASE t.DEPARTMENT_CODE WHEN'DEPT2'THEN t.MEMBER ELSE''END)    AS DEPT2_MEMBERS,
       MAX(CASE t.DEPARTMENT_CODE WHEN'DEPT3'THEN t.MEMBER ELSE''END) AS DEPT3_MEMBERS,
       MAX(CASE t.DEPARTMENT_CODE WHEN'DEPT4'THEN t.MEMBER ELSE''END)          AS DEPT4_MEMBERS,
       MAX(CASE t.DEPARTMENT_CODE WHEN'DEPT5'THEN t.MEMBER ELSE''END)          AS DEPT5_MEMBERS,
       MAX(CASE t.DEPARTMENT_CODE WHEN'DEPT6'THEN t.MEMBER ELSE''END)       AS DEPT6_MEMBERS

FROM (
         SELECT CUSTOMER_CODE,
                lower(AREA_CODE) || initcap(DEPARTMENT_CODE)                                               AS DEPARTMENT_CODE,
                (
                    LISTAGG(MEMBERS, ',') withinGROUP ( ORDERBY AREA_CODE, DEPARTMENT_CODE, CUSTOMER_CODE )) ASMEMBER
         FROM  CUSTOMER_DETAIL
         GROUPBY AREA_CODE,
                  DEPARTMENT_CODE,
                  CUSTOMER_CODE
     ) t
GROUPBY t.CUSTOMER_CODE

通过CASE WHEN和MAX函数，将行数据转换为列数据，实现了数据的灵活展示。

（六）某张表授权给某一个用户

将表的查询权限授权给用户：

GRANT SELECT ON TEST_SCHEMA.TEST_TABLE TO TEST_USER;

使用GRANT语句将TEST_SCHEMA.TEST_TABLE表的SELECT权限授予TEST_USER用户。

通过以上对 Oracle 数据库常见操作技巧的介绍和代码解析，希望能够帮助开发人员更好地理解和应用 Oracle 数据库，提升数据处理的效率和质量。在实际应用中，根据具体业务需求灵活运用这些技巧，能够有效地解决各种数据处理问题。

本文参与腾讯云自媒体同步曝光计划，分享自微信公众号。

原始发表：2025-02-26，如有侵权请联系 cloudcommunity@tencent.com 删除

排序

本文分享自小码农薛尧微信公众号，前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

本文参与腾讯云自媒体同步曝光计划，欢迎热爱写作的你一起参与！

登录后参与评论

0 条评论

热度