Если у вас возникли сложности с курсовой, контрольной, дипломной, рефератом, отчетом по практике, научно-исследовательской и любой другой работой - мы готовы помочь.
Предоплата всего
Подписываем
CREATE OR REPLACE FUNCTION fSalary
(id IN NUMBER)
RETURN NUMBER
AS
salary NUMBER;
BEGIN
SELECT salary INTO salary FROM employees WHERE employee_id=id;
RETURN salary;
END fSalary;
/
DECLARE
salary NUMBER;
BEGIN
salary:=fSalary(100);
DBMS_OUTPUT.PUT_LINE(salary);
END;
/
CREATE OR REPLACE PROCEDURE pSalary
(id IN NUMBER, new_sal IN NUMBER)
IS
BEGIN
UPDATE employees SET salary=new_sal WHERE employee_id=id;
END pSalary;
/
DECLARE
id NUMBER;
sal NUMBER;
BEGIN
pSalary(103,30000);
SELECT employee_id, salary INTO id, sal FROM employees WHERE employee_id=103;
DBMS_OUTPUT.PUT_LINE(id);
DBMS_OUTPUT.PUT_LINE(sal);
END;
/
CREATE OR REPLACE PACKAGE pkgSalary IS
PROCEDURE pSalary(id IN NUMBER, new_sal IN NUMBER);
FUNCTION fSalary(id IN NUMBER) RETURN NUMBER;
END pkgSalary;
/
CREATE OR REPLACE PACKAGE BODY pkgSalary IS
-- PROCEDURE pSalary -- *******************************************
PROCEDURE pSalary(id IN NUMBER, new_sal IN NUMBER)
IS
BEGIN
UPDATE employees SET salary=new_sal WHERE employee_id=id;
END pSalary;
-- FUNCTION fSalary -- ********************************************
FUNCTION fSalary(id IN NUMBER) RETURN NUMBER
IS
salary NUMBER;
BEGIN
SELECT salary INTO salary FROM employees WHERE employee_id=id;
RETURN salary;
END fSalary;
END pkgSalary;
/
DECLARE
salary NUMBER;
BEGIN
pkgSalary.pSalary(104,10000);
salary:=pkgSalary.fSalary(104);
DBMS_OUTPUT.PUT_LINE(salary);
END;
/
CREATE OR REPLACE TRIGGER MySalaryCheck
BEFORE INSERT OR UPDATE OF salary ON employees
REFERENCING NEW as new
FOR EACH ROW
DECLARE
BEGIN
IF :new.salary<1000 THEN
raise_application_error(-20300, 'Ailyk minimumnan az!');
END IF;
IF :new.salary>50000 THEN
raise_application_error(-20301, 'Ailyk maximumnan kop!');
END IF;
END;
/
INSERT INTO employees
(employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, comission_pct, manager_id, department_id)
VALUES
(105, 'Elaman', 'Bekbolatov', 'EBEKBOLATOV', '877.148.871', '8.12.2013', 'AD_STUD', 100000, NULL, 100, 11)
/
DECLARE
nNum1 NUMBER(10,0);
nNum2 NUMBER(10,0);
nResult NUMBER(10,5);
BEGIN
nNum1:=&Bolgish;
nNum2:=&Bolingish;
nResult:=nNum1/nNum2;
DBMS_OUTPUT.PUT_LINE(nResult);
EXCEPTION
WHEN ZERO_DIVIDE THEN
DBMS_OUTPUT.PUT_LINE('Nolge boluge bolmaydi!');
WHEN VALUE_ERROR THEN
DBMS_OUTPUT.PUT_LINE('Malimetter typi durys emes!');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Belgisiz kate...');
END;
/