On the seat sorting problem! Online etc ... (.net)

0小宝贝0

Wrong at the beginning, only the seatno of status1 = N is unchanged

psycholmu

Does seatno allow duplicates?

0小宝贝0

seatno is unique

yanan13115

This should be written in 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

Of course, it is best to change the above procedure to trigger. Then, after you delete the data, it will automatically update the relevant values ​​for you. You can use select * from table_name order by seatno!

Remember to create the index. This method is a bit slow when the amount of data is large and the data is read and written frequently.

psycholmu

Mark

yanan13115

There was a problem with Procedure just now, and the following changes have been made!
---- 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

One missing End Loop; haha, please add in the penultimate line!

yanan13115

The above is written with reference to the way in which holes in dislocations in materials science move! Ha ha