Posts tagged: PL/SQL

Rank function in PL/SQL

Here’s some code which demonstrates the usefulness of the Rank function in PL/SQL (can also be used in T-SQL):

Sample Data:

user_id email_address email_type activity_date
6437 jim@yahoo.com A 6/21/2009
5271 fred@gmail.com A 6/17/2009
5271 fred12@gmail.com A 6/29/2009
6988 steve@yahoo.com B 6/09/2009
5271 freddy@hotmail.com B 6/26/2009
6988 stephen@comcast.com A 6/22/2009
6437 jimbo@gmail.com B 6/23/2009
5271 fredericko@cantina.com C 6/24/2009

SELECT user_id,
     email_address,
     email_type,
 RANK () OVER (PARTITION BY user_id ORDER BY email_type, activity_date DESC) rnk
FROM email_table

Output:

user_id email_address email_type activity_date rnk
6437 jim@yahoo.com A 6/21/2009 1
6437 jimbo@gmail.com B 6/23/2009 2
5271 fred12@gmail.com A 6/29/2009 1
5271 fred@gmail.com A 6/17/2009 2
5271 freddy@hotmail.com B 6/26/2009 3
5271 fredericko@cantina.com C 6/24/2009 4
6988 stephen@comcast.com A 6/22/2009 1
6988 steve@yahoo.com B 6/09/2009 2


(Notice on the two records where user_id = 5271 and email_type = ‘A’ the rank of 1 is given to the record with the most current activity_date. This is because the ORDER BY portion of the RANK function sorts the records for each unique user_id first by email_type and second by activity_date. This makes it possible to select the most current record)

And with this code we can grab only the email addresses with a type of ‘A’ and the most current activity_date by using the WHERE rnk = 1 clause:

SELECT email_address FROM
 (SELECT user_id,
       email_address,
       email_type,
  RANK () OVER (PARTITION BY user_id ORDER BY email_type, activity_date DESC) rnk
 FROM email_table)
WHERE rnk = 1;

Output:

email_address
jim@yahoo.com
fred12@gmail.com
stephen@comcast.com

count and rowcount in PL/SQL

I promised Marcus that I would post some content to this blog and be an actual contributor to the blog instead of just a typical blog lurker/voyeur (like I usually am with blogs), so here’s a bit of information about some basic SQL commands that have proved useful to me lately in PL/SQL: count() and sql%rowcount. They can be very helpful when issuing sql statements where you need to keep track of the number of records affected by the statement.

In the procedure I was working on recently I had an UPDATE statement that looked like this:

UPDATE {table1}
SET {column1} = {value1}
WHERE {column1} = {value2}
AND {column2} = {value3};

and I wanted to find out how many records were a) going to be affected by the statement as well as how many records were b) actually affected by the statement when the statement was run.

I set up 2 local variables and initialized them to zero:

count1 number(7) := 0;
count2 number(7) := 0;

then I used this statement to pass the number of records which were going to be affected by the UPDATE statement to count1:

SELECT count(*)
INTO count1
FROM {table1}
WHERE {column1} = {value2}
AND {column2} = {value3};

and directly after the UPDATE statement I used this code to pass the number of records that were actually updated by the statement to count2:

count2 := sql%rowcount;

These 2 variables can now be compared to each other for error handling purposes to make sure the correct number of records were updated, they can be used for display purposes, used in subsequent IF statements or in a variety of other practical ways.

This is basically what the code looked like:

IS
count1 number(7) := 0;
count2 number(7) := 0;
BEGIN
SELECT count(*)
INTO count1
FROM {table1}
WHERE {column1} = {value2}
AND {column2} = {value3};

UPDATE {table1}
SET {column1} = {value1}
WHERE {column1} = {value2}
AND {column2} = {value3};
count2 := sql%rowcount;

IF (count1 = count2) THEN
dbms_output.put_line(‘Number of records match!’);
END IF;

Panorama theme by Themocracy