SQL: Oracle 11g create procedure, oracle11g

SQL: Oracle 11g create procedure, oracle11g

Create or replace procedure proc_Insert_BookKindList (temTypeName nvarchar2, temParent int) ASncount number; begin -- select count (*) INTO ncount FROM BookKindList fm1 where EXISTS (SELECT BookKindName from BookKindList fm2 where region = temTypeName); -- determine whether a shard stores 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; exist ('the same record exists and cannot be added! '| Ncount); end if; Exception When others then dbms_output.put_line (' has a problem and cannot be added! '| Ncount); Rollback; end proc_Insert_BookKindList; -- Test oracle 11g tu juwen 20150526 exec proc_Insert_BookKindList ('oil color', 3); drop PROCEDURE proc_Insert_BookKindOut; create or replace procedure procInsertBookKindOut -- add the returned ID (temTypeName nvarchar2, temParent number, temId out number) ASncount number; reid number; begin -- select count (*) INTO ncount FROM BookKindList fm1 where EXISTS (SELECT BookKindName from Bo OkKindList fm2 where exist = exist); -- determines whether the SELECT count (*) INTO ncount FROM BookKindList where BookKindName = exist; if ncount <= 0 thenbeginINSERT INTO BookKindList (BookKindID, BookKindName, bookKindParent) VALUES (BookKindList_SEQ.nextval, temTypeName, temParent); select into reid from dual; temId: = reid; dbms_output.put_line ('added successfully! '| TemId); commit; end; elsebegin SELECT BookKindID INTO ncount FROM BookKindList where BookKindName = temTypeName; dbms_output.put_line (' the same record exists and cannot be added! '| Ncount); temId: = 0; end if; Exception When others then begin dbms_output.put_line (' problem exists, adding failed! '| Ncount); temId: = 0; Rollback; end procInsertBookKindOut; -- Test oracle 11g tu juwen 20150526 declaremid number: = 0; nam nvarchar2 (100 ): = 'black and white'; par number: = 3; begin -- proc_Insert_BookKindOut (nam in nvarchar2, par in int, mid in out int); procInsertBookKindOut (nam, par, mid ); if mid> 0 thendbms_output.put_line ('added successfully! Output parameter: '| mid); elsedbms_output.put_line (' the same record exists and cannot be added! Output Parameters: '| mid); end if; end;

Csharp call:

/// <Summary> /// append record /// </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> /// return the append record /// </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");            }        }