处理Oracle CLOB字段

PROCEDURE prc_read_clob(table_name               IN VARCHAR2,
                          clob_column_name         IN VARCHAR2,
                          primary_Key_Column_names IN VARCHAR2,
                          primary_key_values       IN VARCHAR2,
                          offset_i                 IN NUMBER,
                          read_length_i            IN NUMBER,
                          RES                      OUT VARCHAR2,
                          total_length             OUT NUMBER) AS
    /**
      Autor:Hanks_gao.
      Create Date:2008/12/10
      Description:This procedure is to read clob value by conditions
      --------------------------------------------------------------
      -----------------Parameters descritption----------------------
                    table_name : The table that contains clob/blob columns(表名)
            clob_column_name : Clob/blob column name of table_name(类型为clob的字段名)
    primary_key_column_names : The columns seperated by '}' that can fix only one row data (that is primary key) (主键名，以'}'分隔的字符串)
            primary_key_values : The primary keyes values that seperated by '}'(主键键值,以'}'分隔的字符串)
                      offset_i : The offset of reading clob data(要读取的位移量)
                 read_length_i : The length of reading clob data per times(要读取的长度)
                           res : Return value that can be referenced by application(读取的结果)
                  total_length : The total length of readed clob data(数据库查询到的clob数据的总长度)
      -----------------End Parameters descritption------------------
    */
  
    tmpPrimaryKeys      VARCHAR2(2000); --To save primary_Key_Column_names temporarily(暂存主键,主键是以'}'分隔的字符串)
    tmpPrimaryKeyValues VARCHAR2(2000); --To save primary_key_values temporarily(暂存主键键值,以'}'分隔的字符串)
    i                   NUMBER; --循环控制变量
    tmpReadLength       NUMBER; --暂存要读取的长度
    sqlStr              VARCHAR2(6000); --Query string(查询字符串)
    sqlCon              VARCHAR2(5000); --Query condition(查询条件)
  
    TYPE tmparray IS TABLE OF VARCHAR2(5000) INDEX BY BINARY_INTEGER;
    arrayPrimaryKeys      tmparray; --To save the analyse result of primary_Key_Column_names (暂存分析后得到的主键名)
    arrayPrimaryKeyValues tmparray; --To save the analyse result of primary_key_values(暂存分析后得到的主键键值)
  BEGIN
    total_length := 0;
    RES          := '';
    DECLARE
      clobvar CLOB := EMPTY_CLOB;
    BEGIN
      tmpPrimaryKeys      := primary_Key_Column_names;
      tmpPrimaryKeyValues := primary_key_values;
    
      i := 0;
      WHILE INSTR(tmpPrimaryKeys, '}') > 0 LOOP
        --Analyse the column names of primary key(将主键分开,相当于arrayPrimaryKeys =tmpPrimaryKeys.split("}") )
        arrayPrimaryKeys(i) := subSTR(tmpPrimaryKeys,
                                      1,
                                      (INSTR(tmpPrimaryKeys, '}') - 1));
        tmpPrimaryKeys := subSTR(tmpPrimaryKeys,
                                 (INSTR(tmpPrimaryKeys, '}') + 1));
        i := i + 1;
      END LOOP;
    
      i := 0;
      WHILE INSTR(tmpPrimaryKeyValues, '}') > 0 LOOP
        --Analyse the values of primary key
        arrayPrimaryKeyValues(i) := subSTR(tmpPrimaryKeyValues,
                                           1,
                                           (INSTR(tmpPrimaryKeyValues, '}') - 1));
        tmpPrimaryKeyValues := subSTR(tmpPrimaryKeyValues,
                                      (INSTR(tmpPrimaryKeyValues, '}') + 1));
        i := i + 1;
      END LOOP;
    
      IF arrayPrimaryKeys.COUNT() <> arrayPrimaryKeyValues.COUNT() THEN
        --判断键与键值是否能匹配起来
        res := 'KEY-VALUE NOT MATCH';
        RETURN;
      END IF;
    
      i      := 0;
      sqlCon := '';
      WHILE i < arrayPrimaryKeys.COUNT() LOOP
        sqlCon := sqlCon || ' AND ' || arrayPrimaryKeys(i) || '=''' ||
                  replace(arrayPrimaryKeyValues(i), '''', '''''') || '''';
        i      := i + 1;
      END LOOP;
    
      sqlStr := 'SELECT ' || clob_column_name || ' FROM ' || table_name ||
                ' WHERE 1=1 ' || sqlCon || ' AND ROWNUM = 1'; --组查询字符串
    
      dbms_lob.createtemporary(clobvar, TRUE);
      dbms_lob.OPEN(clobvar, dbms_lob.lob_readwrite);
    
      EXECUTE IMMEDIATE TRIM(sqlStr)
        INTO clobvar; --执行查询
    
      IF offset_i <= 1 THEN
        total_length := dbms_lob.getlength(clobvar);
      END IF;
    
      IF read_length_i <= 0 THEN
        tmpReadLength := 4000;
      ELSE
        tmpReadLength := read_length_i;
      END IF;
    
      dbms_lob.READ(clobvar, tmpReadLength, offset_i, res); --读取数据
    
      IF dbms_lob.ISOPEN(clobvar) = 1 THEN
        dbms_lob.CLOSE(clobvar);
      END IF;
    
    END;
  EXCEPTION
    WHEN OTHERS THEN
      res          := '';
      total_length := 0;
  END;