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