DEV Community

Nikti Paul
Nikti Paul

Posted on

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;
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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);
Enter fullscreen mode Exit fullscreen mode

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 description
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;
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

For Loop Syntax:-

FOR i IN <start>..<stop> LOOP
 <statements>
END LOOP;
Enter fullscreen mode Exit fullscreen mode

While Loop Syntax:-

WHILE <condition> LOOP
 <statements>
END LOOP;
Enter fullscreen mode Exit fullscreen mode

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>;
Enter fullscreen mode Exit fullscreen mode

for e.g:

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

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

dbms_output.put_line(<statement>);
Enter fullscreen mode Exit fullscreen mode

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

dbms_output.put_line('Hello '||'World!');
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

This will give output:

Output of the above code

In Oracle Application Express 11g:

Image description

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

Top comments (0)