Using BULK COLLECT INTO construct in Oracle PLSQL

At times when you are programming in PLSQL you would want to fetch multiple records into an ARRAY or some collection. One approach to do is to loop through the result of the SQL query and then keep adding each record to the ARRAY or Collection. PLSQL provides another construct to perform the same operation in a much more time efficient manner by using the BULK COLLECT INTO construct. In the first approach there has to be frequent switching between the PLSQL and the SQL engines which causes overhead but while using BULK COLLECT INTO that switching overhead is reduced considerably. To know more about this difference and to see the time variations in both the cases, I would recommend you to read this.

Using BULK COLLECT INTO construct:

Consider a STUDENT table with the following data:
Data1

I want to fetch all the Students age into a different array which I would do using BULK COLLECT INTO as shown below:

[sql]
SET SERVEROUTPUT ON;
DECLARE
–Define a Collection Type
TYPE NumberArray IS TABLE OF STUDENT.AGE%TYPE;

–Declare a variable of the Type defined above.
–This holds the student age fetched.
StudentIds NumberArray;
BEGIN
/*
Select age from the table and bulk collect them in to the
variable of collection type.
*/
SELECT age BULK COLLECT INTO StudentIds FROM STUDENT;

/*
Looping through the collection just to confirm that the
data has really been fetched into it.
*/
for i in 1..StudentIds.COUNT
LOOP
DBMS_OUTPUT.PUT_LINE(StudentIds(i));
END LOOP;
END;
[/sql]

And the output would be:
BulkColelctOutput

Note: I have used the SQL Worksheet tool provided with JDeveloper to execute this example.

Using ROWNUM with ORDER BY in Oracle SQL

Lot of times you have tried to use ROWNUM clause along along with the ORDER BY clause and must have been treated with a different output than you expected. You would expect the ROWNUM to be applied after the results have been ordered by the column, but instead it applies the ROWNUM and then does an order by.

Lets look at an example:
Support you had the following data in a student table:
Data1
And we want to find out the youngest student, you would immediately write:
[sql]SELECT * FROM student WHERE ROWNUM=1 ORDER BY age;[/sql]
which would give the output:
Data2

But looking at the data above John/David would should have been the youngest. As I said before, the ORDER BY clause is applied after the ROWNUM selects one row. Lets edit the query a bit and try:
[sql]SELECT * FROM (SELECT * FROM student ORDER BY age) WHERE ROWNUM=1;[/sql]
which would give us the correct output:
Data3

The idea here is to first order the rows/records by moving the order by query as a nested sql and then applying ROWNUM on the resultant data.

MySQL: World’s Most popular Open Source database

What is MySQL?

MySQL is an Open Source database management system(DBMS) that one can download for free from MySQL Website (www.mysql.com). MySQL is one of the world’s most popular systems for Java web applications because of its consistent fast performance, high reliability and ease of use. MySQL runs on more than 20 platforms including Linux, Windows, OS/X, HP-UX, AIX, Netware, giving the developers right kind of flexibility. 

MySQL is owned and sponsored by a single for-profit firm, the Swedish company MySQL AB, now a subsidiary of Sun Microsystems, which holds the copyright to most of the codebase. The project’s source code is available under terms of the GNU General Public License, as well as under a variety of proprietary agreements.

Continue reading “MySQL: World’s Most popular Open Source database”

MySQL: World's Most popular Open Source database

What is MySQL?

MySQL is an Open Source database management system(DBMS) that one can download for free from MySQL Website (www.mysql.com). MySQL is one of the world’s most popular systems for Java web applications because of its consistent fast performance, high reliability and ease of use. MySQL runs on more than 20 platforms including Linux, Windows, OS/X, HP-UX, AIX, Netware, giving the developers right kind of flexibility. 

MySQL is owned and sponsored by a single for-profit firm, the Swedish company MySQL AB, now a subsidiary of Sun Microsystems, which holds the copyright to most of the codebase. The project’s source code is available under terms of the GNU General Public License, as well as under a variety of proprietary agreements.

Continue reading “MySQL: World's Most popular Open Source database”