sql: Oracle 11g create procedure

標籤：

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;

　　

sql: Oracle 11g create procedure