Oracle-based high-performance dynamic SQL Program Development

The Oracle tutorial is: Oracle-based high-performance dynamic SQL Program Development.

Abstract: This article summarizes the development of dynamic SQL programs and provides several development skills based on the actual development experience of the author.

Keywords: dynamic SQL, PL/SQL, high performance

1. Static SQL and dynamic SQL

Oracle compiler PL/SQL program blocks are divided into two types: one is the early compilation (early binding), that is, the SQL statement has been determined during program compilation, and most of the compilation situations belong to this type; the other is late binding, that is, an SQL statement can be created only in the running stage. For example, when the query condition is user input, therefore, the SQL engine of Oracle cannot determine the program statement during the compilation period. It can only be submitted to the SQL engine for processing after you enter certain query conditions. Generally, static SQL adopts the previous compiling method, while dynamic SQL adopts the latter compiling method.

This article mainly discusses the development of dynamic SQL and provides some practical development skills at the end.

2. dynamic SQL Program Development

Understand the principles of dynamic SQL compilation, and master its basic development ideas. Since dynamic SQL is an "uncertain" SQL statement, its execution has its own characteristics. Oracle provides the execute immediate statement to execute dynamic SQL statements. The syntax is as follows:

Excute immediate dynamic SQL statement using binding parameter list returning into output parameter list;

This statement is described as follows:

1) dynamic SQL refers to DDL and uncertain DML (that is, DML with parameters)

2) The bound parameter list is the input parameter list, that is, the type is in, and the parameters in the runtime and dynamic SQL statements (in fact, placeholders can be understood as formal parameters in the function).

3) the output parameter list is the list of parameters returned after the dynamic SQL statement is executed.

4) Because dynamic SQL statements are determined at runtime, compared with static SQL statements, it will lose some system resources in exchange for its flexibility.

To better illustrate the development process, the following example is provided:

Set the EMP table of the database as follows:

Requirements:

1. Create the table and enter the corresponding data.

2. You can query the name and salary information based on the specific ID.

3. query the employee information based on the salary that exceeds the specified salary.

Based on the preceding requirements, you can create three processes (both use dynamic SQL) to achieve the following:

Process 1:

Process 2:

Process 3:

Note: In process 2, the dynamic SQL statement uses the Placeholder ": 1". In fact, it is equivalent to the form parameter of the function and uses ":" As the prefix, then use the using statement to replace p_id with: 1 at the runtime. p_id is equivalent to the real parameter in the function. In addition, the cursor opened in process 3 is a dynamic cursor, which also belongs to the category of dynamic SQL. The entire compilation and development process is similar to the execute immediate execution process, which is not described here.

[Nextpage]

3. dynamic SQL statement development skills

According to the previous analysis, the execution of dynamic SQL is a result of a loss of system ability in exchange for its flexibility. Therefore, it is necessary to optimize it to a certain extent. Based on the actual development experience, the author provides some development skills, it should be noted that many experiences here are not limited to dynamic SQL, but also some are suitable for static SQL, which will be marked in the description.

Tip 1: Try to use similar SQL statements, so that Oracle can directly cache the SQL statement through the sharing pool in SGA, then, when you execute similar statements in the next time, you can directly call the parsed statements in the cache to improve the execution efficiency.

Tip 2: Use batch editing whenever possible when it comes to a set unit. For example, if you want to raise a salary of 100 for employees with IDs of 101 and 10%, the salary should generally be in the following format:

For the above processing, when the amount of data is large, it will appear relatively slow, then if the use of Batch encoding, the entire set is first imported into the SQL engine for processing, this process is much more efficient than separate processing.CodeAs follows:

Forall is used for batch editing. Here we will summarize the batch editing process:

1) if insert, delete, update, and other statements in a loop reference a set element, you can move it to a forall statement.

2) If the select into, fetch into, or returning into clause references a set, use the bulk collect clause for merge.

3) If possible, use the host array to implement passing parameters between the program and the database server.

Tip 3: Use the nocopy compiler to improve PL/SQL Performance. By default, parameters of the out and in out types are transmitted by value. However, for parameter transfer of large object types or set types, the expected loss is great. To reduce the loss, the reference transmission method can be used, that is to say, this effect can be achieved by referencing the nocopy keyword during parameter declaration. For example, to create a process:

Square is a large object type. In this way, only an address is passed, rather than the entire object. Obviously, this process also improves the efficiency.

4. Summary

This article discusses the compilation principle, development process, and development skills of dynamic SQL. After introducing this article, I believe that readers have a general understanding of dynamic SQL program development, lay a good foundation for future in-depth work.

The preceding code has been successfully debugged in the following environments:

Server: Unix + oracle9.2

Client: Windows2000 Pro + Toad

Previous Page