Coding FAQ (PL/SQL and Java)

Please contact me if you have any questions :)

Coding FAQ (PL/SQL and Java)
1. the PL/SQL data type is incorrect. For example, aac001 is of the varchar2 type, but it is not included in ''. Although the program can be queried, it poses a hidden risk for future performance problems.
Select aac002 from ac01 where aac001 = 123456;
Select aac002 from ac01 where aac001 = '000000 ';
2. The code is too long.
No matter in Java or PL/SQL, too long code is difficult to understand and maintain. In Java, the proper code length is generally between 50-lines.
If PL/SQL does not have such norms, I personally understand that it is best not to exceed-rows.
3. ultra-long SQL statements
Super-long SQL statements are not easy to understand. A large amount of business logic is included in the SQL where. Such SQL statements are neither convenient for tuning nor maintenance. It is recommended that there be no more than three objects in from.
It is not a pride to be able to write super complex SQL statements.
4. SQL statements are case-insensitive. keywords are uppercase and lowercase, which is not conducive to Oracle parsing.
You can use the PL/SQL developer tool to format PL/SQL. However, in Java, only the attention of the coders is required.
5. Use the code value instead of the code name.
Select aac002 from ac01 where aac008 = '1 ';
Select aac002 from ac01 where aac008 = pkg_a_macro.def_aac008_emp;
We can see that if the aac008 code value changes,
The first method is to search all the SQL statements, which may cause problems;
In the second method, you only need to modify the macro definition in pkg_a_macro.
6. Apply a large number of global variables
Global variables will destroy the program structure, and maintenance of global variable values will occur at any point in the program code. Many errors are not easy to debug, and the program cannot be componentized and difficult to replace.
7. cursor usage in PL/SQL is not closed
Java-side DB access generally uses mature ORM tools, which has fewer problems. In PL/SQL, you must disable cursor in time and pay attention to the cursor status when exceptions occur.
8. error message
For convenience, many people directly copy the error information to different places. This saves time for coding, but may cause potential problems for future debugging. Different information should be prompted for different errors, and try to pass
The error information is restored to the current environment, which is very convenient for testing. The direct benefit is that the work efficiency is greatly improved.
9. resource processing during exception handling. When an exception occurs, release the corresponding resources, such as whether the database connection and file read/write have been opened, and transactions.
10. do not litter database operations in the program code. For example, modifications to ac01 may occur in many places. An update statement appears in the-segment code of a business, it also appears in section B code,
If the ac01 structure changes one day, for example, to write a common service serial number, you can only modify the update statement one by one, and be more careful.
The correct method should be to encapsulate DB access to a method as much as possible. This is not just for PL/SQL. Even if you use Orm, you should also pay attention to minimize distributed access to DB.
11. The old saying goes with Goto.
In Java, there are very few cases of using Goto, while in PL/SQL, there are still some places where goto is used. In this case, we should consider refactoring the code process.
12. Cyclic Variables
Because of the convenience of the coding personnel diagram, the circular code is copied by copying the circular code, which is likely to duplicate the circular variable.
For (INT I = 0; I <10; I ++ ){
For (INT I = 2; I <10; I ++ ){

}
}
Even if you are lazy, you may lose yourself.
13. rownum = 1
Rownum = 1 is a very subtle thing. It can turn your error into a non-typed one. If this sentence is added to the Code, no error will be reported. ^ _ ^ it's a pleasure and no error will be reported. Don't worry. Check whether the calculation is correct.
It is better to hide errors as early as possible, especially in key businesses, such as Account Division. If errors have already been generated, make coffee and work overtime.
14. Add execution judgments for each SQL statement.
When you execute a large number of SQL statements in a process, you can only capture them in one place At the end. When an error occurs, you can adjust it slowly.
15. Are your comments sufficient?
If there are not enough comments, you will be scolded by the person who maintains your program in the future. It is shameful not to write comments...
16. Avoid writing SQL statements in Java code as much as possible
Why not use an Orm? It also avoids field changes.
17. Pay attention to the Java objects not initialized
Check whether they will report a null pointer error.
18. The interface must be implemented in Java
For example, applogic
19. Copy or copy
Stupid errors are often hidden in ongoing copies. Since there are refactoring, what should we do with so many copies?


Write so much first. Remember one thing in a word. How do you treat programs? What do programs treat you!