exercise14

EXPERIMENT NO:14 PL/SQL PROGRAMS- STORED PROCEDURES 
-----------------------------------
1.Create a procedure to display all tables in the invoice database.

mysql> delimiter sanan


mysql> create procedure invoice_table() 
	-> begin
	-> show tables;
	-> end
	-> sanan
Query OK, 0 rows affected (0.00 sec)

mysql> call invoice_table sanan
+-------------------+
| Tables_in_INVOICE |
+-------------------+
| CUSTOMER          |
| CUST_bck          |
| INVOICE_ITEM      |
| INVOICE_MASTER    |
| PRODUCT           |
+-------------------+
5 rows in set (0.00 sec)


2.Create a procedure to find greatest of 3 numbers.
SET DELIMITER //
mysql> CREATE PROCEDURE Largenumber1(a int,b int,c int)
    -> begin
    -> declare x,y,z int;
    -> set x=a;
    -> set y=b;
    -> set z=c;
    -> if a>b and a>c then
    -> select x as 'large number';
    -> else if b>a and b>c then
    -> select y as 'large number';
    -> else
    -> select z as 'large number';
    -> end if;
    -> end if;
    -> end //
Query OK, 0 rows affected (0.00 sec)

mysql> call Largenumber1(15,21,19)//
+--------------+
| large number |
+--------------+
|           21 |
+--------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)




3.Create a procedure to display invoice details of a particular customer.

mysql> create procedure invdetails()
    -> begin
    -> 
    -> select CUSTOMER.NAME,PRODUCT.PNAME,INVOICE_MASTER.INV_DATE,INVOICE_ITEM.QUANTITY FROM PRODUCT INNER JOIN INVOICE_ITEM ON PRODUCT.PID=INVOICE_ITEM.PID INNER JOIN INVOICE_MASTER ON INVOICE_ITEM.INV_ID=INVOICE_MASTER.INV_ID INNER JOIN CUSTOMER ON INVOICE_MASTER.CUST_ID=CUSTOMER.ID WHERE CUSTOMER.NAME='EDGE';
    -> 
    -> end //
Query OK, 0 rows affected (0.01 sec)




mysql> CALL invdetails
    -> //
+------+-------+------------+----------+
| NAME | PNAME | INV_DATE   | QUANTITY |
+------+-------+------------+----------+
| EDGE | BOOK  | 2022-02-16 |        4 |
| EDGE | PEN   | 2022-02-16 |        4 |
+------+-------+------------+----------+
2 rows in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> 



//VERIFIED-CHECK CORRECTION