PL / SQL coding rules should follow
Author: Steven Feuerstein
Four simple and easy guidelines with the number of PL / SQL code numbers and quality
I start writing PL / SQL code since 1990. This means that I have written a tens of thousands of software code, but I am sure that most of the code is very poor, and it is difficult to maintain.
Fortunately, I found that I found and follow the new way to write better code is still not too late. It was last year, my code quality has been significantly improved; these improvements are mainly due to the development of some simple rules, and complied with discipline.
This article puts four suggestions for PL / SQL novices and experienced developers; abide by any one, your code quality will increase. These four suggestions are adopted, you may be surprised to discover: You are a very good programmer, far from your imagination.
All work is complete
We rarely have someone working; most PL / SQL development work is carried out in a relatively large mechanism. But we basically work alone in your own small compartment. There is almost no PL / SQL development team for regular code review or system testing.
I can't change the basic state of your development team through this article. Therefore, I carefully select the following suggestions. Implementing any point does not need to be consent. Regardless of your group is small, you don't have to let everyone agree with these coding rules. You just need to change your own coding method as follows:
1. Strictly follow the naming conventions, as if they are your life pillars. 2. Qualifies the preparation of SQL hobby: The less SQL written is, the better. 3. Make the execution part short: bid farewell to "Italian top code". 4. Find a partner: I agree with someone to supervise your work.
1. Follow the naming agreement
If you build and strictly follow a set of naming conventions, especially for application components, you can save a lot of time.
Of course, there is no new idea that follows the name agreement, you may have been annoyed. So I don't propose a magnificent naming plan, but give some very specific and clear agreements, then prove how useful.
I have been designing a new tool for PL / SQL developers a few months ago. It is named SWYG (which can be found in www.swyg.com), can help programmers complete the code generation, test, and reuse. It has several unique components. I specified a abbreviation name consisting of two letters as follows:
SF-SWYG's basic part of SM-SWYG's metadata SG-SWYG generating program SL-SWYG's code base ST-SWYG unit test
So I follow the naming agreement in Table 1 and use these abbreviations. What are the benefits of following these conventions? Generally speaking, if I ask consistent naming rules, I can write code more and more efficiently.
It is clear that these conventions have predictable, meaning that I have written SQL program generate useful scripts. For example, by using the convention in Table 1, the installation script of all the basic packets in the SWYG can be generated. SQL * PLUS scripts that perform these work are shown in Listing 1. This type of script is very useful because it means that I don't have to maintain the installation script manually. When I add another table to the SWYG scheme, when I generate a group of related packages, I just run my script, and the updated installation script will jump out.
2. Qualify the preparation of SQL hobby
The less SQL written, the better, which seems to be unintended to our intuition. For PL / SQL developers, this is a strange suggestion, because one of the main advantages of PL / SQL is to write SQL statements in the code effortlessness. However, this simpleness is also a fatal weakness of this language.
A pure SQL statement can be placed directly in the PL / SQL code without the need for JDBC or an ODBC. Therefore, whenever, the PL / SQL developers will usually embed the SQL statement from their application code as long as they need SQL statements. So what is the problem? Using SQL statements everywhere in the PL / SQL code will inevitably lead to the following consequences:
Although the actual performance is different, the same logical statement still repeats, resulting in excessive grammar analysis, and it is difficult to optimize the performance of the application.
Exposure of business rules and programs. This includes logic that executes business rules directly in the SQL statement. These rules are always changing, so the maintenance cost of the application will increase dramatically.
Of course, every PL / SQL application you have to write is almost all based on the base table and view. You need to perform SQL statements. The problem is not whether it is executed, but when is executed, how to execute.
If you encapsulate the data structure or hide them in a PL / SQL code layer (usually a code package), then your application will be more robust, and you will also find more and more easier. .
Let's take a simple example. Assume that I need to write a program that handles a work of an employee. The first thing is to get the full name of the employee, defined as "Name Comma (,)"; then I can analyze. Listing 2 gives an example of such code I am likely to write in this case.
Everything seems to be so simple and straight; what do these codes may have? It is actually very bad. The most important thing is that I expose a business rule: a full name. I may take a few hours to test this code and its application. But when I just put into use, I only knew that the customer would constantly call me, in fact, their full name should be expressed as "name-free surname".
What should we do now? Search all single comma in quotation marks?
Realistic solutions are to use hidden details, only provide a set of predefined, predictive and pre-optimized and can complete all tasks. Listing 3 is a process_employee process that is rewritten based on the package code. The HR_EMPLOYEE_TP package provides a type for defining a local variable of the saved name; HR_EMPLOYEE_RP contains a function of returning a full name based on a business rule.
It is easy to display the PL / SQL statement to the SQL code. Similarly, it is important to discuss how these statements are important. On the other hand, the code to write to the encapsulation task is challenging; even unrealistic. Generate these packages may be more meaningful.
A few years ago, I helped building such a generating program. The block is PL / Generator, which is now owned by Quest Software, and the PL / SQL development community can be used free of charge. You can download from my website www.stevenfeuerstein.com/pute/gencentral.htm. It is to know that its package architecture is different from the agreement I summarized earlier. PL / Generator created a separate package that contains all the contents of the table, query and change logic.
When you no longer write too much SQL, but when you call the execution SQL program, your application will benefit your application whether you are generating or preparing your own custom packaging.
3. Small the execution part
In the face of reality: always with our judgment and the latest series of new year resolutions, we must stop writing the pasta code: huge and lengthy, people can't understand them, not to say maintenance or upgrade. How can I avoid "Italian noodles"?
In fact, the answer is simple: I will never allow the execution to more than 50 or 60 lines. This size allows you to view the entire logical structure of the code block on one sheet or one screen, which means you can realize the intent of the program, and you can understand it completely. You may agree with the above views, but at the same time, we laugh at me: The program code will never exceed 50 lines. Yes, you should laugh, because this is of course impossible. There is no doubt that you need more than 50 rows of executable code; the problem is where you put these code and how you organized.
If you take the following practice, you can deal with a variety of complex requirements and limit the code within 50 lines:
Place all business rules and discrete logic blocks in its own procedure (usually a function), thereby cauting to reuse the code at any time. Try to use partial modules, procedures, and functions defined in the program's declaration section.
Assume that I have prepared a call center application. I need to write a program that meets the following requirements:
"For each employee of a particular department, it is compared to the average workload of the employees of the staff. If a employee's workload is lower than the average workload, they will Processing call assignments are arranged based on this situation. "
I learned from the previous work: My friend Claudia has written an analysis package, which will return information on workload. However, assignment to handle calls and arrangements are all new work, and the rest of the demand document has been described in detail.
Initially I want to read this 15 pages, but I don't do that. I used a technology called "Step-seeking" or "by the top-down design" and presented the code in Listing 4 to implement the program.
The interpretation of the most critical code rows in Listing 4 is given below; the final start of the program (compact execution section) is performed. This seems to be discouraged, but this is indeed the best way to read the procedures that are gradually prepared to prepare.
22 to 30 lines. Use a cursor for loop to iterate all employees of the designated department. On lines 24-25, the program in the analysis package is used to determine whether the current employee is not working. In line 27-28, three programs are called: assign_next_open_case, schedule_case, and next_appinkment. I don't know how to implement these programs, but I know that they express their work that needs to be completed in advance through their name and parameter table.
Rows 10-19. Create "Stub" for the three programs in the 27th to 28th, which is the placeholder. Note that they are partial modules, defined in Assign_Workload, and cannot be called from any of the other programs.
Articles 5 ~ 8. Define a cursor to get all the employees of the designated department. You can now compile this code.
Successful completion of such a small program is like a small victory, and it is true. Complete correctly, then simply test, then add a little code, then correctly compile, so that small victories such as this class creates excellent constructive procedures, and it will be very satisfied.
I can also verify that the analysis program is effective and identifies the right employee to be assigned. After these works are all completed, I will pick up one from three programs, such as assign_next_open_case, perform the fine design of the next or next level. I want to read the documentation of this task and write a short execution section in Assign_Next_Open_Case, which reflects the overview of the task.
Soon, my partial process has its own partial processes and functions, but in every step of the process, my code is very short, readable, easy to test, can be adjusted as needed. 4. Find a good partner
Computers are not programmed, talent will.
How many times, you bent your waist, the hump is sitting in front of the computer, because you can't find the error in your code, is it very depressed? It was a few minutes a few minutes, then after a few hours. Finally, I am bored with yourself, I feel very failed, you reach your head and ask your friends to help you see.
There is usually one of the three situations:
When your friend stands up from her chair, everything is very clear. Your friend glances at the screen, immediately pointed out the problem. Your friend is not responsible for the part you do in the system, so you must show what your program is doing. When you gradually explain the logic, the problem that causes the wrong problem will suddenly expose to you.
The fact is that it is difficult to debug your code, because you have an investment to it, too focused.
Next step
Read more of the Oracle Database 10g Oracle.com/Database Feuerstein More Articles Otn.Orcle.com/oramag/webcolumns/2003
The best solution for this problem is to create such a culture by the development manager: Various ideas is sharing, do not understand is that it can be forgiven and will not be punished and regularly constructive code review. Unfortunately, these cultural changes are difficult to achieve.
At the same time, I suggest that you should take the lead in helping to change the culture of your team. Find another developer, it is better than you experience, and build a "partner" relationship: When there is a problem, he can act as your staff, of course, you can act as his staff. Cooperation beforehand: I don't know if all the answers have nothing wrong.
Then make a simple rule for yourself: don't think more than half an hour for a mistake. After 30 minutes, you call your partner, let human psychology serve you, not to your right.
Four steps to get a new way of work
This article provides you with four steps that can be taken to chase your programming experience without investing new tools or change the workflow of the entire team. These four steps can even follow, as long as you follow a step, you will benefit you.
Steven feuerstein (Steven@stevenfeuerstein.com) is an authoritative in PL / SQL language. FeUerstein wrote 9 books about PL / SQL (all published by O'Reilly & Associates published), including "Oracle PL / SQL Best Practices" and "Oracle PL / SQL programming (Oracle PL / SQL Programming ". He is still a senior technical consultant from Quest Software.
Object Name Structure Note Generation (DDL) File
