September 20, 2017
Hot Topics:

# Sample Chapter: Efficient PL/SQL

#### Binary Operations

To use the available PL/SQL features, first learn which features are available. (If you are thinking this is a polite way of saying: "Go reread those manuals," you're correct.) For example, if we need to perform the logical numeric operations, AND, OR, and XOR, with a little bit of arithmetic, the following PL/SQL function can be built for binary AND. We will not delve too deeply into the specifics of how it works because as we will see imminently, creating such a function is a wasted effort.

```SQL> create or replace
2  function binary_and(x number,y number)return number is
3    max_bin number(22):=power(2,64);
4    l_x number :=x;
5    l_y number :=y;
6    result number :=0;
7 begin
8   for i in reverse 0 ..64 loop
9     if l_x >=max_bin and l_y >=max_bin then
10       result :=result +max_bin;
11     end if;
12     if l_x >=max_bin then
13       l_x :=l_x -max_bin;
14     end if;
15     if l_y >=max_bin then
16       l_y :=l_y -max_bin;
17     end if;
18     max_bin :=max_bin/2;
19   end loop;
20   return result;
21 end;
22 /

Function created.
```

This is a wasted effort because such a function already exists, namely the BITAND function. This is perhaps a special case, and a developer could possibly be excused for building his own version because although the BITAND function existed as far back as version 7 (and probably before), Oracle didn't document its existence until version 8.1.7. Performance-wise, there is no comparison. Comparing 50,000 executions of the home-grown BITAND PL/SQL function to its native counterpart using the SQL*Plus timing facility shows the dramatic difference.

```SQL> declare
2    x number;
3 begin
4   for i in 1 ..50000 loop
5     x:=binary_and(i,i+1);
6   end loop;
7 end;
8 /

PL/SQL procedure successfully completed.

Elapsed:00:00:07.07
SQL> declare
2    x number;
3 begin
4   for i in 1 ..50000 loop
5     x:=bitand(i,i+1);
6   end loop;
7 end;
8 /

PL/SQL procedure successfully completed.

Elapsed:00:00:00.01
```

Page 12 of 19

Comment and Contribute

(Maximum characters: 1200). You have characters left.

## Enterprise Development Update

Don't miss an article. Subscribe to our newsletter below.

## Most Popular Developer Stories

Thanks for your registration, follow us on our social networks to keep up-to-date