KNE04Dxt

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