注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

linux++的博客

LINUX+ORACLE+NBA+GAME=NET

 
 
 

日志

 
 

向oracle10g中存放图片  

2009-05-07 14:32:02|  分类: oracle |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

一、以sysdba连接oracle库

E:\bak>sqlplus sys/oracle as sysdba

SQL*Plus: Release 10.1.0.2.0 - Production on 星期四 5月 7 10:21:57 2009

Copyright (c) 1982, 2004, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> show user
USER is "SYS"

二、建用户并注册目录(此目录我们要在系统建好,在oracle里再指定)

SQL> create user eygle identified by eygle default tablespace users;

User created.

SQL> grant connect ,resource,dba to eygle;

Grant succeeded.

SQL> connect / as sysdba
Connected.
SQL> create or replace directory BLOBDIR as 'D:\oradata\Pic';

Directory created.

SQL> grant read on directory BLOBDIR to eygle;

Grant succeeded.


三、创建测试表

SQL> connect eygle/eygle
Connected.
SQL> CREATE TABLE eygle_blob (
  2  fid    number,
  3  fname       varchar2(50),
  4  fdesc  varchar2(200),
  5  fpic        BLOB)
  6  /

Table created.

SQL>
SQL> create sequence S_EYGLE_SEQ
  2  start with 1
  3  increment by 1
  4  /

Sequence created.

四、创建存储过程(我们使用这个过程去存放图片)

SQL> CREATE OR REPLACE PROCEDURE eygle_load_blob (pfname VARCHAR2,pdesc varchar2)
  2  IS
  3  src_file BFILE;
  4  dst_file BLOB;
  5  lgh_file BINARY_INTEGER;
  6  BEGIN
  7     src_file := bfilename('BLOBDIR', pfname);
  8
  9     INSERT INTO eygle_blob (fid,fname,fdesc,fpic)
10     VALUES (S_EYGLE_SEQ.Nextval,pfname,pdesc,EMPTY_BLOB())
11     RETURNING fpic INTO dst_file;
12
13     SELECT fpic INTO dst_file
14     FROM eygle_blob  WHERE fname = pfname FOR UPDATE;
15
16     dbms_lob.fileopen(src_file, dbms_lob.file_readonly);
17     lgh_file := dbms_lob.getlength(src_file);
18     dbms_lob.loadfromfile(dst_file, src_file, lgh_file);
19
20    UPDATE eygle_blob  SET fpic = dst_file
21    WHERE fname = pfname;
22
23    dbms_lob.fileclose(src_file);
24    commit;
25  END eygle_load_blob;
26  /

Procedure created.

SQL> col segment_name for a30
SQL> select segment_name,segment_type,bytes/1024/1024 from dba_segments where owner='EYGLE';

SEGMENT_NAME                   SEGMENT_TYPE       BYTES/1024/1024
------------------------------ ------------------ ---------------
SYS_IL0000050545C00004$$       LOBINDEX                     .0625
SYS_LOB0000050545C00004$$      LOBSEGMENT                   .0625
EYGLE_BLOB                     TABLE                        .0625

五、加载Blob对象(此目录下要有图片)

SQL> exec eygle_load_blob('111.jpg','111);

PL/SQL procedure successfully completed.

SQL> exec eygle_load_blob('111.jpg','111);

PL/SQL procedure successfully completed.
SQL> col fname for a20
SQL> col fdesc for a30
SQL> select fid,fname,fdesc,dbms_lob.getlength(fpic) siz from eygle_blob;

       FID FNAME                FDESC                                 SIZ
---------- -------------------- ------------------------------ ----------
         1  111.jpg                 111                              1768198
         2  222.jpg                 222                              2131553

这是使用oracle的blob来存放,当然也可以存放视频文件。

  评论这张
 
阅读(328)| 评论(1)
推荐 转载

历史上的今天

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2017