Программирование на стороне сервера

Создание домена:

CREATE DOMAIN D_DAT AS

DATE

NOT NULL;

Создание таблицы:

CREATE TABLE CHILDREN (

ID_CHIL D_ID,

SURNAME_C D_FIO,

NAME_C D_FIO,

LNAME_C D_FIO,

GOD_ROZD D_DAT,

COUNT_LET D_NUM,

ID_GROUP D_ID

);

ALTER TABLE CHILDREN ADD CONSTRAINT PK_CHILDREN PRIMARY KEY (ID_CHIL);

ALTER TABLE CHILDREN ADD CONSTRAINT FK_CHILDREN_1 FOREIGN KEY (ID_GROUP) REFERENCES GROUPS (ID_GROUP) ON DELETE CASCADE ON UPDATE CASCADE;

Создание представления:

CREATE VIEW V_SOTR_GROUP(

GRYPA,

SOTRUDNIK,

DOLZNOST)

AS

select gr.name_gr as grypa, (st.surname_s||' '||st.name_s||' '||st.lname_s) as sotrudnik, dl.name_dolz as dolznost

from groups gr, sotrudniki st, dolznosti dl, sotr_group sg

where gr.id_group=sg.id_group AND sg.id_sotr=st.id_sotr AND st.id_dolz=dl.id_dolz

order by grypa;

Создание выполняемой хранимой процедуры:

create or alter procedure ADD_GR (

NAME_GRYPA varchar(20),

NUM_ROOM smallint)

as

begin

if(:name_grypa='' or:name_grypa is null) then exception null_except;

else if(:num_room<=0 or:num_room is null) then exception number_except;

begin

INSERT INTO groups (groups.name_gr, groups.num_room)

VALUES (:name_grypa,:num_room);

end

end

Создание хранимой процедуры на выборку:

create or alter procedure CHILD_GRYPA (

FAMILIA varchar(20))

returns (

GRYPA varchar(20),

NUM_ROOM smallint)

as

begin

for select distinct groups.name_gr, groups.num_room

from groups, children

WHERE children.surname_c=:familia AND children.id_group=groups.id_group

into:grypa,:num_room

do

suspend;

end

Создание триггера:

CREATE OR ALTER TRIGGER GROUPS_BI0 FOR GROUPS

ACTIVE BEFORE INSERT POSITION 0

AS

begin

if (new.id_group is null) then

new.id_group=gen_id(g_gr, 1);

end

Создание генератора:

CREATE generator G_CH;

ALTER generator G_CH RESTART WITH 1;

Создание исключения:

CREATE EXCEPTION DUBL_EXCEPT 'Дублирующая запись';

Создание индекса:

CREATE INDEX CH_FAM_IDX1 ON CHILDREN (SURNAME_C);

ПРИЛОЖЕНИЕ 2