usp_insertrole.sql

数据库学习的绝好例子简单的数据库经典入门

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

RankAvailable number(3,0);

BEGIN
   BEGIN
      SELECT Ranking INTO RankAvailable
      FROM Roles
      WHERE Ranking = inRanking;
      RAISE_APPLICATION_ERROR (-20999,
         'Ranking already exists and cannot be duplicated');
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
      BEGIN
         INSERT INTO Roles
            (RoleID, RoleName, RoleDescription, Ranking, LastUpdateDate)
            VALUES(inRoleID, inRoleName, inRoleDescription, inRanking, SYSDATE);
         EXCEPTION
            WHEN OTHERS THEN
            RAISE_APPLICATION_ERROR( -20999,'Insert into Roles failed.');
      END;
   END;
END;