Developer on Alibaba Coud: Build your first app with APIs, SDKs, and tutorials on the Alibaba Cloud. Read more ＞
oracle| Temporary Table | question
In a program, a subquery is often required within a primary key range, which is dynamically generated in the foreground. So the only one that comes over is a string-formatted SQL statement that determines the range of the primary key. The previous practice was to spell out the larger statement of the final result in the SP based on the SQL statement. It is often inefficient to find statements like this, and database changes (such as column names) It is often not possible to be checked out when the package is compiled.
The solution is to first query the value of this primary key range, and then use the results of this query and other tables as a link, so that the final SQL is no longer a string format. Because the primary key range is relatively small, the efficiency can be improved a lot.
Then turn to Oracle's temporary table, which looks like the ideal place to store the results of a primary key query. First thought is the transaction type, but found in the foreground. NET program after the stored procedure is executed, a commit is performed by default, so the result returned is "object no longer exists." There is no way to understand the return mechanism of the cursor, but it seems to be a bit of a drastic meaning.
So instead of going to the conversational type, the result of the query will be retained after the transaction is completed, as long as the connection is not disconnected--this can be satisfied in our program of C/s architecture. A new problem is how to ensure that two executions do not have an impact. The practice now is simply to truncate the temporary table before each execution, so that every time you perform, the stored procedure sees an empty table. So far, most of the problems have been solved in this rather bizarre way.
But the problem is still there. That's the problem of concurrency. Because it is the C/s architecture, each of the current connections is limited to one client, while the chances of generating such concurrency on the same client are relatively small (not without). Just think of a possible idea is after each query is completed, first go to the data, and then immediately close the connection, The data in the temporary table is truncated automatically. Go and have a try tomorrow.
A little episode, a buddy searching the web for Oracle temporary table related Materials, send me a code similar to the following:
DECLARE @table1 Table (cust_id int NOT null)
Insert INTO @table1 select cust_id from Customer
It always feels familiar, but it does not compile in Oracle. Until I stared at the @ ... I finally understand, this is the code in SQL Server, Ft Ah, half a year not to degenerate to this point
This article is an English version of an article which is originally in the Chinese language on aliyun.com and is provided for information purposes only. This website makes no representation or warranty of any kind, either expressed or implied, as to the accuracy, completeness ownership or
reliability of the article or any translations thereof. If you have any concerns or complaints relating to the article, please send an email, providing a detailed description of the concern or
complaint, to email@example.com. A staff member will contact you within 5 working days. Once verified, infringing content will be removed immediately.
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:
and provide relevant evidence. A staff member will contact you within 5 working days.