Oracle programming code_Part1

Oracle programming codes_Part_1

//If-else block
declare
a number;
b number;
begin
a := &a;  --input from console
b := &b;  --input from console
if a<b  then
   dbms_output.put_line('a is smallest');
else
   dbms_output.put_line('b is smallest');
end if;
end;
-----------------------
declare
a number;
b number;
c number;
begin
 a := &a;
 b := &b;
 c := &c;
 if a>b and a>c then
   dbms_output.put_line('a is  >');
 elsif b>c then
   dbms_output.put_line('b is  >');
 elsif a=b and b=c then
   dbms_output.put_line('a=b=c');
 else
   dbms_output.put_line('c is  >');
 end if;
end;
========================

//Simple input output
declare
a number;
b number;
c number;
begin
  a := &a;
  b := &b;
  c := a+b;
  dbms_output.put_line('a+b = ' || c);
end;
=========================

//Loop programs
declare
 c number;
begin
 c := 0;
 loop
  c := c+1;
  dbms_output.put_line(c);
  if c = 10 then exit;
  end if;
 end loop;
end;
--------------------
declare
c number;
begin
 c := 0;
 until c>11 loop
  c:=c+1;
  dbms_output.put_line(c);
 end loop;
end;
--------------------
declare
c number;
begin
for c in 1..10  loop
 dbms_output.put_line(c);
end loop;
end;
---------------------
declare
 c number;
begin
 c := 0;
 loop
  c := c+1;
  dbms_output.put_line(c);
  exit when c = 10;
 end loop;
end;

==========================

//Rowtype
declare
x emp%rowtype;
begin
 select * into x from emp where empno=7839;
 if(x.sal >3000) then
  update temp2 set comm=300 where x.empno=7839;
 elsif (x.sal < 6000) then
  update temp2 set comm=600 where x.empno=7839;
 else
  update temp2 set comm=100 where x.empno=7839;
 end if;
 
 dbms_output.put_line('job done');
end;
--------------------------
//Type
declare
 x emp.sal%type;
 e emp.ename%type;
 eno  emp.empno%type;
begin
 eno := &eno;
 select sal into x from emp where empno=eno;
 select ename into e from emp     where empno=eno;
 if x>3000 then
  dbms_output.put_line(e||'s salary is greater than 3000'); 
 else
  dbms_output.put_line(e||'s salary is less than 3000'); 
end if;
end;
==========================
//Exception in oracle
//User defined exception
declare
 a number;
 b number;
 c number;
 my_zero_divide exception;
begin
 a := &a;
 b := &b;
 if b=0 then
   raise my_zero_divide; 
 else
   c := a/b;
   dbms_output.put_line('a/b = ' || c);
 end if;

Exception
when my_zero_divide then
  dbms_output.put_line('you tried to divide a number by zero');

dbms_output.put_line('There was an exception');
end;
--------------------------
//System defined exception
declare
 vename emp.ename%type;
 vjob emp.job%type;
begin
 select ename ,job into vename,vjob from emp where sal>2000;

 Exception
 when NO_DATA_FOUND then
    dbms_output.put_line('NO_DATA_FOUND SQLCODE '||SQLCODE||'SQLERRM '||SQLERRM);
 when TOO_MANY_ROWS then
    dbms_output.put_line('TOO_MANY_ROWS'||'sqlcode   '||sqlcode||'   SQLERRM '||SQLERRM);
 when others then 
    dbms_output.put_line('other Exception');
end;
==========End of chapter1==================