PL/SQL Practice Example - Part 2

11)Count the Number of Characters in a word

DECLARE
PROCEDURE count_no_o(c VARCHAR2)
IS
cnt NUMBER := 0;
val NUMBER := 0;
inc NUMBER := 1;
BEGIN

LOOP
FOR i IN 1 .. inc
LOOP
val := INSTR(c,'o',1,inc);
END LOOP;

IF val = 0 THEN
dbms_output.put_line('No of O IS :' || cnt);
END IF;

EXIT WHEN val = 0;
cnt := cnt +1;
inc := inc + 1;
END LOOP;

END;

BEGIN
count_no_o('snoooooooooopy');
END;



12)Remove a letter form a word

DECLARE
PROCEDURE rem_o(n VARCHAR2)
IS
name1 VARCHAR2(5);
name2 VARCHAR2(5);
x NUMBER;
BEGIN
x := INSTR(n,'o',1,1);
name1 := SUBSTR(n,1,(x-1));
name2 := SUBSTR(n,(x+1),LENGTH(n));
dbms_output.put_line( name1 || name2);
END;

BEGIN
rem_o('snoopy');
END;


13)Check for palindrome

DECLARE
PROCEDURE chk_palin(n CHAR)
IS
len NUMBER;
half_way NUMBER;
char1 CHAR;
char2 CHAR;
chk NUMBER := 0;
num NUMBER;
tab NUMBER;
BEGIN
len := LENGTH(n);
<>
FOR i IN 1 .. (FLOOR(len/2))
LOOP
char1 := SUBSTR(n,i,1);
num := len - chk;
char2 := SUBSTR(n,num,1);

dbms_output.put_line(char1);
dbms_output.put_line(char2);
chk := chk +1;

IF char1 = char2 THEN
tab := 1;
ELSIF char1 != char2 THEN
tab := 0;
EXIT for_loop;
END IF;

END LOOP;
IF tab = 1 THEN
dbms_output.put_line('THIS IS A PALIDROME');
ELSE
dbms_output.put_line('this is not a palindrome');
END IF;
END;


BEGIN
chk_palin('abcdcba');
END;

14)Count number of characters

DECLARE
PROCEDURE check_char(name CHAR)
IS
extract_char CHAR;
incr_count NUMBER :=1;
no_of_char NUMBER :=0;
BEGIN
LOOP
extract_char := SUBSTR(name,incr_count,1);
IF extract_char IS NULL OR extract_char = '' THEN
EXIT WHEN extract_char IS NULL;
ELSE
no_of_char := no_of_char +1;
END IF;
incr_count := incr_count +1;
END LOOP;
dbms_output.put_line('no of chars:' || no_of_char);
END check_char;

BEGIN
check_char('abcd');

END;

15)Print reverse of name

DECLARE

PROCEDURE reverse_of_name(name CHAR)
IS
extract_char CHAR;
BEGIN

FOR i IN REVERSE 1 .. LENGTH(name)
LOOP
extract_char := SUBSTR(name,i,1);
dbms_output.put(extract_char);
END LOOP;
dbms_output.new_line;
END;

16)Change some value in a word (from jk001 to jk0025)

BEGIN
reverse_of_name('snoopy');
END;

BEGIN
FOR i IN 1 .. 25
LOOP
dbms_output.put_line('jk' ||'00' ||i);
END LOOP;
END;


17)Captalize alternate Characters (aBcDeFgHiJkLmNoPqR)

DECLARE
PROCEDURE sent_name(n VARCHAR2)
IS
extract_char VARCHAR2(10);
chk NUMBER := 0;
BEGIN
FOR i IN 1 .. LENGTH(n)
LOOP
extract_char := SUBSTR(n,i,1);
IF (i MOD 2) = 0 THEN
-- dbms_output.put(extract_char || ' This is Even');
dbms_output.put(UPPER(extract_char));
NULL;
ELSIF (i MOD 2) > 0 THEN
--dbms_output.put_line(extract_char || 'this is odd');
dbms_output.put((extract_char));
NULL;
END IF;
chk := chk +1;
END LOOP;
dbms_output.new_line;

END sent_name;
BEGIN
sent_name('abcdefghijklmnopqr');
END;


18)Add Number in a word (a0b1c2d3)

DECLARE

PROCEDURE sent_data(name VARCHAR2)
IS
extract_data CHAR;
BEGIN
FOR i IN 1 .. LENGTH(name)
LOOP
extract_data := SUBSTR(name,i,1);
dbms_output.put( extract_data || (i-1));
END LOOP;
dbms_output.new_line;
END;

BEGIN
sent_data('abcd');
END;


19)Captalize all the first characters in a Sentence


DECLARE

PROCEDURE capi_ini(n VARCHAR2)
IS
extract_str VARCHAR2(1);
chk NUMBER := 0;
BEGIN
FOR i IN 1 .. LENGTH(n)
LOOP
extract_str := SUBSTR(n,i,1);



--dbms_output.put_line('chk : ' || chk);
IF chk = 0 THEN
dbms_output.put(UPPER(extract_str));
ELSIF chk = 1 THEN
dbms_output.put(LOWER(extract_str));
END IF;

IF extract_str = ' ' THEN
chk := 0;
ELSE
chk := 1;
END IF;

END LOOP;
dbms_output.new_line;

END;


BEGIN
capi_ini('this IS a CHECK to SEE if every thing is working properly');
END;

20)tHIS iS tO cONVERT tEXT

DECLARE
ttext VARCHAR2(25) := 'this is to convert text';
len_of_text NUMBER;
extract_text VARCHAR2(1);
after_space VARCHAR2(1) := 'T';
change_text VARCHAR2(25);
--yes_cap VARCHAR2(1) := 'T';


BEGIN
len_of_text := LENGTH(ttext);

FOR for_check IN 1 .. len_of_text
LOOP
extract_text := SUBSTR(ttext, for_check,1);


IF extract_text = ' '
THEN
change_text := change_text || extract_text;
after_space := 'T';

ELSIF extract_text <> ' '
THEN
IF after_space = 'T'
THEN
change_text := change_text || LOWER(extract_text);
ELSIF after_space = 'F'
THEN
change_text := change_text || UPPER(extract_text);
END IF;
after_space := 'F';
END IF;

END LOOP;
dbms_output.put_line(change_text);

END;