oracle查询blob模糊搜索,BLOB字段模糊查询

1. BLOB字段模糊查询报错

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

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

2. UTL_RAW.CAST_TO_RAW

该函数按照缺省字符集,将VARCHAR2字符串转换为RAW。

sys@ORCL>select

utl_raw.cast_to_raw('shall') raw1,utl_raw.cast_to_raw('zhong') raw2 from dual;

RAW1                 RAW2

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

7368616C6C           7A686F6E67

也可以用rawtohex函数实现:

sys@ORCL>select rawtohex('shall')

raw1,rawtohex('zhong') raw2 from dual;

RAW1                 RAW2

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

7368616C6C           7A686F6E67

3. UTL_RAW.CAST_TO_VARCHAR2

该函数按照缺省字符集合,将RAW转换为VARCHAR2。

sys@ORCL>select

utl_raw.cast_to_varchar2('7368616C6C')

var1,utl_raw.cast_to_varchar2('7A686F6E67') var2 from dual;

VAR1

VAR2

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

shall

zhong

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

4.实验:

----创建表

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

Table

created

----插入数据

sys@ORCL>insert into blob_test values(1,'shall

zhong');

insert into blob_test values(1,'shall

zhong')

*

ERROR at line 1:

ORA-01465: invalid hex number

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

SQL> insert into blob_test values(1,UTL_RAW.CAST_TO_RAW('shall

zhong'));

1 row

inserted

SQL> insert into blob_test values(1,UTL_RAW.CAST_TO_RAW('这里是BLOB字段,数据zhong'));

1 row

inserted

SQL> select * from blob_test;

ID CONTENT

----------

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

1 7368616C6C207A686F6E67

1 D5E2C0EFCAC7424C4F42D7D6B6CEA3ACCAFDBEDD7A686F6E67

----可以看到用两个函数转换,都可以插入成功,并且可以直接查询出来,但插入到数据库里面的结果为16进制数据(注:11g的数据库可以直接查出来,10g的无法直接查询blob字段数据)。

----那么我们能直接往表中以16进制的方式插入数据吗?

SQL> insert into blob_test

values(2,'D5E2C0EFCAC7424C4F42D7D6B6CEA3ACCAFDBEDD7A686F6E67');

1 row

inserted

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

ID VAR1

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

1 shall zhong

1 这里是BLOB字段,数据zhong

2 这里是BLOB字段,数据zhong

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

----下面是对 blob 字段的修改操作

SQL> update blob_test set content=rawtohex('当前数据库环境为11G') where id =1;

2 rows updated

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

ID VAR1

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

1 当前数据库环境为11G

1 当前数据库环境为11G

2 这里是BLOB字段,数据zhong

SQL> update blob_test set content='7368616C6C207A686F6E67' where id =2;

1 row updated

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

ID VAR1

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

1 当前数据库环境为11G

1 当前数据库环境为11G

2 shall zhong

----对 blob 字段的模糊查询。

SQL> select

id,UTL_RAW.CAST_TO_VARCHAR2(content) var1 from blob_test where content like

'%shall%';

select id,UTL_RAW.CAST_TO_VARCHAR2(content)

var1 from blob_test where content like '%shall%'

*

ERROR at line 1:

ORA-00932: inconsistent datatypes: expected

NUMBER got BLOB

SQL> select

id,UTL_RAW.CAST_TO_VARCHAR2(content) var1 from blob_test where content like ' 7368616C6C207A686F6E67';

select id,UTL_RAW.CAST_TO_VARCHAR2(content)

var1 from blob_test where content like ' 7368616C6C207A686F6E67'

*

ERROR at line 1:

ORA-00932: inconsistent datatypes: expected

NUMBER got BLOB

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

SQL> select id,UTL_RAW.CAST_TO_VARCHAR2(content) var1 from blob_test

where dbms_lob.instr(content,utl_raw.cast_to_raw('shall'),1,1) > 0;

ID VAR1

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

2 shall zhong

SQL> select id,UTL_RAW.CAST_TO_VARCHAR2(content) var1 from blob_test

where dbms_lob.instr(content,utl_raw.cast_to_raw('G'),1,1) > 0;

ID VAR1

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

1 当前数据库环境为11G

1 当前数据库环境为11G

refencen:

http://blog.sina.com.cn/s/blog_ad6555610102v9q1.html

参与评论 您还未登录,请先 登录 后发表或查看评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
©️2022 CSDN 皮肤主题:1024 设计师:我叫白小胖 返回首页
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值