在存储过程里面,需要有个变量存储一段字符串,因为这段字符串可能很长,可能达到几万个字符,所以考虑用clob来存储,但是我在存储过程中如下写,报错message:=empty_clob();
DBMS_LOB.WRITEAPPEND(message, 10, '0123456789');说指定的lob定位符无效,谁知道如何往clob里面直接写入字符串,在线等。
oracle如何操作clob数据类型
答案:2 悬赏:20 手机版
解决时间 2021-04-05 17:29
- 提问者网友:咪咪
- 2021-04-04 20:40
最佳答案
- 五星知识达人网友:执傲
- 2020-03-28 20:46
在做数据库开发的时候,有时候会遇到需要读取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
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;
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
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;
全部回答
- 1楼网友:猎心人
- 2021-01-25 02:19
clob是二进制类型的,可以存文件
具体用法就是将一个文件( 比如图片) 二进制序列化后存入这个字段,
读取后再反序列化回来
我要举报
如以上问答信息为低俗、色情、不良、暴力、侵权、涉及违法等信息,可以点下面链接进行举报!
大家都在看
推荐资讯