Printing the largest element from an array using PLSQL (in Oracle Application Express 11g)

Printing largest element from an array is a basic code. Most of us have done it using c,c++,Java,Python,etc.
But have you thought of printing the same using PLSQL?

Here I am going to show you how to print the largest element from an array using PLSQL in Oracle Application Express 11g.

Prerequistics

1. Oracle Application Express 11g should be installed in your system.
2. Basic SQL knowledge is required.
3. Basic coding knowledge is required.

Let's Begin with the Logic

To print largest element, first we should assume that 0 is the largest element and declare a variable say maxnum and assign 0 to it like,

int maxnum = 0; (where 0 may or may not belong to the array)

In some cases people also declare the first element of the array as the largest element.
Now,
we will use loop and check whether maxnum is smaller than current element or not, if it is true then we will replace it with the current element and if it is false then we will skip it. Like,

if (maxnum < current_element)
  maxnum = current_element;

After the loop end, the value of maxnum will be the correct ans.

Let's Begin with PLSQL

To write a the above code in PLSQL you should know about:-

• Declaration of statement.
• Executable command.
• Variable,Operators and Datatypes.
• Conditions.
• Loops.
• Arrays and how to declare an array.
• Output Statement.

1. Declaration of statement.
It is an optional section in PLSQL and it is used to define variables, cursors, subprograms, etc.
It starts with DECLARE

e.g:

DECLARE
 num INTEGER := 20;

2. Executable Command.
Well, it is a mandatory section and all the statements enclosed in this blocks are executable statements.
It is eclosed between BEGIN and END.

e.g:

DECLARE
 num INTEGER := 20;
BEGIN
 dbms_output.put_line(num);
 num := num + 30;
 dbms_output.put_line(num);

3.Variable,Operators and Datatypes.
Naming a Variable in PLSQL is not much different from other programming languages.
Even Operators are also same like +,-,*,/, but = is represented here as := and == is represented as =.
Datatypes in PLSQL are different,
Here strings are represented as VARCHAR2,int as INTEGER,NUMBER(Scale,Precision) and many more.

Image Source: Oracle Docs

4.Condition
Like other programming languages PLSQL also have condition statements.
Here IF ELSE is implemented as:-

IF <condition> THEN
 <statement 1>;
ELSE
 <statement 2>;
END IF;

for multiple IF statement

IF <condition 1> THEN
 <statement 1>;
ELSIF <condition 2> THEN
 <statement 2>;
ELSIF <condition 3> THEN
 <statement 3>
ELSE
 <statement 4>
END IF;

5.Loops
There are three types of loops in PLSQL
(a) Basic Loop
(b) For Loop
(c) While Loop

Basic Loop Syntax:-

LOOP 
 <statements>
 <exit condition>
END LOOP;

For Loop Syntax:-

FOR i IN <start>..<stop> LOOP
 <statements>
END LOOP;

While Loop Syntax:-

WHILE <condition> LOOP
 <statements>
END LOOP;

Note: This Loop statements should be enclosed between the executable statements.

6. Array
To declare an array in PLSQL we write the statement as:-

type <array_datatype_name> IS VARRAY(<size>) OR <DATATYPE>;
<array_variable_name> <array_datatype_name>;

for e.g:

DECLARE 
 type intarray is VARRAY(10) OF INTEGER;
 myarray intarray(10,20,30,40,50,60,70,80);

7. Output Statement
To print any statement in PLSQL we use:-

dbms_output.put_line(<statement>);

To concatenate to strings we use || double pipes
like,

dbms_output.put_line('Hello '||'World!');

Let's Begin with writing the code

So here we are writing the code for largest element in an array.

So first we need to declare the required variables:

DECLARE 
 type intarray is VARRAY(10) OF INTEGER;
 myarray intarray;
 total INTEGER;
 maxnum INTEGER;

now, let's use the logic inside the executable statements.

BEGIN
 myarray := intarray(12,43,56,32,88,46,67,50,66,74);
 total := myarray.count;
 maxnum := 0;
FOR i IN 1..total LOOP
 IF maxnum < myarray(i) THEN
  maxnum := myarray(i);
 END IF;
END LOOP;
dbms_output.put_line('Maximum number in the array is '|| maxnum);
END;

So final code becomes:-

DECLARE 
 type intarray is VARRAY(10) OF INTEGER;
 myarray intarray;
 total INTEGER;
 maxnum INTEGER;
BEGIN
 myarray := intarray(12,43,56,32,88,46,67,50,66,74);
 total := myarray.count;
 maxnum := 0;
FOR i IN 1..total LOOP
 IF maxnum < myarray(i) THEN
  maxnum := myarray(i);
 END IF;
END LOOP;
dbms_output.put_line('Maximum number in the array is '|| maxnum);
END;

This will give output:

In Oracle Application Express 11g:

Thanks for going through this little blog. If you find any thing wrong please comment below. Don't hesitate to give feedbacks and suggestions.