Pl/sql Worst practices

1. Super Long Pl/sql Code

Impact: maintainability, performance

Symptoms:

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

Why is the worst:

Too long pl/sql code is not good for reading, and third-party tools can cause code-line clutter when debugging. Pl/sql storage objects (stored procedures, packages, functions, triggers, and so on) have an upper limit of about 6000000 rows, but in practice, debugging problems occur when the package size exceeds 5000 rows.

Way to solve the problem:

The Pl/sql code is loaded into the shared pool before execution, shared pool in bytes, 64K under Unix, 32K in the desktop environment, and can be user_object_size by querying the data dictionary Parsed_ Size field to see object sizes. For larger packages, a split strategy should be used to extract the reuse section and reduce repetitive code; For larger stored procedures, the stored procedures should be organized into packages that are easy to manage, and for larger anonymous blocks, the anonymous blocks should be redefined to be stored in the database.

2. Global variables that are out of control

Impact: Maintainability

Symptom (s): Global variables are used in the package, and global variables are manipulated at multiple locations.

create OR REPLACE PACKAGE BODY PKG_TEST IS
GN_全局变量 NUMBER(12, 2);
PROCEDURE 过程A IS
BEGIN
GN_全局变量:=1;
END;
PROCEDURE 过程B IS
BEGIN
GN_全局变量:=2; -- 这里对全局变量进行了另外的操作
END;

Why is the worst:

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

Way to solve the problem:

Reduce or eliminate the use of global variables, for the variables to interact between processes, through the parameter pass to achieve. If global variables must be used, the global variables should be encapsulated in Get/set functions, and the access to global variables should be normalized.