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

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