Function my_sqrt

The following example shows an SQL PL function named my_sqrt, that computes the square root of a floating point (double) number to a specified accuracy.

 1 create function my_sqrt(
 2     in val double,
 3     in eps double)
 4 returns double
 5 begin
 6     declare low, high, mid, oldmid, midsqr double;
 7     if val < 0.0 then
 8         signal sqlcode eINVARG set message_text = "sq root of negative number";
 9     end if;
10     if eps <= 0.0 or eps > 0.01 then
11         signal sqlcode eINVARG set message_text = "2nd arg must be >= 0.0 and < 0.01";
12     end if;
13     set (low, mid, high, oldmid) = (0.0, val, val, -1.0);
14     while abs(oldmid - mid) >= eps do
15         set oldmid = mid;
16         set mid = (high + low)/2.0;
17         set midsqr = mid * mid;
18         if midsqr > val then
19             set high = mid;
20         else
21             set low = mid;
22         end if;
23     end while;
24     return mid;
25 end;
26 ;

A SQL PL stored function is declared just like a stored procedure except—as shown in line 4—the data type of the value returned by the function must be specified. The algorithm used to compute the square root is not efficient but it is simple enough to understand. The function computes an estimate of the square root of the first argument, val to the accuracy specified by the second argument, eps.

Note how the SIGNAL statements (lines 7-12) are used by the function to report when the argument values are invalid. You can see the results from the script below.

rdm-sql: open database bookshop;
rdm-sql: :compile my_sqrt.sql
rdm-sql: select my_sqrt(6.0, 0.00001), sqrt(6.0);
    my_sqrt(6.0, .00001)                sqrt(6.0)
                   2.449                    2.449
rdm-sql: select my_sqrt(6.0, 0.01), sqrt(6.0);
       my_sqrt(6.0, .01)                sqrt(6.0)
                   2.455                    2.449
rdm-sql: select my_sqrt(6.0, 0.1), sqrt(6.0);
****RDM SQL Error eINVARG: invalid argument value: 2nd arg must be >= 0.0 and < 0.01
rdm-sql: select my_sqrt(-1.0, 0.00001), sqrt(6.0);
****RDM SQL Error eINVARG: invalid argument value: sq root of negative number