PL/SQL encoding rules to be followed by Oracle databases

Introduction: Oracle DatabaseThere is no constraint. Oracle databases must comply with certain rules. As we all know, few of us work in isolation; most PL/SQL development work is carried out 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.

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, defined as "name comma (,) surname". 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.

3. Make the execution part short

Face the reality: it is always different from our judgment and the latest series of New Year's resolutions. We must stop writing spaghetti-style code: huge and lengthy, which people can't really understand, not to mention maintenance or upgrade. How can we avoid "Pasta?

In fact, the answer is simple: Never allow execution of more than 50 or 60 rows. This size enables you to view the entire logical structure of the code block on one page or screen, which also means you can truly understand the program's intent, and can be understood simply by intuition.

You may agree with the above idea very much, but at the same time laugh at my suggestion: the program code will never exceed 50 lines. Yes, you should laugh at it, because of course it is impossible. There is no doubt that you need more than 50 lines of executable code; the question is where you put the code and how you organize it.

If you take the following steps, you can indeed cope with various complex requirements and limit the code to 50 lines:

Place all business rules and discrete logical blocks in their own programs (usually functions), so that code can be reused with caution whenever possible.

Try to use the local modules, processes, and functions defined in the Declaration part of the program.

Suppose I am compiling a call center application. I need to write a program that meets the following requirements:

"For each employee in a specific department, the workload (number of calls assigned to the employee) is compared with the average workload of the employee in the department. If the workload of an employee is lower than the average workload, the next pending call is assigned to the employee and an agreement is arranged based on this situation. "

I learned from my previous work that my friend cladedia has compiled an analysis package that will return workload information. However, the assignment of pending calls and scheduling conventions are all new work, and the rest of the requirement document describes this in detail.

At first I wanted to read all the content on the 15 pages, but I didn't do that. I used a technology called "progressive refinement" or "Top-Down Design", and first compiled the code in Listing 4 to implement this program.

The following describes the most critical lines of code in Listing 4, starting from the end of the program (compact execution), and proceeding upwards. This seems to be contrary to intuition, but it is indeed the best way to write a program using the progressive refinement method.

22nd ~ 30 rows. Cursor FOR loop is used to iterate all employees in a specified department. In 24th ~ In line 25, the program in the analysis package is used to determine whether the current employee has insufficient workload. In 27th ~ Line 28 calls three programs: assign_next_open_case, schedule_case, and next_appointment. I still don't know how to implement these programs, but I know that they express the work that needs to be done in advance through their names and parameter tables.

10th ~ 19 rows. 27th ~ Three programs in line 28 create "stub", that is, placeholder programs. Note that they are local modules that are defined in assign_workload and cannot be called from any other program.

5th ~ 8 rows. Defines a cursor to obtain all employees of a specified department. Now you can try to compile this code.

It seems to be a small victory for such a small program to successfully complete the compilation. Complete the correct compilation, then perform a simple test, then add a little code, and then perform the correct compilation, and so on. Such a small victory creates a well-constructed program and will be very satisfied.

I can also verify that the analysis program is valid and find the appropriate employee for the task to be assigned. After all this work is done, I will pick out one of the three programs, such as assign_next_open_case, for the next or next level of fine design. I want to read the document of this task and write a brief Execution Section in assign_next_open_case to reflect the overview of this task.

Soon, my local process has its own local process and function, but in every step of the process, my code is short, readable, easy to test, and can be adjusted as needed.

4. Find a good partner

Computers do not program, talent will.

This article summarizes so many rules for everyone. Only by following these rules described above, Oracle database is reasonable and I hope it will be helpful to everyone.

1. Be proactive in Oracle Database Maintenance
2. How to delete an Oracle database
3. Differences between Oracle Data Pump export and traditional export
4. Summary of methods to improve the performance of Oracle Database Systems