Basic structures напишете pl/sql блок, който въвежда номера в таблицата messages
Създайте спецификация и тяло на пакет наречен PROD_PACK, които съдържат вашите ADD_PROD, UPD_PROD, DEL_PROD процедури, както и функцията Q_PROD
Изтегляне 450.15 Kb.
|
- Навигация на страницата:
- CREATE OR REPLACE PACKAGE chk_pack IS PROCEDURE chk_hiredate(v_date in emp.hiredate%type); PROCEDURE chk_dept_mgr(v_empno in emp.empno%type
- Създайте пакет наречен OVER_LOAD. Създайте 2 функции в този пакет; наречете и двете функции PRINT_IT. Функцията отпечатват дата или реално число в зависимост от това как е извикана.
- SQL> variable todays_date varchar2(20) SQL> execute :todays_date := over_load.print_it(sysdate)
Създайте спецификация и тяло на пакет наречен PROD_PACK, които съдържат вашите ADD_PROD, UPD_PROD, DEL_PROD процедури, както и функцията Q_PROD.Забележка: Използвайте кода, които сте съхранили в съответните скрипт файлове.
Забележка: Помислете дали все още се нуждаете от процедурите и функциите, които вече сте пакетирали – те съществуват в речника на данни като “stand-alone ” обекти;
################################################3 CREATE OR REPLACE PACKAGE BODY prod_pack IS PROCEDURE add_prod (v_prodid IN product.prodid%TYPE, v_descrip IN product.descrip%TYPE) IS BEGIN INSERT INTO product (prodid, descrip) VALUES (v_prodid, v_descrip); END add_prod; PROCEDURE upd_prod (v_prodid IN product.prodid%TYPE, v_descrip IN product.descrip%TYPE) IS BEGIN
UPDATE product SET descrip = v_descrip WHERE prodid = v_prodid; IF SQL%NOTFOUND THEN RAISE_APPLICATION_ERROR(-20202,'No products updated.'); END IF;
END upd_prod; PROCEDURE del_prod (v_prodid IN product.prodid%TYPE) IS BEGIN DELETE FROM product WHERE prodid = v_prodid; IF SQL%NOTFOUND THEN RAISE_APPLICATION_ERROR (-20203, 'No products deleted.'); END IF; END DEL_PROD; FUNCTION q_prod (v_prodid IN product.prodid%TYPE) RETURN VARCHAR2 IS v_descrip product.descrip%TYPE; BEGIN SELECT descrip INTO v_descrip FROM product WHERE prodid = v_prodid; RETURN (v_descrip); END q_prod; END prod_pack; /
########################################### CREATE OR REPLACE PACKAGE prod_pack IS PROCEDURE add_prod (v_prodid IN product.prodid%TYPE, v_descrip IN product.descrip%TYPE); PROCEDURE upd_prod (v_prodid IN product.prodid%TYPE, v_descrip IN product.descrip%TYPE); PROCEDURE del_prod (v_prodid IN product.prodid%TYPE); FUNCTION q_prod (v_prodid IN product.prodid%TYPE) RETURN VARCHAR2; END prod_pack; /
############################################################3 CREATE OR REPLACE PACKAGE BODY emp_pack IS FUNCTION valid_deptno (v_deptno IN dept.deptno%TYPE) RETURN BOOLEAN IS
v_dummy VARCHAR2(1); BEGIN
SELECT 'x' INTO v_dummy FROM dept WHERE deptno = v_deptno; RETURN (TRUE); EXCEPTION WHEN NO_DATA_FOUND THEN RETURN(FALSE); END valid_deptno; PROCEDURE new_emp (v_ename emp.ename%TYPE, v_job emp.job%TYPE DEFAULT 'SALESMAN', -- Repetition v_mgr emp.mgr%TYPE DEFAULT 7839, -- of defaults v_sal emp.sal%TYPE DEFAULT 1000, -- for completeness v_comm emp.comm%TYPE DEFAULT 0, -- only v_deptno emp.deptno%TYPE DEFAULT 30) IS BEGIN
IF valid_deptno(v_deptno) THEN INSERT INTO emp VALUES (seq_empno.NEXTVAL, v_ename, v_job, v_mgr, TRUNC (SYSDATE, 'DD'), v_sal, v_comm, v_deptno); ELSE RAISE_APPLICATION_ERROR (-20205, 'Invalid department number. Try again.'); END IF;
END new_emp; END emp_pack; /
##################################################### CREATE OR REPLACE PACKAGE prod_pack IS PROCEDURE add_prod (v_prodid IN product.prodid%TYPE, v_descrip IN product.descrip%TYPE); PROCEDURE upd_prod (v_prodid IN product.prodid%TYPE, v_descrip IN product.descrip%TYPE); PROCEDURE del_prod (v_prodid IN product.prodid%TYPE); FUNCTION q_prod (v_prodid IN product.prodid%TYPE) RETURN VARCHAR2; END prod_pack; /
Забележки:
Забележки:
######################################################### CREATE OR REPLACE PACKAGE BODY chk_pack IS PROCEDURE chk_hiredate(v_date in emp.hiredate%type) IS v_low date := add_months (trunc(sysdate), - (50 * 12)); v_high date := add_months (trunc(sysdate),3); BEGIN
IF TRUNC(v_date) NOT BETWEEN v_low and v_high OR v_date IS NULL THEN RAISE_APPLICATION_ERROR(-20200,'Not a valid hiredate'); END IF;
END chk_hiredate; PROCEDURE chk_dept_mgr(v_empno in emp.empno%type, v_mgr in emp.mgr%type) IS v_empnr emp.empno%type; v_deptno emp.deptno%type; BEGIN
BEGIN SELECT deptno --pick up the department number INTO v_deptno FROM emp
WHERE empno = v_empno; EXCEPTION WHEN no_data_found THEN RAISE_APPLICATION_ERROR (-20000, 'Not a valid empno'); END; BEGIN
SELECT empno /*check valid combination deptno/mgr for given employee */ INTO v_empnr FROM emp
WHERE deptno = v_deptno AND empno = v_mgr AND job = 'MANAGER'; EXCEPTION WHEN no_data_found THEN RAISE_APPLICATION_ERROR (-20000, 'Not a valid manager for this department'); END;
END chk_dept_mgr; END chk_pack; /
############################################################ CREATE OR REPLACE PACKAGE chk_pack IS PROCEDURE chk_hiredate(v_date in emp.hiredate%type); PROCEDURE chk_dept_mgr(v_empno in emp.empno%type, v_mgr in emp.mgr%type); END chk_pack; / MORE PACKAGE CONCEPTS
SQL> variable todays_date varchar2(20) SQL> execute :todays_date := over_load.print_it(sysdate) PL/SQL procedure successfully completed. Каталог: fmi -> fmi-ftp-upload-folder -> 5%20Semestur%202005%20&%202006 -> Predmeti -> Oracle -> OraTest fmi-ftp-upload-folder -> - fmi-ftp-upload-folder -> Бази от данни Упражнение 10 Predmeti -> Oracle e-business Suite Модул Financials, подмодул Fixed Assets fmi-ftp-upload-folder -> Бази от данни Упражнение 9 fmi-ftp-upload-folder -> Спецификация на изискванията Predmeti -> 1. Принципи на проектирането на потребителски интерфейс 5 Взаимодействие с потребителя Изтегляне 450.15 Kb. Сподели с приятели:
|