declare
books integer;
begin
books := 20;
if books = 8820
then
dbms_output.put_line('they are equal');
else
dbms_output.put_line('***$$$### they are not equal');
end if;
end;
/
----------------------
# This is the same program in Python: p1.py
books = 20
if books == 8820:
print('they are equal')
else: print('they are not equal')
=============================================
-- Better output
declare
books integer;
begin
books := 20;
if books = 20
then
dbms_output.put_line('there are ' || books || ' books');
else
dbms_output.put_line('they are not equal');
end if;
end;
/
------------------------
books = 20
if books == 20:
print('there are ' + str(books) + ' books')
else: print('they are not equal')
====================================================
-- This is a kind of Fibonacci generator.
-- It introduces a WHILE loop.
-- Also it shows a new output function.
declare
current integer;
next integer;
previous integer;
begin
current := 1;
next := 1;
while current < 100000
loop
dbms_output.put(current);
dbms_output.put(' ');
previous := current;
current := next;
next := previous + current;
end loop;
dbms_output.new_line;
end;
/
----------------------------------
current = 1
next = 1
while current < 100000:
print(current, end=" ") # without the end=" " every number is on a
# separate line
previous = current
current = next
next = previous + current
=======================================
-- This is another Fibonacci generator.
-- Also shows another kind of comments.
/* Note that in this version we generate the initial 1 1 in a
different way. */
-- Lastly, this shows a use of elsif.
declare
previous integer;
current integer;
next integer;
result varchar2(80);
counter integer;
begin
counter := 20;
previous := 1;
current := 1;
result := '';
if counter = 1
then
result := ' 1';
elsif counter = 2
then
result := ' 1 1 ';
else
result := ' 1 1 ';
while counter > 2
loop
next := previous + current;
previous := current;
current := next;
result := result || ' ' || current;
counter := counter - 1;
end loop;
end if;
dbms_output.put_line(result); -- All output here.
end;
/
----------------
counter = 20
previous = 1
current = 1
result = ''
if counter == 1:
result = ' 1'
elif counter == 2:
result = ' 1 1 '
else:
result = ' 1 1'
""" This is a multi-line
comment """
while counter > 2:
next = previous + current
previous = current
current = next
result = result + ' ' + str(current)
counter = counter - 1
print(result)
============================================
-- A basic procedure with no parameters.
-- A procedure is like a function that does not return
-- anything.
create or replace procedure hello3
as
begin
dbms_output.put_line('hello_world_33');
end;
/
-- This is how to call that procedure.
-- This is the main program.
begin
hello3;
end;
/
----------------------------
def hello3():
print('hello world 33')
hello3()
================================================
-- A procedure call with one parameter.
-- This is called an "in" parameter
-- because data can be sent ONLY from the main program
-- into the procedure. (But not out.)
create or replace procedure hello3(num in number)
as
begin
dbms_output.put_line('hello_world_3');
dbms_output.put_line(num);
end;
/
-- This is how to call that procedure.
begin
hello3(12);
end;
/
------------------
def hello3(num):
print('hello world 33')
print(num)
hello3(12)
==============================================
-- A basic function declaration for a function with one parameter.
create or replace function mysquare(num in number)
-- "in parameter" values go INto function
return number
as
begin
-- Following line for debugging only!
dbms_output.put_line('Inside of function ' || num * num);
return num * num;
end;
/
-- This is one way to call that function.
declare
z number;
begin
z := mysquare(12);
dbms_output.put_line('Outside of function ' || z);
end;
/
------------------
def mysquare(num):
print('Inside of function ' + str(num*num))
return(num*num)
z = mysquare(12)
print("outside of function " + str(z))
=============================================
-- The same function again.
create or replace function mysquare(
num in number)
return number
as
begin
dbms_output.put_line(num * num);
return num * num;
end;
/
-- Another way to call that function.
begin
dbms_output.put_line(mysquare(15));
end;
/
---------------------
def mysquare(num):
print('Inside of function ' + str(num*num))
return(num*num)
print(mysquare(15))
=======================================
-- No, it is not case sensitive
-- Same function again.
create or replace function mysquare(
num in number)
return number
as
begin
dbms_output.put_line(num * num);
return num * num;
end;
/
-- This is how to call that function.
BEGIN
dbms_OUTPUT.put_line(MYSQUARE(19));
END;
/
-----------------------------------
Python IS CASE SENSITIVE.
The following does NOT work.
def mysquare(num):
print('Inside of function ' + str(num*num))
return(num*num)
print(MYSQUARE(15))
=======================================
-- One in parameter and one out parameter.
create or replace procedure mytest(
num in number, num2 out number)
as
begin
-- num := 21; /* This would cause an error message. */
dbms_output.put_line('Inside of procedure ' || num2);
num2 := 55;
end;
/
-- This is how to call that procedure.
declare
y number;
z number;
begin
y := 888;
z := 999;
dbms_output.put_line('In main program before call' || y || ' ' || z);
mytest(y, z); -- z is changed!!
dbms_output.put_line('In main program after call' || y || ' ' || z);
end;
/
-----------------------------------
Python has NO OUT PARAMETERS.
You cannot do the above.
There are two "tricks."
One is to wrap the number into an object and change the object.
There is a whole theory of mutable and immutable objects.
You have to pass in a mutable object.
Like this:
def change(x):
x[0] = 3
x = [999]
print(x[0])
change(x)
print(x[0])
The other way is to use a global variable.
But global variables are a no-no in most programming languages.
So don't do this.
z = 20
def changer():
global z
z = 88
print(z)
changer()
print(z)
=========================================
-- This adds a parameter that is both in and out at the same time.
create or replace procedure mytest(
num in number, num2 out number,
num3 in out number)
as
begin
-- num := 21; /* This would cause an error message. */
num2 := 55;
num3 := 44;
end;
/
-- This is how to call that procedure.
declare
y number;
z number;
x number;
begin
y := 888;
z := 999;
x := 777;
dbms_output.put_line('Before call ' || y || ' ' || z || ' ' || x);
mytest(y, z, x); -- z and x are changed!!
dbms_output.put_line('After call ' || y || ' ' || z || ' ' || x);
end;
/
--------------------------
This is irrelevant, given that there are no real out parameters
in Python.
=================================================
-- Out parameters need to be called with variables.
create or replace procedure mytest(
num in number, num2 out number,
num3 in out number)
as
begin
-- num := 21;
num2 := 55;
num3 := 44;
end;
/
-- This is how to call that procedure.
declare
z number;
x number;
begin
z := 999;
x := 777;
mytest(888, z, x); -- IN parameter called with a number;
-- mytest(888, 22, x); -- Would cause an error!
-- mytest(888, z, 22); -- Out parameters have to be variables.
-- In Out parameters have to be variables.
dbms_output.put_line( z || ' ' || x);
end;
/
------------------
Irrelevant for same reasons as before. No good out parameters in Python.
================================
-- Two more data types
-- Booleans cannot be output with put_line()!
declare
b boolean;
c number(10,3);
begin
b := false;
-- b := 0; This would give an error message.
if b then
dbms_output.put_line(2);
end if;
c := 1234567.891;
dbms_output.put_line(c);
end;
/
-- 10 - 3 = 7 is the maximum number of positions
-- allowed BEFORE the decimal point.
-- 3 is the number of digits after the decimal point.
----------------------------------------------------
b = False
if b:
print(2)
if not b:
print(3)
c = 1234567.891
print(c)
import sys
print(sys.maxsize) # largest "int"
print(sys.maxsize * sys.maxsize) # long integers are essentially unlimited
=========================================
-- A recursive function
create or replace function factorial(
num in number)
return number
as
begin
if num = 0
then return 1;
else return num * factorial(num - 1);
end if;
end;
/
-- This is how to call that function.
declare
z number;
begin
z := factorial(3);
dbms_output.put_line(z);
end;
/
-----------------------------------------
def factorial(num):
if num == 0:
return 1
else:
return num * factorial(num - 1)
z = factorial(3)
print(z)
The largest possible factorial is factorial(972).
After that it dies with a recursion level error.
===========================================
create or replace procedure pr(
str in varchar2)
as
begin
dbms_output.put_line(str);
end;
/
begin
pr('Listening to you I get the music');
pr(1234);
end;
/
--------------------------
This is not necessary.
"print" is short enough in Python.
===========================================
-- The "loop - end loop" loop.
-- An "almost endless" loop
-- You need to jump out with exit.
create or replace procedure vector
as
i number;
begin
i := 0;
loop -- potentially an endless loop
dbms_output.put_line(i);
i := i + 1;
exit when i = 10; -- This makes it "not endless"
end loop;
end;
/
begin
vector;
end;
/
--------------------------------------------
def vector():
i = 0
while True:
print(i)
i = i + 1
if i == 10:
break
vector()
=================================================
create or replace procedure matrix
as
i number;
begin
for i in 1 .. 10
loop
dbms_output.put_line(i);
end loop;
end;
/
begin
matrix;
end;
/
-------------------------------------------------------------
# Note that range(1, 11) = 1, 2, 3, ... 10
def matrix():
for i in range(1, 11):
print(i)
matrix()
========================================
-- More examples of for loops
create or replace procedure matrix
as
i number;
begin
for i in 10 .. 15 -- works
loop
dbms_output.put_line(i);
end loop;
for i in -5 .. 5 -- works
loop
dbms_output.put_line(i);
end loop;
for i in 20 .. 20 -- works, done once
loop
dbms_output.put_line(i);
end loop;
for i in 5 .. 4 -- Nothing happens
loop
dbms_output.put_line(i);
end loop;
for i in reverse 9 .. 6 -- Nothing happens
loop
dbms_output.put_line(i);
end loop;
for i in reverse 6 .. 9 -- works, counts down
loop
dbms_output.put_line(i);
end loop;
end;
/
begin
matrix;
end;
/
-------------------------
def matrix():
for i in range(10, 16): # 10, 11... 15
print(i)
for i in range(-5, 6): # -5, -4, ... 5
print(i)
for i in range(20, 21): # 20
print(i)
for i in range(20, 20: # nothing
print(i)
for i in range(9, 6): # nothing
print(i)
for i in range(9, 6, -1): # 9, 8, 7
print(i)
matrix()
=======================================
-- More loops
-- This is a nested for loop.
create or replace procedure testnestedloop
as
i number;
j number;
line varchar2(80);
begin
for i in 1 .. 10
loop
line := i || '::';
for j in 20 .. 30
loop
line := line || ' ' || j;
end loop;
dbms_output.put_line(line);
end loop;
end;
/
begin
testnestedloop;
end;
/
------------------------------
def testnestedloop():
for i in range(1, 11):
line = str(i) + '::'
for j in range(20, 31):
line = line + ' ' + str(j)
print(line)
testnestedloop()
========================================
-- Print a triangle matrix.
create or replace procedure matrix
as
i number;
j number;
line varchar2(80);
begin
for i in 1 .. 10 -- Count rows
loop
line := '';
for j in 1 .. i+1 -- Count columns in row
loop
line := line || ' ' || j;
end loop;
dbms_output.put_line(line);
end loop;
end;
begin
matrix;
end;
/
---------------------------------
def matrix()
for i in range(1,11):
line = ''
for j in range(1, i+2):
line = line + ' ' + str(j)
print(line)
matrix()
================================================