2025年oraclesql试题及答案

2025年oraclesql试题及答案一、单项选择题（每题2分，共20分）1.以下关于Oracle数据类型的描述，正确的是（）A.VARCHAR2(20)最多存储20个汉字（假设字符集为AL32UTF8）B.NUMBER(5,2)可以存储的数值范围是-999.99到999.99C.DATE类型包含时分秒信息，而TIMESTAMP类型不包含时区信息D.BLOB类型用于存储大文本数据，CLOB用于存储二进制数据答案：B解析：A选项中，AL32UTF8字符集下，一个汉字占3字节，VARCHAR2(20)指20字节，最多存储6个汉字（20/3≈6）；C选项中，TIMESTAMPWITHTIMEZONE才包含时区信息，普通TIMESTAMP不包含；D选项中，BLOB是二进制大对象，CLOB是字符大对象；B选项正确，NUMBER(5,2)表示总长度5位，小数位2位，整数部分最多3位，范围-999.99到999.99。2.执行以下SQL语句后，EMP表中受影响的行数是（）```sqlUPDATEEMPSETSALARY=SALARY1.1WHEREDEPT_IDIN(SELECTDEPT_IDFROMDEPTWHERELOCATION='BEIJING');```假设EMP表有100行，DEPT表中LOCATION='BEIJING'的DEPT_ID对应EMP表中有30行，且所有DEPT_ID在EMP中存在。A.30B.100C.0D.无法确定答案：A解析：子查询返回DEPT表中LOCATION为'BEIJING'的DEPT_ID集合，EMP表中DEPT_ID属于该集合的30行将被更新，因此受影响行数为30。3.关于Oracle索引的说法，错误的是（）A.位图索引适合高基数列（如员工编号）B.函数索引可以基于表达式创建（如UPPER(NAME)）C.唯一索引会自动创建约束（如UNIQUE约束）D.复合索引的顺序会影响查询效率答案：A解析：位图索引适用于低基数列（如性别、部门），高基数列（如员工编号）更适合B树索引，因此A错误。4.以下哪条SQL可以查询EMP表中SALARY最高的前5名员工（）A.SELECTFROMEMPORDERBYSALARYDESCFETCHFIRST5ROWS;B.SELECTFROMEMPORDERBYSALARYDESCLIMIT5;C.SELECTFROMEMPWHEREROWNUM<=5ORDERBYSALARYDESC;D.SELECTFROMEMPORDERBYSALARYDESCWHEREROWNUM<=5;答案：A解析：Oracle12c及以上支持FETCHFIRST子句，正确语法为A；B是MySQL语法；C和D错误，因为ROWNUM在排序前分配，直接使用ROWNUM<=5会先取前5行再排序，无法得到正确结果。5.事务隔离级别为READCOMMITTED时，可能出现的问题是（）A.脏读B.不可重复读C.幻读D.以上都不会答案：B解析：READCOMMITTED（读已提交）避免脏读（读取未提交事务的数据），但可能出现不可重复读（同一事务内两次查询结果不同）；可串行化（SERIALIZABLE）可避免所有问题，而READUNCOMMITTED可能出现脏读。6.以下关于分区表的描述，错误的是（）A.范围分区（RANGE）适合按时间字段分区（如ORDER_DATE）B.散列分区（HASH）适合均匀分布数据，减少热点C.列表分区（LIST）适合离散值（如地区编码）D.分区表必须为每个分区指定独立的表空间答案：D解析：分区表的分区可以共享表空间，并非必须独立，因此D错误。7.执行以下PL/SQL块，输出结果是（）```plsqlDECLAREv_numNUMBER:=10;BEGINLOOPv_num:=v_num1;EXITWHENv_num=5;ENDLOOP;DBMS_OUTPUT.PUT_LINE(v_num);END;```A.5B.6C.10D.9答案：B解析：循环中v_num从10递减，第一次循环v_num=9，不满足EXIT（9≠5）；第二次8，第三次7，第四次6，此时v_num=6，再次进入循环，v_num=5，触发EXIT，最终输出5？不，仔细看：循环结构是先执行循环体，再判断EXIT条件。初始v_num=10，进入LOOP，执行v_num=9，判断EXITWHEN9=5？否，继续循环；v_num=8，判断否；v_num=7，判断否；v_num=6，判断否；v_num=5，判断EXITWHEN5=5，退出循环。此时v_num=5，输出5？原题可能有误，正确逻辑应为：循环体执行v_num减1，然后判断是否等于5。初始v_num=10，第一次循环：v_num=9，判断9≠5，继续；第二次v_num=8，判断否；第三次v_num=7，否；第四次v_num=6，否；第五次v_num=5，判断是，退出。最终v_num=5，输出5。但原题选项中A是5，所以答案应为A。（注：此处可能存在笔误，需根据实际执行逻辑确认，正确答案应为A）8.以下关于视图的说法，正确的是（）A.视图可以作为DML操作的目标（如INSERT），但受限于WITHCHECKOPTIONB.物化视图（MATERIALIZEDVIEW）是实时更新的，与普通视图无区别C.视图的定义中不能包含子查询D.删除基表时，视图会被自动删除答案：A解析：B选项物化视图是物理存储的，需要刷新策略，并非实时；C选项视图可以包含子查询；D选项删除基表时，视图变为无效（INVALID），不会自动删除；A正确，带WITHCHECKOPTION的视图可限制DML操作符合视图定义的条件。9.要查询EMP表中NAME以“张”开头且长度为3个汉字的员工，正确的WHERE子句是（）（假设字符集为AL32UTF8）A.WHERENAMELIKE'张%'ANDLENGTH(NAME)=3B.WHERENAMELIKE'张__'ANDLENGTHB(NAME)=9C.WHERENAMELIKE'张%'ANDLENGTHB(NAME)=9D.WHERENAMELIKE'张__'ANDLENGTH(NAME)=3答案：D解析：LIKE'张__'表示“张”后接两个任意字符（每个汉字在AL32UTF8中占3字节，但LIKE的下划线匹配一个字符），LENGTH(NAME)=3表示总字符数为3（张+2个字符），因此D正确。LENGTHB返回字节数，3个汉字占9字节，但LIKE'张__'已限制字符数为3，LENGTH(NAME)=3更直接。10.关于Oracle执行计划（EXPLAINPLAN），以下说法错误的是（）A.执行计划中的“全表扫描（TABLEACCESSFULL）”一定比索引扫描慢B.可以通过DBMS_XPLAN.DISPLAY获取执行计划的文本输出C.执行计划中的“连接类型（JOINTYPE）”包括嵌套循环（NESTEDLOOPS）、哈希连接（HASHJOIN）等D.统计信息（STATS）的准确性会影响执行计划的提供答案：A解析：全表扫描在数据量小或索引选择性低时可能比索引扫描更快，因此A错误。二、填空题（每题3分，共15分）1.要将EMP表中DEPT_ID为10的员工的SALARY增加10%，并确保操作可回滚，应使用的事务控制语句是______（写出关键操作）。答案：BEGIN;UPDATEEMPSETSALARY=SALARY1.1WHEREDEPT_ID=10;COMMIT;（或不写BEGIN，直接UPDATE后COMMIT，但事务默认自动开始，所以关键是UPDATE和COMMIT/ROLLBACK）2.窗口函数中，用于计算当前行及其前N行、后M行的聚合值的子句是______。答案：ROWSBETWEENNPRECEDINGANDMFOLLOWING3.要创建一个自动递增的主键（EMP_ID），在Oracle12c及以上版本中，推荐使用______约束结合______对象实现。答案：PRIMARYKEY；SEQUENCE（或IDENTITY列，Oracle12c支持IDENTITY属性，如GENERATEDBYDEFAULTASIDENTITY）4.物化视图的刷新方式包括快速刷新（FASTREFRESH）和完全刷新（COMPLETEREFRESH），其中快速刷新需要依赖______。答案：物化视图日志（MATERIALIZEDVIEWLOG）5.要查询EMP表中SALARY高于所在部门平均工资的员工，应使用______子查询（填“相关”或“非相关”）。答案：相关三、简答题（每题8分，共40分）1.简述Oracle中UNION和UNIONALL的区别，并说明各自的适用场景。答案：UNION用于合并两个查询结果集，自动去重（即删除重复行），需要对结果集排序；UNIONALL直接合并所有行，包括重复行，不进行去重和排序。适用场景：当需要结果集中无重复行且数据量较小时使用UNION；当确认两个结果集无重复行或需要保留所有行（包括重复）且追求性能时使用UNIONALL（因为UNIONALL无需去重，效率更高）。2.什么是索引失效？列举3种可能导致索引失效的情况。答案：索引失效指查询时数据库未使用预期的索引，转而执行全表扫描，导致性能下降。常见原因：（1）查询条件使用函数或表达式（如WHEREUPPER(NAME)='ZHANG'，若索引在NAME列则无法使用）；（2）查询条件包含NULL值（如WHEREDEPT_IDISNULL，若索引列不包含NULL值则可能失效）；（3）复合索引的顺序与查询条件不匹配（如索引为(DEPT_ID,HIRE_DATE)，但查询仅使用HIRE_DATE作为条件）；（4）使用LIKE通配符开头（如WHERENAMELIKE'%张%'，无法使用前缀索引）；（5）数据类型隐式转换（如WHEREEMP_ID='123'，若EMP_ID是NUMBER类型，字符串会转换为数值，可能导致索引失效）。3.说明Oracle中事务的ACID特性及其含义。答案：ACID是事务的四大特性：（1）原子性（Atomicity）：事务中的操作要么全部完成，要么全部不完成，不可部分执行；（2）一致性（Consistency）：事务执行前后，数据库从一个一致状态转换到另一个一致状态（如转账操作中，转出和转入金额必须相等）；（3）隔离性（Isolation）：多个事务并发执行时，一个事务的执行不被其他事务干扰，Oracle通过锁和多版本并发控制（MVCC）实现；（4）持久性（Durability）：事务提交后，对数据库的修改永久保存，即使系统故障也不会丢失（通过重做日志实现）。4.比较普通视图（VIEW）和物化视图（MATERIALIZEDVIEW）的区别，并说明物化视图的适用场景。答案：区别：（1）存储方式：普通视图是虚拟表，不存储数据，查询时动态执行定义的SQL；物化视图是物理表，存储查询结果的副本；（2）更新方式：普通视图随基表实时更新（查询时重新计算）；物化视图需要手动或按计划刷新（如基于物化视图日志的快速刷新）；（3）性能：普通视图适合频繁查询但数据量小的场景；物化视图适合复杂查询、基表不频繁更新但需要快速访问的场景。适用场景：数据仓库中的复杂报表查询、跨库查询（如dblink连接的远程表）、基表更新频率低但查询频繁的场景（如每日汇总数据）。5.如何优化Oracle中的慢查询？请列出至少5种常用方法。答案：优化慢查询的方法：（1）分析执行计划（EXPLAINPLAN或AWR报告），确认是否使用索引或高效的连接方式；（2）为查询条件中的高选择性列创建索引（如WHERE、JOIN、ORDERBY涉及的列）；（3）避免全表扫描，通过限制查询范围（如添加时间条件）减少数据量；（4）优化SQL语句结构，避免嵌套过深的子查询，改用JOIN或窗口函数；（5）更新表和索引的统计信息（ANALYZETABLE或DBMS_STATS），确保优化器提供正确的执行计划；（6）使用分区表，将大表按时间或范围分区，减少扫描的数据量；（7）避免在WHERE子句中对列使用函数或表达式（如将WHERETO_CHAR(HIRE_DATE,'YYYY')='2023'改为WHEREHIRE_DATE>=DATE'2023-01-01'ANDHIRE_DATE<DATE'2024-01-01'）；（8）调整数据库参数（如PGA_AGGREGATE_TARGET），优化排序和哈希连接的内存使用。四、综合题（共25分）1.（10分）现有以下表结构：ORDERS（ORDER_IDNUMBERPRIMARYKEY,CUST_IDNUMBER,ORDER_DATEDATE,TOTAL_AMOUNTNUMBER(10,2),STATUSVARCHAR2(20)）CUSTOMERS（CUST_IDNUMBERPRIMARYKEY,CUST_NAMEVARCHAR2(50),REGIONVARCHAR2(20)）需求：查询2024年每个地区（REGION）的订单总金额、订单数量，并按总金额降序排序。要求：（1）过滤STATUS为'COMPLETED'的订单；（2）使用JOIN连接两张表；（3）结果包含REGION、总金额、订单数量。答案：```sqlSELECTc.REGION,SUM(o.TOTAL_AMOUNT)AS总金额,COUNT(o.ORDER_ID)AS订单数量FROMCUSTOMERScJOINORDERSoONc.CUST_ID=o.CUST_IDWHEREo.STATUS='COMPLETED'ANDo.ORDER_DATE>=DATE'2024-01-01'ANDo.ORDER_DATE<DATE'2025-01-01'GROUPBYc.REGIONORDERBY总金额DESC;```解析：通过INNERJOIN关联CUSTOMERS和ORDERS表，过滤2024年且状态为'COMPLETED'的订单，按REGION分组统计总金额和订单数，最后按总金额降序排序。2.（8分）某表EMP有100万条记录，字段包括EMP_ID（主键）、NAME、DEPT_ID、SALARY、HIRE_DATE。现有查询：```sqlSELECTNAME,SALARYFROMEMPWHEREDEPT_ID=30ANDHIRE_DATE>'2020-01-01'ORDERBYSALARYDESC;```执行缓慢。请分析可能的原因，并给出优化方案。答案：可能原因：（1）DEPT_ID=30和HIRE_DATE>'2020-01-01'的条件未使用索引，导致全表扫描；（2）排序（ORDERBYSALARY）在大量数据上执行，未使用索引加速排序；（3）统计信息过时，优化器选择错误的执行计划。优化方案：（1）创建复合索引：CREATEINDEXIDX_EMP_DEPT_HIREONEMP(DEPT_ID,HIRE_DATE,SALARYDESC);索引包含DEPT_ID和HIRE_DATE作为过滤条件，SALARY作为排序键，可直接通过索引获取排序结果，避免额外排序操作；（2）更新统计信息：EXECDBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'SCOTT',TABNAME=>'EMP');确保优化器基于最新数据分布提供执行计划；（3）检查DEPT_ID=30的数据量：若该部门记录数占总数据量很大比例（如超过15%），全表扫描可能更高效，此时索引可能不适用，需调整查询条件或分区表。3.（7分）编写一个PL/SQL存储过程，实现以下功能：输入参数：CUST_ID（客户ID）、NEW_STATUS（新状态）