CREATE OR REPLACE PROCEDURE proc_Insert_BookKindList

(

temTypeName nvarchar2,

temParent int

)

AS

ncount 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 then

begin

INSERT INTO BookKindList (BookKindName,BookKindParent) VALUES(temTypeName,temParent);

commit;

end;

else

begin

  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 涂聚文 20150526

exec proc_Insert_BookKindList ('油彩画',3);

drop PROCEDURE proc_Insert_BookKindOut;

CREATE OR REPLACE PROCEDURE procInsertBookKindOut --添加返回ID

(

temTypeName nvarchar2,

temParent number,

temId out number

)

AS

ncount 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 then

begin

INSERT INTO BookKindList (BookKindID,BookKindName,BookKindParent) VALUES(BookKindList_SEQ.nextval,temTypeName,temParent);

select BookKindList_SEQ.currval into reid from dual;

temId:=reid;

dbms_output.put_line('添加成功!'||temId);

commit;

end;

else

begin

  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 procInsertBookKindOut;

--测试 oracle 11g 涂聚文 20150526

declare

mid  number:=0;

nam  nvarchar2(100):='黑白画';

par  number:=3;

begin

--proc_Insert_BookKindOut(nam in nvarchar2,par in int,mid in out int);

procInsertBookKindOut(nam,par ,mid);

if mid>0 then

dbms_output.put_line('添加成功!输出参数:'||mid);

else

dbms_output.put_line('存在相同的记录,添加不成功!输出参数:'||mid);

end if;

end;

///<summary>

        /// 追加记录

        ///</summary>

        ///<param name="BookKindListInfo"></param>

        ///<returns></returns>

        public int InsertBookKindList(BookKindListInfo bookKindList)

        {

            int ret = 0;

            try

            {

                OracleParameter[] par = new OracleParameter[]{

				new OracleParameter("temTypeName",OracleType.NVarChar,1000),

				new OracleParameter("temParent",OracleType.Number,4),

				};

                par[0].Value = bookKindList.BookKindName;

                par[1].Value = bookKindList.BookKindParent;

                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",OracleType.NVarChar,1000),

                new OracleParameter("temParent",OracleType.Number,4),

                new OracleParameter("temId",OracleType.Number,4),

				};

                par[0].Value = bookKindList.BookKindName;

                par[1].Value = bookKindList.BookKindParent;

                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>

        ///

        /// </summary>

        /// <param name="sender"></param>

        /// <param name="e"></param>

        private void button1_Click(object sender, EventArgs e)

        {

            BookKindListInfo bookKindListInfo = new BookKindListInfo();

            BookKindListBLL bookKindListBLL = new BookKindListBLL();

            bookKindListInfo.BookKindParent =(int)this.numericUpDownBookKindParent.Value;

            bookKindListInfo.BookKindName = this.textBoxBookKindName.Text.Trim();

            int k = 0;

            k = bookKindListBLL.InsertBookKindList(bookKindListInfo);

            if (k > 0)

            {

                MessageBox.Show("ok");

            }

            else

            {

                MessageBox.Show("no");

            }

        }

        /// <summary>

        ///

        /// </summary>

        /// <param name="sender"></param>

        /// <param name="e"></param>

        private void button2_Click(object sender, EventArgs e)

        {

            BookKindListInfo bookKindListInfo = new BookKindListInfo();

            BookKindListBLL bookKindListBLL = new BookKindListBLL();

            bookKindListInfo.BookKindParent = (int)this.numericUpDownBookKindParent.Value;

            bookKindListInfo.BookKindName = this.textBoxBookKindName.Text.Trim();

            int ou = 0;

            int k = 0;

            k = bookKindListBLL.InsertBookKindOutput(bookKindListInfo,out ou);

            if (k > 0)

            {

                MessageBox.Show("ok:id"+ou.ToString());

            }

            else

            {

                MessageBox.Show("no");

            }

        }