sql: Oracle 11g create procedure，oracle11g

sql: Oracle 11g create procedure，oracle11g

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;

　　csharp 調用：

///<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");
            }
        }