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
原创
©著作权归作者所有:来自51CTO博客作者geovin的原创作品,请联系作者获取转载授权,否则将追究法律责任
提问和评论都可以,用心的回复会被更多人看到
评论
发布评论
相关文章
-
Oracle Database 11g : SQL 基础
Oracle Database 11g : SQL 基础
Oracle Database -
PL/SQL 连接Oracle 11g
pl/sql 连接oracle11g
pl/sql 连接 oracle11g -
Oracle - 11g安装
关于oracle 11g的详细安装过程
安装 oracle 11g -
Oracle 11g初学—sql基础(一)
sql语句基础。
sql 基础 语言分类