[ Pobierz całość w formacie PDF ]DEFINE P_LASTNAME = Pataballa
b. Store his or her salary in the salvariable.
c. If the salary is less than 3,000, give the employee a raise of 500 and
display the message s salary updated in the
window.
d. If the salary is more than 3,000, print the employee s salary in the format,
earns & ...& & &
e. Test the PL/SQL block for the last names.
LAST_NAME SALARY
Pataballa 4800
Greenberg 12000
Ernst 6000
Note: Undefine the variable that stores the employee s name at the end of
the script.
Unauthorized reproduction or distribution prohibited. Copyright© 2009, Oracle and/or its affiliates.
Oracle Database 10g: Develop PL/SQL Program Units APS-7
use this Student Guide.
Arek Puchala (arekpuchala@o2.pl) has a non-transferable license to
Additional Practices 7 and 8: Solutions
DECLARE
SAL NUMBER(7,2);
LASTNAME EMPLOYEES.LAST_NAME%TYPE;
BEGIN
SELECT salary INTO SAL
FROM employees
WHERE last_name = INITCAP('&&P_LASTNAME') FOR
UPDATE of salary;
LASTNAME := INITCAP('&P_LASTNAME');
IF SAL
UPDATE employees SET salary = salary + 500
WHERE last_name = INITCAP('&P_LASTNAME') ;
DBMS_OUTPUT.PUT_LINE (LASTNAME || '''s salary
updated');
ELSE
DBMS_OUTPUT.PUT_LINE (LASTNAME || ' earns '
||
TO_CHAR(SAL));
END IF;
END;
/
SET SERVEROUTPUT OFF
UNDEFINE P_LASTNAME
8. Write a PL/SQL block to store the salary of an employee in an iSQL*Plus
substitution variable. In the executable part of the program, perform the following:
" Calculate the annual salary as salary * 12.
" Calculate the bonus as indicated below:
Annual Salary Bonus
>= 20,000 2,000
19,999 - 10,000 1,000
" Display the amount of the bonus in the window in the following format:
The bonus is $& & & & & & ..
" Test the PL/SQL for the following test cases:
SALARY BONUS
5000 2000
1000 1000
15000 2000
Unauthorized reproduction or distribution prohibited. Copyright© 2009, Oracle and/or its affiliates.
Oracle Database 10g: Develop PL/SQL Program Units APS-8
use this Student Guide.
Arek Puchala (arekpuchala@o2.pl) has a non-transferable license to
Additional Practices 8 and 9: Solutions
SET SERVEROUTPUT ON
DEFINE P_SALARY = 5000
DECLARE
SAL NUMBER(7,2) := &P_SALARY;
BONUS NUMBER(7,2);
ANN_SALARY NUMBER(15,2);
BEGIN
ANN_SALARY := SAL * 12;
IF ANN_SALARY >= 20000 THEN
BONUS := 2000;
ELSIF ANN_SALARY =10000 THEN
BONUS := 1000;
ELSE
BONUS := 500;
END IF;
DBMS_OUTPUT.PUT_LINE ('The Bonus is $ ' ||
TO_CHAR(BONUS));
END;
/
SET SERVEROUTPUT OFF
9. a. Execute thelab_ap_09_a.sqlscript to create a temporary table called
emp. 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.
SET SERVEROUTPUT ON
DEFINE P_EMPNO = 100
DEFINE P_NEW_DEPTNO = 10
DEFINE P_PER_INCREASE = 2
b. Update the department ID of the employee with the new department number,
and update the salary with the new salary. Use the emptable for the updates.
After the update is complete, display the message Update complete in the
window. If no matching records are found, display the message No Data
Found. Test the PL/SQL block for the following test cases.
EMPLOYEE_ID NEW_DEPARTMENT_ID % INCREASE MESSAGE
100 20 2 Update
Complete
10 30 5 No Data
found
126 40 3 Update
Complete
Unauthorized reproduction or distribution prohibited. Copyright© 2009, Oracle and/or its affiliates.
Oracle Database 10g: Develop PL/SQL Program Units APS-9
use this Student Guide.
Arek Puchala (arekpuchala@o2.pl) has a non-transferable license to
Additional Practices 9 and 10: Solutions
DECLARE
EMPNO emp.EMPLOYEE_ID%TYPE := &P_EMPNO;
NEW_DEPTNO emp.DEPARTMENT_ID%TYPE :=
&P_NEW_DEPTNO;
PER_INCREASE NUMBER(7,2) := & P_PER_INCREASE;
BEGIN
UPDATE emp
SET department_id = NEW_DEPTNO,
salary = salary + (salary * PER_INCREASE/100)
WHERE employee_id = EMPNO;
IF SQL%ROWCOUNT = 0 THEN
DBMS_OUTPUT.PUT_LINE ('No Data Found');
ELSE
DBMS_OUTPUT.PUT_LINE ('Update Complete');
END IF;
END;
/
SET SERVEROUTPUT OFF
10. Create a PL/SQL block to declare an EMP_CURcursor to select the employee
name, salary, and hire date from the employeestable. 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.
SET SERVEROUTPUT ON
DECLARE
CURSOR EMP_CUR IS
SELECT last_name,salary,hire_date FROM EMPLOYEES;
ENAME VARCHAR2(25);
SAL NUMBER(7,2);
HIREDATE DATE;
BEGIN
OPEN EMP_CUR;
FETCH EMP_CUR INTO ENAME,SAL,HIREDATE;
WHILE EMP_CUR%FOUND
LOOP
IF SAL > 15000 AND HIREDATE >= TO_DATE('01-FEB-
1988','DD-MON-
YYYY') THEN
DBMS_OUTPUT.PUT_LINE (ENAME || ' earns ' ||
TO_CHAR(SAL)||
' and joined the organization on ' ||
TO_DATE(HIREDATE,'DD-
Mon-YYYY'));
END IF;
Unauthorized reproduction or distribution prohibited. Copyright© 2009, Oracle and/or its affiliates.
Oracle Database 10g: Develop PL/SQL Program Units APS-10
use this Student Guide.
Arek Puchala (arekpuchala@o2.pl) has a non-transferable license to
Additional Practices 10 and 11: Solutions
FETCH EMP_CUR INTO ENAME,SAL,HIREDATE;
END LOOP;
CLOSE EMP_CUR;
END;
/
SET SERVEROUTPUT OFF
11. Create a PL/SQL block to retrieve the last name and department ID of each
employee from the employeestable for those employees whose EMPLOYEE_ID
is less than 114. From the values retrieved from the employeestable, 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 them in the window, using DBMS_OUTPUT.PUT_LINE. Display these
details for the first 15 employees in the PL/SQL tables.
SET SERVEROUTPUT ON
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
TRACK NUMBER := 15;
BEGIN
FOR emprec in Namedept
LOOP
i := i +1;
Tename(i) := emprec.last_name;
Tdept(i) := emprec.department_id;
END LOOP;
Unauthorized reproduction or distribution prohibited. Copyright© 2009, Oracle and/or its affiliates.
Oracle Database 10g: Develop PL/SQL Program Units APS-11
use this Student Guide.
Arek Puchala (arekpuchala@o2.pl) has a non-transferable license to
Additional Practices 11 and 12: Solutions
FOR i IN 1..TRACK
LOOP
DBMS_OUTPUT.PUT_LINE ('Employee Name: ' ||
Tename(i) || ' Department_id: ' || Tdept(i));
END LOOP;
END;
/
SET SERVEROUTPUT OFF
12. a. Create a PL/SQL block that declares a cursor called DATE_CUR. Pass a
parameter of theDATEdata type to the cursor and print the details about all the
employees who have joined after that date.
SET SERVEROUTPUT ON
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.
DECLARE
CURSOR DATE_CURSOR(JOIN_DATE DATE) IS
SELECT employee_id,last_name,hire_date FROM
employees
WHERE HIRE_DATE >JOIN_DATE ;
EMPNO employees.employee_id%TYPE;
ENAME employees.last_name%TYPE;
HIREDATE employees.hire_date%TYPE;
HDATE employees.hire_date%TYPE := '&P_HIREDATE';
BEGIN
OPEN DATE_CURSOR(HDATE);
LOOP
FETCH DATE_CURSOR INTO EMPNO,ENAME,HIREDATE;
EXIT WHEN DATE_CURSOR%NOTFOUND;
DBMS_OUTPUT.PUT_LINE (EMPNO || ' ' || ENAME || '
' ||
HIREDATE);
END LOOP;
END;
/
SET SERVEROUTPUT OFF;
Unauthorized reproduction or distribution prohibited. Copyright© 2009, Oracle and/or its affiliates.
Oracle Database 10g: Develop PL/SQL Program Units APS-12
use this Student Guide.
[ Pobierz całość w formacie PDF ]zanotowane.pldoc.pisz.plpdf.pisz.plkajaszek.htw.pl
