Oct/Nov - 2019 ==Q5

--Oct/Nov - 2019
--Q5
CREATE TABLE product
(
    prod_no NUMBER primary key,
    name VARCHAR(20) NOT NULL,
    price_per_unit NUMBER,
    quantity_sold NUMBER,
    total_sale NUMBER,
    constraints "ck_qs" CHECK (quantity_sold>0),
    constraints "ck_ppu" CHECK (price_per_unit>0)

);
--insert query
        INSERT INTO product VALUES(1,'A.C.',24000,2,NULL);
        INSERT INTO product VALUES(2,'Freeze',16500,4,NULL);
        INSERT INTO product VALUES(3,'Smart_TV',4000,5,NULL);
        INSERT INTO product VALUES(4,'Home_theater',2500,10,NULL);

1)
DECLARE
    no1 NUMBER;
    n1 NUMBER;
    n2 NUMBER;
    n3 NUMBER;
    ans NUMBER;
    PROCEDURE t_S(n1 NUMBER,n2 NUMBER,n3 OUT NUMBER)
    IS
    BEGIN
        n3:=n1*n2;
    END t_s;
BEGIN
    FOR x IN (SELECT * FROM product)
    LOOP
            SELECT QuantitySold,PricePerUnit INTO n1,n2 FROM product WHERE ProdNo=x.ProdNo ;
            t_S(n1,n2,ans);
            UPDATE product SET TotalSale =ans WHERE ProdNo=x.ProdNo;

    END LOOP;
END;
/


2)
--function
CREATE OR REPLACE FUNCTION price(no1 NUMBER)
    RETURN  NUMBER AS ans NUMBER;
    BEGIN
        SELECT total_sale INTO ans FROM product WHERE prod_no=no1;
        RETURN ans;
    END price;
--pl\sql block
DECLARE
    p_no NUMBER;
    temp NUMBER;

BEGIN
    p_no:=1;
    temp:=price(p_no);
    DBMS_OUTPUT.put_line(' TOTAL SALE of  prod_no => ' ||p_no ||' is '|| temp);
END;
3)

--trigger
CREATE OR REPLACE TRIGGER p3
before DELETE ON product
FOR EACH ROW
  DECLARE
  tday VARCHAR(10):= TO_CHAR(SYSDATE,'DY');
  BEGIN
    DBMS_OUTPUT.put_line(LENGTH(tday));
    IF DELETING THEN
        IF tday IN ('SAT','SUN') THEN
            raise_application_error(-20000,'Delete is not allowed....') ;
                DBMS_OUTPUT.put_line(tday);
        END IF;
    END IF;

 END;
  /


DELETE FROM product WHERE prod_no=4;

SELECT * FROM product