PL SQL

mysql> CREATE DATABASE ASS5;
Query OK, 1 row affected (0.00 sec)

mysql> USE ASS5 ;
Database changed
___________________________________________________________________________________
___
mysql>CREATE TABLE BORROWER (ROLLNO INTEGER(10),NAME VARCHAR
(30),DATEOFISSUE VARCHAR (15), NAMEOFBOOK VARCHAR (30), STATUS CHAR
(5));
Query OK, 0 rows affected (0.36 sec)
mysql> INSERT INTO BORROWER
(ROLLNO,NAME,DATEOFISSUE,NAMEOFBOOK,STATUS)
VALUES
('101','RAM','2017-06-17','DBMS','R'),
('102','SHAM','2017-07-20','ISEE','R'),
('103','SITA','2017-07-15','SE','R'),
('106','GITA','2017-08-27','DBMS','I'),
('107','QQQ','2017-08-17','CN','I'),
('108','SSSQQQ','2017-08-1','DBMS','I');
mysql> SELECT * FROM BORROWER;
+--------+--------+-------------+------------+--------+
| ROLLNO | NAME | DATEOFISSUE | NAMEOFBOOK | STATUS |

+--------+--------+-------------+------------+--------+
| 101 | RAM | 2017-06-17 | DBMS | R |
| 102 | SHAM | 2017-07-20 | ISEE | R |
| 103 | SITA | 2017-07-15 | SE | R |
| 106 | GITA | 2017-08-27 | DBMS | I |
| 107 | QQQ | 2017-08-17 | CN | I |
| 108 | SSSQQQ | 2017-08-1 | DBMS | I |
+--------+--------+-------------+------------+--------+
6 rows in set (0.00 sec)
___________________________________________________________________________________
_______
DELIMITER //
CREATE PROCEDURE DOISSUEBOOK(p1 int,p2 varchar(30),p3 varchar (15),p4
varchar(30))
X:BEGIN
SET @p3val = str_to_date(p3,'%d/%m/%y');
SET @p5val = 'i';
SET @ERRORMSG = '';
IF p1 <= 0 THEN
BEGIN
SET @ERRORMSG = 'Wrong Roll no Specified';
SELECT @ERRORMSG;
LEAVE X;
END;
END IF;
IF LENGTH(p4) <= 0 THEN
BEGIN
SET @ERRORMSG = 'WRONG BOOK';
SELECT @ERRORMSG;
LEAVE X;
END;
END IF;
INSERT INTO BORROWER (NAME,DATEOFISSUE,NAMEOFBOOK,STATUS,ROLLNO)
VALUES(p2,@p3val,p4,@p5val,p1);
END //
___________________________________________________________________________________
_______
CREATE PROCEDURE DORETURN(RNO integer(10),BOOKNAME VARCHAR(30))
BEGIN
SET @NUMDAYS = 0;
SET @FINEAMT = 0.0;
SET @NUMREC = 0;
SELECT DATEDIFF(CURDATE(),DATEOFISSUE) FROM BORROWER WHERE
ROLLNO = RNO AND NAMEOFBOOK = BOOKNAME INTO @NUMDAYS;
SELECT COUNT(*) FROM BORROWER WHERE ROLLNO = RNO AND NAMEOFBOOK
= BOOKNAME INTO @NUMREC;
IF @NUMDAYS >= 15 AND @NUMDAYS <= 30 THEN SET @FINEAMT=(@NUMDAYS-
15)*5;
ELSEIF @NUMDAYS > 30 THEN SET @FINEAMT = ((15*5)+(@NUMDAYS-30)*50);
ELSE SET @FINEAMT = 0;
END IF;
IF @NUMREC >0 THEN
UPDATE BORROWER SET STATUS = 'R' WHERE ROLLNO = RNO AND
NAMEOFBOOK = BOOKNAME;
INSERT INTO FINE(ROLLNO,DATE,AMT) VALUES (RNO,CURDATE(),@FINEAMT);
END IF;
END;
//
___________________________________________________________________________________
__
mysql>CREATE TABLE FINE (ROLLNO INTEGER,DATE DATE,AMT DECIMAL (5,2)); //
mysql>CALL DOISSUEBOOK('106','GITA','2017-08-27',''); //
+------------+
| @ERRORMSG |
+------------+
| WRONG BOOK |
+------------+
1 row in set (0.00 sec)
__________________________________________________________________________________
mysql>CALL DOISSUEBOOK('0','GITA','2017-08-27','DBMS'); //
+-------------------------+
| @ERRORMSG |
+-------------------------+
| Wrong Roll no Specified |
+-------------------------+
___________________________________________________________________________________
_____
mysql>CALL DORETURN('106','DBMS'); //
mysql>SELECT * FROM FINE; //
+--------+------------+------+
| ROLLNO | DATE | AMT |
+--------+------------+------+
| 106 | 2017-09-12 | 5.00 |
+--------+------------+------+
___________________________________________________________________________________
_
mysql> SELECT * FROM BORROWER; //
+--------+--------+-------------+------------+--------+
+--------+--------+-------------+------------+--------+
| 101 | RAM | 2017-06-17 | DBMS | R |
| 102 | SHAM | 2017-07-20 | ISEE | R |
| 103 | SITA | 2017-07-15 | SE | R |

| 106 | GITA | 2017-08-27 | DBMS | R |
| 107 | QQQ | 2017-08-17 | CN | I |
| 108 | SSSQQQ | 2017-08-1 | DBMS | I |
+--------+--------+-------------+------------+--------+
________________________________________________________________________________
mysql> CALL DORETURN('107','CN'); //
mysql>SELECT * FROM BORROWER; //
+--------+--------+-------------+------------+--------+
+--------+--------+-------------+------------+--------+
| 101 | RAM | 2017-06-17 | DBMS | R |
| 102 | SHAM | 2017-07-20 | ISEE | R |
| 103 | SITA | 2017-07-15 | SE | R |
| 106 | GITA | 2017-08-27 | DBMS | R |
| 107 | QQQ | 2017-08-17 | CN | R |
| 108 | SSSQQQ | 2017-08-1 | DBMS | I |
+--------+--------+-------------+------------+--------+
___________________________________________________________________________________
mysql>SELECT * FROM FINE; //
+--------+------------+-------+
| ROLLNO | DATE | AMT |
+--------+------------+-------+
| 106 | 2017-09-12 | 5.00 |
| 107 | 2017-09-12 | 55.00 |
+--------+------------+-------+

PL SQL

Uploaded by

Copyright:

Available Formats

PL SQL

Uploaded by

Document Information

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

PL SQL

Uploaded by

Copyright:

Available Formats

mysql> CREATE DATABASE ASS5;

Query OK, 1 row affected (0.00 sec)

| ROLLNO | NAME | DATEOFISSUE | NAMEOFBOOK | STATUS |

| 103 | SITA | 2017-07-15 | SE | R |

You might also like