Sample Chapter: Efficient PL/SQL
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