关于座位排序问题!在线等....（.net）

0小宝贝0

一开始写错了,只有status1=N的seatno不变

psycholmu

seatno是否允许重复？

0小宝贝0

seatno(座号)是唯一的

yanan13115

这个应该用Procedure写！
----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;

yanan13115

当然最好把上面的procedure改成trigger，那样，再你删除资料后，会自动帮你更新相关值，你用select * from table_name order by seatno  就可以了！

记得要建立index，要不资料量大并且数据读写频繁的时候，这个方法有点慢。

psycholmu

Mark

yanan13115

刚才的Procedure有问题，现已做如下改动！
----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;

yanan13115

少了一个End Loop;哈哈，请加在倒数第二行处！

yanan13115

上面是参照高校里材料学里的位错的空穴移动的方式写成！呵呵