oracle中varchar2字段转clob字段，并提供读取方法。

转载自：http://my.oschina.net/guyfar/blog/73837

问题描述

CMS系统中有一个表的字段(如：content)是varchar2类型(最多只能存储4000字符)，不够用了，因此将其改为clob类型(支持4G存储量)。

如果该字段content列不为空的话，不能直接通过sql语句修改其字段类型，会报ORA-22858的错误，如图所示：

解决方法:

1.首先创建一个clob的临时字段：

1	alter table table_name add(tmp clob);

2.然后将content字段内容全部复制到tmp字段中，具体方法请见：

http://my.oschina.net/guyfar/blog/73829

3.修改content字段名为content_bak，将tmp的字段名改为content即可。

1	alter table table_name rename column content to content_bak;

2

3	alter table  table_name  rename column tmp to content;

到这里就完成了字段类型的转换。

虽然数据类型已经转换完成，会发现调用此字段的程序会报错。那是因为通过sql查询出的clob字段是对象类型，因此会报错。

我是通过存储过程来调用的，存储过程如下：

01	create or replace function getclob(

02

03	table_name  in varchar2,

04

05	field_id     in varchar2,

06

07	field_name  in varchar2,

08

09	v_id  in number,

10

11	v_pos  in number) return varchar2

12

13

is

14

15	lobloc  clob;

16

17	buffer  varchar2(32767);

18

19	amount  number := 2000;

20

21	offset  number := 1;

22

23	query_str  varchar2(1000);

24

25

begin

26

27	query_str :='select '||field_name||' from '||table_name||' where '||field_id||'= :id ';

28

29	--initialize buffer with data to be found

30

31	EXECUTE IMMEDIATE query_str INTO lobloc USING v_id;

32

33	offset:=offset+(v_pos-1)*2000;

34

35	--read 2000 varchar2 from the buffer

36

37	dbms_lob.read(lobloc,amount,offset,buffer);

38

39	return buffer;

40

41

exception

42

43	when no_data_found then

44

45	return buffer;

46

47

end;

调用存储过程的PHP方法：

01	function edt_disp_getclob($id, $field_id, $field_name, $len) {

02	$a = 2000;

03	$b = $a / 2;

04	if (($len % $b) == 0) {

05	$maxpage = floor ( $len / $a );

06

} else {

07	$maxpage = floor ( $len / $a ) + 1;

08

}

09

$i = 0;

10	$con = "";

11	$a = new DB_Sql ();

12	while ( $i < $maxpage ) {

13	$i = $i + 1;

14	$sql = "select getclob('table_name','$field_id','$field_name','$id','$i') as h from dual";

15	//echo $sql."<BR>";exit;

16	$a->query ( $sql );

17	if ($a->next_record ()) {

18	$h = $a->Record ["h"];

19

}

20	$con = $con . $h;

21

}

22	if (! empty ( $con )) {

23	$a->disconnect ();

24

}

25	return $con;

26

}

调用：

1	$con = edt_disp_getclob($id, 'id', 'con', $con_len);

参数说明：

$id ：要查询的行

id   :按ID查询

con  ：字段名称

$con_len  :要查询的字段长度(提前通过sql查出)

 

至此，所有改动完成。

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;