| How to make this query run faster using PL/SQL update and insert [message #132248] |
Thu, 11 August 2005 09:46  |
Rathor
Messages: 5
Registered: August 2005
|
Junior Member |
|
|
Hi,
Any idea on how to make this faster (oracle 7), please!
DECLARE
V_TEMP VARCHAR2(10);
V_TRAP_ERROR EXCEPTION;
CURSOR c1 IS
SELECT a.emp_code
,a.action
,e.seq_no
,e.rowid
FROM code_table a
,emp1_table e
WHERE e.emp_id = '&1' AND
e.emp_code = a.emp_code;
v_c NUMBER(3) := 0;
BEGIN
FOR cur IN c1
LOOP
if cur.action = 'D' THEN
BEGIN
UPDATE emp1_table e
SET e.TOTAL = NULL
,e.emp_grade = 'X'
,e.timestamp = SYSDATE
WHERE e.emp_id = '&1' AND
e.rowid = cur.rowid;
EXCEPTION WHEN OTHERS THEN
ROLLBACK;
RAISE V_TRAP_ERROR;
END;
v_c := v_c + 1;
BEGIN
UPDATE emp2_table e
SET e.tier = 3
,e.timestamp = SYSDATE
WHERE e.emp_id = '&1'
AND e.seq_no = cur.seq_no;
EXCEPTION WHEN OTHERS THEN
ROLLBACK;
RAISE V_TRAP_ERROR;
END;
BEGIN
INSERT INTO hist_emp_table
(emp_id,
emp_total,
emp_seq_no,
emp_tier,
emp_timestamp
)
SELECT e.emp_id,
e.total,
e.seq_no,
e.tier,
sysdate
FROM emp2_table e
WHERE e.emp_id = '&1'
AND e.seq_no = cur.seq_no;
EXCEPTION WHEN OTHERS THEN
ROLLBACK;
RAISE V_TRAP_ERROR;
END;
END IF;
IF v_c = 1000 THEN
COMMIT;
v_c := 0;
END IF;
END LOOP;
COMMIT;
EXCEPTION WHEN V_TRAP_ERROR THEN
ROLLBACK;
END;
/
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
