Oracle 19c存储过程JSON输出格式调整技术求助

修改后的存储过程代码 #

PROCEDURE getCities
(
    v_province_fips IN geo_counties.FIPS_CODE%TYPE,
    v_city_like IN geo_cities.NAME%TYPE, 
    p_out OUT CLOB
)
AS
    v_country_code geo_cities.country_code%TYPE;
BEGIN
    v_country_code := SUBSTR(v_province_fips, 1, 2); -- 简化字符串拼接逻辑
    
    SELECT JSON_OBJECT(
        'CITY_SEARCH' VALUE JSON_ARRAYAGG(
            JSON_OBJECT(
                'GEONAME_ID_CITY' VALUE c.GEONAME_ID,
                'NAME' VALUE c.NAME,
                'ASCII_NAME' VALUE c.ASCII_NAME,
                'LATITUDE' VALUE c.LATITUDE,
                'LONGITUDE' VALUE c.LONGITUDE,
                'STATE_PROV_NAME' VALUE a.NAME,
                'GEONAME_ID_COUNTY' VALUE b.GEONAME_ID,
                'COUNTY_NAME' VALUE b.NAME,
                'COUNTY_ASCII_NAME' VALUE b.ASCII_NAME,
                'STATE_PROV' VALUE p.NAME,
                'COUNTRY_CODE' VALUE c.COUNTRY_CODE,
                'COUNTY_LATITUDE' VALUE b.LATITUDE,
                'COUNTY_LONGITUDE' VALUE b.LONGITUDE,
                'FIPS_CODE' VALUE b.FIPS_CODE 
            ) ORDER BY c.NAME, b.NAME, c.ADMIN_1 -- 将排序逻辑嵌入聚合函数
        ) RETURNING CLOB
    ) INTO p_out
    FROM geo_cities c
    JOIN geo_counties b
        ON b.COUNTRY_CODE = c.COUNTRY_CODE
        AND c.ADMIN_1 = SUBSTR(b.FIPS_CODE, 4, 2)
        AND c.ADMIN_2 = SUBSTR(b.FIPS_CODE, 7)
    JOIN GEO_ADMIN1_CODES_ASCII a
        ON a.FIPS_CODE = v_country_code || '.' || SUBSTR(b.FIPS_CODE, 4, 2)
    JOIN GEO_PROVINCES p
        ON p.FIPS_CODE = v_country_code || '.' || SUBSTR(b.FIPS_CODE, 4, 2)
    WHERE LOWER(c.NAME) LIKE LOWER('%' || v_city_like || '%')
      AND c.COUNTRY_CODE = v_country_code
      AND c.feature_code IN ('ADM1','ADM2','PPL','PPLA', 'PPLA2');
END getCities;

关键修改说明 #

1. 移除冗余GROUP BY
   原代码的GROUP BY会将每一行数据单独分组，导致JSON_ARRAYAGG生成的数组中每个元素都是带CITY_SEARCH键的独立对象。去掉GROUP BY后，聚合函数会直接将所有城市对象合并成一个数组，再由外层JSON_OBJECT包装成你需要的结构。

2. 调整JSON函数嵌套顺序

   • 内层：用JSON_OBJECT将单条城市记录转为JSON对象
   • 中层：用JSON_ARRAYAGG将所有城市对象聚合成数组
   • 外层：用JSON_OBJECT将数组绑定到CITY_SEARCH键，生成外层对象包裹数组的目标格式

3. 解决ORA-01422错误
   未使用聚合函数时，SELECT返回多行结果，直接INTO单个CLOB变量会触发“返回行数超出请求数量”的错误。JSON_ARRAYAGG将多行结果压缩为单个CLOB类型的JSON数组，完美适配INTO的单行要求。

4. 简化代码细节

   • 删除不必要的字符串拼接'' || 变量 || ''，直接调用SUBSTR即可
   • 将排序逻辑移至JSON_ARRAYAGG内部，确保数组元素按指定规则排序

内容的提问来源于stack exchange，提问作者David