iCwQXB2V

1create or replace procedure CREATE_GIVING_LEVEL_SP (
2p_projectID             IN I_GIVING_LEVEL.PROJECT_ID%TYPE,
3p_givingLevelAmt        IN I_GIVING_LEVEL.GIVING_LEVEL_AMOUNT%TYPE,   -- Must be > zero or NULL
4p_givingDescription     IN I_GIVING_LEVEL.GIVING_LEVEL_DESCRIPTION%TYPE    -- Must not be NULL
5)
6IS
7    ex_error    EXCEPTION;
8    err_msg_txt VARCHAR(100) := NULL;
9    project_count NUMBER;
10    description_count NUMBER;
11BEGIN
12    
13-- DESCRIPTION CAN'T BE NULL
14IF p_GivingDescription IS NULL THEN
15        err_msg_txt := 'A description is required!';
16        RAISE ex_error;
17END IF;    
18    
19 -- AMOUNT CAN'T BE NULL
20IF p_givingLevelAmt  IS NULL THEN
21        err_msg_txt := 'An amount is required!';
22        RAISE ex_error;
23END IF;    
24
25 -- AMOUNT CAN'T BE ZERO OR BELOW
26IF p_givingLevelAmt <=0 THEN
27        err_msg_txt := 'A positive amount is required!';
28        RAISE ex_error;
29END IF;   
30
31-- CHECK IF THE PROJECT EXISTS    
32SELECT COUNT(*) INTO project_count FROM I_PROJECT WHERE project_ID = p_projectID;
33IF project_count < 1 THEN
34    err_msg_txt := 'Project ' || p_projectID || ' does not exist!';
35RAISE ex_error;
36END IF;  
37    
38-- CHECK DUPLICATE GIVING LEVEL
39SELECT COUNT(*) INTO description_count FROM I_GIVING_LEVEL WHERE giving_level_amount = p_givingLevelAmt;
40IF description_count < 1 THEN
41    INSERT INTO I_GIVING_LEVEL(PROJECT_ID, GIVING_LEVEL_AMOUNT, GIVING_LEVEL_DESCRIPTION)
42    VALUES (p_projectID, p_givingLevelAmt, p_givingDescription);
43    COMMIT;
44ELSE 
45    UPDATE I_GIVING_LEVEL
46    SET GIVING_LEVEL_AMOUNT = p_givingLevelAmt
47    WHERE I_GIVING_LEVEL.PROJECT_ID = CREATE_GIVING_LEVEL_SP.p_projectID;
48END IF;
49--Insert a value to the table
50
51EXCEPTION
52    WHEN ex_error THEN
53        DBMS_OUTPUT.PUT_LINE(err_msg_txt);
54        ROLLBACK;
55    WHEN OTHERS THEN
56        DBMS_OUTPUT.PUT_LINE('The error code is: ' || SQLCODE);
57        DBMS_OUTPUT.PUT_LINE('The error msg is: ' || SQLERRM);
58        ROLLBACK;    
59        null;
60        
61        
62   
63END;