SELECT * FROM DRUGS


Proc1();

a = Func1()  // inside select 

Trigger Tr1


CREATE [OR REPLACE] FUNCTION function_name  
   [ (parameter [,parameter]) ]  
RETURN return_datatype  
IS | AS  
 [declaration_section]  
BEGIN  
   executable_section  
[EXCEPTION  
   exception_section]  
END [function_name];  
--------------------------------------------------------------

create or replace function adder(n1 in number, n2 in number)    
return number    
is     
n3 number(8);    
begin    
n3 :=n1+n2;    
return n3;    
end;    

create or replace function CharCount(DId in number)    
return number    
is     
Cnt number(8);    
begin    
SELECT Length(NAME) into Cnt FROM  DRUGS WHERE DRUGID = DId;
return Cnt;    
end;   

SELECT DRUGID, NAME, CharCount(DRUGID) CntChar 
FROM DRUGS
WHERE CharCount(DRUGID) > 6
ORDER BY CharCount(DRUGID)  DESC;

SELECT StdID, StdName, Fn_CalcCGPA(StdID) AS CGPA  FROM STUDENTS

SELECT GoodsID, GoodsName, LastPrice() from ... 


DECLARE    
   Cnt number(8);      
BEGIN    
   SELECT Max(Length(NAME)) into Cnt FROM  DRUGS ; 
   dbms_output.put_line('Addition is: ' || Cnt);    
END;   




DECLARE    
   n3 number(2);    
BEGIN    
   n3 := adder(11,22);    
   dbms_output.put_line('Addition is: ' || n3);    
END;   

--------------------------------------------------


CREATE OR REPLACE FUNCTION totalCustomers  
RETURN number IS  
   total number(2) := 0;  
BEGIN  
   SELECT count(*) into total  
   FROM customers;  
    RETURN total;  
END; 

DECLARE  
   c number(2);  
BEGIN  
   c := totalCustomers();  
   dbms_output.put_line('Total no. of Customers: ' || c);  
END;


SELECT DRUGID, NAME FROM DRUGS
UPDATE DRUGS SET NAME = CONCAT(NAME, '1')  WHERE DRUGID = 1;

--PROCEDURE
CREATE OR REPLACE PROCEDURE UpdDrugs(Did number)  AS 
BEGIN

UPDATE DRUGS SET NAME = CONCAT(NAME, '1')  WHERE DRUGID = Did;

END ;




BEGIN
  UpdDrugs(2);
END;



DROP TABLE books
CREATE TABLE books (
  id NUMBER GENERATED AS IDENTITY,
  title VARCHAR2(100) NOT NULL
);

INSERT INTO books (title) values('title1');
INSERT INTO books (title) values('title2');

Select * From books

Select User from dual

DROP TABLE books_Log;
CREATE TABLE books_Log (
  id_Log NUMBER GENERATED AS IDENTITY,
  id NUMBER,
  title VARCHAR2(100) ,
  Change_By  varchar2(100), 
  TrnsTyp varchar2(100) ,
  ChangeDate date
);

create or replace trigger Tr_Book after update or delete 
on books
for each row
DECLARE
   TrnsTyp VARCHAR2(10);
BEGIN
   -- determine the transaction type
   TrnsTyp := CASE  
         WHEN UPDATING THEN 'UPDATE'
         WHEN DELETING THEN 'DELETE'
   END;

   INSERT INTO books_Log (id, title,  Change_By,  TrnsTyp ,  ChangeDate)
   values(:old.id, :old.title,  User,  TrnsTyp ,  sysdate);
END;

Select * FROM books_Log;
DELETE FROM books_Log;

Select * FROM books;

UPDATE books set TITLE = 'Titsadle1' WHERE ID = 1;

Select * FROM books_Log;

UPDATE books set TITLE = 'NewTitle2'
WHERE ID = 1;

Select * FROM books_Log;

DELETE FROM books WHERE ID = 1;

Select * FROM books_Log;
---------------------------------

DROP trigger BIUD_STUDENT;

create or replace trigger T1_STUDENT
before insert or update or delete 
on STUDENT
for each row
DECLARE
   Avg_CGPA number;
   TrnsTyp VARCHAR2(10);
BEGIN
   -- determine the transaction type
   TrnsTyp := CASE  
         WHEN UPDATING THEN 'UPDATE'
         WHEN DELETING THEN 'DELETE'
   END;

   -- insert a row into the audit table   
   --SELECT user INTO v_username
   --FROM dual;
   --INSERT INTO Log  (table_name, transaction_name, by_user, transaction_date)
   --VALUES('CUSTOMERS', l_transaction, USER, SYSDATE);
   --ALTER TABLE Department ADD AvgCGPA NUMBER DEFAULT 0
   --SELECT * FROM Department
   
   SELECT SUM(CGPA) into Avg_CGPA FROM Student;
   
   
    --insert into PERSON(Perid) values(15);
 
   --Update Department 
   --Set 
  --    AVGCGPA = Avg_CGPA
  -- WHERE Dept_Name  =:new.Dept_Name;
   
   --dbms_output.put('Old AVGCGPA: ' || :old.CGPA);
   --dbms_output.put('Old AVGCGPA: ' );
END;


create or replace trigger T1_STUDENT
after insert or update or delete
on STUDENT
for each row
DECLARE
   Avg_CGPA number;
   TrnsTyp VARCHAR2(10);
BEGIN
   -- determine the transaction type
   TrnsTyp := CASE  
         WHEN UPDATING THEN 'UPDATE'
         WHEN DELETING THEN 'DELETE'
   END;

   -- insert a row into the audit table   
   --SELECT user INTO v_username
   --FROM dual;
   --INSERT INTO Log  (table_name, transaction_name, by_user, transaction_date)
   --VALUES('CUSTOMERS', l_transaction, USER, SYSDATE);
   --ALTER TABLE Department ADD AvgCGPA NUMBER DEFAULT 0
   --SELECT * FROM Department
   
   SELECT SUM(CGPA) into Avg_CGPA FROM Student;
   
   
    --insert into PERSON(Perid) values(15);
 
   Update Department 
   Set 
      AVGCGPA = Avg_CGPA
   WHERE Dept_Name  =:new.Dept_Name;
   
   --dbms_output.put('Old AVGCGPA: ' || :old.CGPA);
   --dbms_output.put('Old AVGCGPA: ' );
END;


SELECT * FROM student;
SELECT * FROM Department;

insert into student(ID, Name, Dept_Name, Tot_credit) values (10005,'AgjhAAA','CMPE',101)
insert into student(ID, Name, Dept_Name, Tot_credit) values (5435305,'derter','CMPE',101)

--UPDATE

UPDATE student
SET CGPA = 3.1
WHERE ID = 10001;



ALTER TRIGGER T1_STUDENT DISABLE;

ALTER TABLE student ENABLE ALL TRIGGERS;