Database Management-SEM 3

Creating and working with Insert/Update/Delete Trigger using Before/After clause.

* Before Delete

create table employee
(employee_id Number,
employee_name varchar2(1000),
creation_date Date,
created_by varchar2(1000)
);

select * from employee;

create or replace trigger employee_trigger
before update
on employee
for each row
declare
v_creator_name varchar2(1000);
begin
select user into v_creator_name from dual;
:new.creation_date:=sysdate;
:new.created_by:=v_creator_name;
end;
/

insert into employee values(1,'Shubham',null,null);
insert into employee values(2,'Surve',null,null);

update employee set employee_name='Jay' where employee_id=1;

select * from new_employee;

create table newemployee
(employee_id number,
employee_name varchar2(1000),
creation_date Date,
created_by varchar2(1000));

select * from newemployee;

create table newemployee_duplicate as
(select * from newemployee);

select * from newemployee_duplicate;

create or replace trigger new_employee_trigger2
before delete
on newemployee
for each row
begin
insert into newemployee_duplicate values
(:old.employee_id,:old.employee_name,:old.creation_date,:old.created_by);
end;
/

insert into newemployee values(1,'Shubham',sysdate,'scott');

select * from newemployee;

select * from newemployee_duplicate;

delete from newemployee;

select * from newemployee;

select * from newemployee_duplicate;


     

* After Update

create table employee_salary
(
emp_id number(10),
salary number(10),
emp_name varchar2(50)
);

create table employee_salary_log
(
emp_id number(10),
new_salary number(10),
updated_date date,
updated_by varchar2(20)
);

create or replace trigger try_log_employee_salary
after update
of salary
on employee_salary
for each row
declare
username varchar2(20);
begin
select user into username from dual;
insert into employee_salary_log values
(:new.emp_id,:new.salary,sysdate,username);
end;
/

insert into employee_salary values(101,15000,'Shubham');
insert into employee_salary values(201,50000,'Surve');
insert into employee_salary values(301,89000,'Jay');

update employee_salary set salary='10000' where emp_id=101;
update employee_salary set salary='99999' where emp_id=201;

select * from employee_salary;
select * from employee_salary_log;


 

Creating and working with Insert/Update/Delete Trigger using Before/After clause.

* After Delete

create table medical_bills
(
bill_id number(10) PRIMARY KEY,
bill_number varchar2(20),
party_name varchar2(50),
bill_date date,
created_by varchar2(20),
created_date date
);

create table medical_bills_history
(
bill_id number(10),
bill_number varchar2(20),
party_name varchar2(50),
bill_date date,
deleted_by varchar2(20),
deleted_date date
);

CREATE OR REPLACE trigger trg_after_delete_bill
after delete
on medical_bills
for each row
declare
username varchar2(10);
begin
select user into username from dual;
insert into medical_bills_history values(:old.bill_id,:old.bill_number,:old.party_name,:old.bill_date,username,sysdate);
END;
/

insert into medical_bills values(1,'bill10','peter thomas','12-may-2016','hr',sysdate);

insert into medical_bills values(2,'bill12','petty thomas','18-may-2016','hr',sysdate);

insert into medical_bills values(3,'bill14','jone thomas','27-may-2016','hr',sysdate);

select * from medical_bills;

delete from medical_bills where bill_id=1;

delete from medical_bills where bill_id=3;

select * from medical_bills;

select * from medical_bills_history;

* After Insert

create table new_employee
(employee_id NUMBER,employee_name VARCHAR2 (1000),creation_date DATE,created_by VARCHAR2(1000));

select * from new_employee;

create table new_employee_duplicate as (select * from new_employee);

select * from new_employee_duplicate;

create or replace trigger new_employee_trigger
after insert
on new_employee
for each row
declare
v_creator_name varchar2(1000);
v_creation_date DATE;
begin
select user into v_creator_name
from dual;
v_creation_date := sysdate;
insert into new_employee_duplicate
VALUES(:new.employee_id,:new.employee_name,v_creation_date,v_creator_name);
END;
/

insert into new_employee VALUES (1,'Emp ABCD',sysdate,'SCOTT');

select * from new_employee;
select * from new_employee_duplicate;

Writing PL/SQL Blocks with basic programming constructs by including a GoTO to jump out of a loop and NULL as a statement inside IF.

declare
n number;
m number:=1;
begin
n:=&n;
dbms_output.put_line('Loop will stop at '||n);
while True loop
  dbms_output.put_line(m);
  m:=m+1;
  if(m=n) then
    goto stop;
  end if;
end loop;
<<stop>>
if(m=n) then
 null;
else
 dbms_output.put_line('Process stopped');
end if;
end;
/
   
   

Writing Functions in PL/SQL Block

* Define and call a function
CREATE TABLE CUSTOMERS(
   ID   INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY   DECIMAL (18, 2),     
   PRIMARY KEY (ID)
);

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (1, 'Shubham', 18, 'Mumbai', 2000.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (2, 'Jai', 25, 'Delhi', 1500.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (3, 'Jay', 23, 'Kota', 2000.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (4, 'Kristen', 25, 'Mumbai', 6500.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (5, 'Carl', 27, 'Bhopal', 8500.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (6, 'Jones', 22, 'MP', 4500.00 );

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;
/

* Write a function in plsql to find max of a given number

declare
a number;
B NUMBER;
c number;
function findMax(x IN number,y IN number)
return number
is z number;
begin
if x>y then
z:=x;
else
z:=y;
end if;
return z;
end;
begin
a:=23;
b:=45;
c:=findMax(a,b);
dbms_output.put_line('Maximum of (23,45):'||c);
end;
/

* Write a function in plsql to cal sq of a given number

create or replace function sqr1(no number)
return number
as
sq number;
begin
sq:=no*no;
return sq;
end;
/

begin
dbms_output.put_line('squareis:'||sqr1(10));
end;
/

* Write a plsql program to calculate REV of a number using function

declare
a int;
c int;
n int;
rev int:=0;
r int;
function reverse_it(x IN int)
return int as
z int;
begin
n:=x;
while(n>0) loop
r:=mod(n, 10);
rev:=(rev*10)+r;
n:=trunc(n/10);
end loop;
z:=rev;
return z;
end;

begin
a:=123456789;
c:=reverse_it(a);
dbms_output.put_line('the reverse of a number is'||c);
end;
/

* Calculate a factorial of a number using recursive function

declare
num number;
factorial number;

function fact(x number)
return number
is
f number;
begin
if x=0 then
f:=1;
else
f :=x * fact(x-1);
end if;
return f;
end;
/

begin
num:=6;
factorial:=fact(num);
dbms_output.put_line('Factorial'||num||'is'||factorial);
end;
/

Study of Transactions

create table customers1
(ID number(10),
Name varchar2(30),
Age number(2),
Address varchar2(50),
Salary number(10));

insert into customers1 values(1,'SHUBHAM',18,'Byculla',89000);
insert into customers1 values(2,'SURVE',18,'Thane',80000);
insert into customers1 values(3,'JAI',18,'Dadar',80000);

commit;

insert into customers1 values(4,'ARNAV',18,'Thane',10);

savepoint sav1;

update customers1 set Salary=Salary+1000;

rollback to sav1;

update customers1 set Salary=Salary+1000 where Id=1;
update customers1 set Salary=Salary+1000 where Id=2;

commit;

select * from customers1;

Writing PL/SQL Blocks with basic programming constructs by including following: a. Sequential Statements b. unconstrained loop

a. Sequential Statements

* GOTO Statements

BEGIN
  GOTO second_message;

  <<first_message>>
  DBMS_OUTPUT.PUT_LINE( 'Hello World' );
  GOTO the_end;

  <<second_message>>
  DBMS_OUTPUT.PUT_LINE( ' GOTO Statement' );
  GOTO first_message;

  <<the_end>>
  DBMS_OUTPUT.PUT_LINE( 'Good Bye World' );

END;

* NULL Statement

DECLARE
  b_status BOOLEAN;
BEGIN
  IF b_status THEN
    GOTO end_of_program;
  END IF;

  <<end_of_program>>
  NULL;
END;

Sequences:  a. Creating simple Sequences with clauses like START WITH, INCREMENT BY, MAXVALUE, MINVALUE, CYCLE | NOCYCLE, CACHE | NOCACHE, ORDER | NOORECER.  b. Creating and using Sequences for tables.

create table sample
( col1 number(3) PRIMARY KEY,
col2 varchar2(20));

create sequence sample_col1_get
start with 100
increment by 1
maxvalue 1000
nocycle
cache 10;

desc user_sequences;
select sample_col1_get.currval,sample_col1_get.nextval from dual;

insert into sample values(sample_col1_get.nextval,'&value');
insert into sample values(sample_col1_get.nextval,'&value');

Writing PL/SQL Blocks with basic programming constructs by including  following: a. If...then...Else, IF...ELSIF...ELSE... END IF b. Case statement

* If...then...Else 

DECLARE
a NUMBER:=11;
BEGIN
dbms_output.put_line ('Program started');
IF( mod(a,2)=0) THEN
dbms_output.put_line('a is even number' );
ELSE
dbms_output.put_line('a is odd number');
END IF;
dbms_output.put_line ('Program completed.');
END;
/

* IF...ELSIF...ELSE... END IF 

declare
marks number:=80;
begin
if (marks>=75) THEN
dbms_output.put_line('awesome performance');
else if (marks>=60 and marks<=74.99) THEN
dbms_output.put_line('good performance');
else if (marks>=45 and marks<=59.99) THEN
dbms_output.put_line('average performance');
else if (marks>=35 and marks<=44.99)  THEN
dbms_output.put_line('bad performance');
else
dbms_output.put_line('you are fail');
end if;
end if;
end if;
end if;
end;
/

* Simple Case

declare
grade char(1):='C';
begin
dbms_output.put_line('Program Started');
case grade
when 'A' then dbms_output.put_line('Excellent');
when 'B' then dbms_output.put_line('Very Good');
when 'C' then dbms_output.put_line('Good');
when 'D' then dbms_output.put_line('Pass');
end case;
dbms_output.put_line('Program Completed');
end;
/

* Search Case

DECLARE
a NUMBER :=55;
b NUMBER :=5;
arth_operation VARCHAR2(20) := 'DIVIDE';
BEGIN
dbms_output.put_line('Program started.' );
case
WHEN arth_operation = 'ADD'
THEN dbms_output.put_line('Addition of the numbers are: ' ||a+b );
WHEN arth_operation = 'SUBTRACT'
THEN dbms_output.put_line('Subtraction of the numbers are: ' || a-b);
WHEN arth_operation = 'MULTIPLY'
THEN dbms_output.put_line('Multiplication of the numbers are: ' || a*b );
WHEN arth_operation = 'DIVIDE'
THEN dbms_output.put_line('Division of the numbers are: ' || a/b );
ELSE
dbms_output.put_line('No operation action defined. Invalid operation');
END case;
dbms_output.put_line('Program completed.' );
END;
/

Loop Statements

* Basic Loop

/*print number from 1 to 5 using basic loop statement*/
DECLARE
a NUMBER:=1;
BEGIN
dbms_output.put_line('Program started.');
LOOP
dbms_output.put_line(a);
a:=a+1;
EXIT WHEN a>5;
END LOOP;
dbms_output.put_line('Program completed');
END;
/



DECLARE
   x number := 10;
BEGIN
   LOOP
      dbms_output.put_line(x);
      x := x + 10;
      exit WHEN x > 50;
   END LOOP;
   dbms_output.put_line('After Exit x is: ' || x);
END;
/

* For LOOP

/*print number from 1 to 5 using FOR loop statement*/
begin
dbms_output.put_line('Program started');
for a in 1 .. 5
loop
dbms_output.put_line(a);
end loop;
dbms_output.put_line('Program Completed');
end;
/

/*Reverse of a Given number*/ 

DECLARE
   a number(2) ;
BEGIN
   FOR a IN REVERSE 10 .. 20 LOOP
      dbms_output.put_line('value of a: ' || a);
   END LOOP;
END;
/

/*Fibonacci of a number*/

declare
    first number:=0;
    second number:=1;
    third number;
    n number:=&n;
    i number;

begin
    dbms_output.put_line('Fibonacci series is:');
    dbms_output.put_line(first);
    dbms_output.put_line(second); 

    for i in 2..n
    loop
        third:=first+second;
        first:=second;
        second:=third;
        dbms_output.put_line(third);
    end loop;
end;
/

/*factorial of a number*/

declare
    n number;
    fac number:=1;
    i number;

begin
    n:=&n;

    for i in 1..n
    loop
        fac:=fac*i;
    end loop;

    dbms_output.put_line('factorial='||fac);
end;
/

* WHILE LOOP

/*print number from 1 to 5 using WHILE loop statement*/
DECLARE
a NUMBER :=1;
BEGIN
dbms_output.put_line('Program started');
WHILE (a < 5)
LOOP
dbms_output.put_line(a);
a:=a+1;
END LOOP;
dbms_output.put_line('Program completed' );
END;
/

/*factorial of a number*/

DECLARE
  n_counter   NUMBER := 10;
  n_factorial NUMBER := 1;
  n_temp      NUMBER;
BEGIN
  n_temp := n_counter;
  WHILE n_counter > 0
  LOOP
    n_factorial := n_factorial * n_counter;
    n_counter   := n_counter - 1;
  END LOOP;

  DBMS_OUTPUT.PUT_LINE('factorial of ' || n_temp ||
                       ' is ' || n_factorial);

END;
/

/*Fibonacci of a Number*/

declare
   a number(3):=1;
   b number(3):=1;
   c number(3);
   n number(3):=&n;
begin
  Dbms_output.put_line('the fibinocci series is:');
  while a<=n
    loop
        dbms_output.put_line(a);
        c:=a+b;
        a:=b;
        b:=c;
     end loop;
  end;
/

Writing PL/SQL Blocks with basic programming constructs by including a GoTO to jump out of a loop and NULL as a statement inside IF

declare
p varchar2(30);
n integer:=37;
begin
for j in 2..round(sqrt(n))loop
if n mod j=1 then
p:='is not a prime number';
goto print_now;
end if;
end loop;
p:='is a prime number';
<<print_now>>
dbms_output.put_line(to_char(n)||p);
end;
/



declare
done boolean;
begin
for i in 1..50 loop
if done then
goto end_loop;
end if;
<<end_loop>>
null;
end loop;
end;
/

Creating and working with Insert/Update/Delete Trigger using Before/After clause.

* Before Insert 

create table new_employee0
(employee_id number,employee_name varchar2(1000),creation_date Date,created_by varchar2(1000));

select * from new_employee0;

create table new_employee_duplicate0 as(select * from new_employee0);

select * from new_employee_duplicate0;

create or replace trigger new_employee_trigger0
before insert
on new_employee0
for each row
declare
v_creator_name varchar2(1000);
v_creation_date Date;
begin
select user into v_creator_name from dual;
v_creation_date:=sysdate;
insert into new_employee_duplicate0 values
(:old.employee_id,:old.employee_name,
v_creation_date,v_creator_name);
end;
/

insert into new_employee0 values
(1,'Dave',sysdate,'scott');

select * from new_employee0;
select * from new_employee_duplicate0;

* Before Update :

create table employee
(
emp_id number(10),
salary number(10),
emp_name varchar2(50)
);

create table employeeoldlog
(
emp_id number(10),
new_salary number(10),
updated_date date,
updated_by varchar2(20)
);

create or replace trigger try_log_employee_salary1
before update
of salary
on employee
for each row
declare
username varchar2(20);
begin
select user into username from dual;
insert into employeeoldlog values
(:old.emp_id,:old.salary,sysdate,username);
end;
/

insert into employee values(101,15000,'Dave');

insert into employee values(201,50000,'Dev');

insert into employee values(301,89000,'Ajay');

update employee set salary='10000' where emp_id=101;

update employee set salary='99999' where emp_id=201;

select * from employee;

select * from employeeoldlog;

* Stored Procedures

create or replace  procedure greetings
as
begin
dbms_output.put_line('Hello World!');
end;
/
execute greetings;
begin
greetings;
end;
/

* Write the stored procedure in pl/sql to demonstrate in/out parameter

declare
a number;
b number;
c number;
procedure findMin(x in number, y in number,z out number)is
begin
if x<y then
z:=x;
else
z:=y;
end if;
end;
begin
a:=23;
b:=45;
findMin(a,b,c);
dbms_output.put_line('Minimum of (23,45):'||c);
end;
/

* Write the stored procedure in pl/sql to demonstrate in/out parameter

declare
a number;
procedure squareNum(x in out number)is
begin
x:=x*x;
end;
begin
a:=23;
squareNum(a);
dbms_output.put_line('Square of (23):'||a);
end;
/

* Create an empty procedure,replace a procedure and call a procedure

create or replace procedure proc as
begin
null;
end proc;
/
create or replace procedure proc as
begin
dbms_output.put_line('welcome to pl/sql');
end proc;
/
execute proc;



create or replace procedure procOneINParameter(param1 in varchar2)
is
begin
dbms_output.put_line('Hello World IN parameter'||param1);
end;
/
exec procOneINParameter('mkyong');


create or replace procedure Proc1(p_out out number)
is
begin
p_out := 123;
end;
/
create or replace procedure Proc2
is
l_val number;
begin
proc1(l_val);
dbms_output.put_line('Here is a value returned by Proc1:'||(l_val));
end;
/
execute Proc2;




create table emppp(emp_name varchar2(10),emp_no number(10),dept number(10),salary number(10));
insert into emppp values('Shubham',11,123,2000);
insert into emppp values('asdf',21,123,26000);
insert into emppp values('zxcv',31,123,27000);
select * from empp;
create or replace procedure p121(x in number)
is
begin
delete from emppp where emp_no=x;
end;
/
execute p121;

* Create a table stud.define a procedure to insert a data into stud table

create table stud(id number(10) primary key,name varchar2(100));
create or replace procedure vpm
(id in number,
name in varchar2)
is
begin
insert into stud values(id,name);
end;
/
begin
vpm(101,'Shubham');
dbms_output.put_line('record inserted successfully');
end;
/