你的位置:首页 > 数据库

[数据库]csharp: Oracle Stored Procedure DAL using ODP.NET


Oracle sql: 

--书分类目录kind -- Geovin Du create table BookKindList(	BookKindID INT  PRIMARY KEY,	BookKindName nvarchar2(500) not null,	BookKindParent INT null,	BookKindCode varchar(100)  ---編號);--序列创建 drop SEQUENCE BookKindList_SEQ;CREATE SEQUENCE BookKindList_SEQINCREMENT BY 1   -- 每次加几个START WITH 1   -- 从1开始计数NOMAXVALUE    -- 不设置最大值NOCYCLE      -- 一直累加,不循环NOCACHE;      --设置缓存cache个序列,如果系统down掉了或者其它情况将会导致序列不连续,也可以设置为---------NOCACHE--自增长触发器 drop trigger BookKindList_ID_AUTO; create or replace trigger BookKindList_ID_AUTO before insert on BookKindList  --BookKindList 是表名 for each rowdeclare nextid number;begin IF :new.BookKindID IS NULL or :new.BookKindID=0 THEN --BookKindID是列名  select BookKindList_SEQ.Nextval --BookKindList_SEQ正是刚才创建的  into nextid  from dual;  :new.BookKindID:=nextid; end if;end;  -- 添加drop PROCEDURE proc_Insert_BookKindList;CREATE OR REPLACE PROCEDURE proc_Insert_BookKindList(temTypeName nvarchar2,temParent number)ASncount number;begin--SELECT COUNT (*) INTO ncount FROM BookKindList fm1 where EXISTS (SELECT BookKindName from BookKindList fm2 where fm2.BookKindName=temTypeName);--判斷是否存SELECT count(*) INTO ncount FROM BookKindList where BookKindName=temTypeName;if ncount<=0 thenbeginINSERT INTO BookKindList (BookKindName,BookKindParent) VALUES(temTypeName,temParent);commit;end;elsebegin SELECT BookKindID INTO ncount FROM BookKindList where BookKindName=temTypeName; dbms_output.put_line('存在相同的记录,添加不成功!'||ncount);end;end if;Exception  When others then   dbms_output.put_line('存在问题,添加不成功!'||ncount);    Rollback;end proc_Insert_BookKindList; --测试 oracle 11g 涂聚文 20150526exec proc_Insert_BookKindList ('油彩画',3); drop PROCEDURE proc_Insert_BookKindOut;drop PROCEDURE procInsertBookKindOut; -- 添加有返回值CREATE OR REPLACE PROCEDURE proc_Insert_BookKindOut --添加返回ID(temTypeName nvarchar2,temParent int,temId out int )ASncount number;reid number;begin--SELECT COUNT (*) INTO ncount FROM BookKindList fm1 where EXISTS (SELECT BookKindName from BookKindList fm2 where fm2.BookKindName=temTypeName);--判斷是否存SELECT count(*) INTO ncount FROM BookKindList where BookKindName=temTypeName;if ncount<=0 thenbegin--INSERT INTO BookKindList (BookKindID,BookKindName,BookKindParent) VALUES(BookKindList_SEQ.nextval,temTypeName,temParent);INSERT INTO BookKindList (BookKindName,BookKindParent) VALUES(temTypeName,temParent);select BookKindList_SEQ.currval into reid from dual;temId:=reid;dbms_output.put_line('添加成功!'||temId);commit;end;elsebegin SELECT BookKindID INTO ncount FROM BookKindList where BookKindName=temTypeName; dbms_output.put_line('存在相同的记录,添加不成功!'||ncount); temId:=0;end;end if;Exception  When others then  begin   dbms_output.put_line('存在问题,添加不成功!'||ncount);   temId:=0;    Rollback;  end;end proc_Insert_BookKindOut;--测试 oracle 11g 涂聚文 20150526declaremid number:=0;nam nvarchar2(100):='黑白画';par number:=3;begin--proc_Insert_BookKindOut(nam in nvarchar2,par in int,mid in out int);proc_Insert_BookKindOut(nam,par ,mid);if mid>0 thendbms_output.put_line('添加成功!输出参数:'||mid);elsedbms_output.put_line('存在相同的记录,添加不成功!输出参数:'||mid);end if;end;--修改CREATE OR REPLACE PROCEDURE procUpdateBookKindList (p_id IN INT,--BookKindList.BookKindID%TYPE,  p_name IN nvarchar2,--BookKindList.BookKindName%TYPE, p_parent IN INT,--BookKindList.BookKindParent%TYPE,p_code IN varchar--BookKindList.BookKindCode%TYPE) ISncount number; BEGIN SELECT count(*) INTO ncount FROM BookKindList where BookKindName=p_name;if ncount<=0 thenbeginUPDATE BookKindList SET BookKindName=p_name,BookKindParent=p_parent,BookKindCode=p_code WHERE BookKindID=p_id; COMMIT; end;elsebegin SELECT BookKindID INTO ncount FROM BookKindList where BookKindName=p_name; dbms_output.put_line('存在相同的记录,修改不成功!'||ncount); end; end if;END procUpdateBookKindList; --测试beginprocUpdateBookKindList(8,'哲学',1,'Geovin Du'); end;--删除CREATE OR REPLACE PROCEDURE procDeleteBookKindList(p_BookKindID IN BookKindList.BookKindID%TYPE)ISBEGIN DELETE BookKindList where BookKindID = p_BookKindID; COMMIT;END;---一条记录--创建包:create or replace package pack_BookKindId is    type cur_BookKindId is ref cursor; end pack_BookKindId; --创建存储过程create or replace procedure procSelectBookKindList(p_id in int,p_cur out pack_BookKindId.cur_BookKindId) is     v_sql varchar2(400);begin     if p_id = 0 then  --0 查询所有     open p_cur for select * from BookKindList;    else       v_sql := 'select * from BookKindList where BookKindID =: p_id';      open p_cur for v_sql using p_id;     end if; end procSelectBookKindList;--创建包以游标的形式返回BookKindList表的所有记录结果集drop package pkg_Select_BookKindListAll;drop procedure proc_Select_BookKindListAll;create or replace package pkgSelectBookKindListAll is-- Author : geovindu type mycur is ref cursor;  procedure procSelectBookKindListAll(cur_return out mycur);end pkgSelectBookKindListAll;create or replace package body pkgSelectBookKindListAll is -- Function and procedure implementations procedure procSelectBookKindListAll(cur_return out mycur) is   begin  open cur_return for select * from BookKindList;   end procSelectBookKindListAll;end pkgSelectBookKindListAll;-- 测试包和存储过程查询表中所有内容declare --定义游标类型的变量cur_return pkgSelectBookKindListAll.mycur;--定义行类型pdtrow BookKindList%rowtype;begin --执行存储过程 pkgSelectBookKindListAll.procSelectBookKindListAll(cur_return); --遍历游标中的数据    LOOP     --取当前行数据存入pdtrow      FETCH cur_return INTO pdtrow;      --如果未获取数据就结束循环      EXIT WHEN cur_return%NOTFOUND;      --输出获取到的数据      DBMS_OUTPUT.PUT_LINE (pdtrow.BookKindID||','||pdtrow.BookKindName);     END LOOP;     CLOSE cur_return;end;

  csharp code:

/// <summary>  /// 20160918 涂聚文  /// Geovin Du  /// </summary>  public class BookKindListDAL : IBookKindList  {    //private static string connectionString =@"DATA SOURCE=oracle11g;USER ID=geovin;password=geovindu;";    ///<summary>    /// 追加记录    ///</summary>    ///<param name="BookKindListInfo"></param>    ///<returns></returns>    public int InsertBookKindList(BookKindListInfo bookKindList)    {      int ret = 0;      try      {        OracleParameter[] par = new OracleParameter[]{				new OracleParameter("temTypeName",OracleDbType.NVarchar2,1000),				new OracleParameter("temParent",OracleDbType.Int32,4),				};        par[0].Value = bookKindList.BookKindName;        par[0].Direction = ParameterDirection.Input;        par[1].Value = bookKindList.BookKindParent;        par[1].Direction = ParameterDirection.Input;        ret = OracleHelper.ExecuteSql("proc_Insert_BookKindList", CommandType.StoredProcedure, par);      }      catch (OracleException ex)      {        throw ex;      }      return ret;    }    /// <summary>    /// 追加记录返回    /// </summary>    /// <param name="authorList"></param>    /// <param name="authorID"></param>    /// <returns></returns>    public int InsertBookKindOutput(BookKindListInfo bookKindList, out int bookKindLID)    {      bookKindLID = 0;      int ret = 0;      try      {        OracleParameter[] par = new OracleParameter[]{				new OracleParameter("temTypeName",OracleDbType.NVarchar2,1000),        new OracleParameter("temParent",OracleDbType.Int32,4),        new OracleParameter("temId",OracleDbType.Int32,4),				};        par[0].Value = bookKindList.BookKindName;        par[0].Direction = ParameterDirection.Input;        par[1].Value = bookKindList.BookKindParent;        par[1].Direction = ParameterDirection.Input;        par[2].Direction = ParameterDirection.Output;        ret = OracleHelper.ExecuteSql("proc_Insert_BookKindOut", CommandType.StoredProcedure, par);        if (ret > 0)        {          bookKindLID =int.Parse(par[2].Value.ToString());        }      }      catch (OracleException ex)      {        throw ex;      }      return ret;    }    ///<summary>    ///修改记录    ///涂聚文 20160920    ///</summary>    ///<param name="BookKindListInfo"></param>    ///<returns></returns>    public int UpdateBookKindList(BookKindListInfo bookKindList)    {      int ret = 0;      try      {        OracleParameter[] par = new OracleParameter[]{				new OracleParameter("p_id",OracleDbType.Int32,4),				new OracleParameter("p_name",OracleDbType.NVarchar2,1000),				new OracleParameter("p_parent",OracleDbType.Int32,4),        new OracleParameter("p_code",OracleDbType.Varchar2,1000),				};        par[0].Value = bookKindList.BookKindID;        par[0].Direction = ParameterDirection.Input;        par[1].Value = bookKindList.BookKindName;        par[1].Direction = ParameterDirection.Input;        par[2].Value = bookKindList.BookKindParent;        par[2].Direction = ParameterDirection.Input;        par[3].Value = bookKindList.BookKindCode;        par[3].Direction = ParameterDirection.Input;        ret = OracleHelper.ExecuteSql("procUpdateBookKindList", CommandType.StoredProcedure, par);        // ret = 1;      }      catch (OracleException ex)      {        throw ex;      }      return ret;    }    ///<summary>    /// 删除记录    ///</summary>    ///<param name="bookKindIDInfo"></param>    ///<returns></returns>    public bool DeleteBookKindList(int bookKindID)    {      bool ret = false;      try      {        OracleParameter par = new OracleParameter("p_BookKindID", bookKindID);        par.Direction = ParameterDirection.Input;        int temp = 0;        temp = OracleHelper.ExecuteSql("procDeleteBookKindList", CommandType.StoredProcedure, par);        if (temp != 0)        {          ret = true;        }      }      catch (OracleException ex)      {        throw ex;      }      return ret;    }    ///<summary>    /// 查询记录    ///</summary>    ///<param name="bookKindIDInfo"></param>    ///<returns></returns>    public BookKindListInfo SelectBookKindList(int bookKindID)    {      BookKindListInfo bookKindList = null;      try      {        OracleParameter[] par = new OracleParameter[]{        new OracleParameter("p_id",OracleDbType.Int32,4),        new OracleParameter("p_cur",OracleDbType.RefCursor),              };        par[0].Value = bookKindID;        par[0].Direction = ParameterDirection.Input;        par[1].Direction = ParameterDirection.Output;        using (OracleDataReader reader = OracleHelper.GetReader("procSelectBookKindList", CommandType.StoredProcedure, par)) //proc_Select_BookKindList 提示名称过长Oracle        {          if (reader.Read())          {            bookKindList = new BookKindListInfo();            bookKindList.BookKindID = (!object.Equals(reader["BookKindID"], null)) ? (decimal)reader["BookKindID"] : 0;            bookKindList.BookKindName = (!object.Equals(reader["BookKindName"], null)) ? (string)reader["BookKindName"] : "";            bookKindList.BookKindParent = (!object.Equals(reader["BookKindParent"], null)) ? (decimal)reader["BookKindParent"] : 0;          }        }      }      catch (OracleException ex)      {        throw ex;      }      return bookKindList;    }    ///<summary>    /// 查询所有记录    ///</summary>    ///<returns></returns>    public List<BookKindListInfo> SelectBookKindListAll()    {      List<BookKindListInfo> list = new List<BookKindListInfo>();      BookKindListInfo bookKindList = null;      try      {        //定义参数,注意参数名必须与存储过程定义时一致,且类型为OracleType.Cursor        OracleParameter cur_set = new OracleParameter("cur_return", OracleDbType.RefCursor);        //设置参数为输出类型        cur_set.Direction = ParameterDirection.Output;        //        //OracleHelper.ExecuteReader(connectionString, CommandType.StoredProcedure, "pkg_Select_BookKindListAll.proc_Select_BookKindListAll", cur_set)        using (OracleDataReader reader = OracleHelper.GetReader("pkg_Select_BookKindListAll.proc_Select_BookKindListAll", CommandType.StoredProcedure, cur_set))        {          while (reader.Read())          {            bookKindList = new BookKindListInfo();            string s = reader["BookKindID"].ToString();            bookKindList.BookKindID = (!object.Equals(reader["BookKindID"], null)) ? (decimal)reader["BookKindID"] : 0;            bookKindList.BookKindName = (!object.Equals(reader["BookKindName"], null)) ? (string)reader["BookKindName"] : "";            bookKindList.BookKindParent = (!object.Equals(reader["BookKindParent"], null)) ? (decimal)reader["BookKindParent"] : 0;            list.Add(bookKindList);          }        }      }      catch (OracleException ex)      {        throw ex;      }      return list;    }    ///<summary>    /// 查询所有记录    ///</summary>    ///<returns></returns>    public DataTable SelectBookKindListDataTableAll()    {      DataTable dt = new DataTable();      try      {        //定义参数,注意参数名必须与存储过程定义时一致,且类型为OracleType.Cursor        OracleParameter cur_set = new OracleParameter("cur_return", OracleDbType.RefCursor);        //设置参数为输出类型        cur_set.Direction = ParameterDirection.Output;        //添加参数        //comm.Parameters.Add(cur_set);        using (DataTable reader = OracleHelper.GetTable("pkgSelectBookKindListAll.procSelectBookKindListAll", CommandType.StoredProcedure, cur_set))        {          dt = reader;        }      }      catch (OracleException ex)      {        throw ex;      }      return dt;    }    /// <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", OracleDbType.Varchar2).Value = psql;      cmd.Parameters.Add("prcount", OracleDbType.Int32).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", OracleDbType.Int32).Value = pindex - 1;      }      else      {        cmd.Parameters.Add("pindex", OracleDbType.Int32).Value = pindex;      }      cmd.Parameters.Add("psql", OracleDbType.Varchar2).Value = psql;      cmd.Parameters.Add("psize", OracleDbType.Int32).Value = psize;      cmd.Parameters.Add("v_cur", OracleDbType.RefCursor).Direction = ParameterDirection.Output;      cmd.Parameters.Add("pcount", OracleDbType.Int32).Direction = ParameterDirection.Output;      dr.SelectCommand = cmd;      try      {        DataSet 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;    }  }