Summary of PL and SQL programming experience developer network Oracle
- 2020-06-01 11:13:06
- OfStack
The ORACLE tutorial you are looking at is :PL/SQL programming experience summary developer web Oracle. I have encountered some problems in PL/SQL programming at ordinary times. Here we summarize them in the form of questions and answers for everyone to share.
1. When a large number of data streams need to be loaded into the table or a large number of data streams need to be processed, can a pipeline be used to improve the processing efficiency?
Pipe functions are very convenient for improving the performance of parallel queries by speeding up the loading of data into a table. The use of pipeline functions is summarized as follows:
Whenever the PL/SQL function is used in a query, the query is serialized, meaning that one processor can only run one instance of the query, which makes it impossible to use parallel queries (for example, frequently in a data warehouse). Therefore, in order to use parallel queries, you must use pipeline functions, which speed up execution.
The input parameter to the pipe function must be a reference recordset type (ref cursor) and return a nested table type (with each row in the table corresponding to each reference record). Before using the pipe function, you must write PARALLEL_ENABLE in the program header so that you can use the pipe function in the query.
2. How do I make the PL/SQL program wait for 1 period of time to execute?
The method is to use the SLEEP function of the DBMS_LOCK package, which can be precisely timed. The syntax is:
DBMS_LOCK.SLEEP (seconds IN NUMBER);
3. If you need to wait a few seconds after inserting a record into a table before performing another operation, how do you schedule the operation in the PL/SQL program?
The method of 1 is to use the loop as the delay, and use the gettime function of DBMS_UTILITY to detect the current time. The program code is as follows:
In addition, if the timing is between different sessions (session), the functions of the DBMS_PIPE package must be used to achieve inter-session messaging.
4. When PL/SQL returns a data set, should you use a collection or a cursor?
1. In general, the following two points are taken as the basis:
1) if the PL/SQL program returns multiple rows of data to another PL/SQL program, it is recommended to use a collection, as this can take advantage of the batch collection of the collection (bulk collection) to increase the speed of data extraction from the database.
2) if you need to return data to the host language environment in the environment of PL/SQL programs (such as Sql*plus,c,delphi, etc.), you should use cursor variables to return the data, because almost all host languages support cursor variables, but not all host languages support collections. This increases the portability of the program.
5. How to use cursors in PL/SQL more efficiently?
Cursor is a very important concept in PL/SQL, and the retrieval of database is mainly operated by cursor. 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 one is an explicit cursor, such as cursor v_cur is select clno table_detail. Here are some Suggestions for using cursors:
1) use bulk collection whenever possible. It can greatly improve performance, and even write data directly to the recording table using bulk collection in version 2 of Oracl9i
2) try to use explicit cursors, which are faster than implicit ones.
3) if the table of the query is small or static, the table can be cached in a package-level collection. This allows your query function to fetch data directly from the collection (PGA cache), rather than from the system global area (SGA), which is much faster.
1. When a large number of data streams need to be loaded into the table or a large number of data streams need to be processed, can a pipeline be used to improve the processing efficiency?
Pipe functions are very convenient for improving the performance of parallel queries by speeding up the loading of data into a table. The use of pipeline functions is summarized as follows:
Whenever the PL/SQL function is used in a query, the query is serialized, meaning that one processor can only run one instance of the query, which makes it impossible to use parallel queries (for example, frequently in a data warehouse). Therefore, in order to use parallel queries, you must use pipeline functions, which speed up execution.
The input parameter to the pipe function must be a reference recordset type (ref cursor) and return a nested table type (with each row in the table corresponding to each reference record). Before using the pipe function, you must write PARALLEL_ENABLE in the program header so that you can use the pipe function in the query.
2. How do I make the PL/SQL program wait for 1 period of time to execute?
The method is to use the SLEEP function of the DBMS_LOCK package, which can be precisely timed. The syntax is:
DBMS_LOCK.SLEEP (seconds IN NUMBER);
3. If you need to wait a few seconds after inserting a record into a table before performing another operation, how do you schedule the operation in the PL/SQL program?
The method of 1 is to use the loop as the delay, and use the gettime function of DBMS_UTILITY to detect the current time. The program code is as follows:
In addition, if the timing is between different sessions (session), the functions of the DBMS_PIPE package must be used to achieve inter-session messaging.
4. When PL/SQL returns a data set, should you use a collection or a cursor?
1. In general, the following two points are taken as the basis:
1) if the PL/SQL program returns multiple rows of data to another PL/SQL program, it is recommended to use a collection, as this can take advantage of the batch collection of the collection (bulk collection) to increase the speed of data extraction from the database.
2) if you need to return data to the host language environment in the environment of PL/SQL programs (such as Sql*plus,c,delphi, etc.), you should use cursor variables to return the data, because almost all host languages support cursor variables, but not all host languages support collections. This increases the portability of the program.
5. How to use cursors in PL/SQL more efficiently?
Cursor is a very important concept in PL/SQL, and the retrieval of database is mainly operated by cursor. 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 one is an explicit cursor, such as cursor v_cur is select clno table_detail. Here are some Suggestions for using cursors:
1) use bulk collection whenever possible. It can greatly improve performance, and even write data directly to the recording table using bulk collection in version 2 of Oracl9i
2) try to use explicit cursors, which are faster than implicit ones.
3) if the table of the query is small or static, the table can be cached in a package-level collection. This allows your query function to fetch data directly from the collection (PGA cache), rather than from the system global area (SGA), which is much faster.