pl sql needed for cursor and logic?

Question

create table MEMBER(
Member_ID NUMBER PRIMARY KEY,
First_Name VARCHAR2(25),
Last_Name VARCHAR2(25),
Street VARCHAR2(25),
City VARCHAR2(15),
Phone CHAR(11),
Valid_Date DATE,
message VARCHAR2(200))
/

create table TITLE_COPY(
Copy_ID NUMBER,
Title_ID NUMBER,
Status VARCHAR2(30),
Title_Name VARCHAR2(20),
constraint TITLE_COPY_PK PRIMARY KEY(Copy_ID,Title_ID))
/

create table RENTAL(
Book_Date DATE,
Copy_ID NUMBER,
Member_ID NUMBER,
Title_ID NUMBER,
Act_Return_Date DATE,
Exp_Return_Date DATE,
constraint RENTAL_PK PRIMARY KEY(Book_Date, Copy_ID),
constraint RENTAL_FK FOREIGN KEY(Copy_ID,Title_ID) references TITLE_COPY(Copy_ID,Title_ID) on delete cascade)
/

create table RESERVATION(
Reserve_Date DATE,
Member_ID NUMBER,
Title_ID NUMBER)

/

I understand that I need to check if the video status of my OUT is in and if sql%notfound then i need to insert info into a reservation table. but if the video is in, i need to update status to out. here is what I got so far, not sure if this is the best way to do it.

Create or replace procedure checkout (

p_memberid IN member.member_id%type,
p_titleNum IN rental.title_id%type
p_video_status OUT title_copy.status%type )
is

cnumber number;
titleNum number;

cursor c1 is
select member_id
from member
where member_id = p_memberid;
cursor c2 is
select title_id
from rental
where title_id = p_titleNum;

begin
open c1;
fetch c1 into cnumber;
close c1;
open c2;
fetch c2 into titleNum;
close c2;


here are my objectives:
procedure name is checkout
pass in customer member# and title# into checkout procedure
update video status to OUT if the video is available.
populate a new checkout record into rental table.
populate a new reservation record into reservation table if the video is not available.

Answer 1

Good answer from the Mad Professor but it could be improved slightly and needs to modified slightly for Oracle as follows:



c) See if any copies available, if so, d) Get an available copy, check it out and log the rental, if not, make a reservation



UPDATE title_copy SET status = "OUT"

WHERE titleID = intitleID AND copy_id =

(SELECT MIN(copy_id) FROM title_copy

WHERE titleID = intitleID AND Status = "IN")



IF SQL%ROWCOUNT = 0 THEN /* no copies available */

INSERT INTO reservation VALUES (sysdate, memberID, titleID);

return_status := 'RESERVED';

ELSE /* available */

INSERT INTO rental VALUES

return_status := '"COPY # "||copy_id||" checked out"

END IF;



For help with sql see http://www.asktheoracle.net/oracle-tutorials.html

Answer 2

First off, I wouldn't pass back status, since (assuming a valid titleID) wouldn't it be 'OUT' regardless (either already out or just checked out)? Instead, I'd pass back something like either "Checked Out" or "Reserved", depending on what happened.



Second, I don't see the reason for c1 - I'm assuming that member_id is unique, so it would return at most 1 row (so why use a cursor?) and in any case, you've already HAVE the member id...that's one of the input parms. Likewise, c2 as written is useless...that's the other input parm. However, since multiple copies are a possibility, a cursor might appropriate here except one really still isn't needed.



What I'd do, if passed @memberID and @titleID:



a) validate member

SELECT COUNT(*) INTO @myCount FROM member

WHERE memberID = @memberID



IF myCount = 0, return "Member Not Found" end_if



b) validate title

SELECT COUNT(*) INTO @myCount FROM title_copy

WHERE titleID = @titleID



IF myCount = 0, return "Title Not Found" end_if



c) See if any copies available and, if not, make a reservartion

SELECT COUNT(*) INTO @myCount FROM title_copy

WHERE titleID = @titleID AND Status = "IN"



IF @myCount = 0

INSERT INTO reservation VALUES (GETDATE(), @memberID, @titleID)

RETURN "Reserved"

end_if



d) Get an available copy, check it out and log the rental

SELECT MIN(copy_id) INTO @copy_id) FROM title_copy

WHERE titleID = @titleID AND Status = "IN"



UPDATE title_copy SET status = "OUT"

WHERE titleID = @titleID AND copy_id = @copy_id



INSERT INTO rental VALUES

RETURN "COPY # " + @copy_id + " checked out"

Answer 3

No, inspite of the incontrovertible fact that it relies upon upon the institute or e book ur reffering. for sure if u understand sq. then pl-sq. wouls be effortless to check. it rather is purely like c, c++ and java. u want not 2 study all yet whilst u understand c then discovering c++ n java could be extra uncomplicated.