Главная страница Случайная страница КАТЕГОРИИ: АвтомобилиАстрономияБиологияГеографияДом и садДругие языкиДругоеИнформатикаИсторияКультураЛитератураЛогикаМатематикаМедицинаМеталлургияМеханикаОбразованиеОхрана трудаПедагогикаПолитикаПравоПсихологияРелигияРиторикаСоциологияСпортСтроительствоТехнологияТуризмФизикаФилософияФинансыХимияЧерчениеЭкологияЭкономикаЭлектроника |
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: Insert the last name, years of service, and salary into the analysis table.
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.
|