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;

海外公司注册、海外银行开户、跨境平台代入驻、VAT、EPR等知识和在线办理：https://www.xlkjsw.com

原标题：sql: Oracle 11g create procedure

关键词：sql