使用預存程序讀取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;