1. 为什么存储过程总被出成程序填空题如果你经历过数据库相关的笔试一定对程序填空题不陌生。题目给你一段残缺的存储过程代码挖掉几个空让你补上DECLARE、CURSOR、OPEN、FETCH、EXCEPTION之类的关键字。很多新手觉得这类题是背单词背熟关键字就能过。但实际情况是——背了关键字也填不对因为存储过程考察的从来不是语法本身而是你有没有理解整个代码块的运行顺序。我见过太多人栽在同一个地方把OPEN写在FETCH之后或者把EXCEPTION放在了BEGIN中间更常见的是一看到动态SQL就忘记用EXECUTE IMMEDIATE。这些错误背后的原因都一样对存储过程的骨架缺乏整体认知。存储过程不是一段顺序执行的普通SQL脚本它是一个有声明区、执行区、异常处理区的完整程序块每个区域有固定的位置和严格的语法约束。出题人喜欢用存储过程出填空题恰恰因为它能一次性考察好几个能力点。第一是声明与作用域的理解变量在哪里声明、游标在哪里声明、异常处理器在哪里声明顺序错了直接编译失败第二是流程控制逻辑循环怎么进、怎么出NOT FOUND之后怎么跳转第三是SQL与程序语言的结合什么时候用普通SQL、什么时候必须上动态SQL。这三项能力对应到实际工作中就是你能不能写出一个像统计当前库下各表数据总量这样真实可用的生产脚本。先给大家一张通用骨架图三类主流数据库都大同小异声明区Define变量、Define游标、Define异常处理器执行区打开游标、循环遍历、动态执行SQL、赋值累加结束区关闭游标、提交或输出结果这个结构用大白话讲就是一条流水线先把工具准备好然后启动传送带取一件货、处理一件货最后收工整理。理解了这个大框架填空题里那些空就不会是孤立的单词而是流水线上必然要有的环节。2. 三种主流数据库的存储过程声明差异大多数教科书讲存储过程只讲一种数据库但实际考卷和面试题会横向串着问。MySQL、Oracle、openGauss这三家的存储过程语法放一起对比你会立刻发现核心逻辑完全一样差的只是外衣。2.1 MySQL定界符与BEGIN...ENDMySQL的存储过程定义里最容易填错的是DELIMITER和BEGIN...END。为什么需要DELIMITER因为MySQL默认用分号结束一条语句而存储过程内部有大量分号如果不临时改变定界符客户端会在半路就认为语句结束了。所以标准写法是DELIMITER $$ CREATE PROCEDURE proc_name() BEGIN -- 过程体 END$$ DELIMITER ;填空题最喜欢在这个地方挖空而且空经常是$$本身。有人会问能不能不用DELIMITER在命令行客户端里基本不行图形化工具可能能绕过但考试和面试一定按标准写法来。MySQL里的变量声明必须放在BEGIN之后、任何语句之前这点和Java里变量声明必须在方法体开头有相似之处但比Java更严格。参数模式用IN、OUT、INOUT比如IN p_id INT表示传入参数OUT p_count INT表示输出参数。2.2 OracleAS与独立的BEGIN...END块Oracle的存储过程没有DELIMITER这个概念因为PL/SQL块本身有明确的开始和结束标记。它的声明区和执行区是分开的声明在AS或IS之后执行区在BEGIN之后CREATE OR REPLACE PROCEDURE proc_name IS v_count NUMBER; -- 声明区 BEGIN SELECT COUNT(*) INTO v_count FROM users; DBMS_OUTPUT.PUT_LINE(v_count); END proc_name; /注意几个高频考点IS和AS等价可以互换DECLARE关键字只在匿名块里使用命名存储过程用IS/AS很多人习惯在CREATE PROCEDURE后面写DECLARE这是错的末尾的END后面可以跟过程名也可以不跟但跟了过程名就要写对否则编译报错。最后那个斜杠/是SQL*Plus等客户端的执行命令不是PL/SQL语法的一部分但考题里经常出现别漏写。2.3 openGauss兼容Oracle的写法也有自己的脾气openGauss这几年在国产数据库里出镜率很高考题里也越来越多。它的存储过程设计上有两条路线Oracle兼容模式A模式下写法基本可以照搬Oracle的CREATE OR REPLACE PROCEDURE ... IS ... BEGIN ... END;PostgreSQL兼容模式B模式下则习惯用带$$的DO块或者CREATE FUNCTION。实际考试中常出现的是Oracle兼容写法比如在B兼容模式下一些细节会有差异。我自己的经验是不要把openGauss完全当Oracle来写至少要在环境里跑一遍验证。比如RAISE NOTICE输出提示信息和Oracle的DBMS_OUTPUT.PUT_LINE在客户端显示时机上就不同前者更容易在函数和过程中用。这类差异恰恰是出题人喜欢挖坑的点。2.4 参数模式IN/OUT/INOUT三类数据库的参数模式语义基本一致但写法细节有区别我用一张表总结数据库输入参数输出参数输入输出参数默认模式MySQLINOUTINOUTINOracleINOUTIN OUTINopenGaussINOUTIN OUTIN注意Oracle和openGauss的IN OUT中间有空格这是一个经典填空题空位。默认模式都是IN意味着不写模式时按输入参数处理。参数顺序也很重要MySQL要求输出参数放在参数列表中的对应位置调用时用CALL proc_name(var)传入用户变量接收结果Oracle和openGauss的OUT参数在调用时传入一个变量占位过程内赋值后调用方就能读到。3. 一个必考的实战题统计当前库各表数据总量题目描述通常是这样一句话建一个统计当前库下各表数据总量的存储过程。看起来简单实际能完整写出来的人不多。这个题好就好在它把所有核心考点全串起来了查元数据、游标遍历、动态SQL、数值累加、异常处理、结果输出。3.1 先想清楚需求统计口径与输出方式很多人上来就写SELECT COUNT(*) FROM information_schema.tables这就是没理解题意。information_schema.tables里的table_rows只是优化器估算值在InnoDB引擎下尤其不准差了十倍甚至百倍都不奇怪。真正要精确统计每张表的行数必须逐表执行COUNT(*)。输出方式也有三种第一种用OUT参数返回总数适合被其他程序调用第二种把每张表的行数写到临时表里方便后续查询第三种直接打印到控制台。我给的建议是在填空题场景里优先选择写入临时表并最终输出明细的方案因为它能同时展示游标循环、动态SQL、临时表操作三个知识点评分点最多。3.2 MySQL实现游标动态SQL异常处理先看完整代码我加了行号方便讲解DELIMITER $$ CREATE PROCEDURE sp_count_all_tables() BEGIN DECLARE v_finished INT DEFAULT 0; DECLARE v_table_name VARCHAR(64); DECLARE cur_tables CURSOR FOR SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA DATABASE() AND TABLE_TYPE BASE TABLE; DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_finished 1; DROP TEMPORARY TABLE IF EXISTS tmp_row_counts; CREATE TEMPORARY TABLE tmp_row_counts ( table_name VARCHAR(64) PRIMARY KEY, row_count BIGINT ); OPEN cur_tables; fetch_loop: LOOP FETCH cur_tables INTO v_table_name; IF v_finished 1 THEN LEAVE fetch_loop; END IF; SET sql : CONCAT(SELECT COUNT(*) INTO cnt FROM , v_table_name, ); PREPARE stmt FROM sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; INSERT INTO tmp_row_counts(table_name, row_count) VALUES (v_table_name, cnt); END LOOP; CLOSE cur_tables; SELECT * FROM tmp_row_counts ORDER BY table_name; DROP TEMPORARY TABLE IF EXISTS tmp_row_counts; END$$ DELIMITER ;拆开看几个关键空位DECLARE ... CURSOR FOR是游标声明注意它必须在变量声明之后DECLARE CONTINUE HANDLER FOR NOT FOUND是一个条件处理器这里的NOT FOUND是SQLSTATE 02000的别名当FETCH读不到下一行时会把v_finished置为1OPEN和CLOSE是固定的成对操作漏掉任何一个都要扣分。SET sql : CONCAT(SELECT COUNT(*) INTO cnt FROM, v_table_name, );这一段是动态SQL用开头的用户变量是可以在PREPARE中使用的。这里有个实测心得MySQL不同版本对PREPARE中SELECT...INTO的支持存在差异有的环境会报错更稳的写法是SET sql : CONCAT(SET cnt (SELECT COUNT(*) FROM , v_table_name, ));两种写法本质一样后者最终都用cnt保存结果。如果你在考试中拿不准版本优先写SET cnt (SELECT ...)这种形式兼容性更好。3.3 Oracle实现user_tables与EXECUTE IMMEDIATEOracle的元数据视图不叫information_schema而是user_tables。动态SQL用的是EXECUTE IMMEDIATE比MySQL的PREPARE简洁不少CREATE OR REPLACE PROCEDURE sp_count_all_tables IS v_cnt NUMBER; v_total NUMBER : 0; BEGIN FOR rec IN (SELECT table_name FROM user_tables ORDER BY table_name) LOOP EXECUTE IMMEDIATE SELECT COUNT(*) FROM || rec.table_name INTO v_cnt; DBMS_OUTPUT.PUT_LINE(表 || rec.table_name || : || v_cnt); v_total : v_total v_cnt; END LOOP; DBMS_OUTPUT.PUT_LINE(当前用户下所有表记录总数: || v_total); END sp_count_all_tables; /这个版本用FOR ... IN (SELECT ...)隐式游标代替了显式的游标声明和OPEN/FETCH/CLOSE三步Oracle里这叫游标FOR循环PHP用户应该很熟悉这种自动遍历的写法。出题人如果在填空题里给这种写法空位通常会挖在EXECUTE IMMEDIATE ... INTO中间的动态SQL字符串拼接上。注意EXECUTE IMMEDIATE和INTO的顺序很关键正确写法是先写动态SQL字符串再写INTO v_cnt最后是绑定变量。这个顺序和直觉相反很多人会写成EXECUTE IMMEDIATE INTO v_cnt SELECT ...这就是送分题变送命题。3.4 openGauss实现语法兼容与注意事项openGauss里最稳妥的写法是结合Oracle风格和B兼容模式的优势我用的版本如下CREATE OR REPLACE PROCEDURE sp_count_all_tables() IS v_cnt BIGINT; BEGIN FOR rec IN (SELECT tablename FROM pg_tables WHERE schemaname current_schema()) LOOP EXECUTE IMMEDIATE SELECT count(*) FROM || rec.tablename INTO v_cnt; RAISE NOTICE 表 % 行数: %, rec.tablename, v_cnt; END LOOP; END; /两点注意事项。第一pg_tables视图来自PostgreSQL系字段名是tablename和schema_name而不是Oracle的table_name在Oracle兼容模式下你也能访问user_tables但前提是把数据库初始化成A兼容格式。第二RAISE NOTICE的信息输出到服务端日志和客户端连接上并不像DBMS_OUTPUT.PUT_LINE那样需要SET SERVEROUTPUT ON用起来更方便但格式符是PG系的%不是Oracle的||拼接。遇到题目问openGauss统计各表行数先看清楚考的是哪种兼容模式再动手。4. 这类填空题最常见的挖空位置与失分点我统计过身边人做这类题的错误分布声明段和游标相关的错误占了七成以上。下面按出错频率一个一个说。4.1 声明段变量作用域与%TYPEMySQL的DECLARE只允许出现在BEGIN...END的最开头而且不能和DEFAULT混在一起写错位置。很多人把游标声明放在普通变量声明之前这在MySQL里是允许的——实际上官方文档要求游标声明必须在变量声明之后、处理器声明之前。这个顺序本身就是考点。Oracle和openGauss里有个加分项用%TYPE或%ROWTYPE声明变量。比如v_count users.id%TYPE;意思是v_count的类型跟着users表的id字段走以后表结构变更时变量类型自动适配不容易出类型不匹配的错误。填空题如果给IS后面留空填v_count NUMBER;没问题但填v_count users.id%TYPE;更能体现水平而且某些评分标准里会特别标注这种写法。4.2 游标使用OPEN/FETCH/CLOSE三步走显式游标的生命周期很死板OPEN一个游标用FETCH ... INTO取一行处理完再FETCH直到取不到数据最后CLOSE释放资源。缺了CLOSE在长连接场景下游标会一直占着内存和句柄积少成多就出问题。FETCH取不到数据时会发生什么MySQL里如果没有CONTINUE HANDLER FOR NOT FOUND存储过程会直接抛异常退出Oracle里则会抛出NO_DATA_FOUND异常。所以要实现遍历完所有表就结束循环这个需求必须配异常处理机制。MySQL的写法是DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_finished 1;Oracle的写法是EXCEPTION WHEN NO_DATA_FOUND THEN NULL;注意Oracle的EXCEPTION块必须放在BEGIN...END的末尾也就是说它前面是正常逻辑后面是异常捕获这个位置关系也是填空常客。4.3 动态SQL与绑定变量为什么要用动态SQL因为表名不能直接作为绑定变量传入普通SQL。你写成SELECT COUNT(*) FROM :tbl_name是行不通的大多数数据库不允许表名绑定。这时就只能把表名拼进SQL字符串再交给PREPARE或EXECUTE IMMEDIATE执行。但动态SQL最忌讳的是拼接用户输入而不做校验。统计当前库数据总量这个场景里表名来自元数据视图相对可控但生产环境里如果表名来自外部输入必须用白名单校验。这里分享一个小技巧MySQL里用反引号把表名包起来Oracle和openGauss里可以用双引号包表名能避免表名恰好是保留字时导致的语法错误。4.4 异常处理NOT FOUND与WHEN OTHERSMySQL的条件处理器除了CONTINUE还有EXIT区别在于遇到异常时是继续执行还是跳出当前块。统计行数这个场景用CONTINUE合适因为NOT FOUND只是循环结束的标志不是真正的错误。适合用EXIT的场景是读文件、连外部接口遇到错误干脆结束整个过程。Oracle里除了WHEN NO_DATA_FOUND还有WHEN OTHERS兜底异常它是万能捕获器。我见过不少人在WHEN OTHERS里不写任何处理逻辑直接NULL这其实是掩盖错误调试时非常痛苦。至少要DBMS_OUTPUT.PUT_LINE(SQLERRM)看一眼错误消息或者用RAISE重新抛出。考试填空如果问异常处理段应该写什么WHEN OTHERS THEN ...这个骨架是必须有的。5. 排查与调试心得从填空能填对到过程能跑通会填题不等于会写过程。我带新人时最常看到的场景是笔试满分一上真实环境立刻卡壳。存储过程不像普通SQL有结果集反馈错误信息又简短排查起来需要一套自己的方法。5.1 调试三步法先查语法再查权限最后查数据第一步是看编译能否通过。MySQL用SHOW WARNINGS或者直接看CREATE PROCEDURE的报错信息Oracle在SQL*Plus里通常能精确到哪一行openGauss的报错信息更细一般会指出字符位置。大部分语法错误都是标点符号或关键字顺序问题仔细读一遍就能发现。第二步查权限。MySQL的PROCESS、SELECT权限不足Oracle要CREATE PROCEDURE权限openGauss的默认权限模型和Oracle还有区别这个不细看容易一头雾水。有一个快速验证方法把过程体里的动态SQL换成普通SQL手动执行一遍如果能跑通而过程里跑不通大概率是权限或角色会话环境问题。第三步查数据。在过程中临时加一句输出把关键变量打出来。MySQL可以在循环里SELECT cnt;Oracle用DBMS_OUTPUT.PUT_LINEopenGauss用RAISE NOTICE。别嫌土这是我调试存储过程用得最多的手段。逐表统计的脚本如果数字不对先看是不是漏掉了TABLE_TYPE BASE TABLE这个过滤条件再看游标是否把系统表也算进去了。5.2 容易被忽略的细节临时表的生命周期我上面写的MySQL示例里用了临时表tmp_row_counts这里有个经典坑TEMPORARY TABLE在当前会话内有效存储过程执行结束后并不会自动消失所以脚本开头应该DROP TEMPORARY TABLE IF EXISTS结尾也可以再清理一次否则同一个会话连续调用两次会报表已存在。这个细节在填空题里通常不会出现但实际运行一定会遇到。另一个坑是临时表和PREPARE共用时如果临时表是动态创建的表PREPARE里的SQL可能引用不到因为语法解析发生在执行前。解决方法是先创建临时表结构再在动态SQL中只做INSERT或SELECT操作不要动态创建临时表。上面的示例就是在过程开头静态创建临时表动态SQL只负责数行数这是最稳的配合方式。5.3 我自己常用的学习路线如果是新手想彻底掌握存储过程我不建议直接刷题。先把三件事做扎实第一熟练读懂一个最简单的无参数、无变量、只有一段SQL的过程第二在上面加一个变量和SET赋值第三把游标循环异常处理器这套组合练熟。这套组合练熟了再去看动态SQL和事务控制会发现一通百通。我自己带人的时候还要求加一个扩展练习把统计各表行数的脚本改造成只统计指定前缀的表同时把结果按行数从大到小排序。这个练习会迫使人去思考参数传递、LIKE匹配、排序输出三个问题做完之后再回头看填空题每个空都能看懂出题人为什么要挖在这里。最后分享一个压箱底的经验写存储过程时尽量在代码顶部注释里写明这个过程的输入是什么、输出是什么、哪些情况可能抛异常。看似废话但三个月后你自己回来看这段代码就会感谢这行注释。程序填空题考的是语法和逻辑真实项目里考的是可维护性——两者都别落下。