BASIC STRUCTURES
-
Напишете PL/SQL блок, който въвежда номера в таблицата MESSAGES.
-
Въведената естествените числа от 1 до 10, без 6 и 8;
-
Потвърдете операцията преди края на блока;
-
Изкарайте резултата от таблицата MESSAGES, за да се уверите че PL/SQL блока ви работи коректно;
############################3
BEGIN
FOR i in 1..10 LOOP
IF i = 6 or i = 8 THEN
null;
ELSE
INSERT INTO messages(results)
VALUES (i);
END IF;
COMMIT;
END LOOP;
END;
-
Създайте PL/SQL блок, които изчислява комисионната за даден работник на база на неговата заплата.
-
Стартирайте скрипта BS_2.sql, за да въведете нов работник в таблицата ЕМР;
Забележка: Може да има въведени работници със неизвестна заплата.
-
Приемете номера на работника като потребителски вход чрез субституционна променлива на SQL*Plus;
-
Ако работникът има заплата по-малка от 1000$, установете комисионната му на 10% от заплатата му;
-
Ако работника има заплата между 1000$ и 1500$, установете комисионната му на 15% от заплатата му;
-
Ако работника има заплата над 1500$, установете комисионната му на 20% от заплатата му;
-
Ако заплата на работника е неизвестна, установете комисионната му на 0.
-
Потвърдете извършените действия;
-
Тествайте PL/SQL блока за всички случай, използвайки следната тестова таблица:
-
Employee Number
|
Salary
|
Resulting Commission
|
7369
|
800
|
80
|
7934
|
1300
|
195
|
7499
|
1600
|
320
|
8000
|
NULL
|
0
|
##########################################
ACCEPT p_empno PROMPT 'Please enter employee number: '
DECLARE
v_empno emp.empno%TYPE := &p_empno;
v_sal emp.sal%TYPE;
v_comm emp.comm%TYPE;
BEGIN
SELECT sal
INTO v_sal
FROM emp
WHERE empno = v_empno;
IF v_sal < 1000 THEN
v_comm := .10;
ELSIF v_sal BETWEEN 1000 and 1500 THEN
v_comm := .15;
ELSIF v_sal > 1500 THEN
v_comm := .20;
ELSE
v_comm := 0;
END IF;
UPDATE emp
SET comm = NVL(sal,0) * v_comm
WHERE empno = v_empno;
COMMIT;
END;
SELECT empno, sal, comm
FROM emp
WHERE empno IN (7369, 7934, 7499, 8000)
ORDER BY comm
COMPOSITE_DATAYPES
-
.Създайте PL/SQL блок, за да върнете имената на всеки отдел от таблицата DEPT и да отпечатите името на отдела на екрана, като за целта използвате PL/SQL таблица.
-
Декларирайте PL/SQL таблица MY_DEPT_TABLE, за да съхраните временно имената на отделите;
-
Използвайте цикъл, за да вземете имената всички отдели съхранени в таблицата DEPT и да ги запазите в PL/SQL таблицата. Всеки номер на отдел е умножен по 10;
-
Използвайте друг цикъл, за да вземете имената на отделите от PL/SQL таблицата и да ги отпечатите на екрана, посредством DBMS_OTPUT.PUT_LINE;
SQL> START sol1
ACCOUNTING
RESEARCH
SALES
OPERATIONS
#######################
SET SERVEROUTPUT ON
DECLARE
TYPE dept_table_type is table of dept.dname%TYPE
INDEX BY BINARY_INTEGER;
my_dept_table dept_table_type;
v_count NUMBER (2);
BEGIN
SELECT COUNT(*)
INTO v_count
FROM dept;
FOR i IN 1..v_count
LOOP
SELECT dname
INTO my_dept_table(i)
FROM dept
WHERE deptno = i*10;
END LOOP;
FOR i IN 1..v_count
LOOP
DBMS_OUTPUT.PUT_LINE (my_dept_table(i));
END LOOP;
END;
/
SET SERVEROUTPUT OFF
-
Напишете PL/SQL блок, за да отпечатите информацията за дадена поръчка.
-
Декларирайте PL/SQL запис на база на структурата на таблицата ORD;
-
Използвайте SQL*Plus субституционена променлива, за да извлечете всичката информация за определена поръчка и съхранете тази информация в PL/SQL записа ;
-
Използвайте DBMS_OTPUT.PUT_LINE, за да отпечатите селектираната информация за поръчката.
Please enter an order number: 615
Order 615 was placed on 01-FEB-87 and shipped on 06-FEB-87 for a total of $710.00
#####################
SET SERVEROUTPUT ON
SET VERIFY OFF
ACCEPT p_ordid PROMPT 'Please enter an order number: '
DECLARE
ord_record ord%ROWTYPE;
BEGIN
SELECT *
INTO ord_record
FROM ord
WHERE ordid = &p_ordid;
DBMS_OUTPUT.PUT_LINE ('Order ' || TO_CHAR(ord_record.ordid) ||
' was placed on ' || TO_CHAR(ord_record.orderdate)
|| ' and shipped on ' ||
TO_CHAR(ord_record.shipdate) || ' for a total of '
|| TO_CHAR(ord_record.total, '$99,999.99'));
END;
/
SET SERVEROUTPUT OFF
-
Модифицирайте блока от упражнение 1 да връща цялата информация за всеки отдел от таблицата DEPT и да я отпечава на екрана, използвайки PL/SQL таблица от записи:
-
Декларирайте PL/SQL таблица MY_DEPT_TABLE, за да съхраните временно номера, името и мястото на всички отдели;
-
Използвайте цикъл, за да вземете информацията от таблицата DEPT и да я запазите в PL/SQL таблицата. Всеки номер на отдел е умножен по 10;
-
Използвайте друг цикъл, за да вземете информацията от PL/SQL таблицата и да я отпечатите на екрана, посредством DBMS_OTPUT.PUT_LINE ;
Dept. 10, ACCOUNTING is located in NEW YORK
Dept. 20, RESEARCH is located in DALLAS
Dept. 30, SALES is located in CHICAGO
Dept. 40, OPERATIONS is located in BOSTON
-
за всички оставали клиенти отстъпката е 5%;
-
Обходете PL/SQL таблицата и изкарайте на екрана номер на клиент разликата между платената сума и сумата с отстъпка, както и процентът на сума му с отстъпка, спрямо общата сума с отстъпка за всички клиенти.
#################################################
SET SERVEROUTPUT ON
DECLARE
TYPE dept_table_type is table of dept%ROWTYPE
INDEX BY BINARY_INTEGER;
my_dept_table dept_table_type;
v_count NUMBER (2);
BEGIN
SELECT COUNT(*)
INTO v_count
FROM dept;
FOR i IN 1..v_count
LOOP
SELECT *
INTO my_dept_table(i)
FROM dept
WHERE deptno = i*10;
END LOOP;
FOR i IN 1..v_count
LOOP
DBMS_OUTPUT.PUT_LINE ('Dept. ' || my_dept_table(i).deptno || ', ' || my_dept_table(i).dname ||
' is located in ' || my_dept_table(i).loc);
END LOOP;
END;
/
SET SERVEROUTPUT OFF
EXPLICIT CURSORS
2 Създайте PL/SQL блок, който определя “топ” работниците съобразно техните заплати.
-
Въведете номер n като потребителски вход чрез субституционен параметър на SQL*Plus;
-
С помощта на цикъл, вземете фамилиите и заплатите на първите “топ” n работника от таблицата EMP ;
-
Съхранете имената на работниците в таблицата TOP_DOGS;
-
Приемете че няма двама работници с еднакви заплати;
-
Тествайте различни специални случаи като n=0 или когато n е по-голямо от броя на работниците в таблицата EMP. Изпразвайте таблицата TOP_DOGS след всеки тест.
#################################################
DELETE FROM top_dogs;
ACCEPT p_num PROMPT 'Please enter the number of top money makers: '
DECLARE
v_num NUMBER(3) := &p_num;
v_ename emp.ename%TYPE;
v_sal emp.sal%TYPE;
CURSOR emp_cursor IS
SELECT ename, sal
FROM emp
WHERE sal IS NOT NULL
ORDER BY sal DESC;
BEGIN
OPEN emp_cursor;
FETCH emp_cursor INTO v_ename, v_sal;
WHILE emp_cursor%ROWCOUNT <= v_num AND
emp_cursor%FOUND LOOP
INSERT INTO top_dogs (name, salary)
VALUES (v_ename, v_sal);
FETCH emp_cursor INTO v_ename, v_sal;
END LOOP;
CLOSE emp_cursor;
COMMIT;
END;
/
SELECT * FROM top_dogs;
-
Разгледайте случая, когато няколко работника имат еднакви заплати. Ако даден човек е включен в списъка, то и всички хора, които имат същите заплати, също трябва да бъдат включени.
-
Например, ако потребителят въведе стойност 2 за n, тогава King, Ford и Scott трябва да бъдат изкарани. (Тримата работника са свързани със втората по големина заплата);
-
Ако потребителят въведе стойност 3, тогава King, Ford, Scott и Jones трябва да бъдат изкарани;
-
Изтриите всички редове от TOP_DOGS и тествайте упражнението.
###########################################
DELETE FROM top_dogs;
SET ECHO OFF
ACCEPT p_num PROMPT 'Please enter the number of top money makers: '
DECLARE
v_num NUMBER(3) := &p_num;
v_ename emp.ename%TYPE;
v_current_sal emp.sal%TYPE;
v_last_sal emp.sal%TYPE;
CURSOR emp_cursor IS
SELECT ename, sal
FROM emp
WHERE sal IS NOT NULL
ORDER BY sal DESC;
BEGIN
OPEN emp_cursor;
FETCH emp_cursor INTO v_ename, v_current_sal;
WHILE emp_cursor%ROWCOUNT <= v_num AND emp_cursor%FOUND LOOP
INSERT INTO top_dogs (name, salary)
VALUES (v_ename, v_current_sal);
v_last_sal := v_current_sal;
FETCH emp_cursor INTO v_ename, v_current_sal;
IF v_last_sal = v_current_sal THEN
v_num := v_num + 1;
END IF;
END LOOP;
CLOSE emp_cursor;
COMMIT;
END;
/
SET ECHO ON
SELECT * FROM top_dogs;
-
Създайте PL/SQL блок, който определя общата сума на заплатите на работниците по отдели и изкарва получените резултати на екрана във следният вид:
Total Salary for Department 10 IS 3750
Total Salary for Department 20 IS 4875
Total Salary for Department 30 IS 9400
Сподели с приятели: |