Practicals of RollBack, Commit and Cursor programs


                                                               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 not exceed 20000/-.if it happens then UNDO  the update and give the  error message “TOTAL SALARY EXCEED 20000”.

       Table : Salary

Emp_no
Name
Salary
1001
Riya
5000
1002
Rima
10000
1003
Rekha
12000
1004
Dhara
6000







Ans:-
   declare

   sal number(8,2);                   
  begin
  savepoint first;
  update Salary set salary=7000 where name='Riya' or name='Rima';                  
  select sum(salary) into sal from salary;
  ifsal>20000 then
  rollback to savepoint first;
  dbms_output.put_line('TOTAL SALARY EXEEDED 20000');
  end if;
  end;
  /

select * from salary;


3. Write a pl/sql to give 5% raise to every employee.

 Use Table Salary.

Ans:- 

declare
cursor cursor1 is select * from SALARY;
emp_no number(10);
name varchar2(10);
salary number(10);
begin
open cursor1;
loop
fetch cursor1 into emp_no,name,salary;
exit when cursor1%notfound;
dbms_output.put_line('emp_no is'||emp_no);
dbms_output.put_line('name is'||name);
dbms_output.put_line('salary is'||salary);

end loop;
update SALARY set salary=salary+(salary*0.05);
close cursor1;
end;
/

4. Write a pl/sql block that find the table empmaster and stores the data into another table CITYLIST(no,name) where CITY=’Bombay’ . Create this pl/sql using following two:
Use pl/sql data type %TYPE
Use pl/sql data %ROWTYPE.
Table: Empmaster

No
Name
City
10
Riya
Bombay
20
Rima
Delhi
30
Rekha
Bombay
40
Dhara
Ahemdabad
50
Dhaval
Jaypur









Ans:- 

declare
cursor cursor2 is select * from Empmaster_1 where City='Bombay';
emp_rec cursor2 %ROWTYPE;
begin
open cursor2;
loop
fetch cursor2 into emp_rec;
exit when cursor2 %NOTFOUND;
dbms_output.put_line('No is'||emp_rec.No);
dbms_output.put_line('Name is'||emp_rec.Name);
dbms_output.put_line('City is'||emp_rec.City);
insert into citylist_1 values(emp_rec.NO,emp_ec.Name);
end loop;
close cursor2;
end;
/

select * from Citylist_1;

5.Write a pl/sql block that update SALARY of employee of a specified company Using parameter in Cursor.
Table : Temp

No
Company
Salary
01
INFOSYS
18000
02
TCS
22000
03
WIPRO
20000
04
ORACLE
25000






Create table Temp_1(No number(2),Company varchar2(15),Salary number(8));

insert into Temp_1 values(01,'INFOSYS',18000);
insert into Temp_1 values(02,'TCS',22000);
insert into Temp_1 values(03,'WIPRO',20000);
insert into Temp_1 values(04,'ORECAL',25000);



Ans:-
 declare

cursor c6(compa varchar2) is select * from Temp_1  where Company=compa;
rec c6%rowtype;
compa varchar2(10);
sal number;
begin
compa:='&compa';
sal:=&sal;
open c6(compa);
if c6 %isopen then
loop
fetch c6 into rec;
exit when c6 %notfound;
update Temp_1 set Salary=sal where Company=rec.Company;
end loop;
commit;
end if; 
close c6;
end;
/

6. Display Only NO,NAME of First three row . Use table Empmaster ( Use FOR LOOP)
Ans:-


declare
cursor cursor4 is select * from Empmaster_1;
No number;
Name varchar2(20);
City varchar2(20);
begin
open cursor4;
loop
fetch  cursor4 into No,Name,City;
 exit when cursor4 %ROWCOUNT>3;
dbms_output.put_line('No’||’       ‘||’Name’);
dbms_output.put_line('---'||'     '||'---');
 end loop;
 end;
 /

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

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_R
Read more