-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathpkg_employee_api.plb
245 lines (205 loc) · 9.57 KB
/
pkg_employee_api.plb
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
CREATE OR REPLACE
PACKAGE BODY PKG_EMPLOYEES_API AS
--------------------------------------------------------------------------------
-- Package Name: PKG_EMPLOYEES_API
--
-- Author : Widget Ltd
-- DESCRIPTION : This package is created in regard to
--
-- MODULES : This package Body comprises of the following modules
--
-- pr_create_employee : Procedure, creates an employee
-- pr_change_salary_by_pct : Procedure, allows the Salary for an Employee to be
-- increased or decreased by a percentage
-- pr_transfer_employee : Procedure, allows the transfer of an Employee to a different Department
-- f_get_salary(pi_employee_id : Function, return the Salary for an Employee.
--------------------------------------------------------------------------------
-- Declare global Variables
g_strErrMsg VARCHAR2(512) := ''; -- stores SQLERRM
PROCEDURE pr_create_employee( pi_emp_name IN employees.employee_name%TYPE,
pi_job_title IN employees.job_title%TYPE,
pi_Manager_id IN employees.manager_id%TYPE,
pi_date_hired IN employees.date_hired%TYPE,
pi_salary IN employees.salary%TYPE,
pi_department_id IN employees.department_id%TYPE) IS
--------------------------------------------------------------------------------
--PROCEDURE NAME : pr_create_employee
--
-- DESCRIPTION: This procedure allow an Employee to be created
-- in the EMPLOYEES table. Employee_id is created by increamenting
-- sequence number empseq (empseq.nextval) which starts from 90001
--
-- IN PARAMETERS:
-- pi_emp_name : Name of the Employee
-- pi_job_title : The job role undertaken by the employee.
-- pi_Manager_id : Line manager of the employee
-- pi_date_hired : The date the employee was hired
-- pi_salary : Current salary of the employee
-- pi_department_id : Department ID (as created in departments table)
-- Each employee must belong to a department
--
-- OUT PARAMETERS : N/A
--
-- IN OUT PARAMETERS: N/A
--
--
--------------------------------------------------------------------------------
BEGIN
g_strErrMsg := NULL;
--check if the department_id exists
-- if exists then proceed, if not throw an exception
INSERT INTO employees (
employee_id,
employee_name,
job_title,
manager_id,
date_hired,
salary,
department_id
) VALUES (
empseq.NEXTVAL,
pi_emp_name,
pi_job_title,
pi_manager_id,
pi_date_hired,
pi_salary,
pi_department_id
);
Commit;
EXCEPTION
WHEN OTHERS then
g_strErrMsg := NULL;
g_strErrMsg := substr(SQLERRM,1,512);
dbms_output.put_line('Error in inserting employee record');
RAISE_APPLICATION_ERROR( SQLCODE
, 'Error in inserting employee record '||g_strErrMsg);
END pr_create_employee;
/*
Create an appropriate executable database object to return the Salary for an
Employee.
*/
FUNCTION f_get_salary(pi_employee_id IN employees.employee_id%TYPE) RETURN employees.salary%TYPE
IS
--------------------------------------------------------------------------------
--FUNCTION NAME : f_get_salary
--
-- DESCRIPTION: This function returns salary of an employee
--
-- IN PARAMETERS:
-- pi_employee_id : Employee Identification number
--
-- RETURN : This function returns Salary of the employee
--
--------------------------------------------------------------------------------
Cursor cur_get_salary (cp_employee_id IN employees.employee_id%TYPE) IS
SELECT salary
from employees
Where employee_id = cp_employee_id;
employee_does_not_exist EXCEPTION;
l_salary employees.salary%TYPE := 0;
BEGIN
-- get the salary for the employee
OPEN cur_get_salary(pi_employee_id);
FETCH cur_get_salary INTO l_salary;
IF cur_get_salary%NOTFOUND THEN
RAISE employee_does_not_exist;
Else
RETURN l_salary;
END IF;
EXCEPTION
WHEN Employee_does_not_exist THEN
RAISE_APPLICATION_ERROR(-20001,'Employee does not exist, please enter valid employee');
WHEN OTHERS THEN
g_strErrMsg := NULL;
g_strErrMsg := substr(SQLERRM,1,512);
RAISE_APPLICATION_ERROR(-20000,g_strErrMsg);
END f_get_salary;
PROCEDURE pr_change_salary_by_pct(pi_employee_id IN employees.employee_id%TYPE
,pi_sal_pct IN NUMBER) IS
--------------------------------------------------------------------------------
--PROCEDURE NAME : pr_change_salary_by_pct
--
-- DESCRIPTION: This procedure allows the Salary for an
-- Employee to be increased or decreased by a percentage
--
-- IN PARAMETERS:
-- pi_employee_id : Employee Identification number
-- pi_sal_pct : Percentage of salary to be increased/decreased
-- pi_sal_pct > 0 => Increase
-- pi_sal_pct < 0 => Decrease
--
-- OUT PARAMETERS : N/A
--
-- IN OUT PARAMETERS: N/A
--
--
--------------------------------------------------------------------------------
l_salary employees.salary%TYPE := 0;
l_sal_pct NUMBER;
BEGIN
-- get the salary for the employee
l_salary := f_get_salary(pi_employee_id);
-- if pi_sal_pct > 0 then Increase
-- elsif pi_sal_pct < 0 then decrease
IF NVL(pi_sal_pct,0) <> 0 THEN
l_sal_pct := l_salary * (pi_sal_pct/100);
l_salary := l_salary+l_sal_pct;
END IF;
--
-- Update slary with new salary
--
UPDATE employees
SET salary = l_salary
WHERE employee_id = pi_employee_id;
Commit;
EXCEPTION
WHEN OTHERS THEN
g_strErrMsg := NULL;
g_strErrMsg := substr(SQLERRM,1,512);
RAISE_APPLICATION_ERROR(-20000,g_strErrMsg);
END pr_change_salary_by_pct;
/*
Create an appropriate executable database object to allow the transfer of an Employee to a different Department
*/
PROCEDURE pr_transfer_employee(pi_employee_id IN employees.employee_id%TYPE,pi_department_id IN departments.department_id%TYPE)
IS
--------------------------------------------------------------------------------
--PROCEDURE NAME : pr_transfer_employee
--
-- DESCRIPTION: This procedure allows
-- the transfer of an Employee to a different Department
--
-- IN PARAMETERS:
-- pi_employee_id : Employee Identification number
-- pi_Department_id : Target Department Id
--
-- OUT PARAMETERS : N/A
--
-- IN OUT PARAMETERS: N/A
--
--
--------------------------------------------------------------------------------
transfer_failed Exception ;
BEGIN
UPDATE EMPLOYEES emp
SET emp.department_id = pi_department_id
WHERE emp.employee_id = pi_employee_id
AND EXISTS (SELECT 1
FROM departments dept
WHERE dept.department_id = pi_department_id);
IF SQL%ROWCOUNT = 0 THEN
RAISE transfer_failed;
END IF;
Commit;
EXCEPTION
WHEN transfer_failed THEN
RAISE_APPLICATION_ERROR(-20003,'Transfer Failed, Check for valied Employee/department');
WHEN OTHERS THEN
g_strErrMsg := NULL;
g_strErrMsg := substr(SQLERRM,1,512);
RAISE_APPLICATION_ERROR(-20000,g_strErrMsg);
END pr_transfer_employee;
END PKG_EMPLOYEES_API;
/
SHOW ERRORS
/