Finally handled Oracle's temporary table problem in a strange way.

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