October 21, 2018
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.

By submitting your information, you agree that developer.com may send you developer offers via email, phone and text message, as well as email offers about other products and services that developer believes may be of interest to you. developer will process your information in accordance with the Quinstreet Privacy Policy.

## Most Popular Developer Stories

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