解决wm_concat字符串截断问题(超出最大长度限制)， clob类型转String

╰+哭是因爲堅強的太久メ 2024-03-27 16:33 38阅读 0赞

### 执行解决替代方案–适用于达梦数据库和oracle数据库 ###

#### 步骤 ####

1） 数据库新建查询，执行上述的函数来创建函数（创建类型，创建类型内容，创建函数）  
2）仿照调用函数的sql语句或者最后执行结果图片中的调用语句，写出符 合自己条件的查询语句,返回的为clob类型的数据，犹豫前端无法解析，需要转类型  
3）java后台接受转换clob类型方法，文章后写了两个方法，推荐使用第二个转String的方法  
4）最后附上查询结果图

##### 希望对大家有所帮助，被此问题困扰了很久！！！ #####

---创建类型
    CREATE OR REPLACE TYPE zh_concat_im
    AUTHID CURRENT_USER AS OBJECT
    (
    CURR_STR clob,
    STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT zh_concat_im) RETURN NUMBER,
    MEMBER FUNCTION ODCIAGGREGATEITERATE( SELF IN OUT zh_concat_im,
    P1 IN VARCHAR2) RETURN NUMBER,
    MEMBER FUNCTION ODCIAGGREGATETERMINATE( SELF IN zh_concat_im,
    RETURNVALUE OUT clob,
    FLAGS IN NUMBER)
    RETURN NUMBER,
    MEMBER FUNCTION ODCIAGGREGATEMERGE( SELF IN OUT zh_concat_im,
    SCTX2 IN zh_concat_im) RETURN NUMBER
    );
    
    
    ---创建类型内容
    CREATE OR REPLACE TYPE BODY zh_concat_im
    IS
    STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT zh_concat_im)
    RETURN NUMBER
    IS
    BEGIN
    SCTX := zh_concat_im( NULL) ;
    RETURN ODCICONST.SUCCESS;
    END;
    MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT zh_concat_im,
    P1 IN VARCHAR2)
    RETURN NUMBER
    IS
    BEGIN
    IF(CURR_STR IS NOT NULL) THEN
    CURR_STR := CURR_STR || ',' || P1;
    ELSE
    CURR_STR := P1;
    END IF;
    RETURN ODCICONST.SUCCESS;
    END;
    MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN zh_concat_im,
    RETURNVALUE OUT clob,
    FLAGS IN NUMBER)
    RETURN NUMBER
    IS
    BEGIN
    RETURNVALUE := CURR_STR ;
    RETURN ODCICONST.SUCCESS;
    END;
    MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT zh_concat_im,
    SCTX2 IN zh_concat_im)
    RETURN NUMBER
    IS
    BEGIN
    IF(SCTX2.CURR_STR IS NOT NULL) THEN
    SELF.CURR_STR := SELF.CURR_STR || ',' || SCTX2.CURR_STR ;
    END IF;
    RETURN ODCICONST.SUCCESS;
    END;
    END;
    
    
    ---创建函数
    create or replace FUNCTION zh_concat(P1 VARCHAR2)
    RETURN clob AGGREGATE USING zh_concat_im ;
    
    
    ---调用方式
     select zh_concat(d.sid) from pmp_p_info d;

#### java后台拿到返回的clob类型 ####

BufferedReader in = null;
                                StringWriter out = null;
                                String content = "";
                                try {
        
                                    Clob clob = (Clob)objs[5];
                                    in = new BufferedReader(clob.getCharacterStream());
                                    out = new StringWriter();
                                    int c;
                                    while ((c = in.read()) != -1) {
        
                                        out.write(c);
                                    }
                                    content = out.toString();
                                } catch (Exception e) {
        
                                    e.printStackTrace();
                                } finally {
        
                                    in.close();
                                    out.close();
                                }

#### java-- clob类型转String ####

package org.jeecg.codegenerate.util;
    
    import java.io.BufferedReader;
    import java.io.IOException;
    import java.io.Reader;
    import java.sql.Clob;
    import java.sql.SQLException;
    
    public class clodUtuil {
        
    
        public  static String  ClobToString(Clob clob) throws SQLException, IOException {
        
            String result = "";
            if ( clob == null){
        
                return "";
            }
            Reader is = clob.getCharacterStream();
            BufferedReader reader = new BufferedReader(is);
            String s = reader.readLine();
            StringBuffer buffer = new StringBuffer();
            while (s!=null){
        
                buffer.append(s);
                s= reader.readLine();
            }
            return buffer.toString();
            
    
        }
    }

### SQL执行结果 ###

![在这里插入图片描述][f74b3c8cb89b417099c4129dce8d6398.png]

[f74b3c8cb89b417099c4129dce8d6398.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/a10b93b3cac242e1a7c47ef01927483e.png