Oracle期末复习最能拉开分差的其实就是那几道典型的SQL与PL/SQL题目。很多同学复习时习惯把教材从头翻到尾概念背了一堆结果到了考场上面对“用SQL实现XX查询”“补齐下面的PL/SQL块”这类题目还是卡壳。我带过不少学生的考前突击也见过太多“背了语法却写不出完整语句”的例子总结下来与其漫无目的地刷题不如把最常考的十类知识点做成一个闭环练习。这篇文章整理了10道有代表性的Oracle期末实例题覆盖基础查询与排序、多表连接、分组统计、子查询、数据更新与事务回滚、建表约束、用户权限、视图、PL/SQL匿名块和索引每道题都给出标准答案、逐步解析、易错提醒以及实际执行效果说明。适合正在准备Oracle数据库期末考试的在校学生也适合刚入门想检验自己掌握程度的学习者。1. 考前摸底10道题的考点地图1.1 题型分布与知识权重先把这10道题的考点分布铺开方便对照自己的薄弱环节。题号核心考点常见题型难度建议用时题1SELECT过滤与排序编写SQL入门3分钟题2多表连接ANSI语法编写SQL入门5分钟题3GROUP BY与HAVING聚合编写SQL基础5分钟题4单行子查询编写SQL基础5分钟题5UPDATE与事务回滚编写SQL简答基础6分钟题6建表与完整性约束编写SQL基础6分钟题7用户创建与权限授予编写SQL简答基础4分钟题8视图与WITH CHECK OPTION编写SQL进阶6分钟题9PL/SQL匿名块编程题进阶10分钟题10索引创建与删除编写SQL入门3分钟从这张表能看出两点第一期末试卷不会只考概念背诵绝大多数分数落在“你能不能写出能跑的语句”上第二PL/SQL匿名块几乎是必考的压轴题因为它能同时考察变量声明、SELECT INTO、异常处理这几个得分点。如果时间有限优先把题1到题5滚瓜烂熟这五道题涵盖了SQL语句的骨架其余题目更多是在此之上的组合变化。1.2 复习策略与刷题顺序我的复习建议顺序很关键先SQL后PL/SQL再数据库对象。SQL部分是地基SELECT怎么写、JOIN怎么写、聚合函数怎么分组这些不熟的话后面PL/SQL块里的SQL语句一样写不出来。我遇到过有同学背了一大堆DECLARE、EXCEPTION的语法结果进入模拟题后根本写不对正文因为UPDATE语句本身就没掌握。建议这样刷第一轮计时完成题1到题5先不要看解析把能写出来的写出来第二轮对照解析把没写出来的部分重点标注默写三遍第三轮再做题6到题10重点记忆关键词的拼写和语法位置。Oracle的SQL语法只要大小写不搞错、子句顺序不错基本能拿大部分分数不需要把整个参考手册背下来。2. 基础查询与多表连接题1、题2精讲2.1 题1过滤与排序的固定执行顺序原题查询employees表中部门编号为50的员工姓名与工资按工资从高到低显示。这里用最典型的employees表结构字段就是employee_id、first_name、last_name、salary、department_id等。标准写法SELECT first_name, last_name, salary FROM employees WHERE department_id 50 ORDER BY salary DESC;拆开解释一下为什么这道题是“送分题”也可能是“丢分题”。WHERE写在FROM之后、ORDER BY之前。SQL有严格的子句顺序这个顺序是FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY。有的同学把ORDER BY写到WHERE前面或者在SELECT前写ORDER BY都会语法报错写SQL时一旦看到“ORA-00933: SQL command not properly ended”多半就是这个原因。ORDER BY salary DESC表示按工资从高到低DESC代表降序如果漏掉DESCOracle默认升序ASC会变成工资从低到高。这种细节在机考时不会立刻报错但输出顺序和题目要求不符一样扣分。字符串字面量必须用单引号。number类型的50写不写引号都行但字符串条件如果写成IT_PROG就可能报ORA-00904之类的错误。双引号在SQL中一般用来括对象名初学者最容易混淆。执行之后会看到该部门下的员工记录按salary从大到小排列。如果没有匹配行查询会返回空结果但不会报错这是SQL比较友好的一点。这里还有一个批改作业时反复强调的点题目要求的是“姓名和工资”如果写完SELECT只选了salary而漏掉姓名列虽然查询不报错但结果不符合要求。期末阅卷是按点给分的经常有人因为少选列、多选了无关列被扣分攒起来就是十来分。2.2 题2多表连接为什么要用ON子句原题查询员工姓名和对应的部门名称要求使用ANSI标准连接语法。这道题的完整答案SELECT e.last_name, d.department_name FROM employees e JOIN departments d ON e.department_id d.department_id;关于表别名这里多讲几句。e和d是表别名不是可选项而是建议项。当两个表都有last_name或department_name时如果不写表的引用Oracle会报ORA-00918: column ambiguously defined。这题中last_name只存在于employees表department_name只存在于departments表不写前缀也能跑但为了严谨和扩展最好每次都写前缀。这个习惯能帮你避免多表查询时的列歧义坑。连接条件是ON e.department_id d.department_id。employees表通过department_id外键关联到departments表的主键。有些同学会顺手写成ON e.department_id d.department_id AND e.last_name d.last_name画蛇添足或者干脆忘掉ON子句直接JOIN后面接WHERE虽然旧式语法FROM employees e, departments d WHERE e.department_id d.department_id在很多环境也能跑但期末题目明确要求ANSI语法时用JOIN...ON更稳。还有一个很容易踩的坑JOIN不加ON会形成笛卡尔积。假设employees有107行、departments有27行不写ON会返回2889行而正确结果大约106行左右因为有一个员工没有部门号。以前给同学调试时就遇到这种情况他发现返回行数特别多还以为数据有问题实际上就是连接条件缺失。所以写完多表查询后可以用SELECT COUNT(*)检查返回行数这应该成为肌肉记忆。3. 聚合、子查询与数据更新题3到题53.1 题3GROUP BY与HAVING的协同工作原题统计每个部门的员工人数只显示人数超过5人的部门。标准写法SELECT department_id, COUNT(*) FROM employees GROUP BY department_id HAVING COUNT(*) 5;这道题表面考聚合函数COUNT实际考的是分组逻辑。理解分组很关键GROUP BY department_id会把数据按部门编号分成多个组每组一行结果SELECT里的字段有两类一类是分组依据本身department_id另一类是聚合函数COUNT(*)、SUM、AVG、MAX、MIN。如果在SELECT里写了department_name或者其他非聚合、非分组的列Oracle会直接报ORA-00979: not a GROUP BY expression。这个错误在期末卷面上出现频率很高。再解释HAVING为什么不能用WHERE替代。WHERE的执行时机是在分组之前它过滤的是“行”HAVING的执行时机是在分组之后它过滤的是“组”。查询要“人数大于5的部门”需要先算出每个部门的人数然后对组结果做筛选。可以在WHERE里写salary 5000来先过滤员工行但绝不能写WHERE COUNT(*) 5因为COUNT是在分组后才产生的执行到WHERE时根本没这个值。正确思路是先用WHERE过滤不需要参与分组的行再用GROUP BY分组最后用HAVING过滤组。这是SQL子句执行顺序的直接体现。另外给一个实用建议COUNT()和COUNT(1)在Oracle中的执行结果基本一致但写COUNT(department_id)会忽略department_id为NULL的行从而导致计数不一致。如果department_id允许为空建议明确使用COUNT()统计行数。期末题一般不会为难你考察这种细微差别但实际工作中早晚会遇到。3.2 题4单行子查询的运用与边界条件原题查询工资高于公司平均工资的员工信息。标准写法SELECT employee_id, last_name, salary FROM employees WHERE salary (SELECT AVG(salary) FROM employees);子查询分单行和多行。这里的AVG(salary)返回的是一个数值因此属于单行子查询可以用大于、小于、等于这些比较运算符。如果子查询不止返回一行例如写成WHERE salary (SELECT salary FROM employees WHERE department_id 10)而部门10有多个员工就会报ORA-01427: single-row subquery returns more than one row。讲一个更常见的变形如果要“查询工资大于本部门平均工资的员工”写法会变成相关子查询SELECT e.last_name, e.salary FROM employees e WHERE e.salary ( SELECT AVG(salary) FROM employees WHERE department_id e.department_id );其中e.department_id和外层查询联动这就是“相关子查询”。期末考试如果加难度往往会把题4改造成这个版本。相关子查询的执行逻辑可以理解为外层每一行进入时内层子查询用该行的department_id重新计算一次平均工资再比较salary是否大于这个平均值。做题时要养成检查子查询括号闭合的习惯。见过太多人在最后少写一个右括号导致ORA-00907: missing right parenthesis。最简单的检查办法是写完SQL后从左到右数一遍括号配对或者直接把子查询单独执行一遍确认它确实能返回一个值。3.3 题5UPDATE与事务回滚的完整操作链原题将department_id为10的员工工资上调10%修改后执行查询验证最后使用ROLLBACK撤销本次修改。这道题完整的操作过程分成四步-- 第一步确认修改前状态 SELECT department_id, salary FROM employees WHERE department_id 10; -- 第二步执行更新 UPDATE employees SET salary salary * 1.1 WHERE department_id 10; -- 第三步验证修改结果 SELECT department_id, salary FROM employees WHERE department_id 10; -- 第四步撤销修改 ROLLBACK; -- 验证回滚后的状态 SELECT department_id, salary FROM employees WHERE department_id 10;解析关键点。UPDATE的WHERE子句是安全阀门。如果不写WHERE整个employees表所有人的工资都会被乘以1.1这是最严重的低级错误。我建议在实验环境里养成的习惯是手写UPDATE之前先用相同WHERE的SELECT确认一下你要影响哪些行执行UPDATE后立刻再跑一次SELECT确认影响行数。这条经验永远不会过时。事务是这门课的重点概念。在Oracle中事务从第一条DML语句开始直到COMMIT或ROLLBACK结束。执行UPDATE之后数据在会话内可见但其他会话看不到因为事务尚未提交此时如果误操作了可以ROLLBACK撤回。这个特性叫读一致性Oracle靠undo表空间实现。很多同学在实验课卡在这一步明明UPDATE执行了SELECT也看到了结果但换一个SQL窗口一查数据没变急得团团转。原因就是没COMMIT事务没有结束数据只是处于“改而未交”的状态。这道题的延伸考点是如果此时执行COMMIT数据就永久生效再ROLLBACK也救不回来了。期末试卷常见陷阱题就是“UPDATE之后执行COMMIT再执行ROLLBACK问数据是否恢复原样”答案是“不会”。提交之后事务结束ROLLBACK只能影响提交前未提交的部分。理解这一点能帮你拿下一道选择题或简答题。4. 表结构、权限与视图题6到题84.1 题6建表与三类完整性约束原题创建学生表student包含student_id、student_name、age三个字段。要求student_id为主键student_name非空age取值范围在0到150之间。标准写法CREATE TABLE student ( student_id NUMBER(10) PRIMARY KEY, student_name VARCHAR2(50) NOT NULL, age NUMBER(3) CONSTRAINT ck_student_age CHECK (age BETWEEN 0 AND 150) );为什么age用NUMBER(3)因为0到150最多三位数占用空间小也直接满足约束。注意这里用的是列级约束写法PRIMARY KEY直接跟在列后NOT NULL也直接跟在列后。CHECK约束一般给一个名字格式是CONSTRAINT 约束名 CHECK (条件)。不命名的话Oracle会系统自动生成一个类似SYS_C00666的名字以后想删除约束会很麻烦。表级约束写法长这样CREATE TABLE student ( student_id NUMBER(10), student_name VARCHAR2(50) NOT NULL, age NUMBER(3), CONSTRAINT pk_student_id PRIMARY KEY (student_id), CONSTRAINT ck_student_age CHECK (age BETWEEN 0 AND 150) );两种写法期末都认但建议掌握表级约束写法因为当一个主键由多个字段组成时复合主键只能写成表级约束。比如选课表需要(student_id, course_id)作为联合主键就必须用CONSTRAINT pk_enroll PRIMARY KEY (student_id, course_id)。验证约束是否生效可以插入一条违反规则的数据。例如INSERT INTO student (student_id, student_name, age) VALUES (2019001, 张三, 200);运行后会报ORA-02290: check constraint (CK_STUDENT_AGE) violated看到这个报错就说明CHECK约束生效了。同理插入重复的student_id会违反PRIMARY KEY约束报ORA-00001: unique constraint violated插入NULL的student_name会违反NOT NULL约束。这种“故意跑一个错误再看报错信息”的方式是学习阶段最有效的验证方法之一期末时写SQL题如果要求“添加约束”就可以直接回忆起报错细节。4.2 题7用户创建与授权的最小不犯错方案原题创建一个新用户testuser并授予其连接数据库和创建数据库表的权限。标准写法CREATE USER testuser IDENTIFIED BY Pass123; GRANT CONNECT, RESOURCE TO testuser;这里稍微补充一下。CONNECT角色至少包含CREATE SESSION权限用来允许用户登录RESOURCE角色包含CREATE TABLE、CREATE SEQUENCE、CREATE VIEW等一组常用系统权限也就是允许这个用户在自己的模式中创建对象。题目只要求“连接数据库和创建数据表”所以这个GRANT语句组合是最稳妥、最符合常规教学库的方案。还需要提防一个坑Oracle 12c及更高版本引入了CDB/PDB架构。在某个PDB中创建用户时用户名前面加不加C##只有在CDB根容器中创建通用用户才要求C##前缀在普通PDB内创建普通用户不需要加前缀。学校实验环境一般直接连接PDB所以用CREATE USER testuser即可。如果连的是容器数据库根确实会报ORA-65096: invalid common user or role name。这个报错一出现先检查当前处于哪个容器。权限授予之后可以用ALTER USER testuser QUOTA UNLIMITED ON users;来配置表空间配额否则这个用户虽然能建表也可能因为配额为0而报ORA-01536。实验课里最容易出现的问题就是“用户已授权但仍无法建表”原因多半在这个配额上。期末大题一般只考GRANT语句但实际实验时一定要把配额也设上。4.3 题8视图的创建与WITH CHECK OPTION的行为约束原题创建一个视图emp_dept10只显示部门编号为10的员工信息要求通过视图进行DML操作时数据仍满足部门编号为10的条件。标准写法CREATE OR REPLACE VIEW emp_dept10 AS SELECT employee_id, last_name, salary, department_id FROM employees WHERE department_id 10 WITH CHECK OPTION;视图是一个虚拟表不实际存储数据底层查询引用的是employees表的行。CREATE OR REPLACE的意思是已存在同名视图则替换否则创建。考试可能不要求加OR REPLACE但加上更稳妥。WITH CHECK OPTION是重点。它的作用是所有通过该视图执行的INSERT和UPDATE操作都必须满足视图定义中的WHERE条件。举例来说-- 这条语句会成功因为department_id仍然是10 UPDATE emp_dept10 SET salary salary 100 WHERE employee_id 100; -- 这条语句会报ORA-01402: view WITH CHECK OPTION where-clause violation UPDATE emp_dept10 SET department_id 20 WHERE employee_id 100;第二条更新的意思是想把某条记录的部门号改成20但如果这条数据来自emp_dept10视图修改后视图的WHERE条件department_id10就不再成立所以Oracle直接拒绝。这能防止用户绕过视图的限制修改底层数据。在期末简答题里只要把“WITH CHECK OPTION用于保证通过视图修改的数据仍满足视图定义条件”这句话写清楚就能拿到关键分。视图还有一个常考考点删除视图的语句是DROP VIEW emp_dept10删除表格的语句是DROP TABLE student千万别把单词混了。另外视图上做DML有很多限制比如使用了聚合函数、GROUP BY、DISTINCT的复杂视图通常不能直接做DML操作。期末一般不会考到这么深但选择题偶尔会问记住“简单视图可更新复杂视图一般不可更新”即可。5. PL/SQL与索引题9、题10精讲5.1 题9PL/SQL匿名块的完整骨架原题编写一个PL/SQL匿名块声明一个变量保存公司最高工资。如果最高工资对应的员工存在则将该最高工资上调300元并输出调整前的最高工资如果表为空则输出提示信息“暂无可更新员工”。标准答案SET SERVEROUTPUT ON; DECLARE v_max_salary employees.salary%TYPE; BEGIN SELECT MAX(salary) INTO v_max_salary FROM employees; IF v_max_salary IS NULL THEN DBMS_OUTPUT.PUT_LINE(暂无可更新员工); RETURN; END IF; UPDATE employees SET salary salary 300 WHERE salary v_max_salary; DBMS_OUTPUT.PUT_LINE(调整前最高工资为: || v_max_salary); EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE(暂无可更新员工); END; /这是PL/SQL题的经典骨架。DECLARE部分声明变量变量类型使用了employees.salary%TYPE这是一个很加分的写法表示变量类型和表中salary列一致避免了写错类型。BEGIN开始执行体SELECT...INTO把查询结果放进变量UPDATE更新数据DBMS_OUTPUT.PUT_LINE输出信息EXCEPTION处理异常END结束末尾的斜杠/意味着提交这个PL/SQL块并执行。几个容易扣分的点。第一PL/SQL中的赋值符号是:不是等号。SELECT...INTO也属于赋值输入。第二DBMS_OUTPUT.PUT_LINE的输出默认不显示需要在SQLPlus中先执行SET SERVEROUTPUT ON否则只看到“PL/SQL procedure successfully completed”看不到输出的文字。很多同学考试时输出不出来以为是程序写错其实是没打开serveroutput。第三END后面要写分号块主体外层是BEGIN...END;如果要让SQLPlus识别并执行整个块末尾还要加上斜杠/。少了分号或斜杠执行时会一直显示continue提示像一个没写完的句子。题目还要求“如果表为空则输出提示”。这里有一个容易混淆的细节SELECT MAX(salary) INTO v_max_salary在employees表为空时MAX聚合函数会返回一行NULL值大多数Oracle版本中SELECT INTO对返回的NULL不会触发NO_DATA_FOUND。所以我用了IF v_max_salary IS NULL THEN直接判断这样最稳妥EXCEPTION块负责处理没有返回行等异常情况。两种可能的场景都覆盖到了这道题就能稳稳拿满分。5.2 题10索引的创建、删除与性能代价原题为employees表的salary列创建一个索引写出创建语句如果该索引不再需要写出删除语句。标准答案CREATE INDEX idx_emp_salary ON employees(salary); DROP INDEX idx_emp_salary;索引在Oracle中的作用类似书的目录。没有索引时SELECT语句查询salary可能需要扫描整张表的所有行索引可以让我们用B-Tree结构快速定位对应记录。但索引不是越多越好每次INSERT、UPDATE、DELETE都要同步维护索引写入性能会下降索引也占表空间。期末考试最常考的就是CREATE INDEX和DROP INDEX两个语法。DROP INDEX这里有个高频错误很多人会写成DROP INDEX idx_emp_salary ON employees或者在删除时又带上表名。正确写法就是DROP INDEX 索引名不需要再写表名Oracle通过索引名就能定位。另外复合索引的创建格式可以是CREATE INDEX idx_emp_dept ON employees(department_id, salary)查询条件同时包含这两个字段时复合索引通常更有效。还有一类延伸题创建主键或唯一约束时Oracle会自动创建唯一索引所以不需要手动为唯一列再创建一个普通索引。手贱再去建会得到重复索引浪费空间。理解“自动索引”这个概念对答选择题很有帮助。6. 考场答题技巧与高频扣分点6.1 写SQL之前先在心里跑一遍执行顺序我做题时积累了一个非常实用的习惯无论题目多简单先在草稿纸上或者脑子里过一遍SQL子句执行顺序FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY。这个顺序不只是规则它决定了能不能在正确的位置写过滤条件。比如题3如果忘掉HAVING就一定不会在GROUP BY之后过滤组自然做不对。ORDER BY虽然是逻辑上的倒数第二步但它在物理上写在整个语句的最后这两个“位置”不要搞混。还有一个加分习惯在关键语句前加注释标注意图。比如题5的UPDATE前面写一行注释“WHERE强制限定范围”阅卷老师看到也知道你是清楚得分点的。考试不是写论文能展示你对考点有充分意识分数往往更稳。6.2 高频扣分点速查表把我在批改和实际指导中反复看到的错误汇总成一张表错误类型错误示例正确做法原因子句顺序错误ORDER BY写WHERE前FROM → WHERE → SELECT → ORDER BYSQL语法固定顺序字符串用双引号WHERE name 张三WHERE name 张三双引号用于对象名UPDATE缺WHEREUPDATE emp SET salary...始终写WHERE限定行范围否则更新全表等号与赋值混淆salary 100放在PL/SQL变量赋值中变量 : 100PL/SQL赋值用:忘记连接条件JOIN后不写ON写上等值连接条件否则出现笛卡尔积删除索引带表名DROP INDEX idx ON employeesDROP INDEX idxOracle语法不识别ON视图可更新性混淆认为所有视图都能DML简单视图可更新复杂视图受限底层查询决定这张表建议考前最后一天看一遍。刚复习时不建议背着张表硬记而是做到那些题、撞过几次ORA报错之后再回来看印象会深得多。7. 期末自救锦囊从会看到会写的最后一公里最后聊点心得。数据库这门课真正的难点不是“看懂”而是“写对”。看懂SELECT和看不出自己漏了WHERE在分数上的差别很大。复习阶段可以给自己准备一个错题本不是抄题目而是抄报错。ORA-01427、ORA-00979、ORA-01402、ORA-02290这几个报错代码遇到时全部抄一遍然后写清楚触发原因和解决思路。到考前再翻一遍比硬背一百个知识点更有效。我自己也是这样一路过来的先会用再理解原理。不要怕报错不要怕一个命令执行三分钟。多执行、多读报错信息、多用DESCRIBE验证表结构这比什么都重要。把这10道题写两三遍Oracle期末的大题基本就能稳稳收住。