Explain the application of Oracle temporary tables in practice

The Application of Oracle temporary tables in actual development is what we will introduce in this article. We know that I have always been amazed by SQL Server when writing a query-type stored procedure. Create procedure ps_procedure_name as select * from table; the Select query statement can be used as part of the SQL Server Stored procedure and directly return the result set. However, implementing this type of stored procedures in Oracle is not syntactic. To achieve this goal, we need to use an Oracle temporary table (this is one of the solutions ).
In addition to permanent tables, the Oracle database can also create temporary tables. Oracle temporary tables are divided into session-level temporary tables (on commit preserve rows) and transaction-level temporary tables (on commit delete rows ). A session-level temporary table means that the data in the temporary table only exists in the session lifecycle. When the user exits the session, Oracle automatically clears the data in the temporary table. Transaction-level temporary table means that the data in the temporary table only exists in the transaction lifecycle. When a transaction ends (commit or rollback), Oracle automatically clears the data in the temporary table. The data in the temporary table is only valid for the current Session. Each Session has its own temporary data and cannot access the data in the temporary table of other sessions. // CREATE a temporary table in the database: create global temporary table esps. ESPS_EMP_RANK_TEMP (COM VARCHAR2 (20 BYTE), EMPNO VARCHAR2 (20 BYTE), EMPNAME VARCHAR2 (100 BYTE), DEPTNAME VARCHAR2 (300 BYTE), amount number, comment NUMBER, FOLDING_AMOUNT NUMBER, benefit number, EXECUTED_RATE NUMBER, FOLDING_RANK NUMBER, BENEFIT_RANK NUMBER, rank number, TOTAL_RANK NUMBER) // on commit preserve rows -- transaction-level temporary table ON COMMI T preserve rows -- this indicates that the session-level temporary table is verified in actual operations. At first, I used a transaction-level temporary table. When I inserted data into a temporary table, I used the commit operation to clear the contents of the temporary table. For example: insert into ESPS_EMP_RANK_TEMP select * from table; commint; after executing these two SQL statements, it is equivalent to not inserting data. If we use a session-level temporary table, even if we use commit, the content in the temporary table still exists. The content in the temporary table is cleared only when the session is disconnected. Therefore, in the actual development process, we use different types of temporary tables for this combination scenario. During the development of the proposed proposal system, I used a session-level temporary table. Insert the queried data into the temporary table and read the content of the temporary table. When the session is disconnected, the content in the temporary table is automatically cleared. Three major steps for calling in C #: // C # a String-type variable defined in it, storing a dynamic SQL statement. The stored procedure is ESPS_EMP_RANK_STATISTIS ({0 }, '{1}') public static string I _EMP_RANK_STATISTIS = "BEGIN ESPS_EMP_RANK_STATISTIS ({0}, '{1}'); END;"; // executes the stored procedure, insert the result set to the temporary table ESPS_EMP_RANK_TEMP WAF. COM. core. oracleHelper. executeNonQuery (conn, CommandType. text, string. format (SqlCollector. I _EMP_RANK_STATISTIS, 2, '123'); // read the result set saved in the temporary table Oracle DataReader dr = WAF. COM. core. OracleHelper. ExecuteReader ("select * from ESPS_EMP_RANK_TEMP") This article introduces the application of Oracle temporary tables in actual development. I hope this article will help you gain some benefits!

Author zqs520