|
/******************************************************************************/
/*** Generated by IBExpert 2010. 01. 22 28. 05.2013 13: 40: 53 ***/
/******************************************************************************/
SET SQL DIALECT 3; SET NAMES WIN1251; CREATE DATABASE '172. 23. 64. 64: //
home/ftp/10/1410028/G141001_YUSHCHENKO. FDB' USER 'SYSDBA' PASSWORD
'masterkey' PAGE_SIZE 16384 DEFAULT CHARACTER SET WIN1251;
/******************************************************************************/
/*** Domains ***/ /******************************************************************************/
CREATE DOMAIN D_CEN AS DECIMAL (10,2) NOT NULL CHECK (value>0); CREATE
DOMAIN D_DATA AS TIMESTAMP NOT NULL CHECK (value<='TODAY'); CREATE DOMAIN
D_NO AS SMALLINT NOT NULL CHECK (value >0); CREATE DOMAIN D_VCH AS VARCHAR
(40) NOT NULL;
******************************************************************************/
/*** Generators ***/
/******************************************************************************/
CREATE GENERATOR GEN_KLIENT_NO; SET GENERATOR GEN_KLIENT_NO TO 9; CREATE
GENERATOR GEN_KONS_NO; SET GENERATOR GEN_KONS_NO TO 5; CREATE GENERATOR
GEN_KORZ_NO; SET GENERATOR GEN_KORZ_NO TO 5; CREATE GENERATOR GEN_POKUP_NO;
SET GENERATOR GEN_POKUP_NO TO 6; CREATE GENERATOR GEN_PROIZ_NO; SET GENERATOR
GEN_PROIZ_NO TO 5; CREATE GENERATOR GEN_TOVAR_NO; SET GENERATOR GEN_TOVAR_NO
TO 5; CREATE GENERATOR GEN_USTR_NO; SET GENERATOR GEN_USTR_NO TO 7;
/******************************************************************************/
/*** Exceptions ***/ /******************************************************************************/
CREATE EXCEPTION EXC_NODATA 'не введены числовые данные'; CREATE EXCEPTION
EXC_NOTEXT 'не введены текстовые данные'; SET TERM; ^
/******************************************************************************/
/*** Stored Procedures ***/
/******************************************************************************/
CREATE PROCEDURE DEL_KLI ( NO_KLI SMALLINT) AS BEGIN EXIT; END^ CREATE
PROCEDURE DEL_KONS ( NO_KONS SMALLINT) AS BEGIN EXIT; END^ CREATE PROCEDURE
DEL_KORZ ( NO_KORZ SMALLINT) AS BEGIN EXIT; END^ CREATE PROCEDURE DEL_POKUP (
NO_POKUPKI SMALLINT) AS BEGIN EXIT; END^ CREATE PROCEDURE DEL_PROIZ (
NO_PROIZ SMALLINT) AS BEGIN EXIT; END^ CREATE PROCEDURE DEL_TOV ( NO_TOV SMALLINT)
AS BEGIN EXIT; END^ CREATE PROCEDURE DEL_USTR ( NO_USTR SMALLINT) AS BEGIN
EXIT; END^ CREATE PROCEDURE INSERT_KLI ( NO_KLI SMALLINT, FAMILIYA VARCHAR
(40), IMYA VARCHAR (40), OTCHESTVO VARCHAR (40), TELEFON VARCHAR (40), E_MAIL
VARCHAR (40), GOROD VARCHAR (40), ULICA VARCHAR (40), DOM VARCHAR (40)) AS
BEGIN EXIT; END^ CREATE PROCEDURE INSERT_KONS ( NO_KONS SMALLINT,
FAMILIYAKONS VARCHAR (40), IMYAKONS VARCHAR (40), OTCHESTVOKONS VARCHAR (40),
TELEFONKONS VARCHAR (40), GORODKONS VARCHAR (40), ULICAKONS VARCHAR (40),
DOMKONS VARCHAR (40), DATAPRIEMA VARCHAR (40)) AS BEGIN EXIT; END^ CREATE
PROCEDURE INSERT_KORZ ( NO_KORZ SMALLINT, NO_TOV SMALLINT, KOLICHESTVO
VARCHAR (40)) AS BEGIN EXIT; END^ CREATE PROCEDURE INSERT_POKUP ( NO_POKUPKI
SMALLINT, NO_KORZ SMALLINT, NO_KLI SMALLINT, NO_KONS SMALLINT, DATAPRIDAJI
VARCHAR (40), CENAGLAV DECIMAL (10,2)) AS BEGIN EXIT; END^ CREATE PROCEDURE
INSERT_PROIZ ( NO_PROIZ SMALLINT, NAIMPROIZV VARCHAR (40)) AS BEGIN EXIT;
END^ CREATE PROCEDURE INSERT_TOV ( NO_TOV SMALLINT, NO_USTR SMALLINT,
NO_PROIZ SMALLINT, MODEL VARCHAR (40), SERIALNO VARCHAR (40), PARTNO VARCHAR
(40), CENA DECIMAL (10,2)) AS BEGIN EXIT; END^ CREATE PROCEDURE INSERT_TOV1 (
NO_TOV SMALLINT, SERIALNO VARCHAR (40), PARTNO VARCHAR (40), CENA DECIMAL
(10,2), MODEL VARCHAR (40), NO_USTR SMALLINT, NO_PROIZ SMALLINT) AS BEGIN
EXIT; END^ CREATE PROCEDURE INSERT_USTR ( NO_USTR SMALLINT, NAIMENOVANIE
VARCHAR (40)) AS BEGIN EXIT; END^ CREATE PROCEDURE UPD_KLI ( NO_KLI SMALLINT,
FAMILIYA VARCHAR (40), IMYA VARCHAR (40), OTCHESTVO VARCHAR (40), TELEFON
VARCHAR (40), E_MAIL VARCHAR (40), GOROD VARCHAR (40), ULICA VARCHAR (40),
DOM VARCHAR (40)) AS BEGIN EXIT; END^ CREATE PROCEDURE UPD_KONS ( NO_KONS
SMALLINT, FAMILIYAKONS VARCHAR (40), IMYAKONS VARCHAR (40), OTCHESTVOKONS
VARCHAR (40), TELEFONKONS VARCHAR (40), GORODKONS VARCHAR (40), ULICAKONS
VARCHAR (40), DOMKONS VARCHAR (40), DATAPRIEMA VARCHAR (40)) AS BEGIN EXIT;
END^ CREATE PROCEDURE UPD_KORZ ( NO_KORZ SMALLINT, NO_TOV SMALLINT,
KOLICHESTVO VARCHAR (40)) AS BEGIN EXIT; END^ CREATE PROCEDURE UPD_POKUP (
NO_POKUPKI SMALLINT, NO_KORZ SMALLINT, NO_KLI SMALLINT, NO_KONS SMALLINT,
DATAPRIDAJI VARCHAR (40), CENAGLAV DECIMAL (10,2)) AS BEGIN EXIT; END^ CREATE
PROCEDURE UPD_PROIZ ( NO_PROIZ SMALLINT, NAIMPROIZV VARCHAR (40)) AS BEGIN
EXIT; END^ CREATE PROCEDURE UPD_TOV ( NO_TOV SMALLINT, NO_USTR SMALLINT,
NO_PROIZ SMALLINT, MODEL VARCHAR (40), SERIALNO VARCHAR (40), PARTNO VARCHAR
(40), CENA DECIMAL (10,2)) AS BEGIN EXIT; END^ CREATE PROCEDURE UPD_TOV1 (
NO_TOV SMALLINT, SERIALNO VARCHAR (40), PARTNO VARCHAR (40), CENA DECIMAL
(10,2), MODEL VARCHAR (40), NO_USTR SMALLINT, NO_PROIZ SMALLINT) AS BEGIN
EXIT; END^ CREATE PROCEDURE UPD_USTR ( NO_USTR SMALLINT, NAIMENOVANIE VARCHAR
(40)) AS BEGIN EXIT; END^ CREATE PROCEDURE XP_VIEW_OBSLUGF ( FAMILIYA VARCHAR
(40)) RETURNS ( X_NO_POKUPKI SMALLINT, X_FAMILIYA VARCHAR (40), X_IMYA
VARCHAR (40), X_FAMILIYAKONS VARCHAR (40), X_IMYAKONS VARCHAR (40),
X_DATAPRIDAJI TIMESTAMP) AS BEGIN SUSPEND; END^ CREATE PROCEDURE
XP_VIEW_OBSLUGFK ( FAMILIYAKONS VARCHAR (40)) RETURNS ( X_NO_POKUPKI
SMALLINT, X_FAMILIYA VARCHAR (40), X_IMYA VARCHAR (40), X_FAMILIYAKONS
VARCHAR (40), X_IMYAKONS VARCHAR (40), X_DATAPRIDAJI TIMESTAMP) AS BEGIN
SUSPEND; END^ CREATE PROCEDURE XP_VIEW_PROIZ ( NAIMPROIZV VARCHAR (40))
RETURNS ( X_NO_PROIZ SMALLINT, X_NAIMPROIZV VARCHAR (40)) AS BEGIN SUSPEND;
END^ CREATE PROCEDURE XP_VIEW_PROIZNO ( NO_PROIZ SMALLINT) RETURNS (
X_NO_PROIZ SMALLINT, X_NAIMPROIZV VARCHAR (40)) AS BEGIN SUSPEND; END^ CREATE
PROCEDURE XP_VIEW_TOVAR1 ( NAIMENOVANIE VARCHAR (20)) RETURNS ( X_NO_TOV
SMALLINT, X_NAIMENOVANIE VARCHAR (40), X_NAIMPROIZV VARCHAR (40), X_MODEL
VARCHAR (40)) AS BEGIN SUSPEND; END^ CREATE PROCEDURE XP_VIEW_TOVARABC (
NO_TOV SMALLINT) RETURNS ( X_NO_TOV SMALLINT, X_NO_USTR SMALLINT, X_NO_PROIZ
SMALLINT, X_MODEL VARCHAR (40), X_SERIALNO VARCHAR (40), X_PARTNO VARCHAR
(40), X_CENA DECIMAL (10,2)) AS BEGIN SUSPEND; END^ CREATE PROCEDURE
XP_VIEW_TOVARV ( NAIMENOVANIE VARCHAR (20)) RETURNS ( X_NO_TOV SMALLINT,
X_NAIMENOVANIE VARCHAR (40), X_NAIMPROIZV VARCHAR (40), X_MODEL VARCHAR (40))
AS BEGIN SUSPEND; END^ CREATE PROCEDURE XP_VIEW_USTR ( NAIMENOVANIE VARCHAR
(20)) RETURNS ( X_NO_USTR SMALLINT, X_NAIMENOVANIE VARCHAR (40)) AS BEGIN
SUSPEND; END^ SET TERM; ^ /******************************************************************************/
/*** Tables ***/
/******************************************************************************/
CREATE TABLE KLIENT ( NO_KLI D_NO, FAMILIYA D_VCH NOT NULL, IMYA D_VCH NOT
NULL, OTCHESTVO D_VCH NOT NULL, TELEFON D_VCH NOT NULL, E_MAIL D_VCH NOT
NULL, GOROD D_VCH NOT NULL, ULICA D_VCH NOT NULL, DOM D_VCH NOT NULL); CREATE
TABLE KONSULTANT ( NO_KONS D_NO NOT NULL, FAMILIYAKONS D_VCH NOT NULL,
DATAPRIEMA D_DATA NOT NULL, TELEFONKONS D_VCH NOT NULL, IMYAKONS D_VCH NOT
NULL, OTCHESTVOKONS D_VCH NOT NULL, GORODKONS D_VCH NOT NULL, ULICAKONS D_VCH
NOT NULL, DOMKONS D_VCH NOT NULL); CREATE TABLE KORZINA ( NO_KORZ D_NO NOT
NULL, KOLICHESTVO D_VCH NOT NULL, NO_TOV D_NO NOT NULL); CREATE TABLE POKUPKA
( NO_POKUPKI D_NO NOT NULL, DATAPRIDAJI D_DATA NOT NULL, NO_KONS D_NO NOT
NULL, CENAGLAV D_CEN NOT NULL, NO_KLI D_NO NOT NULL, NO_KORZ D_NO NOT NULL);
CREATE TABLE PROIZVODITEL ( NO_PROIZ D_NO NOT NULL, NAIMPROIZV D_VCH NOT
NULL); CREATE TABLE TOVAR ( NO_TOV D_NO NOT NULL, SERIALNO D_VCH NOT NULL,
PARTNO D_VCH NOT NULL, CENA D_CEN NOT NULL, MODEL D_VCH NOT NULL, NO_USTR
D_NO NOT NULL, NO_PROIZ D_NO NOT NULL); CREATE TABLE USTROIYSTVO ( NO_USTR
D_NO NOT NULL, NAIMENOVANIE D_VCH NOT NULL); /******************************************************************************/
/*** Views ***/
/******************************************************************************/
/* View: VIEW_KLIENT */ CREATE VIEW VIEW_KLIENT ( FAMILIYA, IMYA, OTCHESTVO,
DATAPRIDAJI) AS select klient. familiya, klient. imya, klient. otchestvo,
pokupka. datapridaji from klient, pokupka where klient. no_kli=pokupka.
no_kli and klient. familiya like 'С%'; /* View: VIEW_KLIENTX */ CREATE VIEW
VIEW_KLIENTX ( FAMILIYA, IMYA, OTCHESTVO) AS select klient. familiya, klient.
imya, klient. otchestvo from klient where klient. familiya like 'С%'; /*
View: VIEW_OBSLUG */ CREATE VIEW VIEW_OBSLUG ( NO_POKUPKI, FAMILIYA, IMYA,
FAMILIYAKONS, IMYAKONS, DATAPRIDAJI) AS SELECT pokupka. no_pokupki, klient.
familiya, klient. imya, konsultant. familiyakons, konsultant. imyakons,
pokupka. datapridaji FROM klient, konsultant, pokupka WHERE klient.
no_kli=pokupka. no_kli and konsultant. no_kons=pokupka. no_kons; /* View:
VIEW_PROIZ */ CREATE VIEW VIEW_PROIZ ( NO_PROIZ, NAIMPROIZV) AS select
proizvoditel. no_proiz, proizvoditel. naimproizv from proizvoditel order BY
proizvoditel. naimproizv; /* View: VIEW_TOVAR */ CREATE VIEW VIEW_TOVAR (
NO_TOV, SERIALNO, PARTNO, CENA, MODEL, NO_USTR, NO_PROIZ) AS select * from
tovar where tovar. cena > 800; /* View: VIEW_TOVARSUM */ CREATE VIEW
VIEW_TOVARSUM ( CENA) AS select SUM (tovar. cena) from Tovar; /* View:
VIEW_TOVARV */ CREATE VIEW VIEW_TOVARV ( NO_TOV, NAIMENOVANIE, NAIMPROIZV,
MODEL) AS select tovar. no_tov, ustroiystvo. naimenovanie, proizvoditel.
naimproizv, tovar. model from tovar, ustroiystvo, proizvoditel where
ustroiystvo. no_ustr=tovar. no_ustr and proizvoditel. no_proiz=tovar.
no_proiz; /* View: VIEW_TOVARX */ CREATE VIEW VIEW_TOVARX ( NO_TOV, CENA,
MODEL, NO_USTR, NO_PROIZ, NAIMENOVANIE) AS select tovar. no_tov, tovar. cena,
tovar. model, tovar. no_ustr, tovar. no_proiz, ustroiystvo. naimenovanie from
tovar, ustroiystvo where ustroiystvo. no_ustr=tovar. no_ustr and tovar. cena
> 800;; INSERT INTO KLIENT (NO_KLI, FAMILIYA, IMYA, OTCHESTVO, TELEFON,
E_MAIL, GOROD, ULICA, DOM) VALUES (1, 'Ющенко', 'Елена', 'Васильевна', '8 900
000 00 00', 'yushch@mail.ru', 'Белгород', '1й Ореховый тупик', '60'); INSERT
INTO KLIENT (NO_KLI, FAMILIYA, IMYA, OTCHESTVO, TELEFON, E_MAIL, GOROD,
ULICA, DOM) VALUES (2, 'Горшков', 'Григорий', 'Георгиевич', '8 900 800 50
60', 'grisha11@maill.ru', 'Белгород', 'Конева', '17'); INSERT INTO KLIENT
(NO_KLI, FAMILIYA, IMYA, OTCHESTVO, TELEFON, E_MAIL, GOROD, ULICA, DOM)
VALUES (3, 'Седугина', 'Гадя', 'Анатольевна', '8 951 430 53 84',
'bdgn@mail.ru', 'Белгород', 'Шаландина', '20'); INSERT INTO KLIENT (NO_KLI,
FAMILIYA, IMYA, OTCHESTVO, TELEFON, E_MAIL, GOROD, ULICA, DOM) VALUES (4,
'Шалестьев', 'Егор', 'Арсеньевич', '8 258 546 46 57', 'sholoh@mail.ru',
'Строитель', 'Маршалкова', '3'); INSERT INTO KLIENT (NO_KLI, FAMILIYA, IMYA,
OTCHESTVO, TELEFON, E_MAIL, GOROD, ULICA, DOM) VALUES (5, 'Кобзев', 'Иван',
'васильевич', '8 879 798 76 91', '1va17n@mail.ru', 'Белгород', 'Костюкова',
'25'); INSERT INTO KLIENT (NO_KLI, FAMILIYA, IMYA, OTCHESTVO, TELEFON,
E_MAIL, GOROD, ULICA, DOM) VALUES (6, 'Семагин', 'Антон', 'Маркович', '8 865
876 79 74', 'evria@mail.ru', 'Валуйки', 'Ленина', '9'); INSERT INTO KLIENT
(NO_KLI, FAMILIYA, IMYA, OTCHESTVO, TELEFON, E_MAIL, GOROD, ULICA, DOM)
VALUES (7, 'Петров', 'Петор', 'Петрович', '8 913 468 46 58', 'byr76d@ya.ru',
'Шебекино', 'Садовая', '5'); INSERT INTO KLIENT (NO_KLI, FAMILIYA, IMYA,
OTCHESTVO, TELEFON, E_MAIL, GOROD, ULICA, DOM) VALUES (8, 'Жданов', 'Сергей',
'Васильевич', '8 965 436 59 56', 'gdanoff@ya.ru', 'Строитель', 'Победы',
'85'); INSERT INTO KLIENT (NO_KLI, FAMILIYA, IMYA, OTCHESTVO, TELEFON,
E_MAIL, GOROD, ULICA, DOM) VALUES (9, 'Иванов', 'Игорь', 'Иванович', '8 903
265 48 73', 'iva54@bigmir.com', 'Шебекино', 'Строителей', '12'); INSERT INTO
KLIENT (NO_KLI, FAMILIYA, IMYA, OTCHESTVO, TELEFON, E_MAIL, GOROD, ULICA,
DOM) VALUES (10, 'Мельник', 'Ирина', 'Аркадьевна', '8 912 469 43 26',
'meln1k@ya.ru', 'Валуйки', 'Ленина', '13'); COMMIT WORK; INSERT INTO
KONSULTANT (NO_KONS, FAMILIYAKONS, DATAPRIEMA, TELEFONKONS, IMYAKONS,
OTCHESTVOKONS, GORODKONS, ULICAKONS, DOMKONS) VALUES (1, 'Сумкин',
'2008-02-15 00: 00: 00', '8 910 120 30 25', 'Федор', 'Ионович', 'Белгород',
'Попова', '5'); INSERT INTO KONSULTANT (NO_KONS, FAMILIYAKONS, DATAPRIEMA,
TELEFONKONS, IMYAKONS, OTCHESTVOKONS, GORODKONS, ULICAKONS, DOMKONS) VALUES
(2, 'Перышкин', '2009-09-03 00: 00: 00', '8 915 176 23 46', 'Святослав',
'Леонидович', 'Белгород', 'Красноармейская', '133'); INSERT INTO KONSULTANT
(NO_KONS, FAMILIYAKONS, DATAPRIEMA, TELEFONKONS, IMYAKONS, OTCHESTVOKONS,
GORODKONS, ULICAKONS, DOMKONS) VALUES (3, 'Боков', '2008-11-20 00: 00: 00',
'8 910 246 46 46', 'Олег', 'Николаевич', 'Белгород', 'Садовая', '28'); INSERT
INTO KONSULTANT (NO_KONS, FAMILIYAKONS, DATAPRIEMA, TELEFONKONS, IMYAKONS,
OTCHESTVOKONS, GORODKONS, ULICAKONS, DOMKONS) VALUES (4, 'Котов', '2010-08-11
00: 00: 00', '8 920 684 65 56', 'Валерий', 'Олегович', 'Белгород',
'Пугачева', '2'); INSERT INTO KONSULTANT (NO_KONS, FAMILIYAKONS, DATAPRIEMA,
TELEFONKONS, IMYAKONS, OTCHESTVOKONS, GORODKONS, ULICAKONS, DOMKONS) VALUES
(5, 'Здравин', '2011-05-16 00: 00: 00', '8 913 176 54 69', 'Дмитрий',
'Александрович', 'Белгород', 'Королева', '10'); COMMIT WORK; INSERT INTO
PROIZVODITEL (NO_PROIZ, NAIMPROIZV) VALUES (1, 'Benq'); INSERT INTO
PROIZVODITEL (NO_PROIZ, NAIMPROIZV) VALUES (2, 'Acer'); INSERT INTO
PROIZVODITEL (NO_PROIZ, NAIMPROIZV) VALUES (3, 'Asus'); INSERT INTO
PROIZVODITEL (NO_PROIZ, NAIMPROIZV) VALUES (4, 'TopDevice'); INSERT INTO
PROIZVODITEL (NO_PROIZ, NAIMPROIZV) VALUES (5, 'SamSang'); INSERT INTO
PROIZVODITEL (NO_PROIZ, NAIMPROIZV) VALUES (6, 'HP'); COMMIT WORK; INSERT
INTO USTROIYSTVO (NO_USTR, NAIMENOVANIE) VALUES (1, 'Монитор'); INSERT INTO
USTROIYSTVO (NO_USTR, NAIMENOVANIE) VALUES (2, 'Сиситемный блок'); INSERT INTO
USTROIYSTVO (NO_USTR, NAIMENOVANIE) VALUES (3, 'Клавиатура'); INSERT INTO
USTROIYSTVO (NO_USTR, NAIMENOVANIE) VALUES (4, 'Мышь'); INSERT INTO
USTROIYSTVO (NO_USTR, NAIMENOVANIE) VALUES (5, 'Kолонки'); INSERT INTO
USTROIYSTVO (NO_USTR, NAIMENOVANIE) VALUES (6, 'Монитор'); INSERT INTO
USTROIYSTVO (NO_USTR, NAIMENOVANIE) VALUES (7, 'test'); INSERT INTO
USTROIYSTVO (NO_USTR, NAIMENOVANIE) VALUES (10, 'Факс'); INSERT INTO
USTROIYSTVO (NO_USTR, NAIMENOVANIE) VALUES (8, 'Принтер лазерный'); INSERT
INTO USTROIYSTVO (NO_USTR, NAIMENOVANIE) VALUES (9, 'Принтер струйный');
COMMIT WORK; INSERT INTO TOVAR (NO_TOV, SERIALNO, PARTNO, CENA, MODEL,
NO_USTR, NO_PROIZ) VALUES (1, 'BX53FBU667', '6327HF1', 5020, 'BN103', 1, 1);
INSERT INTO TOVAR (NO_TOV, SERIALNO, PARTNO, CENA, MODEL, NO_USTR, NO_PROIZ)
VALUES (2, 'FCH63763NJE639', 'BED7FHEU7', 10000, 'Aspire 1282', 2, 2); INSERT
INTO TOVAR (NO_TOV, SERIALNO, PARTNO, CENA, MODEL, NO_USTR, NO_PROIZ) VALUES
(3, '34563G', '1527', 700, '2000', 5, 4); INSERT INTO TOVAR (NO_TOV, SERIALNO,
PARTNO, CENA, MODEL, NO_USTR, NO_PROIZ) VALUES (4, 'HG22GG37', 'DFJHDF7',
800, '559SE', 3, 5); INSERT INTO TOVAR (NO_TOV, SERIALNO, PARTNO, CENA,
MODEL, NO_USTR, NO_PROIZ) VALUES (5, 'GD6', '1252V', 250, 'H12', 4, 2);
INSERT INTO TOVAR (NO_TOV, SERIALNO, PARTNO, CENA, MODEL, NO_USTR, NO_PROIZ)
VALUES (6, 'BX867RGG', '8763G76F', 2400, 'LJ 1100', 8, 6); INSERT INTO TOVAR
(NO_TOV, SERIALNO, PARTNO, CENA, MODEL, NO_USTR, NO_PROIZ) VALUES (7, '5FG4',
'54RGF5', 5468, 'FH2', 2, 1); COMMIT WORK; INSERT INTO KORZINA (NO_KORZ,
KOLICHESTVO, NO_TOV) VALUES (1, '1', 1); INSERT INTO KORZINA (NO_KORZ,
KOLICHESTVO, NO_TOV) VALUES (2, '3', 4); INSERT INTO KORZINA (NO_KORZ,
KOLICHESTVO, NO_TOV) VALUES (3, '2', 5); INSERT INTO KORZINA (NO_KORZ,
KOLICHESTVO, NO_TOV) VALUES (4, '1', 2); INSERT INTO KORZINA (NO_KORZ,
KOLICHESTVO, NO_TOV) VALUES (5, '3', 3); INSERT INTO KORZINA (NO_KORZ,
KOLICHESTVO, NO_TOV) VALUES (6, '2', 3); COMMIT WORK; INSERT INTO POKUPKA
(NO_POKUPKI, DATAPRIDAJI, NO_KONS, CENAGLAV, NO_KLI, NO_KORZ) VALUES (2,
'2009-12-13 00: 00: 00', 4, 1900, 2, 2); INSERT INTO POKUPKA (NO_POKUPKI,
DATAPRIDAJI, NO_KONS, CENAGLAV, NO_KLI, NO_KORZ) VALUES (3, '2009-03-20 00:
00: 00', 3, 2400, 2, 2); INSERT INTO POKUPKA (NO_POKUPKI, DATAPRIDAJI,
NO_KONS, CENAGLAV, NO_KLI, NO_KORZ) VALUES (4, '2009-02-28 00: 00: 00', 2,
500, 5, 3); INSERT INTO POKUPKA (NO_POKUPKI, DATAPRIDAJI, NO_KONS, CENAGLAV,
NO_KLI, NO_KORZ) VALUES (5, '2009-03-23 00: 00: 00', 1, 10000, 6, 4); INSERT
INTO POKUPKA (NO_POKUPKI, DATAPRIDAJI, NO_KONS, CENAGLAV, NO_KLI, NO_KORZ)
VALUES (6, '2009-04-15 00: 00: 00', 4, 2100, 3, 5); INSERT INTO POKUPKA
(NO_POKUPKI, DATAPRIDAJI, NO_KONS, CENAGLAV, NO_KLI, NO_KORZ) VALUES (1,
'2013-05-15 11: 39: 56', 2, 15, 2, 2); COMMIT WORK;
/******************************************************************************/
/*** Primary Keys ***/
/******************************************************************************/
ALTER TABLE KLIENT ADD PRIMARY KEY (NO_KLI); ALTER TABLE KONSULTANT ADD
PRIMARY KEY (NO_KONS); ALTER TABLE KORZINA ADD PRIMARY KEY (NO_KORZ); ALTER
TABLE POKUPKA ADD PRIMARY KEY (NO_POKUPKI); ALTER TABLE PROIZVODITEL ADD
PRIMARY KEY (NO_PROIZ); ALTER TABLE TOVAR ADD PRIMARY KEY (NO_TOV); ALTER
TABLE USTROIYSTVO ADD PRIMARY KEY (NO_USTR); /******************************************************************************/
/*** Foreign Keys ***/
/******************************************************************************/
ALTER TABLE KORZINA ADD FOREIGN KEY (NO_TOV) REFERENCES TOVAR (NO_TOV); ALTER
TABLE KORZINA ADD FOREIGN KEY (NO_TOV) REFERENCES TOVAR (NO_TOV); ALTER TABLE
POKUPKA ADD FOREIGN KEY (NO_KONS) REFERENCES KONSULTANT (NO_KONS); ALTER
TABLE POKUPKA ADD FOREIGN KEY (NO_KONS) REFERENCES KONSULTANT (NO_KONS);
ALTER TABLE POKUPKA ADD FOREIGN KEY (NO_KLI) REFERENCES KLIENT (NO_KLI);
ALTER TABLE POKUPKA ADD FOREIGN KEY (NO_KORZ) REFERENCES KORZINA (NO_KORZ);
ALTER TABLE TOVAR ADD FOREIGN KEY (NO_USTR) REFERENCES USTROIYSTVO (NO_USTR);
ALTER TABLE TOVAR ADD FOREIGN KEY (NO_PROIZ) REFERENCES PROIZVODITEL (NO_PROIZ);
/******************************************************************************/
/*** Indices ***/
/******************************************************************************/
CREATE INDEX KLIENT_IDXFAM ON KLIENT (FAMILIYA); CREATE INDEX KONSULTANT_IDXDATK
ON KONSULTANT (DATAPRIEMA); CREATE INDEX KONSULTANT_IDXFAMK ON KONSULTANT
(FAMILIYAKONS); CREATE INDEX PROIZVODITEL_IDXNAIM ON PROIZVODITEL
(NAIMPROIZV); CREATE INDEX TOVAR_IDXMOD ON TOVAR (MODEL);
/******************************************************************************/
/*** Triggers ***/
/******************************************************************************/
SET TERM ^;
/******************************************************************************/
/*** Triggers for tables ***/ /******************************************************************************/
/* Trigger: KLIENT_INS */ CREATE TRIGGER KLIENT_INS FOR KLIENT ACTIVE BEFORE
INSERT POSITION 0 AS begin if (NEW. no_kli IS NULL) then NEW. no_kli=GEN_ID
(gen_klient_no,1); End ^ /* Trigger: KONSULTANT_INS */ CREATE TRIGGER
KONSULTANT_INS FOR KONSULTANT ACTIVE BEFORE INSERT POSITION 0 AS begin if
(NEW. no_kons IS NULL) then NEW. no_kons=GEN_ID (gen_kons_no,1); End ^ /*
Trigger: KORZINA_INS */ CREATE TRIGGER KORZINA_INS FOR KORZINA ACTIVE BEFORE
INSERT POSITION 0 AS begin if (NEW. no_korz IS NULL) then NEW. no_korz=GEN_ID
(gen_korz_no,1); End ^ /* Trigger: POKUPKA_INS */ CREATE TRIGGER POKUPKA_INS
FOR POKUPKA ACTIVE BEFORE INSERT POSITION 0 AS begin if (NEW. no_pokupki IS
NULL) then NEW. no_pokupki=GEN_ID (gen_pokup_no,1); End ^ /* Trigger:
PROIZVODITEL_INS */ CREATE TRIGGER PROIZVODITEL_INS FOR PROIZVODITEL ACTIVE
BEFORE INSERT POSITION 0 AS begin if (NEW. no_proiz IS NULL) then NEW.
no_proiz=GEN_ID (gen_proiz_no,1); End ^ /* Trigger: TOVAR_INS */ CREATE
TRIGGER TOVAR_INS FOR TOVAR ACTIVE BEFORE INSERT POSITION 0 AS begin if (NEW.
no_tov IS NULL) then NEW. no_tov=GEN_ID (gen_tovar_no,1); End ^ /* Trigger:
USTROIYSTVO_INS */ CREATE TRIGGER USTROIYSTVO_INS FOR USTROIYSTVO ACTIVE BEFORE
INSERT POSITION 0 AS begin if (NEW. no_ustr IS NULL) then NEW. no_ustr=GEN_ID
(gen_ustr_no,1); End ^ SET TERM; ^
/******************************************************************************/
/*** Stored Procedures ***/ /******************************************************************************/
SET TERM ^; ALTER PROCEDURE DEL_KLI ( NO_KLI SMALLINT) AS begin delete from
klient where (NO_KLI=: NO_KLI); end^ ALTER PROCEDURE DEL_KONS ( NO_KONS
SMALLINT) AS begin delete from konsultant where (NO_KONS=: NO_KONS); end^
ALTER PROCEDURE DEL_KORZ ( NO_KORZ SMALLINT) AS begin delete from korzina
where (NO_KORZ=: NO_KORZ); end^ ALTER PROCEDURE DEL_POKUP ( NO_POKUPKI
SMALLINT) AS begin delete from pokupka where (NO_POKUPKI=: NO_POKUPKI); end^
ALTER PROCEDURE DEL_PROIZ ( NO_PROIZ SMALLINT) AS begin delete from
proizvoditel where (NO_PROIZ=: NO_PROIZ); end^ ALTER PROCEDURE DEL_TOV (
NO_TOV SMALLINT) AS begin delete from tovar where (NO_TOV=: NO_TOV); end^
ALTER PROCEDURE DEL_USTR ( NO_USTR SMALLINT) AS begin delete from ustroiystvo
where (NO_USTR=: NO_USTR); end^ ALTER PROCEDURE INSERT_KLI ( NO_KLI SMALLINT,
FAMILIYA VARCHAR (40), IMYA VARCHAR (40), OTCHESTVO VARCHAR (40), TELEFON
VARCHAR (40), E_MAIL VARCHAR (40), GOROD VARCHAR (40), ULICA VARCHAR (40),
DOM VARCHAR (40)) AS begin if (: familiya = '' or: familiya is null) then
exception exc_notext; else if (: imya = '' or: imya is null) then exception
exc_notext; else if (: otchestvo = '' or: otchestvo is null) then exception
exc_notext; else if (: telefon = '' or: telefon is null) then exception
exc_nodata; else if (: e_mail = '' or: e_mail is null) then exception
exc_notext; else if (: gorod = '' or: gorod is null) then exception
exc_notext; else if (: ulica = '' or: ulica is null) then exception
exc_notext; else if (: dom = '' or: dom is null) then exception exc_nodata;
else begin insert into klient (klient. no_kli, klient. familiya, klient.
imya, klient. otchestvo, klient. telefon, klient. e_mail, klient. gorod,
klient. ulica, klient. dom) values ( : NO_KLI, : FAMILIYA, : IMYA, :
OTCHESTVO, : TELEFON, : E_MAIL, : GOROD, : ULICA, : DOM); end suspend; END^
ALTER PROCEDURE INSERT_KONS ( NO_KONS SMALLINT, FAMILIYAKONS VARCHAR (40),
IMYAKONS VARCHAR (40), OTCHESTVOKONS VARCHAR (40), TELEFONKONS VARCHAR (40),
GORODKONS VARCHAR (40), ULICAKONS VARCHAR (40), DOMKONS VARCHAR (40),
DATAPRIEMA VARCHAR (40)) AS begin if (: familiyakons = '' or: familiyakons is
null) then exception exc_notext; else if (: imyakons = '' or: imyakons is
null) then exception exc_notext; else if (: otchestvokons = '' or:
otchestvokons is null) then exception exc_notext; else if (: telefonkons = ''
or: telefonkons is null) then exception exc_nodata; else if (: gorodkons = ''
or: gorodkons is null) then exception exc_notext; else if (: ulicakons = ''
or: ulicakons is null) then exception exc_notext; else if (: domkons = '' or:
domkons is null) then exception exc_nodata; else if (: datapriema = '' or:
datapriema is null) then exception exc_nodata; else begin insert into
konsultant (konsultant. no_kons, konsultant. familiyakons, konsultant.
imyakons, konsultant. otchestvokons, konsultant. telefonkons, konsultant.
gorodkons, konsultant. ulicakons, konsultant. domkons, konsultant.
datapriema) values ( : NO_KONS, : FAMILIYAKONS, : IMYAKONS, : OTCHESTVOKONS,
: TELEFONKONS, : GORODKONS, : ULICAKONS, : DOMKONS, : DATAPRIEMA); end
suspend; END^ ALTER PROCEDURE INSERT_KORZ ( NO_KORZ SMALLINT, NO_TOV
SMALLINT, KOLICHESTVO VARCHAR (40)) AS begin if (: kolichestvo = '' or:
kolichestvo is null) then exception exc_nodata; else begin insert into
korzina (korzina. no_korz, korzina. no_tov, korzina. kolichestvo) values ( :
NO_KORZ, : NO_TOV, : KOLICHESTVO); end suspend; END^ ALTER PROCEDURE
INSERT_POKUP ( NO_POKUPKI SMALLINT, NO_KORZ SMALLINT, NO_KLI SMALLINT,
NO_KONS SMALLINT, DATAPRIDAJI VARCHAR (40), CENAGLAV DECIMAL (10,2)) AS begin
if (: datapridaji = '' or: datapridaji is null) then exception exc_nodata;
else begin insert into pokupka ( pokupka. no_pokupki, pokupka. no_korz,
pokupka. no_kli, pokupka. no_kons, pokupka. datapridaji, pokupka. cenaglav)
values ( : NO_POKUPKI, : NO_KORZ, : NO_KLI, : NO_KONS, : DATAPRIDAJI, :
CENAGLAV); end suspend; end^ ALTER PROCEDURE INSERT_PROIZ ( NO_PROIZ
SMALLINT, NAIMPROIZV VARCHAR (40)) AS begin if (: naimproizv = '' or:
naimproizv is null) then exception exc_notext; else begin insert into
proizvoditel (proizvoditel. no_proiz, proizvoditel. naimproizv) values ( :
NO_PROIZ, : NAIMPROIZV); end suspend; END^ ALTER PROCEDURE INSERT_TOV (
NO_TOV SMALLINT, NO_USTR SMALLINT, NO_PROIZ SMALLINT, MODEL VARCHAR (40),
SERIALNO VARCHAR (40), PARTNO VARCHAR (40), CENA DECIMAL (10,2)) AS begin if
(: model = '' or: model is null) then exception exc_notext; else if (:
serialno = '' or: model is null) then exception exc_notext; else if (: partno
= '' or: model is null) then exception exc_notext; else begin insert into
tovar (tovar. no_tov, tovar. no_ustr, tovar. no_proiz, tovar. model, tovar.
serialno, tovar. partno, tovar. cena) values ( : NO_TOV, : NO_USTR, :
NO_PROIZ, : MODEL, : SERIALNO, : PARTNO, : CENA); end suspend; END^ ALTER
PROCEDURE INSERT_TOV1 ( NO_TOV SMALLINT, SERIALNO VARCHAR (40), PARTNO
VARCHAR (40), CENA DECIMAL (10,2), MODEL VARCHAR (40), NO_USTR SMALLINT,
NO_PROIZ SMALLINT) AS begin if (: model = '0' or: model is null) then
exception exc_notext; else if (: serialno = '0' or: model is null) then
exception exc_notext; else if (: partno = '0' or: model is null) then
exception exc_notext; else begin insert into tovar (tovar. no_tov, tovar.
serialno, tovar. partno, tovar. cena, tovar. model, tovar. no_ustr, tovar.
no_proiz) values ( : NO_TOV, : SERIALNO, : PARTNO, : CENA, : MODEL, :
NO_USTR, : NO_PROIZ); end suspend; END^ ALTER PROCEDURE INSERT_USTR ( NO_USTR
SMALLINT, NAIMENOVANIE VARCHAR (40)) AS begin if (: naimenovanie = '') then
exception exc_notext; else begin insert into ustroiystvo (ustroiystvo.
no_ustr, ustroiystvo. naimenovanie) values ( : NO_USTR, : NAIMENOVANIE); end
suspend; END^ ALTER PROCEDURE UPD_KLI ( NO_KLI SMALLINT, FAMILIYA VARCHAR
(40), IMYA VARCHAR (40), OTCHESTVO VARCHAR (40), TELEFON VARCHAR (40), E_MAIL
VARCHAR (40), GOROD VARCHAR (40), ULICA VARCHAR (40), DOM VARCHAR (40)) AS
begin update klient set NO_KLI=: NO_KLI, FAMILIYA=: FAMILIYA, IMYA=: IMYA,
OTCHESTVO=: OTCHESTVO, TELEFON=: TELEFON, E_MAIL=: E_MAIL, GOROD=: GOROD, ULICA=:
ULICA, DOM=: DOM where (NO_KLI=: NO_KLI); end^ ALTER PROCEDURE UPD_KONS (
NO_KONS SMALLINT, FAMILIYAKONS VARCHAR (40), IMYAKONS VARCHAR (40),
OTCHESTVOKONS VARCHAR (40), TELEFONKONS VARCHAR (40), GORODKONS VARCHAR (40),
ULICAKONS VARCHAR (40), DOMKONS VARCHAR (40), DATAPRIEMA VARCHAR (40)) AS
begin update konsultant set NO_KONS=: NO_KONS, FAMILIYAKONS=: FAMILIYAKONS,
IMYAKONS=: IMYAKONS, OTCHESTVOKONS=: OTCHESTVOKONS, TELEFONKONS=:
TELEFONKONS, GORODKONS=: GORODKONS, ULICAKONS=: ULICAKONS, DOMKONS=: DOMKONS,
DATAPRIEMA=: DATAPRIEMA where (NO_KONS=: NO_KONS); end^ ALTER PROCEDURE
UPD_KORZ ( NO_KORZ SMALLINT, NO_TOV SMALLINT, KOLICHESTVO VARCHAR (40)) AS
begin update korzina set NO_KORZ=: NO_KORZ, NO_TOV=: NO_TOV, KOLICHESTVO=:
KOLICHESTVO where (NO_KORZ=: NO_KORZ); end^ ALTER PROCEDURE UPD_POKUP (
NO_POKUPKI SMALLINT, NO_KORZ SMALLINT, NO_KLI SMALLINT, NO_KONS SMALLINT,
DATAPRIDAJI VARCHAR (40), CENAGLAV DECIMAL (10,2)) AS begin update pokupka
set NO_KORZ=: NO_KORZ, NO_KLI=: NO_KLI, NO_KONS=: NO_KONS, DATAPRIDAJI=:
DATAPRIDAJI, CENAGLAV=: CENAGLAV where (NO_POKUPKI=: NO_POKUPKI); end^ ALTER
PROCEDURE UPD_PROIZ ( NO_PROIZ SMALLINT, NAIMPROIZV VARCHAR (40)) AS begin
update proizvoditel set NO_PROIZ=: NO_PROIZ, NAIMPROIZV=: NAIMPROIZV where
(NO_PROIZ=: NO_PROIZ); end^ ALTER PROCEDURE UPD_TOV ( NO_TOV SMALLINT,
NO_USTR SMALLINT, NO_PROIZ SMALLINT, MODEL VARCHAR (40), SERIALNO VARCHAR
(40), PARTNO VARCHAR (40), CENA DECIMAL (10,2)) AS begin update tovar set
NO_USTR=: NO_USTR, NO_PROIZ=: NO_PROIZ, MODEL =: MODEL, SERIALNO=: SERIALNO,
PARTNO=: PARTNO, CENA=: CENA where (NO_TOV=: NO_TOV); end^ ALTER PROCEDURE
UPD_TOV1 ( NO_TOV SMALLINT, SERIALNO VARCHAR (40), PARTNO VARCHAR (40), CENA
DECIMAL (10,2), MODEL VARCHAR (40), NO_USTR SMALLINT, NO_PROIZ SMALLINT) AS begin
if (: model = '0' or: model is null) then exception exc_notext; else if (:
serialno = '0' or: model is null) then exception exc_notext; else if (:
partno = '0' or: model is null) then exception exc_notext; else begin update
tovar set NO_TOV=: NO_TOV, SERIALNO=: SERIALNO, PARTNO=: PARTNO, CENA=: CENA,
MODEL=: MODEL, NO_USTR =: NO_USTR, NO_PROIZ=: NO_PROIZ where (NO_TOV=:
NO_TOV); end suspend; END^ ALTER PROCEDURE UPD_USTR ( NO_USTR SMALLINT,
NAIMENOVANIE VARCHAR (40)) AS begin update ustroiystvo set NO_USTR=: NO_USTR,
NAIMENOVANIE=: NAIMENOVANIE where (NO_USTR=: NO_USTR); end^ ALTER PROCEDURE
XP_VIEW_OBSLUGF ( FAMILIYA VARCHAR (40)) RETURNS ( X_NO_POKUPKI SMALLINT,
X_FAMILIYA VARCHAR (40), X_IMYA VARCHAR (40), X_FAMILIYAKONS VARCHAR (40),
X_IMYAKONS VARCHAR (40), X_DATAPRIDAJI TIMESTAMP) AS begin if (: FAMILIYA =
'') then exception exc_notext; else begin for select * FROM VIEW_OBSLUG where
VIEW_OBSLUG. familiya=: FAMILIYA into X_NO_POKUPKI, X_FAMILIYA, X_IMYA,
X_FAMILIYAKONS, X_IMYAKONS, X_DATAPRIDAJI do suspend; end end^ ALTER
PROCEDURE XP_VIEW_OBSLUGFK ( FAMILIYAKONS VARCHAR (40)) RETURNS (
X_NO_POKUPKI SMALLINT, X_FAMILIYA VARCHAR (40), X_IMYA VARCHAR (40),
X_FAMILIYAKONS VARCHAR (40), X_IMYAKONS VARCHAR (40), X_DATAPRIDAJI
TIMESTAMP) AS begin if (: FAMILIYAKONS = '') then exception exc_notext; else
begin for select * FROM VIEW_OBSLUG where VIEW_OBSLUG. familiyakons=:
FAMILIYAKONS into X_NO_POKUPKI, X_FAMILIYA, X_IMYA, X_FAMILIYAKONS,
X_IMYAKONS, X_DATAPRIDAJI do suspend; end end^ ALTER PROCEDURE XP_VIEW_PROIZ
( NAIMPROIZV VARCHAR (40)) RETURNS ( X_NO_PROIZ SMALLINT, X_NAIMPROIZV
VARCHAR (40)) AS begin if (: NAIMPROIZV = '') THEN exception exc_notext; else
begin for select * FROM view_proiz where view_proiz. naimproizv like:
naimproizv||'%' into X_NO_PROIZ, X_NAIMPROIZV do suspend; end end^ ALTER
PROCEDURE XP_VIEW_PROIZNO ( NO_PROIZ SMALLINT) RETURNS ( X_NO_PROIZ SMALLINT,
X_NAIMPROIZV VARCHAR (40)) AS begin if (: NO_PROIZ = '') THEN exception
exc_nodata; else begin for select * FROM view_proiz where view_proiz.
no_proiz like: no_proiz||'%' into X_NO_PROIZ, X_NAIMPROIZV do suspend; end
end^ ALTER PROCEDURE XP_VIEW_TOVAR1 ( NAIMENOVANIE VARCHAR (20)) RETURNS (
X_NO_TOV SMALLINT, X_NAIMENOVANIE VARCHAR (40), X_NAIMPROIZV VARCHAR (40),
X_MODEL VARCHAR (40)) AS begin if (: NAIMENOVANIE = '0') THEN exception
exc_notext; else begin for select * FROM VIEW_TOVARV where view_tovarv.
naimenovanie =: naimenovanie into X_NO_TOV, X_NAIMENOVANIE, X_NAIMPROIZV,
X_MODEL do suspend; end end^ ALTER PROCEDURE XP_VIEW_TOVARABC ( NO_TOV
SMALLINT) RETURNS ( X_NO_TOV SMALLINT, X_NO_USTR SMALLINT, X_NO_PROIZ
SMALLINT, X_MODEL VARCHAR (40), X_SERIALNO VARCHAR (40), X_PARTNO VARCHAR
(40), X_CENA DECIMAL (10,2)) AS begin if (: NO_TOV = '') THEN exception
exc_notext; else begin for select * FROM view_tovar where view_tovar. no_tov
=: no_tov into X_NO_TOV, X_NO_USTR, X_NO_PROIZ, X_MODEL, X_SERIALNO,
X_PARTNO, X_CENA do suspend; end end^ ALTER PROCEDURE XP_VIEW_TOVARV (
NAIMENOVANIE VARCHAR (20)) RETURNS ( X_NO_TOV SMALLINT, X_NAIMENOVANIE VARCHAR
(40), X_NAIMPROIZV VARCHAR (40), X_MODEL VARCHAR (40)) AS begin if (:
NAIMENOVANIE = '0') THEN exception exc_notext; else begin for select * FROM
VIEW_TOVARV where view_tovarv. naimenovanie like: naimenovanie||'%' into
X_NO_TOV, X_NAIMENOVANIE, X_NAIMPROIZV, X_MODEL do suspend; end end^ ALTER
PROCEDURE XP_VIEW_USTR ( NAIMENOVANIE VARCHAR (20)) RETURNS ( X_NO_USTR
SMALLINT, X_NAIMENOVANIE VARCHAR (40)) AS begin if (: NAIMENOVANIE = '0')
THEN exception exc_notext; else begin for select * FROM ustroiystvo where
ustroiystvo. naimenovanie like: naimenovanie||'%' into X_NO_USTR,
X_NAIMENOVANIE do suspend; end end^ SET TERM; ^
|