Shulou(Shulou.com)06/02 Report--

In this issue, the editor will bring you about how to use temporary tables in Oracle. The article is rich in content and analyzes and narrates it from a professional point of view. I hope you can get something after reading this article.

Introduction of Oracle temporary Table

The temporary table temporary tables of Oracle is generally divided into two kinds of temporary tables, the session-level temporary table and the transaction-level temporary table.

1. Session-level temporary table

Session-level temporary table means that the data in the temporary table only exists in the session life cycle. When the user exits and the session ends, Oracle automatically clears the temporary table data.

Format:

Create Global Temporary Table Table_Name

(

Col1 Type1

Col2 Type2

...

)

On Commit Preserve Rows

two。 Transaction-level temporary table

A transaction-level temporary table means that the data in the temporary table exists only in the transaction life cycle.

Create Global Temporary Table Table_Name

(

Col1 Type1

Col2 Type2

...

)

On Commit Delete Rows

When a transaction ends (commit or rollback), Oracle automatically clears the data in the temporary table.

Be careful

Generally speaking, when we use temporary tables in SQL, we will create them and delete them (or delete them automatically) after using them, as follows:

However, in Oracle, because there are session-level and transaction-level temporary tables, they will be emptied automatically after use, and so on. It is not recommended to Drop and then Create every time. The main reason is that I found that I used the Drop temporary table in the process of testing. The error at that time was not recorded, and then it was created without it.

Temporary table usage

Let's go straight to the code.

Declare vi_count integer

Vs_sSql varchar2 (4000): =''

Begin

Vs_sSql:= 'select count (*) from user_tables where table_name = upper (' | |

Chr (39) | | 'temp_cstable' | | chr (39) | |')'

Execute immediate vs_sSql into vi_count

Dbms_output.put_line (vi_count)

-- determine whether the temporary table of temp_cstable exists. If there is a temporary table, it will be created if it does not exist.

If vi_count > 0 then

Vs_sSql: = 'delete from temp_cstable'

Execute immediate vs_sSql

Else

Vs_sSql: ='

Create global temporary table temp_cstable (

Incode varchar2 (20)

Barcode varchar2 (20)

Xstotal number

Yhtotal number) on commit delete rows'

Execute immediate vs_sSql

End if

End

This is how to use temporary tables in Oracle shared by Xiaobian. If you happen to have similar doubts, please refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.