Practice PL/SQL Examples - Part 1
By Jaanve
1) Write a Program to Print 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) Multiplication table (eg 2)
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) Print One sided Pyramid
*
**
***
****
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;
Sneha 8 months ago
Really a nice and challenging one.... Keep them coming,!