SQL> print todays_date
TODAYS_DATE
--------------------------------
January,29/1998
-
Тествайте втората версия на PRINT_IT със следните команди
SQL> variable g_emp_sal number
SQL> execute :g_emp_sal := over_load.print_it('33,600')
PL/SQL procedure successfully completed.
SQL> print g_emp_sal
G_EMP_SAL
---------
33600
##############################################
CREATE OR REPLACE PACKAGE BODY over_load
IS
FUNCTION print_it(v_arg IN DATE)
RETURN VARCHAR2
IS
BEGIN
RETURN to_char(v_arg,'FmMonth,dd/yyyy');
END print_it;
FUNCTION print_it(v_arg IN VARCHAR2)
RETURN NUMBER
IS
BEGIN
RETURN to_number(v_arg,'999,999.00');
-- or use the NLS characters for grands and decimals
-- RETURN to_number(v_arg,'999G999D00');
END print_it;
END over_load;
/
###################################################
CREATE OR REPLACE PACKAGE over_load IS
FUNCTION print_it(v_arg IN DATE)
RETURN VARCHAR2;
FUNCTION print_it(v_arg IN VARCHAR2)
RETURN NUMBER;
END over_load;
/
-
Създайте нов пакет, наречен CHK_PACK, за да реализирате ново бизнес правило (ограничение).
-
Създайте процедура, наречена CHK_DEPT_JOB, за да проверите дали дадена комбинация от номер на отдел и длъжност (job) е валидна комбинация. В този случай “валидна комбинация” означава, че такава комбинация вече трябва да съществува в таблицата EMP.
Забележки:
-
Използвайте PL/SQL таблица, за да съхраните валидните “номер на отдел/длъжност” комбинации;
-
PL/SQL таблицата трябва да бъде инициализирана само веднъж;
-
Вдигнете програмна грешка с подходящо съобщение, ако комбинацията е невалидна.
-
Тествайте вашата CHK_DEPT_JOB пакетна процедура, като изпълните следната команда:
SQL> execute chk_pack.chk_dept_job(20,'CLERK')
-
Тествайте вашата CHK_DEPT_JOB пакетна процедура, като изпълните следната команда:
SQL> execute chk_pack.chk_dept_job(40,'CLERK')
################################################
CREATE OR REPLACE PACKAGE BODY chk_pack
IS
i number :=0;
TYPE emp_table_type IS TABLE OF VARCHAR2(50)
INDEX BY BINARY_INTEGER;
deptno_job emp_table_type;
CURSOR emp_cur IS SELECT deptno,job
FROM emp;
PROCEDURE chk_dept_job(v_deptno in emp.deptno%type,
v_job in emp.job%type)
IS
v_deptno_job varchar2(50);
BEGIN
v_deptno_job := to_char(v_deptno) || v_job;
FOR k in 0..i
LOOP
IF v_deptno_job = deptno_job(k)
THEN EXIT;
ELSIF (v_deptno_job != deptno_job(k) and k >= i-1)
THEN RAISE_APPLICATION_ERROR(-20500,
'Not a valid job for this dept');
END IF;
END LOOP;
END chk_dept_job;
BEGIN
FOR emp_rec in emp_cur
LOOP
deptno_job(i) := to_char(emp_rec.deptno) || emp_rec.job;
i := i + 1;
END LOOP;
END chk_pack;
/
#########################################################
CREATE OR REPLACE PACKAGE chk_pack IS
PROCEDURE chk_dept_job(v_deptno in emp.deptno%type,
v_job in emp.job%type);
END chk_pack;
/
ORACLE SUPPLIED PACKAGES
-
Създайте процедура DROP_TABLE, която изтрива таблица от базата данни, чието име се подадва като входен параметър. Използвайте процедурите и функциите от пакета DBMS_SQL.
Тествайте процедурата DROP_TABLE като създадете нова таблица наречена EMP_DUP, която е копие на таблицата EMP и след това изпълнете процедурата DROP_TABLE, за да изтриете таблицата EMP_DUP.
######################################################
/*
COPY FROM ora1/oracle@db TO ora1/oracle@db CREATE emp_dup USING select * from emp;
*/
CREATE TABLE emp_dup
AS
SELECT *
FROM emp
##############################################
CREATE OR REPLACE PROCEDURE drop_table
(v_table_name IN VARCHAR2)
IS
dyn_cur number;
dyn_err varchar2(255);
BEGIN
dyn_cur := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(dyn_cur,'drop table '||
v_table_name,DBMS_SQL.NATIVE);
DBMS_SQL.CLOSE_CURSOR(dyn_cur);
EXCEPTION
WHEN OTHERS THEN dyn_err := sqlerrm;
DBMS_SQL.CLOSE_CURSOR(dyn_cur);
RAISE_APPLICATION_ERROR(-20600,dyn_err);
END drop_table;
/
-
Създайте друга процедура DROP_TABLE2, която изтрива таблица от базата данни, чието име се подадва като входен параметър. Използвайте оператора EXECUTE IMMEDIATE.
Повторете горния тест
##################################################
CREATE PROCEDURE DROP_TABLE2
(v_table_name IN VARCHAR2)
IS
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE '||v_table_name;
END;
/
-
Създайте процедура наречена ANALYZE_OBJECT, която анализира даден обект, определен от входните параметри. Използвайте пакета DBMS_DDL и метода COMPUTE.
Тествайте процедурата с таблицата EMP. Уверете се, че процедурата ANALYZE_OBJECT работи, като изкарате съдържанието на колоната LAST_ANALYZED от таблицата USER_TABLES.
########################################
CREATE OR REPLACE procedure analyze_object
(v_obj_type IN VARCHAR2,
v_obj_name IN VARCHAR2)
is
begin
DBMS_DDL.ANALYZE_OBJECT(
v_obj_type,
USER,
UPPER(V_OBJ_NAME),
'COMPUTE');
END;
/
-
Като използвате пакета DBMS_JOB направете така, че процедурата ANALYZE_OBJECT да се старитира автоматично след определен период от време. Анализирайте таблицата DEPT като стартирате подходящо дефинирана задача (job) след 5 минути.
Уверете се, че задачата ще бъде изпълнена като използвате USER_JOBS.
####################################################
VARIABLE JOBNO NUMBER
EXECUTE DBMS_JOB.SUBMIT(:JOBNO, -
'ANALYZE_OBJECT (''TABLE'',''DEPT'');', -
SYSDATE + 1/288)
PRINT JOBNO
SELECT JOB, NEXT_DATE, NEXT_SEC, WHAT FROM USER_JOBS
/
Допълнителни задачи:
-
Да се функции, която връща всички имена на таблици (отделени със запетай) от работната ви схема, който отговарят на следните условия :
-
в първичния им ключ участва точно една колона, която съдържа в името си “ID” ;
-
в таблицата има запис със стойност 100 в съответната PK колона ;
Забележка: Разгледайте таблиците user_constraints и user_cons_columns.
Отг: CUSTOMER.
Въведете в таблицата ORD запис с ORDID =100, за да тествате по-добре функцията си.
(следват задачи с по-висока трудност )
-
Създайте функция GEN_FK, която всяка таблиците в работната ви схема, такава че съдържа колони с коментар от вида {GEN_FK: }, създава външен ключ, рефериращ първичния ключ на таблицата . Например ако в коментара на колоната DEPTNO от таблицата EMP се съдържа низа {GEN_FK: DEPT} , функцията ще създаде ограничение EMP_DEPTNO_FK, изглеждащо по следния начин:
Alter table EMP add constraint EMP_DEPTNO_FK foreign key (DEPTNO) references DEPT (DEPTNO);
-
Съзайте таблица GEN_FK_LOG с 3 колони: table_name,column_name и text, status.
-
За всяка таблица от работната схема, функцията записва в таблицата GEN_FK_LOG името на таблицата (“child” таблицата), колоната от тази таблица участваща в ограничението и командата за неговото създаване. В колоната status се записва “not created”;
-
Функцията приема параметър наречен create_FK от булев тип. Ако стойността му е истина, тя проверява дали вече подобно ограничение съществува и ако такова няма го създава. Ако в таблицата имаме данни, които нарушават ограничението, функцията създава ограничението в състояние “enable novalidate”. Същевременно в колоната status на GEN_FK_LOG се записва “enable novalidate”. За ограниченията при които всичко е наред се записва “enable validate” .
-
Функцията връща като резултат броя на създадените ограниченията в състояние enable novalidate”.
-
Ако някое от ограниченията не може да бъде създадено поради някаква причина: например типа на колоната с коментар {GEN_FK: } не съответства на типа на колоната първичен ключ в родителската таблицата, пък първичния ключ на таблицата е съставен и т.н., то функцията не вдига грешка, а записва в колоната status на GEN_FK_LOG “ERROR OCCUR!”. Непосредствено преди приключването на функцията, след като за всички таблици имащи колони с коментар, съдържащ търсения образец са отбелязани в GEN_FK_LOG функцията вдига грешка със съобщение ‘Failed to create the following constraints: EMP_DEPTNO_FK, ITEM_ORDID_FK, …”
-
Модифицирайте функция GEN_FK, така че тя да проверява дали освен първичния ключ в родителската таблица има и други колони с unique ограничение, които могат да участват във външен ключ рефериран от колоната с коментар {GEN_FK: } (типът на данните им съвпада). Ако в реферираната таблица има само една подходяща колона за създаване на FK ограничението дори тя да не е PK, ограничението са създава, в противен случай (и при условие че образецът за създаване на FK не е зададен във вида GEN_FK: .}) в колоната text на GEN_FK_LOG вместо команда се записва съобщението :”Referenced column is not determinate!” .
-
Модифицирайте функция GEN_FK, така че тя да да работи и за съставни FK ограничения.
CREATING DATABASE TRIGGERS
-
Модификации (вмъкване, промяна или триене) на данните от таблиците в базата данни са позволени само по време на нормалното работно време – от 8:45 сутринта до 5:30 следобяд, в дните от Понеделник до Петък.
-
Създайте съхранена процедура наречена SECURE_DML, която в часовете извън разрешения интервал връща съобщение:
“You may only make data changes during normal office hours.”
#######################################################
CREATE OR REPLACE PROCEDURE secure_dml
IS
BEGIN
IF TO_CHAR (SYSDATE, 'HH24:MI') NOT BETWEEN '08:45' AND '17:30'
OR TO_CHAR (SYSDATE, 'DY') IN ('SAT', 'SUN') THEN
RAISE_APPLICATION_ERROR (-20205,
'You may only make changes during normal office hours.');
END IF;
END secure_dml;
/
-
Създайте тригер на ниво оператор, който извиква горната процедура.
-
Тествайте процедурата като временно модифицирате часовете в процедурата и се опитате да вмъкнете нов ред в таблицата PRODUCT.
#####################################################
CREATE OR REPLACE TRIGGER secure_prod
BEFORE INSERT OR UPDATE OR DELETE ON product
BEGIN
secure_dml;
END secure_prod;
/
-
Комисионната на продавачите (SALESMAN) трябва да се променя при всяка нова поръчка или при промяна на съществуваща поръчка. Техните комисионни се съхраняват в колоната COMM от таблицата EMP. Всеки продавач обслужва конкретен клиент от таблицата CUSTOMER (полето REPID).
-
Създайте процедура, която променя съответните комисионни на продавачи. Използвайте параметри, за да предадете на процедурата номера на клиента, старата и новата обща сума на поръчката от извикващия тригер. Процедурата трябва след това да намери подходящия номер на работник от таблицата CUSTOMER и да промени записа на продавача в таблицата EMP, добавяйки нова комисионна към съществуващата стойност. Допуснете за това упражнение фиксирана комисионна от 5 процента.
-
Създайте тригер на ниво ред върху таблицата ORD, които извиква процедурата, подавайки и необходимите параметри.
-
Използвайте двете пакетни процедури UPD_ITEM и ADD_ITEM от пакета ITEM_PACK, за да промените поръчка на клиент. В директорията labs има скрипт с ITEM_PACK.sql, който ще създаде пакета.
Използвайте двете процедури, за да промените поръчка на клиент:
SQL> EXECUTE ITEM_PACK.ADD_ITEM (610, 4, 102130, 3.4,1)
SQL> EXECUTE ITEM_PACK.UPD_ITEM (610, 2, -1)
-
След като модифицирате поръчка 610, проверете дали комисионната на WARD се е увеличила с 0.03. Оргинална комисионна е 500.
#################################################33
CREATE OR REPLACE TRIGGER update_emp_comm
AFTER INSERT OR UPDATE OR DELETE ON ord
FOR EACH ROW
BEGIN
update_comm (:new.custid, :old.total, :new.total);
END;
/
################################################################
CREATE OR REPLACE PROCEDURE update_comm
(v_custid IN NUMBER,
v_old_tot IN NUMBER,
v_new_tot IN NUMBER)
IS
v_repid NUMBER;
v_comm NUMBER;
BEGIN
SELECT repid
INTO v_repid
FROM customer
WHERE custid = v_custid;
v_comm := (NVL(v_new_tot, 0) - NVL(v_old_tot, 0)) * .05;
UPDATE emp
SET comm = comm + v_comm
WHERE empno = v_repid;
EXCEPTION
-- No exception section has been explicitly advised,
-- it has been included for completeness.
WHEN no_data_found
THEN RAISE_APPLICATION_ERROR (-20203,
'No salesman for this customer');
END update_comm;
/
Допълнителни задачи:
-
Създайте тригер TRG_EMP_SAL_UPD, които при промяна на заплатата на работник, ако длъжността му е ‘Programmer’ не позволява заплатата му да бъде намалена, при това без да връща съобщение за грешка: напротив, при всеки опит заплата му да бъде намалена, тя се увеличава с 10$. Създайте тригерът така, че той да се активира само при промяна на полето за заплата.
-
Създайте тригер TRG_ORDTOT_CALC, който при промяна на сумата от даден ред на поръчка (полето ITEMTOT от таблицата ITEM), въвеждане на нов ред от поръчка или изтриване на ред от поръчка, преизчислява общата сума на цялата порчъчка (полето TOTAL от таблицата ORD). Създайте тригерът така, че той да се активира само при въвеждане на нов ред, изтриване на ред или промяна на полето ITEMTOT от таблицата ITEM.
-
Създайте тригер TRG_ORDTOT_CALC, който при промяна на сумата от даден ред на поръчка (полето ITEMTOT от таблицата ITEM), въвеждане на нов ред от поръчка или изтриване на ред от поръчка, преизчислява общата сума на цялата порчъчка (полето TOTAL от таблицата ORD). Създайте тригерът така, че той да се активира само при въвеждане на нов ред, изтриване на ред или промяна на полето ITEMTOT от таблицата ITEM.
-
Създайте тригер TRG_CHECK_SAL, които не допуска въвеждане или промяна на заплата на работник, ако тя не попада в някоя от интервалите описани в таблицата SALGRADE. Създайте тригерът така, че той да се активира само при вмъкване на нов запис или при промяна на полето SAL.
Тестове:
TRG_CHECK_SAL | Трябва да минат | Не трябва да минат | |
INSERT INTO EMP(empno, sal,deptno)
VALUES(3333,750,10);
UPDATE EMP
SET sal=3300
WHERE empno=7788;
ROLLBACK;
|
DELETE SALGRADE
WHERE grade=0;
INSERT INTO EMP(empno, sal,deptno)
VALUES(2222,50,10);
UPDATE EMP
SET sal=0
WHERE empno=7788;
ROLLBACK;
| TRG_ORDTOT_CALC | |
OLD TOTAL= 2.4
NEW TOTAL=4.8
OLD TOTAL= 101.4
NEW TOTAL=201.4
|
SELECT ORDID,TOTAL
FROM ORD
WHERE ORDID in (601,610);
UPDATE ITEM
SET ITEMTOT=ITEMTOT*2
WHERE ORDID=601 AND ITEMID=1;
UPDATE ITEM
SET ITEMTOT=ITEMTOT+100
WHERE ORDID=610 AND ITEMID=3;
SELECT ORDID,TOTAL
FROM ORD
WHERE ORDID in (601,610);
ROLLBACK;
|
|
SELECT ORDID,TOTAL
FROM ORD
WHERE ORDID=601;
INSERT INTO ITEM (ORDID,ITEMID,ITEMTOT)
VALUES (601,2,200);
SELECT ORDID,TOTAL
FROM ORD
WHERE ORDID=601;
DELETE ITEM
WHERE ORDID=601 AND ITEMID=2;
|
Сподели с приятели: |
