Introduction
For a 30+ year old language, SQL is doing pretty good in terms of its relevance and usefulness. It can solve a lot of problems, even a lot of the problems people believe require a traditional programming language. The legendary Tom Kyte, when asked in an interview about the most underutilized feature of the Oracle Database, replied, SQL! He went on to emphasize that a lot of procedural code can be replaced using native, advanced SQL capabilities. With a mastery of this language, a host of different problems can be solved very easily with clean, elegant declarative code.
Of course SQL has its Programmatic extension: PL/SQL. It helps bridge the gap between data manipulation and complex business logic. A common advice given by Tom and other Oracle experts is to only use PL/SQL when SQL cannot achieve the task at hand. Given the extreme capabilities of SQL, that list of limitations is not that long.
In this post I will share an example scenario of a problem that was solved using PL/SQL and show how plain old SQL can do the exact same function much simpler and with a lot less code.
A sample problem
Imagine a requirement came in as such; Your business would like to do some predictive data analysis. They would like to see how much an initial $1000 credit card balance that remains unpaid, at a compounding interest rate of 22% per period would eventually cost if the defaulter fails to make any payment for a period of 12 months. They also want to be able to play around with the numbers and store the various results of these tests in a table.
A PL/SQL solution
Using PL/SQL and Table Functions, we could create a simple application for the business users. Table functions are functions that can be called in the FROM clause of a SELECT statement as if they were tables. To learn more about these functions, see this course on the devgym.
Create an object Type
We create an object type to define the structure of each row returned by our table function.
--create object type
CREATE OR REPLACE TYPE compound_interest_tab_ot IS OBJECT
(
period NUMBER
,initial_principal NUMBER
,interest_rate NUMBER
,new_principal NUMBER
)
/Create a nested table
We then define a nested table collection type containing objects of the type that we just created.
--create nested table
CREATE OR REPLACE TYPE compound_interest_tab_nt IS TABLE OF compound_interest_tab_ot
/Table function
We create a table function to do the computation
--create table function
CREATE OR REPLACE FUNCTION compute_compound_interest
(
initial_principal_in IN NUMBER
,preiodic_rate_in IN NUMBER
,periods_in IN NUMBER
)
RETURN compound_interest_tab_nt
AUTHID DEFINER
IS
v_initial_principal NUMBER := initial_principal_in;
v_periodic_rate NUMBER := preiodic_rate_in;
v_profit NUMBER;
v_new_principal NUMBER;
v_period NUMBER := periods_in;
v_repitition NUMBER := 1;
v_ci_tab compound_interest_tab_nt := compound_interest_tab_nt();
BEGIN
v_new_principal := v_initial_principal;
WHILE v_repitition <= v_period
LOOP
v_initial_principal := v_new_principal;
v_profit := v_new_principal * v_periodic_rate;
v_new_principal := v_profit + v_initial_principal;
v_ci_tab.EXTEND(1);
v_ci_tab(v_ci_tab.COUNT) := compound_interest_tab_ot(
v_repitition,
ROUND(v_initial_principal, 2),
ROUND(v_profit, 2),
ROUND(v_new_principal, 2)
);
v_repitition := v_repitition + 1;
END LOOP;
RETURN v_ci_tab;
END compute_compound_interest;
/The function above essentially computes compound interest. It accepts 3 parameters; the principal, the interest rate expressed as a decimal and the number of compuonding periods. We can use this function to calculate compound interest and solve the business problem defined above.
Solving our Problem
To compute compound interest for a starting principal of 1000, interest rate of 22%, and period of 12 compounding periods (Months in this case) we can use a query like :
SELECT *
FROM compute_compound_interest( 1000, 0.22, 12);
PERIOD INITIAL_PRINCIPAL INTEREST_RATE NEW_PRINCIPAL
------ ----------------- ------------- -------------
1 1000 220 1220
2 1220 268.4 1488.4
3 1488.4 327.45 1815.85
4 1815.85 399.49 2215.33
5 2215.33 487.37 2702.71
6 2702.71 594.6 3297.3
7 3297.3 725.41 4022.71
8 4022.71 885 4907.71
9 4907.71 1079.7 5987.4
10 5987.4 1317.23 7304.63
11 7304.63 1607.02 8911.65
12 8911.65 1960.56 10872.21As seen above, on an $1000 of unpaid credit card debt can accumulate to $10872.21 in just 12 months given the conditions in this example. Safe to say, it’s not a great idea to not pay off your credit cards!
Store results in a table
Of course the business users also want to see these results stored in a table. We can create a sample table to store the results of this query with the code below:
CREATE TABLE interest_on_loan (
loan_id NUMBER
,period NUMBER
,loan_type VARCHAR2(32 CHAR) --e.g car payyment, mortgage,
,period_type VARCHAR2(32 CHAR) --e.g year(s), day(s), month(s), week(s)
,starting_principal NUMBER
,interest_accrued NUMBER
,new_loan_amount NUMBER
,CONSTRAINT interest_on_loan_pk PRIMARY KEY (loan_id, period)
);
Table INTEREST_ON_LOAN created.Now we can then insert the results of our computation into the table defined above.
--
-- compute compound interest for a credit card balance of 1000, interest rate of 22%, over a period of 12 months
--
INSERT INTO interest_on_loan
SELECT 1, period, 'Credit card payment', 'Month', initial_principal, interest_rate, new_principal
FROM compute_compound_interest( 1000, 0.22, 12);
12 rows inserted.We can query our table for these results
SELECT * FROM interest_on_loan ;
LOAN_ID PERIOD LOAN_TYPE PERIOD_TYPE STARTING_PRINCIPAL INTEREST_ACCRUED NEW_LOAN_AMOUNT
------- ------ --------------------- ----------- ------------------ ---------------- ---------------
1 1 Credit card payment Month 1000 220 1220
1 2 Credit card payment Month 1220 268.4 1488.4
1 3 Credit card payment Month 1488.4 327.45 1815.85
1 4 Credit card payment Month 1815.85 399.49 2215.33
1 5 Credit card payment Month 2215.33 487.37 2702.71
1 6 Credit card payment Month 2702.71 594.6 3297.3
1 7 Credit card payment Month 3297.3 725.41 4022.71
1 8 Credit card payment Month 4022.71 885 4907.71
1 9 Credit card payment Month 4907.71 1079.7 5987.4
1 10 Credit card payment Month 5987.4 1317.23 7304.63
1 11 Credit card payment Month 7304.63 1607.02 8911.65
1 12 Credit card payment Month 8911.65 1960.56 10872.21
Elapsed: 00:00:00.038
12 rows selected. Problem Solved !
All fine and good, we can perform our computation and analysis, store the results in a table and go back to review it at any time we want. We can use the function to do other type of analysis such as :
- How much your money could make you in the stock Market over a specified amount of years assuming a given growth rate.
- How much your Adjustable-Rate Mortgage (ARM) with rising rates could cost you over a specified amount of years if the interest rate goes up.
and much more.
You can put this function into an Oracle Apex application and your business users would be all good to go. All is well that ends well!
However, with a simple SQL statement we can perform the same computation in just a few lines of code.
A SQL solution
Using SQL we can do the exact same computation we used our table function to perform using the code below.
SELECT
LEVEL AS period,
ROUND(&&initial_principal * POWER(1 + &&periodic_rate, LEVEL - 1), 2) AS initial_principal,
ROUND((&&initial_principal * POWER(1 + &&periodic_rate, LEVEL - 1)) * &&periodic_rate, 2) AS interest_rate,
ROUND(&&initial_principal * POWER(1 + &&periodic_rate, LEVEL), 2) AS new_principal
FROM dual
CONNECT BY LEVEL <= &periods;At runtime, the code prompts for input for the various parameters we need for the computation. Those parameters could also be defined within the session if using SQL*Plus or similar IDEs.
For example to solve the business problem defined earlier in this post, we can use the code below:
--define session variables
define initial_principal = 1000
define periodic_rate = 0.22
define periods = 12
-- run query
SELECT
LEVEL AS period,
ROUND(&&initial_principal * POWER(1 + &&periodic_rate, LEVEL - 1), 2) AS initial_principal,
ROUND((&&initial_principal * POWER(1 + &&periodic_rate, LEVEL - 1)) * &&periodic_rate, 2) AS interest_rate,
ROUND(&&initial_principal * POWER(1 + &&periodic_rate, LEVEL), 2) AS new_principal
FROM dual
CONNECT BY LEVEL <= &periods;
PERIOD INITIAL_PRINCIPAL INTEREST_RATE NEW_PRINCIPAL
---------- ----------------- ------------- -------------
1 1000 220 1220
2 1220 268.4 1488.4
3 1488.4 327.45 1815.85
4 1815.85 399.49 2215.33
5 2215.33 487.37 2702.71
6 2702.71 594.6 3297.3
7 3297.3 725.41 4022.71
8 4022.71 885 4907.71
9 4907.71 1079.7 5987.4
10 5987.4 1317.23 7304.63
11 7304.63 1607.02 8911.65
12 8911.65 1960.56 10872.21
12 rows selected.
-- Clean up session variables
UNDEFINE initial_principal;
UNDEFINE periodic_rate;
UNDEFINE periods; Using the power function, we are able to achieve the exact same results in pure SQL. Also, without using the define command, most IDEs and applications will prompt the user for the parameter values allowing for an interactive user experience.
Conclusion
I have always found SQL to be extremely powerful and useful. I’m amazed at some of the things I have seen people do with it like solving sudoku or drawing the Monalisa. As the example in this post showed, SQL is capable of solving some of the problems that PL/SQL is often called upon to solve. Also, following the advice of folk a lot more smarter than I am; If you can do it in SQL then do it that way and if you can’t for for some reason then use PL/SQL. This makes sense, as using only SQL avoids context switching between the SQL and PL/SQL engines and thus reduces the performance overhead.
Additionally, you only have to learn SQL once but folks who learned SQL back in the 80s have a skill that is still relevant and useful today. Furthermore, the same SQL code that ran many years ago can probably still run today with little to no changes. Those are pretty good returns for a 30 year old Language and a couple of weeks of personal development. Lastly, the resources to learn are also overwhelmingly free and my favorite is the devgym. So what are you waiting for, go learn SQL yesterday!
I hope this was informative and thanks for reading.