sp_rent

DROP procedure IF EXISTS rent;
delimiter //
CREATE procedure rent(mname varchar(100),cno int, eno int, out STATUS varchar(100), out rented_dvd_no int )
begin
        declare dno     int ;
        declare did     int;
        declare cid     int;
        declare eid     int;
        declare due_days int DEFAULT 10;
 
        SELECT id INTO cid FROM customer WHERE customer_no = cno;
        SELECT id INTO eid FROM employee WHERE employee_no = eno;
 
        start transaction;
        SELECT dvd_no,dvd.id INTO dno,did  FROM dvd INNER JOIN movie ON ( dvd.movie_id = movie.id) WHERE movie.movie_name='Slumdog Millionaire' AND availability_status=1 LIMIT 1 FOR UPDATE;
        IF dno IS NOT NULL
        then
                INSERT INTO rent (customer_id,employee_id,rent_date,due_date,dvd_id) VALUES(cid,eid,current_date(),current_date()+due_days,did);
                UPDATE dvd SET availability_status=0 WHERE id=did;
                SET rented_dvd_no = dno;
                SELECT concat("DVD(",dno,") is rented successfully") INTO STATUS;
                commit;
        else
                SELECT concat("No available stock for the movie",mname) INTO STATUS;
                rollback;
        end IF;
 
end //
delimiter ;