使用存储过程读取Oracle中的clob字段的数据

使用存储过程读取Oracle中的clob字段的数据

在做数据库开发的时候，有时候会遇到需要读取Oracle数据库中的clob类型的数据的情况。本着代码复用的目的，我写了下面的存储过程：读取数据库中clob字段的数据。希望跟大家交流一下。

CREATE OR REPLACE 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;