请帮助看看这段过程修改和删除的问题

[jianqin_cai]

表
create table CANTEEN
(
RID NUMBER not null,
CNAME VARCHAR2(100),
DISCRIPTION VARCHAR2(1000)
)

过程
create or replace procedure maintain_canteen
(
btype in number,-- 维护类型 1 增加 2 修改 3 删除
rid in number,
cname in varchar2,
discription in varchar2,
bRt_id out number, -- 1 返回当前记录id
bResult out number -- 1 正常 6 未找到相应记录 8 其它错误
) is
-- p_Result number;
begin
-- 维护Canteen表
-- 1 增加
bResult := 1;
bRt_id := Rid;
if btype in (1) then
insert into Canteen(RID,CName,Discription)
values(SQ_Canteen.NEXTVAL,cname,discription);
select max(RID) into bRt_id from Canteen;
end if;
if btype in (2) then
update Canteen t set
t.cname = cname,
t.discription = discription
where t.RId = rid;
end if;
if btype in (3) then
delete from Canteen t
where t.RId = rid;
end if;
commit;
exception
When NO_DATA_FOUND then
bResult := 6;
When OTHERS then
bResult := 8;
rollback;
end maintain_canteen;


问题描述：Oracle9.2，当btype为1时操作正确；当btype为2时出现问题是，没能修改任何记录；当btype为3时会删除所有记录。请问高手问题出现在哪里，谢谢！

[qiaozhiwei]

btype in (x) 改为 btype = x
t.RId = rid 中t.RId和rid的类型相同吗

[summer781029]

以下为个人建议：没有必要在事务执行的时候去进行判断，这样会消耗SQL的执行时间，错误代码也不需要在过程里获取，不知道你用的什么开发，如果是JAVA，那么JDBC有SQLEXCEPTION来处理例外，如果是PL/SQL那么也有处理例外的机制，所以过程和函数都应该简洁，这样提高事务的执行效率，代码也很好维护。
下面是帮你重写的PACKAGE，请参考。
-- 包头申明
CREATE OR REPLACE PACKAGE maintain_canteen
AS
PROCEDURE add(rid IN NUMBER, cname IN VARCHAR2, discription VARCHAR2);
PROCEDURE modify(rid IN NUMBER, cname IN VARCHAR2, discription VARCHAR2);
PROCEDURE delete(rid IN NUMBER);
END maintain_canteen;
/
SHOW ERRORS;
-- 包体实现
CREATE OR REPLACE PACKAGE BODY maintain_canteen
AS
PROCEDURE add(rid IN NUMBER, cname IN VARCHAR2, discription VARCHAR2)
IS
BEGIN
INSERT INTO Canteen VALUES(rid, cname, discription);
END add;

PROCEDURE modify(rid IN NUMBER, cname IN VARCHAR2, discription VARCHAR2)
IS
BEGIN
UPDATE Canteen
SET cname = cname,
discription = discription
WHERE rid = rid;
END modify;

PROCEDURE delete(rid IN NUMBER)
IS
BEGIN
DELETE FROM Canteen WHERE rid = rid;
END delete;
END maintain_canteen;
/
SHOW ERRORS;

[jianqin_cai]

谢谢楼上的两朋友，经过与tony,hansomee朋友讨论,得知Oracle9i开始后，过程中的参数名不能与表中的字段名相同，修改参数名后就改正问题了。修改后的过程为：
create or replace procedure maintain_canteen
(
btype in number,-- 维护类型 1 增加 2 修改 3 删除
r_id in number,
c_name in varchar2,
disc in varchar2,
bRt_id out number, -- 1 返回当前记录id
bResult out number -- 1 正常 6 未找到相应记录 8 其它错误
) is
-- p_Result number;
begin
-- 维护Canteen表
-- 1 增加
bResult := 1;
bRt_id := r_id;
if btype in (1) then
insert into Canteen(RID,CName,Discription)
values(SQ_Canteen.NEXTVAL,c_name,disc);
select max(RID) into bRt_id from Canteen;
end if;
if btype in (2) then
update Canteen t set
t.cname = c_name,
t.discription = disc
where t.RId = r_id;
end if;
if btype in (3) then
delete from Canteen t
where t.RId = r_id;
end if;
commit;
exception
When NO_DATA_FOUND then
bResult := 6;
When OTHERS then
bResult := 8;
rollback;
end maintain_canteen;


再次感谢楼上的朋友，结贴。

[KingSunSha]

create or replace procedure maintain_canteen (
btype in number
, rid in number
, cname in varchar2
, discription in varchar2
, brt_id out number
, bresult out number
) is
begin
bresult := 1;
brt_id := rid;

if btype = 1 then
insert into canteen
(rid, cname, discription
)
values (sq_canteen.nextval, cname, discription
)
returning rid
into brt_id;
elsif btype = 2 then
update canteen t
set t.cname = cname
, t.discription = discription
where t.rid = rid
returning rid
into brt_id;
elsif btype = 3 then
delete from canteen t
where t.rid = rid
returning rid
into brt_id;
else
raise_application_error (-20001, 'Unknown type.');
end if;
exception
when others then
bresult := 8;
raise_application_error (-20002, sqlcode || '-' || sqlerrm);
end maintain_canteen;