JsUT1xib

1create or replace PROCEDURE ADD_PROJTYPE_SP (
2p_project_ID              IN NUMBER,
3p_projType                IN VARCHAR2
4)
5AS
6
7-- Formalities to simplify the raising of an exception
8  ex_error                  EXCEPTION;
9  err_msg_txt               VARCHAR(100) := NULL;
10  PROJECT_TYPE_NAME_COUNT   NUMBER;
11  PROJECT_ID_COUNT          NUMBER;
12  PROJECT_ASSOCIATION_COUNT NUMBER;
13
14BEGIN
15
16    -- Check if the project ID is null
17    IF p_project_ID IS NULL THEN
18        err_msg_txt := 'The project id can not be null';
19        RAISE ex_error;
20    END IF;
21
22    -- Check if the project type name is null
23    IF p_projType IS NULL THEN
24        err_msg_txt := 'The name of the project type cannot be null';
25        RAISE ex_error;
26    END IF;
27
28    -- Check if the project type name is valid or not
29    SELECT COUNT(p_projType) 
30    INTO PROJECT_TYPE_NAME_COUNT
31    FROM I_PROJECT_TYPE
32    WHERE PROJECT_TYPE_NAME 
33    LIKE p_projType;
34
35    IF PROJECT_TYPE_NAME_COUNT < 1 THEN
36        err_msg_txt := 'The project type does not exist, try creating it first';
37        RAISE ex_error;
38    END IF;
39
40    -- Check if the project ID is valid or not
41    SELECT COUNT(PROJECT_ID) 
42    INTO PROJECT_ID_COUNT
43    FROM I_PROJECT
44    WHERE PROJECT_ID = p_project_ID
45    AND ROWNUM = 1;
46
47    IF PROJECT_ID_COUNT < 1 THEN
48        err_msg_txt := 'The project ID does not exist';
49        RAISE ex_error;
50    END IF;
51
52    -- Check if the project ID and Project type have already been associated
53    SELECT COUNT(*) 
54    INTO PROJECT_ASSOCIATION_COUNT
55    FROM I_PROJ_PROJTYPE
56    WHERE PROJECT_ID = p_project_ID  
57    AND PROJECT_TYPE_NAME 
58    LIKE p_projType
59    AND ROWNUM = 1;
60
61    IF PROJECT_ASSOCIATION_COUNT >= 1 THEN
62        err_msg_txt := 'This association already exists';
63        RAISE ex_error;
64    END IF;
65
66    -- Inserts the data into the correct table
67    INSERT INTO I_PROJ_PROJTYPE (PROJECT_ID, PROJECT_TYPE_NAME) 
68    VALUES (p_project_ID , p_projType);
69
70    -- Commits the transaction
71    COMMIT;
72
73    DBMS_OUTPUT.PUT_LINE('The operation was successful, your changes have been commited to the database');
74
75EXCEPTION
76
77    -- Prints the error message when the exception is raised, and rolls back the transaction
78    WHEN ex_error THEN
79        DBMS_OUTPUT.PUT_LINE(err_msg_txt);
80    ROLLBACK;
81
82    WHEN OTHERS THEN
83        DBMS_OUTPUT.PUT_LINE('The error code is: ' || SQLCODE);
84        DBMS_OUTPUT.PUT_LINE('The error msg is:  ' || SQLERRM);
85   ROLLBACK;
86   
87END;