Reusability: Score = 2

Because the Example 1-4 code isn’t structured as a procedure or function, it’s not easily reusable. One of the merits of writing reusable code is that you must aim for more generality in terms of structure, parameter names, and so on. So rather than just solving the problem at hand, your aim should always be to write the code so that other developers can use it for their work. An unexpected benefit is that your code then gets looked at by more developers, and this can result in fixes or improvements to your original work. You’ll see this later on.

Repeatable Testing: Score = 2

Again, a low score has been assigned because we don’t have any test infrastructure. Without this, we can’t be absolutely sure that the code is rock-solid. This code can only really be tested by manual means, i.e., looking at the original data and comparing it with the output data.

Avoiding Common Antipatterns: Score = 0

The code in Example 1-4 contains at least one antipattern related to the way it handles data types. Also, the lack of error handling is an egregious antipattern. I’ll come back to this later and show how to resolve these types of issues.

Schema Evolution: Score = 0

The next piece of Example 1-4 that we’ll analyze is a source of disappointment. Why do I get 0? Well, what happens to the following lines if the underlying table data definitions change?

SELECT
ename
,
empno
,
sal
FROM
emp
ORDER
BY
sal
DESC
;
— start with highest paid employee
my_ename
VARCHAR2
(
10
);
my_empno
NUMBER
(
4
);
my_sal
NUMBER
(
7
,
2
);

What happens if a DBA changes the definition of my_ename VARCHAR2(10) to my_ename VARCHAR2(100)? This happens quite regularly in organizations as business requirements change. It is an example of schema evolution and it has a bad effect on the code in Example 1-4, which will no longer work correctly.

Summary

This chapter’s aims were as follows:

• Broadly understand what “resilient software” means.
• Understand the requirements for resilience: what versus how.
• Understand why PL/SQL is a good idea.
• Appreciate why using SQL for business logic is generally a bad idea.
• Know some of the disadvantages of PL/SQL.
• Gain a basic understanding of PL/SQL.
• Read existing or legacy PL/SQL code.
• Understand the need for a PL/SQL learning and development environment.
• Understand a basic scale of resilience.

Learning PL/SQL is much easier than many people might believe. The language is relatively simple and allows you to be flexible about using uppercase or lowercase characters. Spacing is similarly flexible and you can choose an indentation style that suits your needs or your house style.

I think one of the merits of PL/SQL is that it is a readable language, though some readers might not agree with me on this. One element of the language that really merits study is that of CURSORs. CURSORs demonstrate one of the major differences between PL/SQL and SQL. While SQL does use CURSORs internally, these objects are not visible to users. The use of a PL/SQL CURSOR, on the other hand, allows you to collect data in a type-safe way, and you’ll see more on this in the coming chapters.

Introducing a scale of resilience allows us to numerically analyze a block of PL/SQL. This is useful because we are not so much focused on what the code does but on other aspects of it, such as modularity, simplicity, antipatterns, and so on. The scale gives us metrics that can then inform further work on the underlying code, such as enhancing and refactoring it.

By addressing these aspects and refactoring the PL/SQL examples, you will gain many benefits, as will be seen in the coming chapters.