Improved Oracle PL/SQL programming style and system performance

Oracle is a large database with a C/SClient/Server structure. It is mainly used in the 4GLSQL language and has the advantages of being powerful, simple, and easy to learn. The working process of the C/S structure is: when the client c end) inputs and sends an SQL statement, it will be sent to the server S end through the network ), the result is analyzed and executed, and then returned to the client through the network. At this time, the client can send an SQL statement. It can be seen that the client sends a statement and a statement, and the server analyzes and executes the statement one by one, and the results are returned again and again. However, such a single SQL statement causes frequent network communication, greatly reducing system performance. PL/SQL is the language after SQL expansion. PL/SQL blocks are sent as a unit and use less network communication. In addition, PL/SQL blocks can be used according to the conditions during operation, deciding what statements to execute or execute repeatedly not only retains the power of SQL, but also makes up for the shortcomings.

1. Improve program running speed

1.1 Use stored procedures

A stored procedure is an object of the Oracle database. It is a named PL/SQL process block. After being created, it is analyzed by the database server for syntax and syntax, stored in the database in compiled form, and can be called by the authorized user wherever needed. When a client application is called, the database server only needs to send a call command to execute this process. The main difference from PL/SQL blocks is that you do not need to transmit a large number of source code on the Internet, but only send one invocation command, which greatly reduces the burden of network communication; it only analyzes and compiles the code once when it was just created, and runs the compiled code directly at each call, so the running speed is faster.

In actual development, it is best to use stored procedures for functional modules with common features. Different actual parameter values are used for calling to implement specific processing. If the stored procedure can be fully used to complete the operation and processing of the application system, the system running performance can be greatly improved.

1.2 compile SQL statements that can reuse existing statements in the Shared Pool

The shared memory buffer and background process are collectively called an Oracle instance. When an Oracle instance is started, many Oracle background processes are started. Each process is responsible for processing different aspects of the database. Each process communicates with each other through the shared memory, this block of memory is the global SGA of the system. SGA is separated into different regions, one of which is called the Shared Pool.) The region contains the body of the SQL statement sent to the database and the PL/SQL block, and their representation and execution scheme after analysis. The execution scheme is the method used by the database to actually process the statement, such as the tables and indexes to be accessed and whether to perform sorting.

To execute an SQL statement, the database must determine its execution plan. When the database receives an SQL statement from the client application, it first checks whether the statement is in the shared pool. If the statement is in, the form and execution plan already in the shared pool will be reused instead of re-analysis. If not, the statement will be analyzed, get a new analysis form and execution scheme, and store it to overwrite the previous content in the shared pool.

From this we can see that it is necessary to write SQL statements that can reuse statements in the Shared Pool, because avoiding unnecessary re-analysis will greatly reduce the workload of the server. To reuse the statements in the shared pool, you should write the statements in the same format, including uppercase and lowercase letters, punctuation marks, and line breaks. An effective and practical method is recommended below.

1.2.1 format of each part of the SQL statement

A statement can be written in one or more lines, but it is best to write in a line break. Each clause is a row, and the first keyword of each line is aligned with the end of the keyword of the first line, this ensures that the row position is consistent each time the same statement is used, instead of accidental overflow of the statement to the next row in column 80th, for example, in the following format, T, M, E, D, and P are aligned, and a clause occupies one row:

SELECT col1,col2FROM table—name1WHERE col1 > col2AND col2 > col3GROUP BY col1;

After the preceding statement is executed, the following statement is received:

SELECT col1,col2FROM table—name2WHERE col1 > col2AND col2 > col3GROUP BY col2;

Check that the statement in the shared pool is consistent with that in the shared pool. You can reuse the execution scheme in the shared pool without re-analyzing it. The following statement is considered to be different because the branch location is different and needs to be analyzed again.

SELECT col1,col2 FROM table—name2WHERE col1 > col2AND col2 > col3GROUP BY col1 ;

1.2.2 uppercase/lowercase letters

Keywords, reserved words in upper case, and user-declared identifiers in lower case. See the following two statements:

SELECT xmFROM student; and select xmFROM student;

The comparison result is that the two sentences do not match or are not equivalent, because the SELECT clause in the first sentence is in upper case and the second sentence is in lower case. 1.2.3 others, such as leaving a space on both sides of the operator

In short, designing your own writing conventions and complying with these conventions will make the statements to be processed consistent with those in the sharing pool and help improve the running performance.

2. improve maintainability

2.1 compile a trigger

It is very common to modify, delete, or insert data in a table. When the table is modified, it should automatically send signals to other programs that require operations. The trigger can complete this function. In Oracle8, a trigger is a program, but this program is automatically executed when an INSERT, UPDATE, or DELETE operation occurs, which is different from a process call by calling a statement, therefore, when an event causes a serial update or other related operations, it is implemented by automatically executing the trigger code without manual intervention, which greatly reduces the maintenance work, it also ensures data consistency.

The advantage of a trigger is that the trigger is automatically triggered, No matter what causes the data to be modified from the program or from the user), they all work, so they are often used for the serial modification of the relevant data in different data tables. Using this method, indirect data association of data tables can be centrally maintained and controlled by the database. When rules change, you only need to modify the corresponding trigger. This makes the system easy to maintain and improves work efficiency.

2.2 declare variables using % TYPE and % ROWTYPE

In program design, variables are often used to transmit data between programs. In this case, the data in the table is assigned to the variable or the variable value is inserted into the table. To complete these operations, the data in the table must be consistent with the variable type. However, in practice, the data, type, or width of a table must change sometimes. Once changed, you must modify the variable declaration part of the program. Otherwise, the program will not run properly. To reduce the modification of this part of the program, the variables are declared using % TYPE and % ROWTYPE during programming, so that the declared types of variables are synchronized with those in the table and change with the changes in the table, such a program is more universal to a certain extent.

3. Improve program self-check capability

A good application system should not only have a good user interface, complete functional processing modules, but also have strong error processing capabilities. Therefore, programmers are required to declare abnormal modal and cause various possible situations), and try to recover from the error to write the corresponding abnormal modal processor Code ), this is the program design of the exception section in Oracle8. However, this part of the design is not easy. Setting the OTHERS exception modal processor at the end of the exception section is a good programming habit, because it specifies the processing destination for other errors captured at runtime, thus ensuring the normal operation of the program. The format is as follows:

BEGIN…EXCEPTION　WHEN excep—name1 THEN　…　WHEN excep—name2 THEN　…　WHEN OTHERS THEN　…END;

But to correctly handle errors in the program, that is, to correctly select and execute the exception processor), you must also find out the propagation problem of the abnormal modal.

The spread of abnormal modal refers to when the declaration, execution, and exception sections of the program block have abnormal modal, or where the exception pattern will be transmitted when there is no corresponding exception processor in this block, it will inspire the processor in that block. The Propagation Rules are as follows: When an abnormal modal is the most common cause of block execution, PL/SQL uses the following rules to determine which exception processor to activate. 1) if the current block sets a processor for the abnormal modal, execute it, successfully complete the execution of the block, and then transfer the control to the inclusion block. 2) if the current block does not have this processor, It is triggered in the inclusion block to spread the abnormal modal. Then, perform Step 1 on the contained blocks. In addition, whether an abnormal modal is triggered in the declaration part or in the exception handling part, the abnormal modal is immediately transmitted to the inclusion block. When the preceding rules are referenced in the contained block, the processing of abnormal modal will not be executed even if the OTHERS processor is set in the current block.

4 easy to read

● End ID is used for subprograms, triggers, packages, and other program blocks with names. For example:

Create or replace procedure addstud IS... BEGIN... END addstud;/* The process name add here is optional. It should be well written and correspond to the CREATE statement at the beginning of the block */

● Adopt uniform identifier naming rules. When naming database objects such as variable name, subroutine name, and trigger name, you must specify the function purpose or meaning.

● The indent style of procedural statements and program blocks makes the program structure clear, hierarchical, and easy to read.

● Unified uppercase and lowercase letters are used. Although PL/SQL programs are case-insensitive, the use of unified letters such as the Case conventions described above) will greatly improve the readability of the program.

● Add comments.

● A statement is written in multiple lines, so that it is not allowed to be automatically split.

You are familiar with this content and will not go into details.

In short, a good programming style can improve the system performance and development efficiency in multiple aspects, which deserves our attention in our work.

(