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;
sql: Oracle 11g create procedure
最新推荐文章于 2021-04-09 05:18:36 发布