PL/SQL worst practices

1. Super-long PL/SQL code

Impact: maintainability and Performance

Symptoms:

In complex enterprise applications, there are hundreds of thousands of rows of stored procedures or tens of thousands of rows of packages.

Why is it the worst:

Too long PL/SQL code is not conducive to reading, and third-party tools may also encounter issues such as code line disorder during debugging. The maximum number of PL/SQL storage objects (stored procedures, packages, functions, triggers, and so on) is about 6000000 rows. However, when the package size exceeds 5000 rows, debugging may occur.

Solution:

PL/SQL code is loaded into the shared pool before execution. The shared pool is measured in bytes, 64 K in UNIX, and 32 K in desktop, you can view the object size by querying the parsed_size field of the data dictionary user_object_size. For large packages, the package splitting policy should be adopted to extract reusable parts to reduce repeated code. For large stored procedures, the stored procedures should be organized into the packages for ease of management; for large anonymous blocks, the anonymous blocks should be redefined as sub-processes and stored in the database.

2. Global variables that are out of control

Impact: maintainability

Symptom: global variables are used in the package to operate on global variables in multiple locations.

Create or replace package body pkg_test is

GN _ global variable number (12, 2 );

Procedure process a is

Begin

GN _ global variable: = 1;

End;

Procedure process B is

Begin

GN _ global variable: = 2; -- another operation is performed on the global variable.

End;

Why is it the worst:

Global variables can be accessed throughout the package range, so it is difficult to track and debug global variables. If the variable is defined in the package, it can be accessed by other packages, which is more dangerous.

Solution:

Reduce or disable the use of global variables. For variables that require interaction between processes, they are implemented through parameter transmission. If global variables must be used, get/set function encapsulation should be performed on global variables to standardize access to global variables.

3. Embedded complex SQL statements in PL/SQL

Impact: maintainability

Symptoms:

Embed SQL statements in PL/SQL code, such:

...

Procedure process a is

Begin

Update T_A set col1 = 10;

End;

Procedure process B is

Begin

Delete from T_A where col1 = 10;

End;

...

Why is it the worst:

? Embedding SQL statements in PL/SQL code makes it difficult to read and understand the meaning of the Code.

? Accessing tables in multiple locations is not conducive to SQL Optimization

Solution:

? Encapsulate scattered SQL statements. For example, the delete statement in the previous example can be encapsulated as the type parameter of T_A when "PRC _ Delete T_A, the delete operation on T_A is delegated to this process. When adding or deleting fields in Table T_A, the main changes are concentrated in these processes, with less impact on other logics.

? SQL optimization is concentrated in the encapsulation process.

4. Exception Handling

Impact: maintainability and robustness

Symptoms: Let's look at the following code:

Procedure process a (error code out varchar2, error message out varchar2) is

Begin

...

Update T_A set col1 = 10;

Select... from T_A where ...;

Delete from T_A where col1 = 20;

...

Exception

When others then

...

End;

Why is it the worst:

There is only one when others exception segment throughout the process. Exceptions in the three statements in the example can only be caught by the outermost layer, and the types and locations of exceptions cannot be distinguished.

Solution:

? Do not use when others to catch all exceptions. For example, no_data_found exceptions should not be caught, and special exceptions should be used to catch specific exceptions.

? Declare your own exception handling mechanism, handle business-related exceptions, and process business exceptions separately from system runtime exceptions.

? Custom complete exception information, including the scenario in which an exception occurs. 5. Fixed Variable Length and variable type

Impact: maintainability

Symptom: When declaring a variable based on the field type, especially the varchar2 type, use a fixed length statement directly.

Why is it the worst:

? The size of the hard-coded variable may be inconsistent with the actual size in the database.

? If the field type and size change, you also need to adjust the variable in PL/SQL.

Solution:

Use % type to declare variables related to the field type.

6. No unit test is performed.

Impact: Robustness

Symptom: PL/SQL code contains a large number of business logic. After these logic is compiled, no suitable unit test cases are provided for verification.

Why is it the worst: the harm of not doing unit tests is no longer nonsense here.

Solution:

PL/SQL does not provide easy-to-use unit testing tools such as JUnit. Some open-source tools are available now. Use utplsql (http://utplsql.sourceforge.net/) tool for unit testing, or dbunit for secondary development to meet the needs of different applications.

7. Use the code value instead of the code name

Impact: maintainability

Symptoms: Let's look at the following code:

Method 1:

V_sex: = '1'; -- male

Method 2:

Const_male constant varchar2 (1): = '1'; -- defines the constant male.

V_sex: = const_male;

Why is it the worst:

? From the example, we can see that the same gender is used. method 1 uses the code value directly, and method 2 uses constants. It seems that method 2 is more troublesome than method 1, however, method 2 is more intuitive than method 1, and the code is more readable. Readers of the Code do not need to pay attention to the meaning of "1.

? When the male gender definition of other projects is changed to "2", the program using method 1 encoding needs to carefully find each piece of code, which is prone to errors, the program using method 2 encoding can only modify the definition of a constant.

Solution:

Put the constant definition in a common code package for other programs to share. All comparisons and references involving code values must use the constant name, rather than writing code values directly. The relationships between some complex code values can be further encapsulated to provide calls in the form of functions.

8. Configuration Management is not performed on PL/SQL objects.

Impact: maintainability

Symptoms: PL/SQL objects (package, package body, trigger, procedure, type, type body, function, etc.) are not maintained and updated using the configuration management tool.

Why is it the worst:

Because of the differences in the internal structure of Oracle, object management is difficult, especially in parallel development.

? The division of object responsibilities is unclear, causing multiple users to modify an object at the same time. During compilation, if the later developer does not obtain the latest code, the code modified by the previous developer will be overwritten.

? Oracle Objects cannot be traced back to the past, and the database can only store the latest

Solution:

? Standardize the development process, and use the PL/SQL code on the configuration management tool as the latest.

? Use third-party plug-ins to reduce synchronization workload, such as the VCs version control plug-in under PL/SQL developer.

9. If... Else... Bad taste

Impact: maintainability

Symptom: use if... Else

Why is it the worst:

A large number of IF/else operations make the code logic messy and difficult to modify. This type of code, whether it's PL/SQL or other programming languages, is already "bad smell.

Solution:

? Uses the inheritance feature of Oracle Database, implements Object Inheritance through type, and encapsulates differences using policy modes to provide unified calling interfaces for external users.

? Frequently used if/else code is restructured into a separate process or function for reuse by other code.

 

10. control transactions in non-autonomous transactions

Impact: Data Consistency

Symptoms:

Control transactions in PL/SQL non-autonomous transaction code, for example:

Procedure process a (error code out varchar2, error message out varchar2) is

Begin

...

Savepoint;

Update T_A set col1 = 10;

Commit;

Delete from T_A where col1 = 20;

Rollback to;
...

Exception

When others then

...

End;

Why is it the worst:

This kind of behavior is the most harmful in my opinion. Transaction Control codes that are everywhere can cause data inconsistency and cause problems that are difficult to track and debug.

Solution:

? The caller determines when to commit or roll back the transaction.

? For processes that require special transaction management, such as logging, autonomous transactions are used.

11. Do not use variable binding

Impact: Performance

Symptom: directly use the value instead of the bound variable for query. Especially in the spelling of SQL programs, this situation is more prominent.

Why is it the worst:

This is a common problem. When the code is filled with a large number of fixed code values, the database engine needs to re-Parse each time and cannot use the existing execution plan.

Solution: for such frequently executed statements, bind variables instead of actual parameter values.

12. Use rownum = 1 with caution.

Impact: maintainability and Data Consistency

Symptom: when reading data, you sometimes only need to take a row. rownum = 1 is used in the where condition.

Why is it the worst:

This practice is rated worst because I have encountered such problems in my actual work and it is difficult to track and debug them. The processing order of rownum itself is before order by, so the results generated when rownum = 1 are likely to be random.

Solution: understand the meaning of the data to be queried and use other conditions to limit the result set.

13. Flexible Dynamic SQL

Impact: maintainability and Performance

Symptom: execute immediate 'select A from tab1' into v_a;

Why is it the worst:

Dynamic SQL statements lose the checking capability during the compilation period and delay the possibility of problems until the runtime. Dynamic SQL is not conducive to optimization, because only full SQL statements can be obtained at runtime.

Solution: Avoid using dynamic SQL as much as possible, and extract the variable business logic to the middle layer for implementation.

14. Access rowid

Impact: Data Consistency

Symptom: Use rowid as the where condition for data update and deletion.

Why is it the worst:

Rowid belongs to the underlying storage structure of Oracle, and changes with data migration, import, and export. business logic should not rely on the underlying storage structure.

Solution: Use the primary key for data operations.

This article from: http://oracle.chinaitlab.com/PLSQL/754539_2.html