Programs Based on Exception Handling and Trigger.


                                                       PRACTICAL-11

Aim :-  Programs Based on Exception Handling and Trigger.

1. Table: STUDENT (sr_no, name, address). Write a pl/sql code to retrieve the data from                student table whose sr.no is 02ce33. if   data not found then show the message “data not                found “  (Use EXCEPTION in pl/sql code).
Ans :-

declare
no int;
nm varchar2(20);
begin
no:=&no;
select NAME into nm from student where ROLLNO=no;
dbms_output.put_line('Name of student is '||nm);
exception
when NO_DATA_FOUND then
dbms_output.put_line('No data found!!!');
end;
/

2. Give Example of “TOO_MANY_ROWS” Exception.
    Give Example of Unnamed system Exception.(Give Example of Primary key constraint  violation)
Ans:-

declare

nm varchar2(20);
begin
select name into nm from student;
dbms_output.put_line('Name of student is '||nm);
 exception
when TOO_MANY_ROWS then
dbms_output.put_line('Too many rows!!!');
end;
/

declare
primary_ky exception;
pragma Exception_init(primary_ky,-0001);
begin
insert into student values(1,'ABC','AHM');
exception
when primary_ky then
dbms_output.put_line('Can not add values');
end;
/




3. When you perform update or delete operation on table company1, stores the updated or deleted                values in table company2. Also show the operation ‘update’ and ‘delete’ and username.

     Table: Company1 (no, name)
                Company2 (no,name , operation, username)
Ans:- 
create or replace trigger Comp after update or delete on Company1 for each row

declare
no number(1);
name varchar2(20);
username varchar2(20);
op varchar2(10);                
begin
if updating then
op:='UPDATE';
no:=:OLD.No;
name:=:OLD.Name;
end if;
if deleting then
op:='DELETE';
no:=:OLD.No;
name:=:OLD.Name;
end if;
insert into Company2 values(no,name,op,USER);
end;
/

4. Whenever you insert any value into table test1 then table test2 automatically calculate the Total    of No 1 and No 2.

Table:  Test1 (No1,No2)   
          Test2 (Total)
Ans:-

create table Test1(a number,b number);
create table Test2(c number);

create or replace trigger Add_numb1 after insert on Test1 for each row
declare
a number;
b number;
c number;
begin
c:=:new.a+:new.b;
insert into Test2 values(c);
end;
/
  
insert into Test1 values(1,2);

select * from Test2;

Comments

Post a Comment

Popular posts from this blog

SQL QUERY FOR PRIMARY AND FOREIGN KEY

Practical-3 1. Create table Account_key Column name Data Type Size Attributes Acc_no Varchar2 5 Primary key / first letter must start with ‘A’ Name Varchar2 30 NOT NULL City Varchar2 20 NOT NULL Balance Number 10,2 Balance >=500 Loan_taken Varchar2 3 Values(‘NO’,’YES’) ANS . create table account_key(acc_no varchar2(5) primary key check(acc_no like 'A%'),name varchar2(30) not null,city varchar2(20) not null,balance number(10,2) check(balance>=500),loan_taken varchar2(3) check(loan_taken='yes' or loan_taken='no')); acc_no Name City Balance loan_taken A001 Patel Jigar Mehsana 50000 YES A002 Patel Ramesh Mehsana 50000 YES A003 Dave Hardik Ahmeda
Read more

SQL QUERY

                                Prac tical-2 1. Retrieve specified information for the account holder who are not in ‘Ahmedabad’. Ans: select * from account where city not in ‘Ahemdabad’; 2. Retrieve specified information for the account holder who are not in ‘Ahmedabad or ‘Vadodara’. Ans: select * from account where city not in('Ahemadabad', 'Vadodara'); 3. Retrieve those records of Account holder whose balance between is 50000 and 100000. Ans: select * from account where balance between 50000 and   1000000; .4. Retrieve those records of Account holder whose balance not between is 50000 and 100000. Ans: select * from account where balance not between 50000 and 100000; 5. Display only those records whose amount is 5000, 25000, 30000. Ans: select * from Transaction where amount in (5000, 25000, 30000); 6. Display only those records whose amount not in 5000, 25000, 30000. Ans:   select * from Transaction where Amt not in (5
Read more

SQL QUERY practical-5

                                     PRACTICAL-5 CREATE TABLE SALESMEN(SNUM VARCHAR2(6) PRIMARY KEY CHECK(SNUM LIKE 'S%'), SNAME VARCHAR2(20) NOT NULL, CITY VARCHAR2(15), COMM NUMBER(5,2)); INSERT INTO SALESMEN VALUES('S1001','PIYUSH','LONDON',0.12); INSERT INTO SALESMEN VALUES('S1002','NIRAJ','SAN JOSE',0.13); INSERT INTO SALESMEN VALUES('S1003','MITI','LONDON',0.11); INSERT INTO SALESMEN VALUES('S1004','RAJESH','BARCELONA',0.15); INSERT INTO SALESMEN VALUES('S1005','HARESH','NEW YORK',0.10); INSERT INTO SALESMEN VALUES('S1006','RAM','BOMBAY',0.10); INSERT INTO SALESMEN VALUES('S1007','NEHAL','DELHI',0.09); CREATE TABLE CUSTOMER(CNUM VARCHAR2(6) PRIMARY KEY CHECK(CNUM LIKE 'C%'), CNAME VARCHAR2(20) NOT NULL, CITY VARCHAR2(15), RATING NUMBER(5), SNUM VARCHAR2(6)); INSERT INTO CUSTOMER VALUES('
Read more

Practicals of RollBack, Commit and Cursor programs

Image
                                                                 Practical-10 AIM :- RollBack   , Commit and Cursor bases Programs. 1. Analyze output of following scripts: A.      Begin                               Insert into new_salary values ('6', '6000');                               Update new_salary set salary=4000 where id=4;                               Commit;                               Rollback;                 End;               / Ans:-                B.   Begin                                 Insert into new_salary values (8, 8000);                Update new_salary set salary=50000 where id=5;                Savepoint   first;                    Rollback to savepoint first;        End;        /               Ans:- 2. Write pl/sql blocks that first insert the new record into table salary. Update the salary of employee name ‘Riya’ and ‘Rima’ by 7000/- then check the total salary   which does
Read more