üÔÏÔ ×ÙÐÕÓË ÐÏÓ×ÑÝÅÎ ÏÂÓÕÖÄÅÎÉÀ ÐÒÏÉÚ×ÏÄÉÔÅÌØÎÏÓÔÉ ÄÏÓÔÁÔÏÞÎÏ ÓÌÏÖÎÏÇÏ ÏÐÅÒÁÔÏÒÁ UPDATE Ó ×ÌÏÖÅÎÎÙÍÉ. ðÏ ÍÏÔÉ×ÁÍ ÏÞÅÒÅÄÎÏÇÏ ÏÔ×ÅÔÁ ôÏÍÁ ëÁÊÔÁ ÎÁ ×ÏÐÒÏÓ, ÚÁÄÁÎÎÙÊ × ÉÀÎÅ 2003 ÇÏÄÁ.
ëÓÔÁÔÉ, ×ÙÐÕÓË ÜÔÏÔ, × ËÁËÏÍ-ÔÏ ÓÍÙÓÌÅ, ÀÂÉÌÅÊÎÙÊ. ðÒÏÛÅÌ ÇÏÄ Ó ÍÏÍÅÎÔÁ ×ÙÈÏÄÁ ÐÅÒ×ÏÇÏ ×ÙÐÕÓËÁ ÒÁÓÓÙÌËÉ. üÔÏ ÂÙÌÏ 6 ÉÀÎÑ 2002 ÇÏÄÁ. ó ÕÄÏ×ÏÌØÓÔ×ÉÅÍ ÐÒÉÍÕ ÐÏÚÄÒÁ×ÌÅÎÉÑ É ÐÏÖÅÌÁÎÉÑ ÐÏ ÜÔÏÍÕ ÐÏ×ÏÄÕ ;)
ðÒÉ×ÅÔ, ôÏÍ!
ñ ÉÓÐÏÌØÚÕÀ ÓÌÅÄÕÀÝÉÊ ÏÐÅÒÁÔÏÒ update ÄÌÑ ÔÁÂÌÉÃÙ t1:
update t1
set (amount, enter_date) =
(select amount, trunc(enter_date)
from t2
where t2.id = t1.id
and t2.code in (select code from t3 where t3.sign = 1)
and t2.enter_date = (select max(enter_date) from t2
where t2.id = t1.id
and t2.code in (select code from t3
where t3.sign = 1)));
÷ ÔÁÂÌÉÃÅ t1 - ÏËÏÌÏ 10000 ÓÔÒÏË, Á × ÔÁÂÌÉÃÅ t2 - ÏËÏÌÏ 2 ÍÉÌÌÉÏÎÏ×. ñ ÐÙÔÁÀÓØ ÐÏÌÕÞÉÔØ × ÔÁÂÌÉÃÅ t1 ÉÎÆÏÒÍÁÃÉÀ Ï ÓÕÍÍÅ É ÄÁÔÅ ÐÏÓÌÅÄÎÅÊ ÂÁÎËÏ×ÓËÏÊ ÔÒÁÎÚÁËÃÉÉ ÄÌÑ ×ÓÅÈ ÉÄÅÎÔÉÆÉËÁÔÏÒÏ× ÉÚ ÔÁÂÌÉÃÙ t2 (ÔÁÂÌÉÃÙ ÔÒÁÎÚÁËÃÉÊ). ôÁÂÌÉÃÁ t3 (ËÏÄ ÔÒÁÎÚÁËÃÉÉ) ÏÐÒÅÄÅÌÑÅÔ, ËÁËÉÅ ÔÒÁÎÚÁËÃÉÉ ÓÌÅÄÕÅÔ ÕÞÉÔÙ×ÁÔØ.
üÔÏÔ ÏÐÅÒÁÔÏÒ update ×ÙÐÏÌÎÑÅÔÓÑ ÄÏÓÔÁÔÏÞÎÏ ÄÏÌÇÏ. þÔÏ ÂÙ ÔÙ ÐÏÒÅËÏÍÅÎÄÏ×ÁÌ ÄÌÑ ÐÏ×ÙÛÅÎÉÑ ÐÒÏÉÚ×ÏÄÉÔÅÌØÎÏÓÔÉ ÜÔÏÇÏ ÏÐÅÒÁÔÏÒÁ update?
îÕ, ÜÔÏÔ ÏÐÅÒÁÔÏÒ update, ÐÒÉ ÎÁÌÉÞÉÉ ÓÏÏÔ×ÅÔÓÔ×ÕÀÝÉÈ ÉÎÄÅËÓÏ× É ÉÓÐÏÌØÚÏ×ÁÎÉÉ ÓÔÏÉÍÏÓÔÎÏÇÏ ÏÐÔÉÍÉÚÁÔÏÒÁ, ÄÏÌÖÅÎ ÒÁÂÏÔÁÔØ "ÓÒÁ×ÎÉÔÅÌØÎÏ ÂÙÓÔÒÏ". îÁ ÍÏÅÊ ÐÅÒÓÏÎÁÌËÅ ÏÎ ÒÁÂÏÔÁÅÔ ÍÅÎÅÅÅ 2 ÍÉÎÕÔ - ÒÁÚ×Å ÜÔÏ ÄÏÌÇÏ?
÷ ÌÀÂÏÍ ÓÌÕÞÁÅ, Ñ ÓÙÍÉÔÉÒÏ×ÁÌ ×ÁÛÕ ÓÉÔÕÁÃÉÀ ÔÁË:
create table t1 ( id int, amount int, enter_date date );
insert into t1
select rownum, object_id, sysdate
from all_objects big_table
where rownum <= 100000;
alter table t1 add constraint t1_pk primary key(id);
analyze table t1 compute statistics
for table
for all indexes
for all indexed columns;
create table t2 ( id int, amount int, enter_date date, code int );
begin
for i in 1 .. 20
loop
insert /*+ APPEND */ into t2
select id, amount-i, enter_date-i, mod(i,2)
from t1;
commit;
end loop;
end;
/
create index t2_idx on t2(id,enter_date);
analyze table t2 compute statistics
for table
for all indexes
for all indexed columns;
create table t3 ( code int, sign int primary key);
insert into t3
select rownum, rownum
from all_users;
analyze table t3 compute statistics
for table
for all indexes
for all indexed columns;
éÔÁË, Õ ÍÅÎÑ ÔÏÖÅ 100000 ÓÔÒÏË, 2000000 ÓÔÒÏË É ÏÔÄÅÌØÎÁÑ ÔÁÂÌÉÃÁ ÄÌÑ ÐÏÉÓËÁ ËÏÄÁ ÔÒÁÎÚÁËÃÉÉ. ñ ÓÏÚÄÁÌ ÓÌÅÄÕÀÝÅÅ ÐÒÅÄÓÔÁ×ÌÅÎÉÅ:
create or replace view v
as
select rid, amount, trunc(enter_date) enter_date
from ( select t1.rowid rid, t2.amount, t2.enter_date,
max(t2.enter_date) over (partition by t2.id) max_enter_date
from t1, (select amount, enter_date, id
from t2
where code in (select code from t3 where sign = 1)) t2
where t1.id = t2.id (+))
where (enter_date = max_enter_date)
or (enter_date is null and max_enter_date is null)
/
é ÐÏÐÒÏÂÏ×ÁÌ ×ÙÐÏÌÎÉÔØ ÉÚÍÅÎÅÎÉÅ ÐÒÏÃÅÄÕÒÎÏ:
declare
type ridArray is table of rowid index by binary_integer;
type numArray is table of number index by binary_integer;
type datArray is table of date index by binary_integer;
l_rid ridArray;
l_amt numArray;
l_ed datArray;
l_array_size number := 100;
l_cnt number := 0;
cursor c is select rid, amount, enter_date from v;
begin
open c;
loop
fetch c bulk collect
into l_rid, l_amt, l_ed
limit l_array_size;
forall i in 1 .. l_rid.count
update t1 set amount = l_amt(i), enter_date = l_ed(i)
where rowid = l_rid(i);
l_cnt := l_cnt + l_rid.count;
dbms_application_info.set_client_info( 'updated ' || l_cnt || ' rows' );
exit when c%notfound;
end loop;
close c;
end;
/
úÁÔÅÍ ÐÏÐÒÏÂÏ×ÁÌ ÓÄÅÌÁÔØ ÔÏ ÖÅ ÓÁÍÏÅ ÏÄÎÉÍ ÏÐÅÒÁÔÏÒÏÍ update:
update t1
set (amount, enter_date) =
(select amount, trunc(enter_date)
from t2
where t2.id = t1.id
and t2.code in (select code from t3 where t3.sign = 1)
and t2.enter_date = (select max(enter_date) from t2
where t2.id = t1.id
and t2.code in (select code from t3
where t3.sign = 1)));
äÌÑ PL/SQL-ÂÌÏËÁ ÐÏÔÒÅÂÏ×ÁÌÏÓØ:
call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.02 0.03 0 0 1 0 Execute 1 71.28 80.10 15610 106393 101914 1 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 71.30 80.14 15610 106393 101915 1
ÏËÏÌÏ 80 ÓÅËÕÎÄ, Á ÄÌÑ update:
update t1
set (amount, enter_date) =
(select amount, trunc(enter_date)
from t2
where t2.id = t1.id
and t2.code in (select code from t3 where t3.sign = 1)
and t2.enter_date = (select max(enter_date) from t2
where t2.id = t1.id
and t2.code in (select code from t3
where t3.sign = 1)))
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.01 0 0 0 0
Execute 1 62.90 108.38 13665 2728542 204504 100000
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 62.90 108.39 13665 2728542 204504 100000
ÎÅÍÎÏÇÏ ÂÏÌØÛÅ...
ôÁË ÞÔÏ, ÍÏÖÅÔÅ ÐÏÐÒÏÂÏ×ÁÔØ ÐÒÏÃÅÄÕÒÎÙÊ ÐÏÄÈÏÄ, ÎÏ, ÐÒÉ ÎÁÌÉÞÉÉ ÓÏÏÔ×ÅÔÓÔ×ÕÀÝÉÈ ÉÎÄÅËÓÏ×, ÉÚÍÅÎÅÎÉÅ 100000 ÓÔÒÏË ÎÅ ÄÏÌÖÎÏ ×ÙÚÙ×ÁÔØ ÏÓÏÂÙÈ ÐÒÏÂÌÅÍ.
ôÏÍ, ÞÔÏ × ÜÔÏÍ ÐÒÉÍÅÒÅ ÄÅÌÁÅÔ ÅÇÏ ÉÓËÌÀÞÅÎÉÅÍ ÉÚ Ô×ÏÅÇÏ ÐÒÏÓÔÏÇÏ ÐÒÁ×ÉÌÁ: ÏÄÉÎ SQL-ÏÐÅÒÁÔÏÒ ÒÁÂÏÔÁÅÔ ÂÙÓÔÒÅÅ É ÉÓÐÏÌØÚÕÅÔ ÍÅÎØÛÅ ÒÅÓÕÒÓÏ×, ÞÅÍ ÐÒÏÃÅÄÕÒÎÏÅ ÒÅÛÅÎÉÅ?
äÅÌÏ × ÓÕÔÉ ÉÚÍÅÎÅÎÉÑ: × ÎÅÍ ÉÓÐÏÌØÚÕÅÔÓÑ ÍÎÏÇÏ ËÏÒÒÅÌÉÒÏ×ÁÎÎÙÈ ÐÏÄÚÁÐÒÏÓÏ× ÄÌÑ ÐÏÉÓËÁ max enter_date É Ô.Ð. üÔÏ ÕÓÌÏÖÎÑÅÔ ÐÒÉ ÏÐÔÉÍÉÚÁÃÉÉ ×ÙÂÏÒ ÌÀÂÏÇÏ ÄÒÕÇÏÇÏ ÐÌÁÎÁ ËÒÏÍÅ ×ÌÏÖÅÎÎÙÈ ÃÉËÌÏ× (ÉÍÅÎÎÏ ÔÁË ×ÙÐÏÌÎÑÀÔÓÑ ËÏÒÒÅÌÉÒÏ×ÁÎÎÙÅ ÐÏÄÚÁÐÒÏÓÙ).
ñ ÂÙ ÈÏÔÅÌ ÓÄÅÌÁÔØ ÓÌÅÄÕÀÝÅÅ:
UPDATE(
select *
from ( select t1.amount t1_amt, t1.enter_date t1_ed,
t2.amount t2_amt, t2.enter_date t2_ed,
max(t2.enter_date) over (partition by t2.id) max_enter_date
from t1, (select amount, enter_date, id
from t2
where code in (select code from t3 where sign = 1)) t2
where t1.id = t2.id (+))
where (enter_date = max_enter_date)
or (enter_date is null and max_enter_date is null)
)
set t1_amt = t2_amt, t1_ed = t2_ed;
îÏ, ÐÏÓËÏÌØËÕ ÎÅÔ ÚÁÝÉÔÙ ËÌÀÞÏÍ (key preservation) É ÉÓÐÏÌØÚÕÀÔÓÑ ÁÎÁÌÉÔÉÞÅÓËÉÅ ÆÕÎËÃÉÉ (ÞÔÏ ÐÏÄÒÁÚÕÍÅ×ÁÅÔ "ÕÐÏÒÑÄÏÞÉ×ÁÎÉÅ" ÐÏÄÓÔÁ×ÌÑÅÍÏÇÏ ÐÒÅÄÓÔÁ×ÌÅÎÉÑ), ÔÁË ÄÅÌÁÔØ ÎÅÌØÚÑ. ñ ÈÏÔÅÌ ÂÙ ×ÙÂÉÒÁÔØ ÓÔÒÏËÉ Ó ÐÏÍÏÝØÀ ÉÍÅÎÎÏ ÔÁËÏÇÏ ÚÁÐÒÏÓÁ, ÐÏÓËÏÌØËÕ ÏÎ ×ÙÐÏÌÎÑÅÔ ÍÎÏÖÅÓÔ×ÅÎÎÙÅ ÏÐÅÒÁÃÉÉ (ÞÔÏ ÕÓËÏÒÑÅÔ ÒÁÂÏÔÕ ÐÒÉ ÂÏÌØÛÏÍ ËÏÌÉÞÅÓÔ×Å ÓÔÒÏË), Á ÎÅ 100000 ÓÌÏÖÅÎÎÙÈ ÃÉËÌÏ×...
éÔÁË, ÞÔÏÂÙ "ÉÚÍÅÎÉÔØ ÒÅÚÕÌØÔÁÔÙ ÜÔÏÇÏ ÚÁÐÒÏÓÁ", ÍÎÅ, Ë ÓÏÖÁÌÅÎÉÀ (× 8i), ÐÒÉÈÏÄÉÔÓÑ ÉÓÐÏÌØÚÏ×ÁÔØ ÚÁÐÒÏÓ + update. ÷ÙÐÏÌÎÑÑ ÍÎÏÖÅÓÔ×ÅÎÎÙÅ ÏÐÅÒÁÃÉÉ...
÷ ×ÅÒÓÉÉ 9i ÐÏÑ×ÉÌÓÑ ÏÐÅÒÁÔÏÒ MERGE:
merge into t1
using (
select rid, amount, trunc(enter_date) enter_date
from ( select t1.rowid rid, t2.amount, t2.enter_date,
max(t2.enter_date) over (partition by t2.id) max_enter_date
from t1, (select amount, enter_date, id
from t2
where code in (select code from t3 where sign = 1)) t2
where t1.id = t2.id (+))
where (enter_date = max_enter_date)
or (enter_date is null and max_enter_date is null)
) t2
on ( t1.rowid = t2.rid )
when matched then
update set t1.amount = t2.amount, t1.enter_date = t2.enter_date
when not matched then
insert (id) values ( null )
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.01 0 0 0 0
Execute 2 43.03 53.45 16486 7558 102809 100000
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 43.05 53.46 16486 7558 102809 100000
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 137
Rows Row Source Operation
------- ---------------------------------------------------
2 MERGE (cr=7558 r=16486 w=10807 time=53444549 us)
100000 VIEW (cr=7531 r=16477 w=10807 time=49308344 us)
100000 HASH JOIN OUTER (cr=7531 r=16477 w=10807 time=48646484 us)
100000 VIEW (cr=7187 r=16139 w=10807 time=46776991 us)
1000000 WINDOW SORT (cr=7187 r=16139 w=10807 time=45026596 us)
1000000 HASH JOIN OUTER (cr=7187 r=5331 w=0 time=27551523 us)
100000 INDEX FAST FULL SCAN OBJ#(37458) (cr=215 r=210 w=0 time=235332 us)(object id 37458)
1000000 VIEW (cr=6972 r=5121 w=0 time=18959603 us)
1000000 HASH JOIN (cr=6972 r=5121 w=0 time=15955318 us)
1 TABLE ACCESS BY INDEX ROWID OBJ#(37461) (cr=2 r=0 w=0 time=97 us)
1 INDEX UNIQUE SCAN OBJ#(37462) (cr=1 r=0 w=0 time=58 us)(object id 37462)
2000000 TABLE ACCESS FULL OBJ#(37459) (cr=6970 r=5121 w=0 time=4275638 us)
100000 TABLE ACCESS FULL OBJ#(37457) (cr=344 r=338 w=0 time=376606 us)
ïÎ ÄÅÌÁÅÔ ÔÏ ÖÅ ÓÁÍÏÅ, ÞÔÏ É ÍÏÊ PL/SQL-ËÏÄ, ÎÏ ÎÅÍÎÏÇÏ ÜÆÆÅËÔÉ×ÎÅÅ.
ñ ÐÙÔÁÌÓÑ ÒÁÚÏÂÒÁÔØÓÑ, ÐÏÞÅÍÕ ÍÏÊ ÏÐÅÒÁÔÏÒ update ×ÙÐÏÌÎÑÅÔÓÑ ÞÁÓÁÍÉ, Á ÅÓÌÉ ÒÁÚÄÅÌÉÔØ ÅÇÏ ÎÁ Ä×Å ÞÁÓÔÉ - ÜÔÉ ÞÁÓÔÉ ×ÙÐÏÌÎÑÀÔÓÑ ÎÅÓËÏÌØËÏ ÓÅËÕÎÄ. ÷ÏÚÍÏÖÎÏ, ÐÒÏÂÌÅÍÁ Ó×ÑÚÁÎÁ Ó ÓÁÍÏÓÏÅÄÉÎÅÎÉÅÍ, ÎÏ Ñ ÎÅ Õ×ÅÒÅÎ.
íÏÑ ÐÒÏÂÌÅÍÁ × ÔÏÍ, ÞÔÏ ÐÅÒ×ÏÎÁÞÁÌØÎÏ ÎÅ ÂÙÌÁ ÕÞÔÅÎÁ ×ÏÚÍÏÖÎÏÓÔØ ÒÏÖÄÅÎÉÑ ÏÄÎÏÐÏÌÙÈ Ä×ÏÊÎÑÛÅË. ÷ ÔÁÂÌÉÃÅ ÅÓÔØ ÓÔÏÌÂÃÙ CLIID, ÐÏÌ É ÄÁÔÁ ÒÏÖÄÅÎÉÑ. ñ ÐÒÏÓÔÏ ÈÏÞÕ ÐÏÍÅÔÉÔØ ×ÏÚÍÏÖÎÙÅ ÄÕÂÌÉÒÕÀÝÉÅÓÑ ÓÔÒÏËÉ (ÞÁÓÔØ ÉÚ ÎÉÈ ÍÏÖÅÔ ÂÙÔØ Ó×ÑÚÁÎÁ Ó Ä×ÏÊÎÑÛËÁÍÉ, Á ÞÁÓÔØ - Ó ÏÐÅÞÁÔËÁÍÉ ÐÒÉ ÎÁÂÏÒÅ) ÐÏ ËÁÖÄÏÍÕ CLIID. ÷ ÔÁÂÌÉÃÅ - ÏËÏÌÏ 150000 ÚÁÐÉÓÅÊ, ÉÚ ËÏÔÏÒÙÈ ÏËÏÌÏ 10000 ÐÏÔÅÎÃÉÁÌØÎÏ ÍÏÇÕÔ ÂÙÔØ ÄÕÂÌÉÒÕÀÝÉÍÉÓÑ.
ñ ÎÁÐÉÓÁÌ ÓÌÅÄÕÀÝÉÊ ÏÐÅÒÁÔÏÒ:
update testchild tcx set pd = (select "rank" from (select cliid,id,fname, rank() over (partition by cliid order by fname) "rank" from testchild tc2 where exists (select * from testchild tc3 where tc3.cliid=tc2.cliid and cliid > 1000000000 group by cliid having count(*) > 1)) x where x.id = tcx.id) Plan Table ------------------------------------------------------------------------------------------ | Operation | Name | Rows | Bytes| Cost | Pstart| Pstop | ------------------------------------------------------------------------------------------ | UPDATE STATEMENT | | 152K| 745K| 284 | | | | UPDATE |TESTCHILD | | | | | | | TABLE ACCESS FULL |TESTCHILD | 152K| 745K| 284 | | | | VIEW | | 7K| 193K| 319 | | | | WINDOW SORT | | 7K| 149K| 319 | | | | FILTER | | | | | | | | TABLE ACCESS FULL |TESTCHILD | 7K| 149K| 284 | | | | FILTER | | | | | | | | SORT GROUP BY NOSORT| | 1 | 9 | 1 | | | | FILTER | | | | | | | | INDEX RANGE SCAN |TC_CLIID | 1 | 9 | 1 | | | ------------------------------------------------------------------------------------------
óÁÍ ÚÁÐÒÏÓ ×ÙÐÏÌÎÑÅÔÓÑ ÏËÏÌÏ 6 ÓÅËÕÎÄ. åÓÌÉ Ñ ×ÙÐÏÌÎÑÀ CREATE TABLE AS SELECT, Á ÚÁÔÅÍ ÉÚÍÅÎÑÀ ÔÁÂÌÉÃÕ testchild ÎÁ ÏÓÎÏ×Å ÜÔÏÊ "×ÒÅÍÅÎÎÏÊ" ÔÁÂÌÉÃÙ, ÔÒÅÂÕÅÔÓÑ ÍÅÎÅÅ ÍÉÎÕÔÙ. äÏÂÁ×ÌÅÎÉÅ ÉÎÄÅËÓÁ ÐÏ ID ÄÌÑ ×ÒÅÍÅÎÎÏÊ ÔÁÂÌÉÃÙ ÏÓÏÂÏÊ ÒÏÌÉ ÎÅ ÉÇÒÁÅÔ. á ×ÏÔ ÅÓÌÉ ×ÙÐÏÌÎÑÔØ ÐÒÅÄÓÔÁ×ÌÅÎÎÙÊ ×ÙÛÅ ÐÏÌÎÙÊ update... ïÎ ÎÁÞÁÌÓÑ × 11 ÕÔÒÁ ×ÞÅÒÁ, É ÓÅÇÏÄÎÑ × 9 ÕÔÒÁ ÅÝÅ ×ÙÐÏÌÎÑÅÔÓÑ. ëÏÌÉÞÅÓÔ×Ï consistent gets ÉÚÍÅÒÑÅÔÓÑ ÍÉÌÌÉÁÒÄÁÍÉ. ÷ÓÅ ÜÔÏ ÒÁÂÏÔÁÅÔ × ×ÅÒÓÉÉ 9i ÎÁ ÐÅÒÓÏÎÁÌËÅ.
þÔÏ ÐÒÏÉÓÈÏÄÉÔ, ÎÅ ÐÏÄÓËÁÖÅÛØ?
ðÒÏÂÌÅÍÁ ÔÁ ÖÅ.
üÔÏÔ 6-ÓÅËÕÎÄÎÙÊ ÚÁÐÒÏÓ ×ÙÐÏÌÎÑÅÔÓÑ 152k ÒÁÚ (ÐÒÅÄÓÔÁ×ÌÅÎÎÁÑ ÓÔÁÔÉÓÔÉËÁ Ó×ÉÄÅÔÅÌØÓ×ÕÅÔ, ÞÔÏ ÉÚÍÅÎÑÔØ × ÔÁÂÌÉÃÅ ÐÒÉÄÅÔÓÑ 152k ÓÔÒÏË) ðÒÅÄÓÔÁ×ÌÅÎÎÙÊ ÒÁÎÅÅ ÐÒÉÅÍ ÐÏÚ×ÏÌÉÔ ×ÙÐÏÌÎÑÔØ 6-ÓÅËÕÎÄÎÙÊ ÚÁÐÒÏÓ ÂÌÏËÏÍ (IN BULK), Á ÎÅ ÐÏÓÔÒÏÞÎÏ, É ÉÚÍÅÎÑÔØ ÅÇÏ ÒÅÚÕÌØÔÁÔÙ.
ñ ÐÏÐÒÏÂÏ×ÁÌ ÐÒÅÄÌÏÖÅÎÎÏÅ ÐÒÏÃÅÄÕÒÎÏÅ ÒÅÛÅÎÉÅ, É ÏÎÏ ÐÏÔÒÅÂÏ×ÁÌÏ ÏËÏÌÏ 60 ÓÅËÕÎÄ. áÎÁÌÉÔÉÞÅÓËÉÅ ÆÕÎËÃÉÉ - ÜÔÏ ÚÁÍÅÞÁÔÅÌØÎÏ.
õ ÍÅÎÑ, ÏÄÎÁËÏ, ÏÓÔÁÌÁÓØ ÏÄÎÁ ÐÒÏÂÌÅÍÁ. ÷ÍÅÓÔÏ ÓÏÚÄÁÎÉÑ ÐÒÅÄÓÔÁ×ÌÅÎÉÑ Ñ ÐÏÐÙÔÁÌÓÑ ÓÏÚÄÁÔØ ËÕÒÓÏÒ:
CURSOR last_payment_cur IS
select rid, amount, trunc(enter_date) enter_date
from ( select t1.rowid rid, t2.amount, t2.enter_date,
max(t2.enter_date) over (partition by t2.id) max_enter_date
from t1, (select amount, enter_date, id
from t2
where code in (select code from t3 where sign = 1)) t2
where t1.id = t2.id (+))
where (enter_date = max_enter_date)
or (enter_date is null and max_enter_date is null);
÷ ÒÅÚÕÌØÔÁÔÅ ÂÙÌÏ ÐÏÌÕÞÅÎÏ ÓÌÅÄÕÀÝÅÅ ÓÏÏÂÝÅÎÉÅ ÏÂ ÏÛÉÂËÅ:
LINE/COL ERROR
51/32 PLS-00103: Encountered the symbol "(" when expecting one of the
following: , from
îÏ ÜÔÏÔ ÖÅ ÚÁÐÒÏÓ × ÏËÎÅ SQL*Plus ÐÒÅËÒÁÓÎÏ ÒÁÂÏÔÁÅÔ. ðÏÞÅÍÕ ÎÅÌØÚÑ ÓÏÚÄÁÔØ ËÕÒÓÏÒ?
ñ ÓÐÅÃÉÁÌØÎÏ ÓÏÚÄÁÌ ÐÒÅÄÓÔÁ×ÌÅÎÉÅ, Õ×ÉÄÅ× × ×ÏÐÒÏÓÅ ÕÐÏÍÉÎÁÎÉÅ ×ÅÒÓÉÉ 8.1.7
óÍ. ÇÌÁ×Õ × ÍÏÅÊ ËÎÉÇÅ, ÐÏÓ×ÑÝÅÎÎÕÀ ÁÎÁÌÉÔÉÞÅÓËÉÍ ÆÕÎËÃÉÑÍ. óÔÒ. 586
ïÒÉÇÉÎÁÌ ÏÂÓÕÖÄÅÎÉÑ ÜÔÏÇÏ ×ÏÐÒÏÓÁ ÍÏÖÎÏ ÎÁÊÔÉ ÚÄÅÓØ.
Copyright © 2003 Oracle Corporation
ëÏÅ-ÞÔÏ Ï ÉÎÔÅÒÐÒÅÔÁÃÉÉ ÒÅÚÕÌØÔÁÔÏ× ÔÒÁÓÓÉÒÏ×ËÉ É ÐÒÉÍÅÎÅÎÉÑ ÕÔÉÌÉÔÙ TKPROF. óÌÅÄÉÔÅ ÚÁ ÎÏ×ÏÓÔÑÍÉ ÎÁ ÓÁÊÔÅ ÐÒÏÅËÔÁ Open Oracle.
ó ÎÁÉÌÕÞÛÉÍÉ ÐÏÖÅÌÁÎÉÑÍÉ,
÷.ë.
|
|