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个带参数的存储过程.
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个带参数的存储过程.
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 ;
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里面的值可以参数来代替那种类型,然后在程序对里面的参数进行赋值.
(
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 ;
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;