存儲過程這個老面孔在數(shù)據(jù)庫考試的程序填空題里是常駐選手在實(shí)際業(yè)務(wù)里也是處理復(fù)雜邏輯的一把好手。很多同學(xué)在填空題里能寫對CREATE PROCEDURE的拼寫但一碰到DELIMITER、游標(biāo)、異常處理就露怯很多開發(fā)同學(xué)說會用但真讓他寫一個帶事務(wù)、帶循環(huán)、帶游標(biāo)的存儲過程又容易卡殼。這篇就來把存儲過程從頭到尾拆一遍覆蓋MySQL、Oracle、openGauss三種主流數(shù)據(jù)庫的寫法差異還會帶上.NET生態(tài)里用SQLSugar調(diào)存儲過程的實(shí)戰(zhàn)姿勢。不管你是準(zhǔn)備考試、應(yīng)付面試還是要上手寫生產(chǎn)級存儲過程這篇都能給你一套能直接抄的思路。1. 存儲過程到底是什么——把它想象成數(shù)據(jù)庫里的“預(yù)制菜生產(chǎn)線”1.1 存儲過程的本質(zhì)不是“能放代碼”而是把邏輯沉淀在數(shù)據(jù)層很多人把存儲過程理解成“數(shù)據(jù)庫里的函數(shù)”這個說法對了一半。函數(shù)重在計(jì)算和返回存儲過程重在“執(zhí)行一段完整的事務(wù)邏輯”。它是一段預(yù)先編譯好的、保存在數(shù)據(jù)庫服務(wù)端的代碼塊客戶端通過一個名字加一組參數(shù)就能觸發(fā)它。我習(xí)慣用一個類比來解釋程序填空題里讓你補(bǔ)全的存儲過程就像一條預(yù)制菜生產(chǎn)線。原材料是表里的數(shù)據(jù)生產(chǎn)線的每個工位是一條SQL語句控制流轉(zhuǎn)的是IF、WHILE、CURSOR這些流程控制結(jié)構(gòu)最后出貨口是OUT參數(shù)或者結(jié)果集。調(diào)用方不需要關(guān)心生產(chǎn)線內(nèi)部怎么運(yùn)轉(zhuǎn)只需要把原料IN參數(shù)送進(jìn)去到點(diǎn)來取結(jié)果就行。這個設(shè)計(jì)帶來的核心價值有三個第一高性能——存儲過程在首次執(zhí)行時會被數(shù)據(jù)庫優(yōu)化器編譯后續(xù)執(zhí)行直接走緩存計(jì)劃尤其在循環(huán)調(diào)用場景下比反復(fù)發(fā)送SQL文本快得多第二低耦合——業(yè)務(wù)邏輯對客戶端隱藏修改內(nèi)部實(shí)現(xiàn)不需要改應(yīng)用代碼也不需要對客戶端暴露表結(jié)構(gòu)第三安全性可控——DBA可以只授予執(zhí)行權(quán)限而不授予底層表的讀寫權(quán)限。不過正因?yàn)檫壿嫵猎跀?shù)據(jù)庫里它也帶來了調(diào)試?yán)щy、版本管理不便、跨庫遷移成本高等問題。所以現(xiàn)在的架構(gòu)趨勢是“能不放就不放”但遇到復(fù)雜報(bào)表、批量數(shù)據(jù)處理、事務(wù)一致性要求高的場景存儲過程依然是不可替代的選擇。1.2 考試、面試和真實(shí)業(yè)務(wù)里存儲過程各占什么位置在考試的程序填空題里存儲過程主要考三件事語法結(jié)構(gòu)完整性、流程控制邏輯、參數(shù)與變量的使用。也就是說你不需要設(shè)計(jì)一個復(fù)雜的業(yè)務(wù)但要能準(zhǔn)確補(bǔ)全DELIMITER、BEGIN、DECLARE、END IF、FETCH這些關(guān)鍵字并且保證邏輯正確。在面試?yán)锟疾禳c(diǎn)會偏向“是否理解存儲過程的邊界”什么時候適合用、什么時候不能用、和事務(wù)如何配合、并發(fā)下有什么問題。在真實(shí)業(yè)務(wù)里尤其是銀行、電商、ERP這類系統(tǒng)存儲過程依然是核心交易鏈路里的?？蚈racle存儲過程、MySQL存儲過程、openGauss存儲過程都有大量存量代碼在跑。從搜索熱詞能看出一個趨勢——很多人在找“mysql存儲過程”和“oracle存儲過程”的語法對比還有“opengauss存儲過程”這種國產(chǎn)數(shù)據(jù)庫的寫法。這說明存儲過程不僅沒有過時反而因?yàn)閲a(chǎn)數(shù)據(jù)庫遷移再次成為技能剛需。不管底層數(shù)據(jù)庫怎么換存儲過程的思維模型是通用的只是語法細(xì)節(jié)有差異。2. 聲明語法逐行拆解——程序填空題的得分關(guān)鍵2.1 從一段最樸素的MySQL存儲過程講起先看一段最簡單的MySQL存儲過程也是程序填空題最基礎(chǔ)的模板DELIMITER $$ CREATE PROCEDURE sp_hello(IN name VARCHAR(20)) BEGIN SELECT CONCAT(Hello, , name, !) AS greeting; END$$ DELIMITER ;這里面每行都有考點(diǎn)。DELIMITER是MySQL特有的、最容易在填空題里被挖掉的關(guān)鍵字。它的作用是把MySQL客戶端默認(rèn)的分號結(jié)束符臨時改成別的符號否則客戶端會在CREATE PROCEDURE的END處就把整個語句截?cái)鄬?dǎo)致語法錯誤。CREATE PROCEDURE后面跟過程名和參數(shù)列表參數(shù)用括號包裹每個參數(shù)由“模式 參數(shù)名 數(shù)據(jù)類型”組成。BEGIN和END之間是過程體過程體里可以寫多條SQL語句、變量聲明、流程控制、游標(biāo)等。最后一定要把DELIMITER改回分號否則后續(xù)的普通語句都會以$$結(jié)尾容易踩坑。Oracle和openGauss的存儲過程結(jié)構(gòu)略有不同它們沒有DELIMITER這一說直接以AS或IS開始聲明部分過程體用BEGIN...END;包裹。比如CREATE OR REPLACE PROCEDURE sp_hello (p_name IN VARCHAR2) IS BEGIN DBMS_OUTPUT.PUT_LINE(Hello, || p_name || !); END sp_hello; /openGauss兼容Oracle語法的存儲過程同時也支持類似PL/pgSQL的寫法。填空題里如果考Oracle或openGauss多半會挖IN、IS、BEGIN、END這些位置。2.2 IN、OUT、INOUT三種參數(shù)模式怎么選才不丟分參數(shù)模式是存儲過程的核心考點(diǎn)也是新手最容易混淆的地方。MySQL的參數(shù)模式有三種IN、OUT、INOUTOracle和openGauss則寫成IN、OUT、IN OUT注意中間有空格。IN模式是默認(rèn)模式也是最常用的。它表示調(diào)用方傳入一個值存儲過程內(nèi)部可以讀取這個值但對它的修改不會傳回給調(diào)用方。想象成你把錢放進(jìn)保險(xiǎn)箱保險(xiǎn)箱處理完錢還是你的不會因?yàn)槟愦孢^一次它就變成別的面額。OUT模式表示存儲過程內(nèi)部會給這個參數(shù)賦值調(diào)用方在調(diào)用前不需要給它值調(diào)用完成后可以讀取到結(jié)果。這就是“出貨口”。比如你送進(jìn)去一個訂單號過程把統(tǒng)計(jì)金額寫到OUT參數(shù)里返回。INOUT模式則是雙向通道調(diào)用方先傳入一個值存儲過程內(nèi)部可以讀、可以改最終值傳回給調(diào)用方。最典型的應(yīng)用場景是做自增序號或累加器。選擇題和填空題最喜歡出這種題問“存儲過程內(nèi)部修改后哪個參數(shù)的值能傳回調(diào)用方”答案是OUT和INOUTIN不行。如果題目問“調(diào)用前必須賦初值的是哪個”答案是INOUT因?yàn)镺UT調(diào)用前不需要賦值IN就算賦了也會被忽略。2.3 變量的三個層級搞不清就寫不出正確的賦值邏輯存儲過程里變量有三個層級局部變量、用戶變量、系統(tǒng)變量。程序填空題里常挖的是前兩種。局部變量用DECLARE聲明必須放在BEGIN...END語句塊的開頭部分也就是所有可執(zhí)行語句之前。比如DECLARE total_amount DECIMAL(10,2) DEFAULT 0; DECLARE done INT DEFAULT 0;局部變量只能在聲明它的BEGIN...END塊內(nèi)使用塊結(jié)束變量就銷毀。給它賦值有兩種方式SET和SELECT...INTO。用戶變量用前綴比如dept_count。它不需要DECLARE直接用SET賦值即可創(chuàng)建生命周期是當(dāng)前會話。用戶變量的一個特點(diǎn)是可以在存儲過程內(nèi)部使用也可以在過程返回后繼續(xù)在客戶端查詢。這常用于在存儲過程之間傳遞臨時值或者在調(diào)試時打印中間結(jié)果。區(qū)分這三個層級的有效記憶方法是DECLARE是“面試前的本地草稿紙”是“會議室的公共白板”系統(tǒng)變量則不要碰一般不用存儲過程去修改。填空題里如果出現(xiàn)“聲明變量”的關(guān)鍵字答案通常就是DECLARE如果出現(xiàn)“給變量賦值”的語句優(yōu)先考慮SET。3. 核心實(shí)現(xiàn)機(jī)制的五個必考模塊IF、CASE、循環(huán)、游標(biāo)、異常3.1 條件判斷IF和CASE的語法差異填空題的“送分題”與“送命題”存儲過程里的IF和普通編程語言里的IF很接近但語法細(xì)節(jié)不同。MySQL的IF結(jié)構(gòu)是IF 條件 THEN 語句; ELSEIF 條件 THEN 語句; ELSE 語句; END IF;注意ELSEIF是一個單詞不是ELSE IF而且整個結(jié)構(gòu)必須以END IF加空格加冒號結(jié)尾也就是END IF;。這兩個位置是程序填空題的高頻挖空點(diǎn)。如果寫成ENDIF會直接語法報(bào)錯。CASE有兩個變體。第一個是簡單CASE拿一個表達(dá)式和多個值比較CASE level WHEN A THEN SET score 100; WHEN B THEN SET score 80; ELSE SET score 60; END CASE;第二個是搜索CASE每個WHEN后面跟完整條件布爾表達(dá)式CASE WHEN score 90 THEN SET grade 優(yōu); WHEN score 60 THEN SET grade 及格; ELSE SET grade 不及格; END CASE;別漏掉END CASE;結(jié)尾。在Oracle和openGauss中IF的寫法變成了ELSIF少一個EEND IF同樣不可缺少而CASE的結(jié)束是END CASE;與MySQL一致?？糘racle時ELSIF就是一個經(jīng)典的坑很多人想當(dāng)然寫上ELSEIF就丟了分。3.2 三種循環(huán)結(jié)構(gòu)WHILE、REPEAT、LOOP什么時候用哪個MySQL存儲過程支持三種循環(huán)WHILE、REPEAT、LEAVE配合LOOP。WHILE循環(huán)是“先判斷后執(zhí)行”循環(huán)體可能一次都不執(zhí)行。語法WHILE 條件 DO 語句; END WHILE;REPEAT循環(huán)是“先執(zhí)行后判斷”至少執(zhí)行一次。語法REPEAT 語句; UNTIL 條件 END REPEAT;LOOP則是一個無限循環(huán)必須配合LEAVE語句跳出LOOP 語句; IF 條件 THEN LEAVE loop_label; END IF; END LOOP;括號里這個loop_label就是循環(huán)標(biāo)簽標(biāo)簽必須在循環(huán)體開頭聲明格式是標(biāo)簽名加冒號比如loop_label: LOOP。程序填空題里考LOOP循環(huán)時會挖LEAVE和標(biāo)簽的位置。實(shí)際寫復(fù)雜邏輯時我比較推薦WHILE因?yàn)樗钪庇^不容易出現(xiàn)死循環(huán)。REPEAT適合“無論如何處理至少要先做一次”的場景比如遍歷生成序列。LOOP則適合需要在循環(huán)體中間多條件跳出、又沒有統(tǒng)一循環(huán)條件的場景。3.3 游標(biāo)的四步走聲明、打開、抓取、關(guān)閉一步都不能少游標(biāo)是程序填空題的壓軸題。游標(biāo)本質(zhì)上是一個指向查詢結(jié)果集的指針讓你可以一行一行地處理數(shù)據(jù)而不是一次性把整個結(jié)果集塞進(jìn)內(nèi)存。一個標(biāo)準(zhǔn)的游標(biāo)使用流程包含四個步驟正好是填空題的四個空位-- 第一步聲明游標(biāo)綁定查詢結(jié)果集 DECLARE cur_emp CURSOR FOR SELECT id, salary FROM employee; -- 第二步聲明異常處理器用于判斷數(shù)據(jù)是否遍歷完 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done TRUE; -- 第三步打開游標(biāo) OPEN cur_emp; -- 第四步循環(huán)抓取數(shù)據(jù)處理完關(guān)閉 FETCH cur_emp INTO v_id, v_salary; CLOSE cur_emp;關(guān)鍵點(diǎn)在于done變量的配合使用。在MySQL中當(dāng)FETCH沒有更多數(shù)據(jù)可抓取時會觸發(fā)NOT FOUND條件如果之前聲明了CONTINUE HANDLER FOR NOT FOUND就會把done變量置為TRUE。于是循環(huán)條件可以寫成WHILE NOT done DO循環(huán)體內(nèi)第一件事是FETCH第二件事是處理數(shù)據(jù)。這里有個非常經(jīng)典的踩坑點(diǎn)FETCH語句和CONTINUE HANDLER的聲明順序。在MySQL里DECLARE語句必須在BEGIN塊最前面游標(biāo)和異常處理器也都屬于DECLARE范疇所以必須放在一起聲明并且游標(biāo)必須聲明在變量的聲明之后、異常處理器的聲明之前否則會報(bào)語法錯誤。這個順序在程序填空題里如果挖空很容易寫亂。Oracle和openGauss的游標(biāo)寫法也遵循類似的四步流程不過異常處理的寫法變成BEGIN OPEN cur_emp; LOOP FETCH cur_emp INTO v_id, v_salary; EXIT WHEN cur_emp%NOTFOUND; END LOOP; CLOSE cur_emp; END;這里用的是游標(biāo)屬性%NOTFOUND來判斷是否取完不再依賴CONTINUE HANDLER。兩種風(fēng)格都要熟因?yàn)榭荚嚭兔嬖嚳赡芤黄鹂肌?.4 異常處理不是加分項(xiàng)而是生產(chǎn)環(huán)境的保命項(xiàng)MySQL的異常處理核心是DECLARE...HANDLER它有兩種級別CONTINUE和EXIT。CONTINUE表示遇到異常繼續(xù)執(zhí)行后續(xù)代碼EXIT表示遇到異常直接退出當(dāng)前BEGIN塊。常見寫法DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; SELECT 事務(wù)回滾 AS msg; END;SQLEXCEPTION是MySQL的異常類別它捕獲SQL執(zhí)行過程中的所有錯誤。也可以針對具體錯誤碼寫比如聲明一個專門針對“表不存在”錯誤碼1146的處理器。這種精細(xì)化控制在批量任務(wù)里很有用能做到“遇到某類錯誤就跳過遇到致命錯誤就停止”。Oracle和openGauss則是用EXCEPTION塊EXCEPTION WHEN NO_DATA_FOUND THEN v_msg : 數(shù)據(jù)不存在; WHEN OTHERS THEN v_msg : 未知錯誤; END;這里OTHERS對應(yīng)MySQL的SQLEXCEPTIONNO_DATA_FOUND對應(yīng)“NOT FOUND”條件。在生產(chǎn)級存儲過程里異常處理不是可有可無的裝飾而是必須在編碼階段就布局好的安全網(wǎng)——否則一條報(bào)錯就能讓整個批量任務(wù)的中斷狀態(tài)變得不可控。4. 完整實(shí)操從建表到SQLSugar調(diào)用一條龍寫一個訂單統(tǒng)計(jì)存儲過程4.1 需求設(shè)計(jì)這個存儲過程要解決什么問題光講語法不落地等于紙上談兵。這里我設(shè)計(jì)一個貫穿始終的實(shí)戰(zhàn)例子一個電商系統(tǒng)的訂單表需要按部門統(tǒng)計(jì)訂單總額并把結(jié)果保存到統(tǒng)計(jì)表同時返回總額給調(diào)用方。這個場景涵蓋了建表、變量、游標(biāo)、事務(wù)、OUT參數(shù)、異常處理可以一次性全用上。先建兩張表CREATE TABLE dept_order_count ( dept_id INT PRIMARY KEY, total_amount DECIMAL(12,2) DEFAULT 0.00, stat_date DATE ); CREATE TABLE order_detail ( order_id INT PRIMARY KEY AUTO_INCREMENT, dept_id INT NOT NULL, amount DECIMAL(10,2) NOT NULL, status TINYINT DEFAULT 1 COMMENT 1正常 0取消 );order_detail是訂單流水dept_order_count是統(tǒng)計(jì)結(jié)果表。存儲過程要做的事情是遍歷所有部門統(tǒng)計(jì)每個部門在order_detail里未取消訂單的總額如果統(tǒng)計(jì)表里已有該部門的記錄就更新沒有就插入最后把所有部門的總匯總金額寫到OUT參數(shù)。這個需求用一條普通的GROUP BY SQL也能做但一旦牽扯到“逐部門處理還要做UPSERT還要返回總金額給外部”用存儲過程就顯得更內(nèi)聚。4.2 完整代碼帶注釋的MySQL存儲過程范本先不急著上完整代碼要分析一個關(guān)鍵點(diǎn)為什么這里要用游標(biāo)而不是直接INSERT...SELECT因?yàn)樾枨笠蟆爸饌€部門計(jì)算并輸出中間過程”在實(shí)際的報(bào)表場景里可能每個部門還要調(diào)用獨(dú)立的算法或者寫日志。游標(biāo)能把“遍歷部門”這個流程顯式化方便擴(kuò)展。如果MySQL的窗口函數(shù)能完美處理就不必游標(biāo)“能用集合操作優(yōu)先用集合”始終是鐵律。下面是完整代碼也是可以直接背下來的模板DELIMITER $$ CREATE PROCEDURE sp_stat_dept_order( IN p_stat_date DATE, OUT p_total_amount DECIMAL(12,2) ) BEGIN -- 聲明局部變量 DECLARE v_dept_id INT; DECLARE v_amount DECIMAL(12,2); DECLARE v_total DECIMAL(12,2) DEFAULT 0.00; DECLARE done INT DEFAULT FALSE; -- 聲明游標(biāo)遍歷有效訂單的部門 DECLARE cur_dept CURSOR FOR SELECT dept_id, SUM(amount) FROM order_detail WHERE status 1 AND DATE(create_time) p_stat_date GROUP BY dept_id; -- 聲明異常處理器 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done TRUE; OPEN cur_dept; REPEAT FETCH cur_dept INTO v_dept_id, v_amount; IF NOT done THEN -- 如果統(tǒng)計(jì)表中已有記錄更新否則插入 IF EXISTS(SELECT 1 FROM dept_order_count WHERE dept_id v_dept_id) THEN UPDATE dept_order_count SET total_amount v_amount, stat_date p_stat_date WHERE dept_id v_dept_id; ELSE INSERT INTO dept_order_count(dept_id, total_amount, stat_date) VALUES(v_dept_id, v_amount, p_stat_date); END IF; SET v_total v_total v_amount; END IF; UNTIL done END REPEAT; CLOSE cur_dept; -- 結(jié)果寫入OUT參數(shù) SET p_total_amount v_total; END$$ DELIMITER ;有幾個細(xì)節(jié)值得留意。第一游標(biāo)查詢里明確只查status1的訂單把無效訂單在源頭過濾掉第二IF EXISTS判斷后面子查詢SELECT 1比SELECT *性能更好第三v_total累加邏輯放在IF NOT done之內(nèi)避免done為TRUE時把重復(fù)的FETCH結(jié)果累加進(jìn)去。程序填空題如果拿這個當(dāng)原型挖空點(diǎn)會集中在DELIMITER、DECLARE、CURSOR FOR、CONTINUE HANDLER、REPEAT、UNTIL、CLOSE這些位置。你可以在心里試著把這些詞遮住默寫一遍就知道自己哪里薄弱了。4.3 在Navicat和命令行里調(diào)用調(diào)用的姿勢也要規(guī)范存儲過程創(chuàng)建好之后調(diào)用本身很簡單。命令行里用CALL命令CALL sp_stat_dept_order(2025-01-31, total); SELECT total;第一句執(zhí)行存儲過程傳入統(tǒng)計(jì)日期第二個參數(shù)傳一個用戶變量用來接收OUT返回值。調(diào)用完成后SELECT total;能看到總金額。Navicat里可以直接選中存儲過程點(diǎn)擊運(yùn)行按鈕在彈出的參數(shù)輸入界面填參數(shù)值。如果你傳的是一個普通字符串注意加上單引號否則會被當(dāng)成列名解析報(bào)錯。這是很多新手在可視化工具里的第一個坑。4.4 用SQLSugar在.NET里調(diào)用存儲過程ORM不是只能查表如果項(xiàng)目是.NET技術(shù)棧SQLSugar是一個非常好用的ORM框架它對存儲過程的支持也很完整。熱詞里出現(xiàn)“sqlsugar存儲過程”說明現(xiàn)在Express應(yīng)用里調(diào)存儲過程的需求并不少。用SQLSugar調(diào)存儲過程核心是SqlSugarClient的Ado屬性。最常用的方式是var result db.Ado.UseStoredProcedure() .GetDataTable(sp_stat_dept_order, new SugarParameter(p_stat_date, 2025-01-31), new SugarParameter(p_total_amount, null, System.Data.DbType.Decimal, ParameterDirection.Output)); decimal total result.Rows[0][0] DBNull.Value ? 0 : Convert.ToDecimal(result.Rows[0][0]);如果你更關(guān)心OUT參數(shù)用UseStoredProcedure配合GetDataTable時可以把輸出參數(shù)一并傳遞返回的表數(shù)據(jù)是過程里最后SELECT出來的結(jié)果集。關(guān)于OUT參數(shù)注意SugarParameter的構(gòu)造重載第四個參數(shù)是ParameterDirection.Output這樣調(diào)用完成后同一個SugarParameter對象的Value屬性里就會有輸出值。如果你要的是OUT值而非結(jié)果集更清晰的做法是var outParam new SugarParameter(p_total_amount, null, System.Data.DbType.Decimal, ParameterDirection.Output); db.Ado.UseStoredProcedure().ExecuteCommand(sp_stat_dept_order, new SugarParameter(p_stat_date, 2025-01-31), outParam); Console.WriteLine(outParam.Value);建議在項(xiàng)目里統(tǒng)一封裝一個StoredProcedureHelper把參數(shù)構(gòu)造、異常處理、日志輸出都放到公共方法里避免每個業(yè)務(wù)點(diǎn)都重復(fù)寫參數(shù)代碼。調(diào)用完成后務(wù)必判斷outParam.Value是否為DBNull因?yàn)榇鎯^程里沒給OUT賦值時客戶端收到的值會是DBNull直接轉(zhuǎn)decimal會拋異常。5. 常見報(bào)錯與踩坑實(shí)錄——每一個都是真金白銀換來的教訓(xùn)5.1 DELIMITER不生效的幾種情況第一個坑是生命周期問題。DELIMITER只在當(dāng)前會話里生效如果你斷開連接重連必須再設(shè)置一次。用Navicat或DataGrip時如果創(chuàng)建存儲過程總是報(bào)錯先檢查編輯器底部的會話是否支持多語句執(zhí)行有些圖形工具默認(rèn)就把DELIMITER當(dāng)成普通語句發(fā)給服務(wù)端需要關(guān)閉“將查詢用分號分隔”這類選項(xiàng)。第二個坑是最容易忽略的DELIMITER后面必須跟空格。寫成DELIMITER$$和DELIMITER $$在多數(shù)情況下都能用但老版本MySQL里如果沒空格分隔符解析可能出錯。更穩(wěn)妥的寫法是DELIMITER $$后面留一個空格。第三個坑是無意中把DELIMITER寫進(jìn)了存儲過程內(nèi)部。分隔符只是客戶端指令不是數(shù)據(jù)庫服務(wù)端語法所以千萬別把DELIMITER寫在BEGIN...END塊內(nèi)部那會直接報(bào)語法錯誤。5.2 參數(shù)名和列名沖突這是一個隱形的邏輯炸彈很多初學(xué)者會給參數(shù)起名叫dept_id而表里正好也有dept_id列。這時候在WHERE子句里寫WHERE dept_id dept_id數(shù)據(jù)庫會怎么理解MySQL在這種情況下會把兩個dept_id都當(dāng)作列名來解析結(jié)果就是條件恒為真查詢結(jié)果是全表數(shù)據(jù)。更隱蔽的情況是在SET語句里寫SET dept_id dept_id本意是想把參數(shù)值賦給變量結(jié)果兩邊都指向列名根本沒有賦值效果。避免辦法只有兩條一是參數(shù)命名統(tǒng)一加前綴比如p_dept_id、v_dept_id做區(qū)分二是在存儲過程開頭就養(yǎng)成習(xí)慣任何參數(shù)和變量都不與列名同名。Oracle和openGauss開發(fā)者也有個習(xí)慣就是在參數(shù)前加p_前綴變量加v_前綴這不是什么編碼潔癖而是實(shí)打?qū)嵰?guī)避二義性的做法。MySQL同樣適用。5.3 游標(biāo)和循環(huán)的性能陷阱游標(biāo)是逐行處理性能天然不如集合操作。在數(shù)據(jù)量大的表上跑游標(biāo)如果每行都執(zhí)行一個INSERT或UPDATE大量行鎖、日志和網(wǎng)絡(luò)往返很快會把數(shù)據(jù)庫拖垮。建議的做法是能用JOIN、窗口函數(shù)、CASE WHEN聚合解決的就堅(jiān)決不用游標(biāo)必須用游標(biāo)時在游標(biāo)查詢里就把條件收緊盡量只撈必要字段。另外在循環(huán)里做UPDATE前先仔細(xì)檢查WHERE條件是否命中索引否則每行一個全表掃描直接變成生產(chǎn)事故。OPEN到CLOSE之間的游標(biāo)會占用臨時表空間用完務(wù)必CLOSE釋放。程序填空題里的CLOSE就是這個作用它不是形式而是資源管理的一部分。5.4 權(quán)限問題創(chuàng)建和執(zhí)行不是一回事存儲過程創(chuàng)建后調(diào)用方不一定有權(quán)限執(zhí)行。MySQL里需要單獨(dú)授權(quán)GRANT EXECUTE ON PROCEDURE demo_db.sp_stat_dept_order TO app_userlocalhost;很多開發(fā)者在本地用root寫好了存儲過程部署到測試環(huán)境直接報(bào)“PROCEDURE sp_stat_dept_order does not exist”排查半天發(fā)現(xiàn)其實(shí)是賬號沒有EXECUTE權(quán)限導(dǎo)致的不是過程不存在。權(quán)限設(shè)計(jì)上還有個專業(yè)建議存儲過程內(nèi)部讀哪些表授權(quán)時不需要把這些表的SELECT權(quán)限也授給調(diào)用方。因?yàn)榇鎯^程默認(rèn)以創(chuàng)建者權(quán)限執(zhí)行或明確使用SQL SECURITY INVOKER來按調(diào)用者權(quán)限。這是存儲過程在安全層面的優(yōu)勢也是設(shè)計(jì)存儲過程時一定要考慮清晰的邊界。說到我的經(jīng)驗(yàn)最值得記住的一點(diǎn)是存儲過程的代碼風(fēng)格必須比普通SQL更保守每一步都要考慮異常退出后的狀態(tài)該回滾就回滾該關(guān)游標(biāo)就關(guān)游標(biāo)。程序填空題只考你會不會寫而生產(chǎn)環(huán)境會考你能不能善后。