C#对Oracle存储过程的调用

C#对Oracle存储过程的调用

-- 创建表
declare  num  number ; 
begin  
select   count ( 1 )  into  num  from  user_tables  where  table_name = ' SRCT ' ;    -- 判断当前要创建的表在数据库中是否存在.
if  num > 0  
then   execute  immediate  ' drop table  ' || ' SRCT ' ;  -- 表名要大写
end   if ; 
execute  immediate  ' CREATE TABLE SRCT
(  
  SN  char(11), 
  XM  varchar2(30),  --姓名 
  KSCJ  number(3),   --考试成绩
  KSRQ  Date         --考试日期
      
) ' ; 
end ;
/
commit ;
/

-- 注:上面的表名要大写.

-- 插入数据
declare
maxrecords constant  int : = 50 ;
i  int : = 1 ;
begin
for  i  in   1 ..maxrecords
loop
insert   into  SRCT(SN,XM,KSCJ,KSRQ) values (i, ' frj ' || i,i + 10 ,sysdate);
end  loop
-- dbms_output.put_line('成功录入数据!');
commit ;
end ;
/
-- 查询数据,检查数据插入操作是否成功.
SELECT   *   FROM  SRCT  WHERE  ROWNUM < 3 ;
/

-- 检查存储过程是否存在
declare  
num  number ;
msg  varchar2 ( 30 ): =   ' 数据库中不存在该存储过程 ' ;
begin
   select     count ( 1 )  into  num    
   from    user_objects     
   where    object_type    =   ' PROCEDURE '    
   and     object_name = ' WRITE_SRC '   ;
  
   if  num > 0  
   then  
      msg: =   ' 该存储过程已经存在 ' ;
   end   if ;
  dbms_output.put_line(msg); 
end  ;
/

-- 创建存储过程
CREATE   OR   REPLACE   Procedure  
WRITE_SRC( M_SN  in   char   , M_XM  in    varchar2 ,M_KSCJ  in   integer ,
M_KSRQ  in  Date,RES out  integer ,ERR out
varchar2 )
as
V_COUNT  number : = 0 ;
   Begin
  
  RES: =- 2 ;
  ERR: = ' 数据库中不存在该纪录,更新失败. ' ;
   select   count (SN)  INTO  V_COUNT 
   FROM  SRCT 
   Where   SN = M_SN  AND  XM = M_XM;
  
   IF  V_COUNT > 0   THEN  
     Update  SRCT
     Set  KSCJ = M_KSCJ,KSRQ = M_KSRQ
     Where   SN = M_SN  AND  XM = M_XM;
     Commit ;
    RES: = 1 ;
    ERR: = ' 更新成功! ' ;
    return ;
   END   IF ;
  exception
     when  others  then
     RES: =- 1 ;
     ERR: = ' 更新失败 ' ;
    return  ;
End   ;
/

commit ;
/

-- 调用存储过程
declare  
res  int ;
err  Varchar2 ( 80 );
Begin
res: = ' 3 ' ;
err: = ' 更新成功 ' ;
WRITE_SRC( ' 1 ' , ' frj1 ' , 300 ,sysdate,res,err);
COMMIT ;
dbms_output.put_line(res);
dbms_output.put_line(err); 
End ;
/

select  sn,xm,kscj  from  srct  WHERE  SN = ' 1 '   AND  XM = ' frj1 ' ;
/

-- 注:易犯错误
-- 以上语句均在"SQL*Plus 工作单"上运行;

   1 .每一个小单元的语句后要加 ' ; ' 号;
   2 .不能将字符串赋值的单引号写成双引号;
    如: err: = ' 更新成功! ' ; 不能写成 err: = "更新成功!";
    以上错误系统将提示:"警告: 创建的过程带有编译错误。"
   3 .存储过程传递与赋值的参数名称,个数,类型(字段类型，返回的类型( in/ out))要与调用的存储过程以及该存储过程
    所访问的表中相应的字段类型严格对应.
    还有一些约定的写法也需遵守.
    如:其中的" M_KSCJ "对应表中的" KSCJ "字段, 应写成(M_KSCJ  in   integer )不能写成( M_KSCJ  in    number ( 3 ));
    "M_SN"对应表中的 "SN" 字段,应写成 (M_SN  in   char ) 不能写成 (M_SN  in   Varchar2 )
   4 .在c#中进行调用时,还要注意它的输入 / 输出类型,如上例中的" out  integer  res "  为输出类型,应将其
      OracleParameter []  parm  =  new OracleParameter [ 1 ] ;
      parm [ 0 ]   =  new OracleParameter("RES", OracleType.Int16   );
      parm [ 0 ] .Direction  =  ParameterDirection.Output  ;  -- 将其设为输出类型;
      具体调用方法将在稍后进行介绍;
   5 .定义存储过程时,其参数名称最好不要与字段名称同名(不区分大小写);
     如上面的存储过程建议不要写成:
     WRITE_SRC( SN  in   char   , XM  in    varchar2 ,KSCJ  in   integer ,
                KSRQ  in  Date,RES out  integer ,ERR out  varchar2 )

// --在c#中的调用
 
 
    public   int  upInfo( string  m_sn, string  m_sxm, int  m_ikscj,DateTime m_dksrq,  out   int  m_ires,  out   string  m_serr)
         {
            string ConnStr=GetConnStr();
            OracleCommand cmd = new OracleCommand();
            OracleConnection conn = new OracleConnection(ConnStr);
            int rows = 0;
            mres = -110;
            merr = "";
            try
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "WRITE_SRC";
                OracleParameter[] parm = new OracleParameter[6];
                //in
                parm[0] = new OracleParameter("M_SN", OracleType.Char, 11);   --与SQL区别,sql存储过程需要在定义与此处,在其参数前加"@"符号;
                parm[1] = new OracleParameter("M_XM", OracleType.VarChar, 2);
                parm[2] = new OracleParameter("M_KSCJ", OracleType.Number, 3);
                parm[3] = new OracleParameter("M_KSRQ", OracleType.DateTime , 8);
                //out
                parm[4] = new OracleParameter("RES", OracleType.Int16);
                parm[5] = new OracleParameter("ERR", OracleType.VarChar, 50);

                //指明参数是输入还是输出型
                for (int i = 0; i < parm.Length-2; i++)
                {
                    parm[i].Direction = ParameterDirection.Input;
                }
                parm[4].Direction = ParameterDirection.Output;
                parm[5].Direction = ParameterDirection.Output;

              
                //给参数赋值
                parm[0].Value = m_sn;
                parm[1].Value = m_sxm;
                parm[2].Value = m_ikscj;
                parm[3].Value = OracleDateTime.Parse(m_dksrq.ToShortDateString());
                --直接用update语句更新时,需要采用下面的日期格式.
                -- string msksrq = mksrq.Day.ToString() + "-" + mksrq.Month.ToString() + "月" + " -" + mksrq.Year.ToString().Substring(2, 2);
          
                //传递参数给Oracle命令
                for (int i = 0; i < parm.Length; i++)
                {
                    cmd.Parameters.Add(parm[i]);
                }

                //打开连接
                if (conn.State != ConnectionState.Open)
                    conn.Open();

                cmd.Connection = conn;
                rows = cmd.ExecuteNonQuery();

                //取出返回值
                m_ires = Convert.ToInt16(parm[4].Value);//res
                m_serr = parm[5].Value.ToString();//err

            }
            catch (Exception er)
            {
                merr = System.Environment.NewLine + "res:" + m_ires.ToString() + "err:" + er.ToString();
                MrfuWriteEventLog.C_WriterEventLog.WriteEventLogAppend("UploadDriInfo: mres=" + m_ires.ToString() + "merr:" + er.ToString());
            }
            finally
            {
                //关闭连接,释放空间.
                if (conn.State == ConnectionState.Open)
                    conn.Close();
                conn.Dispose();
                cmd.Parameters.Clear();
                cmd.Dispose();
            }

            return rows;
        }

-- 按时间段分页显示
 
select  sn,xm,kscj,ksrq  from  SRCT  
where  ksrq  between   to_date( ' 2003-01-01 ' , ' yyyy-mm-dd ' )    and    to_date( ' 2007-06-28 ' , ' yyyy-mm-dd ' )  order   by  ksrq;
/


select   *   from  
(  select  b. * ,rownum row_num  from
    (
      select   sn,xm,kscj,ksrq  from  SRCT  c
      where  ksrq  between   to_date( ' 2003-01-01 ' , ' yyyy-mm-dd ' )    and    to_date( ' 2007-06-28 ' , ' yyyy-mm-dd ' )  order   by  c.sn 
     )b
)a  where  a.row_num  between   1   and   10 ; 

/
-- 注: oracle的rownum是在提取记录时就已经生成，它先于排序操作，所以必须使用子查询先排序.

-- ==转oracle分页存储过程==
CREATE   OR   REPLACE   PACKAGE DotNet   is

  TYPE type_cur  IS  REF  CURSOR ;      -- 定义游标变量用于返回记录集
   PROCEDURE  DotNetPagination
  (
  Pindex  in   number ,                 -- 分页索引
  Psql  in   varchar2 ,                 -- 产生dataset的sql语句
  Psize  in   number ,                  -- 页面大小
  Pcount out  number ,                -- 返回分页总数
  v_cur out type_cur                -- 返回当前页数据记录
  );
   procedure  DotNetPageRecordsCount
  (
  Psqlcount  in   varchar2 ,            -- 产生dataset的sql语句
  Prcount   out  number               -- 返回记录总数
  );
end  DotNet;
/
CREATE   OR   REPLACE   PACKAGE BODY DotNet   is
  -- ***************************************************************************************
   PROCEDURE  DotNetPagination
  (
  Pindex  in   number ,
  Psql  in   varchar2 ,
  Psize  in   number ,
  Pcount out  number ,
  v_cur out type_cur
  )
   AS
  v_sql  VARCHAR2 ( 1000 );
  v_count  number ;
  v_Plow  number ;
  v_Phei  number ;
   Begin
   -- ----------------------------------------------------------取分页总数
  v_sql : =   ' select count(*) from ( '   ||  Psql  ||   ' ) ' ;
   execute  immediate v_sql  into  v_count;
  Pcount : =  ceil(v_count / Psize);
   -- ----------------------------------------------------------显示任意页内容
  v_Phei : =  Pindex  *  Psize  +  Psize;
  v_Plow : =  v_Phei  -  Psize  +   1 ;
   -- Psql := 'select rownum rn,t.* from cd_ssxl t' ;            --要求必须包含rownum字段
  v_sql : =   ' select * from ( '   ||  Psql  ||   ' ) where rn between  '   ||  v_Plow  ||   '  and  '   ||  v_Phei ;
   open  v_cur  for  v_sql;
   End  DotNetPagination;
  -- **************************************************************************************
   procedure  DotNetPageRecordsCount
  (
  Psqlcount  in   varchar2 ,
  Prcount   out  number
  )
   as
  v_sql  varchar2 ( 1000 );
  v_prcount  number ;
   begin
  v_sql : =   ' select count(*) from ( '   ||  Psqlcount  ||   ' ) ' ;
   execute  immediate v_sql  into  v_prcount;
  Prcount : =  v_prcount;                   -- 返回记录总数
   end  DotNetPageRecordsCount;
  -- **************************************************************************************
end  DotNet;

/

// ==使用示例==
        /**/ /// <summary>
       /// 填充dataSet数据集－Oracle库
       /// </summary>
       /// <param name="pindex">当前页</param>
       /// <param name="psql">执行查询的SQL语句</param>
       /// <param name="psize">每页显示的记录数</param>
       /// <returns></returns>
       private   bool  gridbind( int  pindex,  string  psql,  int  psize)
      {
            OracleConnection conn = new OracleConnection();
            OracleCommand cmd = new OracleCommand();
            OracleDataAdapter dr = new OracleDataAdapter();
            conn.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
            cmd.Connection = conn;
            cmd.CommandType = CommandType.StoredProcedure;
            conn.Open();
            cmd.CommandText = "DotNet.DotNetPageRecordsCount";
            cmd.Parameters.Add("psqlcount", OracleType.VarChar).Value = psql;
            cmd.Parameters.Add("prcount", OracleType.Number).Direction = ParameterDirection.Output;
            
            cmd.ExecuteNonQuery();
            string PCount = cmd.Parameters["prcount"].Value.ToString();
            cmd.Parameters.Clear();
            cmd.CommandText = "DotNet.DotNetPagination";
            if (pindex != 0)
            {
                cmd.Parameters.Add("pindex", OracleType.Number).Value = pindex - 1;
            }
            else
            {
                cmd.Parameters.Add("pindex", OracleType.Number).Value = pindex;
            }
            cmd.Parameters.Add("psql", OracleType.VarChar).Value = psql;
            cmd.Parameters.Add("psize", OracleType.Number).Value = psize;
            cmd.Parameters.Add("v_cur", OracleType.Cursor).Direction = ParameterDirection.Output;
            cmd.Parameters.Add("pcount", OracleType.Number).Direction = ParameterDirection.Output;
            dr.SelectCommand = cmd;
            try
            {
                ds = new DataSet();
                dr.Fill(ds);
                //显示页码条的状态
                showStatus(Convert.ToInt32(cmd.Parameters["pindex"].Value) + 1,
                    Convert.ToInt32(cmd.Parameters["pcount"].Value),
                    Convert.ToInt32(PCount));
                for (int i = 0; i < ds.Tables.Count; i++)
                { //把数据行为零的表删除
                    if (ds.Tables[i].Rows.Count == 0)
                        ds.Tables.Remove(ds.Tables[i].TableName);
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                return false;
            }
 
            conn.Close();
            return true;
    }

全文SQL
 

posted on 2007-06-18 23:04 DotNet编程 阅读( ...) 评论( ...) 编辑 收藏

转载于:https://www.cnblogs.com/furenjun/archive/2007/06/18/orcaleprocedure.html