pl/sql programming

1) Reverse of a Given Number

Declare
num number:=&n;
rev number:=0;
Begin
while(num>0)
Loop
rev=rev*10+mod(num,10);
num=num/10;
End loop;
Dbms_Output.Put_Line("Reverse of number is '|| rev);
End;

2) Factorial of a Given number
Declare
 num   number:= #
 fact number:= 1;
 temp number;
 begin
   temp := num;
    while (num > 0)
loop
    fact := fact * num;
     num   := num - 1;
  end loop;
   Dbms_Output.Put_line('factorial of ' || num || '  is ' || fact);
end;

3) Check a given numbers is Amstrong or not
declare
  num number;
  tot number:=0;
  var1 number;
  var2 number;
begin
  num:=#
  tmp:=num;
  while tmp>0
  loop
    var1:=tmp mod 10;
    tot:= tot + (var1*var1*var1);
    tmp:=tmp/10;
  end loop;
  if(tot==num) then
    dbms_output.put_line(num||' is armstrong no');
  else
    dbms_output.put_line(num||' is not a armstrong no');
  end if
end;

4) Print Fibonacci Series
DECLARE
    num number := &n;
    n1 number := 0;
    n2 number := 1;
    n3 number;
BEGIN
    dbms_output.put_line(n1);
    dbms_output.put_line(n2);
    for i in 3..num
loop
     n3 := n1 + n2;
     dbms_output.put_line(n3);
     n1 := n2;
     n2 := n3;
end loop;
END;

5) Evaluate Prime number between 1..100
begin
 for i in 1..100 loop
 if i in (1,5,7) then
 dbms_output.put_line(' These are known prime numbers '||i);
 end if;
 if i not in (1,5,7) then
 if mod(i,3)=0 or mod(i,6)=0 or mod(i,9)=0 then
 null;
  elsif mod(i,2)=0 or mod(i,4)=0 or mod(i,8)=0 then
 null;
 elsif mod(i,5)=0 or mod(i,10)=0 then
 null;
  elsif mod(i,7)=0 then
 null;
 else
 dbms_output.put_line(' Is this a prime number?? '||i);
 end if;
 end if;
 end loop;
 end;

6) Using Iteration
Declare
NUM number:=&N;
begin
if(NUM>0)
dbms_output.put_line (NUM||'Natural Number');
else
dbms_output.put_line (NUM||'Wrong Number');
exit when NUM1=100;
end if;
end;

7) How to define Variable(just adding two numbers)
Declare
NUM1 number:=20;
NUM2 number:=50;
SUM number:=0;
Begin
SUM := NUM1+NUM2;
dbms_output.put_line (SUM||',');
end;

8) Hello World in PlSql
Begin
dbms_output.put_line ('Hello World');
end;

9) Inserting Using Procedure and Cursor
create  or replace procedure p1 as
   CURSOR c_emp IS
      SELECT *
        FROM emp
       WHERE sal< 5000;
BEGIN
   FOR i IN c_emp
   LOOP
      
insert into e1 values (i.sal);
   END LOOP;
END;

10) Implecit Cursor Example
DECLARE
employee_id employee.emp_id%TYPE := 1;
BEGIN
DELETE FROM employee
WHERE employee_id = employee_id;
If(SQL%FOUND)
 Dbms_Output.Put_Line('Row Deleted');
END;
/

11) How to Create procedure
Ctrate or replace procedure emp100
as
   CURSOR c_emp IS
      SELECT *
        FROM emp
       WHERE sal< 5000;
BEGIN
   FOR i IN c_emp
   LOOP
      dbms_output.put_line(i.sal);
insert into e1 values (i.sal);
   END LOOP;
END;

12) How to use substitution variables
DECLARE
v_sal NUMBER(9,2) := &p_annual_sal;
BEGIN
v_sal := v_sal/12;
DBMS_OUTPUT.PUT_LINE ('The monthly salary is ' ||
TO_CHAR(v_sal));
END;
/
DEFINE p_annual_sal = 60000

13) How to raise error
DECLARE
   e_rec emp%ROWTYPE;
   e1 EXCEPTION;
   sal1 emp.sal%TYPE;
BEGIN
   
   SELECT sal INTO sal1 FROM emp WHERE deptno = 30 AND ename = 'WARD';
   IF sal1 < 5000 THEN
   RAISE e1;
   END IF;
EXCEPTION
   WHEN no_data_found THEN
   RAISE_APPLICATION_ERROR (-20001, 'WARD is not there.');
WHEN e1 THEN
   RAISE_APPLICATION_ERROR (-20002, 'Less Salary.');
END;

14) Insert Update Delete in a Table
DECLARE
    v_sal emp.sal%TYPE;
BEGIN

    INSERT INTO emp VALUES (6, 'Tomcat', 1000);

    UPDATE emp SET sal = sal + 5000 WHERE empno = 6;

      SELECT sal INTO v_sal FROM employee WHERE empno = 6;

    DBMS_OUTPUT.PUT_LINE('Salary increased to ' || v_sal);

    DELETE FROM emp WHERE empno = 6;

      COMMIT;
END;