1. 為什么存儲過程總被出成程序填空題如果你經(jīng)歷過數(shù)據(jù)庫相關(guān)的筆試一定對程序填空題不陌生。題目給你一段殘缺的存儲過程代碼挖掉幾個空讓你補上DECLARE、CURSOR、OPEN、FETCH、EXCEPTION之類的關(guān)鍵字。很多新手覺得這類題是背單詞背熟關(guān)鍵字就能過。但實際情況是——背了關(guān)鍵字也填不對因為存儲過程考察的從來不是語法本身而是你有沒有理解整個代碼塊的運行順序。我見過太多人栽在同一個地方把OPEN寫在FETCH之后或者把EXCEPTION放在了BEGIN中間更常見的是一看到動態(tài)SQL就忘記用EXECUTE IMMEDIATE。這些錯誤背后的原因都一樣對存儲過程的骨架缺乏整體認知。存儲過程不是一段順序執(zhí)行的普通SQL腳本它是一個有聲明區(qū)、執(zhí)行區(qū)、異常處理區(qū)的完整程序塊每個區(qū)域有固定的位置和嚴格的語法約束。出題人喜歡用存儲過程出填空題恰恰因為它能一次性考察好幾個能力點。第一是聲明與作用域的理解變量在哪里聲明、游標在哪里聲明、異常處理器在哪里聲明順序錯了直接編譯失敗第二是流程控制邏輯循環(huán)怎么進、怎么出NOT FOUND之后怎么跳轉(zhuǎn)第三是SQL與程序語言的結(jié)合什么時候用普通SQL、什么時候必須上動態(tài)SQL。這三項能力對應(yīng)到實際工作中就是你能不能寫出一個像統(tǒng)計當(dāng)前庫下各表數(shù)據(jù)總量這樣真實可用的生產(chǎn)腳本。先給大家一張通用骨架圖三類主流數(shù)據(jù)庫都大同小異聲明區(qū)Define變量、Define游標、Define異常處理器執(zhí)行區(qū)打開游標、循環(huán)遍歷、動態(tài)執(zhí)行SQL、賦值累加結(jié)束區(qū)關(guān)閉游標、提交或輸出結(jié)果這個結(jié)構(gòu)用大白話講就是一條流水線先把工具準備好然后啟動傳送帶取一件貨、處理一件貨最后收工整理。理解了這個大框架填空題里那些空就不會是孤立的單詞而是流水線上必然要有的環(huán)節(jié)。2. 三種主流數(shù)據(jù)庫的存儲過程聲明差異大多數(shù)教科書講存儲過程只講一種數(shù)據(jù)庫但實際考卷和面試題會橫向串著問。MySQL、Oracle、openGauss這三家的存儲過程語法放一起對比你會立刻發(fā)現(xiàn)核心邏輯完全一樣差的只是外衣。2.1 MySQL定界符與BEGIN...ENDMySQL的存儲過程定義里最容易填錯的是DELIMITER和BEGIN...END。為什么需要DELIMITER因為MySQL默認用分號結(jié)束一條語句而存儲過程內(nèi)部有大量分號如果不臨時改變定界符客戶端會在半路就認為語句結(jié)束了。所以標準寫法是DELIMITER $$ CREATE PROCEDURE proc_name() BEGIN -- 過程體 END$$ DELIMITER ;填空題最喜歡在這個地方挖空而且空經(jīng)常是$$本身。有人會問能不能不用DELIMITER在命令行客戶端里基本不行圖形化工具可能能繞過但考試和面試一定按標準寫法來。MySQL里的變量聲明必須放在BEGIN之后、任何語句之前這點和Java里變量聲明必須在方法體開頭有相似之處但比Java更嚴格。參數(shù)模式用IN、OUT、INOUT比如IN p_id INT表示傳入?yún)?shù)OUT p_count INT表示輸出參數(shù)。2.2 OracleAS與獨立的BEGIN...END塊Oracle的存儲過程沒有DELIMITER這個概念因為PL/SQL塊本身有明確的開始和結(jié)束標記。它的聲明區(qū)和執(zhí)行區(qū)是分開的聲明在AS或IS之后執(zhí)行區(qū)在BEGIN之后CREATE OR REPLACE PROCEDURE proc_name IS v_count NUMBER; -- 聲明區(qū) BEGIN SELECT COUNT(*) INTO v_count FROM users; DBMS_OUTPUT.PUT_LINE(v_count); END proc_name; /注意幾個高頻考點IS和AS等價可以互換DECLARE關(guān)鍵字只在匿名塊里使用命名存儲過程用IS/AS很多人習(xí)慣在CREATE PROCEDURE后面寫DECLARE這是錯的末尾的END后面可以跟過程名也可以不跟但跟了過程名就要寫對否則編譯報錯。最后那個斜杠/是SQL*Plus等客戶端的執(zhí)行命令不是PL/SQL語法的一部分但考題里經(jīng)常出現(xiàn)別漏寫。2.3 openGauss兼容Oracle的寫法也有自己的脾氣openGauss這幾年在國產(chǎn)數(shù)據(jù)庫里出鏡率很高考題里也越來越多。它的存儲過程設(shè)計上有兩條路線Oracle兼容模式A模式下寫法基本可以照搬Oracle的CREATE OR REPLACE PROCEDURE ... IS ... BEGIN ... END;PostgreSQL兼容模式B模式下則習(xí)慣用帶$$的DO塊或者CREATE FUNCTION。實際考試中常出現(xiàn)的是Oracle兼容寫法比如在B兼容模式下一些細節(jié)會有差異。我自己的經(jīng)驗是不要把openGauss完全當(dāng)Oracle來寫至少要在環(huán)境里跑一遍驗證。比如RAISE NOTICE輸出提示信息和Oracle的DBMS_OUTPUT.PUT_LINE在客戶端顯示時機上就不同前者更容易在函數(shù)和過程中用。這類差異恰恰是出題人喜歡挖坑的點。2.4 參數(shù)模式IN/OUT/INOUT三類數(shù)據(jù)庫的參數(shù)模式語義基本一致但寫法細節(jié)有區(qū)別我用一張表總結(jié)數(shù)據(jù)庫輸入?yún)?shù)輸出參數(shù)輸入輸出參數(shù)默認模式MySQLINOUTINOUTINOracleINOUTIN OUTINopenGaussINOUTIN OUTIN注意Oracle和openGauss的IN OUT中間有空格這是一個經(jīng)典填空題空位。默認模式都是IN意味著不寫模式時按輸入?yún)?shù)處理。參數(shù)順序也很重要MySQL要求輸出參數(shù)放在參數(shù)列表中的對應(yīng)位置調(diào)用時用CALL proc_name(var)傳入用戶變量接收結(jié)果Oracle和openGauss的OUT參數(shù)在調(diào)用時傳入一個變量占位過程內(nèi)賦值后調(diào)用方就能讀到。3. 一個必考的實戰(zhàn)題統(tǒng)計當(dāng)前庫各表數(shù)據(jù)總量題目描述通常是這樣一句話建一個統(tǒng)計當(dāng)前庫下各表數(shù)據(jù)總量的存儲過程?？雌饋砗唵螌嶋H能完整寫出來的人不多。這個題好就好在它把所有核心考點全串起來了查元數(shù)據(jù)、游標遍歷、動態(tài)SQL、數(shù)值累加、異常處理、結(jié)果輸出。3.1 先想清楚需求統(tǒng)計口徑與輸出方式很多人上來就寫SELECT COUNT(*) FROM information_schema.tables這就是沒理解題意。information_schema.tables里的table_rows只是優(yōu)化器估算值在InnoDB引擎下尤其不準差了十倍甚至百倍都不奇怪。真正要精確統(tǒng)計每張表的行數(shù)必須逐表執(zhí)行COUNT(*)。輸出方式也有三種第一種用OUT參數(shù)返回總數(shù)適合被其他程序調(diào)用第二種把每張表的行數(shù)寫到臨時表里方便后續(xù)查詢第三種直接打印到控制臺。我給的建議是在填空題場景里優(yōu)先選擇寫入臨時表并最終輸出明細的方案因為它能同時展示游標循環(huán)、動態(tài)SQL、臨時表操作三個知識點評分點最多。3.2 MySQL實現(xiàn)游標動態(tài)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 ;拆開看幾個關(guān)鍵空位DECLARE ... CURSOR FOR是游標聲明注意它必須在變量聲明之后DECLARE CONTINUE HANDLER FOR NOT FOUND是一個條件處理器這里的NOT FOUND是SQLSTATE 02000的別名當(dāng)FETCH讀不到下一行時會把v_finished置為1OPEN和CLOSE是固定的成對操作漏掉任何一個都要扣分。SET sql : CONCAT(SELECT COUNT(*) INTO cnt FROM, v_table_name, );這一段是動態(tài)SQL用開頭的用戶變量是可以在PREPARE中使用的。這里有個實測心得MySQL不同版本對PREPARE中SELECT...INTO的支持存在差異有的環(huán)境會報錯更穩(wěn)的寫法是SET sql : CONCAT(SET cnt (SELECT COUNT(*) FROM , v_table_name, ));兩種寫法本質(zhì)一樣后者最終都用cnt保存結(jié)果。如果你在考試中拿不準版本優(yōu)先寫SET cnt (SELECT ...)這種形式兼容性更好。3.3 Oracle實現(xiàn)user_tables與EXECUTE IMMEDIATEOracle的元數(shù)據(jù)視圖不叫information_schema而是user_tables。動態(tài)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(當(dāng)前用戶下所有表記錄總數(shù): || v_total); END sp_count_all_tables; /這個版本用FOR ... IN (SELECT ...)隱式游標代替了顯式的游標聲明和OPEN/FETCH/CLOSE三步Oracle里這叫游標FOR循環(huán)PHP用戶應(yīng)該很熟悉這種自動遍歷的寫法。出題人如果在填空題里給這種寫法空位通常會挖在EXECUTE IMMEDIATE ... INTO中間的動態(tài)SQL字符串拼接上。注意EXECUTE IMMEDIATE和INTO的順序很關(guān)鍵正確寫法是先寫動態(tài)SQL字符串再寫INTO v_cnt最后是綁定變量。這個順序和直覺相反很多人會寫成EXECUTE IMMEDIATE INTO v_cnt SELECT ...這就是送分題變送命題。3.4 openGauss實現(xiàn)語法兼容與注意事項openGauss里最穩(wěn)妥的寫法是結(jié)合Oracle風(fēng)格和B兼容模式的優(yōu)勢我用的版本如下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 表 % 行數(shù): %, rec.tablename, v_cnt; END LOOP; END; /兩點注意事項。第一pg_tables視圖來自PostgreSQL系字段名是tablename和schema_name而不是Oracle的table_name在Oracle兼容模式下你也能訪問user_tables但前提是把數(shù)據(jù)庫初始化成A兼容格式。第二RAISE NOTICE的信息輸出到服務(wù)端日志和客戶端連接上并不像DBMS_OUTPUT.PUT_LINE那樣需要SET SERVEROUTPUT ON用起來更方便但格式符是PG系的%不是Oracle的||拼接。遇到題目問openGauss統(tǒng)計各表行數(shù)先看清楚考的是哪種兼容模式再動手。4. 這類填空題最常見的挖空位置與失分點我統(tǒng)計過身邊人做這類題的錯誤分布聲明段和游標相關(guān)的錯誤占了七成以上。下面按出錯頻率一個一個說。4.1 聲明段變量作用域與%TYPEMySQL的DECLARE只允許出現(xiàn)在BEGIN...END的最開頭而且不能和DEFAULT混在一起寫錯位置。很多人把游標聲明放在普通變量聲明之前這在MySQL里是允許的——實際上官方文檔要求游標聲明必須在變量聲明之后、處理器聲明之前。這個順序本身就是考點。Oracle和openGauss里有個加分項用%TYPE或%ROWTYPE聲明變量。比如v_count users.id%TYPE;意思是v_count的類型跟著users表的id字段走以后表結(jié)構(gòu)變更時變量類型自動適配不容易出類型不匹配的錯誤。填空題如果給IS后面留空填v_count NUMBER;沒問題但填v_count users.id%TYPE;更能體現(xiàn)水平而且某些評分標準里會特別標注這種寫法。4.2 游標使用OPEN/FETCH/CLOSE三步走顯式游標的生命周期很死板OPEN一個游標用FETCH ... INTO取一行處理完再FETCH直到取不到數(shù)據(jù)最后CLOSE釋放資源。缺了CLOSE在長連接場景下游標會一直占著內(nèi)存和句柄積少成多就出問題。FETCH取不到數(shù)據(jù)時會發(fā)生什么MySQL里如果沒有CONTINUE HANDLER FOR NOT FOUND存儲過程會直接拋異常退出Oracle里則會拋出NO_DATA_FOUND異常。所以要實現(xiàn)遍歷完所有表就結(jié)束循環(huán)這個需求必須配異常處理機制。MySQL的寫法是DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_finished 1;Oracle的寫法是EXCEPTION WHEN NO_DATA_FOUND THEN NULL;注意Oracle的EXCEPTION塊必須放在BEGIN...END的末尾也就是說它前面是正常邏輯后面是異常捕獲這個位置關(guān)系也是填空常客。4.3 動態(tài)SQL與綁定變量為什么要用動態(tài)SQL因為表名不能直接作為綁定變量傳入普通SQL。你寫成SELECT COUNT(*) FROM :tbl_name是行不通的大多數(shù)數(shù)據(jù)庫不允許表名綁定。這時就只能把表名拼進SQL字符串再交給PREPARE或EXECUTE IMMEDIATE執(zhí)行。但動態(tài)SQL最忌諱的是拼接用戶輸入而不做校驗。統(tǒng)計當(dāng)前庫數(shù)據(jù)總量這個場景里表名來自元數(shù)據(jù)視圖相對可控但生產(chǎn)環(huán)境里如果表名來自外部輸入必須用白名單校驗。這里分享一個小技巧MySQL里用反引號把表名包起來Oracle和openGauss里可以用雙引號包表名能避免表名恰好是保留字時導(dǎo)致的語法錯誤。4.4 異常處理NOT FOUND與WHEN OTHERSMySQL的條件處理器除了CONTINUE還有EXIT區(qū)別在于遇到異常時是繼續(xù)執(zhí)行還是跳出當(dāng)前塊。統(tǒng)計行數(shù)這個場景用CONTINUE合適因為NOT FOUND只是循環(huán)結(jié)束的標志不是真正的錯誤。適合用EXIT的場景是讀文件、連外部接口遇到錯誤干脆結(jié)束整個過程。Oracle里除了WHEN NO_DATA_FOUND還有WHEN OTHERS兜底異常它是萬能捕獲器。我見過不少人在WHEN OTHERS里不寫任何處理邏輯直接NULL這其實是掩蓋錯誤調(diào)試時非常痛苦。至少要DBMS_OUTPUT.PUT_LINE(SQLERRM)看一眼錯誤消息或者用RAISE重新拋出?？荚囂羁杖绻麊柈惓Ｌ幚矶螒?yīng)該寫什么WHEN OTHERS THEN ...這個骨架是必須有的。5. 排查與調(diào)試心得從填空能填對到過程能跑通會填題不等于會寫過程。我?guī)氯藭r最常看到的場景是筆試滿分一上真實環(huán)境立刻卡殼。存儲過程不像普通SQL有結(jié)果集反饋錯誤信息又簡短排查起來需要一套自己的方法。5.1 調(diào)試三步法先查語法再查權(quán)限最后查數(shù)據(jù)第一步是看編譯能否通過。MySQL用SHOW WARNINGS或者直接看CREATE PROCEDURE的報錯信息Oracle在SQL*Plus里通常能精確到哪一行openGauss的報錯信息更細一般會指出字符位置。大部分語法錯誤都是標點符號或關(guān)鍵字順序問題仔細讀一遍就能發(fā)現(xiàn)。第二步查權(quán)限。MySQL的PROCESS、SELECT權(quán)限不足Oracle要CREATE PROCEDURE權(quán)限openGauss的默認權(quán)限模型和Oracle還有區(qū)別這個不細看容易一頭霧水。有一個快速驗證方法把過程體里的動態(tài)SQL換成普通SQL手動執(zhí)行一遍如果能跑通而過程里跑不通大概率是權(quán)限或角色會話環(huán)境問題。第三步查數(shù)據(jù)。在過程中臨時加一句輸出把關(guān)鍵變量打出來。MySQL可以在循環(huán)里SELECT cnt;Oracle用DBMS_OUTPUT.PUT_LINEopenGauss用RAISE NOTICE。別嫌土這是我調(diào)試存儲過程用得最多的手段。逐表統(tǒng)計的腳本如果數(shù)字不對先看是不是漏掉了TABLE_TYPE BASE TABLE這個過濾條件再看游標是否把系統(tǒng)表也算進去了。5.2 容易被忽略的細節(jié)臨時表的生命周期我上面寫的MySQL示例里用了臨時表tmp_row_counts這里有個經(jīng)典坑TEMPORARY TABLE在當(dāng)前會話內(nèi)有效存儲過程執(zhí)行結(jié)束后并不會自動消失所以腳本開頭應(yīng)該DROP TEMPORARY TABLE IF EXISTS結(jié)尾也可以再清理一次否則同一個會話連續(xù)調(diào)用兩次會報表已存在。這個細節(jié)在填空題里通常不會出現(xiàn)但實際運行一定會遇到。另一個坑是臨時表和PREPARE共用時如果臨時表是動態(tài)創(chuàng)建的表PREPARE里的SQL可能引用不到因為語法解析發(fā)生在執(zhí)行前。解決方法是先創(chuàng)建臨時表結(jié)構(gòu)再在動態(tài)SQL中只做INSERT或SELECT操作不要動態(tài)創(chuàng)建臨時表。上面的示例就是在過程開頭靜態(tài)創(chuàng)建臨時表動態(tài)SQL只負責(zé)數(shù)行數(shù)這是最穩(wěn)的配合方式。5.3 我自己常用的學(xué)習(xí)路線如果是新手想徹底掌握存儲過程我不建議直接刷題。先把三件事做扎實第一熟練讀懂一個最簡單的無參數(shù)、無變量、只有一段SQL的過程第二在上面加一個變量和SET賦值第三把游標循環(huán)異常處理器這套組合練熟。這套組合練熟了再去看動態(tài)SQL和事務(wù)控制會發(fā)現(xiàn)一通百通。我自己帶人的時候還要求加一個擴展練習(xí)把統(tǒng)計各表行數(shù)的腳本改造成只統(tǒng)計指定前綴的表同時把結(jié)果按行數(shù)從大到小排序。這個練習(xí)會迫使人去思考參數(shù)傳遞、LIKE匹配、排序輸出三個問題做完之后再回頭看填空題每個空都能看懂出題人為什么要挖在這里。最后分享一個壓箱底的經(jīng)驗寫存儲過程時盡量在代碼頂部注釋里寫明這個過程的輸入是什么、輸出是什么、哪些情況可能拋異常?？此茝U話但三個月后你自己回來看這段代碼就會感謝這行注釋。程序填空題考的是語法和邏輯真實項目里考的是可維護性——兩者都別落下。