DECLARE
v_servid NUMBER(16);
v_stdno VARCHAR2(30);
BEGIN
FOR i IN (SELECT rownum rn, t.*
FROM tb_server t
WHERE t.seller = 'abd')
LOOP
v_servid := i.servid;
BEGIN
SELECT t1.stdno
INTO v_stdno
FROM tb_digit t1
WHERE t1.servid = i.servid;
IF i.servtype = '0' THEN
UPDATE tb_device t2
SET t2.place = 999
WHERE t2.stdno = i.stdno;
ELSIF i.servtype = '1' THEN
UPDATE tb_device t2
SET t2.place = 111
WHERE t2.stdno = i.stdno;
END IF;
EXCEPTION
WHEN no_data_found THEN
dbms_output.put_line(SQLERRM);
END;
IF MOD(i.rn, 1000) = 0 THEN
COMMIT;
END IF;
END LOOP;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(v_servid || '------' || SQLERRM);
END;
PLSQL 块demo
收藏于 2012-07-25
来自于百度空间