Summary of Oracle Database PL/SQL Encoding Rules (1)

I have been writing PL/SQL code since 1990. This means that I have written tens of thousands of lines of software code, but I am sure that most of the code is very poor and difficult to maintain.

Fortunately, it is not too late for me to find and follow a new method to write better code. That is, last year, my code quality has been significantly improved. These improvements are mainly due to the creation of simple rules and the compliance with them like discipline.

This article provides four suggestions for PL/SQL beginners and experienced developers. Your code quality will be improved by following one of them. You may be surprised to find that you are a very good programmer, far beyond your imagination.

All work is done by yourself

Few of us work in isolation; most PL/SQL development work is done in a relatively large organization. But we basically work with our own device in our own compartment. Almost no PL/SQL development team performs formal code review or system testing.

I cannot use this article to change the basic status of your development team. Therefore, I carefully selected the following suggestions. Any implementation does not require the consent of the management personnel. No matter whether your group is large or small, you don't have to let everyone agree with these encoding rules. You only need to follow the suggestions below to change your encoding method:

1. Strictly follow naming conventions, as if they are your pillars of life.

2. Skip the habit of writing SQL: The fewer SQL statements, the better.

3. Make the execution part short: Say goodbye to "pasta-style code ".

4. Find a partner: I strongly agree to find someone to supervise your work.

1. Follow naming conventions

If you create and strictly follow a naming convention, especially for application components, you can save a lot of time.

Of course, the idea of following naming conventions is nothing new, and you may be bored. Therefore, I did not propose any ambitious naming scheme, but gave some very specific and clear conventions, and then proved how useful these conventions would be.

I have been designing and building a new tool for PL/SQL developers for the past few months. It is called Swyg (which can be found in www.swyg.com) and can help programmers to generate, test, and reuse code. It has several unique components. I have specified an abbreviated name consisting of two letters for each component, as shown below:

◆ Basic parts of SF-Swyg

◆ SM-Swyg metadata

◆ SG-Swyg Generation Program

◆ SL-Swyg code library

◆ ST-Swyg unit test

Therefore, I followed the naming conventions in Table 1 and used these abbreviations. What are the benefits of following these conventions? Generally, If I require consistent naming rules, I can write code more smoothly and efficiently.

Specifically, these conventions are predictable, meaning that SQL programs I write can generate useful scripts. For example, you can use the conventions in table 1 to generate installation scripts for all basic packages in Swyg. The SQL * Plus script that executes these jobs is shown in Listing 1. This type of script is very useful because it means that I do not have to manually maintain the installation script. When I add another table to the Swyg scheme and generate a group of related packages, I only need to run my script and the updated installation script will jump out.

2. Skip the SQL writing hobby

The fewer SQL statements, the better. This seems to be different from our intuition. For PL/SQL developers, this is a strange suggestion, because one of the main advantages of PL/SQL is that you can easily write SQL statements in code. However, this simplicity is also a critical weakness of the language.

Pure SQL statements can be directly placed in PL/SQL code, without the middle layer such as JDBC or ODBC. Therefore, PL/SQL developers usually embed SQL statements into their application code whenever and wherever they need SQL statements. So what's the problem?

Using SQL statements everywhere in PL/SQL code will inevitably lead to the following consequences:

Although the actual performance is different, the same logic statement will still be repeated, resulting in excessive syntax analysis, and it is difficult to optimize the performance of the application.

Expose business rules and solutions. This directly contains the logic for executing business rules in SQL statements. These rules are always changing, so the maintenance cost of applications will increase dramatically.

Of course, almost every PL/SQL application you want to write is based on basic tables and views. You need to execute the SQL statement. The question is not whether to execute it, but when and how to execute it.

If You encapsulate data structures or hide them in a PL/SQL code layer (usually a code package), your applications will become more robust, in addition, you will find creation and maintenance easier.

Let's look at a simple example. Suppose I need to write a program to handle the work of an employee. The first thing is to get the employee's full name and define it as "name comma (,)". Then I can perform a detailed analysis. Listing 2 provides an example of this type of code that is likely to be written in this case.

Everything seems to be so simple and straightforward. What are the possible errors with these codes? Actually, it's really bad. The most important thing is that I have exposed a business rule: the structure of the full name. It may take me several hours to test this code and the applications it is based on. But when it was just put into use, I realized that the customer would keep calling and telling me that their full name should be "name, space, and surname ".

What should I do now? Search for all single commas (,) enclosed in quotation marks?

A realistic solution is to use a package that hides all details and provides only one set of predefined, pre-tested, pre-optimized, and can complete all tasks. Listing 3 is the process_employee process rewritten based on the encapsulated code. The hr_employee_tp package provides the type of local variables used to define the name to be saved; hr_employee_rp contains a function that returns the full name based on a business rule.

It is easy to add PL/SQL statements to the SQL code. Similarly, it is not difficult to encapsulate these statements. On the other hand, writing code to execute encapsulation tasks is challenging, or even unrealistic. Generating these packages may make more sense.

A few years ago, I helped build such a program. This program segment is PL/Generator, which is now owned by Quest Software. The PL/SQL development community is free of charge. You can download from my website www.steven feuerstein.com/puter/gencentral.htm. You must know that the encapsulation architecture is different from the conventions I have outlined earlier. PL/Generator creates a separate package that contains all the content of the table type, query, and change logic.

When you no longer write too many SQL statements, but call programs that execute SQL statements, whether you generate or write your own custom packages, your applications will benefit a lot.