PL/SQL Practice Example - Part 1

1) Post 1 to 10 Numbers


DECLARE

PROCEDURE print_number( n1 NUMBER)
IS
tot NUMBER := n1 ;
BEGIN

FOR i IN 1 .. 10
LOOP
dbms_output.put_line(tot);
tot := tot + 1;
END LOOP;
END;

BEGIN
print_number(1);
END;




2)Check if it is an even or odd number


DECLARE

PROCEDURE check_even( numb1 NUMBER)
IS
BEGIN
IF numb1 MOD 2 = 0 THEN
dbms_output.put_line('this is an even number');
ELSE
dbms_output.put_line('this is an odd number');

END IF;
END;

BEGIN
check_even(24783957357925);

END;


3)Print multiplication table

DECLARE
PROCEDURE multi_of_two( numb1 NUMBER)
IS
c NUMBER := numb1;
BEGIN
LOOP

dbms_output.put_line (c);
c:= c * 2;

EXIT WHEN c > 50;
END LOOP;
END;

BEGIN
multi_of_two(2);
END;


4)Count the no of ‘o’ in a name

DECLARE
PROCEDURE count_a( name VARCHAR2)
IS
COUNT1 NUMBER:=0;
extract VARCHAR2(1);

BEGIN

FOR i IN 1 .. LENGTH(name)
LOOP
extract:= SUBSTR(name,i,1);

IF extract = 'o' THEN

COUNT1 := COUNT1 + 1;

END IF;

END LOOP;
dbms_output.put_line(COUNT1);

END;
BEGIN
count_a('snooooooooooooooooooopy');
END;

5)Count the number of characters and numbers in a given text

DECLARE

PROCEDURE cnt_char_numb(name VARCHAR2)

IS

extracted_letter VARCHAR2(1);

cnt_numb NUMBER :=0;

cnt_char NUMBER := 0;



BEGIN



FOR i IN 1 .. LENGTH(name)

LOOP

extracted_letter := SUBSTR(name,i,1);

IF TO_CHAR(extracted_letter) IN ('0','1','2','3','4','5','6','7','8','9')THEN

cnt_numb := cnt_numb + 1;

ELSE

cnt_char := cnt_char + 1;

END IF;

END LOOP;



dbms_output.put_line('no of number : ' || cnt_numb);

dbms_output.put_line('no of char : ' || cnt_char);

END;

BEGIN

cnt_char_numb('snoopy123');

END;


6)
*
**
***
****

DECLARE
PROCEDURE star(num NUMBER)
IS
chk NUMBER := 0;
BEGIN
LOOP
chk := chk + 1;
FOR i IN 1 .. chk
LOOP
dbms_output.put('*');
END LOOP;
dbms_output.new_line;
EXIT WHEN chk > num;
END LOOP;
END;
BEGIN
star(3);
END;


7)Sorting a given number

DECLARE
PROCEDURE sort_number(numb NUMBER)
IS
a NUMBER;
b NUMBER;
c NUMBER;
numb1 NUMBER := numb;
BEGIN

FOR i IN 1 .. LENGTH(TO_CHAR(numb1))
LOOP

IF c IS NOT NULL THEN
numb1 := REPLACE(numb1,c);
--dbms_output.put_line('AFTER REPLACE numb1: ' || numb1);
END IF;

a := SUBSTR(numb1,1,1);

--dbms_output.put_line('value of a:' || a);
FOR j IN 1 .. LENGTH(TO_CHAR(numb1))
LOOP
b := SUBSTR(numb1,j,1);

IF b < a THEN
a := b;
END IF;
END LOOP;
c := a;
dbms_output.put_line(a);

END LOOP;
END;

BEGIN

sort_number(9834567210);
END;

8)
****
***
**
*

DECLARE

PROCEDURE reverse_star(numb NUMBER)
IS
chk NUMBER := numb;
BEGIN
LOOP
FOR i IN REVERSE 1 .. chk
LOOP
dbms_output.put('*');
END LOOP;
chk := chk -1;
dbms_output.new_line;
EXIT WHEN chk < 1;
END LOOP;
END;

BEGIN
reverse_star(4);
END;


9)

********
******
****
**
*

DECLARE
PROCEDURE pyramid(n NUMBER)
IS
star VARCHAR2(1) := '*';
chk NUMBER := n;
sp NUMBER := 0;
BEGIN

LOOP
FOR i IN 1 .. chk
LOOP
dbms_output.put(star);
END LOOP;
sp := sp+ 1;
dbms_output.new_line;
IF chk > 1 THEN
FOR j IN 1 .. sp
LOOP
dbms_output.put(' ');
END LOOP;
END IF;
IF chk != 2 THEN
chk := chk - 2;
ELSIF chk = 2 THEN
chk := 1;
END IF;
EXIT WHEN chk < 1;
END LOOP;

END;

BEGIN
pyramid(8);
END;

10)


*
***
*****
*******
*********



DECLARE

PROCEDURE reverse_pyramid(n NUMBER)
IS
chk NUMBER := 1;
sp NUMBER := (n/2)+1;
BEGIN
LOOP
FOR j IN REVERSE 1 .. sp
LOOP
dbms_output.put(' ');
END LOOP;

FOR i IN 1 .. chk
LOOP
dbms_output.put('*');
END LOOP;
dbms_output.new_line;
chk := chk +2;
sp := sp - 1;
EXIT WHEN chk > 10 ;
END LOOP;
END;


BEGIN
reverse_pyramid(10);
END;