Приложение 7. Коды процедур добавления в таблицы новых данных

/////////////////////////////////   СОЗДАНИЕ ПРОЦЕДУР ДЛЯ ЗАПОЛНЕНИЯ ТАБЛИЦ

CREATE OR REPLACE PROCEDURE NewPos

(

NEWNAME IN CHAR,

NEWSALARY IN NUMBER

)

AS

ROWCOUNT INTEGER;

BEGIN

SELECT COUNT(*) INTO ROWCOUNT

FROM POSITION

WHERE Name_Position = NEWNAME;

IF ROWCOUNT > 0 THEN

BEGIN

DBMS_OUTPUT.PUT_LINE ('The position is already in the database - no action taken');

RETURN;

END;

END IF;

INSERT INTO POSITION

(ID_Position, Name_Position, salary)

VALUES

(PosID.NEXTVAL, NEWNAME, NEWSALARY);

DBMS_OUTPUT.PUT_LINE ('New position was added');

END;

CREATE OR REPLACE PROCEDURE NewEmployee

(

NEWNAME IN CHAR,

NEWBIRTHDATE IN DATE,

NEWPOS IN CHAR

)

AS

ROWCOUNT INTEGER;

NEWPOSID INTEGER;

CURSOR POSCURSOR IS

SELECT * FROM POSITION

WHERE Name_Position = NEWPOS;

BEGIN

SELECT COUNT(*) INTO ROWCOUNT

FROM EMPLOYEE

WHERE Name_Employee = NEWNAME AND

birth_date = NEWBIRTHDATE;

IF ROWCOUNT > 0 THEN

BEGIN

DBMS_OUTPUT.PUT_LINE ('The Employee is already in the database - no action taken');

RETURN;

END;

END IF;

FOR POSS IN POSCURSOR

LOOP

NEWPOSID := POSS.ID_Position;

END LOOP;

INSERT INTO EMPLOYEE

(ID_Employee, Name_Employee, birth_date, ID_Position)

VALUES

(EmplID.NEXTVAL, NEWNAME, TO_DATE(NEWBIRTHDATE, 'DD-MM-YYYY'), NEWPOSID);

DBMS_OUTPUT.PUT_LINE ('New Employee was added');

END;

CREATE OR REPLACE PROCEDURE NewSub

(

NEWNAME IN CHAR,

NEWINN IN NUMBER,

NEWKPP IN NUMBER,

NEWPHONE IN CHAR

)

AS

ROWCOUNT INTEGER;

BEGIN

SELECT COUNT(*) INTO ROWCOUNT

FROM SUBCONTRACTOR

WHERE INN_Sub = NEWINN;

IF ROWCOUNT > 0 THEN

BEGIN

DBMS_OUTPUT.PUT_LINE ('The subcontractor is already in the database - no action taken');

RETURN;

END;

END IF;

INSERT INTO SUBCONTRACTOR

(ID_Sub, Name_Sub, INN_Sub, KPP_Sub, Tel_Sub)

VALUES

(SubID.NEXTVAL, NEWNAME, NEWINN, NEWKPP, NEWPHONE);

DBMS_OUTPUT.PUT_LINE ('New subcontractor was added');

END;

CREATE OR REPLACE PROCEDURE NewCust

(

NEWNAME IN CHAR,

NEWPHONE IN CHAR

)

AS

ROWCOUNT INTEGER;

BEGIN

SELECT COUNT(*) INTO ROWCOUNT

FROM CUSTOMER

WHERE Name_Cust = NEWNAME AND

Tel_Cust = NEWPHONE;

IF ROWCOUNT > 0 THEN

BEGIN

DBMS_OUTPUT.PUT_LINE ('The customer is already in the database - no action taken');

RETURN;

END;

END IF;

INSERT INTO CUSTOMER

(ID_Cust, Name_Cust, Tel_Cust)

VALUES

(CustID.NEXTVAL, NEWNAME, NEWPHONE);

DBMS_OUTPUT.PUT_LINE ('New customer was added');

END;

CREATE OR REPLACE PROCEDURE NewObj

(

NEWNAME IN CHAR,

NEWADRESS IN CHAR

)

AS

ROWCOUNT INTEGER;

BEGIN

SELECT COUNT(*) INTO ROWCOUNT

FROM OBJECT

WHERE Name_Obj = NEWNAME AND

Adress_Obj = NEWADRESS;

IF ROWCOUNT > 0 THEN

BEGIN

DBMS_OUTPUT.PUT_LINE ('The object is already in the database - no action taken');

RETURN;

END;

END IF;

INSERT INTO OBJECT

(ID_Obj, Name_Obj, Adress_Obj)

VALUES

(ObjID.NEXTVAL, NEWNAME, NEWADRESS);

DBMS_OUTPUT.PUT_LINE ('New customer was added');

END;

CREATE OR REPLACE PROCEDURE NewWork

(

NEWNAME IN CHAR,

NEWUNITS IN CHAR,

NEWPRICE IN NUMBER

)

AS

ROWCOUNT INTEGER;

BEGIN

SELECT COUNT(*) INTO ROWCOUNT

FROM TYPE_WORK

WHERE Name_type_work = NEWNAME;

IF ROWCOUNT > 0 THEN

BEGIN

DBMS_OUTPUT.PUT_LINE ('The type of work is already in the database - no action taken');

RETURN;

END;

END IF;

INSERT INTO TYPE_WORK

(ID_type_work, Name_type_work, Units_type_work, Price_type_work)

VALUES

(TypeID.NEXTVAL, NEWNAME, NEWUNITS, NEWPRICE);

DBMS_OUTPUT.PUT_LINE ('New type of work was added');

END;

CREATE OR REPLACE PROCEDURE NewPass

(

NEWCUST IN CHAR,

NEWNAME IN CHAR,

NEWOBJ IN CHAR,

NEWSTARTDATE IN DATE,

NEWENDDATE IN DATE,

NEWPERM IN CHAR

)

AS

ROWCOUNT INTEGER;

NEWCUSTID INTEGER;

NEWOBJID INTEGER;

CURSOR CUSTCURSOR IS

SELECT * FROM CUSTOMER

WHERE Name_Cust = NEWCUST;

CURSOR OBJCURSOR IS

SELECT * FROM OBJECT

WHERE Name_Obj = NEWOBJ;

BEGIN

SELECT COUNT(*) INTO ROWCOUNT

FROM PASSPORT

WHERE Permission = NEWPERM;

IF ROWCOUNT > 0 THEN

BEGIN

DBMS_OUTPUT.PUT_LINE ('The passport is already in the database - no action taken');

RETURN;

END;

END IF;

FOR CUSTT IN CUSTCURSOR

LOOP

NEWCUSTID := CUSTT.ID_Cust;

END LOOP;

FOR OBJJ IN OBJCURSOR

LOOP

NEWOBJID := OBJJ.ID_Obj;

END LOOP;

INSERT INTO PASSPORT

(ID_pass, ID_Cust, Name_Contractor, ID_Obj, Start_date, End_date, Permission)

VALUES

(PassID.NEXTVAL, NEWCUSTID, NEWNAME, NEWOBJID, TO_DATE(NEWSTARTDATE, 'DD-MM-YYYY'), TO_DATE(NEWENDDATE, 'DD-MM-YYYY'), NEWPERM);

DBMS_OUTPUT.PUT_LINE ('New permission was added');

END;

CREATE OR REPLACE PROCEDURE NewAct

(

NEWNAME IN CHAR,

NEWSUB IN CHAR,

NEWOBJ IN CHAR,

NEWEMPL IN CHAR,

NEWWORK IN CHAR,

NEWSTARTDATE IN DATE,

NEWENDDATE IN DATE

)

AS

ROWCOUNT INTEGER;

NEWSUBID INTEGER;

NEWOBJID INTEGER;

NEWEMPLID INTEGER;

NEWWORKID INTEGER;

CURSOR SUBCURSOR IS

SELECT * FROM SUBCONTRACTOR

WHERE Name_Sub = NEWSUB;

CURSOR OBJCURSOR IS

SELECT * FROM OBJECT

WHERE Name_Obj = NEWOBJ;

CURSOR EMPLCURSOR IS

SELECT * FROM EMPLOYEE

WHERE Name_Employee = NEWEMPL;

CURSOR WORKCURSOR IS

SELECT * FROM TYPE_WORK

WHERE Name_type_work = NEWWORK;

BEGIN

SELECT COUNT(*) INTO ROWCOUNT

FROM ACT_WORK

WHERE Name_Contractor != NEWNAME;

IF ROWCOUNT > 0 THEN

BEGIN

RETURN;

END;

END IF;

FOR SUBB IN SUBCURSOR

LOOP

NEWSUBID := SUBB.ID_Sub;

END LOOP;

FOR OBJJ IN OBJCURSOR

LOOP

NEWOBJID := OBJJ.ID_Obj;

END LOOP;

FOR EMPLL IN EMPLCURSOR

LOOP

NEWEMPLID := EMPLL.ID_Employee;

END LOOP;

FOR WORKK IN WORKCURSOR

LOOP

NEWWORKID := WORKK.ID_type_work;

END LOOP;

INSERT INTO ACT_WORK

(ID_act_work, Name_Contractor, ID_Sub, ID_Obj, ID_Employee, ID_type_work, Start_date_work, End_date_work)

VALUES

(ActID.NEXTVAL, NEWNAME, NEWSUBID, NEWOBJID, NEWEMPLID, NEWWORKID, TO_DATE(NEWSTARTDATE, 'DD-MM-YYYY'), TO_DATE(NEWENDDATE, 'DD-MM-YYYY'));

DBMS_OUTPUT.PUT_LINE ('New act was added');

END;