Thursday, September 17, 2009

Draw Charminar using Oracle PL/SQL

DECLARE

given_front_space NUMBER := 50;
given_space_btn_minars NUMBER := 150;
total_building_width NUMBER := 150 + 9+9; -- calc the total width, middle space and the both the dome with with the wall star

space_btn_front_dome1 NUMBER;
space_btn_front_dome2 NUMBER;




/* ####################### Procedure to get the cursor down by certain lines ##################*/
PROCEDURE print_blank_lines(no_of_blank_lines NUMBER)
IS
BEGIN
FOR blank_lines IN 1 .. no_of_blank_lines
LOOP
DBMS_OUTPUT.NEW_LINE;
END LOOP;
END;

/* ####################### END OF Procedure to get the cursor down by certain lines ##################*/





/* #################### Procedure to print space ############################*/
PROCEDURE print_space(len_of_space NUMBER)
IS
BEGIN
FOR amt_of_space IN 1 .. len_of_space
LOOP
DBMS_OUTPUT.PUT(' ');
END LOOP;
END;

/* #################### Procedure to print only one star ############################*/





/* ################ Procedure to print a 2 star and a 1 space ################################*/
PROCEDURE two_star_and_1space(space_btn_stars NUMBER)
IS
BEGIN
DBMS_OUTPUT.PUT('*');
print_space(space_btn_stars);
DBMS_OUTPUT.PUT('*');

END;

/* ################ Procedure to print a star and a space ################################*/



/*################## Procedure to print ! #################################################*/
PROCEDURE single_special(no_of_single NUMBER)
IS
BEGIN
FOR single_char in 1 .. no_of_single
LOOP
DBMS_OUTPUT.PUT('!');
END LOOP;
END;

/*################## end of Procedure to print ! #################################################*/


/*################## Procedure to print * #################################################*/
PROCEDURE single_star(no_of_star NUMBER)
IS
BEGIN
FOR single_char in 1 .. no_of_star
LOOP
DBMS_OUTPUT.PUT('*');
END LOOP;
END;

/*################## end of Procedure to print ! #################################################*/






/* ################### Procedure to print single ~ #########################################*/
PROCEDURE single_tilda(no_of_tilda NUMBER)
IS
BEGIN
FOR single_tilda_loop in 1 .. no_of_tilda
LOOP
DBMS_OUTPUT.PUT('~');
END LOOP;
END;
/* ################### end of Procedure to print single ~ #########################################*/


/* ################## Procedure to print hash # ################################################# */
PROCEDURE single_hash(no_of_hash NUMBER)
IS
BEGIN
FOR single_hash_loop in 1 .. no_of_hash
LOOP
DBMS_OUTPUT.PUT('#');
END LOOP;
END;

/* ################## end of Procedure to print hash # ################################################# */



/* ###########################PROCEDURE to pring percent % #############################################*/
PROCEDURE single_percent(no_of_percent NUMBER)
IS
BEGIN
FOR single_percent_loop in 1 .. no_of_percent
LOOP
DBMS_OUTPUT.PUT('%');
END LOOP;
END;


/* ########################### end of PROCEDURE to pring percent % #########################################*/




/* ###########################PROCEDURE to pring line | #############################################*/
PROCEDURE single_line(no_of_line NUMBER)
IS
BEGIN
FOR single_percent_loop in 1 .. no_of_line
LOOP
DBMS_OUTPUT.PUT('|');
END LOOP;
END;


/* ###########################end of PROCEDURE to pring line | #############################################*/




/* #################### Procedure to print ~! in a set ##################################*/
PROCEDURE special_char_verti(no_of_special NUMBER)
IS
BEGIN
FOR special_char in 1 .. no_of_special
LOOP
DBMS_OUTPUT.PUT('~!');
END LOOP;
END;
/* #################### end of Procedure to print !~ in a set ##################################*/





--- ~~~~~~~ printing begins from here ~~~~~~~~~~~-----





/* ################Procedure to print the pines of Charminar #################################*/
PROCEDURE front_pines
IS

BEGIN

-- print the 1st and 2nd pine tips
print_space(given_front_space); -- print the space in front of the tip of the pine
two_star_and_1space(given_space_btn_minars); -- prints the tip of the two front pines
dbms_output.new_line;


-- reduce the front space and the middle space by 1 and 2 to get the slant look of the pine
given_front_space := given_front_space - 1;
given_space_btn_minars := given_space_btn_minars - 2;


FOR tip_of_loop in 1 .. 2
LOOP
-- prints the 1st pine in the front two pines
print_space(given_front_space);
two_star_and_1space(1);

-- prints the 2nd pine in the front two pines
print_space(given_space_btn_minars);
two_star_and_1space(1);

dbms_output.new_line;
END LOOP;

END;


/* ################End of Procedure to print the pines of Charminar #################################*/








/*############### Procedure to print the two domes in the front two minars ########################*/
PROCEDURE front_domes
IS
BEGIN

-- first front dome
given_front_space := given_front_space - 2; -- remove 2,coz bulge from pine to dome
space_btn_front_dome1 := 2 + 3 + 2; --ie space reduced frm front space, pines two stars and one space,
-- and 4 space before the last star


-- print first dome with the front space
print_space(given_front_space);
two_star_and_1space(space_btn_front_dome1);





-- second front dome
given_space_btn_minars := given_space_btn_minars - space_btn_front_dome1; -- remove the btn_dome1,coz
-- the center space starts from the middle and so consider the middle space 3 + two 2 sides
space_btn_front_dome2 := 2 + 3 + 2;

-- print the second dome with the front space

print_space( given_space_btn_minars);
two_star_and_1space(space_btn_front_dome2);


dbms_output.new_line;




-- first front dome for slant look
given_front_space := given_front_space - 2;
space_btn_front_dome1 := space_btn_front_dome1 + 2 + 2;

-- second front dome for slant look
given_space_btn_minars := given_space_btn_minars - 4 ; -- remove only 4 coz, dont have to consider the center space
-- coz, the center space is already removed from the first star of the dome
space_btn_front_dome2 := space_btn_front_dome2 + 2 + 2; -- space_btn_front_dome1 = space_btn_front_dome2


FOR top_domes in 1 .. 2
LOOP

-- print first dome with the front space
print_space(given_front_space);
two_star_and_1space(space_btn_front_dome1);



-- print the second dome with the front space

print_space( given_space_btn_minars);
two_star_and_1space(space_btn_front_dome2);

dbms_output.new_line;




END LOOP;
END;
/*############### END of Procedure to print the two domes in the front two minars ########################*/



/* ###################### Procedure to print the first horizontal line in front minars ################# */
PROCEDURE first_hori_frnt_minar(p_front_space NUMBER,p_space_btn_front_dome NUMBER,p_space_btn_minars NUMBER)
IS
BEGIN

-- to print the 1st vertical bar in the 1st front minar
print_space(p_front_space);
single_special(1);

special_char_verti(p_space_btn_front_dome/3);


-- to print the 1st vertical bar in the 2nd front minar
print_space(p_space_btn_minars);
single_special(1);

special_char_verti(p_space_btn_front_dome/3);

dbms_output.new_line;
END;
/* ###################### end of Procedure to print the first vertical line in front minars #################*/




/* ################# Procedure to print the top poles of hte front minars ######################## */
PROCEDURE top_pole_frnt_minars(p_front_space NUMBER,p_space_btn_front_dome NUMBER,p_space_btn_minars NUMBER, len_of_pillar NUMBER)
IS
BEGIN

FOR pillar_len in 1 .. len_of_pillar
LOOP
-- print the front space, before printing the minar
print_space(p_front_space);
two_star_and_1space(p_space_btn_front_dome);

-- print the middle space, before printing the 2nd minar
print_space(p_space_btn_minars);
two_star_and_1space(p_space_btn_front_dome);

dbms_output.new_line;

END LOOP;
END;

/* ################# END OF Procedure to print the top poles of hte front minars ######################## */


/* ################Procedure to print the second horizontal line in the front minars ###################### */

PROCEDURE second_hori_frnt_minar
IS
snd_vert_frnt_space NUMBER := given_front_space ;
verti_lent NUMBER := space_btn_front_dome1;
snd_verti_btn_minar NUMBER := given_space_btn_minars ;

BEGIN
-- prints the vertical line in the first minar
print_space(snd_vert_frnt_space - 2);
single_special(verti_lent+ 7);

-- prints the vertical line in the second minar
print_space(snd_verti_btn_minar - 4);
single_special(verti_lent+ 7);
DBMS_OUTPUT.NEW_LINE;

END;

/* ################END OF Procedure to print the second vertical line in the front minars ###################### */



/* ############### PROCEDURE to print the bottom part of front minar, with behind minars ################### */
PROCEDURE front_down_hind_top
IS
fbhu_front_space NUMBER := given_front_space;
fbhu_dome_space NUMBER := space_btn_front_dome1;
fbhu_middle_Sminar NUMBER := given_space_btn_minars/2;
fbhu_Sminar_frontSpace NUMBER := given_space_btn_minars/4 ;
fbhu_Sminar_backSpace NUMBER := given_space_btn_minars/4 - 4; -- -4 for cosmetic purpose
fbhu_space_btn_Sminar NUMBER := 4;


-- print the first BIG minar
PROCEDURE top4_big_minar1
IS
BEGIN

print_space(fbhu_front_space); -- front space of the the bottom minar
two_star_and_1space(fbhu_dome_space); -- prints the stars with the dome space in btn
END;


-- print the last BIG minar
PROCEDURE top4_big_minar2
IS
BEGIN


print_space( fbhu_Sminar_backSpace - 3); -- front space of the the bottom minar,-3 for cosmetic purpose
two_star_and_1space(fbhu_dome_space); -- prints the stars with the dome space in btn
END;



-- prints the 1st small minar
PROCEDURE top4_small_minar1
IS
BEGIN
print_space(fbhu_Sminar_frontSpace -2);
two_star_and_1space(fbhu_space_btn_Sminar);
END;


-- prints the 2nd small minar
PROCEDURE top4_small_minar2
IS
BEGIN
print_space(fbhu_middle_Sminar - 4);
two_star_and_1space(fbhu_space_btn_Sminar);
END;



PROCEDURE build_verti_4pillars
IS
BEGIN
-- BUILD THE PILLARS OF ALL THE 4 MINARS
FOR top_four_minars in 1 .. 2
LOOP
top4_big_minar1;
top4_small_minar1;

top4_small_minar2;
top4_big_minar2;

DBMS_OUTPUT.NEW_LINE;


END LOOP;
END;



BEGIN

top4_big_minar1; -- prints the first big minar

-- prints the first SMALL minar pine
print_space(fbhu_Sminar_frontSpace);
DBMS_OUTPUT.PUT('^');


-- prints the second SMALL minar pine
print_space(fbhu_middle_Sminar);
DBMS_OUTPUT.PUT('^');


-- print the last BIG minar
print_space(fbhu_Sminar_backSpace); -- front space of the the bottom minar
two_star_and_1space(fbhu_dome_space); -- prints the stars with the dome space in btn -- pritn the last big minar


DBMS_OUTPUT.NEW_LINE;


-- BUILD THE PILLARS OF ALL THE 4 MINARS

build_verti_4pillars;



-- BUILD HIND MINAR VERTICAL DESIGN WITH TILDA

top4_big_minar1; -- prints the first

print_space(fbhu_Sminar_frontSpace -2);
single_tilda(3); -- prints the vertical line in the first small minar

print_space(fbhu_middle_Sminar - 4);
single_tilda(3); -- prints the vetical line in the second small minar

-- print the last BIG minar
print_space( fbhu_Sminar_backSpace - 4); -- front space of the the bottom minar,-3 for cosmetic purpose
two_star_and_1space(fbhu_dome_space); -- prints the stars with the dome space in btn

dbms_output.new_line;

-- Prints the bottom of the

build_verti_4pillars;

-- BUILD THE LAST HORIZONTAL LINE OF THE MINARS

-- 1 big pillar hash
print_space(fbhu_front_space-1); -- front space of the the bottom minar
single_hash(7);

-- 1 small pillar hash
print_space(fbhu_Sminar_frontSpace - 4);
single_hash(3);

-- 2 small pillar hash

print_space(fbhu_middle_Sminar - 4);
single_hash(3);
-- 2 big pillar hash

print_space(fbhu_Sminar_backSpace - 4); -- front space of the the bottom minar
single_hash(7);


dbms_output.new_line;




END;

/* ############### END OF PROCEDURE to print the bottom part of front minar, with behind minars ################### */



/* ################ PROCEDURE to print the top desing of the building ####################################### */
PROCEDURE curve_top_building
IS
BEGIN
-- top CURVE desing of the building
print_space(given_front_space);
single_tilda(72);
dbms_output.new_line;

END;

/* ################ end of PROCEDURE to print the top desing of the building ####################################### */





/* ################ Procedure to print the top most desing after the curve #################################*/
PROCEDURE top_building_design
IS
BEGIN
FOR top_desing_loop in 1 .. 4
LOOP

print_space(given_front_space);
single_percent(63);
dbms_output.new_line;
END LOOP;
END;

/* ################ END OF Procedure to print the top most desing after the curve ############################*/





/* ############### Procedure to print the bottom pillar in the building ############################## */
PROCEDURE middle_building_design
IS
mid_build_toggle NUMBER;
mid_build_sp2 NUMBER := 14; -- total_middle ie 150/2 = around 72/4 - 4 for !!!
print_clock VARCHAR2(1) := 'Y';

PROCEDURE linePillar_space
IS
BEGIN
-- prints the first | lines in the middle building design
print_space(given_front_space);
single_line(space_btn_front_dome1 * 2);

--print the middle space between the two | design pillars in the building

print_space(given_space_btn_minars);
single_line(space_btn_front_dome1 * 2);

dbms_output.new_line;
END;



PROCEDURE clock_window
IS
BEGIN
-- prints the first | lines in the middle building design
print_space(given_front_space);
single_line(space_btn_front_dome1 * 2);

-- print the first top part of the window
FOR top_window in 1 .. 3
LOOP
print_space(mid_build_sp2);
single_special(4);
END LOOP;

-- print the clock
IF print_clock = 'Y'
THEN
print_space(mid_build_sp2);
DBMS_OUTPUT.PUT('@');
mid_build_toggle := mid_build_sp2 ;
ELSIF print_clock = 'N'
THEN
mid_build_toggle := (mid_build_sp2 * 2) + 4; -- *2 coz, the space before and after clock along with the 3 space occupied by the
-- the clock, this is when the clock is not printed

END IF;

-- this prints the clock or no clock space
print_space(mid_build_toggle);
single_special(4);



-- print the second top part of the window
FOR top_window in 1 .. 2
LOOP
print_space(mid_build_sp2);
single_special(4);
END LOOP;

-- print the second | line in the middle building design
print_space(mid_build_sp2 - 3); -- -3 for cosmetic purpose
single_line(space_btn_front_dome1 * 2);

dbms_output.new_line;

END;

BEGIN

linePillar_space; -- prints the middle part of the building with lines and spaces

-- PRINTS THE MIDDLE DESIGN WITH THE WINDOWS AND THE CENTER CLOCK
clock_window;

print_clock := 'N';
clock_window;




END;


/* ############### end of Procedure to print the bottom pillar in the building ###################### */





/* ################ Procedure to print the 2nd middle part of the building ###########################*/
PROCEDURE mid_in_minar
IS
BEGIN
FOR middle_space in 1 .. 3
LOOP

-- prints the 1st pine in the front two pines
print_space(given_front_space);
two_star_and_1space(space_btn_front_dome1);

-- prints the 2nd pine in the front two pines
print_space(given_space_btn_minars + 2);
two_star_and_1space(space_btn_front_dome2);

dbms_output.new_line;
END LOOP;


END;

/* ################ end of Procedure to print the 2nd middle part of the building ###########################*/





/* ########### Procedure to print the bottom of the building with the door ####################### */
PROCEDURE bottom_door
IS
space_before_door NUMBER := (given_space_btn_minars/2);
space_after_door NUMBER:= given_space_btn_minars - space_before_door;
door_middle_space NUMBER :=0;
door_top VARCHAR2(1) := 'Y';
len_of_side_doors NUMBER := 5;

PROCEDURE enterence_door
IS
BEGIN
FOR door_bend in 1 .. len_of_side_doors
LOOP

IF door_top = 'Y'
THEN
space_before_door := space_before_door - 7;
door_middle_space := door_middle_space + 7 + 7 + 1;
space_after_door := space_after_door - 8.1;
END IF;

-- prints the first bottom minar
print_space(given_front_space);
two_star_and_1space(space_btn_front_dome1);

-- prints the left side of the door
print_space(space_before_door);
single_star(1);

-- print the right side of the door
print_space(door_middle_space);
single_star(1);

-- print the last bottom minar
print_space(space_after_door);
two_star_and_1space(space_btn_front_dome1);
dbms_output.new_line;

END LOOP;


END;




BEGIN

-- prints the first bottom minar

print_space(given_front_space);
two_star_and_1space(space_btn_front_dome1);

-- print the starting of the door
print_space(space_before_door);
single_star(1);

-- print the second bottom minar

print_space(space_after_door);
two_star_and_1space(space_btn_front_dome1);
dbms_output.new_line;

enterence_door; -- to print the top part of the door
door_top := 'N'; -- this is stop reducin the space values in front and behind the door
len_of_side_doors := 11; -- inc's the length of the door after the bend

space_before_door := space_before_door - 2;
door_middle_space := door_middle_space + 4;
space_after_door := space_after_door - 2;
enterence_door;



END;

/* ########### end of Procedure to print the bottom of the building with the door ####################### */







BEGIN

-- get the cursor down by some lines

print_blank_lines(5);


-- Two pines of the front 2 minars

front_pines;
front_domes;
first_hori_frnt_minar(given_front_space,space_btn_front_dome1,given_space_btn_minars-1); -- -1 just for cosmetic purpose

top_pole_frnt_minars(given_front_space,space_btn_front_dome1,given_space_btn_minars, 3);

second_hori_frnt_minar;
front_down_hind_top;

-- building design
top_building_design;
middle_building_design;
curve_top_building;

mid_in_minar;
bottom_door;
-- curve_top_building;
END;

Draw Empire State Building Using Oracle PL/SQL









DECLARE
total_pyramid_length NUMBER;
total_pyramid_width NUMBER;

left_pyramid_shade_count NUMBER :=0;
middle_pyramid_shade_count NUMBER:= 0;
right_pyramid_shade_count NUMBER:= 0;


lines_printed_count NUMBER := 0;
new_line_flag VARCHAR2(3) := 'YES';
check_which_building NUMBER;
amt_of_space_interms_line NUMBER;


middle_space NUMBER := 15; -- this is to keep the initial space between the two horizontal lines to be atleast 3 spaces
print_middle_space NUMBER;
noof_lines_occupied_in_1_space NUMBER := 1.5; -- no of lines occupied in one space
increment_space_by NUMBER := 2; -- space should be incremented inbetween the lines to make it look like a moon


space_occupied_in_middle NUMBER;
noof_pics_left_side NUMBER;
noof_pics_right_side NUMBER;


building_1_width NUMBER;
building_2_width NUMBER;
building_3_width NUMBER;
building_4_width NUMBER;
building_5_width NUMBER;
building_6_width NUMBER;







---- #### Procedur to print the shaded part in the background #### ----
PROCEDURE print_shade(para_width NUMBER, next_line NUMBER)
IS
BEGIN
FOR lines_printed IN 1 .. next_line
LOOP

FOR width_of_shade in 1 .. para_width
LOOP
DBMS_OUTPUT.PUT('|');
END LOOP;

IF new_line_flag = 'YES'
THEN
dbms_output.new_line;
ELSIF new_line_flag = 'NO'
THEN
NULL;
END IF;

END LOOP;
END;



---- #### END OF Procedur to print the shaded part in the background #### ----





----#### Procedure to print empty spaces #### ----
PROCEDURE print_empty_space(width_of_space NUMBER)
IS
BEGIN
FOR for_empty_space in 1.. width_of_space
LOOP
DBMS_OUTPUT.PUT(' ');
END LOOP;
END;
---- ####END OF procedure to print empty spaces #### ----






---- #### Procedure to calculate the left right shaded amount #### ----
PROCEDURE calc_left_right_shade
IS
BEGIN
amt_of_space_interms_line := (middle_pyramid_shade_count * 1.5); --taking 1 spaces occupies around 1.5 times the line
left_pyramid_shade_count := CEIL((total_pyramid_width - middle_pyramid_shade_count)/2);
right_pyramid_shade_count := (total_pyramid_width - left_pyramid_shade_count) - amt_of_space_interms_line; -- space occupies more
--area when commpared to line so remove the extra lines that we calculted
--taking 1.5 as a standard

END;
---- #### END OF Procedure to calculate the left right shaded amount #### ----




---- #### Procedure to print buildings #### ----
PROCEDURE print_buildings
IS
BEGIN
IF check_which_building = 1
THEN
middle_pyramid_shade_count := building_1_width;
calc_left_right_shade;


ELSIF check_which_building = 2
THEN
middle_pyramid_shade_count := building_1_width + building_2_width;
calc_left_right_shade;

ELSIF check_which_building = 3
THEN
middle_pyramid_shade_count := building_1_width + building_2_width + building_3_width;
calc_left_right_shade;

ELSIF check_which_building = 4
THEN
middle_pyramid_shade_count := building_1_width + building_2_width + building_3_width + building_4_width;
calc_left_right_shade;

ELSIF check_which_building = 5
THEN
middle_pyramid_shade_count := building_1_width + building_2_width + building_3_width + building_4_width + building_5_width;
calc_left_right_shade;

ELSIF check_which_building = 6
THEN
middle_pyramid_shade_count := building_1_width + building_2_width + building_3_width + building_4_width + building_5_width + building_6_width;
calc_left_right_shade;

END IF;

END;

---- #### END OF Procedure to print buildings #### ----





---- #### Procedure to Calculate the Left, Right , and Middle space #### ----
PROCEDURE calc_lft_rt_middl(para_middle_space NUMBER)
IS
BEGIN

space_occupied_in_middle := CEIL(middle_space * noof_lines_occupied_in_1_space);

noof_pics_left_side := FLOOR((100 - (space_occupied_in_middle))/2);
noof_pics_right_side := (100 - (noof_pics_left_side + space_occupied_in_middle) );

print_middle_space := middle_space;

new_line_flag := 'NO';

print_shade(noof_pics_left_side,1); -- printing the line on the left side
print_empty_space(print_middle_space); -- printing the space in between
print_shade(noof_pics_right_side,1); -- printing the line of the right side


DBMS_OUTPUT.NEW_LINE;


END;

---- #### END OF Procedure to Calculate the Left, Right , and Middle space #### ----




---- #### Proceudre to print the moon on top of the building #### ----

PROCEDURE print_moon
IS
BEGIN
-- top half of the circle

FOR noof_line_with_space in 1 .. 2
LOOP

calc_lft_rt_middl(middle_space);

middle_space := middle_space + increment_space_by ;

END LOOP;


-- bottom half of the circle
FOR bottom_half_circle in 1 .. 2
LOOP

middle_space := middle_space - increment_space_by ;
calc_lft_rt_middl(middle_space);

END LOOP;

new_line_flag := 'YES';
print_shade(100,2); -- prints the top part of the shade

END;
---- #### END OF Proceudre to print the moon on top of the building #### ----













BEGIN
total_pyramid_width:= 100;

building_1_width := 1;
building_2_width := 2;
building_3_width := 3;
building_4_width := 8;
building_5_width := 8;
building_6_width := 8;



print_shade(100,5); -- prints the top part of the shade
print_moon; -- calls the procedure to print the moon on top

--print_shade(100,5); -- prints the top part of the shade


FOR building_line_printed in 5 .. 50 -- lines printed is taken from 5 coz, 5 lines have already been printed in the print_shade
LOOP

IF building_line_printed BETWEEN 5 AND 9
THEN
check_which_building := 1;

ELSIF building_line_printed BETWEEN 10 AND 14
THEN
check_which_building := 2;
ELSIF building_line_printed BETWEEN 15 AND 19
THEN
check_which_building := 3;
ELSIF building_line_printed BETWEEN 20 AND 24
THEN
check_which_building := 4;
ELSIF building_line_printed BETWEEN 25 AND 29
THEN
check_which_building := 5;
ELSIF building_line_printed BETWEEN 30 AND 50
THEN
check_which_building := 6;

END IF;


print_buildings;


new_line_flag := 'NO';
print_shade(left_pyramid_shade_count,1);


new_line_flag := 'NO';
print_empty_space(middle_pyramid_shade_count);



new_line_flag := 'YES';
print_shade(right_pyramid_shade_count,1);

END LOOP;

END;

Build Castle using Oracle PL/SQL








DECLARE

/*****************************************************************************************************************/
/*-------------------------------------- BUILD CASTLE -----------------------------------------------------------*/

PROCEDURE build_castle(infront_space NUMBER, len_of_castle NUMBER)
IS
blank_space_before_castle NUMBER := infront_space;
blank_space_above_castle NUMBER := FLOOR(blank_space_before_castle/3);
go_new_line VARCHAR2(2) := 'T';





----------- ^^^^^^^^^^ DECLARATIONS for FLAG ^^^^^^^^^ ---------------

space_infront_of_flag NUMBER := infront_space; -- space infront of flag
breadth_of_flag NUMBER := FLOOR(len_of_castle/5);
mid_len_flag_no_verti_lines NUMBER := (breadth_of_flag - 4);
space_btn_flag_hori_lines NUMBER := (breadth_of_flag + (breadth_of_flag - 1))-2;
-- (breadth_of_flag - 1) coz, count the star with space, except the last star whose blank space is outside the rite verti line
-- substract 2 from the total coz, we shouldnt consider the two verti lines in the flag
rod_len_in_flag NUMBER := (mid_len_flag_no_verti_lines + 2); -- keeping the len of flag same as the len of the rod of the flag
-- add 2 here coz,two verti lines in flag are missing in the mid_len_of_flag

next_line VARCHAR2(2) := 'F';

---------- vvvvvvvvvvv END OF DECLARATIONS for FLAG vvvvvvvv ------------






----------- ^^^^^^^^^^ DECLARATIONS for Triangle ^^^^^^^^^ ---------------
-- keeping the first triangle the same length as the breath of the flag

mid_len_of_1st_middle_triangle NUMBER := (breadth_of_flag - 2); -- substracting the hori line in the triangel and the tip of the triangle
first_center_space_in_triangel NUMBER := 1;
space_infront_tip_of_triangle NUMBER := infront_space;



---------- vvvvvvvvvvv END OF DECLARATIONS foR Triangle vvvvvvvv ------------






------- ^^^^^^^^^^ Declaration for tall building ^^^^^^^ --------------------
top_castle_len NUMBER := (mid_len_flag_no_verti_lines +2) + rod_len_in_flag + (mid_len_of_1st_middle_triangle +2);
-- calculates the length of the top part of the castle which includes
-- the length of the flag with pole/rod and the length of the triangle
tot_len_of_tall_building NUMBER := (len_of_castle - top_castle_len)-1; -- gives the len of the tallest building
-- substract 1 coz, we are taking out the last base line in the castle
individual_top_len_each_castle NUMBER := FLOOR(tot_len_of_tall_building/3);

front_space_in_middle_castle NUMBER;
middle_space_in_middle_castle NUMBER;


-------- vvvvvvvvv END OF Declaration for tall building vvvvv ---------------





------------^^^^^^^^^^^^^^^^^ Declaration for the 2nd middle building ^^^^^^^^^ --------

middle_space_2nd_building NUMBER;
front_space_in_2nd_building NUMBER;

middle_hori_line NUMBER;
middle_space_in_small_tri NUMBER :=4;
tot_middle_hori_space NUMBER;
space_infront_smal_tri NUMBER;
----------- vvvvvvvvvvvvvvvv END OF Declaration for the 2nd middle building vvvvv --------






------------ ^^^^^^^^^^^ Declaration for the last building ^^^^^^^^^^ --------------
no_of_stars_last_building NUMBER;
middle_space_in_last_building NUMBER;
spce_btn_hori_line_last_build NUMBER;
spce_before_last_building NUMBER;
door_space_bottom_middle NUMBER;


--------------- vvvvvvvvvvvv END OF Declaration for the last building vvvvvvvvvvv --------




------ ^^^^^^^^^^ Remainig Declarations ^^^^^^^^ -----------------
top_of_the_door NUMBER;
after_top_door VARCHAR2(2):= 'F';
upper_midle_verti_line NUMBER;
lower_verti_line NUMBER;
base_of_the_castle NUMBER;
no_of_base_stars NUMBER;
--------vvvvvvvv END OF Remaining Declarations vvvvvv ------------






---- #### Procedure to print space in castle #### ----
PROCEDURE print_spaces(para_len_of_space NUMBER)
IS
BEGIN
FOR for_len_of_space IN 1 .. para_len_of_space
LOOP
dbms_output.put(' ');
END LOOP;
END;
---- #### END OF Procedure to print space in castle #### ----





---- #### Procedure to print a space and a star #### ----
PROCEDURE print_space_n_star(para_space_infront_of_star NUMBER)
IS
BEGIN
print_spaces(para_space_infront_of_star );
dbms_output.put('*');
END;

---- #### END OF Procedure to print a space and a star #### ----






---- #### Procedure to print stars in a line #### ----
PROCEDURE print_hori_stars_no_space(para_no_of_stars NUMBER, para_space_before_star NUMBER)
IS
BEGIN
print_spaces(para_space_before_star);
FOR for_no_of_stars IN 1 .. para_no_of_stars
LOOP
dbms_output.put('*');
END LOOP;
END;
---- #### END OF Procedure to print stars in a line #### ----




---- #### Procedure to print a single vertical line #### ----
PROCEDURE print_single_verti_line(para_no_of_verti_stars NUMBER, para_space_infront_of_rod NUMBER)
IS
BEGIN
FOR for_print_single_vertei IN 1 .. para_no_of_verti_stars
LOOP
print_spaces(para_space_infront_of_rod);
dbms_output.put('*');
IF next_line = 'T'
THEN
dbms_output.new_line;
END IF;
END LOOP;

END;

---- #### Procedure to print a single vertical line #### ----






---- #### Procedure to print stars in horizontal line along with spaces #### ----
PROCEDURE print_hori_stars_wit_space(para_no_of_verti_stars NUMBER, para_space_infront NUMBER)
IS
BEGIN
print_spaces(para_space_infront); -- prints the space infront of the hori line

FOR for_len_of_hori_line IN 1 .. para_no_of_verti_stars
LOOP
dbms_output.put('*');
dbms_output.put(' ');
END LOOP;

END;

---- #### END OF Procedure to print stars in horizontal line along with spaces #### ----







---- #### Procedure to print two vertical line the Castle #### ----
PROCEDURE print_two_verti_line_spaces(para_no_of_hori_stars NUMBER, para_space_btn_lines NUMBER, space_infront_verti_line NUMBER)
IS
BEGIN


FOR for_2_verti_line IN 1 .. para_no_of_hori_stars
LOOP
print_spaces(space_infront_verti_line);
dbms_output.put('*'); -- prints the first star in the left verti side of the two lines
print_spaces(para_space_btn_lines); -- prints the space btn the two lines
dbms_output.put('*'); -- prints the last star in the right verti side of the two lines

IF go_new_line = 'T'
THEN
dbms_output.new_line;
END IF;


END LOOP;
END;
---- #### END OF Procedure to print two vertical line the Castle #### ----





---- #### Procedure to print a triangle on the castle #### ----
PROCEDURE print_triangle(para_mid_len_of_triangle NUMBER, para_space_infront_triangel NUMBER )
IS
changing_tri_infront_space NUMBER := para_space_infront_triangel;
base_stars_in_triangle NUMBER;

BEGIN
print_spaces(para_space_infront_triangel);
dbms_output.put_line('*');

FOR for_no_of_middle_part IN 1 .. para_mid_len_of_triangle
LOOP
changing_tri_infront_space := changing_tri_infront_space - 1; -- dec the space infront of the triangle
print_spaces(changing_tri_infront_space);
dbms_output.put('*');
print_spaces(first_center_space_in_triangel); -- prints the center space in the triangle
dbms_output.put('*');
dbms_output.new_line;
first_center_space_in_triangel := first_center_space_in_triangel + 2; -- inc the center space in the triangle
END LOOP;
base_stars_in_triangle := CEIL(first_center_space_in_triangel/2);
print_hori_stars_wit_space((base_stars_in_triangle+1),changing_tri_infront_space - 1);
dbms_output.new_line;

front_space_in_middle_castle := changing_tri_infront_space - 1;
middle_space_in_middle_castle := ((base_stars_in_triangle + 1) + (base_stars_in_triangle))- 2;
-- here wer are adding the stars along with the spaces so add base_stars + one less space than the tot stars, as the last space will be printed outside the range
-- substract 2, coz removing the two vertical lines in building, we need only the spaces without the vertical lines


END;

---- #### END OF Procedure to print a triangle on the castle #### ----






------ #### Procedure to print the tallesst middle building in the castle #### ----
PROCEDURE print_tallest_middle_building(para_top_len_of_middle NUMBER)
IS
BEGIN

print_two_verti_line_spaces(para_top_len_of_middle,middle_space_in_middle_castle,front_space_in_middle_castle);

middle_space_2nd_building := middle_space_in_middle_castle;


END;


------ #### END OF Procedure to print the tallesst middle building in the castle #### ----






---- #### Procedure to print the small triangles on the small buildings #### ----
PROCEDURE print_small_triangle(para_middle_2nd_building NUMBER, para_front_middle_castle NUMBER)
IS

small_tri_middle_space NUMBER := para_middle_2nd_building;
right_of_small_triangle NUMBER := CEIL(small_tri_middle_space/2);

small_tri_front_space NUMBER := (para_front_middle_castle - right_of_small_triangle)-1 ;
-- subtract 1,coz we shouldnt consider the space where the top star of the 2nd triangle occupies


BEGIN


print_spaces(small_tri_front_space);
dbms_output.put('*');
go_new_line := 'F';
print_two_verti_line_spaces(1,middle_space_in_middle_castle,right_of_small_triangle);
print_spaces(right_of_small_triangle);
dbms_output.put('*');
dbms_output.new_line;

front_space_in_2nd_building := small_tri_front_space;

END;

---- #### END OF Procedure to print the small triangles on the small buildings #### ----






---- #### Procedure to print the second line in the small triangle #### ----
PROCEDURE secnd_line_triangle(para_times NUMBER)
IS
BEGIN

FOR secnd_line_triange IN 1 .. para_times
LOOP
go_new_line := 'F';
print_two_verti_line_spaces(1,middle_space_in_small_tri,(front_space_in_2nd_building -2));
print_two_verti_line_spaces(1,middle_space_in_middle_castle,1);
print_two_verti_line_spaces(1,middle_space_in_small_tri,1);
dbms_output.new_line;
END LOOP;
END;
---- #### END OF Procedure to print the second line in the small triangle #### ----





---- #### Procedure to print huge spaces infront of a single star #### ----
PROCEDURE print_huge_space_n_star(para_huge_space NUMBER)
IS
BEGIN
print_spaces(para_huge_space);
dbms_output.put('*');
END;



---- #### END OF Procedure to print huge spaces infront of a single star #### ----








BEGIN

---- @@@@ Prints the flag on the top of the castle @@@@ ----

print_hori_stars_wit_space( breadth_of_flag, space_infront_of_flag); -- this is to print the first hori line in the flag with stars and spaces
dbms_output.new_line; -- to get the control to the next line after drawing the first hori line in the flag
print_two_verti_line_spaces(mid_len_flag_no_verti_lines, space_btn_flag_hori_lines, space_infront_of_flag); -- prints the middle part of the flag, which has two hori lines and spaces in btn
print_hori_stars_wit_space( breadth_of_flag, space_infront_of_flag);
dbms_output.new_line; -- to get the control to the next line after drawing the first hori line in the flag
next_line := 'T';
print_single_verti_line(rod_len_in_flag,space_infront_of_flag);

---- @@@@ END OF Prints the flag on the top of the castle @@@@ ----
next_line := 'F';





---- @@@@ Print the triangle in the castle @@@@ ----
print_triangle(mid_len_of_1st_middle_triangle, space_infront_tip_of_triangle);

---- @@@@ END OF Print the triangle in the castle @@@@ ----




---- @@@@ Print the top part of tallest middle castle building @@@@ ----
print_tallest_middle_building(individual_top_len_each_castle-1); -- subtract 1, coz remvoing the base line in the upper part of the castle

---- @@@@ END OF Print the top part of tallest middle castle building @@@@ ----


---- @@@@ Print small triangle @@@@ ----
print_small_triangle( middle_space_2nd_building,front_space_in_middle_castle) ;
secnd_line_triangle(1) ;
---- @@@@ Print small triangle @@@@ ----



tot_middle_hori_space := (middle_space_in_small_tri + middle_space_in_middle_castle+ middle_space_in_small_tri)+ 6 + 2;
-- this add the middle spaces in the two side 2nd buildings and the middle builiding
-- along with the 6 vertical lines and 2 spaces btn the side building and the center building

middle_hori_line := CEIL(tot_middle_hori_space/2)+1;
space_infront_smal_tri := front_space_in_middle_castle - (middle_space_in_small_tri+3);

print_hori_stars_wit_space(middle_hori_line,space_infront_smal_tri);
-- subtracting the space occupied by the 2nd building from
-- the total left space from the center of the middle building

dbms_output.new_line;




---- @@@@ Print the upper middle veretical lines

upper_midle_verti_line := FLOOR(tot_len_of_tall_building/3)-2;
FOR for_upper_middle_veret IN 1 .. upper_midle_verti_line
LOOP
print_two_verti_line_spaces(1, middle_space_2nd_building ,space_infront_smal_tri);
print_two_verti_line_spaces(1, middle_space_2nd_building ,middle_space_in_middle_castle);
dbms_output.new_line;
END LOOP;




---- @@@@ Print the two hori lines for the bottom building of the castle
middle_space_in_last_building := middle_space_2nd_building;


spce_before_last_building := (space_infront_smal_tri - middle_space_in_last_building)-1;
-- calc front space of the last building, by subtracting the front space of the
-- middle space from last building from the front space of 2nd building
-- and then 1 ie the vertical line of the 2nd building

no_of_stars_last_building := FLOOR(middle_space_in_last_building/2)+2; -- half of the center space and add 2
-- for the corner stars on the two verti lines


print_hori_stars_wit_space(no_of_stars_last_building,spce_before_last_building );
print_hori_stars_wit_space(1,(middle_space_2nd_building-1));
print_hori_stars_wit_space(1,(middle_space_in_middle_castle-1));
print_hori_stars_wit_space(no_of_stars_last_building,(middle_space_2nd_building-1));
dbms_output.new_line;



---- @@@@ Print the lower vertical lines
lower_verti_line := ((tot_len_of_tall_building-2) - upper_midle_verti_line);

FOR for_lower_part_castle IN 1 .. lower_verti_line
LOOP

print_two_verti_line_spaces(1, middle_space_in_last_building ,spce_before_last_building);
-- prints the the vertical line of the last building in the lower part of castle
IF(for_lower_part_castle = 6)
THEN
door_space_bottom_middle := FLOOR(middle_space_in_middle_castle/3);
top_of_the_door := (middle_space_in_middle_castle - (door_space_bottom_middle *2));


print_single_verti_line(1,middle_space_2nd_building);
-- prints the right side wall of the 2nd building
print_hori_stars_no_space(top_of_the_door,door_space_bottom_middle);
-- prints the top of hte door
print_two_verti_line_spaces(1,middle_space_2nd_building,door_space_bottom_middle);
-- prints the left and right side walls of the right 2nd building
print_single_verti_line(1,middle_space_in_last_building);
-- prints the right side wall of the last building
after_top_door := 'T';

ELSE
-- this has to be executed after the top of the door is printed in the bottom half of the castle
IF after_top_door = 'T'
THEN
print_two_verti_line_spaces(1,door_space_bottom_middle,middle_space_2nd_building);
print_two_verti_line_spaces(1,door_space_bottom_middle,(top_of_the_door-2));
print_two_verti_line_spaces(1,middle_space_in_last_building,middle_space_2nd_building);

-- this is executed before the the top of the door is printed in the top half of the castle
ELSIF after_top_door = 'F'
THEN
print_two_verti_line_spaces(1,middle_space_in_middle_castle,middle_space_2nd_building);
print_two_verti_line_spaces(1,middle_space_in_last_building,middle_space_2nd_building);
END IF;
END IF;
dbms_output.new_line;
END LOOP;

--- base of the castle
base_of_the_castle := (middle_space_in_last_building *2) + (middle_space_2nd_building *2) + middle_space_in_middle_castle +4;
-- total + 4 coz, counting the vertical lines with the spaces in between
no_of_base_stars := FLOOR(base_of_the_castle/2) +2;
-- + 2 for the vertical lines in the begining and the end of the base line


print_hori_stars_wit_space(no_of_base_stars,spce_before_last_building);
dbms_output.new_line;
END;





/*-------------------------------------- BUILD CASTLE -----------------------------------------------------------*/
/*****************************************************************************************************************/



BEGIN
build_castle(30,25);
END;

Draw a CAR using Oracle PL/SQL








Draw a CAR using Oracle PL/SQL


DECLARE

len_of_car_top NUMBER := 20; -- taking length of car top as a constant value
space_infront_of_car NUMBER := len_of_car_top * 3; -- Multiply by 3 to make enought space for the front of the car
space_btn_frnt_back_glass NUMBER := len_of_car_top + 25; -- This is the space between the front and back glass equating to
-- the len of the top of car,coz inc the

space_infront_engine NUMBER;
last_middle_space NUMBER;

print_space VARCHAR2(1) := 'Y';
tot_car_width NUMBER;

engine_line NUMBER;
bumper_line NUMBER;

tire_size1 NUMBER := 15;
tire_size2 NUMBER := 15;

tire_size3 NUMBER := 15;








---- #### Procedure to print empty space in the front before printing something #### ----
PROCEDURE print_empty_hori_space(para_len_of_empty_line NUMBER)
IS
BEGIN
FOR empty_line_len in 1 .. para_len_of_empty_line
LOOP
DBMS_OUTPUT.PUT(' ');
END LOOP;

END;
----#### END of procedure to print empty space in the front before printing something #### ----







-- #### PROCEDURE to print a horizontal line of stars #### ----

PROCEDURE print_hori_line(para_len_of_line NUMBER)
IS
BEGIN

IF print_space = 'Y'
THEN
print_empty_hori_space(space_infront_of_car); -- multily by 3 to make enough space for the front engine
END IF;


FOR for_line_len in 1 .. para_len_of_line
LOOP

DBMS_OUTPUT.PUT('+');
END LOOP;
END;

---- #### end of Procedure to print a horizontal line of stars #### ----







---- #### Procedure to print empty lines for the picture to move down #### ----
PROCEDURE print_empty_lines(para_noof_empty_lines NUMBER)
IS
BEGIN

FOR space_above in 1 .. para_noof_empty_lines
LOOP
dbms_output.new_line;
END LOOP;
END;

----#### End of Procedur to print empty line for the picture to move down #### ----






---- #### Procedrue to print slant lines of the car front and rear glass #### ----
PROCEDURE print_frnt_rear_glasses(para_len_of_glass NUMBER)
IS
BEGIN

FOR print_middle_space in 1 .. para_len_of_glass
LOOP
space_infront_of_car := space_infront_of_car - 4; -- this will reduce the space infront of the car to get a


print_empty_hori_space(space_infront_of_car); --slant appears for the front glass of the car

DBMS_OUTPUT.PUT('+');

print_empty_hori_space(space_btn_frnt_back_glass);

DBMS_OUTPUT.PUT('+');
DBMS_OUTPUT.NEW_LINE;

space_btn_frnt_back_glass := space_btn_frnt_back_glass + 6;
END LOOP;
space_infront_engine := space_infront_of_car;
last_middle_space := space_btn_frnt_back_glass;
--DBMS_OUTPUT.PUT_LINE(SPACE_INFRONT_ENGINE);
--DBMS_OUTPUT.PUT_LINE(LAST_MIDDLE_SPACE);
END;


---- ####END OF procedure to print slant lines of the care for front and rear glass #### ----





---- ####Procedure to print bumper and engine of the car #### ----
PROCEDURE print_engine_n_bumper --(para_len_of_engineNbumper NUMBER)
IS
BEGIN
engine_line := space_infront_of_car - 37;
print_space := 'N';


print_empty_hori_space(25);
print_hori_line(engine_line);
print_empty_hori_space(LAST_MIDDLE_SPACE);

print_hori_line(engine_line );
DBMS_OUTPUT.NEW_LINE;

END;

---- ####END OF Procedure to print bumper and engine of the car #### ----






----#### Procedure to print the front and rear vertical lines in the car #### ----

PROCEDURE frnt_rear_verti_lines
IS
BEGIN

FOR len_of_front_verti_lines in 1 .. 3
LOOP
print_empty_hori_space(25);
DBMS_OUTPUT.PUT('*');


/* --delete

print_empty_hori_space(60);
dbms_output.put('+');
print_empty_hori_space(7);
dbms_output.put('+');

-- delete*/


tot_car_width := LAST_MIDDLE_SPACE - 10 ;
print_empty_hori_space(engine_line + LAST_MIDDLE_SPACE + engine_line + 19);
DBMS_OUTPUT.PUT('+');
DBMS_OUTPUT.NEW_LINE;
END LOOP;

END;

----#### END OF Procedure to print the front and rear vertical lines in the car #### ----





---- #### Procedure to print the bottom part of the car #### ----
PROCEDURE bottome_of_car
IS
BEGIN
print_empty_hori_space(25);
print_hori_line(tot_car_width);
DBMS_OUTPUT.NEW_LINE;
END;

---- #### Procedure to print the bottom part of the car #### ----





---- #### Procedrue to print wheels of the car #### ----
PROCEDURE wheels_of_car
IS
BEGIN

FOR tire in 1 .. 2
LOOP

print_empty_hori_space(25);
print_empty_hori_space(tire_size1);
DBMS_OUTPUT.PUT('*');
print_empty_hori_space(tire_size2);
dbms_output.put('*');





print_empty_hori_space(35);

Print_empty_hori_space(tire_size3);
DBMS_OUTPUT.PUT('*');
print_empty_hori_space(tire_size2);
dbms_output.put('*');

tire_size1 := tire_size1 + 3 ;
tire_size2 := tire_size2 - 8;

tire_size3 := tire_size3 + 9;



dbms_output.new_line;
END LOOP;


END;
---- #### Procedrue to print wheels of the car #### ----






BEGIN

print_empty_lines(5); -- this will print 5 empty lines, this will make the picture to go down by 5 lines

print_hori_line(len_of_car_top); -- this will print a horizontal line
dbms_output.new_line;
print_frnt_rear_glasses(3);
print_engine_n_bumper;
frnt_rear_verti_lines;
bottome_of_car;
wheels_of_car;

END;

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;

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;