Basic structures напишете pl/sql блок, който въвежда номера в таблицата messages



Изтегляне 450.15 Kb.
страница3/6
Дата30.03.2017
Размер450.15 Kb.
#18074
1   2   3   4   5   6



Примерни решения:
CREATE OR REPLACE PROCEDURE EMP_GRADE

(v_grade IN salgrade.grade%type)

IS

v_losal salgrade.losal%type;

v_hisal salgrade.hisal%type;

v_ename emp.ename%type;

v_job emp.job%type;

CURSOR c_emp(l salgrade.losal%type,h salgrade.hisal%type) IS

SELECT ename, job

FROM EMP

WHERE sal >=l and sal<=h;

BEGIN

SELECT losal , hisal INTO v_losal,v_hisal

FROM salgrade

WHERE grade=v_grade ;

OPEN c_emp(v_losal,v_hisal );

LOOP

FETCH c_emp INTO v_ename,v_job;

EXIT WHEN c_emp%NOTFOUND;

DBMS_OUTPUT.PUT_LINE('Ename: '|| v_ename|| ', Job: '||v_job);

END LOOP;

CLOSE c_emp;

EXCEPTION

WHEN NO_DATA_FOUND THEN

RAISE_APPLICATION_ERROR(-20100,'Invalid grade!');

END;

/
----------------

CREATE OR REPLACE PROCEDURE EMP_GRADE

(v_grade IN salgrade.grade%type)

IS

v_losal salgrade.losal%type;

v_hisal salgrade.hisal%type;

BEGIN

SELECT losal , hisal INTO v_losal,v_hisal

FROM salgrade

WHERE grade=v_grade ;

FOR r IN (SELECT ename, job

FROM EMP

WHERE sal >=v_losal and sal<=v_hisal)

LOOP

DBMS_OUTPUT.PUT_LINE(r.ename|| ','||r.job);

END LOOP;

EXCEPTION

WHEN NO_DATA_FOUND THEN

RAISE_APPLICATION_ERROR(-20100,'Invalid grade!');

END;

/

########################################################

  1. Създайте и извикайте процедура наречена GET_REP_CUSTOMERS, която по подаден номер на работник, извежда имената на всички клиенти, за които отговаря съответният работник. Ако бъде подаден номер на работник, чиято длъжност не е ‘SALESMAN’, то процедурата вдига програмна грешка с номер -20101 и съобщение “Invalid representation employee number!”

  1. Подайте номер на работник като входен параметър;

  2. Изведете имената и градовете на клиентите, за които отговаря работникът на екрана, като използвате пакета DBMS_OUTPUT.

  3. Тествайте различните случаи и демонстрирайте получения резултат.


TEST:

EMPNO

RESULT

1111


PL/SQL procedure successfully completed.

7521

TKB SPORT SHOP,REDWOOD CITY

JUST TENNIS,BURLINGAME


SHAPE UP,PALO ALTO

PL/SQL procedure successfully completed.

7654

VOLLYRITE,BURLINGAME

PL/SQL procedure successfully completed.

7788

…

ORA-20101: Invalid representation employee number!

…




Примерно решение:
CREATE OR REPLACE PROCEDURE GET_REP_CUSTOMERS

(v_empno emp.empno%type)

IS

v_job emp.job%type;

BEGIN

SELECT job INTO v_job

FROM emp

WHERE empno=v_empno;

IF v_job!='SALESMAN' THEN

RAISE_APPLICATION_ERROR('-20101','Invalid representation employee number!');

END IF;

FOR r IN (SELECT name,city FROM CUSTOMER WHERE repid=v_empno)

LOOP

DBMS_OUTPUT.PUT_LINE(r.name||','||r.city);

END LOOP;

EXCEPTION

WHEN NO_DATA_FOUND THEN

RAISE_APPLICATION_ERROR('-20102','Invalid employee number!');

END;

/
#####################################################

CREATING FUNCTIONS

    1. Създайте и извикайте функцията Q_PROD, която връща описание на продукт.

    1. Създайте функция наречена Q_PROD, която връща описание на продукт в променлива на средата (host променлива);

    2. Компилирайте кода, извикайте функцията, след което вижте резултата в host променливата.

#########################################

CREATE OR REPLACE 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;

/


    1. Създайте съхранена функция ANNUAL_COMP, която връща годишна заплата по подадени месечна заплата на работник и комисионна. Функцията трябва да може да получава като входни параметри и NULL стойностти.

    2. Създайте и извикайте функцията ANNUAL_COMP, подавайки и като параметри месечна заплата и комисионна. Една от двете или и двете стойности на входните параметри могат да бъдат NULL, но функцията трябва да връща годишната заплата, която не е NULL. Годишната заплата се определя по следната базисна формула: (sal*12) + comm.

    3. Използвайте съхранената функция в оператор SELECT върху таблицата EMP.

###########################################

CREATE OR REPLACE FUNCTION annual_comp

(v_sal IN emp.sal%TYPE,

v_comm IN emp.comm%TYPE)

RETURN NUMBER

IS

BEGIN



RETURN (NVL(v_sal,0) * 12 + NVL(v_comm,0));

END annual_comp;

/


  1. Създайте процедура NEW_EMP, за да вмъкнете нов работник в таблицата EMP. Процедурата трябва да извиква функция VALID_DEPTNO, за да проверява дали номерът на отдел, определен за новия работник, съществува в таблицата DEPT.

    1. Създайте функция VALID_DEPTNO, за да валидирате определен номер на отдел. Функцията трябва да връща булев резултат.

####################################################

CREATE OR REPLACE 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;

/


    1. Създайте процедурата NEW_EMP, за да добавите работник в таблицата EMP. Нов запис трябва да бъде добавен в EMP, ако функцията VALID_DEPTNO върне TRUE. Ако функцията върне FALSE, процедурата трябва да издаде на потребителя подходящо съобщение. Определете стойности по подразбиране за повечето параметри. Подразбиращата се комисионна е 0, заплатата е 1000, номерът на отдел е 30, позицията - SALESMAN и подразбиращия се номер на мениджър е 7839. За генериране на номер на работник използвайте sequence-a SEQ_EMPNO. Създайте sequence-а в SQL*Plus, като изпълните скрипта cre_seq_empno.sql

########################################

. CREATE OR REPLACE PROCEDURE new_emp

(v_ename emp.ename%TYPE,

v_job emp.job%TYPE DEFAULT 'SALESMAN',

v_mgr emp.mgr%TYPE DEFAULT 7839,

v_sal emp.sal%TYPE DEFAULT 1000,

v_comm emp.comm%TYPE DEFAULT 0,

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 (-20204, 'Invalid department number. Try again.');



END IF;

END new_emp;



/

    1. Тествайте вашата NEW_EMP процедура като добавите нов работник на име HARRIS в отдел 99. Оставете всички останали параметри по подразбиране. Какъв е резултатът?

    2. Тествайте вашата NEW_EMP процедура като добавите нов работник на име HARRIS в отдел 30. Оставете всички останали параметри по подразбиране. Какъв е резултатът?

#################################################

Допълнителна задача:

  1. Нуждаем се от справка, която за всеки работник от таблицата EMP, изкарва отношението между годишната печалба на неговия шеф и собствената му годишна печалба. Годишната печалба на работник се определя по следната базисна формула: (sal*12) + comm.

    1. Напишете съхранена функция GET_MGR_RATIO, която по подаден номер на работник, връща търсеното отношение.

    2. Използвайте функция ANNUAL_COMP, която по подадени месечна заплата на работник и комисионна връща годишна заплата

    3. Напишете подходяща SQL заявка, която изкарва търсената справка.

#######################################
CREATE OR REPLACE FUNCTION

GET_MGR_RATIO(v_empno IN emp.empno%type)

RETURN number

IS

v_emp_sal emp.sal%type;

v_emp_comm emp.comm%type;

v_mgr_sal emp.sal%type:=1;

v_mgr_comm emp.comm%type:=1;

v_mgr emp.mgr%type;

--- sub function

FUNCTION Annual_Networth

(v_sal IN emp.sal%TYPE, v_comm IN emp.comm%TYPE)

RETURN NUMBER

IS

BEGIN

RETURN NVL(v_sal * 12,0) + NVL(v_comm,0);

END Annual_Networth;

--- end subfuction

BEGIN

SELECT sal,comm,mgr

INTO v_emp_sal ,v_emp_comm,v_mgr

FROM emp

WHERE empno=v_empno;

SELECT sal,comm

INTO v_mgr_sal ,v_mgr_comm

FROM emp

WHERE empno = v_mgr;

/*IF Annual_Networth(v_emp_sal ,v_emp_comm) <> 0 THEN

RETURN (Annual_Networth(v_mgr_sal ,v_mgr_comm)/Annual_Networth(v_emp_sal ,v_emp_comm));

ELSE

RETURN 0;

END IF; */

RETURN (Annual_Networth(v_mgr_sal ,v_mgr_comm)/Annual_Networth(v_emp_sal ,v_emp_comm));

EXCEPTION

-- mgr is null

WHEN no_data_found THEN

RETURN null;

-- Annual_Networth(v_emp_sal ,v_emp_comm) = 0

WHEN zero_divide THEN

RETURN 0;

END GET_MGR_RATIO;

/

CREATING PACKAGES


  1. Изтегляне 450.15 Kb.

    Сподели с приятели:
1   2   3   4   5   6



©obuch.info 2024
отнасят до администрацията
    Начална страница
Автореферат
Анализ
Бизнес-план
Биография
Глава
Диплом
Доклад
Задача
Закон
Занятие
Заседание