Forem: Gerald Venzl 🚀

How to write an anonymous PL/SQL function and use it in your SQL statement

Gerald Venzl 🚀 — Mon, 20 Mar 2023 15:00:00 +0000

Have you ever encountered a situation when you were in the middle of writing a SQL query and thought, “if only I could write a quick PL/SQL function for this, it would make this quick and easy?” But, unfortunately, you don’t have any privileges to create any functions in the schema. Luckily, since Oracle Database 12c, there is an answer for you.

With the release of Oracle Database 12.1.0.1 in 2013, Oracle introduced the capability of writing anonymous PL/SQL declarations as part of a SQL query. This is done with the WITH clause also referred to as Common Table Expression clause or CTE, which can now contain said anonymous PL/SQL declarations.

Let’s imagine for a second that you have a table products containing information about various sellers’ products. This could look something like this:

CREATE TABLE products (
  id     NUMBER         NOT NULL PRIMARY KEY,
  name   VARCHAR(255)   NOT NULL,
  url    VARCHAR(255)
);

INSERT INTO products VALUES (1, 'AirPods Pro (2nd gen)', 'https://www.apple.com/shop/product/MQD83AM/A/airpods-pro');

INSERT INTO products VALUES (2, 'SanDisk - Ultra 512GB USB 3.0', 'https://www.bestbuy.com/site/sandisk-ultra-512gb-usb-3-0-flash-drive-black/6422265.p');

commit;

You would like to write a query that provides you, say, the product name, the domain where each product is sold and the URL. But you want to ensure that the first letter of the name and the domain name is always capitalized. Sure, you can do that in pure SQL, but you are comfortable with PL/SQL, and such functions are written quickly:

get_domain_name function

FUNCTION get_domain_name
  (
    p_url VARCHAR2,
    p_sub_domain VARCHAR2 DEFAULT 'www.'
  )
  RETURN VARCHAR2
IS
  v_begin_pos BINARY_INTEGER;
  v_length    BINARY_INTEGER;
BEGIN
  v_begin_pos := INSTR(p_url, p_sub_domain) + LENGTH(p_sub_domain);
  v_length := INSTR(SUBSTR(p_url, v_begin_pos), '.') - 1;
  RETURN SUBSTR(p_url, v_begin_pos, v_length);
END;

capitalize function

FUNCTION capitalize (p_string VARCHAR2)
  RETURN VARCHAR2
IS
BEGIN
  RETURN CONCAT(UPPER(SUBSTR(p_string,1,1)), SUBSTR(p_string,2));
END;

With this new feature, you can simply wrap both these functions into the WITH clause and reuse them within your SQL block, even multiple times:

WITH
  -- Function to capitalize input string
  FUNCTION capitalize
    (
      p_string VARCHAR2
    )
    RETURN VARCHAR2
  IS
  BEGIN
    RETURN CONCAT(UPPER(SUBSTR(p_string,1,1)), SUBSTR(p_string,2));
  END;
  -- Function to retrieve the domain name from a URL
  FUNCTION get_domain_name
    (
      p_url VARCHAR2,
      p_sub_domain VARCHAR2 DEFAULT 'www.'
    )
    RETURN VARCHAR2
  IS
    v_begin_pos BINARY_INTEGER;
    v_length    BINARY_INTEGER;
  BEGIN
    v_begin_pos := INSTR(p_url, p_sub_domain) + LENGTH(p_sub_domain);
    v_length := INSTR(SUBSTR(p_url, v_begin_pos), '.') - 1;
    RETURN SUBSTR(p_url, v_begin_pos, v_length);
  END;
-- SQL statement
SELECT capitalize(name) as name, capitalize(get_domain_name(url)) AS domain_name, url
  FROM products;

To the database, this is just another SELECT statement with a common table expression. You do not need any write privileges on the schema for the user you are connected with:

SQL> WITH
  2    -- Function to capitalize input string
  3    FUNCTION capitalize
  4      (
  5        p_string VARCHAR2
  6      )
  7      RETURN VARCHAR2
  8    IS
  9    BEGIN
 10      RETURN CONCAT(UPPER(SUBSTR(p_string,1,1)), SUBSTR(p_string,2));
 11    END;
 12    -- Function to retrieve the domain name from a URL
 13    FUNCTION get_domain_name
 14      (
 15        p_url VARCHAR2,
 16        p_sub_domain VARCHAR2 DEFAULT 'www.'
 17      )
 18      RETURN VARCHAR2
 19    IS
 20      v_begin_pos BINARY_INTEGER;
 21      v_length    BINARY_INTEGER;
 22    BEGIN
 23      v_begin_pos := INSTR(p_url, p_sub_domain) + LENGTH(p_sub_domain);
 24      v_length := INSTR(SUBSTR(p_url, v_begin_pos), '.') - 1;
 25      RETURN SUBSTR(p_url, v_begin_pos, v_length);
 26    END;
 27  -- SQL statement
 28  SELECT capitalize(name) as name, capitalize(get_domain_name(url)) AS domain_name, url
 29    FROM products;
 30 /

NAME                             DOMAIN_NAME    URL
________________________________ ______________ _______________________________________________________________________________________
AirPods Pro (2nd gen)            Apple          https://www.apple.com/shop/product/MQD83AM/A/airpods-pro
SanDisk - Ultra 512GB USB 3.0    Bestbuy        https://www.bestbuy.com/site/sandisk-ultra-512gb-usb-3-0-flash-drive-black/6422265.p

Percentage calculations using SQL Window Functions

Gerald Venzl 🚀 — Tue, 06 Dec 2022 14:00:00 +0000

Sometimes you have the requirement to calculate percentages on some values of your data. There are multiple ways of doing it, of course, but often people are not aware that you do not have to calculate these percentages in the application itself or via a SQL statement that queries the same table multiple times to first calculate the denominator and then calculate the actual percentage. Instead, you can use SQL window functions to run a variety of complex calculations over different groups of data in a single pass! Window functions were introduced in the SQL:2003 standard back in 2003 and although the SQL standard calls these Window Functions, Oracle Database has them documented as Analytic Functions.

Let’s take a look.

Example Data

First, we need to have some data that we can run queries against. Let’s use movies and calculate the percentages of the actors’ earnings – fictional, of course. 🙂

CREATE TABLE movie_earnings (
  movie_name   VARCHAR(30),
  actor_name   VARCHAR(30),
  earnings     NUMBER
);

INSERT INTO movie_earnings VALUES
   ('Thor: Love and Thunder', 'Chris Hemsworth', 5000000);
INSERT INTO movie_earnings VALUES
   ('Thor: Love and Thunder', 'Natalie Portman', 2000000);
INSERT INTO movie_earnings VALUES
   ('Thor: Love and Thunder', 'Christian Bale',  1000000);

INSERT INTO movie_earnings VALUES
   ('Minions: The Rise of Gru', 'Steve Carell',  500000);
INSERT INTO movie_earnings VALUES
   ('Minions: The Rise of Gru', 'Pierre Coffin', 100000);
COMMIT;

Percentage Total Earnings per Actor

Back in the days before window functions were available, and unfortunately still too often today, you will find queries with the two-step approach that will read the same table twice, first to get the denominator and second to run the actual computation of the percentage using the denominator:

SELECT movie_name, actor_name,
       ROUND(100 * earnings/sums.total, 2) AS percent
FROM movie_earnings, (
       SELECT SUM(earnings) AS total
         FROM movie_earnings
     ) sums
ORDER BY percent DESC;

MOVIE_NAME               ACTOR_NAME      PERCENT
------------------------ --------------- -------
Thor: Love and Thunder   Chris Hemsworth   58.14
Thor: Love and Thunder   Natalie Portman   23.26
Thor: Love and Thunder   Christian Bale    11.63
Minions: The Rise of Gru Steve Carell       5.81
Minions: The Rise of Gru Pierre Coffin      1.16

While this produces the correct result, it has an unnecessary second query on the same table. With window functions, however – and once again, they have been around in some databases for more 20 years now and added to the SQL standard in 2003 – you can calculate the denominator of the percentage in a single go, no need to read the table twice:

SELECT
    movie_name, actor_name,
    ROUND(
       100 * earnings / SUM(earnings) OVER (),
    2) AS percent
FROM movie_earnings
ORDER BY percent DESC;

MOVIE_NAME               ACTOR_NAME      PERCENT
------------------------ --------------- -------
Thor: Love and Thunder   Chris Hemsworth   58.14
Thor: Love and Thunder   Natalie Portman   23.26
Thor: Love and Thunder   Christian Bale    11.63
Minions: The Rise of Gru Steve Carell       5.81
Minions: The Rise of Gru Pierre Coffin      1.16

The window function in the above statement is the SUM(earnings) OVER () part. Concise, isn’t it?

Percentage of Earnings per Movie

Of course, calculating the percentage of earnings for all movies is interesting but perhaps more interesting is to know who the highest-paid actor in a given movie is. Prior to window functions, you may have written a statement like this:

SELECT
    e.movie_name, e.actor_name,
    ROUND(100 * e.earnings/s.total, 2) AS percent
FROM movie_earnings e, (
    SELECT SUM(earnings) AS total, movie_name
      FROM movie_earnings
        GROUP BY movie_name) s
WHERE e.movie_name = s.movie_name
ORDER BY movie_name, percent DESC;

MOVIE_NAME               ACTOR_NAME      PERCENT
------------------------ --------------- -------
Minions: The Rise of Gru Steve Carell      83.33
Minions: The Rise of Gru Pierre Coffin     16.67
Thor: Love and Thunder   Chris Hemsworth    62.5
Thor: Love and Thunder   Natalie Portman      25
Thor: Love and Thunder   Christian Bale     12.5

The benefit of using window functions is that we can reuse the same query from before and just change the way how we partition the data for our analysis. The previous statement had a window function with a trailing OVER() clause. That OVER() clause is there to tell the window function, among other things, what part of the retrieved data to apply the calculation on. Window functions use values from one or multiple rows to return a value for each row. This contrasts with aggregate functions, which return a single value for multiple rows. In short, window functions always have an OVER() clause while any function without an OVER() clause is not a window function, but rather an aggregate or single-row (scalar) function.

To calculate the percentages per movie, all you have to do is to tell the window function to look at the data on a per-movie basis. In other words, partition the retrieved data by the movie_name and apply the calculation for each partition or window of data:

SELECT
    movie_name, actor_name,
    ROUND(
       100 * earnings / SUM(earnings)
                          OVER (PARTITION BY movie_name),
    2) AS percent
FROM movie_earnings
ORDER BY movie_name, percent DESC;

MOVIE_NAME               ACTOR_NAME      PERCENT
------------------------ --------------- -------
Minions: The Rise of Gru Steve Carell      83.33
Minions: The Rise of Gru Pierre Coffin     16.67
Thor: Love and Thunder   Chris Hemsworth    62.5
Thor: Love and Thunder   Natalie Portman      25
Thor: Love and Thunder   Christian Bale     12.5

Pay attention to the (now in a separate line for further illustration purposes) OVER (PARTITION BY movie_name) clause. That’s all that needed to change to execute the percentage calculations per movie.

Percentage of Earnings per Movie and Total Earnings

The cool thing about window functions is that you can use multiple window functions in one SQL statement! So, for example, instead of executing the two statements above, you can just run one single query and retrieve both calculations in one go:

SELECT
    movie_name, actor_name,
    ROUND(
       100 * earnings / SUM(earnings)
                          OVER (PARTITION BY movie_name),
    2) AS ptc_movie,
    ROUND(
       100 * earnings / SUM(earnings)
                          OVER (),
    2) AS ptc_total
FROM movie_earnings
ORDER BY movie_name, ptc_movie DESC;

MOVIE_NAME               ACTOR_NAME      PTC_MOVIE PTC_TOTAL
------------------------ --------------- --------- ---------
Minions: The Rise of Gru Steve Carell        83.33      5.81
Minions: The Rise of Gru Pierre Coffin       16.67      1.16
Thor: Love and Thunder   Chris Hemsworth      62.5     58.14
Thor: Love and Thunder   Natalie Portman        25     23.26
Thor: Love and Thunder   Christian Bale       12.5     11.63

This result provides some interesting insights right away. Here you can quickly see that although Steve Carell got the most money from his movie, it is just a tiny fraction of what others have made with their movies. We know this already, of course, because of the queries earlier on, but here you have the numbers next to each other, making it much more apparent. And, of course, we didn’t need to read the table twice to get the per movie and total earnings percentages either.

Many functions to choose from

Just like with aggregate functions, there are many different window functions available, so always check out the documentation! It just happens to be that Oracle Database has a RATIO_TO_REPORT function that computes the ratio of a value to the sum of a set of values. In other words, there already is a window function to calculate the percentages. So the above query can be further simplified to the following, saving you from having to do the division manually:

SELECT
    movie_name, actor_name,
    ROUND(
       100 * RATIO_TO_REPORT(earnings)
                OVER (PARTITION BY movie_name),
    2) AS ptc_movie,
    ROUND(
       100 * RATIO_TO_REPORT(earnings)
                OVER (),
    2) AS ptc_total
FROM movie_earnings
ORDER BY movie_name, ptc_movie DESC;

MOVIE_NAME               ACTOR_NAME      PTC_MOVIE PTC_TOTAL
------------------------ --------------- --------- ---------
Minions: The Rise of Gru Steve Carell        83.33      5.81
Minions: The Rise of Gru Pierre Coffin       16.67      1.16
Thor: Love and Thunder   Chris Hemsworth      62.5     58.14
Thor: Love and Thunder   Natalie Portman        25     23.26
Thor: Love and Thunder   Christian Bale       12.5     11.63

Conclusion

Window functions, or analytical functions as Oracle calls them, are a powerful way to execute complex calculations over multiple “windows” in a query result.

They are much more compact than other methods and only need to read the data once instead of multiple times, giving you a performance boost for running complex calculations on large data sets.

Window functions should be in the toolbox of any developer who regularly writes SQL!

If you want to learn more about window functions, check out the free Analytic SQL for Developers course from Oracle!

5 ways to get an Oracle Database

Gerald Venzl 🚀 — Tue, 22 Sep 2020 01:17:25 +0000

Do you want to get your hands on an Oracle Database but don't know how? Here are 5 ways to get you going:

LiveSQL

Do you just want to type some awesome SQL and need a database to do so? Then LiveSQL.oracle.com is your friend. LiveSQL is a browser-based SQL scratchpad that not only allows you to pull off some SQL magic but also to save and share your scripts with others. It also comes with a comprehensive library of tutorials and samples. LiveSQL is the best place for anybody that is completely unfamiliar with Oracle Database and wants to get going.

Docker/Container image

If you want to have an Oracle Database on your machine instead, but don't want to worry about setup and configuration, the Oracle provided Docker images are a good choice. All you need is to install Docker, Podman or an equivalent on your machine (Mac or Windows) and pull the Oracle Database image from Oracle's Container Registry. From then on, all you have to remember is:

docker run --name oracle -p 1521:1521 container-registry.oracle.com/database/free:latest

and

docker start oracle

Docker is great for running one or many instances and versions of an Oracle Database on your machine without having to know how to operate (start/stop/setup) them. What you end up with is still a full-fledged Oracle Database.

Vagrant box VM

If you want to have an Oracle Database on your machine, but you prefer to run it inside a Virtual Machine, then the Oracle provided Vagrant scripts will do a great job. HashiCorp's Vagrant is a great tool to provision repeatable VM environments, including VirtualBox VMs. For this scenario, you will need to install Oracle's VirtualBox and HashiCorp's Vagrant on your machine first. Once you have done that, provision a VM via the scripts from the Oracle Vagrant Boxes GitHub repo and let Vagrant take care of the rest. All you have to remember is:

vagrant up

and

vagrant ssh

The Vagrant box is great if you want a scripted and repeatable way of creating a VirtualBox VM that contains an Oracle Database. You can also provision multiple VMs with different versions of the Oracle Database. The VM comes with port forwarding enabled by default, which means that you are able to connect any of your tools from your host directly, say SQL Developer for example, to the database inside the VM and treat the VM like a little embedded server.

Database App Dev VM

If you like the VM approach but don't want or need the repeatable nature of Vagrant, then the Oracle Database Application Development VM is the right choice for you. Simply download the .ova file, import it into VirtualBox, and start the VM. The VM will boot into a graphical Linux desktop.

The Oracle Database App Dev VM comes with tools like SQL Developer and Oracle REST Data Services preinstalled, which makes it a great self-contained, one-stop-shop VM. It too has port forwarding enabled by default, in case you want to connect your tools from your host directly. Another bonus of the App Dev VM is that it also includes some hands-on labs that you can go through.

Always Free Oracle Autonomous Database

If you want an Oracle Database but not on your laptop, then you should check out the Oracle Cloud Free Tier which includes an Always Free Oracle Autonomous Database. Once you have signed up for the free tier and provisioned your Always Free Autonomous Database, you can head over to SQL Developer Web and get going.

The Always Free Tier Oracle Autonomous Database is great if you want the latest and greatest what Oracle has to offer in terms of cloud databases. SQL Developer Web and APEX come out of the box and you can connect any other app or IDE from anywhere around the world, as long as it has access to the internet. And the best part, as long as you use the database, it stays with you forever!

Now, what you are waiting for? Get yourself an Oracle Database!