oracle中Blob、Clob、Varchar之间的互相转换 |
您所在的位置：网站首页 › oracle中blob类型 › oracle中Blob、Clob、Varchar之间的互相转换 |
|
以下是oracle中Blob、Clob、Varchar之间的互相转换（都是百度找的，亲测可用） Blob转Varchar2: CREATE OR REPLACE FUNCTION blob_to_varchar (blob_in IN BLOB) RETURN VARCHAR2 IS v_varchar VARCHAR2(4000); v_start PLS_INTEGER := 1; v_buffer PLS_INTEGER := 4000; BEGIN --select userenv('LANGUAGE') into g_nls_db_char from dual; if DBMS_LOB.GETLENGTH(blob_in) is null then return empty_clob(); end if; DBMS_OUTPUT.put_line('TEST:' || CEIL(DBMS_LOB.GETLENGTH(blob_in))); --DBMS_LOB.CREATETEMPORARY(v_clob, TRUE); FOR i IN 1..CEIL(DBMS_LOB.GETLENGTH(blob_in) / v_buffer) LOOP --如果乱码了就需要调整字符集了 --select userenv('language') from dual 查看数据库编码 v_varchar := UTL_RAW.CAST_TO_VARCHAR2(utl_raw.convert(DBMS_LOB.SUBSTR(blob_in, v_buffer, v_start),'SIMPLIFIED CHINESE_CHINA.ZHS16GBK', 'SIMPLIFIED CHINESE_CHINA.ZHS16GBK')); --DBMS_LOB.WRITEAPPEND(v_clob, LENGTH(v_varchar), v_varchar); v_start := v_start + v_buffer; END LOOP; --DBMS_OUTPUT.put_line(v_varchar); RETURN v_varchar; end blob_to_varchar;Blob转Clob: CREATE OR REPLACE FUNCTION blob_to_clob (blob_in IN BLOB) RETURN CLOB IS v_clob CLOB; v_varchar VARCHAR2(4000); v_start PLS_INTEGER := 1; v_buffer PLS_INTEGER := 4000; g_nls_db_char varchar2(60); BEGIN select userenv('LANGUAGE') into g_nls_db_char from dual; if DBMS_LOB.GETLENGTH(blob_in) is null then return empty_clob(); end if; DBMS_OUTPUT.put_line('TEST:' || CEIL(DBMS_LOB.GETLENGTH(blob_in))); DBMS_LOB.CREATETEMPORARY(v_clob, TRUE); FOR i IN 1..CEIL(DBMS_LOB.GETLENGTH(blob_in) / v_buffer) LOOP --如果乱码了，就需要调整字符集了 select userenv('language') from dual --查看数据库编码 v_varchar := UTL_RAW.CAST_TO_VARCHAR2(utl_raw.convert(DBMS_LOB.SUBSTR(blob_in, v_buffer, v_start),'SIMPLIFIED CHINESE_CHINA.ZHS16GBK','SIMPLIFIED CHINESE_CHINA.ZHS16GBK')); DBMS_LOB.WRITEAPPEND(v_clob, LENGTH(v_varchar), v_varchar); v_start := v_start + v_buffer; END LOOP; --DBMS_OUTPUT.put_line(v_varchar); RETURN v_clob; end blob_to_clob;
Clob转Blob： CREATE OR REPLACE FUNCTION Clob_To_Blob (b IN CLOB default empty_clob()) RETURN BLOB -- typecasts BLOB to CLOB (binary conversion) IS res BLOB; b_len number := dbms_lob.getlength(b) ; dest_offset1 NUMBER := 1; src_offset1 NUMBER := 1; amount_c INTEGER := DBMS_LOB.lobmaxsize; blob_csid NUMBER := DBMS_LOB.default_csid; lang_ctx INTEGER := DBMS_LOB.default_lang_ctx; warning INTEGER; BEGIN if b_len > 0 then DBMS_LOB.createtemporary (res, TRUE); DBMS_LOB.OPEN (res, DBMS_LOB.lob_readwrite); DBMS_LOB.convertToBlob (res, b, amount_c, dest_offset1, src_offset1, blob_csid, lang_ctx, warning ); else select empty_blob() into res from dual ; end if ; RETURN res; END Clob_To_Blob;Clob转Varchar2 : to_char(CLOB) Varchar2转Clob 自动隐式转化； Varchar2转Blob：调用 Clob_To_Blob(); |
今日新闻 |
推荐新闻 |
| CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |