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
 

分类:  ORACLE,  C#.NET

绿色通道：  好文要顶  关注我  收藏该文 与我联系 

DotNet编程
关注 - 2
粉丝 - 45

+加关注

2

0

(请您对文章做出评价)

« 上一篇： Oracle 游标