. , , ,

,,,

— ,

 

 

 

 

 

 

 

" "

: " "

 

:

- 52

..

:

""

..

 

2006


, . , , , , , . (, , , , , ) (, , , , ). , : , .. , , . .

: , , . : , , , .. , . ( 1, 2 . . .), .

:

, , , .

, , , , .

, , , , .

, , , , .

, , , , .

, , , .

, 5 ( ).

, , , , .

, , , , , ( ).

, , , , , .

, 10 ( ).

, , , ( ) .


1.       

2.       

3.       

4.       

5.       

6.        CASE Studio 2.21

7.       

8.       

9.       


1.        

a)        

b)        

c)        

d)        

e)        

f)         

2.        

, , .

, , , , .

, , , .

() , , . () , , , , .

SQL-, .

:

♦ , (LRU least recently used) (MRU most recently used), , .

♦ .

♦ , .

♦ , SQL Server.

♦ , , .

.

, . :

♦ .

♦ .

♦ , ( SQL Server).

♦ Microsoft SQL Server.

,

ADOQuery1->SQL->Add("CREATE PROCEDURE proc2;1 as SELECT nazvanie_armii, COUNT(Nomer_podrazdelenia) AS col FROM chast GROUP BY nazvanie_armii HAVING COUNT(nazvanie_armii)>=ALL(SELECT COUNT(nazvanie_armii) FROM Chast GROUP BY nazvanie_armii)");

. , . , , SQL INSERT, UPDATE DELETE.

, . , . , . , , , . , , .

:

♦ .

♦ , ( UPDATE, . .).

♦ .

♦ 16 .

, , , .

♦ , .

, SQL Server.

CREATE TRIGGER. , . , , DROP TRIGGER. , :

ADOQuery1->SQL->Add("Create trigger trig on Obedinenie for update as begin if update([nazvanie_armii]) begin UPDATE chast SET chast.nazvanie_armii=inserted.nazvanie_armii FROM chast, deleted, inserted WHERE chast.nazvanie_armii=deleted.nazvanie_armii end end");

QuickReport Builder .

, , , ASCII , (CSV) HTML.

QuickReport QReport Delphi. .


Figure 1 - TQuickRep and band components

TQuickRep. , . , . Page , Dataset .

TQuickReport TDataModule , . , TQuickReport - TQuickRep , . TQuickRep : . , TQuickRep OnCreate .

3.        

, . , , , , , .

(, , , , , ) (, , , , ).

, : , .. , , .

.

: , , . : , , , ..

, . ( 1, 2 . . .), .

 

4.        

 

5.        

/*

Created11.12.2006

Modified19.12.2006

Project

Model

Company

Author

Version

DatabaseMS SQL 2000

*/

Drop trigger [tu_Chast]

go

Drop trigger [tu_Rota]

go

Drop trigger [tu_Vzvod]

go

Drop trigger [tu_Obedinenie]

go

Drop trigger [tu_armia]

go

Drop trigger [tu_Podrazdelenie]

go

Drop trigger [td_Chast]

go

Drop trigger [td_Rota]

go

Drop trigger [td_Vzvod]

go

Drop trigger [td_Obedinenie]

go

Drop trigger [td_armia]

go

Drop trigger [td_Podrazdelenie]

go

Drop table [Voorugenie]

go

Drop table [Podrazdelenie]

go

Drop table [Tehnika]

go

Drop table [Soorugenia]

go

Drop table [armia]

go

Drop table [sostav]

go

Drop table [Obedinenie]

go

Drop table [Otdelenie]

go

Drop table [Vzvod]

go

Drop table [Rota]

go

Drop table [Chast]

go

Create table [Chast]

(

[Nazvanie_chasti] Char(10) NULL,

[Nomer_podrazdelenia] Char(10) NOT NULL,

[tip_i_nomer_obedinenia] Char(10) NOT NULL,

[nazvanie_armii] Char(10) NOT NULL,

[Mesto] Char(10) NOT NULL,

Constraint [pk_Chast] Primary Key ([Nomer_podrazdelenia],[tip_i_nomer_obedinenia],[nazvanie_armii],[Mesto])

)

go

Create table [Rota]

(

[Nazv_roti] Char(10) NOT NULL,

[Nomer_podrazdelenia] Char(10) NOT NULL,

[tip_i_nomer_obedinenia] Char(10) NOT NULL,

[nazvanie_armii] Char(10) NOT NULL,

[Mesto] Char(10) NOT NULL,

Constraint [pk_Rota] Primary Key ([Nazv_roti],[Nomer_podrazdelenia],[tip_i_nomer_obedinenia],[nazvanie_armii],[Mesto])

)

go

Create table [Vzvod]

(

[Nazv_vzvoda] Char(10) NOT NULL,

[Nomer_podrazdelenia] Char(10) NOT NULL,

[nazvanie_armii] Char(10) NOT NULL,

[tip_i_nomer_obedinenia] Char(10) NOT NULL,

[Nazv_roti] Char(10) NOT NULL,

[Mesto] Char(10) NOT NULL,

Constraint [pk_Vzvod] Primary Key ([Nazv_vzvoda],[Nomer_podrazdelenia],[nazvanie_armii],[tip_i_nomer_obedinenia],[Nazv_roti],[Mesto])

)

go

Create table [Otdelenie]

(

[Nazv_otdel] Char(10) NOT NULL,

[Nomer_podrazdelenia] Char(10) NOT NULL,

[nazvanie_armii] Char(10) NOT NULL,

[tip_i_nomer_obedinenia] Char(10) NOT NULL,

[Nazv_roti] Char(10) NOT NULL,

[Nazv_vzvoda] Char(10) NOT NULL,

[Mesto] Char(10) NOT NULL,

Constraint [pk_Otdelenie] Primary Key ([Nazv_otdel],[Nomer_podrazdelenia],[nazvanie_armii],[tip_i_nomer_obedinenia],[Nazv_roti],[Nazv_vzvoda],[Mesto])

)

go

Create table [Obedinenie]

(

[tip_i_nomer_obedinenia] Char(10) NOT NULL,

[Nomer_podrazdelenia] Char(10) NOT NULL,

[nazvanie_armii] Char(10) NOT NULL,

Constraint [pk_Obedinenie] Primary Key ([tip_i_nomer_obedinenia],[Nomer_podrazdelenia],[nazvanie_armii])

)

go

Create table [sostav]

(

[Kod_slugashego] Char(10) NOT NULL,

[FIO] Char(30) NOT NULL,

[Zvanie] Char(30) NOT NULL,

[Special] Char(30) NOT NULL,

[Tip_sostava] Char(10) NOT NULL,

[Podchinaetca] Char(10) NOT NULL,

[Nomer_podrazdelenia] Char(10) NOT NULL,

Constraint [pk_sostav] Primary Key ([Kod_slugashego],[FIO],[Zvanie],[Nomer_podrazdelenia])

)

go

Create table [armia]

(

[nazvanie_armii] Char(10) NOT NULL,

[Nomer_podrazdelenia] Char(10) NOT NULL,

Constraint [pk_armia] Primary Key ([nazvanie_armii],[Nomer_podrazdelenia])

)

go

Create table [Soorugenia]

(

[Nomer_coorugenia] Char(10) NOT NULL,

[Nomer_disloc_obedinenia] Char(10) NOT NULL,

[Nomer_podrazdelenia] Char(10) NOT NULL,

Constraint [pk_Soorugenia] Primary Key ([Nomer_coorugenia],[Nomer_disloc_obedinenia],[Nomer_podrazdelenia])

)

go

Create table [Tehnika]

(

[Tip_tehniki] Char(10) NOT NULL,

[Kol_vo_tehniki] Integer NOT NULL,

[Nomer_podrazdelenia] Char(10) NOT NULL,

Constraint [pk_Tehnika] Primary Key ([Tip_tehniki],[Kol_vo_tehniki],[Nomer_podrazdelenia])

)

go

Create table [Podrazdelenie]

(

[Nomer_podrazdelenia] Char(10) NOT NULL,

Constraint [pk_Podrazdelenie] Primary Key ([Nomer_podrazdelenia]))

go

Create table [Voorugenie]

(

[Tip_voorug] Char(10) NOT NULL,

[Kol_vo_voorug] Integer NOT NULL,

[Nomer_podrazdelenia] Char(10) NOT NULL,

Constraint [pk_Voorugenie] Primary Key ([Tip_voorug],[Kol_vo_voorug],[Nomer_podrazdelenia]))

go

Set quoted_identifier on

go

/* Update trigger "tu_Chast" for table "Chast" */

Create trigger [tu_Chast]

on [Chast] for update as

begin

declare @numrows int

select @numrows = @@rowcount

if @numrows = 0

return

/* Restrict child "Rota" when parent "Chast" updated */

if update([Nomer_podrazdelenia]) or

update([tip_i_nomer_obedinenia]) or

update([nazvanie_armii]) or

update([Mesto])

begin

if exists (select 1 from [Rota] t, deleted d

where t.[Nomer_podrazdelenia] = d.[Nomer_podrazdelenia] and

t.[tip_i_nomer_obedinenia] = d.[tip_i_nomer_obedinenia] and

t.[nazvanie_armii] = d.[nazvanie_armii] and

t.[Mesto] = d.[Mesto])

begin

raiserror 50001 'Children still exist in table ''Rota''. Cannot update parent table ''Chast''.'

rollback transaction

return

end

end

end

go

/* Update trigger "tu_Rota" for table "Rota" */

Create trigger [tu_Rota]

on [Rota] for update as

begin

declare @numrows int

select @numrows = @@rowcount

if @numrows = 0

return

/* Restrict child "Vzvod" when parent "Rota" updated */

if update([Nazv_roti]) or

update([Nomer_podrazdelenia]) or

update([tip_i_nomer_obedinenia]) or

update([nazvanie_armii]) or

update([Mesto])

begin

if exists (select 1 from [Vzvod] t, deleted d

where t.[Nazv_roti] = d.[Nazv_roti] and

t.[Nomer_podrazdelenia] = d.[Nomer_podrazdelenia] and

t.[tip_i_nomer_obedinenia] = d.[tip_i_nomer_obedinenia] and

t.[nazvanie_armii] = d.[nazvanie_armii] and

t.[Mesto] = d.[Mesto])

begin

raiserror 50001 'Children still exist in table ''Vzvod''. Cannot update parent table ''Rota''.'

rollback transaction

return

end

end

end

go

/* Update trigger "tu_Vzvod" for table "Vzvod" */

Create trigger [tu_Vzvod]

on [Vzvod] for update as

begin

declare @numrows int

select @numrows = @@rowcount

if @numrows = 0

return

/* Restrict child "Otdelenie" when parent "Vzvod" updated */

if update([Nazv_vzvoda]) or

update([Nomer_podrazdelenia]) or

update([nazvanie_armii]) or

update([tip_i_nomer_obedinenia]) or

update([Nazv_roti]) or

update([Mesto])

begin

if exists (select 1 from [Otdelenie] t, deleted d

where t.[Nazv_vzvoda] = d.[Nazv_vzvoda] and

t.[Nomer_podrazdelenia] = d.[Nomer_podrazdelenia] and

t.[nazvanie_armii] = d.[nazvanie_armii] and

t.[tip_i_nomer_obedinenia] = d.[tip_i_nomer_obedinenia] and

t.[Nazv_roti] = d.[Nazv_roti] and

t.[Mesto] = d.[Mesto])

begin

raiserror 50001 'Children still exist in table ''Otdelenie''. Cannot update parent table ''Vzvod''.'

rollback transaction

return

end

end

end

go

/* Update trigger "tu_Obedinenie" for table "Obedinenie" */

Create trigger [tu_Obedinenie]

on [Obedinenie] for update as

begin

declare @numrows int

select @numrows = @@rowcount

if @numrows = 0

return

/* Restrict child "Chast" when parent "Obedinenie" updated */

if update([tip_i_nomer_obedinenia]) or

update([Nomer_podrazdelenia]) or

update([nazvanie_armii])

begin

if exists (select 1 from [Chast] t, deleted d

where t.[tip_i_nomer_obedinenia] = d.[tip_i_nomer_obedinenia] and

t.[Nomer_podrazdelenia] = d.[Nomer_podrazdelenia] and

t.[nazvanie_armii] = d.[nazvanie_armii])

begin

raiserror 50001 'Children still exist in table ''Chast''. Cannot update parent table ''Obedinenie''.'

rollback transaction

return

end

end

end

go

/* Update trigger "tu_armia" for table "armia" */

Create trigger [tu_armia]

on [armia] for update as

begin

declare @numrows int

select @numrows = @@rowcount

if @numrows = 0

return

/* Restrict child "Obedinenie" when parent "armia" updated */

if update([nazvanie_armii]) or

update([Nomer_podrazdelenia])

begin

if exists (select 1 from [Obedinenie] t, deleted d

where t.[nazvanie_armii] = d.[nazvanie_armii] and

t.[Nomer_podrazdelenia] = d.[Nomer_podrazdelenia])

begin

raiserror 50001 'Children still exist in table ''Obedinenie''. Cannot update parent table ''armia''.'

rollback transaction

return

end

end

end

go

/* Update trigger "tu_Podrazdelenie" for table "Podrazdelenie" */

Create trigger [tu_Podrazdelenie]

on [Podrazdelenie] for update as

begin

declare @numrows int

select @numrows = @@rowcount

if @numrows = 0

return

/* Restrict child "armia" when parent "Podrazdelenie" updated */

if update([Nomer_podrazdelenia])

begin

if exists (select 1 from [armia] t, deleted d

where t.[Nomer_podrazdelenia] = d.[Nomer_podrazdelenia])

begin

raiserror 50001 'Children still exist in table ''armia''. Cannot update parent table ''Podrazdelenie''.'

rollback transaction

return

end

end

/* Restrict child "sostav" when parent "Podrazdelenie" updated */

if update([Nomer_podrazdelenia])

begin

if exists (select 1 from [sostav] t, deleted d

where t.[Nomer_podrazdelenia] = d.[Nomer_podrazdelenia])

begin

raiserror 50001 'Children still exist in table ''sostav''. Cannot update parent table ''Podrazdelenie''.'

rollback transaction

return

end

end

/* Restrict child "Tehnika" when parent "Podrazdelenie" updated */

if update([Nomer_podrazdelenia])

begin

if exists (select 1 from [Tehnika] t, deleted d

where t.[Nomer_podrazdelenia] = d.[Nomer_podrazdelenia])

begin

raiserror 50001 'Children still exist in table ''Tehnika''. Cannot update parent table ''Podrazdelenie''.'

rollback transaction

return

end

end

/* Restrict child "Voorugenie" when parent "Podrazdelenie" updated */

if update([Nomer_podrazdelenia])

begin

if exists (select 1 from [Voorugenie] t, deleted d

where t.[Nomer_podrazdelenia] = d.[Nomer_podrazdelenia])

begin

raiserror 50001 'Children still exist in table ''Voorugenie''. Cannot update parent table ''Podrazdelenie''.'

rollback transaction

return

end

end

/* Restrict child "Soorugenia" when parent "Podrazdelenie" updated */

if update([Nomer_podrazdelenia])

begin

if exists (select 1 from [Soorugenia] t, deleted d

where t.[Nomer_podrazdelenia] = d.[Nomer_podrazdelenia])

begin

raiserror 50001 'Children still exist in table ''Soorugenia''. Cannot update parent table ''Podrazdelenie''.'

rollback transaction

return

end

end

end

go

/* Delete trigger "td_Chast" for table "Chast" */

Create trigger [td_Chast]

on [Chast] for delete as

begin

declare @numrows int

select @numrows = @@rowcount

if @numrows = 0

return

/* Restrict child "Rota" when parent "Chast" deleted */

if exists (select 1 from [Rota] t, deleted d

where t.[Nomer_podrazdelenia] = d.[Nomer_podrazdelenia] and

t.[tip_i_nomer_obedinenia] = d.[tip_i_nomer_obedinenia] and

t.[nazvanie_armii] = d.[nazvanie_armii] and

t.[Mesto] = d.[Mesto])

begin

raiserror 50003 'Children still exist in table ''Rota''. Cannot delete from parent table ''Chast''.'

rollback transaction

return

end

end

go

/* Delete trigger "td_Rota" for table "Rota" */

Create trigger [td_Rota]

on [Rota] for delete as

begin

declare @numrows int

select @numrows = @@rowcount

if @numrows = 0

return

/* Restrict child "Vzvod" when parent "Rota" deleted */

if exists (select 1 from [Vzvod] t, deleted d

where t.[Nazv_roti] = d.[Nazv_roti] and

t.[Nomer_podrazdelenia] = d.[Nomer_podrazdelenia] and

t.[tip_i_nomer_obedinenia] = d.[tip_i_nomer_obedinenia] and

t.[nazvanie_armii] = d.[nazvanie_armii] and

t.[Mesto] = d.[Mesto])

begin

raiserror 50003 'Children still exist in table ''Vzvod''. Cannot delete from parent table ''Rota''.'

rollback transaction

return

end

end

go

/* Delete trigger "td_Vzvod" for table "Vzvod" */

Create trigger [td_Vzvod]

on [Vzvod] for delete as

begin

declare @numrows int

select @numrows = @@rowcount

if @numrows = 0

return

/* Restrict child "Otdelenie" when parent "Vzvod" deleted */

if exists (select 1 from [Otdelenie] t, deleted d

where t.[Nazv_vzvoda] = d.[Nazv_vzvoda] and

t.[Nomer_podrazdelenia] = d.[Nomer_podrazdelenia] and

t.[nazvanie_armii] = d.[nazvanie_armii] and

t.[tip_i_nomer_obedinenia] = d.[tip_i_nomer_obedinenia] and

t.[Nazv_roti] = d.[Nazv_roti] and

t.[Mesto] = d.[Mesto])

begin

raiserror 50003 'Children still exist in table ''Otdelenie''. Cannot delete from parent table ''Vzvod''.'

rollback transaction

return

end

end

go

/* Delete trigger "td_Obedinenie" for table "Obedinenie" */

Create trigger [td_Obedinenie]

on [Obedinenie] for delete as

begin

declare @numrows int

select @numrows = @@rowcount

if @numrows = 0

return

/* Restrict child "Chast" when parent "Obedinenie" deleted */

if exists (select 1 from [Chast] t, deleted d

where t.[tip_i_nomer_obedinenia] = d.[tip_i_nomer_obedinenia] and

t.[Nomer_podrazdelenia] = d.[Nomer_podrazdelenia] and

t.[nazvanie_armii] = d.[nazvanie_armii])

begin

raiserror 50003 'Children still exist in table ''Chast''. Cannot delete from parent table ''Obedinenie''.'

rollback transaction

return

end

end

go

/* Delete trigger "td_armia" for table "armia" */

Create trigger [td_armia]

on [armia] for delete as

begin

declare @numrows int

select @numrows = @@rowcount

if @numrows = 0

return

/* Restrict child "Obedinenie" when parent "armia" deleted */

if exists (select 1 from [Obedinenie] t, deleted d

where t.[nazvanie_armii] = d.[nazvanie_armii] and

t.[Nomer_podrazdelenia] = d.[Nomer_podrazdelenia])

begin

raiserror 50003 'Children still exist in table ''Obedinenie''. Cannot delete from parent table ''armia''.'

rollback transaction

return

end

end

go

/* Delete trigger "td_Podrazdelenie" for table "Podrazdelenie" */

Create trigger [td_Podrazdelenie]

on [Podrazdelenie] for delete as

begin

declare @numrows int

select @numrows = @@rowcount

if @numrows = 0

return

/* Restrict child "armia" when parent "Podrazdelenie" deleted */

if exists (select 1 from [armia] t, deleted d

where t.[Nomer_podrazdelenia] = d.[Nomer_podrazdelenia])

begin

raiserror 50003 'Children still exist in table ''armia''. Cannot delete from parent table ''Podrazdelenie''.'

rollback transaction

return

end

/* Restrict child "sostav" when parent "Podrazdelenie" deleted */

if exists (select 1 from [sostav] t, deleted d

where t.[Nomer_podrazdelenia] = d.[Nomer_podrazdelenia])

begin

raiserror 50003 'Children still exist in table ''sostav''. Cannot delete from parent table ''Podrazdelenie''.'

rollback transaction

return

end

/* Restrict child "Tehnika" when parent "Podrazdelenie" deleted */

if exists (select 1 from [Tehnika] t, deleted d

where t.[Nomer_podrazdelenia] = d.[Nomer_podrazdelenia])

begin

raiserror 50003 'Children still exist in table ''Tehnika''. Cannot delete from parent table ''Podrazdelenie''.'

rollback transaction

return

end

/* Restrict child "Voorugenie" when parent "Podrazdelenie" deleted */

if exists (select 1 from [Voorugenie] t, deleted d

where t.[Nomer_podrazdelenia] = d.[Nomer_podrazdelenia])

begin

raiserror 50003 'Children still exist in table ''Voorugenie''. Cannot delete from parent table ''Podrazdelenie''.'

rollback transaction

return

end

/* Restrict child "Soorugenia" when parent "Podrazdelenie" deleted */

if exists (select 1 from [Soorugenia] t, deleted d

where t.[Nomer_podrazdelenia] = d.[Nomer_podrazdelenia])

begin

raiserror 50003 'Children still exist in table ''Soorugenia''. Cannot delete from parent table ''Podrazdelenie''.'

rollback transaction

return

end

end

go

Set quoted_identifier off

go

/* Roles permissions */

6.         CASE Studio 2.21

 






7.        

 

 

 

 

:

SELECT chast.Nazvanie_chasti, chast.Nomer_podrazdelenia, chast.Mesto, chast.tip_i_nomer_obedinenia, chast.nazvanie_armii, sostav.FIO, sostav.Zvanie, sostav.Kod_slugashego FROM chast, sostav WHERE chast.nazvanie_armii=:par1 AND sostav.Nomer_podrazdelenia=chast.Nomer_podrazdelenia

SELECT chast.Nazvanie_chasti, chast.Nomer_podrazdelenia, chast.Mesto, chast.tip_i_nomer_obedinenia, chast.nazvanie_armii, sostav.FIO, sostav.Zvanie, sostav.Kod_slugashego FROM chast, sostav WHERE chast.Nomer_podrazdelenia=sostav.Nomer_podrazdelenia AND sostav.Tip_sostava=:par1

SELECT Nazvanie_chasti, Nomer_podrazdelenia, Mesto FROM chast WHERE Nomer_podrazdelenia=:par1

SELECT chast.Nazvanie_chasti, chast.Nomer_podrazdelenia, tehnika.Tip_tehniki, tehnika.kol_vo_tehniki FROM chast, tehnika WHERE chast.Nomer_podrazdelenia=tehnika.Nomer_podrazdelenia AND chast.Nomer_podrazdelenia=:par1

SELECT Soorugenia.Nomer_coorugenia, Soorugenia.Nomer_disloc_obedinenia, Chast.Nazvanie_chasti FROM Soorugenia, chast WHERE chast.Nomer_podrazdelenia=Soorugenia.Nomer_podrazdelenia AND chast.Nomer_podrazdelenia=:par1

SELECT nomer_coorugenia, COUNT(Nomer_disloc_obedinenia) as col FROM Soorugenia GROUP BY nomer_coorugenia HAVING COUNT(Nomer_disloc_obedinenia)>:par1

SELECT chast.Nomer_podrazdelenia, tehnika.kol_vo_tehniki, tehnika.tip_tehniki FROM chast, tehnika WHERE chast.Nomer_podrazdelenia=tehnika.Nomer_podrazdelenia AND tehnika.kol_vo_tehniki>:par1 ORDER BY chast.Nomer_podrazdelenia

SELECT chast.Nazvanie_chasti, chast.Nomer_podrazdelenia, Voorugenie.Tip_voorug, Voorugenie.kol_vo_voorug FROM chast, Voorugenie WHERE chast.Nomer_podrazdelenia=Voorugenie.Nomer_podrazdelenia AND chast.Nomer_podrazdelenia=:par1

SELECT sostav.special, COUNT(sostav.FIO) as col FROM Chast, sostav WHERE chast.Nomer_podrazdelenia=sostav.Nomer_podrazdelenia GROUP BY sostav.special HAVING COUNT(sostav.FIO)>:par1

SELECT chast.Nomer_podrazdelenia, chast.Nazvanie_chasti, sostav.FIO, sostav.Zvanie, sostav.Kod_slugashego FROM Chast, sostav WHERE chast.Nomer_podrazdelenia=sostav.Nomer_podrazdelenia AND sostav.special=:par1

SELECT chast.Nazvanie_chasti, chast.Nomer_podrazdelenia, chast.Mesto, Voorugenie.Tip_voorug, Voorugenie.kol_vo_voorug FROM Chast, Voorugenie WHERE chast.Nomer_podrazdelenia= Voorugenie.Nomer_podrazdelenia AND Voorugenie.Tip_voorug=:par1 AND Voorugenie.kol_vo_voorug>10

SELECT nazvanie_armii, COUNT(Nomer_podrazdelenia) AS col FROM chast GROUP BY nazvanie_armii HAVING COUNT(nazvanie_armii)>=ALL(SELECT COUNT(nazvanie_armii) FROM Chast GROUP BY nazvanie_armii)

8.        

 

UNIT1:

//---------------------------------------------------------------------------

#include <vcl.h>

#pragma hdrstop

#include "Unit2.h"

#include "Unit1.h"

#include "Unit3.h"

//---------------------------------------------------------------------------

#pragma package(smart_init)

#pragma resource "*.dfm"

TForm1 *Form1;

int colcount;

TQRDBText *qrdbed[10];

TQRLabel *qrlabl[10];

TDBEdit *dbed[10];

TLabel *labl[10];

//---------------------------------------------------------------------------

__fastcall TForm1::TForm1(TComponent* Owner)

: TForm(Owner)

{

Label5->Caption=ADOTable2->TableName;

Label4->Caption=ADOTable1->TableName;

}

//---------------------------------------------------------------------------

void __fastcall TForm1::Button3Click(TObject *Sender)

{

ADOQuery1->Active=false;

AnsiString zapros;

DataSource3->DataSet=ADOQuery1;

zapros=ComboBox1->Text;

ADOQuery1->SQL->Clear();

ADOQuery1->SQL->Add(zapros);

if (ADOQuery1->Parameters->Count!=0)

ADOQuery1->Parameters->ParamByName("par1")->Value=Edit1->Text;

ADOQuery1->ExecSQL();

ADOQuery1->Active=true;

}

//---------------------------------------------------------------------------

void __fastcall TForm1::Button4Click(TObject *Sender)

{

TLocateOptions Opts;

Opts.Clear();

Opts<<loPartialKey<<loCaseInsensitive;

ADOTable1->Locate("Nomer_podrazdelenia",Edit2->Text,Opts);

ADOTable1->Delete();

}

//---------------------------------------------------------------------------

void __fastcall TForm1::Button1Click(TObject *Sender)

{

Form1->ADOTable1->Active=false;

DBText1->DataField="";

Form2->DBEdit1->DataField="";

Form2->DBEdit2->DataField="";

Form2->DBEdit3->DataField="";

Form2->DBEdit4->DataField="";

Form2->DBEdit5->DataField="";

Form2->DBEdit6->DataField="";

Form2->DBEdit7->DataField="";

//-----------------------------

Form3->QRDBText1->DataField="";

Form3->QRDBText2->DataField="";

Form3->QRDBText3->DataField="";

Form3->QRDBText4->DataField="";

Form3->QRDBText5->DataField="";

Form3->QRDBText6->DataField="";

Form3->QRDBText7->DataField="";

ADOTable1->TableName=ComboBox2->Text;

Label4->Caption=ADOTable1->TableName;

ADOTable1->Active=true;

colcount=ADOTable1->Fields->Count;

//------------------------------------------

dbed[1]=Form2->DBEdit1;

dbed[2]=Form2->DBEdit2;

dbed[3]=Form2->DBEdit3;

dbed[4]=Form2->DBEdit4;

dbed[5]=Form2->DBEdit5;

dbed[6]=Form2->DBEdit6;

dbed[7]=Form2->DBEdit7;

labl[1]=Form2->Label1;

labl[2]=Form2->Label2;

labl[3]=Form2->Label3;

labl[4]=Form2->Label4;

labl[5]=Form2->Label5;

labl[6]=Form2->Label6;

labl[7]=Form2->Label7;

//------------------------------------------------

qrdbed[1]=Form3->QRDBText1;

qrdbed[2]=Form3->QRDBText2;

qrdbed[3]=Form3->QRDBText3;

qrdbed[4]=Form3->QRDBText4;

qrdbed[5]=Form3->QRDBText5;

qrdbed[6]=Form3->QRDBText6;

qrdbed[7]=Form3->QRDBText7;

qrlabl[1]=Form3->QRLabel1;

qrlabl[2]=Form3->QRLabel2;

qrlabl[3]=Form3->QRLabel3;

qrlabl[4]=Form3->QRLabel4;

qrlabl[5]=Form3->QRLabel5;

qrlabl[6]=Form3->QRLabel6;

qrlabl[7]=Form3->QRLabel7;

//------------------------------------------------

for(int i=1;i<=colcount;i++)

{

dbed[i]->Visible=true;

labl[i]->Visible=true;

dbed[i]->DataSource=DataSource1;

dbed[i]->DataField=ADOTable1->Fields->FieldByNumber(i)->FieldName;

labl[i]->Caption=ADOTable1->Fields->FieldByNumber(i)->FieldName;

}

for(int i=colcount+1;i<=7;i++)

{

dbed[i]->Visible=false;

labl[i]->Visible=false;

}

}

//---------------------------------------------------------------------------

void __fastcall TForm1::Button2Click(TObject *Sender)

{

ADOTable2->Active=false;

ADOTable2->TableName=ComboBox3->Text;

Label5->Caption=ADOTable2->TableName;

ADOTable2->Active=true;

}

//---------------------------------------------------------------------------

void __fastcall TForm1::Button5Click(TObject *Sender)

{

Form2->Show();

TLocateOptions Opts;

Opts.Clear();

Opts<<loPartialKey<<loCaseInsensitive;

ADOTable1->Locate("Nomer_podrazdelenia",Edit2->Text,Opts);

if (ADOTable1->TableName==WideString("sostav"))

{

TLocateOptions Opts1;

Opts1.Clear();

Opts1<<loPartialKey<<loCaseInsensitive;

ADOTable1->Locate("kod_slugashego",Edit2->Text,Opts1);

}

}

//---------------------------------------------------------------------------

void __fastcall TForm1::Button6Click(TObject *Sender)

{

ADOTable1->Append();

Form2->Show();

}

//---------------------------------------------------------------------------

void __fastcall TForm1::Button7Click(TObject *Sender)

{

DataSource3->DataSet=ADOStoredProc1;

ADOQuery1->Active=false;

ADOQuery1->SQL->Clear();

ADOQuery1->SQL->Add("CREATE PROCEDURE proc2;1 as SELECT nazvanie_armii, COUNT(Nomer_podrazdelenia) AS col FROM chast GROUP BY nazvanie_armii HAVING COUNT(nazvanie_armii)>=ALL(SELECT COUNT(nazvanie_armii) FROM Chast GROUP BY nazvanie_armii)");

ADOStoredProc1->ProcedureName="proc2;1";

ADOQuery1->ExecSQL();

ADOStoredProc1->Active=false;

ADOStoredProc1->ExecProc();

ADOStoredProc1->Active=true;

ADOQuery1->SQL->Clear();

ADOQuery1->SQL->Add ("drop procedure proc2");

ADOQuery1->ExecSQL();

}

//---------------------------------------------------------------------------

void __fastcall TForm1::Button8Click(TObject *Sender)

{

ADOQuery1->Active=false;

ADOQuery1->SQL->Clear();

ADOQuery1->SQL->Add("Create trigger trig on Obedinenie for update as begin if update([nazvanie_armii]) begin UPDATE chast SET chast.nazvanie_armii=inserted.nazvanie_armii FROM chast, deleted, inserted WHERE chast.nazvanie_armii=deleted.nazvanie_armii end end");

ADOQuery1->ExecSQL();

ADOTable1->Active=false;

Form2->DBEdit1->DataField="";

Form2->DBEdit2->DataField="";

Form2->DBEdit3->DataField="";

Form2->DBEdit4->DataField="";

Form2->DBEdit5->DataField="";

Form2->DBEdit6->DataField="";

Form2->DBEdit7->DataField="";

DBText1->DataField="";

DBText1->DataSource=DataSource1;

DBText1->DataField="nazvanie_armii";

ADOTable1->TableName="Obedinenie";

Label4->Caption=ADOTable1->TableName;

ADOTable1->Active=true;

ADOTable2->Active=false;

ADOTable2->TableName="Chast" ;

Label5->Caption=ADOTable2->TableName;

ADOTable2->Active=true;

DBText1->DataField="nazvanie_armii";

}

//---------------------------------------------------------------------------

void __fastcall TForm1::Button9Click(TObject *Sender)

{

ADOQuery1->Active=false;

ADOQuery1->SQL->Clear();

ADOQuery1->SQL->Add("DROP TRIGGER trig");

ADOQuery1->ExecSQL();

}

//---------------------------------------------------------------------------

void __fastcall TForm1::Button11Click(TObject *Sender)

{

ADOTable1->Active=false;

ADOTable1->Active=true;

ADOTable2->Active=false;

ADOTable2->Active=true;

}

//---------------------------------------------------------------------------

void __fastcall TForm1::Button10Click(TObject *Sender)

{

ADOQuery1->Active=false;

ADOQuery1->SQL->Clear();

ADOQuery1->SQL->Add("UPDATE Obedinenie SET nazvanie_armii=:nd1 WHERE nazvanie_armii=:nd2");

ADOQuery1->Parameters->ParamByName("nd1")->Value=Edit3->Text;

ADOQuery1->Parameters->ParamByName("nd2")->Value=DBText1->Caption;

ADOQuery1->ExecSQL();

ADOTable1->Active=false;

ADOTable1->Active=true;

ADOTable2->Active=false;

ADOTable2->Active=true;

}

//---------------------------------------------------------------------------

void __fastcall TForm1::Button12Click(TObject *Sender)

{

for(int i=1;i<=colcount;i++)

{

qrdbed[i]->DataSet=Form1->ADOTable1;

qrdbed[i]->DataField=ADOTable1->Fields->FieldByNumber(i)->FieldName;

qrlabl[i]->Caption=ADOTable1->Fields->FieldByNumber(i)->FieldName;

qrdbed[i]->Visible=true;

qrlabl[i]->Visible=true;

}

for(int i=colcount+1;i<=7;i++)

{

qrdbed[i]->Visible=false;

qrlabl[i]->Visible=false;

}

Form3->QRLabel13->Caption=Label4->Caption;

Form3->QuickRep1->Preview();

}

//---------------------------------------------------------------------------

void __fastcall TForm1::Button13Click(TObject *Sender)

{

Form3->QuickRep1->Print();

}

//---------------------------------------------------------------------------

void __fastcall TForm1::Button14Click(TObject *Sender)

{

TLocateOptions Opts;

Opts.Clear();

Opts<<loPartialKey<<loCaseInsensitive;

ADOTable1->Locate("Nomer_podrazdelenia",Edit2->Text,Opts);

}

//---------------------------------------------------------------------------

//---------------------------------------------------------------------------

UNIT2:

#include <vcl.h>

#pragma hdrstop

#include "Unit1.h"

#include "Unit2.h"

//---------------------------------------------------------------------------

#pragma package(smart_init)

#pragma resource "*.dfm"

TForm2 *Form2;

//---------------------------------------------------------------------------

__fastcall TForm2::TForm2(TComponent* Owner)

: TForm(Owner)

{

}

//---------------------------------------------------------------------------

void __fastcall TForm2::Button2Click(TObject *Sender)

{

Form1->ADOTable1->Post();

Hide();

}

//---------------------------------------------------------------------------

void __fastcall TForm2::Button1Click(TObject *Sender)

{

Hide();

}

//---------------------------------------------------------------------------


9.        

 

1.         " : , , ", .. , .: -, 2004. 512 .: .

2.         "SQL Server 2000 ", 2 ./. : I; . .; . .. . .: . , 2004. 735 ., .

 

 

 

! , , , .
. , :