Sample: Oracle Create, Update, Insert, Triggers, Cursors, Procedures

/* IGNOU MCA SOLVED QUESTION PAPER MCSL-045 DEC-2012*/

create table Sales_Person 
(S_id number, S_name varchar2(30), Experience number, S_Designation varchar2(30), S_product varchar2(30));

create table Sales_Record 
(S_id number, S_target number, S_sold number, S_order number);

create table Sales_Salary 
(S_id number, S_basic number, S_grade number, S_salary number);

/* A trigger for auto update SALARY */

CREATE OR REPLACE TRIGGER CL_SALARY 
AFTER INSERT OR DELETE OR UPDATE ON Sales_Person
BEGIN
    UPDATE SALES_SALARY SET S_SALARY=S_BASIC+S_GRADE;
END CL_SALARY;

INSERT INTO Sales_Person VALUES(5, 'TUHIN SUMANTA', 30,'MANAGER','COSMETICS');

INSERT INTO Sales_Record VALUES(4,70,65,22);

INSERT INTO SALES_SALARY VALUES(5,7850,500,0);

UPDATE SALES_SALARY SET S_SALARY=S_BASIC+S_GRADE;

SELECT * FROM SALES_PERSON;
SELECT * FROM SALES_RECORD;
SELECT * FROM SALES_SALARY;

/* Creating View with JOIN */

CREATE OR REPLACE VIEW S_VIEW_EX 
AS 
SELECT S.S_NAME,S.EXPERIENCE,S.S_Designation,S.S_product,L.S_SALARY 
FROM SALES_PERSON S JOIN SALES_SALARY L 
ON S.S_ID=L.S_ID
WHERE L.S_SALARY>7000 AND S.S_DESIGNATION='EXECUTIVE';

SELECT * FROM S_VIEW_EX;

/* Record fetching by JOIN */

SELECT S.S_NAME,L.S_SALARY,T.S_TARGET,T.S_SOLD,ROUND(T.S_SOLD*100/T.S_TARGET,0) || '%' AS PERCENT_SOLD
FROM SALES_PERSON S JOIN SALES_SALARY L 
ON S.S_ID=L.S_ID JOIN SALES_RECORD T ON L.S_ID=T.S_ID
WHERE L.S_SALARY>1000 AND T.S_TARGET>S_SOLD;

/* Record fetching by JOIN */

SELECT S.S_NAME,L.S_SALARY,T.S_TARGET,T.S_SOLD,ROUND(T.S_SOLD*100/T.S_TARGET,0) || '%' AS PERCENT_SOLD
FROM SALES_PERSON S JOIN SALES_SALARY L 
ON S.S_ID=L.S_ID JOIN SALES_RECORD T ON L.S_ID=T.S_ID
WHERE  S.S_DESIGNATION='EXECUTIVE' AND T.S_TARGET=S_SOLD;


/* Employee searching by their designation */


create or replace
PROCEDURE FETCH_EMP_DETAILS(VDESIG CHAR)
AS
  CURSOR F_EMP_DETAILS
      IS
        SELECT S_name,S_Designation FROM SALES_PERSON WHERE S_Designation = VDESIG;
        VSNAME VARCHAR2(50);
        VSDESIG VARCHAR2(15);
  BEGIN
      OPEN F_EMP_DETAILS; 
      LOOP
        FETCH F_EMP_DETAILS INTO VSNAME,VSDESIG;
        EXIT WHEN NOT F_EMP_DETAILS%FOUND;
        DBMS_OUTPUT.PUT_LINE('EMP NAME : ' || VSNAME);
        DBMS_OUTPUT.PUT_LINE('DESIGNATION : ' || VSDESIG);
      END LOOP;
      CLOSE F_EMP_DETAILS;
END FETCH_EMP_DETAILS;


SET SERVEROUTPUT ON
BEGIN
  FETCH_EMP_DETAILS('EXECUTIVE');
END;