· 8 years ago · Mar 20, 2018, 10:52 AM
1create or replace package body ioby3a_pkg
2IS
3procedure CREATE_ACCOUNT_PP (
4 p_account_id OUT INTEGER,
5 p_email IN VARCHAR, -- must not be NULL
6 p_password IN VARCHAR, -- must not be NULL
7 p_location_name IN VARCHAR, -- must not be NULL
8 p_account_type IN VARCHAR, -- should have value of 'Group or organization' or 'Individual'
9 p_first_name IN VARCHAR,
10 p_last_name IN VARCHAR
11)
12IS
13BEGIN
14 DECLARE
15 null_input_exists EXCEPTION;
16 incorrect_type EXCEPTION;
17 account_exists EXCEPTION;
18 invalid_format EXCEPTION;
19 countItems INT;
20 not_null_msg VARCHAR2(100) := NULL;
21 myaccounttype VARCHAR2(20) := NULL;
22 BEGIN
23 /* Validate the input data*/
24
25 IF p_email IS NULL THEN
26 SELECT not_null_msg || chr(10) || ' Email ' INTO not_null_msg FROM dual;
27 END IF;
28 IF p_password IS NULL THEN
29 SELECT not_null_msg || chr(10) || ' Password ' INTO not_null_msg FROM dual;
30 END IF;
31 IF p_location_name IS NULL THEN
32 SELECT not_null_msg || chr(10) || ' Location Name ' INTO not_null_msg FROM dual;
33 END IF;
34 /*Need to sanitize the account type casing since there is a check constraint on the field*/
35 myaccounttype := p_account_type;
36 IF myaccounttype IS NULL THEN
37 RAISE incorrect_type;
38 ELSIF UPPER(myaccounttype) = 'GROUP OR ORGANIZATION' THEN
39 myaccounttype := 'Group or organization';
40 ELSIF UPPER(myaccounttype) = 'INDIVIDUAL' THEN
41 myaccounttype := 'Individual';
42 ELSE
43 RAISE incorrect_type;
44 END IF;
45
46 IF NOT not_null_msg IS NULL THEN
47 RAISE null_input_exists;
48 END IF;
49 IF NOT regexp_like(p_email, '[A-Z0-9._%-]+@[A-Z0-9._%-]+\.[A-Z]{2,4}', 'i') THEN
50 RAISE invalid_format;
51 END IF;
52 SELECT COUNT(*) INTO countItems FROM I_ACCOUNT WHERE UPPER(ACCOUNT_EMAIL) = UPPER(p_email);
53
54 IF countItems > 0 THEN
55 RAISE account_exists;
56 END IF;
57
58
59 INSERT INTO I_ACCOUNT
60 (ACCOUNT_ID, ACCOUNT_EMAIL, ACCOUNT_PASSWORD, ACCOUNT_LOCATION_NAME, ACCOUNT_TYPE, ACCOUNT_FIRST_NAME, ACCOUNT_LAST_NAME)
61 SELECT MAX(ACCOUNT_ID) + 1 MyAccountID, p_email, p_password, p_location_name, myaccounttype, p_first_name, p_last_name
62 FROM I_ACCOUNT;
63
64 SELECT MAX(ACCOUNT_ID) INTO p_account_id FROM I_ACCOUNT WHERE UPPER(ACCOUNT_EMAIL) = UPPER(p_email);
65
66 DBMS_OUTPUT.PUT_LINE('An account has been created with the given info. The account id is: ' || p_account_id);
67
68 COMMIT;
69
70 EXCEPTION
71 WHEN null_input_exists THEN
72 DBMS_OUTPUT.PUT_LINE('Missing Input Data.');
73 DBMS_OUTPUT.PUT_LINE('Please make sure to provide input for the following fields: ' || not_null_msg);
74 WHEN incorrect_type THEN
75 DBMS_OUTPUT.PUT_LINE('Incorrect Account Type Defined.');
76 DBMS_OUTPUT.PUT_LINE('Account Type should have value of ''Group or organization'' or ''Individual''');
77 WHEN account_exists THEN
78 DBMS_OUTPUT.PUT_LINE('Account already exists.');
79 DBMS_OUTPUT.PUT_LINE('An existing account has been found for the email address provided. Please use a different email.');
80 WHEN invalid_format THEN
81 DBMS_OUTPUT.PUT_LINE('Email Address not valid.');
82 DBMS_OUTPUT.PUT_LINE('The format of the email address is not valid. Please use a different email.');
83 WHEN OTHERS THEN
84 DBMS_OUTPUT.PUT_LINE('An Error occurred');
85 DBMS_OUTPUT.PUT_LINE('The error number: ' || SQLCODE);
86 DBMS_OUTPUT.PUT_LINE('The error message: ' || SQLERRM);
87 ROLLBACK;
88END;
89END CREATE_ACCOUNT_PP;
90
91procedure CREATE_PROJECT_PP (
92p_project_id OUT INTEGER,
93p_title IN VARCHAR,
94p_goal IN NUMBER, -- The goal should be >= zero
95p_deadline IN DATE,
96p_creation_date IN DATE,
97p_description IN CLOB,
98p_subtitle IN VARCHAR,
99p_street_1 IN VARCHAR,
100p_street_2 IN VARCHAR,
101p_city IN VARCHAR,
102P_state IN VARCHAR,
103p_postal_code IN CHAR,
104p_postal_extension IN CHAR,
105p_steps_to_take IN CLOB,
106p_motivation IN CLOB,
107p_volunteer_need IN VARCHAR, -- should be 'yes' or 'no'
108p_project_status IN VARCHAR, -- should be in {'Closed', 'Completed', 'Open', 'Submitted', 'Underway'}
109p_account_id IN INTEGER -- should match account in the account table
110)
111
112IS
113BEGIN
114 DECLARE
115 null_input_exists EXCEPTION;
116 invalid_status EXCEPTION;
117 invalid_deadline EXCEPTION;
118 invalid_volunteer EXCEPTION;
119 invalid_account EXCEPTION;
120 invalid_goal EXCEPTION;
121 invalid_description EXCEPTION;
122 countItems INT;
123 not_null_msg VARCHAR2(500) := NULL;
124 myprojectstatus VARCHAR2(20) := NULL;
125 myprojectvolunteer VARCHAR2(20) := NULL;
126 CurrentDate DATE := SYSDATE;
127 BEGIN
128 IF p_title IS NULL THEN
129 SELECT not_null_msg || chr(10) || ' Title ' INTO not_null_msg FROM dual;
130 END IF;
131 IF p_goal < 0 THEN
132 RAISE invalid_goal;
133 END IF;
134 IF p_goal IS NULL THEN
135 SELECT not_null_msg || chr(10) || ' Goal ' INTO not_null_msg FROM dual;
136 END IF;
137 IF p_deadline IS NULL THEN
138 SELECT not_null_msg || chr(10) || ' Deadline ' INTO not_null_msg FROM dual;
139 END IF;
140 IF p_description IS NULL THEN
141 SELECT not_null_msg || chr(10) || ' Description ' INTO not_null_msg FROM dual;
142 END IF;
143 IF p_city IS NULL THEN
144 SELECT not_null_msg || chr(10) || ' City ' INTO not_null_msg FROM dual;
145 END IF;
146 IF p_subtitle IS NULL THEN
147 SELECT not_null_msg || chr(10) || ' Subtitle ' INTO not_null_msg FROM dual;
148 END IF;
149 IF p_street_1 IS NULL THEN
150 SELECT not_null_msg || chr(10) || ' Street ' INTO not_null_msg FROM dual;
151 END IF;
152 IF p_state IS NULL THEN
153 SELECT not_null_msg || chr(10) || ' State ' INTO not_null_msg FROM dual;
154 END IF;
155 IF p_postal_code IS NULL THEN
156 SELECT not_null_msg || chr(10) || ' Postal Code ' INTO not_null_msg FROM dual;
157 END IF;
158 IF myprojectstatus IS NULL THEN
159 SELECT not_null_msg || chr(10) || ' Project Status ' INTO not_null_msg FROM dual;
160 END IF;
161
162 IF p_deadline <= p_creation_date
163 THEN
164 RAISE invalid_deadline;
165 END IF;
166
167 IF p_description IS NULL THEN
168 RAISE invalid_description;
169 END IF;
170
171 myprojectvolunteer := p_volunteer_need;
172 IF myprojectvolunteer IS NULL THEN
173 RAISE invalid_volunteer;
174 ELSIF UPPER(myprojectvolunteer) = 'YES' THEN
175 myprojectvolunteer := 'yes';
176 ELSIF UPPER(myprojectvolunteer) = 'NO' THEN
177 myprojectvolunteer := 'no';
178 ELSE
179 RAISE invalid_volunteer;
180 END IF;
181
182 myprojectstatus := p_project_status;
183 IF myprojectstatus IS NULL THEN
184 myprojectstatus := 'Submitted';
185 ELSIF UPPER(myprojectstatus) = 'UNDERWAY' THEN
186 myprojectstatus := 'Underway';
187 ELSIF UPPER(myprojectstatus) = 'OPEN' THEN
188 myprojectstatus := 'Open';
189 ELSIF UPPER(myprojectstatus) = 'CLOSED' THEN
190 myprojectstatus := 'Closed';
191 ELSIF UPPER(myprojectstatus) = 'COMPLETE' THEN
192 myprojectstatus := 'Complete';
193 ELSIF UPPER(myprojectstatus) = 'SUBMITTED' THEN
194 myprojectstatus := 'Submitted';
195 ELSE
196 RAISE invalid_status;
197 END IF;
198
199 SELECT COUNT(*) INTO countItems FROM I_ACCOUNT WHERE account_id = p_account_id;
200 IF countItems < 1 THEN
201 RAISE invalid_account;
202 END IF;
203
204 INSERT INTO I_PROJECT
205 (PROJECT_ID,PROJECT_TITLE,PROJECT_GOAL,PROJECT_DEADLINE,PROJECT_CREATION_DATE,PROJECT_DESCRIPTION,
206 PROJECT_SUBTITLE,PROJECT_STREET_1,PROJECT_STREET_2,PROJECT_CITY,PROJECT_STATE,PROJECT_POSTAL_CODE,
207 PROJECT_POSTAL_EXTENSION,PROJECT_STEPS_TO_TAKE, PROJECT_MOTIVATION, PROJECT_VOLUNTEER_NEED,PROJECT_STATUS,ACCOUNT_ID)
208 SELECT MAX(PROJECT_ID) + 1, p_title, p_goal, p_deadline, NVL(p_creation_date, currentdate), p_description, p_subtitle, p_street_1, p_street_2,
209 p_city, p_state, p_postal_code, p_postal_extension, p_steps_to_take, p_motivation, myprojectvolunteer, myprojectstatus, p_account_id
210 FROM I_PROJECT;
211
212 COMMIT;
213
214 EXCEPTION
215
216 WHEN null_input_exists THEN
217 DBMS_OUTPUT.PUT_LINE('Missing Input Data.');
218 DBMS_OUTPUT.PUT_LINE('Please make sure to provide input for the following fields: ' || not_null_msg);
219 WHEN invalid_description THEN
220 DBMS_OUTPUT.PUT_LINE('Invalid description, please put '' in front and back of the text input.');
221 WHEN invalid_goal THEN
222 DBMS_OUTPUT.PUT_LINE('Invalid goal, please specify a goal larger than zero.');
223 WHEN invalid_deadline THEN
224 DBMS_OUTPUT.PUT_LINE('Invalid deadline, please set deadline to a larger value than the project creation date.');
225 WHEN invalid_status THEN
226 DBMS_OUTPUT.PUT_LINE('Invalid status, accepted input is Open, Underway, Closed and Submitted.');
227 WHEN invalid_volunteer THEN
228 DBMS_OUTPUT.PUT_LINE('Invalid volunteer, accepted input is yes and no.');
229 WHEN invalid_account THEN
230 DBMS_OUTPUT.PUT_LINE('Invalid account, please specify a correct accound ID.');
231 WHEN OTHERS THEN
232 DBMS_OUTPUT.PUT_LINE('An Error occurred');
233 DBMS_OUTPUT.PUT_LINE('The error number: ' || SQLCODE);
234 DBMS_OUTPUT.PUT_LINE('The error message: ' || SQLERRM);
235 ROLLBACK;
236END;
237END CREATE_PROJECT_PP;
238
239
240
241END ioby3a_pkg;