涉及海量数据的查询SQL建议使用“数据库函数”封装并调用
一、问题描述
生产环境中，核心业务表数据量随业务增长迅速膨胀，原统计查询SQL因执行计划劣化、索引失效而突然变慢。若按传统流程修复，需要优化SQL、测试、重新打包、发版，并且SQL优化往往需要多轮迭代、持续打磨；若每次调整都要重走发版流程，时间和风险成本将被无限放大。最终无法快速解决问题，易引发用户投诉。
二、“数据库函数封装”方案（代码仅需改一行）
面对上述问题场景，我们可以将整条海量数据查询SQL封装为数据库函数。应用层仅需调用统一的函数接口，查询逻辑下沉至数据库内部；后续无论加Hint、改索引、调整执行计划，均可通过CREATE OR REPLACE FUNCTION秒级生效，无需再次测试、打包、发版，实现真正的零停机热更与持续迭代优化。
改造前，应用层SQL常规写法
<select id="querySysUserList" parameterType="cn.gdxiash.calculationdemo.entity.SysUser"resultType="cn.gdxiash.calculationdemo.entity.SysUser">SELECT * FROM SYS_USER <where><if test="name!=null and name!=''">NAME LIKE '%' || #{name} || '%'</if><if test="age!=null and age>=0">AGE = #{age}</if></where> </select>
改造后，应用层SQL写法
<select id="querySysUserList" parameterType="cn.gdxiash.calculationdemo.entity.SysUser"resultType="cn.gdxiash.calculationdemo.entity.SysUser">SELECT * FROM TABLE(F_GET_SYS_USER_LIST(#{name},#{age})) </select>
三、简单样例实现
1、创建SYS_USER表
CREATE TABLE "SHANGHAI"."SYS_USER"
(
"ID" VARCHAR2(36) NOT NULL,
"NAME" VARCHAR2(50),
"AGE" NUMBER(3,0),
CLUSTER PRIMARY KEY("ID")) STORAGE(ON "MAIN", CLUSTERBTR) ;2、创建自定义类型
-- 创建自定义类型
CREATE OR REPLACE TYPE T_SYS_USER AS OBJECT(
ID VARCHAR2(36),
NAME VARCHAR2(50),
AGE NUMBER(3,0));
-- 创建自定义类型集合
CREATE OR REPLACE TYPE T_SYS_USER_LIST AS TABLE OF T_SYS_USER;3、将业务SQL封装成函数（可随时通过REPLACE进行修改）
-- 创建查询函数
CREATE OR REPLACE FUNCTION F_GET_SYS_USER_LIST(P_NAME IN VARCHAR2 DEFAULT NULL, -- 允许不传P_AGE IN NUMBER DEFAULT NULL -- 允许不传
) RETURN T_SYS_USER_LIST
ISV_LIST T_SYS_USER_LIST := T_SYS_USER_LIST();
BEGINSELECT T_SYS_USER(ID, NAME, AGE)BULK COLLECT INTO V_LISTFROM SYS_USERWHERE (P_NAME IS NULL OR NAME LIKE '%' || P_NAME || '%')AND (P_AGE IS NULL OR AGE = P_AGE);RETURN V_LIST;
END;4、将代码的查询语句进行改造
原SQL
<select id="querySysUserList" parameterType="cn.gdxiash.calculationdemo.entity.SysUser"resultType="cn.gdxiash.calculationdemo.entity.SysUser">SELECT * FROM SYS_USER <where><if test="name!=null and name!=''">NAME LIKE '%' || #{name} || '%'</if><if test="age!=null and age>=0">AGE = #{age}</if></where>
</select>将原SQL修改为查询数据库函数
<select id="querySysUserList" parameterType="cn.gdxiash.calculationdemo.entity.SysUser"resultType="cn.gdxiash.calculationdemo.entity.SysUser">SELECT * FROM TABLE(F_GET_SYS_USER_LIST(#{name},#{age}))
</select>