High performance dynamic SQL program development based on Oracle

Abstract: this paper summarizes the program development of dynamic SQL, and gives some development skills based on the author's practical development experience.

1. Static SQLSQL and dynamic SQL

The PL/SQL block can be divided into two categories: the first is the pre-compilation (early binding), that is, the SQL statement has been determined at the time of program compilation, and most compilation situations are of this type; The other one is postprocessing (late binding), which means that the SQL statement can only be established at the running stage. For example, when the query condition is entered by the user, the SQL engine of Oracle cannot determine the program statement at the compile time, and it can only be submitted to the SQL engine for processing after the user enters the query condition of late binding. Typically, static SQL is compiled the first way, while dynamic SQL is compiled the last way.
This paper mainly discusses the development of dynamic SQL, and finally gives some practical development techniques.

2. Dynamic SQL program development

If you understand the principles of dynamic SQL compilation, you will grasp its basic development ideas. Since dynamic SQL is an "indeterminate" SQL, its execution has its corresponding characteristics. Oracle provides the Execute immediate statement to execute dynamic SQL, with the syntax as follows:
Excute immediate dynamic SQL statement using binding parameter list returning into output parameter list;

This statement is illustrated as follows:

1) dynamic SQL refers to DDL and uncertain DML (that is, DML with parameters)
2) the binding parameter list is the input parameter list, that is, it is of type in. At the runtime, it is bound to the parameters in the dynamic SQL statement (actually placeholders, which can be understood as formal parameters in the function).
3) the output parameter list is the parameter list returned after the execution of dynamic SQL statement.
4) since dynamic SQL is determined at the running time, compared with static, it will lose more system performance in exchange for its flexibility.
To better illustrate the development process, here is an example:

Set the emp table of the database, and its data are as follows:

Requirements:

1. Create the table and enter the corresponding data.
2. Name and salary information can be found according to the specific ID.
3. Query employee information based on a salary larger than a specific salary.
According to the above requirements, you can create three processes (all using dynamic SQL) to achieve:

Procedure 1:

The process of 2:

The process of 3:

Note: the dynamic SQL statement in procedure 2 USES the placeholder ":1", which is the formal parameter of the function, prefixed with ":", and then replaces p_id with :1 at run time using the using statement, where p_id is the argument in the function. In addition, the cursor opened in process 3 is a dynamic cursor, which also belongs to the category of dynamic SQL. Its entire compilation and development process is very similar to the process performed by execute immediate, which is not described here.

3. Dynamic SQL statement development tips

Analysis to the front, the implementation of dynamic SQL is loss performance of the system for its flexibility, so it 1 degree of optimization is necessary, the author gives according to the practical development experience 1 some development skills, it should be pointed out that, a lot of experience here is not only limited to the dynamic SQL, some can also be applied to static SQL, mark will be given in the description.

Tip 1: try to use a similar SQL statement, so that Oracle itself can directly cache the SQL statement through the Shared pool in SGA, and then directly call the parsed statement in the cache the next time the similar statement is executed, so as to improve the execution efficiency.
Tip 2: when it comes to assembly units, use batch link-up whenever possible. For example, the salary of employees with id of 100 and 101 needs to be increased by 10%. In general, it should be in the following form:

For the above processing, when the data volume is large, it will be slow. If batch linking is adopted, the whole set will be firstly passed into the SQL engine for processing once, which is much more efficient than single processing. The code for batch linking is as follows:

Here, forall is used for batch linking. Here, the situation of batch linking is summarized as follows:
1) if the statements insert, delete, update and so on are executed in a loop and refer to collection elements, then it can be moved to an forall statement.
2) if the select into, fetch into or returning into clauses refer to a collection, they should be merged using the bulk collect clause.
3) if possible, use a host array to pass parameters between the program and the database server.
Tip 3: use the NOCOPY compiler to improve PL/SQL performance. By default, parameters of type out and type in are passed by value. However, for the parameter passing of large object types or collection types, the expected loss will be very large. In order to reduce the loss, the method of reference passing can be adopted, that is, the NOCOPY keyword can be referenced when the parameter declaration is made to illustrate such an effect. For example, create a process:

Where square is a large object type. So instead of passing the whole object, I'm just passing one address. Obviously this process also improves efficiency.

4. summary

This paper discusses the compilation principle, development process and development skills of dynamic SQL. After the introduction of this paper, it is believed that readers have a general understanding of dynamic SQL program development, which will lay a good foundation for further work in the future.
The previous code section was successfully debugged in the following environment:
Server: UNIX+ ORACLE9.2
Client: WINDOWS2000 PRO+TOAD