Главная Случайная страница


Категории:

ДомЗдоровьеЗоологияИнформатикаИскусствоИскусствоКомпьютерыКулинарияМаркетингМатематикаМедицинаМенеджментОбразованиеПедагогикаПитомцыПрограммированиеПроизводствоПромышленностьПсихологияРазноеРелигияСоциологияСпортСтатистикаТранспортФизикаФилософияФинансыХимияХоббиЭкологияЭкономикаЭлектроника






SQL-скрипты объектов базы данных

 

SET SQL DIALECT 3;

 

/* CREATE DATABASE '127.0.0.1/gds_db:F:\TORGOTDEL.GDB' PAGE_SIZE 4096

 

DEFAULT CHARACTER SET */

 

/* Table: ADRES, Owner: SYSDBA */

 

CREATE TABLE "ADRES"

(

"NOMER" INTEGER NOT NULL,

"DOM" INTEGER NOT NULL,

"KORP" INTEGER,

"KVAR" INTEGER,

"KOD_SE" INTEGER,

"KOD_FL" INTEGER,

"KOD_UL" INTEGER NOT NULL,

"KOD_NP" INTEGER NOT NULL,

PRIMARY KEY ("NOMER")

);

 

/* Table: DOGOVOR, Owner: SYSDBA */

 

CREATE TABLE "DOGOVOR"

(

"NOMER" INTEGER NOT NULL,

"DATA_N" DATE NOT NULL,

"DATA_O" DATE,

"KOD_DOL" INTEGER NOT NULL,

"KOD_SE" INTEGER NOT NULL,

"KOD_FL" INTEGER NOT NULL,

PRIMARY KEY ("NOMER")

);

 

/* Table: DOLGNOST, Owner: SYSDBA */

 

CREATE TABLE "DOLGNOST"

(

"KOD" INTEGER NOT NULL,

"NAZV" VARCHAR(40) NOT NULL,

"K_NAZV" VARCHAR(15),

UNIQUE ("NAZV"),

PRIMARY KEY ("KOD")

);

 

/* Table: EDIN_IZM, Owner: SYSDBA */

 

CREATE TABLE "EDIN_IZM"

(

"KOD" INTEGER NOT NULL,

"NAZV" VARCHAR(20) NOT NULL,

"K_NAZV" VARCHAR(10),

UNIQUE ("NAZV"),

PRIMARY KEY ("KOD")

);

 

/* Table: FIZ_LICO, Owner: SYSDBA */

 

CREATE TABLE "FIZ_LICO"

(

"T_NOMER" INTEGER NOT NULL,

"FAM" VARCHAR(20) NOT NULL,

"NAME" VARCHAR(20) NOT NULL,

"OTCH" VARCHAR(20),

"DATA_R" DATE,

"KOD_P" INTEGER NOT NULL,

PRIMARY KEY ("T_NOMER")

);

 

/* Table: NAS_PUNKT, Owner: SYSDBA */

 

CREATE TABLE "NAS_PUNKT"

(

"KOD" INTEGER NOT NULL,

"NAZV" VARCHAR(40) NOT NULL,

"KOD_TIPA" INTEGER NOT NULL,

UNIQUE ("NAZV"),

PRIMARY KEY ("KOD")

);

 

/* Table: POL_FIZ_LICA, Owner: SYSDBA */

 

CREATE TABLE "POL_FIZ_LICA"

(

"KOD" INTEGER NOT NULL,

"NAZV" VARCHAR(7) NOT NULL,

"K_NAZV" VARCHAR(3),

"KK_NAZV" VARCHAR(1),

UNIQUE ("NAZV"),

PRIMARY KEY ("KOD")

);

 

/* Table: POZIC_PR, Owner: SYSDBA */

 

CREATE TABLE "POZIC_PR"

(

"NOMER" INTEGER NOT NULL,

"CENA" NUMERIC(8, 2) NOT NULL,

"KOD_TOV" INTEGER NOT NULL,

"KOD_PR" INTEGER NOT NULL,

"KOD_EI" INTEGER NOT NULL,

"KOD_VC" INTEGER NOT NULL,

PRIMARY KEY ("NOMER")

);

 

/* Table: POZIC_VEDOM, Owner: SYSDBA */

 

CREATE TABLE "POZIC_VEDOM"

(

"NOMER" INTEGER NOT NULL,

"KOL" NUMERIC(8, 2) NOT NULL,

"KOD_TOV" INTEGER NOT NULL,

"KOD_PPR" INTEGER NOT NULL,

"KOD_EI" INTEGER NOT NULL,

"KOD_VED" INTEGER NOT NULL,

PRIMARY KEY ("NOMER")

);

 

/* Table: PRICE, Owner: SYSDBA */

 

CREATE TABLE "PRICE"

(

"NOMER" INTEGER NOT NULL,

"DATA_P" DATE NOT NULL,

PRIMARY KEY ("NOMER")

);

 

/* Table: STRUCT_EDIN, Owner: SYSDBA */

 

CREATE TABLE "STRUCT_EDIN"

(

"KOD" INTEGER NOT NULL,

"NAZV" VARCHAR(40) NOT NULL,

"K_NAZV" VARCHAR(15),

"KOD_SE" INTEGER,

"KOD_T_SE" INTEGER NOT NULL,

UNIQUE ("NAZV"),

PRIMARY KEY ("KOD")

);

 

/* Table: TELEFON, Owner: SYSDBA */

 

CREATE TABLE "TELEFON"

(

"KOD" INTEGER NOT NULL,

"NOMER" VARCHAR(20) NOT NULL,

"KOD_TT" INTEGER NOT NULL,

"KOD_SE" INTEGER,

"KOD_FL" INTEGER,

UNIQUE ("NOMER"),

PRIMARY KEY ("KOD")

);

 

/* Table: TIP_NAS_PUNKTA, Owner: SYSDBA */

 

CREATE TABLE "TIP_NAS_PUNKTA"

(

"KOD" INTEGER NOT NULL,

"NAZV" VARCHAR(20) NOT NULL,

"K_NAZV" VARCHAR(10),

UNIQUE ("NAZV"),

PRIMARY KEY ("KOD")

);

 

/* Table: TIP_STRUCT_EDIN, Owner: SYSDBA */

 

CREATE TABLE "TIP_STRUCT_EDIN"

(

"KOD" INTEGER NOT NULL,

"NAZV" VARCHAR(40) NOT NULL,

"K_NAZV" VARCHAR(15),

UNIQUE ("NAZV"),

PRIMARY KEY ("KOD")

);

 

/* Table: TIP_TEL, Owner: SYSDBA */

 

CREATE TABLE "TIP_TEL"

(

"KOD" INTEGER NOT NULL,

"NAZV" VARCHAR(40) NOT NULL,

"K_NAZV" VARCHAR(15),

UNIQUE ("NAZV"),

PRIMARY KEY ("KOD")

);

 

/* Table: TIP_TOVARA, Owner: SYSDBA */

 

CREATE TABLE "TIP_TOVARA"

(

"KOD" INTEGER NOT NULL,

"NAZV" VARCHAR(40) NOT NULL,

"K_NAZV" VARCHAR(15),

UNIQUE ("NAZV"),

PRIMARY KEY ("KOD")

);

 

/* Table: TIP_ULICI, Owner: SYSDBA */

 

CREATE TABLE "TIP_ULICI"

(

"KOD" INTEGER NOT NULL,

"NAZV" VARCHAR(20) NOT NULL,

"K_NAZV" VARCHAR(10),

UNIQUE ("NAZV"),

PRIMARY KEY ("KOD")

);

 

/* Table: TIP_VED, Owner: SYSDBA */

 

CREATE TABLE "TIP_VED"

(

"KOD" INTEGER NOT NULL,

"NAZV" VARCHAR(40) NOT NULL,

"K_NAZV" VARCHAR(15),

UNIQUE ("NAZV"),

PRIMARY KEY ("KOD")

);

 

/* Table: TOVAR, Owner: SYSDBA */

 

CREATE TABLE "TOVAR"

(

"KOD" INTEGER NOT NULL,

"NAZV" VARCHAR(40) NOT NULL,

"K_NAZV" VARCHAR(15),

"KOD_TIPA" INTEGER NOT NULL,

UNIQUE ("NAZV"),

PRIMARY KEY ("KOD")

);

 

/* Table: ULICA, Owner: SYSDBA */

 

CREATE TABLE "ULICA"

(

"KOD" INTEGER NOT NULL,

"NAZV" VARCHAR(40) NOT NULL,

"KOD_TIPA" INTEGER NOT NULL,

UNIQUE ("NAZV"),

PRIMARY KEY ("KOD")

);

 

/* Table: VEDOMOST, Owner: SYSDBA */

 

CREATE TABLE "VEDOMOST"

(

"NOMER" INTEGER NOT NULL,

"DATA_V" DATE NOT NULL,

"KOD_TV" INTEGER NOT NULL,

"KOD_SE_SC" INTEGER NOT NULL,

"KOD_SE_PP" INTEGER,

"KOD_FL_PP" INTEGER,

"KOD_MEN" INTEGER NOT NULL,

"KOD_ZAV" INTEGER NOT NULL,

PRIMARY KEY ("NOMER")

);

 

/* Table: VID_CENI, Owner: SYSDBA */

 

CREATE TABLE "VID_CENI"

(

"KOD" INTEGER NOT NULL,

"NAZV" VARCHAR(20) NOT NULL,

"K_NAZV" VARCHAR(10),

UNIQUE ("NAZV"),

PRIMARY KEY ("KOD")

);

ALTER TABLE "ADRES" ADD FOREIGN KEY ("KOD_SE") REFERENCES "STRUCT_EDIN" ("KOD");

ALTER TABLE "ADRES" ADD FOREIGN KEY ("KOD_FL") REFERENCES "FIZ_LICO" ("T_NOMER");

ALTER TABLE "ADRES" ADD FOREIGN KEY ("KOD_UL") REFERENCES "ULICA" ("KOD");

ALTER TABLE "ADRES" ADD FOREIGN KEY ("KOD_NP") REFERENCES "NAS_PUNKT" ("KOD");

ALTER TABLE "DOGOVOR" ADD FOREIGN KEY ("KOD_DOL") REFERENCES "DOLGNOST" ("KOD");

ALTER TABLE "DOGOVOR" ADD FOREIGN KEY ("KOD_SE") REFERENCES "STRUCT_EDIN" ("KOD");

ALTER TABLE "DOGOVOR" ADD FOREIGN KEY ("KOD_FL") REFERENCES "FIZ_LICO" ("T_NOMER");

ALTER TABLE "FIZ_LICO" ADD FOREIGN KEY ("KOD_P") REFERENCES "POL_FIZ_LICA" ("KOD");

ALTER TABLE "NAS_PUNKT" ADD FOREIGN KEY ("KOD_TIPA") REFERENCES "TIP_NAS_PUNKTA" ("KOD");

ALTER TABLE "POZIC_PR" ADD FOREIGN KEY ("KOD_TOV") REFERENCES "TOVAR" ("KOD");

ALTER TABLE "POZIC_PR" ADD FOREIGN KEY ("KOD_PR") REFERENCES "PRICE" ("NOMER");

ALTER TABLE "POZIC_PR" ADD FOREIGN KEY ("KOD_EI") REFERENCES "EDIN_IZM" ("KOD");

ALTER TABLE "POZIC_PR" ADD FOREIGN KEY ("KOD_VC") REFERENCES "VID_CENI" ("KOD");

ALTER TABLE "POZIC_VEDOM" ADD FOREIGN KEY ("KOD_TOV") REFERENCES "TOVAR" ("KOD");

ALTER TABLE "POZIC_VEDOM" ADD FOREIGN KEY ("KOD_PPR") REFERENCES "POZIC_PR" ("NOMER");

ALTER TABLE "POZIC_VEDOM" ADD FOREIGN KEY ("KOD_EI") REFERENCES "EDIN_IZM" ("KOD");

ALTER TABLE "POZIC_VEDOM" ADD FOREIGN KEY ("KOD_VED") REFERENCES "VEDOMOST" ("NOMER");

ALTER TABLE "STRUCT_EDIN" ADD FOREIGN KEY ("KOD_SE") REFERENCES "STRUCT_EDIN" ("KOD");

ALTER TABLE "STRUCT_EDIN" ADD FOREIGN KEY ("KOD_T_SE") REFERENCES "TIP_STRUCT_EDIN" ("KOD");

ALTER TABLE "TELEFON" ADD FOREIGN KEY ("KOD_TT") REFERENCES "TIP_TEL" ("KOD");

ALTER TABLE "TELEFON" ADD FOREIGN KEY ("KOD_SE") REFERENCES "STRUCT_EDIN" ("KOD");

ALTER TABLE "TELEFON" ADD FOREIGN KEY ("KOD_FL") REFERENCES "FIZ_LICO" ("T_NOMER");

ALTER TABLE "TOVAR" ADD FOREIGN KEY ("KOD_TIPA") REFERENCES "TIP_TOVARA" ("KOD");

ALTER TABLE "ULICA" ADD FOREIGN KEY ("KOD_TIPA") REFERENCES "TIP_ULICI" ("KOD");

ALTER TABLE "VEDOMOST" ADD FOREIGN KEY ("KOD_TV") REFERENCES "TIP_VED" ("KOD");

ALTER TABLE "VEDOMOST" ADD FOREIGN KEY ("KOD_SE_SC") REFERENCES "STRUCT_EDIN" ("KOD");

ALTER TABLE "VEDOMOST" ADD FOREIGN KEY ("KOD_SE_PP") REFERENCES "STRUCT_EDIN" ("KOD");

ALTER TABLE "VEDOMOST" ADD FOREIGN KEY ("KOD_FL_PP") REFERENCES "FIZ_LICO" ("T_NOMER");

ALTER TABLE "VEDOMOST" ADD FOREIGN KEY ("KOD_MEN") REFERENCES "DOGOVOR" ("NOMER");

ALTER TABLE "VEDOMOST" ADD FOREIGN KEY ("KOD_ZAV") REFERENCES "DOGOVOR" ("NOMER");

 

CREATE GENERATOR "GEN_ADRES";

CREATE GENERATOR "GEN_DOGOVOR";

CREATE GENERATOR "GEN_DOLGNOST";

CREATE GENERATOR "GEN_EDIN_IZM";

CREATE GENERATOR "GEN_FIZ_LICO";

CREATE GENERATOR "GEN_NAS_PUNKT";

CREATE GENERATOR "GEN_POL_FIZ_LICA";

CREATE GENERATOR "GEN_POZIC_PR";

CREATE GENERATOR "GEN_POZIC_VEDOM";

CREATE GENERATOR "GEN_PRICE";

CREATE GENERATOR "GEN_STRUCT_EDIN";

CREATE GENERATOR "GEN_TELEFON";

CREATE GENERATOR "GEN_TIP_NAS_PUNKTA";

CREATE GENERATOR "GEN_TIP_STRUCT_EDIN";

CREATE GENERATOR "GEN_TIP_TEL";

CREATE GENERATOR "GEN_TIP_TOVARA";

CREATE GENERATOR "GEN_TIP_ULICI";

CREATE GENERATOR "GEN_TIP_VED";

CREATE GENERATOR "GEN_TOVAR";

CREATE GENERATOR "GEN_ULICA";

CREATE GENERATOR "GEN_VEDOMOST";

CREATE GENERATOR "GEN_VID_CENI";

 

/* View: VIEW_PRICE_R, Owner: SYSDBA */

 

CREATE VIEW "VIEW_PRICE_R" (

"NAZV",

"CENA"

) AS

 

SELECT TOVAR.NAZV, POZIC_PR.CENA

FROM TOVAR, POZIC_PR

WHERE POZIC_PR.KOD_PR=(SELECT MAX(PRICE.NOMER) FROM PRICE) AND

POZIC_PR.KOD_TOV=TOVAR.KOD AND POZIC_PR.KOD_VC=1

;

 

/* View: VIEW_PRICE_M, Owner: SYSDBA */

 

CREATE VIEW "VIEW_PRICE_M" (

"NAZV",

"CENA"

) AS

 

SELECT TOVAR.NAZV, POZIC_PR.CENA

FROM TOVAR, POZIC_PR

WHERE POZIC_PR.KOD_PR=(SELECT MAX(PRICE.NOMER) FROM PRICE) AND

POZIC_PR.KOD_TOV=TOVAR.KOD AND POZIC_PR.KOD_VC=2

;

 

/* View: VIEW_PRICE_O, Owner: SYSDBA */

 

CREATE VIEW "VIEW_PRICE_O" (

"NAZV",

"CENA"

) AS

 

SELECT TOVAR.NAZV, POZIC_PR.CENA

FROM TOVAR, POZIC_PR

WHERE POZIC_PR.KOD_PR=(SELECT MAX(PRICE.NOMER) FROM PRICE) AND

POZIC_PR.KOD_TOV=TOVAR.KOD AND POZIC_PR.KOD_VC=3

;

 

/* View: VIEW_PRICE_Z, Owner: SYSDBA */

 

CREATE VIEW "VIEW_PRICE_Z" (

"NAZV",

"CENA"

) AS

 

SELECT TOVAR.NAZV, POZIC_PR.CENA

FROM TOVAR, POZIC_PR

WHERE POZIC_PR.KOD_PR=(SELECT MAX(PRICE.NOMER) FROM PRICE) AND

POZIC_PR.KOD_TOV=TOVAR.KOD AND POZIC_PR.KOD_VC=4

;

 

/* View: VIEW_PRIH_VED, Owner: SYSDBA */

 

CREATE VIEW "VIEW_PRIH_VED" (

"NOMER",

"DATA_V",

"NAZV_T",

"KOL",

"CENA",

"SUMMA"

) AS

 

SELECT VEDOMOST.NOMER, VEDOMOST.DATA_V, TOVAR.NAZV, POZIC_VEDOM.KOL,

POZIC_PR.CENA, POZIC_VEDOM.KOL * POZIC_PR.CENA

FROM VEDOMOST, TOVAR, POZIC_VEDOM, POZIC_PR

WHERE VEDOMOST.NOMER=POZIC_VEDOM.KOD_VED AND TOVAR.KOD=POZIC_VEDOM.KOD_TOV

AND VEDOMOST.KOD_TV=1 AND POZIC_VEDOM.KOD_PPR=POZIC_PR.NOMER

;

 

/* View: VIEW_RAS_VED, Owner: SYSDBA */

 

CREATE VIEW "VIEW_RAS_VED" (

"NOMER",

"DATA_V",

"NAZV_T",

"KOL",

"CENA",

"SUMMA"

) AS

 

SELECT VEDOMOST.NOMER, VEDOMOST.DATA_V, TOVAR.NAZV, POZIC_VEDOM.KOL,

POZIC_PR.CENA, POZIC_VEDOM.KOL * POZIC_PR.CENA

FROM VEDOMOST, TOVAR, POZIC_VEDOM, POZIC_PR

WHERE VEDOMOST.NOMER=POZIC_VEDOM.KOD_VED AND TOVAR.KOD=POZIC_VEDOM.KOD_TOV

AND VEDOMOST.KOD_TV=2 AND POZIC_VEDOM.KOD_PPR=POZIC_PR.NOMER

;

ALTER TABLE "DOGOVOR" ADD

CHECK(KOD_DOL>0);

 

ALTER TABLE "DOGOVOR" ADD

CHECK(KOD_SE>0);

 

ALTER TABLE "DOGOVOR" ADD

CHECK(KOD_FL>0);

 

ALTER TABLE "FIZ_LICO" ADD

CHECK(KOD_P>0);

 

ALTER TABLE "TIP_VED" ADD

CHECK(KOD>0);

 

ALTER TABLE "PRICE" ADD

CHECK(NOMER>0);

 

ALTER TABLE "POZIC_PR" ADD

CHECK(NOMER>0);

 

ALTER TABLE "POZIC_PR" ADD

CHECK(CENA>0);

 

ALTER TABLE "POZIC_PR" ADD

CHECK(KOD_TOV>0);

 

ALTER TABLE "POZIC_PR" ADD

CHECK(KOD_PR>0);

 

ALTER TABLE "POZIC_PR" ADD

CHECK(KOD_EI>0);

 

ALTER TABLE "POZIC_PR" ADD

CHECK(KOD_VC>0);

 

ALTER TABLE "VEDOMOST" ADD

CHECK(NOMER>0);

 

ALTER TABLE "VEDOMOST" ADD

CHECK(KOD_TV>0);

 

ALTER TABLE "VEDOMOST" ADD

CHECK(KOD_SE_SC>0);

 

ALTER TABLE "VEDOMOST" ADD

CHECK(KOD_MEN>0);

 

ALTER TABLE "VEDOMOST" ADD

CHECK(KOD_ZAV>0);

 

ALTER TABLE "TIP_TOVARA" ADD

CHECK(KOD>0);

 

ALTER TABLE "POZIC_VEDOM" ADD

CHECK(NOMER>0);

 

ALTER TABLE "POZIC_VEDOM" ADD

CHECK(KOL>0);

 

ALTER TABLE "POZIC_VEDOM" ADD

CHECK(KOD_TOV>0);

 

ALTER TABLE "POZIC_VEDOM" ADD

CHECK(KOD_PPR>0);

 

ALTER TABLE "POZIC_VEDOM" ADD

CHECK(KOD_EI>0);

 

ALTER TABLE "POZIC_VEDOM" ADD

CHECK(KOD_VED>0);

 

ALTER TABLE "TIP_TEL" ADD

CHECK(KOD>0);

 

ALTER TABLE "TELEFON" ADD

CHECK(KOD>0);

 

ALTER TABLE "TELEFON" ADD

CHECK(KOD_TT>0);

 

ALTER TABLE "POL_FIZ_LICA" ADD

CHECK(KOD>0);

 

ALTER TABLE "TOVAR" ADD

CHECK(KOD>0);

 

ALTER TABLE "TOVAR" ADD

CHECK(KOD_TIPA>0);

 

ALTER TABLE "VID_CENI" ADD

CHECK(KOD>0);

 

ALTER TABLE "TIP_STRUCT_EDIN" ADD

CHECK(KOD>0);

 

ALTER TABLE "STRUCT_EDIN" ADD

CHECK(KOD>0);

 

ALTER TABLE "STRUCT_EDIN" ADD

CHECK(KOD_T_SE>0);

 

ALTER TABLE "TIP_ULICI" ADD

CHECK(KOD>0);

 

ALTER TABLE "ULICA" ADD

CHECK(KOD>0);

 

ALTER TABLE "ULICA" ADD

CHECK(KOD_TIPA>0);

 

ALTER TABLE "TIP_NAS_PUNKTA" ADD

CHECK(KOD>0);

 

ALTER TABLE "NAS_PUNKT" ADD

CHECK(KOD>0);

 

ALTER TABLE "NAS_PUNKT" ADD

CHECK(KOD_TIPA>0);

 

ALTER TABLE "FIZ_LICO" ADD

CHECK(T_NOMER>0);

 

ALTER TABLE "DOLGNOST" ADD

CHECK(KOD>0);

 

ALTER TABLE "EDIN_IZM" ADD

CHECK(KOD>0);

 

ALTER TABLE "ADRES" ADD

CHECK(NOMER>0);

 

ALTER TABLE "ADRES" ADD

CHECK(DOM>0);

 

ALTER TABLE "ADRES" ADD

CHECK(KOD_UL>0);

 

ALTER TABLE "ADRES" ADD

CHECK(KOD_NP>0);

 

ALTER TABLE "DOGOVOR" ADD

CHECK(NOMER>0);

 

COMMIT WORK;

SET AUTODDL OFF;

 

/* Stored procedures */

 

CREATE PROCEDURE "DISKAN"

(

"PKODVID" INTEGER,

"PDATE" DATE

)

RETURNS

(

"KOL_ED" FLOAT,

"SUMMA" FLOAT

)

AS

BEGIN EXIT; END ;

 

 

ALTER PROCEDURE "DISKAN"

(

"PKODVID" INTEGER,

"PDATE" DATE

)

RETURNS

(

"KOL_ED" FLOAT,

"SUMMA" FLOAT

)

AS

begin

 

select sum(POZIC_VEDOM.KOL)

from VEDOMOST, POZIC_VEDOM, POZIC_PR

where (POZIC_VEDOM.KOD_VED=VEDOMOST.NOMER) AND (VEDOMOST.KOD_TV=2) AND

(VEDOMOST.DATA_V=:"PDATE") AND (POZIC_VEDOM.KOD_PPR=POZIC_PR.NOMER)

AND (POZIC_PR.KOD_VC=:"PKODVID")

into :kol_ed;

 

select sum(POZIC_VEDOM.KOL*POZIC_PR.CENA)

from VEDOMOST, POZIC_VEDOM, POZIC_PR

where (POZIC_VEDOM.KOD_VED=VEDOMOST.NOMER) AND (VEDOMOST.KOD_TV=2) AND

(VEDOMOST.DATA_V=:"PDATE") AND (POZIC_VEDOM.KOD_PPR=POZIC_PR.NOMER)

AND (POZIC_PR.KOD_VC=:"PKODVID")

into :summa;

 

exit;

end

;

 

COMMIT WORK;

SET AUTODDL ON;

 

/* Triggers only will work for SQL triggers */

 

CREATE TRIGGER "BEF_INS_ADRES" FOR "ADRES"

ACTIVE BEFORE INSERT POSITION 0

AS

BEGIN

NEW.NOMER = GEN_ID(GEN_ADRES,1);

END

;

 

CREATE TRIGGER "BEF_INS_DOGOVOR" FOR "DOGOVOR"

ACTIVE BEFORE INSERT POSITION 0

AS

BEGIN

NEW.NOMER = GEN_ID(GEN_DOGOVOR,1);

END

;

CREATE TRIGGER "BEF_INS_DOLGNOST" FOR "DOLGNOST"

ACTIVE BEFORE INSERT POSITION 0

AS

BEGIN

NEW.KOD = GEN_ID(GEN_DOLGNOST,1);

END

;

 

CREATE TRIGGER "BEF_INS_EDIN_IZM" FOR "EDIN_IZM"

ACTIVE BEFORE INSERT POSITION 0

AS

BEGIN

NEW.KOD = GEN_ID(GEN_EDIN_IZM,1);

END

;

 

CREATE TRIGGER "BEF_INS_FIZ_LICO" FOR "FIZ_LICO"

ACTIVE BEFORE INSERT POSITION 0

AS

BEGIN

NEW.T_NOMER = GEN_ID(GEN_FIZ_LICO,1);

END

;

 

CREATE TRIGGER "BEF_INS_NAS_PUNKT" FOR "NAS_PUNKT"

ACTIVE BEFORE INSERT POSITION 0

AS

BEGIN

NEW.KOD = GEN_ID(GEN_NAS_PUNKT,1);

END

;

 

CREATE TRIGGER "BEF_INS_POL_FIZ_LICA" FOR "POL_FIZ_LICA"

ACTIVE BEFORE INSERT POSITION 0

AS

BEGIN

NEW.KOD = GEN_ID(GEN_POL_FIZ_LICA,1);

END

;

 

CREATE TRIGGER "BEF_INS_POZIC_PR" FOR "POZIC_PR"

ACTIVE BEFORE INSERT POSITION 0

AS

BEGIN

NEW.NOMER = GEN_ID(GEN_POZIC_PR,1);

END

;

 

CREATE TRIGGER "BEF_INS_POZIC_VEDOM" FOR "POZIC_VEDOM"

ACTIVE BEFORE INSERT POSITION 0

AS

BEGIN

NEW.NOMER = GEN_ID(GEN_POZIC_VEDOM,1);

END

;

 

CREATE TRIGGER "BEF_INS_PRICE" FOR "PRICE"

ACTIVE BEFORE INSERT POSITION 0

AS

BEGIN

NEW.NOMER = GEN_ID(GEN_PRICE,1);

END

;

CREATE TRIGGER "BEF_INS_STRUCT_EDIN" FOR "STRUCT_EDIN"

ACTIVE BEFORE INSERT POSITION 0

AS

BEGIN

NEW.KOD = GEN_ID(GEN_STRUCT_EDIN,1);

END

;

 

CREATE TRIGGER "BEF_INS_TELEFON" FOR "TELEFON"

ACTIVE BEFORE INSERT POSITION 0

AS

BEGIN

NEW.KOD = GEN_ID(GEN_TELEFON,1);

END

;

 

CREATE TRIGGER "BEF_INS_TIP_NAS_PUNKTA" FOR "TIP_NAS_PUNKTA"

ACTIVE BEFORE INSERT POSITION 0

AS

BEGIN

NEW.KOD = GEN_ID(GEN_TIP_NAS_PUNKTA,1);

END

;

 

CREATE TRIGGER "BEF_INS_TIP_STRUCT_EDIN" FOR "TIP_STRUCT_EDIN"

ACTIVE BEFORE INSERT POSITION 0

AS

BEGIN

NEW.KOD = GEN_ID(GEN_TIP_STRUCT_EDIN,1);

END

;

 

CREATE TRIGGER "BEF_INS_TIP_TEL" FOR "TIP_TEL"

ACTIVE BEFORE INSERT POSITION 0

AS

BEGIN

NEW.KOD = GEN_ID(GEN_TIP_TEL,1);

END

;

 

CREATE TRIGGER "BEF_INS_TIP_TOVARA" FOR "TIP_TOVARA"

ACTIVE BEFORE INSERT POSITION 0

AS

BEGIN

NEW.KOD = GEN_ID(GEN_TIP_TOVARA,1);

END

;

 

CREATE TRIGGER "BEF_INS_TIP_ULICI" FOR "TIP_ULICI"

ACTIVE BEFORE INSERT POSITION 0

AS

BEGIN

NEW.KOD = GEN_ID(GEN_TIP_ULICI,1);

END

;

 

CREATE TRIGGER "BEF_INS_TIP_VED" FOR "TIP_VED"

ACTIVE BEFORE INSERT POSITION 0

AS

BEGIN

NEW.KOD = GEN_ID(GEN_TIP_VED,1);

END

;

CREATE TRIGGER "BEF_INS_TOVAR" FOR "TOVAR"

ACTIVE BEFORE INSERT POSITION 0

AS

BEGIN

NEW.KOD = GEN_ID(GEN_TOVAR,1);

END

;

 

CREATE TRIGGER "BEF_INS_ULICA" FOR "ULICA"

ACTIVE BEFORE INSERT POSITION 0

AS

BEGIN

NEW.KOD = GEN_ID(GEN_ULICA,1);

END

;

 

CREATE TRIGGER "BEF_INS_VEDOMOST" FOR "VEDOMOST"

ACTIVE BEFORE INSERT POSITION 0

AS

BEGIN

NEW.NOMER = GEN_ID(GEN_VEDOMOST,1);

END

;

 

CREATE TRIGGER "BEF_INS_VID_CENI" FOR "VID_CENI"

ACTIVE BEFORE INSERT POSITION 0

AS

BEGIN

NEW.KOD = GEN_ID(GEN_VID_CENI,1);

END

;

 

COMMIT WORK ;

 

/* Grant Roles for this database */

 

 

/* Role: "ROLE_ADMINISTRATOR", Owner: SYSDBA */

 

CREATE ROLE "ROLE_ADMINISTRATOR";

CREATE ROLE "ROLE_MENEDGER";

CREATE ROLE "ROLE_PROGRAMMIST";

CREATE ROLE "ROLE_ZAVSCLADOM";

 

/* Grant permissions for this database */

 

GRANT DELETE, INSERT, SELECT, UPDATE, REFERENCES ON "ADRES" TO "ROLE_ADMINISTRATOR" WITH GRANT OPTION;

GRANT INSERT, SELECT, UPDATE ON "ADRES" TO "ROLE_MENEDGER";

GRANT INSERT, SELECT, UPDATE, REFERENCES ON "ADRES" TO "ROLE_PROGRAMMIST" WITH GRANT OPTION;

GRANT SELECT ON "ADRES" TO "ROLE_ZAVSCLADOM";

GRANT DELETE, INSERT, SELECT, UPDATE, REFERENCES ON "DOGOVOR" TO "ROLE_ADMINISTRATOR" WITH GRANT OPTION;

GRANT INSERT, SELECT, UPDATE ON "DOGOVOR" TO "ROLE_MENEDGER";

GRANT INSERT, SELECT, UPDATE, REFERENCES ON "DOGOVOR" TO "ROLE_PROGRAMMIST" WITH GRANT OPTION;

GRANT SELECT ON "DOGOVOR" TO "ROLE_ZAVSCLADOM";

GRANT DELETE, INSERT, SELECT, UPDATE, REFERENCES ON "DOLGNOST" TO "ROLE_ADMINISTRATOR" WITH GRANT OPTION;

GRANT INSERT, SELECT, UPDATE ON "DOLGNOST" TO "ROLE_MENEDGER";

GRANT INSERT, SELECT, UPDATE, REFERENCES ON "DOLGNOST" TO "ROLE_PROGRAMMIST" WITH GRANT OPTION;

GRANT SELECT ON "DOLGNOST" TO "ROLE_ZAVSCLADOM";

GRANT DELETE, INSERT, SELECT, UPDATE, REFERENCES ON "EDIN_IZM" TO "ROLE_ADMINISTRATOR" WITH GRANT OPTION;

GRANT INSERT, SELECT, UPDATE ON "EDIN_IZM" TO "ROLE_MENEDGER";

GRANT INSERT, SELECT, UPDATE, REFERENCES ON "EDIN_IZM" TO "ROLE_PROGRAMMIST" WITH GRANT OPTION;

GRANT SELECT ON "EDIN_IZM" TO "ROLE_ZAVSCLADOM";

GRANT DELETE, INSERT, SELECT, UPDATE, REFERENCES ON "FIZ_LICO" TO "ROLE_ADMINISTRATOR" WITH GRANT OPTION;

GRANT INSERT, SELECT, UPDATE ON "FIZ_LICO" TO "ROLE_MENEDGER";

GRANT INSERT, SELECT, UPDATE, REFERENCES ON "FIZ_LICO" TO "ROLE_PROGRAMMIST" WITH GRANT OPTION;

GRANT SELECT ON "FIZ_LICO" TO "ROLE_ZAVSCLADOM";

GRANT DELETE, INSERT, SELECT, UPDATE, REFERENCES ON "NAS_PUNKT" TO "ROLE_ADMINISTRATOR" WITH GRANT OPTION;

GRANT INSERT, SELECT, UPDATE ON "NAS_PUNKT" TO "ROLE_MENEDGER";

GRANT INSERT, SELECT, UPDATE, REFERENCES ON "NAS_PUNKT" TO "ROLE_PROGRAMMIST" WITH GRANT OPTION;

GRANT SELECT ON "NAS_PUNKT" TO "ROLE_ZAVSCLADOM";

GRANT DELETE, INSERT, SELECT, UPDATE, REFERENCES ON "POL_FIZ_LICA" TO "ROLE_ADMINISTRATOR" WITH GRANT OPTION;

GRANT INSERT, SELECT, UPDATE ON "POL_FIZ_LICA" TO "ROLE_MENEDGER";

GRANT INSERT, SELECT, UPDATE, REFERENCES ON "POL_FIZ_LICA" TO "ROLE_PROGRAMMIST" WITH GRANT OPTION;

GRANT SELECT ON "POL_FIZ_LICA" TO "ROLE_ZAVSCLADOM";

GRANT DELETE, INSERT, SELECT, UPDATE, REFERENCES ON "POZIC_PR" TO "ROLE_ADMINISTRATOR" WITH GRANT OPTION;

GRANT INSERT, SELECT, UPDATE ON "POZIC_PR" TO "ROLE_MENEDGER";

GRANT INSERT, SELECT, UPDATE, REFERENCES ON "POZIC_PR" TO "ROLE_PROGRAMMIST" WITH GRANT OPTION;

GRANT SELECT ON "POZIC_PR" TO "ROLE_ZAVSCLADOM";

GRANT DELETE, INSERT, SELECT, UPDATE, REFERENCES ON "POZIC_VEDOM" TO "ROLE_ADMINISTRATOR" WITH GRANT OPTION;

GRANT INSERT, SELECT, UPDATE ON "POZIC_VEDOM" TO "ROLE_MENEDGER";

GRANT INSERT, SELECT, UPDATE, REFERENCES ON "POZIC_VEDOM" TO "ROLE_PROGRAMMIST" WITH GRANT OPTION;

GRANT SELECT ON "POZIC_VEDOM" TO "ROLE_ZAVSCLADOM";

GRANT DELETE, INSERT, SELECT, UPDATE, REFERENCES ON "PRICE" TO "ROLE_ADMINISTRATOR" WITH GRANT OPTION;

GRANT INSERT, SELECT, UPDATE ON "PRICE" TO "ROLE_MENEDGER";

GRANT INSERT, SELECT, UPDATE, REFERENCES ON "PRICE" TO "ROLE_PROGRAMMIST" WITH GRANT OPTION;

GRANT SELECT ON "PRICE" TO "ROLE_ZAVSCLADOM";

GRANT DELETE, INSERT, SELECT, UPDATE, REFERENCES ON "STRUCT_EDIN" TO "ROLE_ADMINISTRATOR" WITH GRANT OPTION;

GRANT INSERT, SELECT, UPDATE ON "STRUCT_EDIN" TO "ROLE_MENEDGER";

GRANT INSERT, SELECT, UPDATE, REFERENCES ON "STRUCT_EDIN" TO "ROLE_PROGRAMMIST" WITH GRANT OPTION;

GRANT SELECT ON "STRUCT_EDIN" TO "ROLE_ZAVSCLADOM";

GRANT DELETE, INSERT, SELECT, UPDATE, REFERENCES ON "TELEFON" TO "ROLE_ADMINISTRATOR" WITH GRANT OPTION;

GRANT INSERT, SELECT, UPDATE ON "TELEFON" TO "ROLE_MENEDGER";

GRANT INSERT, SELECT, UPDATE, REFERENCES ON "TELEFON" TO "ROLE_PROGRAMMIST" WITH GRANT OPTION;

GRANT SELECT ON "TELEFON" TO "ROLE_ZAVSCLADOM";

GRANT DELETE, INSERT, SELECT, UPDATE, REFERENCES ON "TIP_NAS_PUNKTA" TO "ROLE_ADMINISTRATOR" WITH GRANT OPTION;

GRANT INSERT, SELECT, UPDATE ON "TIP_NAS_PUNKTA" TO "ROLE_MENEDGER";

GRANT INSERT, SELECT, UPDATE, REFERENCES ON "TIP_NAS_PUNKTA" TO "ROLE_PROGRAMMIST" WITH GRANT OPTION;

GRANT SELECT ON "TIP_NAS_PUNKTA" TO "ROLE_ZAVSCLADOM";

GRANT DELETE, INSERT, SELECT, UPDATE, REFERENCES ON "TIP_STRUCT_EDIN" TO "ROLE_ADMINISTRATOR" WITH GRANT OPTION;

GRANT INSERT, SELECT, UPDATE ON "TIP_STRUCT_EDIN" TO "ROLE_MENEDGER";

GRANT INSERT, SELECT, UPDATE, REFERENCES ON "TIP_STRUCT_EDIN" TO "ROLE_PROGRAMMIST" WITH GRANT OPTION;

GRANT SELECT ON "TIP_STRUCT_EDIN" TO "ROLE_ZAVSCLADOM";

GRANT DELETE, INSERT, SELECT, UPDATE, REFERENCES ON "TIP_TEL" TO "ROLE_ADMINISTRATOR" WITH GRANT OPTION;

GRANT INSERT, SELECT, UPDATE ON "TIP_TEL" TO "ROLE_MENEDGER";

GRANT INSERT, SELECT, UPDATE, REFERENCES ON "TIP_TEL" TO "ROLE_PROGRAMMIST" WITH GRANT OPTION;

GRANT SELECT ON "TIP_TEL" TO "ROLE_ZAVSCLADOM";

GRANT DELETE, INSERT, SELECT, UPDATE, REFERENCES ON "TIP_TOVARA" TO "ROLE_ADMINISTRATOR" WITH GRANT OPTION;

GRANT INSERT, SELECT, UPDATE ON "TIP_TOVARA" TO "ROLE_MENEDGER";

GRANT INSERT, SELECT, UPDATE, REFERENCES ON "TIP_TOVARA" TO "ROLE_PROGRAMMIST" WITH GRANT OPTION;

GRANT SELECT ON "TIP_TOVARA" TO "ROLE_ZAVSCLADOM";

GRANT DELETE, INSERT, SELECT, UPDATE, REFERENCES ON "TIP_ULICI" TO "ROLE_ADMINISTRATOR" WITH GRANT OPTION;

GRANT INSERT, SELECT, UPDATE ON "TIP_ULICI" TO "ROLE_MENEDGER";

GRANT INSERT, SELECT, UPDATE, REFERENCES ON "TIP_ULICI" TO "ROLE_PROGRAMMIST" WITH GRANT OPTION;

GRANT SELECT ON "TIP_ULICI" TO "ROLE_ZAVSCLADOM";

GRANT DELETE, INSERT, SELECT, UPDATE, REFERENCES ON "TIP_VED" TO "ROLE_ADMINISTRATOR" WITH GRANT OPTION;

GRANT INSERT, SELECT, UPDATE ON "TIP_VED" TO "ROLE_MENEDGER";

GRANT INSERT, SELECT, UPDATE, REFERENCES ON "TIP_VED" TO "ROLE_PROGRAMMIST" WITH GRANT OPTION;

GRANT SELECT ON "TIP_VED" TO "ROLE_ZAVSCLADOM";

GRANT DELETE, INSERT, SELECT, UPDATE, REFERENCES ON "TOVAR" TO "ROLE_ADMINISTRATOR" WITH GRANT OPTION;

GRANT INSERT, SELECT, UPDATE ON "TOVAR" TO "ROLE_MENEDGER";

GRANT INSERT, SELECT, UPDATE, REFERENCES ON "TOVAR" TO "ROLE_PROGRAMMIST" WITH GRANT OPTION;

GRANT SELECT ON "TOVAR" TO "ROLE_ZAVSCLADOM";

GRANT DELETE, INSERT, SELECT, UPDATE, REFERENCES ON "ULICA" TO "ROLE_ADMINISTRATOR" WITH GRANT OPTION;

GRANT INSERT, SELECT, UPDATE ON "ULICA" TO "ROLE_MENEDGER";

GRANT INSERT, SELECT, UPDATE, REFERENCES ON "ULICA" TO "ROLE_PROGRAMMIST" WITH GRANT OPTION;

GRANT SELECT ON "ULICA" TO "ROLE_ZAVSCLADOM";

GRANT DELETE, INSERT, SELECT, UPDATE, REFERENCES ON "VEDOMOST" TO "ROLE_ADMINISTRATOR" WITH GRANT OPTION;

GRANT INSERT, SELECT, UPDATE ON "VEDOMOST" TO "ROLE_MENEDGER";

GRANT INSERT, SELECT, UPDATE, REFERENCES ON "VEDOMOST" TO "ROLE_PROGRAMMIST" WITH GRANT OPTION;

GRANT SELECT ON "VEDOMOST" TO "ROLE_ZAVSCLADOM";

GRANT DELETE, INSERT, SELECT, UPDATE, REFERENCES ON "VID_CENI" TO "ROLE_ADMINISTRATOR" WITH GRANT OPTION;

GRANT INSERT, SELECT, UPDATE ON "VID_CENI" TO "ROLE_MENEDGER";

GRANT INSERT, SELECT, UPDATE, REFERENCES ON "VID_CENI" TO "ROLE_PROGRAMMIST" WITH GRANT OPTION;

GRANT SELECT ON "VID_CENI" TO "ROLE_ZAVSCLADOM";

GRANT DELETE, INSERT, SELECT, UPDATE, REFERENCES ON "VIEW_PRICE_M" TO "ROLE_ADMINISTRATOR" WITH GRANT OPTION;

GRANT INSERT, SELECT, UPDATE ON "VIEW_PRICE_M" TO "ROLE_MENEDGER";

GRANT INSERT, SELECT, UPDATE, REFERENCES ON "VIEW_PRICE_M" TO "ROLE_PROGRAMMIST" WITH GRANT OPTION;

GRANT SELECT ON "VIEW_PRICE_M" TO "ROLE_ZAVSCLADOM";

GRANT DELETE, INSERT, SELECT, UPDATE, REFERENCES ON "VIEW_PRICE_O" TO "ROLE_ADMINISTRATOR" WITH GRANT OPTION;

GRANT INSERT, SELECT, UPDATE ON "VIEW_PRICE_O" TO "ROLE_MENEDGER";

GRANT INSERT, SELECT, UPDATE, REFERENCES ON "VIEW_PRICE_O" TO "ROLE_PROGRAMMIST" WITH GRANT OPTION;

GRANT SELECT ON "VIEW_PRICE_O" TO "ROLE_ZAVSCLADOM";

GRANT DELETE, INSERT, SELECT, UPDATE, REFERENCES ON "VIEW_PRICE_R" TO "ROLE_ADMINISTRATOR" WITH GRANT OPTION;

GRANT INSERT, SELECT, UPDATE ON "VIEW_PRICE_R" TO "ROLE_MENEDGER";

GRANT INSERT, SELECT, UPDATE, REFERENCES ON "VIEW_PRICE_R" TO "ROLE_PROGRAMMIST" WITH GRANT OPTION;

GRANT SELECT ON "VIEW_PRICE_R" TO "ROLE_ZAVSCLADOM";

GRANT DELETE, INSERT, SELECT, UPDATE, REFERENCES ON "VIEW_PRICE_Z" TO "ROLE_ADMINISTRATOR" WITH GRANT OPTION;

GRANT INSERT, SELECT, UPDATE ON "VIEW_PRICE_Z" TO "ROLE_MENEDGER";

GRANT INSERT, SELECT, UPDATE, REFERENCES ON "VIEW_PRICE_Z" TO "ROLE_PROGRAMMIST" WITH GRANT OPTION;

GRANT SELECT ON "VIEW_PRICE_Z" TO "ROLE_ZAVSCLADOM";

GRANT DELETE, INSERT, SELECT, UPDATE, REFERENCES ON "VIEW_PRIH_VED" TO "ROLE_ADMINISTRATOR" WITH GRANT OPTION;

GRANT INSERT, SELECT, UPDATE ON "VIEW_PRIH_VED" TO "ROLE_MENEDGER";

GRANT INSERT, SELECT, UPDATE, REFERENCES ON "VIEW_PRIH_VED" TO "ROLE_PROGRAMMIST" WITH GRANT OPTION;

GRANT SELECT ON "VIEW_PRIH_VED" TO "ROLE_ZAVSCLADOM";

GRANT DELETE, INSERT, SELECT, UPDATE, REFERENCES ON "VIEW_RAS_VED" TO "ROLE_ADMINISTRATOR" WITH GRANT OPTION;

GRANT INSERT, SELECT, UPDATE ON "VIEW_RAS_VED" TO "ROLE_MENEDGER";

GRANT INSERT, SELECT, UPDATE, REFERENCES ON "VIEW_RAS_VED" TO "ROLE_PROGRAMMIST" WITH GRANT OPTION;

GRANT SELECT ON "VIEW_RAS_VED" TO "ROLE_ZAVSCLADOM";

GRANT "ROLE_ADMINISTRATOR" TO ADMINISTRATOR;

 

GRANT "ROLE_MENEDGER" TO MENEDGER;

 

GRANT "ROLE_PROGRAMMIST" TO PROGRAMMIST;

 

GRANT "ROLE_ZAVSCLADOM" TO ZAVSCLADOM;

 

GRANT EXECUTE ON PROCEDURE "DISKAN" TO "ROLE_ADMINISTRATOR" WITH GRANT OPTION;

GRANT EXECUTE ON PROCEDURE "DISKAN" TO "ROLE_MENEDGER";

GRANT EXECUTE ON PROCEDURE "DISKAN" TO "ROLE_PROGRAMMIST" WITH GRANT OPTION;

GRANT EXECUTE ON PROCEDURE "DISKAN" TO "ROLE_ZAVSCLADOM";


Приложение В

(обязательное)

Листинг программы

 

В отдельном файле

Последнее изменение этой страницы: 2016-06-09

lectmania.ru. Все права принадлежат авторам данных материалов. В случае нарушения авторского права напишите нам сюда...