PL/SQL programming skills summary PL/SQL correct choice of the cursor type

Source: Internet
Author: User
Tags sleep function

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,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 firstProgramWrite parallel_enable in the header so that the pipeline function can be used in the query statement.

2. How to Make PL/SQL programsWait for a while to execute?

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 approach is to use a loop for latency,Use the gettime function of dbms_utility to check the current time., ProgramCodeAs 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 a large amount of data to another PL/SQL program, we recommend that you use a set here, because this allows you to use a set of batch collection (Bulk collection) 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, you canEnhances program portability.

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)Use bulk collection whenever possible. 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, the table can be cached in 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.

Refer to here: PL/SQL correctly select the cursor type

Full text reference here: Http://www.cnblogs.com/benio/archive/2011/08/25/2152768.html

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.