oracle存儲過程臨時表

㈠ oracle 存儲過程 怎麼用內存表

一 臨時表語法

臨時表只在Oracle 8i 以及以上產品中支持。

語法：

<textarea cols="50"
rows="15" name="code" class="java:showcolumns">create global
temporary table 臨時表名 on commit preserve/delete rows
--preserve:SESSION級的臨時表，delete:TRANSACTION級的臨時表.</textarea>

SESSION級的臨時表數據在整個SESSION都存在，直到結束此次SESSION；而TRANSACTION級的臨時表數據在
TRANACTION結束後消失，即COMMIT/ROLLBACK或結束SESSION都會清除TRANACTION臨時表數據。

當會話退出或者用戶提交commit和回滾rollback事
務的時候，臨時表的數據自動清空，但是臨時表的結構以及元數據還存儲在用戶的數據字典中。當一個會話結束(用戶正常退出、用戶不正常退出、ORACLE實
例崩潰)或者一個事務結束的時候，Oracle對這個會話的表執行 TRUNCATE
語句清空臨時表數據，但不會清空其它會話臨時表中的數據。臨時表可以使用觸發器。

二 臨時表ORACLE資料庫與sqlserver的區別

ORACLE臨時表，transaction或者session結束，會清空表數據。但是表還存在。

SQL SERVER臨時表，存儲在 tempdb 中，當不再使用時會自動刪除。

1 SQL SERVER臨時表是一種」內存表」，表是存儲在內存中的。ORACLE臨時表除非執行DROP TABLE,否則表定義會保留在數據字典中；

2 SQL SERVER臨時表不存在類似ORACLE臨時表事務級別上的功能；

3 SQL SERVER本地臨時表(#) 與 ORACLE的會話級別臨時表類似，但是在會話退出的時候，SQL SERVER會自動刪除，ORACLE不會刪除表；

4 SQL SERVER的全局臨時表(##) 是指多個連接共享同一片內存。當沒有指針引用該內存區域時，SQL SERVER自動釋放全局臨時表。

ORACLE不是一種內存中的資料庫，所以如果ORACLE類似SQL SERVER 頻繁的對臨時表進行建立和刪除，必定會影響性能。所以ORACLE會保留臨時表的定義直到用戶DROP TABLE。

在ORACLE中，如果需要多個用戶共享一個表(類似
SQL
SERVER的全局臨時表##)，則可以利用永久表，並且在表中添加一些可以唯一標識用戶的列。利用觸發器和視圖，當用戶退出的時候，根據該登陸用戶的唯
一信息刪除相應的表中的數據。 但這種方法給ORACLE帶來了一定量的負載。

三 使用

1 判斷表不存在就創建表

<textarea cols="50" rows="15" name="code" class="java:showcolumns:firstline[1]">create ...
is
temptable_name varchar2(20);
v_count number(1);
begin
temptable_name := 'TEMP_TABLENAME';
select count(*) into v_count from tab where tname = temptable_name;
if(v_count = 0) then
execute immediate 'create table ...';
else
--其他表存在的操作
end if;</textarea>

2 注意使用時，可能會產生的錯誤：ORA-08103: object no longer
exists，ORA-14452 attempt to create, alter or drop an index on temporary
table already in use

ORA-14452 ： 通過1的判斷，一般會解決該問題。如果在表使用時，執行刪除表操作，會出現該錯誤。

ORA-08103 ： 使用事務級的臨時表時，如果，事務提交前執行刪除表操作或者在事務提交以後使用，就會產生這樣的問題。改成會話級別的可以解決這個問題，但是在會話級別的數據的正確性，可能會因為使用連接池，同一個會話中不同操作而產生錯誤。

㈡ ORACLE存儲過程創建臨時表並插入數據。

你這個語法是不對的
你create
table
as
select
的時候是不能指定列的數據類型，因為你是根據查詢結果創建一個表，列名和數據類型都是由查詢結果定的，或者你可以
create
table
然後
insert
into
table
select
...
改成下面這樣
create
or
replace
procere
test_proc
as
begin
execute
immediate
'
create
global
temporary
table
T_AB
as
select
A.id_,
A.name_,
B.num_
from
T_A
A,
T_B
B
where
A.id_=B.id_';
end;

㈢ oracle 在存儲過程中動態的建一個臨時表使用和在資料庫里寫死一個臨時表使用兩者有什麼區別

我們仍使用實驗 05中的環境，略去准備數據的過程。

我們仍然使用兩個會話，一個會話 run，用於運行主 SQL；另一個會話 ps，用於進行 performance_schema 的觀察：

主會話線程號為 29，

可以看到寫入的線程是 page_clean_thread，是一個刷臟操作，這樣就能理解數據為什麼是慢慢寫入的。

也可以看到每個 IO 操作的大小是 16K，也就是刷數據頁的操作。

結論：

我們可以看到，

1. MySQL 會基本遵守 max_heap_table_size 的設定，在內存不夠用時，直接將表轉到磁碟上存儲。

2. 由於引擎不同（內存中表引擎為 heap，磁碟中表引擎則跟隨 internal_tmp_disk_storage_engine 的配置），本次實驗寫磁碟的數據量和實驗 05中使用內存的數據量不同。

3. 如果臨時表要使用磁碟，表引擎配置為 InnoDB，那麼即使臨時表在一個時間很短的 SQL 中使用，且使用後即釋放，釋放後也會刷臟頁到磁碟中，消耗部分 IO。

㈣ ORACLE存儲過程創建臨時表並插入數據。

存儲過程創建表後，在編譯階段資料庫中並沒有該表。這時向表中插入數據，會提示表不存在。所以，插入語句要賦值到變數里，通過e來執行。

㈤ oracle存儲過程中臨時表的使用，該怎麼處理

1、Oracle臨時表分兩種，事務級臨時表和會話級臨時表
2、事務級臨時表在事務結束後會被清空，會話級臨時表在事務結束後不會清空而是在回話結束會自動清空。
3、如果是在存儲過程用臨時表並不需要從臨時表裡把數據返回到存儲過程之外的用事務級臨時表即可；如果要從臨時表裡把數據返回到存儲過程之外則需要用會話級臨時表(這個跟mssql不一樣，mssql用事務級臨時表也可以返回結果)
註：存儲過程里使用會話級臨時表時，往臨時表裡插入數據前要先清空臨時表內的數據

㈥ oracle函數和存儲過程有什麼區別

一、主體不同

1、函數：當需要分析數據清單中的數值是否符合特定條件時，使用資料庫工作表函數。

2、存儲過程：是在大型資料庫系統中，一組為了完成特定功能的SQL 語句集，存儲在資料庫中，一次編譯後永久有效。

二、特點不同

1、函數：只能返回一個變數，可以嵌入sql中和存儲過程中使用。

2、存儲過程：用戶通過指定存儲過程的名字並給出參數（如果該存儲過程帶有參數）來執行。存儲過程是資料庫中的一個重要對象。在數據量特別龐大的情況下利用存儲過程能達到倍速的效率提升。

三、功能不同

1、函數：不能用臨時表，只能用表變數，函數不能執行一組修改全局資料庫狀態的操作。可以作為查詢語句的一個部分來調用，由於函數可以返回一個表對象，所以在查詢中位於from關鍵字後面。

2、存儲過程：存儲過程需要讓sql的query可以執行，存儲過程可以返回參數，如記錄集。sql語句中不可以含有存儲過程。

㈦ oracle 怎麼在存儲過程中創建一個臨時表，在裡面插入數據，再查找這個臨時表的所有數據，最後drop這個表。

Oracle
的臨時表的
處理機制，
和
SQL
Server
的不一樣。
假如你的臨時表的結構不是每次執行都發生變化的話
那麼就是事先
通過
CREATE
GLOBAL
TEMPORARY
TABLE
語句，把臨時表建立好。
存儲過程裡面，就根據需要，
執行
INSERT
/
SELECT
之類的操作就可以了

㈧ ORACLE 存儲過程怎麼返回臨時表結果集

我剛做的，參考下：
SQL> CREATE GLOBAL TEMPORARY TABLE REPROTTEST(
2 ID NUMBER,
3 ANAME VARCHAR2(20)
4 ) ON COMMIT DELETE ROWS;（也可以用PRESERVER ROWS，看實際需求）

Table created

SQL> create or replace procere report_month_responsibility(
2 o_cur out sys_refcursor)
3 as
4 begin
5 insert into reprottest(id,aname) values(1,'1');
6 open o_cur for select * from reprottest;
7 end report_month_responsibility;
8 /

Procere created

SQL> set serverout on
SQL> declare
2 v_id number;
3 v_aname varchar2(20);
4 o_cur sys_refcursor;
5 begin
6 report_month_responsibility(o_cur);
7 fetch o_cur into v_id,v_aname;
8 while o_cur%found loop
9 dbms_output.put_line('輸出結果：'||v_id||','||v_aname);
10 fetch o_cur into v_id,v_aname;
11 end loop;
12 commit;
13 end;
14 /

輸出結果：1,1

PL/SQL procere successfully completed

你的那個ORA-01031: insufficient privileges，是許可權不足的問題。
SQL代碼：

CREATE GLOBAL TEMPORARY TABLE REPROTTEST(
ID NUMBER,
ANAME VARCHAR2(20)
) ON COMMIT DELETE ROWS;

create or replace procere report_month_responsibility(
o_cur out sys_refcursor)
as
begin
insert into reprottest(id,aname) values(1,'1');
open o_cur for select * from reprottest;
end report_month_responsibility;

declare
v_id number;
v_aname varchar2(20);
o_cur sys_refcursor;
begin
report_month_responsibility(o_cur);
fetch o_cur into v_id,v_aname;
while o_cur%found loop
dbms_output.put_line('輸出結果：'||v_id||','||v_aname);
fetch o_cur into v_id,v_aname;
end loop;
commit;
end;
/