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;