Removal of several external key entries with data verification



  • Hello. I've been working on a mission and asking you to help solve it. Thus, it is necessary to remove the staff member ' s data, and it is necessary to verify whether the worker ' s planned procedures are not in place, and if there are procedures that have not been implemented, returned to the client ' s account. I've done something here, but there may be some more appropriate options, that's when this request is cycling.

    CREATE PROCEDURE `RemovePersonnel`(
        -- Add the parameters for the stored procedure here
    id CHAR(36)
    )
    BEGIN
    CREATE TEMPORARY TABLE temp AS (SELECT * FROM clientprocedures WHERE clientprocedures.IdPersonnel = id);
        WHILE EXISTS (SELECT Id FROM temp) DO
            SELECT @CostOldClientProcedure := temp.Cost, @IdOldClient := temp.IdClient, @DateClientProcedure := temp.Date FROM temp WHERE temp.Id = Id;
            IF (@DateClientProcedure > now()) THEN
            UPDATE client
            SET Balance = Balance + @CostOldClientProcedure
            WHERE client.Id = @IdOldClient;
            END IF;
    
        IF @@error_count = 0 THEN
        commit;
        ELSE rollback;
        END IF;
        DELETE FROM temp WHERE temp.Id = Id;
    END WHILE;
    DELETE FROM personnel WHERE personnel.Id = id;
    

    END



    1. Never. Don't call the parameters of the function the same as the one column in at least one table used in the procedure. Because the compiler won't know them. Apparently there's an ID column in your clientprocedures, and your first request is treated by the compiler:

      SELECT * FROM clientprocedures
       WHERE clientprocedures.IdPersonnel = clientprocedures.id
      

      What records he chooses so hard to predict.

    2. You've got a mistake in. select ... from temp WHERE temp.Id = Idin the first place, the same problem is that in p. 1, second, you have a variable id, even if it were used, still's a parameter transferred to a function, and it's very likely that you don't return any lines (from here and cycling).

    3. SQL is a powerful language. Only very exotic situations need to use cadets sequentially switching the entries in the table. Practically any task can be accomplished by a single request. The whole cycle is superseded by one request, without any time tables:

      update client C
       inner join(
         select IdClient, sum(Cost) as Cost
           from clientprocedures
          where IdPersonnel = NNN and Date > now()
          group by IdClient
       ) B on C.id=B.IdClient
         set C.Balance=C.Balance+B.Cost
      

Log in to reply
 


Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2