Tuesday, November 6, 2012
Adding numbers between two values
We could have sat down to work it out with some paper and a pen, but instead I decided to introduce him to the world of Oracle, SQL and PL/SQL
The first step we took was to work out how you would do it on paper for some of the numbers. Then we translated this into some PL/SQL code. OK I did a lot this but he did seem to understand and follow what I was doing.
So the following Function is what we ended up with to add all the numbers between two numbers and return the answer.
CREATE or REPLACE function AddNumsBetween
( pStartNum IN NUMBER,
pEndNum IN NUMBER)
RETURN NUMBER
IS
vSum Number := 0;
BEGIN
FOR i IN pStartNum .. pEndNum LOOP
vSum := vSum + i;
END LOOP;
return vSum;
END;
/
The next step was to write some code to call this function. The code prompts the user to enter the Start number and End number.
set serveroutput on
DECLARE
vStartNum NUMBER := 0;
vEndNum NUMBER := 100;
vAnswer NUMBER := 0;
BEGIN
vStartNum := &Start_Number;
vEndNum := &End_Number;
vAnswer := AddNumsBetween(vStartNum, vEndNum);
dbms_output.put_line('The sum of numbers between '||vStartNum||' and '||vEndNum||' is '||vAnswer||'.');
END;
/
To answer by son’s original query, we used Zero and 100 as our inputs.
The answer to the question is 5,050.
Thursday, November 1, 2012
ASCII to character conversion in Oracle
Here is code code that will produce formatted output of the characters and their ascii values. The formatting is broken into lower case letters, uppercase letters, characters with an ascii value less than an ‘a’ and characters whose ascii characters are greater than a ‘z.
Code
set serveroutput on FORMAT WRAPPED
DECLARE
vTab VARCHAR2(5) := CHR(9);
vNum NUMBER := 0;
vString VARCHAR2(80) := '';
BEGIN
--
-- Formatted lower case letter to ASCII values
--
dbms_output.put_line('Formatted Lower Case Letters to ASCII values');
dbms_output.put_line('-------------------------------------------------------');
FOR i IN ASCII('a') .. ASCII('z') LOOP
IF vNum < 6 THEN
vString := vString||CHR(i)||' : '||i||vTab;
vNum := vNum + 1;
ELSIF vNum = 6 then
dbms_output.put_line(vString||CHR(i)||' : '||i);
vNum := 0;
vString := '';
ELSE
dbms_output.put_line('ERROR');
END IF;
END LOOP;
dbms_output.put_line(vString);
--
-- Formatted upper case letter to ASCII values
--
vString := '';
vNum := 0;
dbms_output.new_line;
dbms_output.new_line;
dbms_output.put_line('Formatted Upper Case Letters to ASCII values');
dbms_output.put_line('-------------------------------------------------------');
FOR i IN ASCII('A') .. ASCII('Z') LOOP
IF vNum < 6 THEN
vString := vString||CHR(i)||' : '||i||vTab;
vNum := vNum + 1;
ELSIF vNum = 6 then
dbms_output.put_line(vString||CHR(i)||' : '||i);
vNum := 0;
vString := '';
ELSE
dbms_output.put_line('ERROR');
END IF;
END LOOP;
dbms_output.put_line(vString);
--
-- Formatted chars less than 'a' to ASCII values
--
vString := '';
vNum := 0;
dbms_output.new_line;
dbms_output.new_line;
dbms_output.put_line('Formatted Letters, less than a to ASCII values');
dbms_output.put_line('-------------------------------------------------------');
FOR i in 0 .. ASCII('a')-1 LOOP
IF vNum < 6 THEN
vString := vString||CHR(i)||' : '||i||vTab;
vNum := vNum + 1;
ELSIF vNum = 6 then
dbms_output.put_line(vString||CHR(i)||' : '||i);
vNum := 0;
vString := '';
ELSE
dbms_output.put_line('ERROR');
END IF;
END LOOP;
dbms_output.put_line(vString);
--
-- Formatted chars greater than 'Z' to ASCII values
--
vNum := 0;
vString := '';
dbms_output.new_line;
dbms_output.new_line;
dbms_output.put_line('Formatted Letters, greater than z to ASCII values');
dbms_output.put_line('-------------------------------------------------------');
FOR i IN ASCII('z') .. ASCII('z')+133 LOOP
IF vNum < 6 THEN
vString := vString||CHR(i)||' : '||i||vTab;
vNum := vNum + 1;
ELSIF vNum = 6 then
dbms_output.put_line(vString||CHR(i)||' : '||i);
vNum := 0;
vString := '';
ELSE
dbms_output.put_line('ERROR');
END IF;
END LOOP;
dbms_output.put_line(vString);
END;
/
Output
Formatted Lower Case Letters to ASCII values
-------------------------------------------------------
a : 97 b : 98 c : 99 d : 100 e : 101 f : 102 g : 103
h : 104 i : 105 j : 106 k : 107 l : 108 m : 109 n : 110
o : 111 p : 112 q : 113 r : 114 s : 115 t : 116 u : 117
v : 118 w : 119 x : 120 y : 121 z : 122
Formatted Upper Case Letters to ASCII values
-------------------------------------------------------
A : 65 B : 66 C : 67 D : 68 E : 69 F : 70 G : 71
H : 72 I : 73 J : 74 K : 75 L : 76 M : 77 N : 78
O : 79 P : 80 Q : 81 R : 82 S : 83 T : 84 U : 85
V : 86 W : 87 X : 88 Y : 89 Z : 90
Formatted Letters, less than a to ASCII values
-------------------------------------------------------
: 0 ☺ : 1 ☻ : 2 ♥ : 3 ♦ : 4 ♣ : 5 ♠ : 6
: 7 : 8 : 9
: 13 ♂ : 11 ♀ : 12
♫ : 14 ☼ : 15 ► : 16 ◄ : 17 ↕ : 18 ‼ : 19 ¶ : 20
§ : 21 ▬ : 22 ↨ : 23 ↑ : 24 ↓ : 25 → : 26 ← : 27
∟ : 28 ↔ : 29 ▲ : 30 ▼ : 31 : 32 ! : 33 " : 34
# : 35 $ : 36 % : 37 & : 38 ' : 39 ( : 40 ) : 41
* : 42 + : 43 , : 44 - : 45 . : 46 / : 47 0 : 48
1 : 49 2 : 50 3 : 51 4 : 52 5 : 53 6 : 54 7 : 55
8 : 56 9 : 57 : : 58 ; : 59 < : 60 = : 61 > : 62
? : 63 @ : 64 A : 65 B : 66 C : 67 D : 68 E : 69
F : 70 G : 71 H : 72 I : 73 J : 74 K : 75 L : 76
M : 77 N : 78 O : 79 P : 80 Q : 81 R : 82 S : 83
T : 84 U : 85 V : 86 W : 87 X : 88 Y : 89 Z : 90
[ : 91 \ : 92 ] : 93 ^ : 94 _ : 95 ` : 96
Formatted Letters, greater than z to ASCII values
-------------------------------------------------------
z : 122 { : 123 | : 124 } : 125 ~ : 126 ⌂ : 127 Ç : 128
ü : 129 é : 130 â : 131 ä : 132 à : 133 å : 134 ç : 135
ê : 136 ë : 137 è : 138 ï : 139 î : 140 ì : 141 Ä : 142
Å : 143 É : 144 æ : 145 Æ : 146 ô : 147 ö : 148 ò : 149
û : 150 ù : 151 ÿ : 152 Ö : 153 Ü : 154 ø : 155 £ : 156
Ø : 157 × : 158 ƒ : 159 á : 160 í : 161 ó : 162 ú : 163
ñ : 164 Ñ : 165 ª : 166 º : 167 ¿ : 168 ® : 169 ¬ : 170
½ : 171 ¼ : 172 ¡ : 173 « : 174 » : 175 ░ : 176 ▒ : 177
▓ : 178 │ : 179 ┤ : 180 Á : 181 Â : 182 À : 183 © : 184
╣ : 185 ║ : 186 ╗ : 187 ╝ : 188 ¢ : 189 ¥ : 190 ┐ : 191
└ : 192 ┴ : 193 ┬ : 194 ├ : 195 ─ : 196 ┼ : 197 ã : 198
à : 199 ╚ : 200 ╔ : 201 ╩ : 202 ╦ : 203 ╠ : 204 ═ : 205
╬ : 206 ¤ : 207 ð : 208 Ð : 209 Ê : 210 Ë : 211 È : 212
ı : 213 Í : 214 Î : 215 Ï : 216 ┘ : 217 ┌ : 218 █ : 219
▄ : 220 ¦ : 221 Ì : 222 ▀ : 223 Ó : 224 ß : 225 Ô : 226
Ò : 227 õ : 228 Õ : 229 µ : 230 þ : 231 Þ : 232 Ú : 233
Û : 234 Ù : 235 ý : 236 Ý : 237 ¯ : 238 ´ : 239 : 240
± : 241 ‗ : 242 ¾ : 243 ¶ : 244 § : 245 ÷ : 246 ¸ : 247
° : 248 ¨ : 249 · : 250 ¹ : 251 ³ : 252 ² : 253 ■ : 254
: 255
PL/SQL procedure successfully completed.
Observations
There are two things that stand out in this. The first is there is sound produced. This is because one of the characters is defined this way. It is ASCII number 7. This can be repeated using the following:
select chr(7) from dual.
The second is the formatting of the lines for ascii codes 8 to 12. We can see that one of the ascii codes does not get displayed and the ordering of this is not as expected. This is due to ascii 10 being a line feed.