Создание базы данных с помощью CASE Studio 2.21
Программа
Запросы: 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) Текст программы 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(); } //---------------------------------------------------------------------------
Список литературы 1. "Базы данных: основы, проектирование, использование", М.П. Малыхина, СПб.: БХВ-Петербург, 2004. – 512 с.: ил. 2. "SQL Server 2000 Программирование", в 2 ч./Р. Вьейра: Часть I; Пер. с англ.; Под ред. С.М. Молявко. – М.: Бином. Лаборатория знаний, 2004. – 735 с., ил.
Популярное: Модели организации как закрытой, открытой, частично открытой системы: Закрытая система имеет жесткие фиксированные границы, ее действия относительно независимы... Почему двоичная система счисления так распространена?: Каждая цифра должна быть как-то представлена на физическом носителе... Как выбрать специалиста по управлению гостиницей: Понятно, что управление гостиницей невозможно без специальных знаний. Соответственно, важна квалификация... ©2015-2024 megaobuchalka.ru Все материалы представленные на сайте исключительно с целью ознакомления читателями и не преследуют коммерческих целей или нарушение авторских прав. (205)
|
Почему 1285321 студент выбрали МегаОбучалку... Система поиска информации Мобильная версия сайта Удобная навигация Нет шокирующей рекламы |