关于在sql上查询blob类型数据的操作要点

关于在sql上查询blob类型数据的操作要点
blob字段直接用 select *from table_name where column like‘%%’查找的时候是不能实现的 ,主要是字段类型不符。不过我们可以用数据库自带的 utl_raw 函数进行blob 字段的插入,查询和模糊匹配。

先介绍一下Oracle中RAW和 varchar2常用的两个转换函数

1. UTL_RAW.CAST_TO_RAW

该函数按照缺省字符集(一般为GB2312),将VARCHAR2字符串转换为RAW。

SQL> select UTL_RAW.CAST_TO_RAW('xiaowurq') from dual;

SQL> select UTL_RAW.CAST_TO_RAW('xiaowurq') raw1,UTL_RAW.CAST_TO_RAW('肖舞') raw2 from dual;

RAW1 RAW2

------------------------------------------------------------

7869616F77757271 D0A4CEE8


也可以用 rawtohex 函数实现:

SQL> select rawtohex('xiaowurq') raw1,rawtohex('肖舞') raw2from dual;

RAW1 RAW2
------------------------------ ------------------------------
7869616F77757271 D0A4CEE8


2.UTL_RAW.CAST_TO_VARCHAR2

该函数按照缺省字符集合(一般为GB2312),将RAW转换为VARCHAR2。

SQL> select UTL_RAW.CAST_TO_VARCHAR2('7869616F77757271')var1, UTL_RAW.CAST_TO_VARCHAR2('D0A4CEE8') var2 fromdual;

VAR1 VAR2

------------------------------------------------------------

xiaowurq 肖舞

其实RAW和VARCHAR是类似的,只是存储在RAW里的是二进制值,在任何时候不会做自动的字符集转换,这是RAW和VARCHAR的不同,RAW只是一种外部类型,其内部存储是VARRAW。

下面整体做个实验:

(1)创建表

SQL> create table blob_test( id number, content blob);

Table created.


(2) 插入数据

SQL> insert into blob_test values(1,'xiaowurq');

insert into blob_test values(1,'xiaowurq')

*

ERROR at line 1:

ORA-01465: invalid hex number

由报错可以看出,无法直接往 blob字段中插入数据,下面用UTL_RAW.CAST_TO_RAW 和 ,RAWTOHEX 函数转换一下再插入。

SQL> insert into blob_testvalues(1,UTL_RAW.CAST_TO_RAW('xiaowurq'));

1 row created.

SQL> insert into blob_testvalues(2,RAWTOHEX('肖舞'));

1 row created.

SQL> select * from blob_test;

ID CONTENT
---------- --------------------------------------------------
1 7869616F77757271
2 D0A4CEE8

可以看到用两个函数转换,都可以插入成功,并且可以直接查询出来,但插入到数据库里面的结果为16进制数据,(注:11g 的数据库可以直接查出来, 10g 的无法直接查询 blob字段数据)。那么我们能直接往表中以16进制的方式插入数据吗?

SQL> insert into blob_test values(3,'7869616F77757271');

1 row created.

SQL> select id,UTL_RAW.CAST_TO_VARCHAR2(content) var1 fromblob_test;

ID VAR1

---------- ------------------------------

1 xiaowurq

2 肖舞

3 xiaowurq

可以看出是可以直接往数据库里插入16进制数据的,并且我们可以看到用两种方式插入的结果是一致的。


(3) 下面是对 blob字段的修改操作,不再做过多解释。

SQL> update blob_test set content = RAWTOHEX('肖舞') whereid=3;

1 row updated.

SQL> select id,UTL_RAW.CAST_TO_VARCHAR2(content) var1 fromblob_test;

ID VAR1

---------- ------------------------------

1 xiaowurq

2 肖舞

3 肖舞

SQL> update blob_test set content ='7869616F77757271D0A4CEE8' where id=3;

1 row updated.

SQL> select * from blob_test;

ID CONTENT

------------------------------------------------------------

1 7869616F77757271

2 D0A4CEE8

3 7869616F77757271D0A4CEE8


(4) 对 blob 字段的模糊查询。

SQL> select id,UTL_RAW.CAST_TO_VARCHAR2(content) var1 fromblob_test where content like '%xiaowu%';

select id,UTL_RAW.CAST_TO_VARCHAR2(content) var1 from blob_testwhere content like '%xiaowu%'
*

ERROR at line 1:

ORA-00932: inconsistent datatypes: expected NUMBER gotBLOB


SQL> select id,UTL_RAW.CAST_TO_VARCHAR2(content) var1 fromblob_test where content like '�A4CEE8%';

select id,UTL_RAW.CAST_TO_VARCHAR2(content) var1 from blob_testwhere content like '�A4CEE8%'
*

ERROR at line 1:

ORA-00932: inconsistent datatypes: expected NUMBER gotBLOB

有上面的两个命令和报错可以看出,无法对 blob 字段进行模糊查询,及时用16进制数据去匹配也不行,不过我们可以通过下面的方法实现对 blob 字段进行模糊匹配。

SQL> select id,UTL_RAW.CAST_TO_VARCHAR2(content) var1 fromblob_test wheredbms_lob.instr(content,utl_raw.cast_to_raw('肖舞'),1,1) >0;

ID VAR1

---------- ------------------------------

2 肖舞

3 xiaowurq肖舞

SQL> select id,UTL_RAW.CAST_TO_VARCHAR2(content) var1 fromblob_test wheredbms_lob.instr(content,utl_raw.cast_to_raw('&1'),1,1) >0;

Enter value for 1: xiaowu

old 1: selectid,UTL_RAW.CAST_TO_VARCHAR2(content) var1 from blob_test wheredbms_lob.instr(content,utl_raw.cast_to_raw('&1'),1,1) >0

new 1: selectid,UTL_RAW.CAST_TO_VARCHAR2(content) var1 from blob_test wheredbms_lob.instr(content,utl_raw.cast_to_raw('xiaowu'),1,1) >0

ID VAR1

---------- ------------------------------

1 xiaowurq

3xiaowurq肖舞

推荐阅读

    excel怎么用乘法函数

    excel怎么用乘法函数,乘法,函数,哪个,excel乘法函数怎么用?1、首先用鼠标选中要计算的单元格。2、然后选中单元格后点击左上方工具栏的fx公

    无法读取U盘中的数据

    无法读取U盘中的数据,,核心提示:我有一个512MB的U盘,把它插在电脑显示器里面是空的,但右键单击以查看已经使用USB 480mb文件的属性未设置为隐

    excel中乘法函数是什么?

    excel中乘法函数是什么?,乘法,函数,什么,打开表格,在C1单元格中输入“=A1*B1”乘法公式。以此类推到多个单元。1、A1*B1=C1的Excel乘法公式

    标准差excel用什么函数?

    标准差excel用什么函数?,函数,标准,什么,在数据单元格的下方输入l标准差公式函数公式“=STDEVPA(C2:C6)”。按下回车,求出标准公差值。详细

    EXCEL数据透视表怎么用?是干什么的

    EXCEL数据透视表怎么用?是干什么的,透视,干什么,怎么,excel透视表:数据透视表(Pivot Table)是一种交互式的表,可以进行某些计算,如求和与计数等

    针灸科设置|针灸科常见操作

    针灸科设置|针灸科常见操作,,1. 针灸科常见操作⒈临床实践技能(临床实际本领)考核 ⑴基本操作:①中医四诊、针灸、推拿、拔罐等中医临床技术;