在。NET中应用Oracle存储过程操作BLOB

原创 2004年08月11日 17:35:00

SQl部分:--一次接线图
drop sequence YW_YCJXT_ID

create sequence YW_YCJXT_ID
start with 1
increment by 1;

drop table YW_YCJXT
truncate table YW_YCJXT

create table YW_YCJXT
(
 id numeric(5) primary key not null,            ---接线图id
 G3E_FNO numeric(10) not null,                  ---设备FNO
 G3E_FID numeric(10) not null,                  ---设备FID
 picsize numeric(10),         ---接线图图片大小
 pictype varchar2(20),        ---图片类型
 picname varchar2(50),        ---接线图图片名称
 description varchar2(500),   --图片描述
 adddate date,                  ---图片添加日期
 modifydate date,                ---图片上次修改日期
 pic BLOB                     ---接线图
)

CREATE OR REPLACE PACKAGE YCJXT AS
  TYPE MyCur IS REF CURSOR;
  PROCEDURE Add_ycjxt(
  p_g3e_fno YW_YCJXT.g3e_fno%type,
  p_g3e_fid YW_YCJXT.g3e_fid%type,
  p_picsize YW_YCJXT.picsize%type,
  p_pictype YW_YCJXT.pictype%type,
  p_picname YW_YCJXT.picname%type,
  p_description YW_YCJXT.description%type,
  p_pic IN BLOB);
 
  procedure update_ycjxt(
  p_id YW_YCJXT.id%type,
  p_g3e_fno YW_YCJXT.g3e_fno%type,
  p_g3e_fid YW_YCJXT.g3e_fid%type,
  p_picsize YW_YCJXT.picsize%type,
  p_pictype YW_YCJXT.pictype%type,
  p_picname YW_YCJXT.picname%type,
  p_description YW_YCJXT.description%type,
  p_pic IN BLOB);
 
  procedure del_ycjxt(
p_id YW_YCJXT.id%type);

  procedure search_ycjxt(SeachCur OUT MyCur,
                         p_g3efno numeric);
 
  procedure search_filter_ycjxt(SearchfilterCur OUT MyCur,
                               P_ID IN YW_YCJXT.id%TYPE);
END YCJXT;
/

CREATE OR REPLACE PACKAGE BODY YCJXT AS
-------------
--***添加***-
-------------
procedure Add_ycjxt(
  p_g3e_fno YW_YCJXT.g3e_fno%type,
  p_g3e_fid YW_YCJXT.g3e_fid%type,
  p_picsize YW_YCJXT.picsize%type,
  p_pictype YW_YCJXT.pictype%type,
  p_picname YW_YCJXT.picname%type,
  p_description YW_YCJXT.description%type,
  p_pic IN BLOB) as
begin
   insert into YW_YCJXT(id,g3e_fno,g3e_fid,picsize,pictype,picname,description,adddate,pic) values(YW_YCJXT_ID.NEXTVAL,p_g3e_fno,p_g3e_fid,p_picsize,p_pictype,p_picname,p_description,SYSDATE,p_pic);
   commit;
end Add_ycjxt;

-------------
--***修改***-
-------------
procedure update_ycjxt(
  p_id YW_YCJXT.id%type,
  p_g3e_fno YW_YCJXT.g3e_fno%type,
  p_g3e_fid YW_YCJXT.g3e_fid%type,
  p_picsize YW_YCJXT.picsize%type,
  p_pictype YW_YCJXT.pictype%type,
  p_picname YW_YCJXT.picname%type,
  p_description YW_YCJXT.description%type,
  p_pic IN BLOB) as
Begin
 update YW_YCJXT set g3e_fno=p_g3e_fno,g3e_fid=p_g3e_fid,picsize=p_picsize,modifydate=Sysdate,pictype=p_pictype,picname=p_picname,description=p_description,pic=p_pic where id=p_id;
 commit;
end update_ycjxt;

-------------
--***删除***-
-------------
procedure del_ycjxt(
p_id YW_YCJXT.id%type) as
begin
  delete from YW_YCJXT where g3e_fid=p_id;
  commit;
end del_ycjxt;

--------------
--***查询***--
--------------
procedure search_ycjxt(
SeachCur OUT MyCur,
p_g3efno IN numeric)
 IS
 LocalSearchCur MyCur;
BEGIN
  OPEN LocalSearchCur FOR
  select id,g3e_fno,g3e_fid,picsize,pictype,picname,description,to_char(adddate,'yyyy/mm/dd'),to_char(modifydate) from yw_ycjxt where g3e_fno=p_g3efno order by adddate desc;
  SeachCur := LocalSearchCur;
END search_ycjxt;

---------------------
--***查询某条接线图**--
---------------------
procedure search_filter_ycjxt(SearchfilterCur OUT MyCur,
                               P_ID IN YW_YCJXT.id%TYPE)
 IS
 LocalSearchFilterCur MyCur;
BEGIN
 OPEN LocalSearchFilterCur FOR
 select pictype,description from yw_ycjxt WHERE ID=P_ID;
 SearchfilterCur := LocalSearchFilterCur;
END search_filter_ycjxt;
END YCJXT;
/
----------------------------------------------------------
----- ************ end  *************- ------------------

class部分:
using System;
using System.Data;
using System.Data.OracleClient;
using Gis;
using System.IO;
using System.Web;
namespace YCJXT{
   public class ycjxtmanage
   {
     logfile lfile;
     public ycjxtmanage()
     {
       lfile = new logfile();
     }
     ////////////////////////////////////////////////////////////////////////////////////
     ///////      功能:删除一次接线图       //////////////////////////////////////////////////////
     ////// 输入参数: p_id 图片ID ;DBConn:  连接参数 ////////////////////////////////////////
     ////////////////////////////////////////////////////////////////////////////////////
     public void delimage(int p_id,string DBConn)
     {
       lfile.Msg("YCJXT.delimage:","Enter");
       OracleConnection MyConn=new OracleConnection(DBConn);

       OracleCommand MyCommand = new OracleCommand();
       MyCommand.Connection=MyConn;
       MyCommand.CommandText = "YCJXT.del_ycjxt";
       MyCommand.CommandType = CommandType.StoredProcedure;
       MyCommand.Parameters.Add(new OracleParameter("p_id", OracleType.Number)).Value = p_id;
       MyConn.Open();
       try
       {
       MyCommand.ExecuteNonQuery();
       lfile.Msg("YCJXT.delimage:","Delete success.");
       }
       catch(Exception ex){
       lfile.Msg("YCJXT.delimage:",ex.Message);
       }
       finally
       {
       MyConn.Close();
       }
     }
    
     /////////////////////////////////////////////////////////////////////////////////////
     ///////    功能:添加一次接线图   ///////////////////////////////////////////////////////////
     //////     输入参数 :  p_g3e_fno 设备FNO ; p_g3e_fid  设备FID/////////////////////////
     /////      p_picsize  接线图图片大小  ; p_pictype  图片类型  ; p_picname  接线图图片名称//
     /////      p_description  图片描述 ;  DBConn: 连接参数 //////////////////
     //////////////////////////////////////////////////////////////////////////////////////
     public void addimage(int p_g3e_fno,int p_g3e_fid,int p_picsize,string p_pictype,HttpPostedFile p_picpath,string p_picname,string p_description,string DBConn)
     {
       lfile.Msg("YCJXT.addimage:","Enter");
      // string fileTitle = p_picname.Split("/",1);
       //Split(MyFile.Value,"/")(ubound(Split(MyFile.Value,"/")))
       OracleConnection MyConn = new OracleConnection(DBConn);
       Stream fileDataStream = p_picpath.InputStream;

       //得到文件大小
       int fileLength = p_picpath.ContentLength;
      // Stream fs = File.OpenRead(p_picpath);
       byte[] tempbuff = new byte[fileLength];
       fileDataStream.Read(tempbuff,0,fileLength);
       //fileDataStream.Close();
       MyConn.Open();
       OracleTransaction OraTrans;
       OraTrans = MyConn.BeginTransaction();
       OracleCommand MyCommand = new OracleCommand();
       MyCommand = MyConn.CreateCommand();
       MyCommand.Transaction = OraTrans;
       MyCommand.CommandText = "declare xx blob; begin dbms_lob.createtemporary(xx,false,0); :tempblob:=xx; end;";
       MyCommand.Parameters.Add(new OracleParameter("tempblob",OracleType.Blob)).Direction = ParameterDirection.Output;
       MyCommand.ExecuteNonQuery();
       OracleLob templob = (OracleLob)MyCommand.Parameters[0].Value;
       templob.BeginBatch(OracleLobOpenMode.ReadWrite);
       templob.Write(tempbuff,0,tempbuff.Length);
       templob.EndBatch();
       MyCommand.Parameters.Clear();
       MyCommand.Connection = MyConn;
       MyCommand.CommandText = "YCJXT.Add_ycjxt";
       MyCommand.CommandType = CommandType.StoredProcedure;
       MyCommand.Parameters.Add(new OracleParameter("p_g3e_fno",OracleType.Number)).Value = p_g3e_fno;
       MyCommand.Parameters.Add(new OracleParameter("p_g3e_fid",OracleType.Number)).Value = p_g3e_fid;
       MyCommand.Parameters.Add(new OracleParameter("p_picsize",OracleType.Number)).Value = Convert.ToInt32(p_picsize/1024);
       MyCommand.Parameters.Add(new OracleParameter("p_pictype",OracleType.Char)).Value = p_pictype;
       MyCommand.Parameters.Add(new OracleParameter("p_picname",OracleType.Char)).Value = p_picname;
       MyCommand.Parameters.Add(new OracleParameter("p_description",OracleType.Char)).Value = p_description;
       MyCommand.Parameters.Add(new OracleParameter("p_pic",OracleType.Blob)).Value = templob;
      
       try
       {
       MyCommand.ExecuteNonQuery();
       lfile.Msg("YCJXT.addimage:","Insert Success.");
       OraTrans.Commit();
       }
       catch(Exception ex){
       lfile.Msg("YCJXT.addimage:",ex.Message);
       }
       finally
       {
       MyConn.Close();
       }
     }

     /////////////////////////////////////////////////////////////////////////////////////
     ///////    功能:修改一次接线图   ///////////////////////////////////////////////////////////
     //////     输入参数 :  p_id 图片ID ; p_g3e_fno 设备FNO ; p_g3e_fid  设备FID///////////////////
     /////      p_picsize  接线图图片大小  ; p_pictype  图片类型  ; p_picname  接线图图片名称//
     /////      p_description  图片描述 ;   p_pic  接线图  ;DBConn: 连接参数 //////////////////
     //////////////////////////////////////////////////////////////////////////////////////
     public void updateimage(int p_id,int p_g3e_fno,int p_g3e_fid,int p_picsize,string p_pictype,HttpPostedFile p_picpath,string p_picname,string p_description,string DBConn)
     {
       lfile.Msg("YCJXT.updateimage:","Enter");
       OracleConnection MyConn = new OracleConnection(DBConn);
       Stream fileDataStream = p_picpath.InputStream;

       //得到文件大小
       int fileLength = p_picpath.ContentLength;
      // Stream fs = File.OpenRead(p_picpath);
       byte[] tempbuff = new byte[fileLength];
       fileDataStream.Read(tempbuff,0,fileLength);
       //fileDataStream.Close();
       MyConn.Open();
       OracleTransaction OraTrans;
       OraTrans = MyConn.BeginTransaction();
       OracleCommand MyCommand = new OracleCommand();
       MyCommand = MyConn.CreateCommand();
       MyCommand.Transaction = OraTrans;
       MyCommand.CommandText = "declare xx blob ; begin dbms_lob.createtemporary(xx,false,0); :tempblob := xx; end;";
       MyCommand.Parameters.Add(new OracleParameter("tempblob",OracleType.Blob)).Direction = ParameterDirection.Output;
       MyCommand.ExecuteNonQuery();
       OracleLob templob = (OracleLob)MyCommand.Parameters[0].Value;
       templob.BeginBatch(OracleLobOpenMode.ReadWrite);
       templob.Write(tempbuff,0,tempbuff.Length);
       templob.EndBatch();
       MyCommand.Parameters.Clear();
     
       MyCommand.Connection = MyConn;
       MyCommand.CommandText = "YCJXT.update_ycjxt";
       MyCommand.CommandType = CommandType.StoredProcedure;
       MyCommand.Parameters.Add(new OracleParameter("p_id", OracleType.Number)).Value = p_id;
       MyCommand.Parameters.Add(new OracleParameter("p_g3e_fno",OracleType.Number)).Value = p_g3e_fno;
       MyCommand.Parameters.Add(new OracleParameter("p_g3e_fid",OracleType.Number)).Value = p_g3e_fid;
       MyCommand.Parameters.Add(new OracleParameter("p_picsize",OracleType.Number)).Value = Convert.ToInt32(p_picsize/1024);
       MyCommand.Parameters.Add(new OracleParameter("p_pictype",OracleType.Char)).Value = p_pictype;
       MyCommand.Parameters.Add(new OracleParameter("p_picname",OracleType.Char)).Value = p_picname;
       MyCommand.Parameters.Add(new OracleParameter("p_description",OracleType.Char)).Value = p_description;
       MyCommand.Parameters.Add(new OracleParameter("p_pic",OracleType.Blob)).Value = templob;
      
       try
       {
       MyCommand.ExecuteNonQuery();
       lfile.Msg("YCJXT.updateimage:","update Success.");
       }
       catch(Exception ex){
       lfile.Msg("YCJXT.updateimage:",ex.Message);
       }
       MyConn.Close();
     }

     ////////////////////////////////////////////////////////////////////////////////////////
     //////////////   显示某设备上的一次接线图 ///////////////////////////////////////////////////
     //////////////   输入参数:p_id ;DBConn 连接数据;//////////////////////////////////////////
     //////////////////////////////////////////////////////////////////////////////////////////
     public DataSet Showimage(int p_g3e_fno,int p_g3e_fid,string DBConn)
     {
      lfile.Msg("YCJXT.Showimage:","Enter");
      OracleConnection MyConn = new OracleConnection(DBConn);
      OracleCommand MyCommand = new OracleCommand();
      MyCommand.Connection = MyConn;
      MyCommand.CommandText = "YCJXT.search_ycjxt";
      MyCommand.CommandType = CommandType.StoredProcedure;
      MyCommand.Parameters.Add(new OracleParameter("p_g3efno",OracleType.Number)).Value = p_g3e_fno;
      MyCommand.Parameters.Add(new OracleParameter("p_g3efid",OracleType.Number)).Value = p_g3e_fid;
      MyCommand.Parameters.Add(new OracleParameter("SeachCur",OracleType.Cursor)).Direction = ParameterDirection.Output;
      MyConn.Open();
      DataSet ds;
      ds = new DataSet();
      OracleDataAdapter OraAdapter;
      OraAdapter = new OracleDataAdapter(MyCommand);
      try
      {
        OraAdapter.Fill(ds);
        return ds;
     }
     // catch (Exception ex)
     // {
     //  lfile.Msg("Showimage Failure:",ex.Message);
     // }
      finally
      {
      MyConn.Close();
      lfile.Msg("YCJXT.Showimage:","End");
      }
     }

     /////////////////////////////////////////////////////////////////////////////////////
     ///////// 显示一次接线图 //////////////////////////////////////////////////////////////
     //////////////   输入参数:p_id ;DBConn 连接数据;//////////////////////////////////////////
     //////////////////////////////////////////////////////////////////////////////////////////
     public OracleDataReader ShowJXT(int p_id,string DBConn)
     {
        lfile.Msg("YCJXT.ShowJXT:","Enter");
 OracleConnection MyConn = new OracleConnection(DBConn);
 OracleCommand MyCommand = new OracleCommand();
 MyCommand.Connection = MyConn;
 MyCommand.CommandText = "YCJXT.search_filter_ycjxt";
 MyCommand.CommandType = CommandType.StoredProcedure;
 MyCommand.Parameters.Add(new OracleParameter("P_ID",OracleType.Number)).Value = p_id;
 MyCommand.Parameters.Add(new OracleParameter("SearchfilterCur",OracleType.Cursor)).Direction = ParameterDirection.Output;
        OracleDataReader OraDr;
 MyConn.Open();
 try
 {
    OraDr = MyCommand.ExecuteReader();
 //   if (OraDr.Read())
      return OraDr;
 
 }
 //catch(Exception ex)
 //{
 //   lfile.Msg("YCJXT.ShowJXT:",ex.Message);
   
 //}
 finally
 { 
   //  OraDr.Close();
     MyConn.Close();
    lfile.Msg("YCJXT.ShowJXT:","End");
 }
      //
     }
   }
}

Oracle使用存储过程下载Blob大对象

1. 创建存储过程 copy_blob_data_to_fileCREATE OR REPLACE PROCEDURE copy_blob_data_to_file( p_blob_id INTEGE...
  • u014199860
  • u014199860
  • 2015-07-07 10:17:13
  • 628

使用存储过程向Oracle数据库中插入BLOB文件

这段时间在做扬子石化的GIS开发,一直以来遇到一个最大的问题就是上传大文件类型(BLOB)超过32K就会从数据库中报出错来,十分头痛,前天在网上看到一个解决方案,是用SQL语句在界面中先插入一个emp...
  • jack_zy1981
  • jack_zy1981
  • 2008-03-26 19:56:00
  • 3476

通过oracle存储过程的Blob参数上传文件

前两天朋友找我做一个上传程序,非常简单的一个东西!我没有用过java,现学现卖,反正也是很简单。 不过,中间除了碰到一个东西,在网上查了半天,也没有找到解决的办法!后来才好不容易成功了。我把这个记录下...
  • xwjbs
  • xwjbs
  • 2007-12-22 21:35:00
  • 2599

C#,oracle数据库操作基类,blob读写,返回数据集,调用存储过程

using System;using System.Data;using System.Windows.Forms;using System.Data.OracleClient;using Syste...
  • yanleigis
  • yanleigis
  • 2007-04-02 15:11:00
  • 3487

在。NET中应用Oracle存储过程操作BLOB

SQl部分:--一次接线图drop sequence YW_YCJXT_IDcreate sequence YW_YCJXT_IDstart with 1increment by 1;drop tab...
  • gengwei80
  • gengwei80
  • 2004-08-11 17:35:00
  • 2387

在Oracle中存取BLOB对象实现文件的上传和下载

最近做一个J2EE项目,需要在JSP页面实现对文件的上传和下载。很早以前就知道JDBC支持大对象(LOB)的存取,以为很容易,做起来才发现问题多多,读了一大堆文章,反而没有什么头绪了。正如一位网友文章...
  • chensheng913
  • chensheng913
  • 2005-12-13 13:56:00
  • 9284

.net 调用Oracle存储过程

在.NET中调用Oracle9i存储过程经验总结(1)  在.NET中调用Oracle9i存储过程可以用多个数据提供者,比如OralceClient,OleDb。本文将用OralceClient为例。...
  • u010678947
  • u010678947
  • 2014-03-07 12:38:04
  • 1736

对oracle中的BLOB的操作(读取,写入)

 将BLOB保存为本地文档: /// /// 读出Blob字段 /// /// sql语句,执行结果为BLOB数据 /// ...
  • ChuXiaMuXiang
  • ChuXiaMuXiang
  • 2009-12-21 11:05:00
  • 2662

读取oracle blob字段内容并以文件形式下载

这个也是从网找的代码攒的,做个记录,以后可能会用得着。表里即存了文件的内容,也存了文件的类型。String xh = request.getParameter("xh")==null?"":reque...
  • hereyouare
  • hereyouare
  • 2010-11-12 15:15:00
  • 2654

利用ORACLE JAVA存储过程实现BLOB图片导出

原文地址:http://fengwen.iteye.com/blog/580323 问题:一个XX收单系统,需要的交易情况统计来自另外一套系统,以图片方式进行存储和导出查看.图   片以BLOB...
  • xing_sky
  • xing_sky
  • 2011-11-29 23:45:34
  • 4571
收藏助手
不良信息举报
您举报文章:在。NET中应用Oracle存储过程操作BLOB
举报原因:
原因补充:

(最多只允许输入30个字)