The Oracle tutorial being looked at is: a summary of Oracle9ipl/sql programming experience. Usually in the Pl/sql programming encountered some problems, here in the form of questions and answers to summarize them, for everyone to share.
1, when the need to load a large number of data flow to the table or to deal with a large number of data flow, can use the pipeline to improve processing efficiency?
Pipeline functions are convenient for improving the performance of parallel queries, which speeds up loading of data into tables. The use of piping functions is summarized as follows: two.
Whenever the Pl/sql function is used in a query, the query is serialized, that is, a single processor can run only one instance of the query, so it is not possible to use parallel queries in this case (for example, this technique is often used in a data warehouse). Therefore, you must use a piping function in order to use a parallel query, which also speeds up execution.
The input parameter of a pipe function must be a reference recordset type (that is, ref CURSOR) and a nested table type (each row in its table corresponds to each reference record). Before you can use a piping function, you must write a parallel_enable on the program header so that the pipe function is used in the query statement.
2. How do I make the Pl/sql program wait for some time to execute?
The method is to use the sleep function of the Dbms_lock packet, which can be timed accurately with the following syntax:
Dbms_lock. Sleep (seconds in number);
3. How do you perform a timed operation in a PL/SQL program after a table has been inserted into a record for a few seconds before another operation is performed?
The general practice is to use the loop as a delay, using the dbms_utility gettime function 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 execution statement * *
End Loop;
End;
/
In addition, if there is a timing between sessions, you must use the Dbms_pipe package function to implement message delivery between sessions.
4. When Pl/sql returns a dataset, does it use a collection or a cursor?
In general, the following two points are used as the basis:
1 if the Pl/sql program returns a lot of row data to another Pl/sql program, the collection is recommended here because it allows you to use the collection's batch collection (bulk collection) to increase the speed at which data is extracted from the database.
2 If you need to return data to the host locale (such as sql*plus,c,delphi, etc.) in the context of the Pl/sql program, you should use a cursor variable to return the data because almost all host languages support cursor variables, but not all host languages support collections. This can enhance the portability of programs.
5. How can I use cursors more effectively in Pl/sql?
Cursors are a very important concept in pl/sql, and the retrieval of a database relies primarily on cursors to manipulate it. There are two types of cursors in Pl/sql, one is an implicit cursor, such as select Clno into V_clno from Table_detail. The other is an explicit cursor, such as cursor v_cur is select Clno from Table_detail 。 The following are some suggestions for using cursors:
1) Use bulk collection as much as possible. It can greatly improve performance, in the second edition of Oracl9i, can even use bulk collection to directly write data to the record table
2 use explicit cursors as much as possible, because the explicit cursors are faster than the implicitly-type cursors.
3 If the table being queried is small or static, the table can be cached in a package-level collection. In this way, your query function directly from the collection (that is, the process global area, the PGA cache), rather than from the system global Area (SGA) to fetch data, such processing speed will improve a lot.
