全局临时表 - Global Temprary Table

冷不防 2022-08-28 13:50 161阅读 0赞

会话
    create global temporary table session_temp_tab on commit preserve rows as select * from dba_users where 1=2;
    
    事务
    create global temporary table transaction_temp_tab on commit delete rows as select * from dba_users where 1=2;
    
    
    select count(1) from session_temp_tab;
    
    select count(1) from transaction_temp_tab;

![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5Zad6YaJ6YWS55qE5bCP55m9_size_20_color_FFFFFF_t_70_g_se_x_16]

insert into session_temp_tab select * from dba_users; 
    
    insert into transaction_temp_tab select * from dba_users;
    
    
    select count(1) from session_temp_tab;
    
    select count(1) from transaction_temp_tab;
    
    commmit;
    
    select count(1) from session_temp_tab;
    
    select count(1) from transaction_temp_tab;

![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5Zad6YaJ6YWS55qE5bCP55m9_size_17_color_FFFFFF_t_70_g_se_x_16]  
当commit之后事务级临时表中的数据自动清除，所以再次查询的时候得到结果为0 !!!

disconnnect;
    exit;
    dba
    select count(1) from session_temp_tab;

![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5Zad6YaJ6YWS55qE5bCP55m9_size_18_color_FFFFFF_t_70_g_se_x_16]  
当断开之后重新连接之后，会话级临时表中的数据也被自动删除了!!!

*  [全局临时表][Link 1]
 *  [全局临时表使用][Link 2]

[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5Zad6YaJ6YWS55qE5bCP55m9_size_20_color_FFFFFF_t_70_g_se_x_16]: /images/20220828/cd2d89b6b49d4385889d447617b9866e.png
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5Zad6YaJ6YWS55qE5bCP55m9_size_17_color_FFFFFF_t_70_g_se_x_16]: /images/20220828/dbebe13ad6b840cc9255248810bfac50.png
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5Zad6YaJ6YWS55qE5bCP55m9_size_18_color_FFFFFF_t_70_g_se_x_16]: /images/20220828/7fc139f971ca4099bec2a7dc02c27e09.png
[Link 1]: https://www.cnblogs.com/coolsundy/p/4546896.html
[Link 2]: https://www.cnblogs.com/taofengfeng/p/10898880.html