usp_updaterole.sql

Beginning VB.NET DatabasesAll_Code.rar

CREATE OR REPLACE PROCEDURE usp_UpdateRole
(
   inRoleID            CHAR,
   inRoleName          VARCHAR2,
   inRoleDescription   CLOB,
   inRanking           NUMBER
)
AS

-- Declare local variables
varID CHAR(36);

BEGIN
   BEGIN
      -- See if the ranking exists
      SELECT RoleID INTO varID
      FROM Roles
      WHERE Ranking = inRanking;
      -- The ranking exists, now verify it doesn't belong to the 
      -- role you are updating
      IF inRoleID <> varID THEN
         RAISE_APPLICATION_ERROR (-20999,
         'Ranking already exists and cannot be duplicated.');
         RETURN;
      END IF;
      EXCEPTION
         WHEN NO_DATA_FOUND THEN
         -- Handle the error but perform no processing
         NULL;
   END;
   BEGIN
      -- Either the ranking does not exist or it belongs 
      -- to the role being updated
      UPDATE Roles
         Set RoleName = inRoleName, 
         RoleDescription = inRoleDescription, 
         Ranking = inRanking, 
         LastUpdateDate = SYSDATE
         WHERE RoleID = inRoleID;
      EXCEPTION
         WHEN OTHERS THEN
         RAISE_APPLICATION_ERROR( -20999,'Update role failed.');
         RETURN;
   END;
END;