PL SQL - gregorymorrison/euler1 GitHub Wiki

PL/SQL is Oracle's procedural language. It originated in the early 90's, and closely resembles Ada - no surprise, since it evolved from a government contract. As such it has a somewhat dated feel to it, though Oracle has been keeping it up to date, adding such things as object-orientation and regular expressions. It also has a nice package system that I might rewrite this example to illustrate in the future.

Its compiler is very picky about syntax and context - the compiler in one environment might refuse to compile until you end your code with a slash, while the same compiler in the same editor in a different context will refuse to work unless you remove the slash, and will only help with cryptic error messages. Sigh... It's not exactly a fun language to work in, but it's not complicated and it does pay the bills... Even though I know this logic and know Oracle well, it took me around 20 minutes to get the following version of Euler1 to make Oracle's compiler happy.

To compile and execute this version of Euler1, I used SQLDeveloper, Oracle's free and not-bad-but-dirt-slow environment. In the Connections pane under your database Functions, right-click on New Function.  Click OK in the popup, and replace the generated stub in the new editor pane with the following:

-- Euler1 in PL/SQL

create or replace FUNCTION EULER1 (n IN NUMBER)
   RETURN NUMBER
IS
    retval NUMBER(10);
BEGIN
    retval := 0;

   FOR i IN 1 .. n LOOP
     IF MOD(i,3)=0 or MOD(i,5)=0 THEN
            retval := retval + i;
     END IF;
   END LOOP;

   RETURN retval;
END EULER1;

Click the green Compile arrow in the toolbar to compile, then in the Connections tree, right-click/Refresh on Functions to see your shiny new function. Right-click/Run on it, and replace the generated stub with this driver:

DECLARE
  v_Return NUMBER;
BEGIN
  v_Return := EULER1(999);
  DBMS_OUTPUT.PUT_LINE('v_Return = ' || v_Return);
END;

Click OK, and you'll see the following appear in the Running pane:

Connecting to the database XE.
v_Return = 233168
Process exited.
Disconnecting from the database XE.

If you want to execute your code from a SQL worksheet, you can run it as a script (F5); just make sure you enable DBMS Output first so that your output gets sent to the console. Here, I've wrapped my PL/SQL function with a SQL declaration and call statement, which resembles my Ada version almost verbatim:

-- Euler1 in PL/SQL

declare

    function Euler1 (n in number) return number is
        result number(10);
    begin
        result := 0;

        for i in 1 .. n loop
            if mod(i,3)=0 or mod(i,5)=0 then
                result := result + i;
            end if;
        end loop;
        return result;
    end Euler1;

begin
    dbms_output.put_line(Euler1(999));
end;