最后输出结果为(status1=N的记录不变)
empno pay_mon busno seatno status1 status2
1 200 8 1 Y OLD
2 200 8 2 Y OLD
3 160 8 3 Y OLD
4 160 8 4 Y OLD
5 100 8 5 Y OLD
6 100 8 6 N OLD
7 100 8 7 Y NEW
8 60 8 8 Y NEW
empno pay_mon busno seatno status1 status2
1 200 8 1 Y OLD
2 200 8 2 Y OLD
3 160 8 3 Y OLD
4 160 8 4 Y OLD
5 100 8 5 Y OLD
6 100 8 6 N OLD
7 100 8 7 Y NEW
8 60 8 8 Y NEW
empno(工号)pay_mon(金额)busno(车号) seatno(座号) status1 status2
1 200 8 1 Y OLD
2 200 8 2 Y OLD
3 200 8 3 Y OLD
4 160 8 4 Y OLD
5 160 8 5 Y OLD
6 100 8 6 N OLD
7 100 8 7 Y OLD
8 100 8 8 Y NEW
9 60 8 9 Y NEW
把empno=3 删除后,得到的表empno pay_mon busno seatno status1 status2
1 200 8 1 Y OLD
2 200 8 2 Y OLD
4 160 8 3 Y OLD
5 160 8 4 Y OLD
7 100 8 5 Y OLD
6 100 8 6 N OLD
8 100 8 7 Y NEW
9 60 8 8 Y NEW
我先说说
1 可以读取所有数据到DS中 然后将所有要处理的数据(empno=3)的seatno号都-1 更新DS至数据库
2 直接拼sql 如果数据量不大 复杂嵌套的sql语句执行效率也不会很低
empno>3
呵呵
----For Oracle!
Create or Replace Procedure ListSequence(V_EMPNO IN NUMBER) as
V_SQL VARCHAR2(200);
V_COUNT NUMBER:=1;
V_DELETE_SEATNO NUMBER:=0;
TYPE REFCUR IS REF CURSOR;
C_T1 REFCUR;
CURSOR C1 IS
SELECT * FROM TABLE_NAME;
C1_DATA C1%ROWTYPE;BEGIN
V_SQL:='SELECT SEATNO FROM TABLE_NAME WHERE EMPNO='||V_EMPNO;
EXECUTE IMMEDIATE V_SQL INTO V_DELETE_SEATNO;EXECUTE IMMEDIATE 'DELETE FROM TABLE_NAME WHERE EMPNO='||V_EMPNO;
COMMIT;OPEN C1;
LOOP
FETCH C1 INTO C1_DATA;
EXIT WHEN C1%NOTFOUND;
IF C1_DATA.SEATNO > V_DELETE_SEATNO THEN
IF C1_DATA.STATUS1='N' THEN
V_COUNT:=V_COUNT+1;
ELSE
V_SQL:='UPDATE TABLE_NAME SET SEATNO=SEATNO-'||V_COUNT||' WHERE SEATNO='||C1_DATA.SEATNO;
EXECUTE IMMEDIATE V_SQL;
COMMIT;
END IF;
END IF;
END;
----For Oracle!
Create or Replace Procedure ListSequence(V_EMPNO IN NUMBER) as
V_SQL VARCHAR2(200);
V_DELETE_SEATNO NUMBER:=0;
V_TEMP_INDEX NUMBER:=0;
TYPE REFCUR IS REF CURSOR;
C_T1 REFCUR;
CURSOR C1 IS
SELECT * FROM TABLE_NAME;
C1_DATA C1%ROWTYPE;BEGIN
V_SQL:='SELECT SEATNO FROM TABLE_NAME WHERE EMPNO='||V_EMPNO;
EXECUTE IMMEDIATE V_SQL INTO V_DELETE_SEATNO;EXECUTE IMMEDIATE 'DELETE FROM TABLE_NAME WHERE EMPNO='||V_EMPNO;
COMMIT;OPEN C1;
LOOP
FETCH C1 INTO C1_DATA;
EXIT WHEN C1%NOTFOUND;
IF C1_DATA.SEATNO > V_DELETE_SEATNO THEN
IF C1_DATA.STATUS1<>'N' THEN
V_TEMP_INDEX:=C1_DATA.SEATNO;
V_SQL:='UPDATE TABLE_NAME SET SEATNO='||V_DELETE_SEATNO||' WHERE SEATNO='||C1_DATA.SEATNO;
EXECUTE IMMEDIATE V_SQL;
COMMIT;
V_DELETE_SEATNO:=V_TEMP_INDEX;
END IF;
END IF;
END;