· 8 years ago · Mar 04, 2018, 07:44 PM
1/*Add a website for an account. The procedure should check that the account email,
2the URL, and the description are not NULL. The procedure should look up the
3account id of the account that has the given email address. If no such account
4exists, the procedure should generate an error message.
5*/
6
7
8create or replace procedure ADD_WEBSITE_SP (
9p_accountEmail IN VARCHAR,
10p_websiteOrder IN INTEGER, -- Must be >= zero or NULL
11p_websiteTitle IN VARCHAR,
12p_websiteURL IN VARCHAR
13)
14IS
15v_AccountId I_ACCOUNT.account_id%type;
16v_websiteOrder I_WEBSITE.website_order%type;
17BEGIN
18 --The procedure should check
19 --that the account email, the URL, and the description are not NULL.
20
21 if (p_accountEmail is NULL)
22 then
23 raise_application_error(-20000, 'p_accountEmail parameter should not be passed a NULL Value');
24 end if;
25
26
27 if (p_websiteURL is NULL or p_websiteURL < 0)
28 then
29 raise_application_error(-20001, 'p_websiteURL parameter should not be passed a NULL Value or a negative value');
30 end if;
31
32
33 if (p_websiteTitle is NULL)
34 then
35 raise_application_error(-20002, 'p_websiteTitle parameter should not be passed a NULL Value');
36 end if;
37
38 begin
39 -- The procedure should look up the account id of the account that has the given email address.
40 select account_id
41 into v_AccountId
42 from I_ACCOUNT
43 where account_email = p_accountEmail;
44 exception
45 when NO_DATA_FOUND then
46 raise_application_error(-20003, 'No account id found for the account email := ' || p_accountEmail);
47 end;
48
49
50 -- fetch the max order number of the website for this account
51 -- if there are no websites for this account then the maz should be 0
52 begin
53 select max(website_order)
54 into v_websiteOrder
55 from I_WEBSITE
56 where account_id = v_AccountId;
57 exception
58 when NO_DATA_FOUND then
59 v_websiteOrder := 0;
60 end;
61
62 -- If the order value is NULL, then the procedure should
63 -- determine the largest order number used for the website list for this
64 -- account and increment this value to be used for the current website.
65
66 -- OR
67
68 --If the order number is more than +1 greater
69 --than the largest order number, increment the largest order number so that
70 --the order numbers are always increments of each other.
71
72 if (p_websiteOrder is null or p_websiteOrder > v_websiteOrder + 1)
73 then
74
75 --insert the record for website by incrementing the largest order value
76 insert into I_WEBSITE values (v_AccountId, v_websiteOrder + 1, p_websiteTitle, p_websiteURL);
77
78 else
79
80 -- If an order number is provided, then the procedure should renumber the websites
81 -- associated with this account with an order number greater than or equal to
82 -- the value of this parameter.
83
84 -- step 1: update all the websites with order >= passed website order to current order nuymber + 1
85 update I_WEBSITE
86 set website_order = website_order + 1
87 where account_id = v_AccountId
88 and website_order >= p_websiteOrder;
89
90 -- step 2: insert into the website table with the given order value.
91 insert into I_WEBSITE values (v_AccountId, p_websiteOrder, p_websiteTitle, p_websiteURL);
92
93
94 end if;
95
96 --commit the transaction
97 commit;
98
99END;
100
101
102/*
103Associate a focus area with a project. The procedure should check that the
104focus area and the project id are valid. If either is NULL or not valid, an
105appropriate error message should be generated.
106*/
107create or replace procedure ADD_FOCUSAREA_SP (
108p_project_ID IN INTEGER,
109p_focusArea IN VARCHAR
110)
111IS
112vCount Number;
113BEGIN
114 --check if the project id is null, raise an error if it is null
115 if (p_project_ID is NULL)
116 then
117 raise_application_error(-20001, 'p_project_ID parameter should not be passed a NULL Value');
118 end if;
119
120 --check if the p_focusArea is null, raise an error if it is null
121 if (p_focusArea is NULL)
122 then
123 raise_application_error(-20002, 'p_focusArea parameter should not be passed a NULL Value');
124 end if;
125
126 --check if the project id is valid and has a corresponding record in I_project table
127 -- raise an error otherwise
128
129 select count(1)
130 into vCount
131 from I_PROJECT
132 where project_id = p_project_ID;
133
134 if (vCount = 0)
135 then
136 raise_application_error(-20003, 'p_project_ID = ' || p_project_ID || ' is not a valid project id');
137 end if;
138
139 --check if the p_focusArea is valid and has a corresponding record in I_FOCUS_AREA table
140 -- raise an error otherwise
141
142 select count(1)
143 into vCount
144 from I_FOCUS_AREA
145 where focus_area_name = p_focusArea;
146
147 if (vCount = 0)
148 then
149 raise_application_error(-20003, 'p_focusArea = ' || p_focusArea || ' is not a valid focusArea');
150 end if;
151
152 --if everything is valid then insert a record in the I_PROJ_FOCUSAREA table
153 insert into I_PROJ_FOCUSAREA values (p_focusArea, p_project_ID);
154
155 -- commit the tranaction
156 commit;
157END;
158/