My PLSQL PRACTICES
1. Evaluate your declarations.
a. DECLARE v_name, v_dept VARCHAR2(14);
This declaration is illegal, because Oracle does not allow two variables to be declared together.
b. DECLARE v_test NUMBER(5);
Legal
c. DECLARE V_MAXSALARY NUMBER(7,2) = 5000;
This declaration is illegal, because assignment is done using := operator, whereas comparison is done using = operator.
d. DECLARE V_JOINDATE BOOLEAN := SYSDATE;
ILLEGAL… Can’t assign date value to Boolean variable. Oracle throws following error.
PLS-00382: expression is of wrong type
2. Determine resulting datatype
a. v_email := v_firstname || to_char(v_empno);
Ans: Result would be of varchar2
b. v_confirm := to_date('20-JAN-1999', 'DD-MON-YYYY');
OUTPUT WILL BE IN DATE.
Explanation:
create or replace procedure VALIDST
as
v_confirm DATE;
begin
v_confirm := to_date('20-Jan-1999','DD-MON-YYYY');
dbms_output.put_line(v_confirm);
end;
/
EXECUTION
SQL> @c:\TRUPTI\PLSQL\VALIDSTATEMENT.TXT
SQL> @c:\TRUPTI\PLSQL\VALIDSTATEMENT.TXT
Procedure created.
SQL> EXEC VALIDST
PL/SQL procedure successfully completed.
SQL> SET SERVEROUTPUT ON
SQL> EXEC VALIDST
20-JAN-99
c. v_sal := (1000*12) + 500
OUTPUT WILL BE IN NUMBER
create or replace procedure VALIDST2
as
v_sal NUMBER;
begin
v_sal := (1000*12) + 500;
dbms_output.put_line(v_sal);
end;
/
EXECUTION
SQL> @c:\TRUPTI\PLSQL\VALIDSTATEMENT2.TXT
Procedure created.
SQL> EXEC VALIDST2
12500
PL/SQL procedure successfully completed.
d. v_test := FALSE;
MY_REMAINDER: OUTPUT WILL BE IN BOOLEAN. BUT DBMS_OUTPUT.PUT_LINE DOESN’T ALLOW BOOLEAN VARIABLE TO BE DISPLAYED. HENCE, WE CAN USE BOOLEAN VALUE FOR COMPARISON.
Explanation:
create or replace procedure VALIDST3
as
v_TEST BOOLEAN;
begin
v_TEST := FALSE;
if V_TEST = True then
dbms_output.put_line('True portion');
else
dbms_output.put_line('False portion');
end if;
end;
/
e. v_temp := v_temp1 < (v_temp2/ 3);
Output will be in boolean
Explanation:
create or replace procedure VALIDST4
as
v_tmp boolean;
v_tmp1 number(5) := 15;
v_tmp2 number(5) := 10;
begin
v_tmp := v_tmp1 < (v_tmp2 / 5);
if v_tmp then
dbms_output.put_line('true');
else
dbms_output.put_line('true');
end if;
end;
/
EXECUTION
SQL> @c:\TRUPTI\PLSQL\VALIDSTATEMENT4.TXT
Procedure created.
SQL> EXEC VALIDST4
true
PL/SQL procedure successfully completed.
f. v_var := sysdate;
Output will be of DATE type.
Explanation:
create or replace procedure VALIDST5
as
v_DT DATE;
begin
v_DT := SYSDATE;
dbms_output.put_line(v_DT);
end;
/
SQL> @c:\TRUPTI\PLSQL\VALIDSTATEMENT5.TXT
Procedure created.
SQL> EXEC VALIDST5
05-OCT-11
PL/SQL procedure successfully completed.
Part A
3. DECLARE
v_custid NUMBER(4) := 1600;
v_custname VARCHAR2(300) := 'Women Sports Club';
v_new_custid NUMBER(3) := 500;
BEGIN
DECLARE
v_custid NUMBER(4) := 0;
v_custname VARCHAR2(300) := 'Shape up Sports Club';
v_new_custid NUMBER(3) := 300;
v_new_custname VARCHAR2(300) := 'Jansports Club';
BEGIN
v_custid := v_new_custid;
v_custname := v_custname || ' ' || v_new_custname;
END;
v_custid := (v_custid *12) / 10;
END;
/
Evaluate the PL/SQL block above and determine the data type and value of each of the following variables
according to the rules of scoping:
a. The value of V_CUSTID at position 1 is:
b. The value of V_CUSTNAME at position 1 is:
c. The value of V_NEW_CUSTID at position 2 is:
d. The value of V_NEW_CUSTNAME at position 1 is:
e. The value of V_CUSTID at position 2 is:
f. The value of V_CUSTNAME at position 2 is:
DECLARE
v_custid NUMBER(4) := 1600;
v_custname VARCHAR2(300) := 'Women Sports Club';
v_new_custid NUMBER(3) := 500;
BEGIN
DECLARE
v_custid NUMBER(4) := 0;
v_custname VARCHAR2(300) := 'Shape up Sports Club';
v_new_custid NUMBER(3) := 300;
v_new_custname VARCHAR2(300) := 'Jansports Club';
BEGIN
v_custid := v_new_custid;
v_custname := v_custname || ' ' || v_new_custname;
dbms_output.put_line(' Cust_id in inner block :- ' || v_custid);
dbms_output.put_line(' Cust_name in inner block :- ' || v_custname);
dbms_output.put_line(' Cust_new_id in inner block :- ' || v_new_custid);
END;
v_custid := (v_custid *12) / 10;
dbms_output.put_line(' Cust_id in outer block :- ' ||v_custid);
dbms_output.put_line(' Cust_name in outer block :- ' || v_custname);
dbms_output.put_line(' Cust_new_id in outer block :- ' || v_new_custid);
END;
/
Copy and paste it in sql. Ctrl+c and right click on sql. Or Edit -> paste.
OUTPUT
Cust_id in inner block :- 300
Cust_name in inner block :- Shape up Sports Club Jansports Club
Cust_new_id in inner block :- 300
Cust_id in outer block :- 1920
Cust_name in outer block :- Women Sports Club
Cust_new_id in outer block :- 500
PL/SQL procedure successfully completed.
Note: These exercises can be used for extra practice when discussing how to interact with the Oracle server and write control structures.
4. Write a PL/SQL block to accept a year and check whether it is a leap year. For example, if the year entered is 1990, the output should be “1990 is not a leap year.”
Hint: The year should be exactly divisible by 4 but not divisible by 100, or it should be divisible by 400
SQL> DECLARE
YR NUMBER(6,2) := &YR;
BEGIN
IF (MOD(YR,4) = 0) AND (MOD(YR,100) <> 0) OR (MOD(YR,400)=0) THEN
DBMS_OUTPUT.PUT_LINE(YR || 'IS A LEAP YEAR');
ELSE
DBMS_OUTPUT.PUT_LINE(YR || 'IS NOT A LEAP YEAR');
END IF;
END;
/
Enter value for yr: 1990
old 2: YR NUMBER(6,2) := &YR;
new 2: YR NUMBER(6,2) := 1990;
1990IS NOT A LEAP YEAR
PL/SQL procedure successfully completed.
-----------------------------------------------------------------------------------------------
SQL> /
Enter value for yr: 2000
old 2: YR NUMBER(6,2) := &YR;
new 2: YR NUMBER(6,2) := 2000;
2000IS A LEAP YEAR
PL/SQL procedure successfully completed.
-----------------------------------------------------------------------------------------------
SQL> /
Enter value for yr: 1996
old 2: YR NUMBER(6,2) := &YR;
new 2: YR NUMBER(6,2) := 1996;
1996IS A LEAP YEAR
PL/SQL procedure successfully completed.
-----------------------------------------------------------------------------------------------
SQL> /
Enter value for yr: 1886
old 2: YR NUMBER(6,2) := &YR;
new 2: YR NUMBER(6,2) := 1886;
1886IS NOT A LEAP YEAR
PL/SQL procedure successfully completed.
-----------------------------------------------------------------------------------------------
SQL> /
Enter value for yr: 1992
old 2: YR NUMBER(6,2) := &YR;
new 2: YR NUMBER(6,2) := 1992;
1992IS A LEAP YEAR
PL/SQL procedure successfully completed.
-----------------------------------------------------------------------------------------------
SQL> /
Enter value for yr: 1824
old 2: YR NUMBER(6,2) := &YR;
new 2: YR NUMBER(6,2) := 1824;
1824IS A LEAP YEAR
PL/SQL procedure successfully completed.
5. a. For the exercises below, you will require a temporary table to store the results. You can either create the table yourself or run the labAp_05.sql script that will create the table for you. Create a table named TEMP with the following three columns:
b.
> Write a PL/SQL block that contains two variables, MESSAGE and DATE_WRITTEN.
> Declare MESSAGE as VARCHAR2 data type with a length of 35 and DATE_WRITTEN as
> DATE data type. Assign the following values to the variables:
Variable Contents
MESSAGE : ‘This is my first PL/SQL program’
DATE_WRITTEN : Current date
Store the values in appropriate columns of the TEMP table. Verify your results by querying the TEMP table.
Column Name NUM_STORE CHAR_STORE DATE_STORE
SQL> create table TMP
(NUM_STORE NUMBER(7,2),
CHAR_STORE VARCHAR2(35),
DATE_STORE DATE) ;
Table created.
SQL> DECLARE
MSG VARCHAR2(35) := 'This is my first PL/SQL program';
DATE_WRITTEN DATE := SYSDATE;
BEGIN
insert into TMP (CHAR_STORE, DATE_STORE) values
(MSG, DATE_WRITTEN);
END;
/
PL/SQL procedure successfully completed.
SQL> select * from tmp;
NUM_STORE CHAR_STORE DATE_STORE
----------------- ---------------------------------------- -------------------
This is my first PL/SQL program 05-OCT-11
7. Write a PL/SQL block to declare a variable called v_salary to store the salary of an employee. In the executable part of the program, do the following:
a. Store an employee name in a iSQL*Plus substitution variable
b. Store his or her salary in the variable v_salary
c. If the salary is less than 3,000, give the employee a raise of 500 and display the message '<Employee Name>’s salary updated' in the window.
d. If the salary is more than 3,000, print the employee’s salary in the format, '<Employee Name> earns …...………'
e. Test the PL/SQL for the following last names:
SQL> DECLARE
ENM VARCHAR2(40) := '&EMPLOYEE_NAME';
SAL NUMBER(7,2) := &SAL;
BEGIN
IF SAL < 3000 THEN
SAL := SAL + 500;
DBMS_OUTPUT.PUT_LINE('Employee ' || ENM || ' salary is increased '|| SAL);
ELSE
DBMS_OUTPUT.PUT_LINE('Employee earns Rs. ' || SAL);
END IF;
END;
/
Enter value for employee_name: PATABELLA
old 2: ENM VARCHAR2(40) := '&EMPLOYEE_NAME';
new 2: ENM VARCHAR2(40) := 'PATABELLA';
Enter value for sal: 4800
old 3: SAL NUMBER(7,2) := &SAL;
new 3: SAL NUMBER(7,2) := 4800;
Employee earns Rs. 4800
PL/SQL procedure successfully completed.
-----------------------------------------------------------------------------------------------
SQL> /
Enter value for employee_name: Greenburg
old 2: ENM VARCHAR2(40) := '&EMPLOYEE_NAME';
new 2: ENM VARCHAR2(40) := 'Greenburg';
Enter value for sal: 12000
old 3: SAL NUMBER(7,2) := &SAL;
new 3: SAL NUMBER(7,2) := 12000;
Employee earns Rs. 12000
PL/SQL procedure successfully completed.
-----------------------------------------------------------------------------------------------
SQL> /
Enter value for employee_name: Ernst
old 2: ENM VARCHAR2(40) := '&EMPLOYEE_NAME';
new 2: ENM VARCHAR2(40) := 'Ernst';
Enter value for sal: 6000
old 3: SAL NUMBER(7,2) := &SAL;
new 3: SAL NUMBER(7,2) := 6000;
Employee earns Rs. 6000
PL/SQL procedure successfully completed.
-----------------------------------------------------------------------------------------------
SQL> /
Enter value for employee_name: Philtanker
old 2: ENM VARCHAR2(40) := '&EMPLOYEE_NAME';
new 2: ENM VARCHAR2(40) := 'Philtanker';
Enter value for sal: 2200
old 3: SAL NUMBER(7,2) := &SAL;
new 3: SAL NUMBER(7,2) := 2200;
Employee Philtanker salary is incresed Rs. 2700
PL/SQL procedure successfully completed.
8. a. Store the salary of an employee in a iSQL*Plus substitution variable.
b. Write a PL/SQL block to use the above defined salary and perform the following:
• Calculate the annual salary as salary * 12.
• Calculate the bonus as indicated below:
• Display the amount of the bonus in the window in the following format:
‘The bonus is $………………..’
Annual Salary Bonus
>= 20,000 2,000
19,999 - 10,000 1,000
<= 9,999 500
SQL> DECLARE
SAL NUMBER(9,2) := &SAL;
ASAL NUMBER(9,2);
BONUS NUMBER(7,2);
BEGIN
ASAL := SAL*12;
DBMS_OUTPUT.PUT_LINE(ASAL);
IF (ASAL > 20000) THEN
BONUS := 2000;
DBMS_OUTPUT.PUT_LINE('Annual Salary is : '|| ASAL);
DBMS_OUTPUT.PUT_LINE(' GREATER THAN 20000 HENCE BONUS = '||BONU
ELSIF (ASAL > 10000) THEN
BONUS := 1000;
DBMS_OUTPUT.PUT_LINE('Annual Salary is : '|| ASAL);
DBMS_OUTPUT.PUT_LINE(' GREATER THAN 10000 BUT LESS THAN 20000 HENCE BONUS = '||BONUS);
ELSE
BONUS := 500;
DBMS_OUTPUT.PUT_LINE('Annual Salary is : '|| ASAL);
DBMS_OUTPUT.PUT_LINE(' LESS THAN 10000 HENCE BONUS = '||BONUS);
END IF;
END;
/
Enter value for sal: 5000
old 3: SAL NUMBER(9,2) := &SAL;
new 3: SAL NUMBER(9,2) := 5000;
60000
Annual Salary is : 60000
GREATER THAN 20000 HENCE BONUS = 2000
PL/SQL procedure successfully completed.
-----------------------------------------------------------------------------------------------
SQL> /
Enter value for sal: 1000
old 3: SAL NUMBER(9,2) := &SAL;
new 3: SAL NUMBER(9,2) := 1000;
12000
Annual Salary is : 12000
GREATER THAN 10000 BUT LESS THAN 20000 HENCE BONUS = 1000
PL/SQL procedure successfully completed.
-----------------------------------------------------------------------------------------------
SQL> /
Enter value for sal: 15000
old 3: SAL NUMBER(9,2) := &SAL;
new 3: SAL NUMBER(9,2) := 15000;
180000
Annual Salary is : 180000
GREATER THAN 20000 HENCE BONUS = 2000
PL/SQL procedure successfully completed.
9. a. Write a PL/SQL block to store an employee number, the new department number, and the percentage increase in the salary in iSQL*Plus substitution variables.
b. Update the department ID of the employee with the new department number, and update the salary with the new salary. Use the EMP table for the updates. Once the update is complete, display the message, 'Update complete' in the window. If no matching records are found, display ‘No Data Found’. Test the PL/SQL for the following test cases:
| EmpNo | DeptNo | %Increase | Message |
| 7895 | 20 | 2 | Updation Complete |
| 7772 | 30 | 5 | No data found |
| 7900 | 10 | 3 | Updation Complete |
DECLARE
EMPID NUMBER(7,2) := &EMPID;
NEW_DEPTNO NUMBER(7,2) := &NEW_DEPTNO;
BEGIN
UPDATE EMPLY SET SAL = SAL + (SAL * 00.2 ) WHERE EMPNO = EMPID;
DBMS_OUTPUT.PUT_LINE(' Salary Updated successfully to ' );
END;
Enter value for empid: 7566
old 3: EMPID NUMBER(7,2) := &EMPID;
new 3: EMPID NUMBER(7,2) := 7566;
Enter value for new_deptno: 10
old 4: NEW_DEPTNO NUMBER(7,2) := &NEW_DEPTNO;
new 4: NEW_DEPTNO NUMBER(7,2) := 10;
Salary Updated successfully
PL/SQL procedure successfully completed.
DECLARE
EMPID NUMBER(7,2) := &EMPID;
NEW_DEPTNO NUMBER(7,2) := &NEW_DEPTNO;
BEGIN
UPDATE EMPLY SET SAL = SAL + (SAL * 00.2 ) WHERE EMPNO = EMPID;
DBMS_OUTPUT.PUT_LINE(' Salary Updated successfully to ' );
END;
Enter value for empid: 7566
old 3: EMPID NUMBER(7,2) := &EMPID;
new 3: EMPID NUMBER(7,2) := 7566;
Enter value for new_deptno: 10
old 4: NEW_DEPTNO NUMBER(7,2) := &NEW_DEPTNO;
new 4: NEW_DEPTNO NUMBER(7,2) := 10;
PL/SQL procedure successfully completed.
10. Create a PL/SQL block to declare a cursor EMP_CUR to select the employee name, salary, and hiredate from the EMPLOYEES table. Process each row from the cursor, and if the salary is greater than 15,000 and the hire date is greater than 01-FEB-1988, display the employee name, salary, and hire date in the window.
PL/SQL BLOCK:
declare
cursor emp_cur is
select * from employees;
emp emp_cur%rowtype;
n NUMBER(7,2);
begin
open emp_cur;
select count(*) into n from employees;
for i in 1..n loop
fetch emp_cur into emp;
if(emp.salary > 15000 AND (to_date(emp.hire_date) > to_date('1-feb-1988'))) then
dbms_output.put_line(emp.first_name||' earns '||emp.salary||' and joined the organization in '||emp.hire_date);
end if;
end loop;
close emp_cur;
end;
/
/* output
Neena earns 17000 and joined the organization in 21-SEP-89
Lex earns 17000 and joined the organization in 13-JAN-93 */
11. Create a PL/SQL block to retrieve the last name and department ID of each employee from the EMPLOYEES table for those employees whose EMPLOYEE_ID is less than 114. From the values retrieved from the EMPLOYEES table, populate two PL/SQL tables, one to store the records of the employee last names and the other to store the records of their department IDs. Using a loop, retrieve the employee name information and the salary information from the PL/SQL tables and display it in the window, using DBMS_OUTPUT.PUT_LINE. Display these details for the first 15 employees in the PL/SQL tables.
PL/SQL BLOCK
declare
cursor emp_dept_cur is
select * from employees;
e_dept emp_dept_cur%rowtype;
n NUMBER(7,2);
begin
open emp_dept_cur;
select count(*) into n from employees;
for i in 1..n loop
fetch emp_dept_cur into e_dept;
if(e_dept.employee_id < 114) then
dbms_output.put_line('Employee ID : ' || e_dept.last_name || ' Department ID : ' || e_dept.department_id);
end if;
end loop;
close emp_dept_cur;
end;
/
OUTPUT:
Employee ID : King Department ID : 90
Employee ID : Kochhar Department ID : 90
Employee ID : De Haan Department ID : 90
Employee ID : Hunold Department ID : 60
Employee ID : Ernst Department ID : 60
Employee ID : Austin Department ID : 60
Employee ID : Pataballa Department ID : 60
Employee ID : Lorentz Department ID : 60
Employee ID : Greenberg Department ID : 100
Employee ID : Faviet Department ID : 100
Employee ID : Chen Department ID : 100
Employee ID : Sciarra Department ID : 100
Employee ID : Urman Department ID : 100
Employee ID : Popp Department ID : 100
PL/SQL procedure successfully completed.
12. a. Create a PL/SQL block that declares a cursor called DATE_CUR. Pass a parameter of DATE data type to the cursor and print the details of all employees who have joined after that date.
DEFINE P_HIREDATE = 08-MAR-00
b. Test the PL/SQL block for the following hire dates: 08-MAR-00, 25-JUN-97, 28-SEP-98, 07-FEB-99.
create or replace procedure Date_cur_proc (d DATE := ‘8-Mar-2000’)
as
cursor date_cur is
select * from employees;
dt_cur date_cur%rowtype;
begin
for dt_cur in date_cur
loop
if(to_date(dt_cur.hire_date) > to_date(d)) then
dbms_output.put_line(dt_cur.first_name);
end if;
end loop;
end;
/
Procedure created.
// Here it takes date as 8-Mar-2000
SQL> exec Date_cur_proc();
Sundar
Amit
Sundita
PL/SQL procedure successfully completed.
SQL> exec Date_cur_proc('7-Feb-1999');
Donald
Douglas
Luis
Karen
Kevin
Steven
TJ
Ki
Hazel
Gerald
Eleni
Oliver
Danielle
Mattea
David
Sundar
Amit
William
Elizabeth
Sundita
Kimberely
Charles
Martha
Girard
Randall
Vance
PL/SQL procedure successfully completed.
13. Create a PL/SQL block to promote clerks who earn more than 3,000 to the job title SR CLERK and increase their salary by 10%. Use the EMP table for this practice. Verify the results by querying on the EMP table. Hint: Use a cursor with FOR UPDATE and CURRENT OF syntax.
PL/SQL BLOCK
declare
cursor update_cur is
select * from employees for update;
update_ed update_cur%rowtype;
begin
open update_cur;
loop
exit when update_cur%NOTFOUND;
fetch update_cur into update_ed;
if(update_ed.employee_id < 114) then
update employees set salary = salary + salary * 0.10
where current of update_cur;
end if;
end loop;
close update_cur;
end;
/
PL/SQL procedure successfully completed.
n Before executing PL/SQL block
select EMPLOYEE_ID, FIRST_NAME, SALARY
from EMPLOYEES
where EMPLOYEE_ID < 114;
EMPLOYEE_ID FIRST_NAME SALARY
------------------- -------------------- ---------------
100 Steven 25615.89
101 Neena 18144.59
102 Lex 18144.59
103 Alexander 9605.96
104 Bruce 6403.98
105 David 5123.18
106 Valli 5123.18
107 Diana 4482.78
108 Nancy 12807.95
109 Daniel 9605.96
110 John 8752.1
111 Ismael 8218.43
112 Jose Manuel 8325.16
113 Luis 7364.57
14 rows selected.
select EMPLOYEE_ID, FIRST_NAME, SALARY
from EMPLOYEES
where EMPLOYEE_ID < 114;
EMPLOYEE_ID FIRST_NAME SALARY
------------------- -------------------- ---------------
100 Steven 28177.48
101 Neena 19959.05
102 Lex 19959.05
103 Alexander 10566.56
104 Bruce 7044.38
105 David 5635.5
106 Valli 5635.5
107 Diana 4931.06
108 Nancy 14088.75
109 Daniel 10566.56
110 John 9627.31
111 Ismael 9040.27
112 Jose Manuel 9157.68
113 Luis 8101.03
14 rows selected.
MY_REMAINDER:
SQL Bind Variables
<SQL> variable v varchar2(20);
<SQL> begin
:v := 5;
dbms_output.put_line(' The value of the sql bind variable or global variable v is = ‘|| :v);
end;
/
The value of the sql bind variable v is : 5
PL/SQL procedure successfully completed.
14. a. For the exercise below, you will require a table to store the results. You can create the ANALYSIS table yourself or run the labAp_14a.sql script that creates the table for you. Create a table called ANALYSIS with the following three columns:
Column Name : ENAME YEARS SAL
b. Create a PL/SQL block to populate the ANALYSIS table with the information from the EMPLOYEES table. Use an iSQL*Plus substitution variable to store an employee’s last name.
c. Query the EMPLOYEES table to find if the number of years that the employee has been with the organization is greater than five, and if the salary is less than 3,500, raise an exception. Handle the exception with an appropriate exception handler that inserts the following values into the ANALYSIS table: employee last name, number of years of service, and the current salary. Otherwise display Not due for a raise in the window. Verify the results by querying the ANALYSIS table. Use the following test cases to test the PL/SQL block:
| LAST_NAME MESSAGE |
| Not due for a raise |
| Nayer Not due for a raise |
| Fripp Not due for a raise |
| Khoo Due for a raise |
Ø CODE
SQL> create table ANALYSIS
(ENAME VARCHAR2(20), YEARS NUMBER(2), SAL NUMBER(8,2));
Table created.
/* This sql code is stored in plexe14.txt file */
SQL> declare
2 CURSOR emp_cur is
3 select * from employees;
4 emp emp_cur%rowtype;
5
6 CURSOR ana_cur is
7 select * from analysis;
8 ana ana_cur%rowtype;
/* user defined exception declaration*/
9 still_low_sal EXCEPTION;
10
11 lstnm VARCHAR2(35) := &Employee_Last_Name;
12 swt NUMBER(2) := 0;
13 begin
14 open emp_cur;
15 loop
16 exit when emp_cur%NOTFOUND;
17 fetch emp_cur into emp;
/* MY_REMAINDER: These comments will be first removed to insert records into analysis table using employee table. No need to run it every time, hence the code is commented. */
18 /* insert into analysis(ename) values(emp.fname); */
19 /* update analysis set years = floor(months_between(to_date(SYSDATE),to
_date(emp.HIRE_DATE))/12) where ename = emp.fname; */
20 /* update analysis set sal = emp.salary where ename = emp.first_
name; */
21 end loop;
22 close emp_cur;
23 open ana_cur;
24 loop
25 exit when ana_cur%NOTFOUND;
26 fetch ana_cur into ana;
27 /* dbms_output.put_line(ana.years || ana.sal); */
28 if(ana.ename = lstnm) then
29 swt := 1;
30 if(ana.years > 5 AND ana.sal < 3000) then
31 RAISE still_low_sal;
32 else
33 dbms_output.put_line('Not due for a raise');
34 end if;
35 end if;
36 end loop;
37
38 if(swt = 0) then
39 dbms_output.put_line('Employee not found');
40 end if;
41
42 close ana_cur;
43 EXCEPTION
44 WHEN still_low_sal THEN
45 dbms_output.put_line('Exception still_low_sal : The employee must g
et salary increment');
46 end;
48 /
Enter value for employee_last_name: 'Donald'
old 11: lstnm VARCHAR2(35) := &Employee_Last_Name;
new 11: lstnm VARCHAR2(35) := 'Donald';
Exception still_low_sal : The employee must get salary increment
PL/SQL procedure successfully completed.
SQL> /
Enter value for employee_last_name: 'Susan'
old 11: lstnm VARCHAR2(35) := &Employee_Last_Name;
new 11: lstnm VARCHAR2(35) := 'Susan';
Not due for a raise
PL/SQL procedure successfully completed.
SQL> /
Enter value for employee_last_name: 'Girard'
old 11: lstnm VARCHAR2(35) := &Employee_Last_Name;
new 11: lstnm VARCHAR2(35) := 'Girard';
Exception still_low_sal : The employee must get salary increment
PL/SQL procedure successfully completed.
SQL> /
Enter value for employee_last_name: 'Austin'
old 11: lstnm VARCHAR2(35) := &Employee_Last_Name;
new 11: lstnm VARCHAR2(35) := 'Austin';
Employee not found
PL/SQL procedure successfully completed.
16. In this practice, create a program to add a new row to the JOB_HISTORY table for an existing employee.
Note: Disable all triggers on the EMPLOYEES, JOBS, and JOB_HISTORY tables before invoking the procedure in part b. Enable all these triggers after executing the procedure.
a. Create a stored procedure called ADD_JOB_HIST to enter a new row into the JOB_HISTORY table for an employee who is changing his job to the new job ID that you created in question 15b. Use the employee ID of the employee who is changing the job and the new job ID for the employee as parameters. Obtain the row corresponding to this employee ID from the
EMPLOYEES table and insert it into the JOB_HISTORY table. Make hire date of this employee as the start date and today's date as end date for this row in the JOB_HISTORY table. Change the hire date of this employee in the EMPLOYEES table to today's date. Update the job ID of this employee to the job ID passed as parameter (Use the job ID of the job created in question 15b) and salary equal to minimum salary for that job ID + 500. Include exception handling to handle an attempt to insert a nonexistent employee.
b. Disable triggers (Refer to the note at the beginning of this question.)
Execute the procedure with employee ID 106 and job ID SY_ANAL as parameters. Enable the triggers that you disabled.
c. Query the tables to view your changes, and then commit the changes.
PL/SQL BLOCK:
SQL> alter trigger UPDATE_JOB_HISTORY disable;
Trigger altered.
Procedure
create or replace procedure add_job_hist_proc(empid VARCHAR2,jobid VARCHAR2)
as
cursor emp_rec is
select * from employees for update;
emp emp_rec%rowtype;
cursor job_rec is
select * from jobs;
job job_rec%rowtype;
s NUMBER(8,2);
begin
open job_rec;
loop
exit when job_rec%NOTFOUND;
fetch job_rec into job;
if(job.job_id = 'SY_ANAL') then
s := job.min_salary + 500;
end if;
end loop;
close job_rec;
open emp_rec;
loop
exit when emp_rec%NOTFOUND;
fetch emp_rec into emp;
if(emp.employee_id = empid) then
insert into JOB_HISTORY values(emp.employee_id, emp.hire_date, sysdate, emp.job_id, emp.department_id);
update EMPLOYEES set HIRE_DATE = SYSDATE, job_id ='SY_ANAL', salary = s where current of emp_rec;
end if;
end loop;
close emp_rec;
end;
/
SQL> exec add_job_hist_proc(105,'IT_PROG');
PL/SQL procedure successfully completed.
SQL> select * from job_history;
EMPLOYEE_ID START_DAT END_DATE JOB_ID DEPARTMENT_ID
----------- --------- --------- ---------------------------------------- -------------
105 07-MAY-09 13-OCT-11 SY_ANAL 60
102 13-JAN-93 24-JUL-98 IT_PROG 60
101 21-SEP-89 27-OCT-93 AC_ACCOUNT 110
101 28-OCT-93 15-MAR-97 AC_MGR 110
201 17-FEB-96 19-DEC-99 MK_REP 20
114 24-MAR-98 31-DEC-99 ST_CLERK 50
122 01-JAN-99 31-DEC-99 ST_CLERK 50
200 17-SEP-87 17-JUN-93 AD_ASST 90
176 24-MAR-98 31-DEC-98 SA_REP 80
176 01-JAN-99 31-DEC-99 SA_MAN 80
200 01-JUL-94 31-DEC-98 AC_ACCOUNT 90
11 rows selected.
SQL> select employee_id, first_name, hire_date, salary, job_id
2 from employees
3 where employee_id = 105;
EMPLOYEE_ID FIRST_NAME HIRE_DATE SALARY JOB_ID
----------- -------------------- --------- ---------- --------------------------
105 David 13-OCT-11 6500 SY_ANAL
