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 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;CREATE OR REPLACE PROCEDURE procInsertBookKindOut --添加返回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 BookKindList fm2 where fm2.BookKindName=temTypeName);--判斷是否存SELECT count(*) INTO ncount FROM BookKindList where BookKindName=temTypeName;if ncount<=0 thenbeginINSERT 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;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 procInsertBookKindOut;--测试 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);procInsertBookKindOut(nam,par ,mid);if mid>0 thendbms_output.put_line('添加成功!输出参数:'||mid);elsedbms_output.put_line('存在相同的记录,添加不成功!输出参数:'||mid);end if;end; csharp 调用：
////// 追加记录 /// /// ///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; } /// /// 追加记录返回 /// /// /// ///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; }
////// /// /// /// 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"); } } ////// /// /// /// 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"); } }