Development of high Performance dynamic SQL program based on Oracle _oracle

The Oracle tutorial you are looking at is the development of high-performance, dynamic SQL programs based on Oracle.

Absrtact: The development of dynamic SQL program is summarized, and some development techniques are given according to the author's actual development experience.

Key words: Dynamic Sql,pl/sql, high performance

1. static sqlsql and dynamic SQL

Oracle compiled PL/SQL program block is divided into two kinds: first, the previous early binding, that is, the SQL statement was established during the program compilation, most of the compilation is this type, and the other is the late-stage (late binding), That is, the SQL statement can only be established at run time, for example, when the query condition is entered by the user, the Oracle SQL engine cannot determine the program statement at compile time, and can submit it to the SQL engine for processing only after the user has entered a certain query condition. In general, static SQL takes the previous form of compilation, and dynamic SQL takes the latter form of compilation.

This paper mainly discusses the development of dynamic SQL, and finally gives some practical development techniques.

2. Dynamic SQL program Development

The principle of dynamic SQL compilation is understood, and the basic development idea is mastered. Since dynamic SQL is an "indeterminate" SQL, its execution has its own characteristics. The Execute immediate statement is provided in Oracle to execute dynamic SQL, 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 indeterminate DML (that is, DML with arguments)

2 The binding parameter list is the input parameter list, which is of type in type, and is bound at runtime with the arguments in the dynamic SQL statement (actually placeholders, which can be understood as the form parameters inside the function).

3 The output parameter list is the list of parameters returned after the execution of the dynamic SQL statement.

4 because dynamic SQL is determined at run time, it will lose some system performance in exchange for its flexibility relative to static.

To better illustrate the process of its development, here is an example:

Set the EMP table for the database with the following data:


Requirements:

1. Create the table and enter the appropriate data.

2. Information about its name and salary can be queried based on a specific ID.

3. The employee information is based on a query that is larger than a specific salary.

According to the previous requirements, you can create three separate processes (all using dynamic SQL) to achieve:

Process one:


Process two:


Process three:


Note: The dynamic SQL statement in procedure two uses the placeholder ": 1", which is equivalent to the form parameter of the function, uses ":" As the prefix, and then uses the using statement to replace the p_id at run time with: 1, where p_id is equivalent to the argument in the function. In addition, the cursor opened in process Three is a dynamic cursor, and it belongs to the category of dynamic SQL, and its entire compilation and development process is similar to that executed by execute immediate.

[NextPage]

3. Techniques for developing dynamic SQL statements

In the previous analysis, dynamic SQL was executed in exchange for its flexibility in the loss of system performance. So it is necessary to optimize it to a certain extent, the author according to the actual development experience to give some development skills, it should be pointed out that many of the experience here is not limited to dynamic SQL, some also apply to static SQL, Annotations are given in the description.

Tip One: Use a similar SQL statement as much as possible, so that Oracle itself caches the SQL statement directly through a shared pool in the SGA, so that it can improve execution efficiency by directly calling the parsed statement in the cache the next time a similar statement is executed.

Tip Two: When it comes to collecting units, use the batch-linked series as much as possible. For example, a salary increase of 10% for employees with IDs of 100 and 101 is required, usually in the following form:


For the above processing, when the volume of data will appear relatively slow, if the use of batch-linked, then the entire collection of the first one-time incoming to the SQL engine processing, which is more efficient than the processing efficiency, the batch of the code to process the following:


Here is the use of forall to carry out the batch of the series, where the context of the batch process is summarized as follows:

1 if a insert,delete,update such as a loop executes a reference to a collection element, it can be moved to a forall statement.

2 If the select Into,fetch into or returning into clause references a collection, it should be merged using the bulk collect clause.

3 if possible, host arrays should be used to implement parameters between the program and the database server.

Tip Three: Use the Nocopy compiler to improve pl/sql performance. By default, parameters for out types and in-out types are passed by value. But for the large object type or the collection type parameter transmission, its hope loss will be very big, in order to reduce the loss, may use the reference transmission way, namely when carries on the parameter declaration the reference Nocopy keyword to explain can arrive this kind of effect. For example, to create a process:


Where square is a large object type. This simply passes an address instead of passing the entire object. It is clear that such treatment also improves efficiency.

4. Summary

This paper discusses the compiling principle, development process and development techniques of dynamic SQL, and after introducing this article, I believe that readers have a general understanding of the development of dynamic SQL program and lay a good foundation for further work in the future.

The previous Code section has been successfully debugged in the following environments:

Server side: unix+oracle9.2

Client: WINDOWS2000 pro+toad

Previous page