--simple pl/sql program
declare
      sno number(3):=&sno;
      sname varchar2(20):='&sname';
      course varchar2(20):='&course';
      feepaid number(5,2):=&feepaid;
      doadmin date:='&doadmin';
      studdes long:='&studdes';
begin
      dbms_output.put_line('Sno  : '||sno);
      dbms_output.put('Sname : '||sname);
      dbms_output.put_line('        Course : '||course);
      dbms_output.put_line('Feepaid : '||feepaid);
      dbms_output.put_line(doadmin);
      dbms_output.put_line('Description: '||studdes);
end;
/
---------------------------------------------------------------------------------
--simple cursor
declare
  cursor cur1 is select * from emp where job='&j';
  emprec emp%rowtype;
begin
  open cur1;
  loop
          fetch cur1 into emprec;
          exit when cur1%notfound;
          dbms_output.put_line(emprec.empno||'  '||emprec.ename);
  end loop;
  close cur1;
end;
/
-------------------------------------------------------------------------------
--simple cursor with exception
declare
  cursor cur1 is select * from emp where job='&j';
  emprec emp%rowtype;
  cnt number;
begin
  open cur1;
  loop
          fetch cur1 into emprec;
          exit when cur1%notfound;
          dbms_output.put_line(emprec.empno||'  '||emprec.ename);
  end loop;
  cnt:=cur1%rowcount;
  close cur1;
  if cnt<>0 then
    dbms_output.put_line('NO OF RECORDS FETCHED ARE: '||cnt);
  else
    dbms_output.put_line('SORRY NO RECORD FOUND WITH THE JOB AND NO RECORD IS FETCHED INTO CONTEXT AREA');
  end if;
end;

/
-------------------------------------------------------------------------
--cursor using for loop
declare
  cursor cur1 is select * from emp where deptno=&deptno;
begin
  for i in cur1 loop
    dbms_output.put_line(i.empno||'    '||i.ename||'    '||i.sal);
  end loop;
end;
/
------------------------------------------------------------------------
--for cursor using multiple tables
declare
  cursor cur1 is select * from emp;
  cursor cur2 is select * from dept;
begin
  for i in cur1 loop
      for j in cur2 loop
        if i.deptno=j.deptno and i.job='MANAGER'  then
        dbms_output.put_line(i.empno||'    '||i.ename||'  '||j.dname||'    '||j.loc);
        end if;
      end loop;
  end loop;
end;
/
---------------------------------------------------------------

--for cursor using multiple tables1
delcare
  cursor cur3 is select *from emp;
  cursor cur4 is select* from dept;
begin
for in cur3 loop
  for in cur4 loop
  if m.deptno=n.deptno and m.job='anylist'then
  dbms_output.put_line(m.empno||'    '||m.ename||'    '||n.dname||'    '||n.loc);

  --------------------------------------------------------------
--for cursor using multiple tables1
declare
  cursor cur3 is select *from emp;
  cursor cur4 is select* from dept;
begin
for m in cur3 loop
  for n in cur4 loop
  if m.deptno=n.deptno and m.job='ANALYST' then
  dbms_output.put_line(m.empno||'    '||m.ename||'    '||n.dname||'    '||n.loc);
  end if;
  end loop;
end loop;
end;
/
--------------------------------------------------------------
--cursor to retrieve the records from emp and insert into emp_dup
declare
  cursor cur1 is select empno,ename,job,sal,comm from emp where job='&job';
  emprec cur1%rowtype;
  cnt number;
begin
  open cur1;
  loop
        fetch cur1 into emprec;
        exit when cur1%notfound;
        insert into emp_dup(eno,ename,design,pay,comm) values(emprec.empno,emprec.ename,emprec.job,emprec.sal,emprec.comm);
  end loop;
  commit;
  cnt:=cur1%rowcount;
  close cur1;
  if cnt=0 then
  raise_application_error(-20010,'SORRY NO JOB WITH THIS NAME');
  else
  dbms_output.put_line(cnt||'  are inserted into the emp_dup table');
  end if;
  exception
  when dup_val_on_index then
  dbms_output.put_line('constraints are imposed on emp_dup table');
  end;
/
        --------------------------------------------------------------------
--cursor to retrieve the records from emp,dept and insert into emp_dept_dup
declare
  cursor cur1 is select empno,ename,job,hiredate,sal,comm,sal+nvl(comm,0),dname,loc from emp,dept where emp.deptno=dept.deptno;
  e_d_r cur1%rowtype;
begin
  open cur1;
    loop
      fetch cur1 into e_d_r;
      exit when cur1%notfound;
      insert into emp_dept_dup values(e_d_r.empno,e_d_r.ename,e_d_r.job,e_d_r.hiredate,e_d_r.sal,e_d_r.comm,e_d_r.sal+nvl(e_d_r.comm,0),e_d_r.dname,e_d_r.loc);
    end loop;
    commit;
    close cur1;
end;
/

------------------------------------------------------------------------------
--cursor using parameter
declare
  cursor cur1(no number) is select * from emp where deptno=no;
begin
  for i in cur1(&no) loop
    dbms_output.put_line(i.empno||'  '||i.ename||'  '||i.job||'  '||i.deptno);
  end loop;
end;
/

--------------------------------------------------------------------------
--cursor using parameters
declare
  cursor cur1(no number,j varchar2) is select * from emp where deptno=no and job=j;
begin
  for i in cur1(&no,'&j') loop
    dbms_output.put_line(i.empno||'  '||i.ename||'  '||i.job||'  '||i.deptno);
  end loop;
end;
/
-------------------------------------------------------------------------
--to check if select statement fails what cursor results
declare
    cursor mycur1 is select * from emp where deptno=&deptno;
    myrec emp%rowtype;
begin
  open mycur1;
  loop
    fetch mycur1 into myrec;
    exit when mycur1%notfound;
  end loop;
  dbms_output.put_line('Record fetched: '||mycur1%rowcount);
  if mycur1%rowcount=0 then
    dbms_output.put_line('INVALID DEPTNO');
  end if;
  close mycur1;
end;
/
-----------------------------------------------------------------------------
--cursor with for update clause using commit will cause error
declare
  cursor mycur1 is select * from emp where deptno=10 for update of sal nowait;
  myrec emp%rowtype;
begin
  open mycur1;
  loop
    fetch mycur1 into myrec;
    if myrec.job='MANAGER' then
      update emp set sal=sal+100 where job='MANAGER';
      commit;
    end if;
    exit when mycur1%notfound;
  end loop;
  dbms_output.put_line('No of Record updated: '||mycur1%rowcount);
  close mycur1;
end;
/
----------------------------------------------------------------
--cursor to find the sum and average of salary for a specific department
declare
  cursor mycur1 is select * from emp where deptno=&deptno;
  emprec emp%rowtype;
  s number:=0;
  a number;
  cnt number;
begin
  open mycur1;
  loop
          fetch mycur1 into emprec;
          exit when mycur1%notfound;
          dbms_output.put_line(emprec.empno||'  '||emprec.ename||'  '||emprec.sal);
          s:=s+emprec.sal;
    end loop;
    cnt:=mycur1%rowcount;
    close mycur1;
    if cnt=0 then
    raise_application_error(-20010,'SORRY NO DEPTNO EXIST');
    else
    dbms_output.put_line('Sum of salary  : '|| s);
      a:=trunc(s/cnt,0);
      dbms_output.put_line('Average of salary : '||a);
    end if;
end;
/

-------------------------------------------------------------------------
--cursor using predefined exception
--cursor_already_open
--raises when the cursor is opened more than once without closing
declare
  cursor cur1 is select * from emp where comm is not null;
  emprec emp%rowtype;
begin
  open cur1;
  open cur1;  --raises the exception
  loop
        fetch cur1 into emprec;
        exit when cur1%notfound;
        dbms_output.put_line(emprec.empno||'  '||emprec.ename||'  '||emprec.sal||'  '||emprec.comm);
  end loop;
  close cur1;
exception
  when cursor_already_open then
  dbms_output.put_line('CANNOT OPEN THE CURSOR MORE THAN ONCE AT SAME TIME');
end;
/
--------------------------------------------------------------------------
--cursor using predefined exception
--invalid_cursor
--raises when the cursor is closed more than once or trying to open a non existing --cursor
declare
  cursor cur1 is select * from emp where comm is not null;
  emprec emp%rowtype;
begin
  open cur1;
  loop
        fetch cur1 into emprec;
        exit when cur1%notfound;
        dbms_output.put_line(emprec.empno||'  '||emprec.ename||'  '||emprec.sal||'  '||emprec.comm);
  end loop;
  close cur1;
  close cur1; --exception raises
exception
  when invalid_cursor then
  dbms_output.put_line('cannot close the cursor more than once at same time');
end;
/
----------------------------------------------------------------------------
--to insert the data into bank table using pl/sql
accept l prompt 'Enter accno : '
accept y prompt 'Enter the accname: '
accept z prompt 'Enter the amount: '
accept p prompt 'Enter the chq facility(y/n): '
cl scr
declare
  x bank.accno%type:=&l;
  y bank.accname%type:='&y';
  z bank.amt%type:=&z;
  p bank.chqfac%type:='&p';
begin
  insert into bank values(x,y,z,p);
  commit;
  dbms_output.put_line('RECORD IS INSERTED INTO THE BANK TABLE');
end;
/
----------------------------------------------------------------------------
--program to retrieve the data from the bank table
accept x prompt 'Enter the accname whose details u want to display: '
declare
    x varchar2(20):='&x';
    bankrec bank%rowtype;
begin
  select * into bankrec from bank where accname=x;
  dbms_output.put_line('ACCOUNT DETAILS');
  dbms_output.put_line('Accno        : '||bankrec.accno);
  dbms_output.put_line('Accname  : '||bankrec.accname);
  dbms_output.put_line('Amt            : '||bankrec.amt);
  dbms_output.put_line('Chq            : '||bankrec.chqfac);
exception
    when no_data_found then
    dbms_output.put_line('SORRY NO RECORD FOUND WITH THIS NAME');
    when too_many_rows then
    dbms_output.put_line('SORRY MORE THAN ONE RECORD FOUND WITH THIS NAME');
end;
/
---------------------------------------------------------------------------------
--program to insert the data into employee
declare
  x employee.eno%type:=&eno;
  y employee.ename%type:='&ename';
  z employee.esal%type:=&esal;
  d employee.design%type:='&design';
  p employee.hra%type;
  q employee.da%type;
  g employee.gross%type;
begin
  p:=(z*5)/100;
  q:=(z*3.5)/100;
  g:=z+p+q;
  insert into employee values(x,y,z,d,p,q,g);
  dbms_output.put_line('RECORD INSERTED INTO EMPLOYEE TABLE BY DOING DATABASE CALCULATIONS');
  commit;
end;
/
--------------------------------------------------------------------------
--zero_divide exception
--raises when a number is divided by zero
accept x prompt 'Enter a no: '
accept y prompt 'Enter a no: '
declare
  x number:=&x;
  y number:=&y;
  z number;
begin
  z:=x/y;
  dbms_output.put_line('x  : '||x);
  dbms_output.put_line('y  : '||y);
  dbms_output.put_line('z  : '||z);
exception
    when zero_divide then
          dbms_output.put_line('SORRY DIVISION BY ZERO IS NOT DEFINED');
end;
/
-------------------------------------------------------------------------
--program to retrieve the data from the bank table
--no_data_found exception
--raises when no record is found by the fetch statement
--too_many_rows
--raises when more than one record is found by the fetch statement
accept x prompt 'Enter the accname whose details u want to display: '
declare
    x varchar2(30):='&x';
    a bank%rowtype;
begin
  select * into a from bank where accname=x;
  dbms_output.put_line('ACCOUNT DETAILS');
  dbms_output.put_line('Accno        : '||a.accno);
  dbms_output.put_line('Accname  : '||a.accname);
  dbms_output.put_line('Amt            : '||a.amt);
  dbms_output.put_line('Chq            : '||a.chq);
  exception
  when no_data_found then
    dbms_output.put_line('SORRY NO RECORD FOUND WITH THIS NAME');
  when too_many_rows then
    dbms_output.put_line('MORE THAN ONE RECORD FOUND WITH THIS NAME');
end;
/
------------------------------------------------------------------------------
--to insert the data into bank table using pl/sql
--dup_val_on_index exception
--raises when duplicate record is inserted into the col having primary/unique cons
accept x prompt 'Enter accno : '
accept y prompt 'Enter the accname: '
accept z prompt 'Enter the amount: '
accept p prompt 'Enter the chq facility(y/n): '
declare
  x bank.accno%type:=&x;
  y bank.accname%type:='&y';
  z bank.amt%type:=&z;
  p bank.chqfac%type:='&p';
begin
  insert into bank values(x,y,z,p);
  commit;
  dbms_output.put_line('RECORD IS INSERTED INTO THE BANK TABLE');
  exception
  when dup_val_on_index then
  dbms_output.put_line('SORRY NO DUPLICATE RECORD IS ALLOWED AS ACCNO IS SET TO PRIMARY KEY');
end;
/
-----------------------------------------------------------------------------------
--value_error
--raises when the size of variable declared is smaller than the size of the value entered
declare
  x number(2);
begin
  select empno into x from emp where ename='&ename';
  dbms_output.put_line('The Employee is :  '||x);
  exception
  when value_error then
  dbms_output.put_line('sorry size of x is less than size of empno');
  when no_data_found then
  dbms_output.put_line('sorry no record found with this name');
end;
/
--------------------------------------------------------------------------------
--invalid_number
--raises when mismatch of datatype takes place i.e charcter is entered into a --number
declare
  x number;
begin
  select to_number('&a') into x from dual;
  dbms_output.put_line('x  : '||x);
  exception
  when invalid_number then
  dbms_output.put_line('SORRY MISMATCH OF DATATYPE');
end;
/
---------------------------------------------------------------------------------
--main program in exception
declare
    x number:=&x;
    y number:=&y;
    z number;
begin
    z:=x/y;
    dbms_output.put_line('x  : '||x||'  '||'y  : '||y||'  '||'z    : '||z);
    exception
      when zero_divide then
              dbms_output.put_line('SORRY DIVISION BY ZERO IS NOT ALLOWED');
      begin
                x:=10;
                y:=3;
                z:=x/y;
              dbms_output.put_line('x  : '||x||'  '||'y  : '||y||'  '||'z    : '||z);
      end;
end;
/

-------------------------------------------------------------------------------------
--predefined exception example
accept no prompt 'Enter the empno whose details you want: '
declare
  n number:=&no;
  x emp%rowtype;
  gross number;
begin
  select * into x from emp where empno=n;
  dbms_output.put_line('Empno        : '||x.empno);
  dbms_output.put_line('Ename        : '||x.ename);
  dbms_output.put_line('Esal            : '||x.sal);
  dbms_output.put_line('Comm          : '||x.comm);
  gross:=x.sal+nvl(x.comm,0);
  dbms_output.put_line('Gross salary : '||gross);
exception
  when no_data_found then
  dbms_output.put_line('sorry no employee exists with this empno');
end;
/
-----------------------------------------------------------------------------
--SIMPLE CALCULATOR

ACCEPT X PROMPT 'ENTER 1ST_NO  : '
ACCEPT Y PROMPT 'ENTER 2ND_NO  : '

CL SCR

DECLARE
X NUMBER:=&X;
Y NUMBER:=&Y;
RES NUMBER;
NO EXCEPTION; --USERDEFINED EXCEPTION
BEGIN
IF X=0 OR Y=0 THEN
  RAISE NO;
END IF;

RES:=X+Y;
DBMS_OUTPUT.PUT_LINE('ADDITION RESULT  : '||RES);
RES:=X-Y;
DBMS_OUTPUT.PUT_LINE('SUBTRACTION RESULT  : '||RES);
RES:=X*Y;
DBMS_OUTPUT.PUT_LINE('MULTIPLICATION RESULT  : '||RES);
RES:=X/Y;
DBMS_OUTPUT.PUT_LINE('DIVISION RESULT  : '||RES);
 
EXCEPTION
  WHEN ZERO_DIVIDE THEN
DBMS_OUTPUT.PUT_LINE('SORRY YOU CANNO DIVIDE BY ZERO');
  WHEN NO THEN
DBMS_OUTPUT.PUT_LINE('SORRY YOU ARE NOT ALLOWED TO ENTER ZERO VALUE');
END;
/
--------------------------------------------------------------------------
--explicit cursor
declare
  cursor mycur1 is select * from emp where job='&job';
  emprec emp%rowtype;
begin
  open mycur1;
  loop
        fetch mycur1 into emprec;
        exit when mycur1%notfound;
        dbms_output.put_line(emprec.empno||'  '||emprec.ename||'  '||emprec.job||'  '||emprec.sal||'  '||emprec.comm||'  '||emprec.deptno);
  end loop;
  close mycur1;
end;
/
---------------------------------------------------------------------------------
--cursor to retrieve multiple records from multiple tables
declare
  cursor mycur1 is select empno,ename,sal,job,dname,loc from emp,dept where emp.deptno=dept.deptno and emp.job='SALESMAN';
  eno emp.empno%type;
  ename emp.ename%type;
  sal emp.sal%type;
  design emp.job%type;
  deptname dept.dname%type;
  venue dept.loc%type;
begin
dbms_output.put_line('EMPNO'||'    '||'ENAME'||'  '||'SAL'||'  '||'JOB'||'  '||'DNAME'||' 
'||'LOC');
dbms_output.put_line('________________________________________________');
  open mycur1;
  loop
          fetch mycur1 into eno,ename,sal,design,deptname,venue;
          exit when mycur1%notfound;
          dbms_output.put_line(eno||'  '||ename||'  '||sal||'  '||design||'  '||deptname||'  '||venue);
  end loop;
    close mycur1;
end;
/
-------------------------------------------------------------------------------
--multiple explicit cursor with multiple tables
declare
    cursor cur1 is select * from emp;
    cursor cur2 is select * from dept;
    emprec emp%rowtype;
    deptrec dept%rowtype;
begin
    open cur1;
    loop
          fetch cur1 into emprec;
          exit when cur1%notfound;
          dbms_output.put(emprec.empno||'  '||emprec.ename||'  '||emprec.sal);
          open cur2;
          loop
                fetch cur2 into deptrec;
                exit when cur2%notfound;
                        if emprec.deptno=deptrec.deptno then
                        dbms_output.put_line('  '||deptrec.dname||'    '||deptrec.loc);
                        end if;
          end loop;
          close cur2;
    end loop;
    close cur1;
end;
/
         
--------------------------------------------------------------------------------------
--explicit cursor
declare
  cursor cur1 is select * from emp10;
  emprec emp10%rowtype;
begin
  open cur1;
  loop
        fetch cur1 into emprec;
        exit when cur1%notfound;
            if emprec.sal<=1500 then
                        update emp10 set sal=sal+100 where deptno=emprec.deptno;
            elsif emprec.sal>1500 and emprec.sal<=3000 then
                        update emp10 set sal=sal+500 where deptno=emprec.deptno;
            else
                        dbms_output.put_line('SORRY NO SALARY INCREMENT');
            end if;
  end loop;
  close cur1;
end;
/
------------------------------------------------------------------------------------
--explicit cursor
declare
  cursor mycur1 is select * from emp where deptno=&deptno;
  emprec emp%rowtype;
begin
    open mycur1;
    loop
          fetch mycur1 into emprec;
          exit when mycur1%notfound;
          dbms_output.put_line(emprec.empno||'  '||emprec.ename||'  '||emprec.deptno);
    end loop;
    close mycur1;
end;
/
----------------------------------------------------------------------------------
--first simple pl/sql program
declare
    sno number(3):=&sno;
    sname varchar2(20):='&sname';
    fathername varchar2(20):='&fathername';
    course varchar2(20):='&course';
    feepaid number(5,2):=&feepaid;
begin
    dbms_output.put_line('STUDENT DETAILS');
    dbms_output.put_line('Sno        : '||sno);
    dbms_output.put_line('Sname    : '||sname||'  '||'S/O'||'  '||fathername);
    dbms_output.put_line('Course    : '||course||'  '||'Feepaid : '||feepaid);
  --dbms_output.put_line('Feepaid  : '||feepaid);
end;
/
--------------------------------------------------------------------------------
--example using for loop
accept n prompt 'Enter a postive no: '
declare
  n number:=&n;
begin
  for i in reverse 1..10 loop
        dbms_output.put_line(n || '  *  '||  i  ||  '  =  ' ||  (n*i));
  end loop;
end;
/
---------------------------------------------------------------------------------
--example using nested for loop
--for loop within another for loop is said to be nested for loop
begin
  for i in 3..5 loop
          for j in 1..10 loop
              dbms_output.put_line(i  || '  *  '|| j  ||  ' = ' || (i*j));
          end loop;
  end loop;
end;
/
------------------------------------------------------------------------------------
--function
create or replace function calsal(s number) return number
is
  hra number;
  da number;
  gross number;
begin
  hra:=(s*3)/100;
  da:=(s*3.25)/100;
  gross:=s+hra+da;
  return gross;
end;
/
----------------------------------------------------------------------------------
--function to calculate the bank interest
create or replace function calinterest(amt number)
return number
is
  interest number;
begin
  if amt<=5000 then
        interest:=amt+(amt*2.5/100);
  elsif amt>5000 and amt<=50000 then
        interest:=amt+(amt*7.25/100)+1000;
  else
        interest:=amt+(amt*9.27/100)+5000;
  end if;
    return (interest);
end;
/
---------------------------------------------------------------------------------
--simple if condition
accept no prompt 'Enter a numeric value: '
declare
    a number:=&no;
begin
  if a>0 then
  dbms_output.put_line('it is Positive no: '||a);
  elsif a<0 then
  dbms_output.put_line('It is Negative no: '||a);
  else
  dbms_output.put_line('It is zero : '||a);
  end if;
end;
/
--------------------------------------------------------------------------------------
-- program to calculate student report
declare
  sno number:=&sno;
  sname varchar2(20):='&sname';
  sub1 number:=&sub1;
  sub2 number:=&sub2;
  sub3 number:=&sub3;
  tot number;
  average number;
  grade varchar2(20);
begin
  dbms_output.put_line('STUDENT REPORT');
  dbms_output.put_line('SNO  : '||sno);
  dbms_output.put_line('SNAME : '||sname);
  dbms_output.put_line('SUB1 : '||sub1||'  '||'SUB2 : '||sub2||'  '||'SUB3 : '||sub3);
  if sub1<35 or sub2<35 or sub3<35 then
  dbms_output.put('STUDENT HAS FAILED IN ONE OR MORE SUBJECTS');
  dbms_output.put_line('      Grade : Fail');
  else
  tot:=sub1+sub2+sub3;
  average:=trunc(tot/3,0);
  dbms_output.put_line('Total Marks : '||tot);
  dbms_output.put_line('Average Marks: '||average);
  if average>=60 then
  grade:='FIRST';
  elsif average>=50 and average<60 then
  grade:='SECOND';
  else
  grade:='THIRD';
  end if;
  dbms_output.put_line('GRADE  : '||grade);
  end if;
  end;
/
----------------------------------------------------------------------------
--calling the procedure and function
declare
  eno number:=&empno;
  ename varchar2(20):='&ename';
  esal number:=&esal;
  egross number;
begin
  line;  --procedure called
  dbms_output.put_line('EMPLOYEE DETAILS');
  line; --procedure called
  dbms_output.put_line('Empno      : '||eno);
  dbms_output.put_line('Ename      : '||ename);
  dbms_output.put_line('Esal          : '||esal);
  egross:=calsal(esal); --function called
  dbms_output.put_line('Egross      : '||egross);
  line; --procedure called
end;
/
---------------------------------------------------------------------------------
--calling the procedure and function
declare
  accno number:=&accno;
  accname varchar2(20):='&accname';
  principle_amt number:=&amount;
  gross_amt number;
begin
  line;
  dbms_output.put_line('ACCOUNT DETAILS');
  line;
  dbms_output.put_line('Accno    : '||accno);
  dbms_output.put_line('Accname: '||accname);
  dbms_output.put_line('Principle Amount : '||principle_amt);
  gross_amt:=calinterest(principle_amt);
  dbms_output.put_line('Gross Amount : '||gross_amt);
  line;
end;
/
----------------------------------------------------------------------------
--pragma exception
declare
  no student1.sno%type:=&sno;
  name student1.sname%type:='&sname';
  sub student1.course%type:='&sub';
  usid student1.unid%type:=&usid;
  par_not_found exception; --userdefined exception
  no_dup_allowed exception; --userdefined exception
  pragma exception_init(par_not_found,-02291);
  pragma exception_init(no_dup_allowed,-00001);
begin
  insert into student1 values(no,name,sub,usid);
  dbms_output.put_line('RECORD INSERTED INTO STUDENT');
  exception
  when par_not_found then
  dbms_output.put_line('SORRY NO PARENT RECORD EXIST WITH THIS UNIVNO');
  when no_dup_allowed then
  dbms_output.put_line('SORRY NO DUPLICATE SNO IS ALLOWED');
end;
/
-----------------------------------------------------------------------------
--pragma exception
accept univno prompt 'Enter the UniversityNo you want to delete: '
declare
  no number:=&univno;
  child_found exception; --userdefined exception
  pragma exception_init(child_found,-02292);
begin
  delete from university where univno=no;
  dbms_output.put_line('UNIVERSITY RECORD DELETED');
  exception
  when child_found then
  dbms_output.put_line('SORRY CANNOT DELETE THE PARENT RECORD WHICH HAS CHILD DEPENDENT');
end;
/
------------------------------------------------------------------------
--simple example showing procedure
create or replace procedure line
is
  begin
    for i in 1..45 loop
      dbms_output.put('*');
    end loop;
    dbms_output.put_line('');
  end;
/
-----------------------------------------------------------------------------
  --raise application error
declare
  no number(3):=&no;
begin
  if no>100 then
    raise_application_error(-20010,'SORRY NO SHOULD BE LESS THAN 100');
  end if;
  dbms_output.put_line('no    : '||no);
end;
/
-------------------------------------------------------------------------
--user defined exception
declare
  no student.sno%type:=&no;
  name student.sname%type:='&name';
  m1 student.sub1%type:=&m1;
  m2 student.sub2%type:=&m2;
  m3 student.sub3%type:=&m3;
  t student.total%type;
  a student.average%type;
  c student.grade%type;
  minmark exception;  --userdefined exceptions
  maxmark exception; --userdefined exceptions
begin
  if m1<35 or m2<35 or m3<35 then
  raise minmark;
  end if;
  if m1>100 or m2>100 or m3>100 then
  raise maxmark;
  end if;
  t:=m1+m2+m3;
  a:=trunc(t/3,0);
  if a>=60 then
  c:='first';
  elsif a>=50 and a<60 then
  c:='second';
  else
  c:='third';
  end if;
  insert into student values(no,name,m1,m2,m3,t,a,c);
  dbms_output.put_line('STUDENT RECORD INSERTED');
  commit;
  exception
  when minmark then
  dbms_output.put_line('MARKS LESS THAN 35 NOT ALLOWED');
  when maxmark then
  dbms_output.put_line('MARKS MORE THAN 100 NOT POSSIBLE');
  when dup_val_on_index then
  dbms_output.put_line('SORRY DUPLICATE RECORD IS  NOT ALLOWED');
end;
/
-------------------------------------------------------------------------------
--userdefined exception
declare
no bank.accno%type:=&accno;
name bank.accname%type:='&accname';
amount bank.amt%type:=&amt;
cheque bank.chqfac%type:='&chq';
negbal exception;  --userdefined exception
minbal exception;  --userdefined exception
chqfail exception;  --userdefined exception
begin
  if amount<=0 then
  raise negbal;
  end if;
  if amount>0 and amount<=999 then
    raise minbal;
  end if;
  if cheque not in ('Y','y','N','n') then
  raise chqfail;
  end if;
insert into bank values(no,name,amount,cheque);
dbms_output.put_line('RECORD INSERTED INTO BANK TABLE');
exception
  when dup_val_on_index then
  dbms_output.put_line('SORRY NO DUPLICATE ACCNO IS ALLOWED');
  when negbal then
  dbms_output.put_line('SORRY AMOUNT LESS THAN 0 IS NOT ENTERTAINED');
  when minbal then
  dbms_output.put_line('MINIMUM BALANCE SHOULD BE >=1000');
  when chqfail then
    dbms_output.put_line('CHEQUE FACILITY SHOULD BE Y/N,y/n');
end;
/
---------------------------------------------------------------------------------
--while loop example
declare
  n number:=1;
  s number:=0;
begin
  while n<=10 loop
    dbms_output.put_line('n      : '||n);
    s:=s+n;
    n:=n+1;
    end loop;
  dbms_output.put_line('sum  : '||s);
end;
/
-----------------------------------------------------------------------------------
--simple pl/sql program
declare
  name varchar2(20):='&name';
begin
  dbms_output.put_line('Hello Mr . '|| name);
  dbms_output.put_line('WELCOME TO PL/SQL PROGRAMING');
end;
/
-------------------------------------------------------------------------------
--simple program to display the student details
declare
  sname varchar2(20):='&a';
  course varchar2(20):='&b';
  institute varchar2(20):='&c';
  feepaid number(5,2):=&d;
  country varchar2(20):='&e';
begin
  dbms_output.put_line('STUDENT DETAILS');
  dbms_output.put_line('SNAME      : '||sname);
  dbms_output.put_line('COURSE    : '||course||'    '||'INSTITUTE  : '||institute);
  dbms_output.put_line('FEEPAID    : '||feepaid);
  dbms_output.put_line('COUNTRY  : '||country);
end;
/
 
------------------------------------------------------------------------------------
--simple program to perform arithmetic operations
accept x prompt 'Enter the numeric value: '
accept y prompt 'Enter the numeric value: '
declare
  x number:=&x;
  y number:=&y;
  res number;
begin
  dbms_output.put_line(x);
  dbms_output.put_line(y);
    res:=x+y;
  dbms_output.put_line('addition  result : '||res);
    res:=x-y;
  dbms_output.put_line('Substraction result : '||res);
    res:=x/y;
  dbms_output.put_line('Division : '||res);
end;
/
------------------------------------------------------------------------------------
--simple loop
declare
  n number:=1;
  s number:=0;
begin
  loop
      dbms_output.put_line('n    : '||n);
      s:=s+n;
      n:=n+1;
  exit when n>10;
    end loop;
  dbms_output.put_line('sum  : '||s);
end;
/
----------------------------------------------------------------------------------
--simple loop example
accept no prompt  'Enter the Positive no: '
declare
  n number:=&no;
  rev number:=0;
  rem number;
begin
loop
  rem:=mod(n,10);
  rev:=(rev*10)+rem;
  n:=trunc(n/10,0);
  exit when n<=0;
end loop;
dbms_output.put_line('Reverse of the no: '||rev);
end;
/
------------------------------------------------------------------------------------------
--program to perform arithmetic operations
accept x prompt 'Enter a Positive no: '
accept y prompt 'Enter a Positive no: '
declare
  a number:=&x;
  b number:=&y;
begin
  dbms_output.put_line('addition    : '|| (a+);
  dbms_output.put_line('substraction    : '|| (a-);
  dbms_output.put_line('Multiplication    : '|| (a*);
  dbms_output.put_line('Division    : '|| (a/:ph34r:);
  dbms_output.put_line('Division with truncate: '|| trunc(a/b,0));
  dbms_output.put_line('power    : '|| power(a,);
end;

-----------------------------------------------------------------------------------------
--program to calculate the employee details
declare
  eno number(3):=&eno;
  ename varchar2(20):='&ename';
  esal number(7,2):=&esal;
  hra number;
  da number;
  gross number;
begin
  dbms_output.put_line('EMPLOYEE DETAILS');
  dbms_output.put_line('Eno          : '||eno);
  dbms_output.put_line('Ename      : '||ename);
  dbms_output.put_line('Esal          : '||esal);
  hra:=(esal*3.5)/100;
  dbms_output.put_line('Hra            : '||hra);
  da:=(esal*4)/100;
  dbms_output.put_line('Da              : '||da);
  gross:=esal+hra+da;
  dbms_output.put_line('Gross        : '||gross);
end;
/
-----------------------------------------------------------------------------------------

شكرا لك سعادة / العميد

ومرحبا بك عضوا مميزا فى المنتدى

واتوقع ان هناك المزيد من المشاركات المميزه ..

اشكرك على هذه المشاركة المميزة ،،

مشكور على هذا الجهد

كيف أنفذ هذا البرنامج واحصل على المخرجات؟