关注墨瑾轩带你探索编程的奥秘超萌技术攻略轻松晋级编程高手技术宝库已备好就等你来挖掘订阅墨瑾轩智趣学习不孤单即刻启航编程之旅更有趣正片存储过程迁移的“五大夺命坑”第一坑骨架重塑——变量声明与赋值的“规矩”在MySQL里写存储过程就像写散文形散神不散。你可以在BEGIN...END里面走到哪声明到哪。但在金仓PL/pgSQL里这是死罪。MySQL的“散漫”写法-- MySQL变量声明可以在BEGIN之后随心所欲CREATEPROCEDUREsp_mysql_demo(INp_user_idINT)BEGIN-- 走着走着突然想起个变量直接DECLAREDECLAREv_user_nameVARCHAR(50);DECLAREv_ageINT;-- 赋值用SET或者SELECT INTOSETv_user_name老墨;SELECTageINTOv_ageFROMusersWHEREidp_user_id;-- 甚至还能在中间再声明一个DECLAREv_statusINTDEFAULT1;END;金仓KingbaseES的“森严”写法金仓要求所有的变量声明必须、一定、绝对要放在DECLARE块中然后才是BEGIN...END执行块。这就像Java里的类结构字段在上面方法在下面。-- 金仓标准的PL/pgSQL骨架规矩就是规矩CREATEORREPLACEFUNCTIONsp_kingbase_demo(p_user_idINT)-- 【墨注】金仓推荐用FUNCTION代替PROCEDUREV8早期V8R6支持PROCEDURE。-- 如果用FUNCTION且不需要返回值返回类型写VOID。RETURNSVOIDAS$$DECLARE-- 【干嘛的】这里集中声明所有局部变量不能放到BEGIN后面。-- 【为啥这么写】PL/pgSQL编译器在预编译时需要先解析整个DECLARE块分配内存和确定类型。-- 【不这么写怎么死】如果你把DECLARE写在BEGIN里面直接报语法错误syntax error at or near DECLARE。v_user_nameVARCHAR(50);v_ageINT;-- 【更骚的写法】可以用 %TYPE 绑定表字段类型。-- 比如v_user_name users.user_name%TYPE;-- 这样以后表字段从VARCHAR(50)改成VARCHAR(100)这里自动适应不用改代码。老手都这么干。v_statusINTDEFAULT1;BEGIN-- 【干嘛的】给变量赋值。-- 【为啥这么写】在PL/pgSQL中给局部变量赋值推荐用 :而不是 。-- 在金仓里是逻辑判断等于: 才是赋值。虽然金仓兼容模式下有时SET也能用但 : 是最纯正、最不容易出歧义的写法。v_user_name :老墨;-- 【干嘛的】把查询结果塞进变量。-- 【不这么写怎么死】如果你写 SELECT age INTO v_age FROM users WHERE id p_user_id;-- 如果查出来多条记录金仓会直接抛异常query returned more than one row。-- MySQL遇到多条记录时默认只取第一条剩下的默默吞掉。这就是典型的“隐性逻辑差异”SELECTageINTOSTRICT v_ageFROMusersWHEREidp_user_id;-- 【墨注】加上 STRICT 关键字是PG/金仓的最佳实践。-- 如果查不到数据抛 NO_DATA_FOUND查到多条抛 TOO_MANY_ROWS。-- 把隐患暴露在明处总比业务逻辑悄悄跑错要好一万倍。-- 业务逻辑...END;$$LANGUAGEplpgsql;老墨吐槽当年我带的一个小弟把MySQL的脚本直接扔到金仓里跑满屏的syntax error。他跑来问我“墨哥金仓是不是有bug”我一看好家伙BEGIN后面跟了十几个DECLARE。我告诉他“不是金仓有bug是MySQL把你惯坏了。在金仓先领粮票声明再吃饭执行懂”第二坑异常处理——从“回调地狱”到“Try-Catch”这是迁移过程中最让人崩溃、改写工作量最大的地方。MySQL的异常处理用的是DECLARE HANDLER。这玩意儿是个“回调函数”你可以在代码开头声明“如果遇到某某错误就执行某某操作”然后继续往下跑。MySQL的“回调式”异常处理-- MySQL声明一个CONTINUE HANDLER遇到NOT FOUND就设置变量CREATEPROCEDUREsp_mysql_handler(INp_idINT)BEGINDECLAREv_nameVARCHAR(50);DECLAREv_not_foundINTDEFAULT0;-- 【痛点】这里的HANDLER是作用域级别的不是块级别的。-- 只要在这个BEGIN...END里发生NOT FOUND比如游标越界或者SELECT INTO没数据都会触发它。DECLARECONTINUEHANDLERFORNOTFOUNDSETv_not_found1;SELECTnameINTOv_nameFROMusersWHEREidp_id;IFv_not_found1THENSETv_name未知用户;ENDIF;END;金仓的“块级 Try-Catch”金仓PL/pgSQL彻底抛弃了这种“隐式回调”采用了类似Java的EXCEPTION WHEN ... THEN块级捕获机制。你必须把可能出错的代码包裹在一个内部的BEGIN...EXCEPTION...END块里。-- 金仓块级异常捕获指哪打哪CREATEORREPLACEFUNCTIONsp_kingbase_handler(p_idINT)RETURNSVARCHARAS$$DECLAREv_nameVARCHAR(50);BEGIN-- 【干嘛的】开启一个内部子块Sub-block专门用来做异常隔离。-- 【为啥这么写】金仓的EXCEPTION只能放在整个FUNCTION的最外层或者一个内部子块的末尾。-- 你不能像MySQL那样在开头声明一个HANDLER然后管全局。BEGIN-- 尝试查询数据SELECTnameINTOSTRICT v_nameFROMusersWHEREidp_id;-- 【干嘛的】捕获特定的异常。-- 【不这么写怎么死】如果你不写EXCEPTION块一旦 p_id 不存在SELECT INTO STRICT 会直接抛出 NO_DATA_FOUND-- 整个函数直接崩溃终止外层调用方也会跟着报错除非外层也捕获了。-- 这就是MySQL的“宽容”和金仓的“严谨”的最大区别。EXCEPTIONWHENNO_DATA_FOUNDTHEN-- 没查到数据时的兜底逻辑v_name :未知用户;WHENTOO_MANY_ROWSTHEN-- 【墨注】防守型编程万一查出多条数据虽然id是主键不可能但如果是其他条件呢-- 记录日志或者给个默认值。v_name :数据异常(多条);-- 这里可以调用金仓的日志函数比如 RAISE WARNING 查到多条数据: %, p_id;WHENOTHERSTHEN-- 【更骚的写法】捕获所有其他未预期的异常类似Java的 catch(Exception e)。-- 在金仓里可以通过 SQLSTATE 和 SQLERRM 获取错误码和错误信息。-- 强烈建议在这里加上 RAISE LOG把错误落盘否则出了bug你连怎么死的都不知道。RAISE LOGsp_kingbase_handler 发生未知错误, p_id%, 错误码%, 错误信息%,p_id,SQLSTATE,SQLERRM;v_name :系统异常;END;-- 【墨注】注意这个END是内部子块的结束不是整个FUNCTION的结束。RETURNv_name;END;$$LANGUAGEplpgsql;老墨的灵魂拷问各位老鸟你们觉得MySQL那种“全局HANDLER”好还是金仓这种“块级Try-Catch”好说实话刚开始从MySQL转金仓时我骂骂咧咧觉得写内部BEGIN...END块太繁琐了缩进层层叠叠像俄罗斯套娃。但后来我悟了MySQL的HANDLER是个坑当你过程里有3个SELECT INTO2个UPDATE1个游标时一个全局的NOT FOUNDHANDLER 根本分不清到底是哪条语句没找到数据而金仓的子块隔离把异常精确控制到了语句级别这才是真正工业级的设计。第三坑动态SQL——拼接字符串的“夺命连环坑”业务系统里动态拼表名、动态拼WHERE条件太常见了。MySQL的PREPAREEXECUTE大家用得飞起但到了金仓这套得全扔。MySQL的动态SQL-- MySQLPREPARE 预编译然后 EXECUTECREATEPROCEDUREsp_mysql_dynamic(INp_table_nameVARCHAR(50),INp_statusINT)BEGIN-- 【痛点】MySQL拼字符串极其随意容易引发SQL注入而且PREPARE的变量作用域很迷。SETsql_strCONCAT(SELECT count(*) INTO cnt FROM ,p_table_name, WHERE status ,p_status);PREPAREstmtFROMsql_str;EXECUTEstmt;DEALLOCATEPREPAREstmt;SELECTcnt;END;金仓的动态SQLEXECUTE format(...) USING ...金仓PG系提供了极其强大的format()函数类似C语言的printf和USING参数绑定机制。这是防SQL注入的终极武器。-- 金仓安全、优雅、防注入的动态SQLCREATEORREPLACEFUNCTIONsp_kingbase_dynamic(p_table_nameVARCHAR,p_statusINT)RETURNSINTAS$$DECLAREv_sqlTEXT;v_cntINT;BEGIN-- 【干嘛的】使用 format() 函数拼接SQL并使用 EXECUTE ... USING 绑定参数。-- 【为啥这么写】-- 1. %I (大写i)代表 Identifier标识符会自动给表名/字段名加双引号防止关键字冲突和大小写问题。-- 2. %L (大写L)代表 Literal字面量会自动加单引号并转义防注入。-- 3. $1代表 USING 后面的第一个参数。-- 【不这么写怎么死】如果你用 || 硬拼字符串比如 SELECT count(*) FROM || p_table_name || WHERE status || p_status-- 第一p_table_name 如果传了 user; DROP TABLE users;你的库就没了SQL注入。-- 第二金仓对类型要求严格硬拼容易报类型不匹配的错。v_sql :format(SELECT count(*) FROM %I WHERE status $1,p_table_name);-- 【干嘛的】执行动态SQL并将结果存入变量 v_cnt。-- 【墨注】USING 后面的参数类型必须和SQL里的占位符严格匹配。-- 这里 $1 对应 p_statusINT完美。EXECUTEv_sqlINTOv_cntUSINGp_status;RETURNv_cnt;END;$$LANGUAGEplpgsql;老墨的血泪教训当年有个外包兄弟不信邪非要在金仓里用||拼动态SQL而且拼的是带时间戳的条件。结果呢MySQL里WHERE create_time 2023-01-01跑得欢到了金仓因为没加类型转换直接报operator does not exist: timestamp without time zone text。查了一下午最后我让他把拼接改成USING传参5分钟搞定。记住在金仓里写动态SQL不用format和USING的都是耍流氓。第四坑游标遍历——别再傻傻FETCH了游标Cursor是处理逐行逻辑的利器。MySQL的游标必须配合HANDLER来检测结束写起来又臭又长。金仓虽然也支持传统的OPEN/FETCH/CLOSE但我强烈建议你用金仓PG特有的“隐式游标 FOR 循环”。MySQL的游标又臭又长-- MySQL传统的游标四步曲声明、打开、循环FETCH、关闭CREATEPROCEDUREsp_mysql_cursor()BEGINDECLAREdoneINTDEFAULTFALSE;DECLAREv_idINT;DECLAREv_nameVARCHAR(50);-- 1. 声明游标DECLAREcurCURSORFORSELECTid,nameFROMusersWHEREstatus1;-- 2. 声明结束标志DECLARECONTINUEHANDLERFORNOTFOUNDSETdoneTRUE;-- 3. 打开游标OPENcur;read_loop:LOOPFETCHcurINTOv_id,v_name;IFdoneTHENLEAVEread_loop;ENDIF;-- 处理逻辑UPDATEusersSETageage1WHEREidv_id;ENDLOOP;-- 4. 关闭游标CLOSEcur;END;金仓的游标降维打击忘掉那些繁琐的OPEN/CLOSE和HANDLER吧。金仓的FOR ... IN ... LOOP语法直接让代码量减少一半而且自动管理游标的打开和关闭绝对不会出现游标泄漏。-- 金仓隐式游标丝滑得像德芙巧克力CREATEORREPLACEFUNCTIONsp_kingbase_cursor()RETURNSVOIDAS$$DECLARE-- 【干嘛的】定义一个 RECORD 类型的变量用来接收游标每次吐出来的一行数据。-- 【为啥这么写】RECORD 是PL/pgSQL的神器它不需要你提前定义好字段结构-- 查询返回什么字段它就能装什么字段。比MySQL里必须先DECLARE一堆变量来接FETCH强太多了。rec_user RECORD;BEGIN-- 【干嘛的】直接遍历查询结果集。-- 【墨注】这里底层其实就是游标但PG引擎帮你封装了OPEN、FETCH、判断结束、CLOSE的所有脏活累活。-- 【不这么写怎么死】如果你非要用金仓的传统游标DECLARE cur CURSOR FOR...-- 你不仅要手动OPEN/CLOSE而且如果循环中途发生了异常EXCEPTION-- 游标可能没被正常CLOSE导致会话级别的游标泄漏连接池直接爆掉。-- 用 FOR...IN 循环即使中途抛出异常PG引擎也会自动帮你清理游标资源。FORrec_userINSELECTid,name,ageFROMusersWHEREstatus1LOOP-- 【干嘛的】通过 rec_user.字段名 的方式访问数据。-- 【更骚的写法】如果你想在循环里更新当前行可以在SELECT里加上 FOR UPDATE-- 然后在UPDATE语句里用 WHERE CURRENT OF cursor_name但这需要显式游标。-- 对于普通的逐行处理直接下面这样写最稳。UPDATEusersSETagerec_user.age1WHEREidrec_user.id;-- 打印日志方便调试RAISE NOTICE处理用户: %, 新年龄: %,rec_user.name,rec_user.age1;ENDLOOP;END;$$LANGUAGEplpgsql;老墨点评从MySQL的游标转到金仓的FOR rec IN那种感觉就像是从手动挡破捷达换成了自动挡特斯拉。你一旦用过就再也回不去了。第五坑事务控制与内置函数——藏在地毯下的炸弹这是迁移收尾阶段最容易让人“阴沟里翻船”的地方。1. 事务控制COMMIT / ROLLBACK的生死线在MySQL的存储过程里你可以随时COMMIT或ROLLBACK只要没被外层事务强制管控。但在金仓PG系中FUNCTION函数绝对不允许在内部执行COMMIT或ROLLBACK。函数必须作为一个原子操作由调用方控制事务。PROCEDURE存储过程金仓V8R6 / PG 11 支持可以在内部执行COMMIT/ROLLBACK但必须是在最顶层调用不能嵌套在其他控制块如IF、LOOP或异常捕获块中。-- 金仓 V8R6在 PROCEDURE 中控制事务的正确姿势CREATEORREPLACEPROCEDUREsp_kingbase_tx_control(p_batch_sizeINT)LANGUAGEplpgsqlAS$$DECLAREv_countINT:0;rec RECORD;BEGINFORrecINSELECTidFROMbig_tableWHEREstatus0LOOPUPDATEbig_tableSETstatus1WHEREidrec.id;v_count :v_count1;-- 【干嘛的】批量处理时每处理N条提交一次事务防止大事务撑爆Undo/Redo日志。-- 【为啥这么写】注意COMMIT 必须直接写在 LOOP 内部不能被 IF 包裹-- 【不这么写怎么死】如果你写成 IF v_count % p_batch_size 0 THEN COMMIT; END IF;-- 金仓会直接报错invalid transaction termination。-- 这是PG内核的硬性限制防止复杂控制流导致事务状态机混乱。IFv_count%p_batch_size0THEN-- 只能把COMMIT提出来或者用其他逻辑绕过。-- 这里为了演示假设 p_batch_size 就是循环的步长。COMMIT;-- 【墨注】COMMIT后会自动开启一个新事务。游标如果是 WITH HOLD 的可以继续否则游标会失效。ENDIF;ENDLOOP;END;$$;2. 内置函数的“偷梁换柱”MySQL里用得贼溜的函数在金仓里很多都不存在或者行为不一致。这里列几个高频“阵亡”名单MySQL 函数金仓 (KingbaseES) 替换方案墨式吐槽IFNULL(a, b)COALESCE(a, b)COALESCE是SQL标准金仓认这个。其实MySQL也支持养成好习惯。IF(cond, a, b)CASE WHEN cond THEN a ELSE b END别找IF()函数了老老实实写CASE WHEN虽然长点但所有数据库通用。GROUP_CONCAT(col)STRING_AGG(col, ,)注意STRING_AGG的第二个参数是分隔符必须传而且结果类型是TEXT。DATE_FORMAT(d, %Y)TO_CHAR(d, YYYY)格式化字符串的占位符全变了PG的TO_CHAR格式串去查官方文档吧别靠猜。NOW()/SYSDATE()NOW()/CURRENT_TIMESTAMP这个倒是兼容谢天谢地。LIMIT offset, countLIMIT count OFFSET offset金仓PG不支持LIMIT 10, 20这种写法必须把 OFFSET 单独拎出来。代码实战一个包含函数替换的综合例子-- 金仓替换MySQL特有函数后的聚合查询CREATEORREPLACEFUNCTIONsp_kingbase_funcs()RETURNSTEXTAS$$DECLAREv_resultTEXT;BEGIN-- 【干嘛的】将部门下的员工名字拼接起来空值替换为佚名。-- 【为啥这么写】-- 1. IFNULL 换成 COALESCE。-- 2. GROUP_CONCAT 换成 STRING_AGG。-- 【不这么写怎么死】如果你在兼容模式下强行用 GROUP_CONCAT-- 可能会遇到排序不可控的问题MySQL支持 GROUP_CONCAT(col ORDER BY id)。-- 在金仓里STRING_AGG 的正确带排序写法是STRING_AGG(col, , ORDER BY id)。SELECTSTRING_AGG(COALESCE(name,佚名),,ORDERBYid)INTOv_resultFROMemployeesWHEREdept_id10;-- 【墨注】日期格式化把 MySQL 的 DATE_FORMAT(create_time, %Y-%m-%d)-- 换成金仓的 TO_CHAR(create_time, YYYY-MM-DD)。-- 记住金仓里大小写敏感yyyy和YYYY在某些老版本里表现可能不同统一用大写最稳。RETURNv_result;END;$$LANGUAGEplpgsql;尾声信创迁移不是搬运是重塑烟抽完了咖啡也见底了。咱们来做个总结。从MySQL迁移到人大金仓表面上看是换个数据库实际上是一次被迫的、深度的代码重构。很多团队在迁移时抱着“能跑就行”、“尽量少改代码”的心态结果呢为了兼容MySQL的IF自己写了个巨慢的自定义函数。为了绕过事务限制把逻辑全搬到Java代码里导致网络IO飙升。忽略了金仓对大小写敏感的特性表名如果不加双引号金仓默认全转小写而MySQL默认不区分导致查询时索引失效全表扫描系统直接卡死。老墨的三条迁移心法别迷信自动迁移工具。工具能帮你搞定80%的表结构和简单SQL但剩下20%的存储过程和复杂视图必须人工逐行Review。拥抱PG系的严谨。别总想着把金仓“降级”成MySQL来用比如强行开各种兼容参数。既然换了国产高端数据库就按它的最佳实践强类型、块级异常、USING防注入来写代码。借机清理技术债。那些在MySQL里写了上千行的“面条式”存储过程趁着迁移该拆到Java代码里的拆到代码里该重构成小函数的重构成小函数。数据库是用来存数据和做简单聚合的不是用来跑复杂业务逻辑的。信创这条路走起来满是泥泞但趟过去你的技术功底绝对能脱胎换骨。