--书分类目录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;

/// <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 = http://www.mamicode.com/bookKindList.BookKindName;"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 = http://www.mamicode.com/bookKindList.BookKindName;"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 = http://www.mamicode.com/bookKindList.BookKindID;"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 = http://www.mamicode.com/bookKindID;"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 = http://www.mamicode.com/psql;"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 = http://www.mamicode.com/pindex - 1;"pindex", OracleDbType.Int32).Value = http://www.mamicode.com/pindex;"psql", OracleDbType.Varchar2).Value = http://www.mamicode.com/psql;"psize", OracleDbType.Int32).Value = http://www.mamicode.com/psize;"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;        }    }