Разработка информационной системы "Компьютерные комплектующие" с использованием клиент-серверной технологии

SQL Код БД

/******************************************************************************/

/*** 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; ^