檢查此 Oracle sql 腳本：
SET SERVEROUTPUT ON SIZE 200000;
declare
start timestamp;
fin timestamp;
opr varchar2(64);
op varchar2(64);
ext_act varchar2(64);
time_sum float;
counter integer := 1;
query1 varchar2(4096):='select * from ITEM_HISTORY IH join PACKAGE P on P.PACKAGE_NAME = IH.PACKAGE_NAME where OPERATOR_ID = :opr and (IH.OPERATION != :op OR IH.EVENT_DATE = IH.INSTALLATION_DATE) and IH.EXTERNAL_SERVICE_ACTION != :ext_act and IH.EVENT_DATE >= :t_start and IH.EVENT_DATE < :t_end and rownum < 500000 order by IH.EVENT_DATE';
query2 varchar2(4096):='select * from (select * from ITEM_HISTORY IH join PACKAGE P on P.PACKAGE_NAME = IH.PACKAGE_NAME where OPERATOR_ID = :opr and (IH.OPERATION != :op OR IH.EVENT_DATE = IH.INSTALLATION_DATE) and IH.EXTERNAL_SERVICE_ACTION != :ext_act and IH.EVENT_DATE >= :t_start and IH.EVENT_DATE < :t_end) where rownum < 500000';
query3 varchar2(4096):='select * from ITEM_HISTORY IH join PACKAGE P on P.PACKAGE_NAME = IH.PACKAGE_NAME where OPERATOR_ID = :opr and (IH.OPERATION != :op OR IH.EVENT_DATE = IH.INSTALLATION_DATE) and IH.EXTERNAL_SERVICE_ACTION != :ext_act and IH.EVENT_DATE >= :t_start and IH.EVENT_DATE < :t_end fetch first 500000 rows only';
begin
FOR query IN (SELECT column_value FROM table(sys.dbms_debug_vc2coll(query1, query2, query3))) loop
dbms_output.put_line('This is query ' || counter);
for i in 1..10 loop
start:=systimestamp;
exec :opr := '88000001';
exec :op := 'CHANGE_OWNER';
exec :ext_act := 'NOT_APPLICABLE';
exec :t_start :=TO_DATE('2018/07/01', 'yyyy/mm/dd');
exec :t_end :=TO_DATE('2020/05/01', 'yyyy/mm/dd');
query;
fin := systimestamp;
duration := fin - start;
dbms_output.put_line('Time taken for query' || counter || ' for run ' || i || ': ' || duration);
time_sum := time_sum || duration;
end loop;
dbms_output.put_line('Avg time for query' || counter || ': ' || time_sum / 10);
time_sum := 0;
counter:=counter 1;
end loop;
end
我有錯誤：SP2-0552: Bind variable "T_END" not declared.，但是T_START很好，并且是之前T_END。如果T_END是壞的，為什么T_START不是？怎么了？
最初它們是t1and t2，但錯誤是一樣的：沒有錯誤t1，錯誤為t2。
uj5u.com熱心網友回復：
如果
T_END是壞的，為什么T_START不是？
它們都是壞的——兩者都沒有被宣告。
SQL Developer（我猜 - 或 SQL*Plus 或 SQLcl）正在報告它發現的第一個高級問題，即未宣告的系結變數。在那個級別，它不會查看 PL/SQL 本身或將任何內容傳遞給資料庫引擎進行驗證，而是進行自己的掃描以查看它是否認為代碼是健全的 - 并確定它不是，所以它停止立即，無需做任何實際作業。
它向后決議塊，因此它t_end首先查看和報告，然后停在那里。如果您注釋掉該行 (23)，那么它將報告t_start未宣告的內容。
您參考的系結變數都沒有在客戶端中定義 - 您需要在 PL/SQL 塊之前使用variable和exec命令；本身是匿名 PL/SQL 塊的客戶端簡寫，因此它不屬于您擁有它的地方——它是一個客戶端命令，而不是 PL/SQL 或 SQL。exec
代碼中的其他一些問題：
- 您正在使用
start非法的保留字（如）； - 您已經宣告
opr,op并且ext_act作為本地 PL/SQL 變數 -不是系結變數 - 但不要使用它們； - 您已宣告
time_sum為浮點數，但您正在處理時間戳之間的差異，即間隔； - 您指的
query是好像它是一個值，但它實際上是一個記錄 - 要使用游標查詢回傳的值，您需要參考query.column_value，或者查詢中的別名以獲得更有用的名稱； - 您正在嘗試將其
query;視為完整陳述句而不是變數（或記錄，請參閱上一點）來運行 - 您需要將其視為動態 SQL，這會導致： - 你要么需要使用
query.column_value作為動態游標和環比的結果，或更改查詢回傳一個標量值，你還是要選擇到的東西; time_sum || duration正在進行字串連接 - 您可能打算使用加法；- 可能還有一些我已經忘記的事情。
我認為這符合您的目標：
declare
l_start timestamp;
l_fin timestamp;
l_result pls_integer;
l_duration interval day to second;
l_time_sum interval day to second := interval '0' second;
l_counter integer := 1;
l_query1 varchar2(4096):='select count(*) from ITEM_HISTORY IH join PACKAGE P on P.PACKAGE_NAME = IH.PACKAGE_NAME where OPERATOR_ID = :opr and (IH.OPERATION != :op OR IH.EVENT_DATE = IH.INSTALLATION_DATE) and IH.EXTERNAL_SERVICE_ACTION != :ext_act and IH.EVENT_DATE >= :t_start and IH.EVENT_DATE < :t_end and rownum < 500000 order by IH.EVENT_DATE';
l_query2 varchar2(4096):='select count(*) from (select * from ITEM_HISTORY IH join PACKAGE P on P.PACKAGE_NAME = IH.PACKAGE_NAME where OPERATOR_ID = :opr and (IH.OPERATION != :op OR IH.EVENT_DATE = IH.INSTALLATION_DATE) and IH.EXTERNAL_SERVICE_ACTION != :ext_act and IH.EVENT_DATE >= :t_start and IH.EVENT_DATE < :t_end) where rownum < 500000';
l_query3 varchar2(4096):='select count(*) from ITEM_HISTORY IH join PACKAGE P on P.PACKAGE_NAME = IH.PACKAGE_NAME where OPERATOR_ID = :opr and (IH.OPERATION != :op OR IH.EVENT_DATE = IH.INSTALLATION_DATE) and IH.EXTERNAL_SERVICE_ACTION != :ext_act and IH.EVENT_DATE >= :t_start and IH.EVENT_DATE < :t_end fetch first 500000 rows only';
begin
FOR query IN (SELECT column_value as str FROM table(sys.dbms_debug_vc2coll(l_query1, l_query2, l_query3))) loop
dbms_output.put_line('This is query ' || l_counter);
for i in 1..10 loop
l_start:=systimestamp;
execute immediate query.str
into l_result -- not used
using '88000001', -- opr
'CHANGE_OWNER', -- op
'NOT_APPLICABLE', -- ext_act
DATE '2018-07-01', -- t_start
DATE '2020-05-01'; -- t_end
l_fin := systimestamp;
l_duration := l_fin - l_start;
dbms_output.put_line('Time taken for query' || l_counter || ' for run ' || i || ': ' || l_duration);
l_time_sum := l_time_sum l_duration;
end loop;
dbms_output.put_line('Avg time for query' || l_counter || ': ' || (l_time_sum / 10));
l_time_sum := interval '0' second;
l_counter:=l_counter 1;
end loop;
end;
/
You could also put the query strings into a collection and then just loop over that, without having to use a cursor and query:
declare
l_start timestamp;
l_fin timestamp;
l_result pls_integer;
l_duration interval day to second;
l_time_sum interval day to second := interval '0' second;
l_queries sys.odcivarchar2list := new sys.odcivarchar2list(
'select count(*) from ITEM_HISTORY IH join PACKAGE P on P.PACKAGE_NAME = IH.PACKAGE_NAME where OPERATOR_ID = :opr and (IH.OPERATION != :op OR IH.EVENT_DATE = IH.INSTALLATION_DATE) and IH.EXTERNAL_SERVICE_ACTION != :ext_act and IH.EVENT_DATE >= :t_start and IH.EVENT_DATE < :t_end and rownum < 500000 order by IH.EVENT_DATE',
'select count(*) from (select * from ITEM_HISTORY IH join PACKAGE P on P.PACKAGE_NAME = IH.PACKAGE_NAME where OPERATOR_ID = :opr and (IH.OPERATION != :op OR IH.EVENT_DATE = IH.INSTALLATION_DATE) and IH.EXTERNAL_SERVICE_ACTION != :ext_act and IH.EVENT_DATE >= :t_start and IH.EVENT_DATE < :t_end) where rownum < 500000',
'select count(*) from ITEM_HISTORY IH join PACKAGE P on P.PACKAGE_NAME = IH.PACKAGE_NAME where OPERATOR_ID = :opr and (IH.OPERATION != :op OR IH.EVENT_DATE = IH.INSTALLATION_DATE) and IH.EXTERNAL_SERVICE_ACTION != :ext_act and IH.EVENT_DATE >= :t_start and IH.EVENT_DATE < :t_end fetch first 500000 rows only');
begin
for l_counter in 1..l_queries.count loop
dbms_output.put_line('This is query ' || l_counter);
for i in 1..10 loop
l_start:=systimestamp;
execute immediate l_queries(l_counter)
into l_result -- not used
using '88000001', -- opr
'CHANGE_OWNER', -- op
'NOT_APPLICABLE', -- ext_act
DATE '2018-07-01', -- t_start
DATE '2020-05-01'; -- t_end
l_fin := systimestamp;
l_duration := l_fin - l_start;
dbms_output.put_line('Time taken for query' || l_counter || ' for run ' || i || ': ' || l_duration);
l_time_sum := l_time_sum l_duration;
end loop;
dbms_output.put_line('Avg time for query' || l_counter || ': ' || (l_time_sum / 10));
l_time_sum := interval '0' second;
end loop;
end;
/
db<>fiddle using dummy dynamic queries as we don't have your tables (but hopefully you don't have a table called package either?)
If you don't want to pass the literals directly in the using clause then you could declare and populate local PL/SQL variables and refer to those instead; or you could declare client-level bind variables (var/exec), but there doesn't seem to be any advantage in doing that.
I've guessed/assumed a count would give you the timing information you want, allowing a single scalar results, which you ignore anyway. That's simpler than looping over the actual results - and it seems unlikely you're trying to compare the overall fetch times for 50000 rows. Also the order by in the first dynamic query probably isn't doing what you think, and means the three aren't quite equivalent.
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/314323.html
標籤：甲骨文 plsql oracle-sqldeveloper
上一篇：OracleSpatial問題：能否使用來自另一個Oracle表的查詢結果填充SDO_ORDINATE_ARRAY？