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.

John S · Accepted Answer

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 <as appropriate>
return_status := '"COPY # "||copy_id||" checked out"
END IF;

For help with sql see http://www.asktheoracle.net/oracle-tutorials.html

TheMadProfessor · Answer

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 <as appropriate>
RETURN "COPY # " + @copy_id + " checked out"

celinka · Answer

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.

Trending News

pl sql needed for cursor and logic?

3 Answers