WW8QgCPv

1create or replace PROCEDURE                 ADD_FOCUSAREA_SP (
2p_project_ID            IN INTEGER,
3p_focusArea             IN VARCHAR
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_FOCUSAREA_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, and should not be null as it is a primary key';
19        RAISE ex_error;
20    END IF;
21
22    -- Check if the focusarea name is null
23    IF p_focusArea IS NULL THEN
24        err_msg_txt := 'The name of the project cannot be null';
25        RAISE ex_error;
26    END IF;
27
28    -- Check if the focus area name is valid or not
29    SELECT COUNT(FOCUS_AREA_NAME) 
30    INTO PROJECT_FOCUSAREA_NAME_COUNT
31    FROM I_FOCUS_AREA
32    WHERE FOCUS_AREA_NAME 
33    LIKE p_focusArea
34    AND ROWNUM = 1;
35
36    IF PROJECT_FOCUSAREA_NAME_COUNT < 1 THEN
37        err_msg_txt := 'The focus area does not exist, try creating it first';
38        RAISE ex_error;
39    END IF;
40
41    -- Check if the project ID is valid or not
42    SELECT COUNT(PROJECT_ID) INTO PROJECT_ID_COUNT
43    FROM I_PROJECT
44    WHERE PROJECT_ID = p_project_ID;
45
46    IF PROJECT_ID_COUNT < 1 THEN
47        err_msg_txt := 'The project ID does not exist';
48        RAISE ex_error;
49    END IF;
50
51    -- Check if the project ID and focus area have already been associated
52    SELECT COUNT(*) INTO PROJECT_ASSOCIATION_COUNT
53    FROM I_PROJ_FOCUSAREA
54    WHERE PROJECT_ID = p_project_ID 
55    AND FOCUS_AREA_NAME 
56    LIKE p_focusArea;
57
58    IF PROJECT_ASSOCIATION_COUNT >= 1 THEN
59        err_msg_txt := 'This association already exists';
60        RAISE ex_error;
61    END IF;
62
63    -- Inserts the data into the correct table
64    INSERT INTO I_PROJ_FOCUSAREA (PROJECT_ID, FOCUS_AREA_NAME) 
65    VALUES (p_project_ID, p_focusArea);
66
67    -- Commits the transaction
68    COMMIT;
69
70    DBMS_OUTPUT.PUT_LINE('The operation was successful, your changes have been commited to the database');
71
72EXCEPTION
73    -- Prints the error message when the exception is raised, and rolls back the transaction
74    WHEN ex_error THEN
75        DBMS_OUTPUT.PUT_LINE(err_msg_txt);
76    ROLLBACK;
77
78    WHEN OTHERS THEN
79        DBMS_OUTPUT.PUT_LINE('The error code is: ' || SQLCODE);
80        DBMS_OUTPUT.PUT_LINE('The error msg is:  ' || SQLERRM);
81    ROLLBACK;
82
83END;