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;

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;