aaa.sql
set serverout on
DECLARE
b_cnt NUMBER DEFAULT 1;
v_cnt NUMBER DEFAULT 0;
supp_cnt NUMBER DEFAULT 0;
tmp_cnt NUMBER;
batch_no_max NUMBER;
batch_no_min NUMBER;
CURSOR c_get_cnt IS
SELECT count(1) from .... where BATCH_NO=b_cnt ;
BEGIN
SELECT COUNT(1)
INTO tmp_cnt
from .....;
supp_cnt:=tmp_cnt;
dbms_output.put_line('test1');
IF(supp_cnt!=0)
THEN
dbms_output.put_line('test2');
SELECT MAX(BATCH_NO)
INTO batch_no_max
FROM .....;
SELECT MIN(BATCH_NO)
INTO batch_no_min
FROM .....;
b_cnt:=batch_no_min;
FOR k IN batch_no_min..batch_no_min
LOOP
OPEN c_get_cnt;
FETCH c_get_cnt INTO v_cnt;
CLOSE c_get_cnt;
UPDATE .....
SET BATCH_NO = b_cnt
WHERE ID in (SELECT SOURCE_ORG_ID from ..... WHERE BATCH_NO=b_cnt);
COMMIT;
b_cnt := b_cnt + 1;
END LOOP;
END IF;
END;
/
EXIT;
~
notes:
1,方法:
set serverout on
。。。
dbms_output.put_line('test1');
。。。
2,run tth sql scripts:
sqlplus username/password@DBname @aaa.sql
set serverout on
DECLARE
b_cnt NUMBER DEFAULT 1;
v_cnt NUMBER DEFAULT 0;
supp_cnt NUMBER DEFAULT 0;
tmp_cnt NUMBER;
batch_no_max NUMBER;
batch_no_min NUMBER;
CURSOR c_get_cnt IS
SELECT count(1) from .... where BATCH_NO=b_cnt ;
BEGIN
SELECT COUNT(1)
INTO tmp_cnt
from .....;
supp_cnt:=tmp_cnt;
dbms_output.put_line('test1');
IF(supp_cnt!=0)
THEN
dbms_output.put_line('test2');
SELECT MAX(BATCH_NO)
INTO batch_no_max
FROM .....;
SELECT MIN(BATCH_NO)
INTO batch_no_min
FROM .....;
b_cnt:=batch_no_min;
FOR k IN batch_no_min..batch_no_min
LOOP
OPEN c_get_cnt;
FETCH c_get_cnt INTO v_cnt;
CLOSE c_get_cnt;
UPDATE .....
SET BATCH_NO = b_cnt
WHERE ID in (SELECT SOURCE_ORG_ID from ..... WHERE BATCH_NO=b_cnt);
COMMIT;
b_cnt := b_cnt + 1;
END LOOP;
END IF;
END;
/
EXIT;
~
notes:
1,方法:
set serverout on
。。。
dbms_output.put_line('test1');
。。。
2,run tth sql scripts:
sqlplus username/password@DBname @aaa.sql