在Oracle这样的存储过程怎么写?

[cheng525jj]

insert into rooms(RoomRkey,roomlevel,total,roomprice,mansionrkey)
values('101','二等房','6','800','001')

select * from rooms where roomrkey = '101'

delete rooms where roomrkey = '101'

update rooms set roomlevel = '一等房',roomprice = '1200' where roomrkey = '101'
本人对Oracel的刚入门,请高手赐教
分别把上面4个SQL语句改为4个带参数的存储过程.

[whodxaje]

CREATE OR REPLACE PROCEDURE PROC_TEST
BEGIN
insert into rooms(RoomRkey,roomlevel,total,roomprice,mansionrkey)
values('101','二等房','6','800','001') ;

--select * from rooms where roomrkey = '101' 有啥用

delete rooms where roomrkey = '101'；

update rooms set roomlevel = '一等房',roomprice = '1200' where roomrkey = '101'；
END ;

[hongqi162]

create or replace package sp_rooms
is
type rooms_row is ref cursor return rooms%rowtype;
procedure InsertRoom(ARoomRkey in Rooms.RoomRkey%Type,Aroomlevel in Rooms.roomlevel%Type,Atotal in Rooms.total%Type,Aroomprice in Rooms.roomprice%Type,Amansionrkey in Rooms.mansionrkey%Type);
procedure DeleteRoom(ARoomRkey in Rooms.RoomRkey%Type);
procedure GetRoomByID(ARoomRkey in Rooms.RoomRkey%Type,ResultData out sp_Rooms.rooms_row);
procedure UpdateRoom(ARoomRkey in Rooms.RoomRkey%Type,Aroomlevel in Rooms.roomlevel%Type,Atotal in Rooms.total%Type,Aroomprice in Rooms.roomprice%Type,Amansionrkey in Rooms.mansionrkey%Type);

end sp_rooms;

create or replace package body sp_rooms
is

procedure InsertRoom(ARoomRkey in Rooms.RoomRkey%Type,Aroomlevel in Rooms.roomlevel%Type,Atotal in Rooms.total%Type,Aroomprice in Rooms.roomprice%Type,Amansionrkey in Rooms.mansionrkey%Type)
is
begin
insert into InsertRoom values(ARoomRkey,Aroomlevel,Atotal,Aroomprice,Amansionrkey);
end InsertRoom;
procedure DeleteRoom(ARoomRkey in Rooms.RoomRkey%Type)
is
begin
delete from Rooms where RoomRKey=ARoomRKey;
end DeleteRoos;
procedure GetRoomByID(ARoomRkey in Rooms.RoomRkey%Type,ResultData out sp_Rooms.rooms_row)
is
begin
open ResultData for
select * from Rooms where RoomRKey=ARoomRKey;
end GetRoomByID;
procedure UpdateRoom(ARoomRkey in Rooms.RoomRkey%Type,Aroomlevel in Rooms.roomlevel%Type,Atotal in Rooms.total%Type,Aroomprice in Rooms.roomprice%Type,Amansionrkey in Rooms.mansionrkey%Type)
is
begin
update Rooms set RoomRkey=ARooms,roomlevel=Aroomlevel,total=total,roomprice=roomprice,mansionrkey=Amansionrkey
end UpdateRoom;

end sp_rooms;

[siteer0344]

www.source520.com 免费免注册80G源码书籍下载

[cheng525jj]

楼主没有明白我意思,我想要的4个通用的存储过程,这样固定没意思.
CREATE OR REPLACE PROCEDURE PROC_TEST
BEGIN
insert into rooms(RoomRkey,roomlevel,total,roomprice,mansionrkey)
values('101','二等房','6','800','001') ;
END ;
比如:values('101','二等房','6','800','001') ;
values里面的值可以参数来代替那种类型,然后在程序对里面的参数进行赋值.

[whodxaje]

CREATE OR REPLACE PROCEDURE PROC_TEST
（
al varchar2,
a2 varchar2,
a3 varchar2,
a4 varchar2,
A5 varchar2
）
BEGIN
insert into rooms(RoomRkey,roomlevel,total,roomprice,mansionrkey)
values(A1,A2,A3,A4,A5) ;
END ;

[cheng525jj]

楼主好像有错误啊!
难道真的没有人写过这样四个通用的存储过程吗?