Declare

DECLARE

v_YEAR NUMBER(4): = & P_YEAR;

V_REMAINDER1 NUMBER(5, 2);

V_REMAINDER2 NUMBER(5, 2);

V_REMAINDER3 NUMBER(5, 2);

BEGIN

v_REMAINDER1: = MOD(v_YEAR, 4);

v_REMAINDER2: = MOD(v_YEAR, 100);

v_REMAINDER3: = MOD(v_YEAR, 400);

IF ((v_REMAINDER1 = 0 AND v_REMAINDER2 < > 0) OR

v_REMAINDER3 = 0) THEN

DBMS_OUTPUT.PUT_LINE(v_YEAR || ' is a leap year');

ELSE

DBMS_OUTPUT.PUT_LINE(v_YEAR || ' is not a leap

year');

END IF;

END;

2)

Write a PL/SQL block which will accept the employee number and print the annual_salary as well as the bonus.

The PL/SQL block should:

• Calculate the annual salary as salary * 12

• Calculate the bonus as indicated in the following table:

DECLARE

V_EMP_ID NUMBER: = & EMPLOYEE_ID;

V_SAL NUMBER(7, 2);

V_BONUS NUMBER(7, 2);

V_ANN_SALARY NUMBER(15, 2);

BEGIN

SELECT SALARY INTO V_SAL FROM EMPLOYEES WHERE EMPLOYEE_ID = V_EMP_ID;

V_ANN_SALARY: = V_SAL * 12;

DBMS_OUTPUT.PUT_LINE('Annual Salary is: '||V_ANN_SALARY);

IF V_ANN_SALARY > = 20000 THEN

V_BONUS: = 2000;

ELSIF V_ANN_SALARY < = 19999 AND V_ANN_SALARY > =10000 THEN

V_BONUS: = 1000;

ELSE

V_BONUS: = 500;

END IF;

DBMS_OUTPUT.PUT_LINE ('The Bonus is $ ' || TO_CHAR(V_BONUS));

END;

3)

Declare a cursor named EMP_CUR to select the employee’s last name, salary, and hire

date from the EMPLOYEES table

Process each row from the cursor, and if the salary is greater than 15, 000 and the hire

date is later than 01-FEB-1988, display the employee name, salary, and hire date in

the format shown in the following sample output:

DECLARE

CURSOR C_EMP_CUR IS

SELECT last_name, salary, hire_date FROM EMPLOYEES;

V_ENAME VARCHAR2(25);

v_SAL NUMBER(7, 2);

V_HIREDATE DATE;

BEGIN

OPEN C_EMP_CUR;

FETCH C_EMP_CUR INTO V_ENAME, V_SAL, V_HIREDATE;

WHILE C_EMP_CUR%FOUND

LOOP

IF V_SAL > 15000 AND V_HIREDATE > =

TO_DATE('01-FEB-1988', 'DD-MON-YYYY') THEN

DBMS_OUTPUT.PUT_LINE (V_ENAME || ' earns '

|| TO_CHAR(V_SAL)|| ' and joined the organization on '

|| TO_DATE(V_HIREDATE, 'DD-Mon-YYYY'));

END IF;

FETCH C_EMP_CUR INTO V_ENAME, V_SAL, V_HIREDATE;

END LOOP;

CLOSE C_EMP_CUR;

END;

4)

Create a PL/SQL block to retrieve and output the last name and department ID of each employee from the EMPLOYEES table for those employees whose EMPLOYEE_ID is less than 115.

In the PL/SQL block, use a cursor FOR loop strategy instead of the OPEN / FETCH /CLOSE cursor methods used in the previous practice.

1. In the declarative section:

• Create two associative arrays. The unique key column for both arrays should be of the BINARY INTEGER data type. One array holds the employee’s last name and the other holds the department ID.

• Declare a counter variable to be used in the executable section

• Declare a cursor that selects the last name and department ID for employees whose ID is less than 115

DECLARE

TYPE Table_Ename IS table of employees.last_name%TYPE

INDEX BY BINARY_INTEGER;

TYPE Table_dept IS table of employees.department_id%TYPE

INDEX BY BINARY_INTEGER;

Tename Table_Ename;

Tdept Table_dept;

i BINARY_INTEGER: =0;

CURSOR Namedept IS SELECT last_name, department_id

FROM employees WHERE employee_id < 115;

BEGIN

FOR emprec in Namedept

LOOP

i: = i +1;

Tename(i): = emprec.last_name;

Tdept(i): = emprec.department_id;

DBMS_OUTPUT.PUT_LINE ('Employee: ' || Tename(i) ||

' is in department number: ' || Tdept(i));

END LOOP;

END;

5)

Create a table:

CREATE TABLE analysis

(ename Varchar2(20), years Number(2), sal Number(8, 2)

);

Write a PL/SQL block that handles an exception, as follows:

1. Declare variables for the employee last name, salary, and hire date. Use a substitution

variable for the employee last name. Then, query the employees table for the

last_name, salary, and hire_date of the specified employee.

2. If the employee has been with the organization for more than five years, and if that

employee’s salary is less than 3, 500, raise an exception. In the exception handler,

perform the following:

• Output the following information: employee last name and the message “due

for a raise, ” similar to the following:

3. If there is no exception, output the employee last name and the message “not due for a raise, ” similar to the following:

Verify the results by querying the analysis table. Use the following test cases to test the PL/SQL block.