sql: Oracle 11g create procedure

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83

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 调用：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56

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

　　

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49

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