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