Posted By

georgemathewkunnathoor on 08/19/11


Tagged

mysql stored procedures


Versions (?)

sp_rent


 / Published in: SQL
 

URL: http://www.gladsys.in/blog/stored-procedures-in-mysql/

  1. DROP procedure IF EXISTS rent;
  2. delimiter //
  3. CREATE procedure rent(mname varchar(100),cno int, eno int, out STATUS varchar(100), out rented_dvd_no int )
  4. begin
  5. declare dno int ;
  6. declare did int;
  7. declare cid int;
  8. declare eid int;
  9. declare due_days int DEFAULT 10;
  10.  
  11. SELECT id INTO cid FROM customer WHERE customer_no = cno;
  12. SELECT id INTO eid FROM employee WHERE employee_no = eno;
  13.  
  14. start transaction;
  15. 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;
  16. IF dno IS NOT NULL
  17. then
  18. INSERT INTO rent (customer_id,employee_id,rent_date,due_date,dvd_id) VALUES(cid,eid,current_date(),current_date()+due_days,did);
  19. UPDATE dvd SET availability_status=0 WHERE id=did;
  20. SET rented_dvd_no = dno;
  21. SELECT concat("DVD(",dno,") is rented successfully") INTO STATUS;
  22. commit;
  23. else
  24. SELECT concat("No available stock for the movie",mname) INTO STATUS;
  25. rollback;
  26. end IF;
  27.  
  28. end //
  29. delimiter ;

Report this snippet  

You need to login to post a comment.