PL/SQL programming skills

I usually encounter some problems in PL/SQL programming. I will summarize them in the form of Q & A for you to share.

1. Can pipelines be used to improve processing efficiency when you need to load a large amount of data streams to a table or process a large amount of data streams?
MPs queue functions are very convenient for improving the performance of parallel queries. they speed up data loading to tables. MPs queue functions are summarized as follows:

When PL/SQL functions are used in a query, the query is serialized, that is, one processor can only run one query instance, in this case, parallel queries cannot be used (for example, this technology is often used in data warehouses ). Therefore, in order to use parallel queries, you must use pipeline functions, which speeds up execution.

The input parameter of the pipeline function must be a reference record set type (that is, ref cursor), and the returned type is a nested table type (each row in the table corresponds to each reference record ). Before using the pipeline function, you must write PARALLEL_ENABLE in the program header to use the Pipeline Function in the query statement.

2. How to Make the PL/SQL program wait for a period of time for execution?
　
The method is to use the SLEEP function of the DBMS_LOCK package for exact timing. Its syntax is:

DBMS_LOCK.SLEEP (seconds in number );

3. You need to insert a record in a table and wait several seconds before performing another operation. How can you perform regular operations in the PL/SQL program?

The general method is to use a loop for latency, and use the gettime function of DBMS_UTILITY to detect the current time. The program code is as follows:

DECLARE
V_delaytime constant integer: = 100;
V_starttime INTEGER;
V_endtime INTEGER;
BEGIN
V_starttime: = DBMS_UTILITY.get_time;
V_endtime: = DBMS_UTILITY.get_time;
While abs (V_endtime-V_starttime) <v_delaytime loop
/* Empty loop or simple time-consuming statement execution */
End loop;
END;
/

In addition, for the timing between different sessions, you must use the DBMS_PIPE Package function to transmit messages between sessions.

4. When PL/SQL returns a dataset, should we use a set or a cursor?

Generally, the following two points are used as the basis:

1) if the PL/SQL program returns multiple rows of data to another PL/SQL program, we recommend that you use a set here, because bulk collection can be used) to speed up data extraction from the database.

2) if you need to return data to the host language environment (such as SQL * plus, c, and delphi) in the PL/SQL program environment ), in this case, the cursor variable should be used to return the data, because almost all host languages support the cursor variable, but not all host languages support the set. In this way, the program portability can be enhanced.

5. How can I use a cursor in PL/SQL more effectively?

Cursor is a very important concept in PL/SQL. It mainly relies on cursor for database retrieval. There are two types of cursor v_cur is select clno from table_detail in PL/SQL, one is implicit cursor, and the other is explicit cursor v_cur is select clno from table_detail. Here are some suggestions for using the cursor:

1) Try to use bulk collection. It can greatly improve the running performance. In the second version of Oracl9i, you can even use bulk collection to directly write data to the record table.

2) Try to use an explicit cursor for processing, because it is faster than an implicit cursor.

3) if the queried table is small or static, You Can cache the table to a package-level set. In this way, your query function retrieves data directly from the set (that is, the process global zone, PGA cache), rather than from the system global zone (SGA, this processing speed will increase a lot.