assi9

CREATE TABLE category
(
    categoryid VARCHAR(4) primary key,
    categoryname VARCHAR(50)
);
CREATE TABLE services
(
    servicesid VARCHAR(4) primary key,
    categoryid VARCHAR(4),
    servicesname VARCHAR(50),
    gst_rate NUMBER,
    constraints fk_cat foreign key (categoryid) references category(categoryid) ON DELETE cascade
);

--insert
INSERT INTO category VALUES('c01','commonly_used_goods_and_services');
INSERT INTO category VALUES('c02','standard_goods_and _service_slab_1');
INSERT INTO category VALUES('c03','standard_goods_and _service_slab_2');
INSERT INTO category VALUES('c04','goods_and _service_luxary');
INSERT INTO category VALUES('c05','exempt_service');


--insert services
INSERT INTO services VALUES ('s01','c01','Service1',NULL);
INSERT INTO services VALUES ('s02','c02','Service2',NULL);
INSERT INTO services VALUES ('s03','c03','Service3',NULL);
INSERT INTO services VALUES ('s04','c04','Service4',NULL);
INSERT INTO services VALUES ('s05','c05','Service5',NULL);
INSERT INTO services VALUES ('s06','c01','Service6',NULL);
INSERT INTO services VALUES ('s07','c02','Service7',NULL);
INSERT INTO services VALUES ('s08','c05','Service8',NULL);
INSERT INTO services VALUES ('s09','c04','Service9',NULL);
INSERT INTO services VALUES ('s10','c03','Service10',NULL);
INSERT INTO services VALUES ('s11','c05','Service11',NULL);

--pl/sql blocks
--1>
DECLARE

BEGIN
        UPDATE services
        SET gst_rate=5
        WHERE categoryid = (    SELECT categoryid
                                                FROM category
                                                WHERE categoryname='commonly_used_goods_and_services');

END;
/


--2>
DECLARE
BEGIN
        UPDATE services
        SET gst_rate=12
        WHERE categoryid = (    SELECT categoryid
                                                FROM category
                                                WHERE categoryname='standard_goods_and _service_slab_1');


END;
/
--3>
DECLARE
BEGIN
        UPDATE services
        SET gst_rate=18
        WHERE categoryid = (    SELECT categoryid
                                                FROM category
                                                WHERE categoryname='standard_goods_and _service_slab_2');
END;
/
--4>
DECLARE
BEGIN
        UPDATE services
        SET gst_rate=28
        WHERE categoryid = (    SELECT categoryid
                                                FROM category
                                                WHERE categoryname='goods_and _service_luxary');
END;
/
--(II) trigger
CREATE OR REPLACE TRIGGER trg_exe
before UPDATE ON services
FOR each ROW
DECLARE
id VARCHAR(4);
rate NUMBER;
 CURSOR c1
 IS
 SELECT  categoryid,gst_rate INTO id,rate
 FROM services;
 BEGIN
    OPEN c1 ;
    LOOP
            FETCH c1 INTO id,rate;
            EXIT WHEN c1%notfound;
            IF id='c05' THEN
                raise_application_error(-20003,'UPDATING is not possible on exempt services ');
            END IF;
    END LOOP;
    CLOSE c1;
END trg_exe;
/
UPDATE services
        SET gst_rate=28
        WHERE categoryid = (    SELECT categoryid
                                                FROM category
                                                WHERE categoryname='exempt_service');

--select * from services;
--select * from category;