capitolul 5 sql în visual foxpro - zenovia coras · capitolul 5 sql în visual foxpro foxpro a...

Capitolul 5

SQL în Visual FoxPro

FoxPro a fost unul dintre primele SGBD-uri xBase care au implementat un important QXFOHX 64/� 9LVXDO )R[3UR UHVSHFW�� vQ FRQWLQXDUH� PXOWH GLQ VSHFLILFD LLOH VWDQGDUGXOXL

SQL-�� GHúL QX SXWHP YRUEL GH XQ QLYHO VLPLODU PDULlor produse ale lumii SGBDR: Oracle, DB2, Sybase, Informix, SQL Server etc. Cu toate limitele sale, care vor fi punctate pe SDUFXUVXO DFHVWXL FDSLWRO� SULQ SRVLELOLWDWHD LQFOXGHULL FRPHQ]LORU 64/ vQ SURJUDPH 9)3 úL

folosirea cursoarelor sau tabelelor virtuale, practic în Visual FoxPro se poate rezolva orice SUREOHP� GH LQWHURJDUH úL PDQLSXODUH D GDWHORU�

5.1. Principalele comenzi SQL din VFP

Comenzile SQL-92 implementate în Visual FoxPro 6 sunt prezentate în tabelul 6.1.

Tabelul 5.1. Comenzi SQL în Visual FoxPro

&RPDQG� Scop Pentru manipularea datelor SELECT Extragerea datelor din BD INSERT $G�XJDUHD GH QRL OLQLL vQWU-R WDEHO� DELETE ùWHJHUHD GH OLQLL GLQWU-R WDEHO� UPDATE Modificarea valorilor unor atribute Pentru definirea bazei de date CREATE TABLE $G�XJDUHD XQHL QRL WDEHOH vQ %' DROP TABLE ùWHUJHUHD XQHL WDEHOH GLQ ED]� ALTER TABLE Modificarea structurii unei tabele CREATE VIEW Crearea unei tabele virtuale DROP VIEW ùWHUJHUHD XQHL WDEHOH YLUWXDOH CREATE CURSOR Crearea unei tabele temporare 3HQWUX FRQWUROXO WUDQ]DF LLORU BEGIN TRANSACTION 0DUFKHD]� vQFHSXWXO XQHL WUDQ]DF LL END TRANSACTION 0DUFKHD]� VIkUúLWXO XQHL WUDQ]DF LL ROLLBACK $EDQGRQHD]� WUDQ]DF LD vQ FXUV

'LQ SDUDJUDIXO XUP�WRU YRP vQFHSH SUH]HQWDUHD FRPHQ]LORU 64/ FX RS LXQLOH HVHQ LDOH

SHQWUX FUHDUHD úL PRGLILFDUHD VWUXFWXULL XQHL ED]H GDWH úL D WDEHOHORU DFHVWHLD – CREATE TABLE, ALTER TABLE� LQFOXVLY D UHVWULF LLORU FH SRW IL GHILQLWH OD QLYHO GH DWULEXW VDX

WDEHO�� 3HQWUX SRSXODUHD úL DFWXDOL]DUHD tabelelor vor fi prezentate clauzele comenzilor INSERT, UPDATE úL DELETE.

SQL în Visual FoxPro 127

5.2. &UHDUHD�úL�PRGLILFDUHD�VWUXFWXULL�WDEHOHORU

'DF� vQ FDSLWROXO � DX IRVW SUH]HQWDWH PRGDOLW� LOH DVLVWDWH GH FUHDUH D ED]HL GH GDWH� D

WDEHOHORU DFHVWHLD� LQFOXVLY D UHVWULF LLORU� în acest capitol ne vom apropia de zona ÄSURIHVLRQDO�´ D GH]YROW�ULL PRGXOHORU ED]HL GH GDWH vQ FDGUXO DSOLFD LLORU 9)3�

5.2.1. &UHDUHD WDEHOHORU úL GHFODUDUHD UHVWULF LLORU

&RPDQGD 64/ XWLOL]DW� SHQWUX FUHDUHD XQHL WDEHOH HVWH CREATE TABLE, iar pentru modificarHD XOWHULRDU� D VWUXFWXULL VH IRORVHúWH ALTER TABLE� 3UHFL]�P F� HVWH YRUED GH

FUHDUHD VWUXFWXULL WDEHOHL� 3RSXODUHD FX vQUHJLVWU�UL úL DFWXDOL]DUHD XOWHULRDU� VH UHDOL]HD]�

prin alte comenzi: INSERT, UPDATE, DELETE. Formatul general al comenzii CREATE TABLE în Visual FoxPro este:

CREATE TABLE | DBF TableName1 [NAME LongTableName] [FREE]

(FieldName1 FieldType [(nFieldWidth [, nPrecision])] [NULL | NOT NULL] [CHECK lExpression1 [ERROR cMessageText1]] [DEFAULT eExpression1] [PRIMARY KEY | UNIQUE] [REFERENCES TableName2 [TAG TagName1]] [NOCPTRANS] [, FieldName2 ...] [, PRIMARY KEY eExpression2 TAG TagName2 |, UNIQUE eExpression3 TAG TagName3] [, FOREIGN KEY eExpression4 TAG TagName4 [NODUP] REFERENCES TableName3 [TAG TagName5]] [, CHECK lExpression2 [ERROR cMessageText2]]) | FROM ARRAY ArrayName ,DW�� SH VFXUW� VHPQLILFD LD RS LXQLORU� CREATE TABLE | DBF TableName1 TableName1 UHSUH]LQW� QXPHOH WDEHOHL FH XUPHD]� D IL FUHDW�� 'H UHPDUFDW F� QX

H[LVW� QLFL R GLIHUHQ � vQWUH RS LXQLOH TABLE úL DBF� 3ULPD VH DGUHVHD]� FHORU DWDúD L

WHRULHL UHOD LRQDOH� LDU FHD GH-D GRXD HVWH PDL DSURDSH GH XWLOL]DWRUXO ÄWUDGL LRQDO´ DO )R[3UR

(xBase-XULORU� vQ JHQHUDO�� SHQWUX FDUH R WDEHO� HVWH XQ ILúLHU FX H[WHQVLD .DBF . NAME LongTableName Permite specLILFDUHD XQXL QXPH PDL OXQJ �SkQ� OD �� GH FDUDFWHUH� SHQWUX WDEHOD FUHDW��

3HQWUX DFHDVWD� HVWH QHFHVDU FD ED]D GH GDWH V� ILH vQ SUHDODELO GHVFKLV�� GHRDUHFH QXPHOH

lungi sunt memorate în containerul asociat bazei (.DBC). FREE ,QGLF� IDSWXO F� WDEHOD UHVSHFWLY� YD IL LQGHSHQGHQW�� GHFL QX YD IDFH SDUWH GLQ ED]��

128 Visual FoxPro

(FieldName1 FieldType [(nFieldWidth [, nPrecision])] Permite declararea, pentru fiecare atribut (câmp) al tabelei, a numelui, tipului, lungimii

úL� HYHQWXDO� D QXP�UXOXL GH SR]L LL SHQWUX UHSUH]HQWDUHD S�U LL IUDF LRQDUH �]HFLPDOH�� NULL 3ULQ VSHFLILFDUHD VD� DWULEXWXO HVWH DXWRUL]DW V� FRQ LQ� YDORUL QXOH �NULL). NOT NULL 1X SHUPLWH DSDUL LD YDORULORU QXOH SHQWUX DWULEXWXO UHVSHFWLY� $XWRPDW� SHQWUX DWULEXWHOH

GH WLS FKHLH SULPDU� VDX SHQWUX FDUH HVWH XWLOL]DW� RS LXQHD UNIQUE nu se admit valori NULL.

CHECK lExpression1 6HUYHúWH OD VSHFLILFDUHD XQHL IXQF LL-utilizator de validare la nivel de atribut (câmp)25.

$VWIHO� IXQF LD HVWH YHULILFDW� LPHGLDW GXS� DG�XJDUHD XQHL QRL vQUHJLVWU�UL �OLQLL� vQ WDEHO��

'DF� UH]XOWDWXO HYDOX�ULL IXQF LHL HVWH .F. (FALSE�� VH GHFODQúHD]� R HURDUH� ERROR cMessageText1 ÌQ FD]XO vQ FDUH IXQF LD GH YDOLGDUH GH OD QLYHOXO DWULEXWXOXL QX VH UHVSHFW� �DGLF�

ÄvQWRDUFH´ YDORDUHD ORJLF� FALSE), pe ecran apare mesajul cMessageText1 . DEFAULT eExpression1 6SHFLILF� YDORDUHD LPSOLFLW� D DWULEXWXOXL �vQ IXQF LH GH WLSXO DFHVWXLD�� YDORDUH SH FDUH R

YD FRQ LQH DFHVW FkPS OD DG�XJDUHD XQHL QRL vQUHJLVWU�UL vQ WDEHO�� PRIMARY KEY 'HFODU� DWULEXWXO UHVSHFWLY FKHLH SULPDU�� SULQ crearea unui index principal cu nume

identic ca al atributului. UNIQUE &UHHD]� XQ ÄLQGH[ FDQGLGDW´ – DOWIHO VSXV� GHFODU� DFHVW DWULEXW FKHLH DOWHUQDWLY�� 6H

SUHYLQH DVWIHO DSDUL LD D GRX� YDORUL LGHQWLFH vQ WDEHO� SHQWUX DFHVW FkPS� REFERENCES TableName2 [TAG TagName1] 3HUPLWH GHILQLUHD XQHL UHVWULF LL UHIHUHQ LDOH SULQ FUHDUHD XQHL OHJ�WXUL SHUPDQHQWH vQWUH

tabele. TableName2 este tabela-S�ULQWH D OHJ�WXULL� 'DF� QX VH VSHFLILF� TAG TagName1� UHOD LD HVWH VWDELOLW� SULQ LQWHUPHGLXO LQGH[XOXL SULPDU DO tabelei TableName2 � GDF� DFHVWD �LQGH[XO SULPDU� QX H[LVW�� VH JHQHUHD]� XQ PHVDM GH HURDUH�

TableName2 QX SRDWH IL R WDEHO� LQGHSHQGHQW�� NOCPTRANS (VWH XWLO� SHQWUX FkPSXUL GH WLS úLU GH FDUDFWHUH úL PHPR� SHQWUX D SUHYHQL FRQYHUVLD OD

un alt cod de pagLQ� �code page).

25. Vezi capitolul 2, paragraful 2.5.1.


PRIMARY KEY eExpression2 TAG TagName2 &UHHD]� DXWRPDW XQ LQGH[ SULPDU SHQWUX WDEHOD FXUHQW�� eExpression2 este un câmp

VDX R FRPELQD LH GH FkPSXUL DOH WDEHOHL� 1XPHOH LQGH[XOXL SRDWH FRQ LQH SkQ� OD ��

FDUDFWHUH� )LUHúWH� FODX]D PRIMARY KEY QX SRDWH DS�UHD GHFkW R VLQJXU� GDW� vQWU-o FRPDQG� CREATE TABLE.

UNIQUE eExpression3 TAG TagName3 &UHHD]� XQ LQGH[ FDQGLGDW� eExpression3 GHVHPQHD]� RULFH FkPS VDX FRPELQD LH

GH FkPSXUL DOH WDEHOHL� DWULEXW�FRPELQD LH FDUH YD vQGHSOLQL UROXO GH FKHLH DOWHUQDWLY��

TagName3 UHSUH]LQW� QXPHOH LQGH[XOXL FDQGLGDW FDUH HVWH DOF�WXLW WRW GLQ PD[LPXP ��

FDUDFWHUH� 3HQWUX R DFHHDúL WDEHO� SRW IL FUHD L PDL PXO L LQGHFúL FDQGLGD L�

FOREIGN KEY eExpression4 TAG TagName4 [NODUP] Are ca rezultat crearea unui indH[ RELúQXLW �regular� SHQWUX WDEHOD FXUHQW� �FKHLD GH

indexare fiind eExpression4 , iar numele acestuia TagName4� úL VWDELOLUHD XQHL UHOD LL

SHUPDQHQWH FX R WDEHO�-S�ULQWH� 3ULQ NODUP VH SRDWH FUHD XQ LQGH[ VWU�LQ FDQGLGDW �SULQ

DQDORJLH FX LQGHFúLL FDQGLGD L OD ÄSRVWXO´ GH LQGH[ SULPDU�� REFERENCES TableName3 [TAG TagName5] 6SHFLILF� QXPHOH WDEHOHL-S�ULQWH LPSOLFDWH vQ OHJ�WXUD FUHDW� SULQ RS LXQHD FOREIGN

KEY. Indexul TagName5 HVWH FHO SULQ FDUH VH UHDOL]HD]� OHJ�WXUD GLQWUH FHOH GRX� WDEHOH�

Implicit este indexul primar al tabelei TableName3 . CHECK eExpression2 [ERROR cMessageText2] 3HUPLWH VSHFLILFDUHD XQHL UHVWULF LL �UHJXOL GH YDOLGDUH� OD QLYHO GH WDEHO��

cMessageText2 HVWH PHVDMXO DILúDW vQ FD]XO QHUHVSHFW�ULL UHVWULF LHL� FROM ARRAY ArrayName PermLWH FUHDUHD XQHL WDEHOH SH ED]D GDWHORU FRQ LQXWH vQWU-R YDULDELO� GH WLS WDEORX� În listingul 5.1 este prezentat programul pentru crearea, în VFP 6, a bazei de date

VINZARI.

Listing 5.1. Crearea tabelelor bazei de date în VFP 6

* In caz ca baza exista deja, * pentru crearea sa, trebuie inchisa CLOSE DATA ALL CLOSE TABLES ALL CLOSE ALL * se sterge BD impreuna cu tabelele care o alcatuiesc * Atentie !!! Comanda urmatoare distruge intreg continutul bazei ! DELETE DATABASE vinzari DELETETABLES * A sosit vremea creatiei CREATE DATABASE vinzari

130 Visual FoxPro

* Secventa de creare a tabelelor * In tabela JUDETE * - JUD este cheie primara * - literele JUD trebuie sunt exclusiv majuscule * - JUDET este cheie candidat (alternativa) * - JUDET nu poate avea valori nule * - prima litera din fiecare cuvint al denumirii judetului * este obligatoriu majuscula * - valoarea implicita a atributului REGIUNE este „MOLDOVA” * - valoarea REGIUNE trebuie sa se incadreze in lista: * „MOLDOVA”, „Banat”,.... CREATE TABLE judete ( ; jud CHAR(2) ; PRIMARY KEY ; CHECK (jud=LTRIM(UPPER(jud))) ; ERROR 'Indicativul judetului se scrie cu majuscule !', ; judet CHAR(25) ; UNIQUE ; NOT NULL ; CHECK (judet=LTRIM(PROPER(judet))) ; ERROR 'Prima litera din fiecare cuvint al'+CHR(13)+; 'denumirii judetului este majuscula; '+CHR(13)+; 'restul literelor sunt mici!', ; regiune CHAR(15) ; DEFAULT 'Moldova' ; CHECK (INLIST(regiune, 'Banat', 'Transilvania', 'Dobrogea', 'Oltenia', ; 'Muntenia', 'Moldova')) ; ERROR 'Regiunea poate avea o valoare numai din lista:'+CHR(13)+; 'Banat, Transilvania, Dobrogea, Oltenia, Muntenia, Moldova' ; ) * In tabela LOCALITATI * - ... * - atributul JUD este cheie straina (parintele fiind tabela JUDETE) CREATE TABLE localitati ( ; codpost CHAR(5) ; PRIMARY KEY ; CHECK (codpost=LTRIM(codpost)) ; ERROR 'Codul postal se scrie fara spatii la inceput !', ; loc CHAR(25) ; NOT NULL ; CHECK (loc=LTRIM(PROPER(loc))) ; ERROR 'Prima litera din fiecare cuvint al'+CHR(13)+; 'denumirii localitatii este majuscula; '+CHR(13)+; 'restul literelor sunt mici!', ; jud CHAR(2) ; DEFAULT 'IS', ; FOREIGN KEY jud TAG jud REFERENCES judete TAG jud ; )


CREATE TABLE clienti ( ; codcl NUMBER(6) ; PRIMARY KEY ; CHECK (codcl > 1000), ; dencl CHAR(30) ; CHECK (SUBSTR(dencl,1,1) = UPPER(SUBSTR(dencl,1,1))) ; ERROR 'Prima litera din denumirea clientului este ; '+CHR(13)+; 'obligatoriu majuscula !', ; codfiscal CHAR(9) ; NULL ; CHECK (SUBSTR(codfiscal,1,1) = UPPER(SUBSTR(codfiscal,1,1))) ; ERROR 'Prima (eventuala) litera din codul fiscal ; '+CHR(13)+; 'este obligatoriu majuscula !', ; adresa CHAR(40) ; NULL ; CHECK (SUBSTR(adresa,1,1) = UPPER(SUBSTR(adresa,1,1))) ; ERROR 'Prima (eventuala) litera din adresa ; '+CHR(13)+; 'este obligatoriu majuscula !', ; codpost CHAR(5), ; telefon CHAR(10) ; NULL, ; FOREIGN KEY codpost TAG codpost REFERENCES localitati TAG codpost ; ) ; CREATE TABLE persoane ( ; cnp CHAR(14) ; PRIMARY KEY ; CHECK (cnp=LTRIM(UPPER(cnp))) ; ERROR 'Codul numeric personal se scrie fara spatii la inceput !', ; nume CHAR(20) ; CHECK (nume=LTRIM(PROPER(nume))) ; ERROR 'Prima litera din fiecare cuvint al'+CHR(13)+; 'numelui este majuscula; '+CHR(13)+; 'restul literelor sunt mici!', ; prenume CHAR(20) ; CHECK (prenume=LTRIM(PROPER(prenume))) ; ERROR 'Prima litera din fiecare cuvint al'+CHR(13)+; 'prenumelui este majuscula; '+CHR(13)+; 'restul literelor sunt mici!', ; adresa CHAR(40) ; NULL ; CHECK (SUBSTR(adresa,1,1) = UPPER(SUBSTR(adresa,1,1))) ; ERROR 'Prima litera din adresa este obligatoriu majuscula !', ; sex CHAR(1) DEFAULT 'B' ; CHECK (INLIST(sex,'F','B')) ; ERROR 'Atributul Sex poate avea valorile F (de la Femeiesc)'+CHR(13)+; 'sau B (de la Barbatesc) !', ; codpost CHAR(5), ; telacasa CHAR(10) NULL, ; telbirou CHAR(10) NULL, ; telmobil CHAR(10) NULL, ; email CHAR(20) NULL, ;

132 Visual FoxPro

FOREIGN KEY codpost TAG codpost REFERENCES localitati TAG codpost ; ) ; * In tabela PERSCLIENTI * - cheia primara este compusa: (CNP, CodCl) CREATE TABLE persclienti ( ; cnp CHAR(14), ; codcl NUMBER(6), ; functie CHAR(25) ; CHECK (SUBSTR(functie,1,1) = UPPER(SUBSTR(functie,1,1))) ; ERROR 'Prima litera din functie este obligatoriu majuscula !', ; PRIMARY KEY cnp+STR(codcl,6)+functie TAG primaru, ; FOREIGN KEY cnp TAG cnp REFERENCES persoane TAG cnp, ; FOREIGN KEY codcl TAG codcl REFERENCES clienti TAG codcl ; ) ; * Tabela PRODUSE * - contine atributul IMAGINE de tip GENERAL CREATE TABLE produse ( ; codpr NUMBER(6) ; PRIMARY KEY ; CHECK (codpr > 0) ; ERROR 'Codul produsului trebuie sa fie mai mare de 100000 !', ; denpr CHAR(30) ; CHECK (SUBSTR(denpr,1,1) = UPPER(SUBSTR(denpr,1,1))) ; ERROR 'Prima litera din denumirea produsului este obligatoriu majuscula !', ; um CHAR(10), ; grupa CHAR(15) ; CHECK (SUBSTR(grupa,1,1) = UPPER(SUBSTR(grupa,1,1))) ; ERROR 'Prima litera din grupa este obligatoriu majuscula !', ; procTVA NUMBER(3,2) ; DEFAULT .22, ; imagine GENERAL ; ) CREATE TABLE gestiuni ( ; gestiune CHAR(4) ; PRIMARY KEY, ; den_gest CHAR(25) ; CHECK (SUBSTR(den_gest,1,1) = UPPER(SUBSTR(den_gest,1,1))) ; ERROR 'Prima litera din denumirea gestiunii este obligatoriu majuscula !', ; adresa CHAR(40) ; NULL ; CHECK (SUBSTR(adresa,1,1) = UPPER(SUBSTR(adresa,1,1))) ; ERROR 'Prima litera din adresa este obligatoriu majuscula !', ; codpost CHAR(5), ; telefon CHAR(10) ; NULL, ; cnp CHAR(14), ; email CHAR(20) NULL, ;


FOREIGN KEY codpost TAG codpost REFERENCES localitati TAG codpost, ; FOREIGN KEY cnp TAG cnp REFERENCES persoane TAG cnp ; ) * In tabela FACTURI * - valorile atributului DATAFACT trebuie sa se incadreze * in intervalul 1 ian.2001 - 31 dec.2010 CREATE TABLE facturi ( ; nrfact NUMBER(8) ; PRIMARY KEY, ; datafact DATE ; DEFAULT DATE() ; CHECK (BETWEEN(datafact,{^2001/01/01},{^2010/12/31})) ; ERROR 'Baza de date functioneaza in intervalul 1 ian 2001 - 31 dec.2010 !', ; gestiune CHAR(4) NOT NULL, ; codcl NUMBER(6), ; obs CHAR(50) NULL, ; FOREIGN KEY gestiune TAG gsetiune REFERENCES gestiuni TAG gestiune, ; FOREIGN KEY codcl TAG codcl REFERENCES clienti TAG codcl ; ) ; CREATE TABLE liniifact ( ; nrfact NUMBER(8), ; linie NUMBER(2) ; CHECK (linie > 0) ; ERROR 'Atributul linie trebuie sa fie mai mare ca zero !', ; codpr NUMBER(6), ; cantitate NUMBER(10), ; pretunit NUMBER (12), ; PRIMARY KEY STR(nrfact,8)+STR(linie,2) TAG primaru, ; FOREIGN KEY nrfact TAG nrfact REFERENCES facturi TAG nrfact, ; FOREIGN KEY codpr TAG codpr REFERENCES produse TAG codpr ; ) * In tabela INCASARI * - valorile atributelor DATAINC si DATADOC trebuie sa se incadreze * in intervalul 1 ian.2001 - 31 dec.2010 * - valoarea implicita a atributului DATAINC este data curenta (data sistemului) * - valoarea implicita a atributului DATADOC este data curenta (data sistemului) * minus 5; aceasta inseamna ca, de obicei, intre emiterea documentului * de plata si data in care banii sosesc in cont trec 5 zile (e doar o presupunere !) CREATE TABLE incasari ( ; codinc NUMBER(8) ; PRIMARY KEY, ; datainc DATE ; DEFAULT DATE() ; CHECK (BETWEEN(datainc,{^2001/01/01},{^2010/12/31})) ; ERROR 'Baza de date functioneaza in intervalul 1 ian.2001 - 31 dec.2010 !', ; coddoc CHAR(4) ; CHECK(coddoc=UPPER(LTRIM(coddoc))) ; ERROR 'Codul documentului se scrie cu majuscule !', ; nrdoc CHAR(16), ;

134 Visual FoxPro

datadoc DATE ; DEFAULT DATE() - 5 ; CHECK (BETWEEN(datadoc, {^2001/01/01},{^2001/12/31})) ; ERROR 'Data documentului trebuie sa fie intre 1 ian.2000 si 31 dec.2010 !' ; ) ; CREATE TABLE incasfact ( ; codinc NUMBER(8), ; nrfact NUMBER(8), ; transa NUMBER(16) ; NOT NULL, ; PRIMARY KEY STR(codinc,8)+STR(nrfact,8) TAG primaru, ; FOREIGN KEY codinc TAG codinc REFERENCES incasari TAG codinc, ; FOREIGN KEY nrfact TAG nrfact REFERENCES facturi TAG nrfact ; ) ;

ÌQ 9)3 HVWH REOLJDWRULX V� VH LQGLFH H[SOLFit ce atribute pot avea valori nule, iar la

declararea cheilor primare compuse, atributele componente trebuie concatenate, lucru ce DWUDJH QHFHVLWDWHD FRQYHUVLHL FHORU GH DOW WLS �QXPHULFH� GDW� FDOHQGDULVWLF�� ORJLFH� vQ úLUXUL

de caractere. Declararea chHLORU SULPDUH� DOWHUQDWLYH úL VWU�LQH vQ 9)3 SUHVXSXQH FUHDUHD

DXWRPDW� D LQGHFúLORU GH WLS PRIMARY, CANDIDATE sau REGULAR. Prin clauza TAG se poate da indexului respectiv un nume la alegere.

'LQ S�FDWH� GHFODUDUHD FKHLORU VWU�LQH QX vQVHDPQ� úL LQVWLWXLUHD UHVWULF LHL UHIHUHQ LDOH�

$FHDVWD HVWH XQD GLQWUH FHOH PDL FLXGDWH ÄJ�VHOQL H´ 9)3� &D XUPDUH� GXS� FUHDUHD %'� ILH

trebuie „umblat” prin Referential Integrity BuilderúL� DVWIHO� JUDILF� LQVWLWXLWH UHJXOLOH SHQWUX

SUH]HUYDUHD UHIHUHQ LDOLW� LL �YH]L FDSLWROXO �� ILH WUHEXLH FUHDWH GHFODQúDWRDUH vQ DFHVW VFRS

(capitolul 6).

5.2.2. 0RGLILFDUHD VWUXFWXULL WDEHOHORU�UHVWULF LLORU vQ 9)3

6FKHPD XQHL ED]H GH GDWH UHSUH]LQW� DVSHFWXO FRQVWDQW� LQYDULDELO VDX� PDL ELQH ]LV� SX LQ

YDULDELO vQ WLPS� 2 EXQ� DQDOL]� úL SURLHFWDUH D DSOLFD LHL �VLVWHPXOXL� GLPLQXHD]� ULVFXO

PRGLILF�ULORU GH DPSORDUH vQ VWUXFWXUD WDEHOHORU úL UHVWULF LLORU� FRQIHULQG VWDELOLWDWH ED]HL úL

GLPLQXkQG VHQVLELO HIRUWXULOH GH vQWUH LQHUH XOWHULRDU� LQVWDO�ULL DSOLFD LHL� &X WRDWH DFHVWHD�

pe parcuUVXO XWLOL]�ULL ED]HL GH GDWH SRW DS�UHD VLWXD LL vQ FDUH HVWH QHFHVDU� DG�XJDUHD XQXL

QRX DWULEXW� úWHUJHUHD XQXL DWULEXW LQXWLO� PRGLILFDUHD OXQJLPLL HWF� Comanda pentru modificarea structurii unei tabele este ALTER TABLE� DOH F�UHL

RS LXQL PDL LPSRUWDQWe le vom parcurge în continuare. $G�XJDUHD XQXL QRX DWULEXW $G�XJDUHD DWULEXWXOXL DataNast �GDWD QDúWHULL� vQ WDEHOD 3(562$1( VH UHDOL]HD]�

astfel: ALTER TABLE PERSOANE ADD DataNast DATE

ùWHUJHUHD XQXL DWULEXW 'DF� QH SURSXQHP V� úWHUJHP DWULEXWXO SH FDUe tocmai l-DP DG�XJDW� WUHEXLH GRDU V�

folosim comanda: ALTER TABLE PERSOANE DROP COLUMN DataNast


Redenumirea unui atribut Spre deosebire chiar de SGBD-XUL FRQVDFUDWH� vQ 9)3 XQ DWULEXW VH SRDWH úL UHGHQXPL�

ALTER TABLE PERSOANE RENAME COLUMN DataNast TO DataNasterii Modificarea tipului/ lungimii unui atribut 'DF� VH GRUHúWH FUHúWHUHD OD �� D QXP�UXOXL GH FDUDFWHUH DORFDWH YDORULORU DWULEXWXOXL

Prenume úL UHGXFHUHD OD �� D OXQJLPLL DWULEXWXOXL Nume, vom utiliza: ALTER TABLE PERSOANE ALTER COLUMN Prenume CHAR(21) ALTER TABLE PERSOANE ALTER COLUMN Nume CHAR(17)

$G�XJDUHD�PRGLILFDUHD YDORULL LPSOLFLWH 'DF� GRULP FD YDORDUHD LPSOLFLW� D DWULEXWXOXL Sex V� ILH µF’ (deoarece sunt mai multe

IHPHL GHFkW E�UED L�� DFHVW OXFUX HVWH SRVLELO úL DVWIHO� ALTER TABLE PERSOANE ALTER COLUMN Sex SET DEFAULT 'F'

18// úL QH18// Interzicerea valorilor nule pentru atributul Sex VH UHDOL]HD]� DVWIHO�

ALTER TABLE PERSOANE ALTER COLUMN Sex NOT NULL Invers, pentru a permite valori NULLSHQWUX DFHODúL DWULEXW�

ALTER TABLE PERSOANE ALTER COLUMN Sex NULL $G�XJDUHD�DQXODUHD UHVWULF LLORU 7RDWH UHVWULF LLOH� FKHLH SULPDU� – PRIMARY KEY, unicitate – UNIQUE, referen LDO� –

FOREIGN KEY, de comportament – CHECK SRW IL GHFODUDWH XOWHULRU FUH�ULL WDEHOHL úL�

ELQHvQ HOHV� DQXODWH OD XQ Poment dat. Spre exemplu, formatul general al comenzii pentru dezactivarea cheii primare este: ALTER TABLE PERSOANE DROP PRIMARY KEY

Pentru reinstituirea cheii primare a tabelei PERSOANE comanda are forma:

ALTER TABLE PERSOANE ADD PRIMARY KEY (CNP) Analog, prin ADD úL DROP SRW IL LQVWLWXLWH�DQXODWH úL FHOHODOWH WLSXUL GH UHVWULF LL�

formatele generale ale comenzii ALTER TABLE fiind:

ALTER TABLE TableName1 ADD | ALTER [COLUMN] FieldName1 FieldType [(nFieldWidth [, nPrecision])] [NULL | NOT NULL] [CHECK lExpression1 [ERROR cMessageText1]] [DEFAULT eExpression1] [PRIMARY KEY | UNIQUE] [REFERENCES TableName2 [TAG TagName1]] [NOCPTRANS] [NOVALIDATE]

sau

136 Visual FoxPro

ALTER TABLE TableName1 ALTER [COLUMN] FieldName2 [NULL | NOT NULL] [SET DEFAULT eExpression2] [SET CHECK lExpression2 [ERROR cMessageText2]] [DROP DEFAULT] [DROP CHECK] [NOVALIDATE]

sau

ALTER TABLE TableName1 [DROP [COLUMN] FieldName3] [SET CHECK lExpression3 [ERROR cMessageText3]] [DROP CHECK] [ADD PRIMARY KEY eExpression3 TAG TagName2 [FOR lExpression4]] [DROP PRIMARY KEY] [ADD UNIQUE eExpression4 [TAG TagName3 [FOR lExpression5]]] [DROP UNIQUE TAG TagName4] [ADD FOREIGN KEY [eExpression5] TAG TagName4 [FOR lExpression6] REFERENCES TableName2 [TAG TagName5]] [DROP FOREIGN KEY TAG TagName6 [SAVE]] [RENAME COLUMN FieldName4 TO FieldName5] [NOVALIDATE]

Unul dintre elementeleGH QRXWDWH ID � GH IRUPDWXO FRPHQ]LL CREATE TABLE îl

UHSUH]LQW� DSDUL LD FODX]HL NOVALIDATE, SULQ SUH]HQ D F�UHLD VH SHUPLWH RSHUDUHD XQRU

PRGLILF�UL DOH VWUXFWXULL FKLDU GDF� DFHVWHD DU YLROD LQWHJULWDWHD GDWHORU GLQ ED]��

5.2.3. ùWHUJHUHD WDEHOHORU

Comanda deúWHUJHUH D XQHL WDEHOH GLQ ED]� HVWH DROP TABLE úL DUH R VLQWD[� H[WUHP

GH VLPSO�� '523 7$%/( WDEHO�

5.3. Comenzi de actualizare

1XFOHXO 64/ GLQ 9LVXDO )R[3UR SUH]LQW� WRDWH FHOH WUHL FRPHQ]L SHQWUX DFWXDOL]DUHD

FRQ LQXWXOXL XQHL WDEHOH� INSERT, UPDATE úL DELETE.

5.3.1. $G�XJDUHD XQHL OLQLL

&RPDQGD 64/ GH DG�XJDUH GH QRL OLQLL HVWH INSERT, care are, în modul de lucru cel PDL SX LQ SUHWHQ LRV� XUP�WRUXO IRUPDW�


,16(57 ,172 WDEHO� >�DWULEXW�� DWULEXW�� «��@ VALUES (valoare_atribut1, valoare_atribut2, ….) Ordinea valorilor din clauza VALUES WUHEXLH V� ILH LGHQWLF� FX FHD GHFODUDW� OD FUHDUHD

�VDX PRGLILFDUHD VWUXFWXULL� WDEHOHORU� 0RGLILFDUHD HVWH SRVLELO� QXPDL SULQ HQXPHUDUHD�

GXS� QXPHOH WDEHOHL� D DWULEXWHORU FH YRU SULPL YDORULOH VSHFLILFDWH� 'DF�� VSUH H[HPSOX� SHQWUX WDEHOHOH &/,(17, úL )$&785, DX IRVW LQFOXVH vQ FODX]D

VALUES PDL SX LQH YDORUL GHFkW DWULEXWHOH WDEHOHL� 9)3 YD JHQHUD R HURDUH� (VWH

obligatorie, în aceste cazuri, precizarea atributelor care vor primi valorile (vezi listingul �� ÌQ 2UDFOH úL '%�� UHVWXO� DGLF� FHOH QHVSHFLILFDWH� YRU DYHD� SH OLQLLOH UHVSHFWLYH� YDORUL

NULL� ÌQ 9)3� DFHVWHD VXQW FRPSOHWDWH FX YDORUL YLGH �IXQF LD EMPTY() întoarce .T., în timp ce ISNULL()– .F.).

5.3.2. ùWHUJHUHD OLQLLORU

&RPDQGD 64/ SHQWUX úWHUJHUHD XQHLD VDX PDL PXOtor linii dintr-R WDEHO� HVWH DELETE. Formatul general este: DELETE FROM nume-WDEHO� :+(5( SUHGLFDW

ÌQ WDEHO� YRU IL PDUFDWH SHQWUX úWHUJHUH WRDWH OLQLLOH FDUH vQGHSOLQHVF FRQGL LD VSHFLILFDW�

în predicatul din clauza WHERE� )LUHúWH� FkQG FODX]D WHERE liSVHúWH� YRU IL PDUFDWH SHQWUX

úWHUJHUH WRDWH OLQLLOH WDEHOHL�

Exemplul 1 6� VH HOLPLQH GLQ WDEHOD )$&785, WRDWH IDFWXULOH HPLVH GH JHVWLXQHD �� DELETE FROM FACTURI ; WHERE gestiune = ‘002’

Exemplul 2 6� VH HOLPLQH GLQ ED]D GH GDWH WRDWH MXGH HOH GLQ 0oldova. DELETE FROM JUDETE ; WHERE Regiune = ‘Moldova’

5.3.3. Popularea bazei de date prin comenzi SQL

)RORVLQG FRPHQ]LOH GH úWHUJHUH úL LQVHUDUH� vQ OLVWLQJXO �� HVWH SUH]HQWDW SURJUDPXO

SHQWUX SRSXODUHD ED]HL GH GDWH� /D vQFHSXW VH IRORVHVF FRPHQ]LOH GH úWHUJHre, pentru a SUHYHQL HYHQWXDOD GXSOLFDUH D vQUHJLVWU�ULORU�

Listing 5.2. Script Visual FoxPro de populare a tabelelor (cu valorile din capitolul 2)

* Program de populare a BAZEI DE DATE * * se deschide baze de date (in caz ca * nu e deja deschisa IF !DBUSED('vinzari') OPEN DATABASE vinzari EXCLUSIVE ENDIF

138 Visual FoxPro

DELETE FROM incasfact SELE incasfact ZAP DELETE FROM incasari SELE incasari ZAP DELETE FROM liniifact SELE liniifact ZAP DELETE FROM facturi SELE facturi ZAP DELETE FROM gestiuni SELE gestiuni ZAP DELETE FROM produse SELE produse ZAP DELETE FROM persclienti SELE persclienti ZAP DELETE FROM persoane SELE persoane ZAP DELETE FROM clienti SELE clienti ZAP DELETE FROM localitati SELE localitati ZAP DELETE FROM judete SELE judete ZAP INSERT INTO judete VALUES ('IS', 'Iasi', 'Moldova') INSERT INTO judete VALUES ('VN', 'Vrancea', 'Moldova') INSERT INTO judete VALUES ('NT', 'Neamt', 'Moldova') INSERT INTO judete VALUES ('SV', 'Suceava', 'Moldova') INSERT INTO judete VALUES ('VS', 'Vaslui', 'Moldova') INSERT INTO judete VALUES ('TM', 'Timis', 'Banat') INSERT INTO localitati VALUES ('6600', 'Iasi', 'IS') INSERT INTO localitati VALUES ('5725', 'Pascani', 'IS') INSERT INTO localitati VALUES ('6500', 'Vaslui', 'VS') INSERT INTO localitati VALUES ('5300', 'Focsani', 'VN') INSERT INTO localitati VALUES ('6400', 'Birlad', 'VS') INSERT INTO localitati VALUES ('5800', 'Suceava', 'SV') INSERT INTO localitati VALUES ('5550', 'Roman', 'NT') INSERT INTO localitati VALUES ('1900', 'Timisoara', 'TM')


INSERT INTO clienti VALUES (1001, 'Client 1 SRL', 'R1001', 'Tranzitiei, 13 bis', ; '6600', NULL) INSERT INTO clienti (codcl, dencl, codfiscal, codpost, telefon) ; VALUES (1002,'Client 2 SA', 'R1002', '6600', '032-212121') INSERT INTO clienti VALUES (1003, 'Client 3 SRL', 'R1003', 'Prosperitatii, 22', ; '6500','035-222222') INSERT INTO clienti (codcl, dencl, adresa, codpost) ; VALUES (1004, 'Client 4', 'Sapientei, 56', '5725') INSERT INTO clienti VALUES (1005, 'Client 5 SRL', 'R1005', NULL, ; '1900', '056-111111') INSERT INTO clienti VALUES (1006, 'Client 6 SA', 'R1006', 'Pacientei, 33', ; '5550', NULL) INSERT INTO clienti VALUES (1007, 'Client 7 SRL', 'R1007', 'Victoria Capitalismului, 2', ; '1900', '056-121212') INSERT INTO persoane VALUES ('CNP1', 'Ioan', 'Vasile', 'I.L.Caragiale, 22', 'B', ; '6600', '123456', '987654', '094222222', NULL) INSERT INTO persoane VALUES ('CNP2', 'Vasile', 'Ion', NULL, 'B', ; '6600', '234567', '876543', '094222223', '[email protected]') INSERT INTO persoane VALUES ('CNP3', 'Popovici', 'Ioana', 'V.Micle, Bl.I, Sc.B,Ap.2', 'F', ; '5725', '345678', NULL, '094222224', NULL) INSERT INTO persoane VALUES ('CNP4', 'Lazar', 'Caraion', 'M.Eminescu, 42', 'B', ; '6500', '456789', NULL, '094222225', NULL) INSERT INTO persoane VALUES ('CNP5', 'Iurea', 'Simion', 'I.Creanga, 44 bis', 'B', ; '6500', '567890', '543210', NULL, NULL) INSERT INTO persoane VALUES ('CNP6', 'Vasc', 'Simona', 'M.Eminescu, 13', 'F', ; '5725', NULL, '432109', '094222227', NULL) INSERT INTO persoane VALUES ('CNP7', 'Popa', 'Ioanid', 'I.Ion, Bl.H2, Sc.C, Ap.45', 'B', ; '1900', '789012', '321098', NULL, NULL) INSERT INTO persoane VALUES ('CNP8', 'Bogacs', 'Ildiko', 'I.V.Viteazu, 67', 'F', ; '5550', '890123', '210987', '094222229', NULL) INSERT INTO persoane VALUES ('CNP9', 'Ioan', 'Vasilica', 'Garii, Bl.B4, Sc.A, Ap.1', 'F', ; '1900', '901234', '109876', '094222230', NULL) INSERT INTO persoane VALUES ('CNP10', 'Tucaliuc', 'Simion', NULL, 'B', ; '6600', '222222', '212121', NULL, '[email protected]') INSERT INTO persoane VALUES ('CNP11', 'Vasiliu', 'Mihai', 'Stefan cel Mare, 34', 'B', ; '5800', '258965', '256985', '093555555', '[email protected]') INSERT INTO persoane VALUES ('CNP12', 'Ciubotaru', 'Toader', 'Carpati, 12', 'B', ; '5725', '454545', '254785', NULL, NULL) INSERT INTO persclienti VALUES ('CNP1', 1001, 'Director general') INSERT INTO persclienti VALUES ('CNP2', 1002, 'Director general') INSERT INTO persclienti VALUES ('CNP3', 1002, 'Sef aprovizionare') INSERT INTO persclienti VALUES ('CNP4', 1003, 'Sef aprovizionare') INSERT INTO persclienti VALUES ('CNP5', 1003, 'Director financiar') INSERT INTO persclienti VALUES ('CNP6', 1004, 'Director general') INSERT INTO persclienti VALUES ('CNP7', 1005, 'Sef aprovizionare') INSERT INTO persclienti VALUES ('CNP8', 1006, 'Director financiar') INSERT INTO persclienti VALUES ('CNP9', 1007, 'Sef aprovizionare') * cimpurile GENERAL nu accepta valori NULL INSERT INTO produse (codpr, denpr, um, grupa, proctva) VALUES (1, 'Produs 1','buc', 'Tigari', .19) INSERT INTO produse (codpr, denpr, um, grupa, proctva) VALUES (2, 'Produs 2','kg', 'Bere', 0. 19) INSERT INTO produse (codpr, denpr, um, grupa, proctva) VALUES (3, 'Produs 3','kg', 'Bere', 0. 19) INSERT INTO produse (codpr, denpr, um, grupa, proctva) VALUES (4, 'Produs 4','l', 'Dulciuri', . 19) INSERT INTO produse (codpr, denpr, um, grupa, proctva) VALUES ; (5, 'Produs 5','buc', 'Tigari', . 19)

140 Visual FoxPro

* popularea cu poze. Din pacate nu exista o comanda SQL in acest sens SELECT produse GO TOP nNrFisiereTIF = ADIR(aTIFuri, „*.tif”) IF nNrFisiereTIF > 0 FOR i = 1 to nNrFisiereTIF APPEND GENERAL Imagine FROM aTIFuri(i,1) SKIP ENDFOR ENDIF INSERT INTO gestiuni VALUES ('001', 'Depozit Pacurari','Sos. Pacurari, nr 145 bis', '6600', ; 'CNP10', '212121', '[email protected]') INSERT INTO gestiuni VALUES ('002', 'Depozit Suceava','Str. Lunetistului nr.33', '5800', ; 'CNP11', '256985', '[email protected]') INSERT INTO gestiuni VALUES ('003', 'Sectie Pascani','Bd. Trompetistului, 56', '5725', ; 'CNP12', '254785', '[email protected]') INSERT INTO facturi (nrfact, datafact, gestiune, codcl) VALUES (1111, {^2001/08/01}, '001', 1001) INSERT INTO facturi VALUES (1112, {^2001/08/01}, '001', 1005, 'Probleme cu transportul') INSERT INTO facturi (nrfact, datafact, gestiune, codcl) VALUES (1113, {^2001/08/01}, '001',1002) INSERT INTO facturi (nrfact, datafact, gestiune, codcl) VALUES (1114, {^2001/08/01}, '002', 1006) INSERT INTO facturi (nrfact, datafact, gestiune, codcl) VALUES (1115, {^2001/08/02},'002', 1001) INSERT INTO facturi VALUES (1116, {^2001/08/02}, '001', 1007, ; 'Pretul propus initial a fost modificat') INSERT INTO facturi VALUES (1117, {^2001/08/03}, '002', 1001, NULL) INSERT INTO facturi VALUES (1118, {^2001/08/04}, '003', 1001, NULL) INSERT INTO facturi VALUES (1119, {^2001/08/07}, '003', 1003, NULL) INSERT INTO facturi VALUES (1120, {^2001/08/07}, '003', 1001, NULL) INSERT INTO facturi VALUES (1121, {^2001/08/07}, '001', 1004, NULL) INSERT INTO facturi VALUES (1122, {^2001/08/07}, '003', 1005, NULL) INSERT INTO liniifact VALUES (1111, 1, 1, 50, 10000) INSERT INTO liniifact VALUES (1111, 2, 2, 75, 10500) INSERT INTO liniifact VALUES (1111, 3, 5, 500, 6500) INSERT INTO liniifact VALUES (1112, 1, 2, 80, 10300) INSERT INTO liniifact VALUES (1112, 2, 3, 40, 7500) INSERT INTO liniifact VALUES (1113, 1, 2, 100, 9750) INSERT INTO liniifact VALUES (1114, 1, 2, 70, 10700) INSERT INTO liniifact VALUES (1114, 2, 4, 30, 15800) INSERT INTO liniifact VALUES (1114, 3, 5, 700, 6400) INSERT INTO liniifact VALUES (1115, 1, 2, 150, 9250) INSERT INTO liniifact VALUES (1116, 1, 2, 125, 9300) INSERT INTO liniifact VALUES (1117, 1, 2, 100, 10000) INSERT INTO liniifact VALUES (1117, 2, 1, 100, 9500) INSERT INTO liniifact VALUES (1118, 1, 2, 30, 11000) INSERT INTO liniifact VALUES (1118, 2, 1, 150, 9300) INSERT INTO liniifact VALUES (1119, 1, 2, 35, 10900) INSERT INTO liniifact VALUES (1119, 2, 3, 40, 7000) INSERT INTO liniifact VALUES (1119, 3, 4, 50, 14000) INSERT INTO liniifact VALUES (1119, 4, 5, 750, 6300) INSERT INTO liniifact VALUES (1120, 1, 2, 80, 11200) INSERT INTO liniifact VALUES (1121, 1, 5, 550, 6400) INSERT INTO liniifact VALUES (1121, 2, 2, 100, 10500)


INSERT INTO incasari VALUES (1234, {^2001/08/15}, 'OP', '111', {^2000/08/10}) INSERT INTO incasari VALUES (1235, {^2001/08/15}, 'CHIT', '222', {^2000/08/15}) INSERT INTO incasari VALUES (1236, {^2001/08/16}, 'OP', '333', {^2000/08/09}) INSERT INTO incasari VALUES (1237, {^2001/08/17}, 'CEC', '444', {^2000/08/10}) INSERT INTO incasari VALUES (1238, {^2001/08/17}, 'OP', '555', {^2000/08/10}) INSERT INTO incasari VALUES (1239, {^2001/08/18}, 'OP', '666', {^2000/08/11}) INSERT INTO incasfact VALUES (1234, 1111, 5399625) INSERT INTO incasfact VALUES (1234, 1118, 1026375) INSERT INTO incasfact VALUES (1235, 1112, 487705) INSERT INTO incasfact VALUES (1236, 1117, 975410) INSERT INTO incasfact VALUES (1236, 1118, 1026375) INSERT INTO incasfact VALUES (1236, 1120, 731557) INSERT INTO incasfact VALUES (1237, 1117, 975410) INSERT INTO incasfact VALUES (1238, 1113, 1160250) INSERT INTO incasfact VALUES (1239, 1117, 369680)

$P SRUQLW GH OD SUHVXSXQHUHD F� vQ GLUHFWRUXO FXUHQW H[LVW� FkWH XQ ILúLHU JUDILF� FX

extensia .TIF pentru fiecare produs, iar ordinea acestora coincinde cu ordinea produselor GLQ WDEHO�� FHHD FH H GHVWXO GH ULVFDQW� $VWIHO vQFkW SURFHGXUD GLQ OLVWLQJXO �.3 este mai LQGLFDW��

/LVWLQJ �� 1RXD VHFYHQ � SHQWUX SRSXODUHD FX LPDJLQL D FkPSXULORU GH WLS General

SELECT produse SCAN nume_ = 'figura_'+LTRIM(STR(codpr,6))+'.TIF' IF FILE((nume_)) APPEND GENERAL Imagine FROM (nume_) LINK ENDIF ENDSCAN

5.3.4. Modificarea valorilor unor atribute

Pentru a modifica valoarea unuia sau mai multor atribute pe una sau mai multe linii dintr-R WDEHO� VH IRORVHúWH FRPDQGD UPDATE cu formatul general (simplificat): 83'$7( WDEHO� � SET atribut1 = expresie1 [, atribut2= expresie2 ….] ; WHERE predicat

0RGLILFDUHD VH YD SURGXFH SH WRDWH OLQLLOH WDEHOHL FDUH vQGHSOLQHVF FRQGL LD IRUPXODW�

prin predicat.

Exemplul 3 1RXO QXP�U GH WHOHIRQ DO FOLHQWXOXL FH DUH FRGXO �� HVWH ��-�� 6� VH RSHUH]H

modificarea în baza de date. 6H DFWXDOL]HD]� DWULEXWXO Telefon din tabela CLIENTI:

UPDATE CLIENTI ; SET Telefon = ‘032-313131’ ; WHERE CodCl = 1001

142 Visual FoxPro

Exemplul 4 ÌQ FDGUXO XQHL QRL UHOD[�UL ILVFDOH� VH GHFLGH FUHúWHUHD SURFHQWXOXL 79$ GH OD �� OD ��

pentru toate produsele. Atributul modificat este ProcTVA din tabela PRODUSE, pe toate liniile:

UPDATE PRODUSE ; SET ProcTVA = .22 ;

5.4. Interogarea bazelor de date – fraza SELECT

%D]D GH GDWH SUH]HQWDW� SH SDUFXUVXO FDSLWROXOXL � HVWH GRDU SX LQ GLIHULW� GH FHD GLQ

cartea 64/� 'LDOHFWH '%�� 2UDFOH úL 9LVXDO )R[3UR �3ROLURP� ,DúL� �� DúD vQFkW QX PDL

UHOX�P GHFkW FkWHYD H[HPSOH�

5.4.1. Sintaxa deED]�

Comanda SELECT UHVSHFW� vQ 9)3 VLQWD[D GH ED]� GLQ VWDQGDUGXO 64/-92, cele trei clauze principale fiind SELECT, FROM úL WHERE� OD FDUH VH DGDXJ� ORDER BY, GROUP BY, HAVING úL DOWH HOHPHQWH OHJDWH GH VXEFRQVXOW�UL� 'H DVHPHQHD� 9)3 IRORVHúWH QRWD LD

SQL-�� vQ PDWHULH GH UHSUH]HQWDUH D MRQF LXQLL LQWHUQH �INNER JOIN � úL H[WHUQH �OUTER JOIN �� &kWHYD H[HPSOH VXQW JU�LWRDUH vQ DFHVW VHQV�

Exemplul 1 3UHVXSXQkQG F� SODWD VH IDFH OD �� ]LOH� FDUH VXQW IDFWXULOH FH XUPHD]� D IL vQFDVDWH vQ

perioada 15-18 august 2001 ? • 6ROX LD ��

SELECT DISTINCT NrFact, DataFact, DataFact + 14 AS Scadenta ; FROM FACTURI ; WHERE DataFact + 14 >= {^2001/08/15} AND ;

DataFact + 14 <= {^2001/08/18} • 6ROX LD �� – folosind operatorul BETWEEN:

SELECT DISTINCT NrFact, DataFact, DataFact + 14 AS Scadenta ; FROM FACTURI ; WHERE DataFact + 14 BETWEEN {^2001/08/15} AND {^2001/08/18}

,QGLIHUHQW GH YDULDQW�� UH]XOWDWXO HVWH FHO GLQ ILJXUD ��


)LJXUD �� )DFWXULOH FH XUPHD]� D IL vQFDVDWH vQ SHULRDGD ��-18 august 2001

Exemplul 2 CarH VXQW FOLHQ LL GLQ RUDúHOH ,DúL úL 3DúFDQL "

• 6ROX LD ��

SELECT CLIENTI.* ; FROM CLIENTI INNER JOIN LOCALITATI ;

ON CLIENTI.CodPost = LOCALITATI.CodPost ; WHERE Loc=„Iasi” OR Loc=„Pascani”

• 6ROX LD �� – folosind operatorul IN :

SELECT CLIENTI.* ; FROM CLIENTI INNER JOIN LOCALITATI ;

ON CLIENTI.CodPost = LOCALITATI.CodPost ; WHERE Loc IN ('Iasi', 'Pascani')

• 6ROX LD �� – YHFKHD VLQWD[� GLQ 64/-89:

SELECT CLIENTI.* ; FROM CLIENTI, LOCALITATI ; WHERE CLIENTI.CodPost = LOCALITATI.CodPost AND ;

Loc IN ('Iasi', 'Pascani') • 6ROX LD �� – sintaxa SQL-89, cu aliasuri pentru tabele:

SELECT C.* ; FROM CLIENTI C, LOCALITATI L ; WHERE C.CodPost = L.CodPost AND ;

Loc IN ('Iasi', 'Pascani') • 6ROX LD �� – ED]DW� SH VXEFRQVXOW�UL�

SELECT * ; FROM CLIENTI ; WHERE CodPost IN ;

(SELECT CodPost ; FROM LOCALITATI ; WHERE Loc IN ('Iasi', 'Pascani') ; )

144 Visual FoxPro

Exemplul 3 Ce persoane-cheie de la firmele-client trebuie felicitate de Sf. Ion ?

• 6ROX LD �� – folosind operatorul LIKE :

SELECT dencl, functie, p.* ; FROM PERSOANE p INNER JOIN PERSCLIENTI pc ON p.CNP=pc.CNP ;

INNER JOIN CLIENTI c ON pc.CodCl=c.codcl ; WHERE UPPER(Prenume) LIKE 'ION%' OR ;

UPPER(Prenume) LIKE 'IOAN%' OR ; UPPER(Prenume) LIKE '% ION%' OR ; UPPER(Prenume) LIKE '% IOAN%' OR ; UPPER(Prenume) LIKE '%-ION%' OR ; UPPER(Prenume) LIKE '%-IOAN%'

Figura 5.2. VIP-urile de la firmele-FOLHQW SH FDUH WUHEXLH V� OH VDOXW�P GH 6I� ,RQ

Exemplul 4 Ce persoane-cheie de la firmele-client nu au cont de e-mail ?

• 6ROX LD �� – folosind operatorul IS NULL : SELECT dencl, functie, p.* ; FROM PERSOANE p INNER JOIN PERSCLIENTI pc ON p.CNP=pc.CNP ;

INNER JOIN CLIENTI c ON pc.CodCl=c.codcl ; WHERE email IS NULL

Exemplul 5 &DUH VXQW IDFWXULOH HPLVH vQ DFHHDúL ]L FD úL IDFWXUD �� "

• 6ROX LD �� – ED]DW� SH VXEFRQVXOWDUH�

SELECT * ; FROM FACTURI ; WHERE DataFact IN ;

(SELECT DataFact ; FROM FACTURI ; WHERE NrFact=1113) • 6ROX LD �� – ED]DW� SH MRQF LXQH�

SELECT F1.NrFact, F1.DataFact ; FROM FACTURI F1 INNER JOIN FACTURI F2 ;

ON F1.DataFact = F2.DataFact AND F2.NrFact = 1113


• 6ROX LD �� – IRORVLQG R LQWHURJDUH FRUHODW�� SELECT * ; FROM FACTURI F1 ; WHERE EXISTS ;

(SELECT 1 ; FROM FACTURI F2 ; WHERE F1.DataFact = F2.DataFact AND F2.NrFact=1113)

Exemplul 6 &DUH GLQWUH SHUVRDQHOH GLQ WDEHOD FX DFHODúL QXPH QX DX IXQF LL OD ILUPHOH-client ?

• 6ROX LD �� – SH ED]� GH VXEFRQVXOWDUH�

SELECT p.* ; FROM PERSOANE p ; WHERE CNP NOT IN ;

(SELECT CNP ; FROM PERSCLIENTI) • 6ROX LD �� – ED]DW� SH MRQF LXQH H[WHUQ��

SELECT * ; FROM PERSOANE p LEFT OUTER JOIN PERSCLIENTI pc ;

ON p.CNP=pc.CNP ; WHERE NVL(pc.CodCl,0) = 0

5.4.2. )XQF LL-DJUHJDW� FX úL I�U� JUXSDUe

Exemplul 7 /D Fk L FOLHQ L V-au trimis facturi ?

• 6ROX LD �� – R IXQF LH COUNT úL R VXEFRQVXOWDUH� SELECT COUNT (*) ; FROM CLIENTI ; WHERE CodCl IN ;

(SELECT CodCl ; FROM FACTURI) • 6ROX LD �� – clauza DISTINCT D XQHL IXQF LL COUNT:

SELECT COUNT (DISTINCT CodCl) ; FROM FACTURI

Exemplul 8 &DUH HVWH YDORDUHD WRWDO� D IDFWXULL �� YDORDUHD PHGLH� PD[LP� úL PLQLP� D SURGXVHORU

YkQGXWH SH DFHDVW� IDFWXU� " 6FRSXO H[HPSOXOXL HVWH GH D LOXVWUD PRGXO GH IRORVLUHD D IXQF LLORU-agregat SUM, AVG,

MIN, MAX. Rezultatul este prrezentat în figura 5.3.

SELECT SUM(Cantitate * PretUnit * ( 1 + ProcTVA) ; AS Val_Totala, ;

AVG(Cantitate * PretUnit + Cantitate * PretUnit * ProcTVA);

146 Visual FoxPro

AS Val_Medie, ; MIN(Cantitate * PretUnit + Cantitate * PretUnit * ProcTVA);

AS Val_Minima, ; MAX(Cantitate * PretUnit + Cantitate * PretUnit * ProcTVA);

AS Val_Maxima ; FROM PRODUSE P INNER JOIN LINIIFACT LF ON P.CodPr = LF.CodPr

)LJXUD �� 9DORULOH� WRWDO�� PHGLH� PLQLP� úL PD[LP� DOH IDFWXULL ��

Exemplul 9 &DUH HVWH YDORDUHD WRWDO� D YkQ]�ULORU SHQWUX ILHFDUH ]L vQ FDUH V-au emis facturi ?

3HQWUX UH]ROYDUHD SUREOHPHL HVWH REOLJDWRULH IRORVLUHD JUXS�ULL �ILJXUD �� SELECT DataFact, SUM(Cantitate * PretUnit * (1+ProcTVA));

AS ValTotala ; FROM LINIIFACT LF, PRODUSE P, FACTURI F ; WHERE LF.CodPr = P.CodPr AND LF.NrFact = F.NrFact ; GROUP BY DataFact

Figura 5.4. Folosirea clauzei GROUP BY

Exemplul 10 &DUH VXQW YkQ]�ULOH� FDQWLWDWLY úL YDORULF� SHQWUX ILHFDUH SURGXV " SELECT DenPr, UM, SUM(Cantitate) AS Cantitativ, ; SUM(Cantitate * PretUnit * (1+ProcTVA)) AS Valoric ; FROM LINIIFACT LF, PRODUSE P, FACTURI F ; WHERE P.CodPr = LF.CodPr AND LF.NrFact = F.NrFact ; GROUP BY DenPr, UM

Exemplul 11 6� VH RE LQ� VLWXD LD YkQ]�ULORU SH FOLHQ L úL ]LOH� DILúkQGX-se câte un subtotal la nivel de FOLHQW úL XQ WRWDO JHQHUDO�

9DULDQWD SURSXV� UHXQHúWH PXO LPHD IDFWXULORU SURSULX-]LVH� RE LQXW� GLQ SULPD IUD]�

SELECT, FX PXO LPHD VXEWRWalurilor la nivel de client (al doilea SELECT� úL FX R OLQLH FH

UHSUH]LQW� WRWDOXO JHQHUDO�


SELECT PADR(ALLT(DenCl),40) AS DenumireClient, ;

DataFact, ; SUM(Cantitate * PretUnit * (1+ProcTVA)) AS Vinzari ;

FROM LINIIFACT LF, PRODUSE P, FACTURI F, CLIENTI C ; WHERE P.CodPr = LF.CodPr AND LF.NrFact = F.NrFact ;

AND F.CodCl=C.CodCl ; GROUP BY DenCl, DataFact ;

UNION ; SELECT PADR(ALLT(DenCl)+'-Subtotal',40), ;

{ / / }, ; SUM(Cantitate * PretUnit * (1+ProcTVA)) ;

FROM LINIIFACT LF, PRODUSE P, FACTURI F, CLIENTI C ; WHERE P.CodPr = LF.CodPr AND LF.NrFact = F.NrFact ;

ND F.CodCl=C.CodCl ; GROUP BY DenCl ;

UNION ; SELECT CHR(255)+'TOTAL GENERAL' , ;

{ / / }, ; SUM(Cantitate * PretUnit * (1+ProcTVA)) ;

FROM LINIIFACT LF, PRODUSE P ; WHERE P.CodPr = LF.CodPr

Figura 5.5. Mini-OLAP în SQL-VFP

148 Visual FoxPro

Exemplul 12 Care sunt zilele în care s-DX vQWRFPLW FHO SX LQ WUHL IDFWXUL " SELECT DataFact, COUNT(*) AS Nr_Facturi ; FROM FACTURI ; GROUP BY DataFact ; HAVING COUNT(*) >= 3

(VWH R SULP� VLWXD LH vQ FDUH VH UHFXUJH OD RS LXQHD HAVING.

Figura 5.6. Zilele în care s-au întocmit minimum 3 facturi

Exemplul 13 În ce zile s-DX YkQGXW úL SURGXVXO FX GHQXPLUHD Ä3URGXV �´ úL FHO FX GHQXPLUHD

„Produs 2” ? 'HúL DUH XQ HQXQ EDQDO� UH]ROYDUHD DFHVWHL SUREOHPH RIHU� RSRUWXQLWDWHD XQRU LQWHURJ�UL

GLQWUH FHOH PDL VSXPRDVH� 9� SUH]HQW�P QXPDL FLQFL GLQWUH DFHVWHD� • 6ROX LD �� – MRQF LRQDUHD XQHL LQVWDQ H RE LQXW� SULQ MRQF LXQHD 352'86(-

LINIIFACT-FACTURI (în care DenPr = ’Produs 1 ¶� FX R DOW� LQVWDQ � D

aceleiDúL FRPELQD LL �vQ FDUH DenPr = ’Produs 2 ’): SELECT DISTINCT F1.DataFact ; FROM PRODUSE P1 ; INNER JOIN LINIIFACT LF1 ON P1.CodPr = LF1.CodPr ; INNER JOIN FACTURI F1 ON LF1.NrFact = F1.NrFact ; INNER JOIN FACTURI F2 ON F1.DataFact=F2.DataFact ; INNER JOIN LINIIFACT LF2 ON LF2.NrFact = F2.NrFact ; INNER JOIN PRODUSE P2 ON LF2.CodPr = P2.CodPr ; WHERE P1.DenPr = 'Produs 1' AND P2.DenPr = 'Produs 2' • 6ROX LD �� – folosind clauza HAVING úL IXQF LD COUNT:

SELECT DISTINCT DataFact ; FROM PRODUSE INNER JOIN LINIIFACT ;

ON PRODUSE.CodPr = LINIIFACT.CodPr ; INNER JOIN FACTURI ON LINIIFACT.Nrfact =FACTURI.NrFact ;

WHERE DenPr IN ('Produs 1', 'Produs 2') ; GROUP BY DataFact ; HAVING COUNT(DISTINCT LINIIFACT.CodPr) = 2


• 6ROX LD �� – VLPXOkQG LQWHUVHF LD SULQ VXEFRQVXOW�UL� SELECT DISTINCT DataFact ; FROM PRODUSE INNER JOIN LINIIFACT ;

ON PRODUSE.CodPr = LINIIFACT.CodPr ; INNER JOIN FACTURI ON LINIIFACT.Nrfact = FACTURI.NrFact ;

WHERE DenPr = 'Produs 1' AND DataFact IN ; (SELECT DISTINCT DataFact ; FROM PRODUSE ; INNER JOIN LINIIFACT ON ;

PRODUSE.CodPr = LINIIFACT.CodPr ; INNER JOIN FACTURI ON ;

LINIIFACT.Nrfact = FACTURI.NrFact ; WHERE DenPr = 'Produs 2') • 6ROX LD �� – SULQ FRUHODUHD D GRX� LQVWDQ H DOH MRQF LXQLL 352'86(-LINIIFACT-

FACTURI; prima con LQH OLQLLOH OHJDWH GH Produs 1, iar a doua de Produs 2: SELECT DISTINCT DataFact ; FROM PRODUSE P1, LINIIFACT LF1, FACTURI F1 ; WHERE P1.CodPr = LF1.CodPr AND ;

LF1.Nrfact = F1.NrFact AND DenPr = 'Produs 1' ; AND EXISTS ; (SELECT 1 ; FROM PRODUSE P2, LINIIFACT LF2, FACTURI F2 ; WHERE P2.CodPr = LF2.CodPr AND LF2.Nrfact = F2.NrFact ;

AND P2.DenPr = 'Produs 2' AND F2.DataFact=F1.DataFact) • 6ROX LD ��

SELECT DISTINCT DataFact ; FROM FACTURI ;

INNER JOIN LINIIFACT ON FACTURI.NrFact=LINIIFACT.NrFact ; INNER JOIN PRODUSE ON LINIIFACT.CodPr=PRODUSE.CodPr AND ; DenPr IN ('Produs 1','Produs 2') ;

WHERE DTOC(DataFact)+DTOC({//}) NOT IN ; (SELECT DISTINCT ;

DTOC(F1.DataFact)+DTOC(NVL(F2.DataFact,{//})) ; FROM FACTURI F1 INNER JOIN PRODUSE P1 ; ON P1.DenPr IN ('Produs 1','Produs 2') ; LEFT OUTER JOIN (LINIIFACT LF2 INNER JOIN FACTURI F2 ; ON LF2.NrFact=F2.NrFact) ; ON F1.DataFact=F2.DataFact AND P1.CodPr=LF2.CodPr)

150 Visual FoxPro

Exemplul 14 6� VH DILúH]H R VLWXD LH D IDFWXULORU HPLVH� SH JHVWLXQL� GH IRUPD FHOHL GLQ ILJXUD ��

Figura 5.7. Folosirea IIF -XULORU vQ LQWHURJ�UL

$ILúDUHD SH FRORDQH VHSDUDWH� SHQWUX FHOH WUHL JHVWLXQL� D IDFWXULORU QHFHVLW� IRORVLUHD XQHL

VHFYHQ H GH IF -uri imediate (IIF -uri): SELECT IIF(gestiune = '001', STR(F.NrFact,8), space(8)) ;

AS Gestiune_001, ; IIF(gestiune = '002', STR(F.NrFact,8), space(8)) ;

AS Gestiune_002, ; IIF(gestiune = '003', STR(F.NrFact,8), space(8)) ;

AS Gestiune_003, ; DataFact, SUM(Cantitate * PretUnit * (1+ProcTVA)) ;

AS ValTotala ; FROM FACTURI F ;

INNER JOIN LINIIFACT LF ON F.NrFact = LF.NrFact ; INNER JOIN PRODUSE P ON LF.CodPr = P.CodPr ;

GROUP BY F.NrFact

Exemplul 15 6FDGHQ D ILHF�UHL IDFWXUL HPLVH HVWH GH �� GH ]LOH� 'DF� vQV� GDWD-OLPLW� FDGH vQWU-o VkPE�W� VDX GXPLQLF�� DWXQFL VFDGHQ D VH PXW� vQ OXQHD XUP�WRDUH� &DUH VXQW ]LOHOH

VFDGHQWH vQ DFHVWH FRQGL LL " 9LVXDO )R[3UR SUH]LQW� IXQF LLOH CDOW (Character Day Of the Week� úL DOW (Day Of

the Week�� UH]XOWDWXO GLQ ILJXUD �� ILLQG RE LQXW GXS� FXP XUPHD]��

SELECT NrFact AS Factura, DataFact, ; DataFact + 20 AS Scadenta1, ; CDOW(DataFact+20) AS NumeZi1, ; IIF(DOW(DataFact+20)=6, ; DataFact+22, ; IIF(DOW(DataFact+20)=1, ; DataFact+21, ; DataFact+20) ) AS Scadenta, ;


CDOW(IIF(DOW(DataFact+20)=6, ; DataFact+22, ; IIF(DOW(DataFact+20)=1, ; DataFact+21, ; DataFact+20) ) ) AS Zi_Scadenta ;

FROM FACTURI

)LJXUD �� 'HWHUPLQDUHD VFDGHQ HL GH vQFDVDUH

Exemplul 16 &DUH VXQW YDORULOH IDFWXUDWH úL vQFDVDWH DOH ILHF�UHL IDFWXUL " 6ROX LD GH PDL MRV� FH RE LQH OLQLLOH GLQ ILJXUD �� UHXQHúWH IDFWXULOH FDUH DX P�FDU R WUDQú�

de încasare cu cele neîncasate deloc:

SELECT F. NrFact, ; SUM(Cantitate * PretUnit * (1+ProcTVA)) / ; COUNT(DISTINCT I.CodInc) AS Facturat, ; SUM(Transa) / MAX(LF.Linie) AS Incasat ;

FROM LINIIFACT LF, PRODUSE P, FACTURI F, INCASFACT I ; WHERE LF.CodPr = P.CodPr AND LF.NrFact = F.NrFact ;

AND F.NrFact=I.NrFact ; GROUP BY F.NrFact ;

UNION ; SELECT F. NrFact, ;

SUM(Cantitate * PretUnit * (1+ProcTVA)) AS Facturat, ; 0 AS Incasat ; FROM LINIIFACT LF, PRODUSE P, FACTURI F ; WHERE LF.CodPr = P.CodPr AND LF.NrFact = F.NrFact ; AND F.NrFact NOT IN ;

(SELECT NrFact ; FROM INCASFACT) ; GROUP BY F.NrFact

152 Visual FoxPro

)LJXUD �� 9DORULOH IDFWXUDWH úL vQFDVDWH DOH ILHF�UHL IDFWXUL

5.4.3. Clauza TOP. Operatorii ALL úL ANY (SOME)

Exemplul 17 &DUH VXQW FHOH PDL PDUL FLQFL SUH XUL XQLWDUH GH YkQ]DUH� SURGXVHOH úL IDFWXULOH vQ FDUH DSDU

FHOH FLQFL SUH XUL PD[LPH" În alte SGBD-XUL� DFHDVW� SUREOHP� DU FUHD GXUHUL GH FDS SOHQDUH� &ODX]D TOP din VFP

UHDOL]HD]� RUGRQDUHD úL H[WUDJHUHD FHORU FLQFL YDORUL – vezi figura 5.10.

SELECT TOP 5 NrFact, DenPr, PretUnit ; FROM LINIIFACT INNER JOIN PRODUSE ;

ON LINIIFACT.CodPr=PRODUSE.CodPr ; ORDER BY PretUnit DESC

Figura 5.10. Folosirea clauzei TOP

Exemplul 18 &DUH VXQW SURGXVHOH YkQGXWH OD SUH XUL XQLWDUH VXSHULRDUH RULF�UXL SUH XQLWDU OD FDUH D

fost vândut ‘Produs 1’ ? Unul dintre obiectivele acestui exemplu este de a prezenta modul de conexiune a unei FRQVXOW�UL FX VXEFRQVXOWDUHD VD SULQ RSHUDWRUXO ALL.


SELECT DISTINCT DenPr, PretUnit ; FROM PRODUSE P, LINIIFACT LF ; WHERE P.CodPr=LF.CodPr AND PretUnit > ALL ;

(SELECT DISTINCT PretUnit ; FROM PRODUSE P, LINIIFACT LF ; WHERE P.CodPr=LF.CodPr AND DenPr ='Produs 1')

Figura 5.11. Operatorul ALL

Exemplul 19 &DUH VXQW SURGXVHOH YkQGXWH OD SUH XUL XQLWDUH VXSHULRDUH P�FDU XQXL SUH XQLWDU DO

‘Produsului 1’ ? (VWH JHQXO GH VLWXD LL vQ FDUH VH IRORVHúWH SOME sau ANY (au acHHDúL IXQF LXQH��

SELECT DISTINCT DenPr, PretUnit ; FROM PRODUSE P INNER JOIN LINIIFACT LF ;

ON P.CodPr=LF.CodPr ; WHERE PretUnit > ANY ;

(SELECT DISTINCT PretUnit ; FROM PRODUSE P INNER JOIN LINIIFACT LF ; ON P.CodPr=LF.CodPr ;

WHERE DenPr ='Produs 1')

Figura 5.12. Operatorul ANY

154 Visual FoxPro

Exemplul 20 &DUH HVWH XOWLPD IDFWXU� vQWRFPLW� �IDFWXUD FHD PDL UHFHQW�� úL GDWD vQ FDUH D IRVW HPLV� "

ùL DFHDVWD HVWH R SUREOHP� FX PXOWH YDULDQWH GH UH]ROYDUH� • 6ROX LD ��

SELECT DataFact, NrFact AS UltimaFactura ; FROM FACTURI ; WHERE NrFact IN ;

(SELECT MAX(NrFact) ; FROM FACTURI) • 6ROX LD �� – în locul operatorului IN , operatorul de conexiune este semnul egal:

SELECT DataFact, NrFact AS UltimaFactura ; FROM FACTURI ; WHERE NrFact = ;

(SELECT MAX(NrFact) ; FROM FACTURI) • 6ROX LD �� – folosind operatorul ANY:

SELECT DataFact, NrFact AS UltimaFactura ; FROM FACTURI ; WHERE NrFact =ANY ;

(SELECT MAX(NrFact) ; FROM FACTURI) • 6ROX LD �� – operatorul ALL úL� vQ VXEFRQVXOWDUH� IXQF LD MAX:

SELECT DataFact, NrFact AS UltimaFactura ; FROM FACTURI ; WHERE NrFact =ALL ;

(SELECT MAX(NrFact) ; FROM FACTURI) • 6ROX LD �� – conexiune prin >= ALL:

SELECT DataFact, NrFact AS UltimaFactura ; FROM FACTURI ; WHERE NrFact >= ALL ;

(SELECT NrFact ; FROM FACTURI) ; • 6ROX ia 20.6 –VROX LH ÄWLSLF�´ VFP – FH IRORVHúWH FODX]D TOP DSOLFDW� JUXSXULORU�

SELECT TOP 1 DataFact, COUNT(*) AS Nr ; FROM FACTURI ; GROUP BY DataFact ; ORDER BY Nr DESC


Exemplul 21 &DUH HVWH JHVWLXQHD FX YkQ]�ULOH FHOH PDL EXQH " 'DF� vQ DOWH 6*%'-uri VXQW QHFHVDUH VXEFRQVXOW�UL vQ FODX]D FROM� vQ 9)3� DFHDúL FODX]�

TOP este salvatoare – figura 5.13:

SELECT TOP 1 Gestiune, SUM(Cantitate * PretUnit * ; (1+ProcTVA)) AS Vinzari_Gestiuni ;

FROM FACTURI F ; INNER JOIN LINIIFACT LF ON F.NrFact=LF.NrFact ; INNER JOIN PRODUSE P ON LF.CodPr=P.CodPr ;

GROUP BY Gestiune ; ORDER BY Vinzari_Gestiuni DESC

Figura 5.13. Gestiunea pentru care s-DX vQUHJLVWUDW FHOH PDL PDUL YkQ]�UL

5.5. �$FWXDOL]DUHD�GDWHORU�IRORVLQG��VXE�FRQVXOW�UL

9LVXDO )R[3UR SUH]LQW�� GH PDL ELQH GH úDVH DQL� FkWHYD DWXXUL FH FRQVWLWXLDX DOW�GDW�

apanajul lumii bune a SGBD-XULORU� SURFHGXULOH VWRFDWH úL� LPSOLFLW� GHFODQúDWRDUHOH� 'HYLQH

DVWIHO SRVLELO� LQWURGXFHUHD XQRU DWULEXWH UHGXQGDQWH� GDU FDUH P�UHVF VHQVLELO YLWH]D GH

OXFUX D DSOLFD LHL� 3URFHVXO VH QXPHúWH GHQRUPDOL]DUH úL IU�PkQW� OXPHD SURLHFWDQ LORU GH

ED]H GH GDWH �QX SH QRL� DXWRULL GH ID �� $FHVWH DWULEXWH SRW IL JHVWLRQDWH DXWRPDW úL IHULWH GH

DFFLGHQWH YRLWH VDX vQWkPSO�WRDUH� Exagerând poate (un pic) cu denormalizarea, introducem R VHULH GH DWULEXWH QRL� GXS�

FXP XUPHD]�� • în tabela LINIIFACT – atributul ValCuTVA � SHQWUX D HOLPLQD� OD RE LQHUHD

rapoartelor/informa LLORU vQ UDSRDUWH� MRQF LXQHD FX WDEHOD 352'86( �GLQ FDUH VH

preia procentul de TVA); • vQ WDEHOD )$&785, DG�XJ�P QX PDL SX LQ GH SDWUX DWULEXWH�

− 9DO7RWDO� – UHSUH]LQW� YDORDUHD WRWDO� �LQFOXVLY 79$� D IDFWXULL� − ValIncasata – HVWH YDORDUHD vQFDVDW� �SkQ� vQ SUH]HQW� GLQ FRQWUDYDORDUHD

facturii; − Reduceri – GH RELFHL SHQWUX SODWD UDSLG� D IDFWXULL� − 3HQDOLW� L – este opusul atributului anterior.

&RPHQ]LOH 64/ SULQ FDUH QH SXQHP vQ RSHU� LQWHQ LLOH VXQW�

ALTER TABLE LINIIFACT ADD ValCuTVA NUMERIC(16) ALTER TABLE FACTURI ADD ValTotala NUMERIC(16) ALTER TABLE FACTURI ADD ValIncasata NUMERIC(16)

156 Visual FoxPro

ALTER TABLE FACTURI ADD Reduceri NUMERIC (15) ALTER TABLE FACTURI ADD Penalizari NUMERIC (15)

'HRDUHFH DFHVWH PRGLILF�UL QX DX IRVW OXDWH vQ FDOFXO vQ PRPHQWXO FUH�ULL ED]HL�

atributele trebuie „umplute” din mers. Motiv de prezentare a comenzilor de actualizare într-R YDULDQW� PDL GH ILQH H – IRORVLQG VXEFRQVXOW�UL� /XFUXO DFHVWD QX H SHUPLV vQF� GH

comanda INSERT, în care valorile nu pot fi specificate printr-un SELECT (spre deosebire de SQL-92).

În schimb, comanda DELETE HVWH GHVWXO GH WROHUDQW� vQ DFHVW VHQV� 6SUH H[HPSOX� VH

GRUHúWH úWHUJHUHD WXWXURU OLQLLORU GLQ WDEHOD )$&785, FDUH QX DX FRSLL vQ /,1,,)$&7 �DOWIHO VSXV� IDFWXULOH I�U� QLFL XQ SURGXV�VHUYLFLX�� 2 VROX LH HOHJDQW� HVWH XUP�WRDUHD� DELETE FROM FACTURI ; WHERE NrFact NOT IN ;

(SELECT DISTINCT NrFact ; FROM LINIIFACT ) )LUHúWH� VXEFRQVXOWDUHD SRDWH IL úL FRUHODW��

DELETE FROM FACTURI ; WHERE NOT EXISTS ;

(SELECT 1 ; FROM LINIIFACT ; WHERE LINIIFACT.NrFact = FACTURI.NrFact) ; 6� VH HOLPLQH GLQ WDEHOD )$&785, WRDWH OLQLLOH FDUH VH UHIHU� OD IDFWXUL FX YDORDUH VXE �

milioane lei. 5H]ROYDUHD SUREOHPHL SUHVXSXQH IRORVLUHD XQHL VXEFRQVXOW�UL FX JUXSDUH� DELETE FROM FACTURI ; WHERE NrFact NOT IN ;

(SELECT NrFact ; FROM LINIIFACT LF INNER JOIN PRODUSE P ;

ON LF.CodPr=P.CodPr ; GROUP BY NrFact ; HAVING SUM(Cantitate*PretUnit*(1+ProcTVA)) >= 5000000 ; ) 3HQWUX DFWXDOL]DUHD DWULEXWHORU LQWURGXVH vQ DFHVW SDUDJUDI� QH LQWHUHVHD]�� vQ SULPXO UkQG�

comanda UPDATE� 'DF� DP IL VLJXUL F� WRDWH SURGXVHOH�VHUYLFLLOH DU DYHD SURFHQWXO GH 79$

unic –úL DQXPH �� –, actualizarea atributului ValCuTVA GLQ /,1,,)$&7 DU IL XúRDU�� UPDATE LINIIFACT ;

SET ValCuTVA = Cantitate * PretUnit * 1.19 6FKHPD ED]HL HVWH XQD SH WHUPHQ OXQJ vQV�� ÌQ IXQF LH GH OHJLVOD LD vQ YLJRDUH� OD XQ

moment dat, produsele pot avea procente diferite de TVA, în fXQF LH GH FDWHJRULD GLQ FDUHIDF SDUWH �GH VWULFW� QHFHVLWDWH� GH OX[ HWF�� 'H DFHHD� OD FDOFXOXO YDORULL FX 79$ HVWHREOLJDWRULH SUHOXDUHD SURFHQWXOXL 79$ GLQ WDEHOD 352'86(� 'LQ S�FDWH� YDULDQWD FDUHne-ar trebui:


UPDATE LINIIFACT ; SET ValCuTVA = ;

(SELECT Cantitate * PretUnit * (1+ProcTVA) ; FROM LINIIFACT LF INNER JOIN PRODUSE P ;

ON LF.CodPr=P.CodPr ; WHERE LF.NrFact = LINIIFACT.NrFact AND ;

P.CodPr = LINIIFACT.CodPr ; )

QX DUH GH JkQG V� IXQF LRQH]H vQ QLFL XQD GLQWUH YHUVLXQLOH 9)3� 'UHSW SHQWUu care suntem QHYRL L V� IRORVLP R VROX LH SURFHGXUDO�� FX VDX I�U� FXUVRDUH �YH]L SDUDJUDIXO XUP�WRU��Singurul loc în care UPDATE-ul poate prezenta o subconsultare este clauza WHERE. De H[HPSOX� GDF� VH GRUHúWH UHGXFHUHD SHQDOL]�ULORU SHQWUX WR L FOLHQ LL GLQ 3DúFDQL� VH SRDWHfolosi comanda:

UPDATE FACTURI ; SET Penalizari = Penalizari * .5 ; WHERE CodCl IN ;

(SELECT CodCl ; FROM CLIENTI INNER JOIN LOCALITATI ; ON CLIENTI.CodPost = LOCALITATI.CodPost ; WHERE Loc = 'Pascani')

5.6. Includerea frazelor SELECT în programe

0XOWH GLQWUH SUREOHPHOH FRPSOH[H GH LQWHURJDUH QHFHVLW� vQ 64/ IRORVLUHD PDL PXOWRUQLYHOXUL GH VXEFRQVXOWDUH VDX SUH]HQ D VXEFRQVXOW�ULORU vQ FODX]HOH FROM sau HAVING, IDFLOLW� L SH FDUH QXFOHXO 64/ GLQ 9LVXDO )R[3UR QX OH DUH vQF� LPSOHPentate. În schimb, SHQWUX DFHVWH FD]XUL VH SRW FUHD SURJUDPH vQ FDUH UH]XOWDWHOH LQWHUPHGLDUH DOH XQRU LQWHURJ�ULsunt stocate în cursoare sau tabele virtuale care, la rândul lor, pot constitui argumente ale XQRU QRL FRQVXOW�UL�

5.6.1. Salvarea rezultatulului unei fraze SELECT în cursor

&XUVRUXO HVWH� vQ 9LVXDO )R[3UR� R WDEHO� WHPSRUDU�� D F�UHL YLD � VH vQWLQGH GH ODmomentul unui SELECT FH SUH]LQW� FODX]D INTO CURSOR sau CREATE CURSOR SkQ� OD vQFKLGHUHD VD� LPSOLFLW� VDX H[SOLFLW��

Revenim la exemplul 13:

În ce zile s-DX YkQGXW úL SURGXVXO FX GHQXPLUHD Ä3URGXV �´, úL FHO FX GHQXPLUHD Ä3URGXV

2” ?) /D VHULD LQWHURJ�ULORU FDUH DX IRVW SUH]HQWDWH DG�XJ�P úL R YDULDQW� VLPSO� GH SURJUDP – listingul 5.4 –� FDUH VH ED]HD]� SH IRORVLUHD D GRX� FXUVRDUH� DVWIHO�

158 Visual FoxPro

ListLQJ �� 3URJUDP 9)3 FH VDOYHD]� UH]XOWDWHOH LQWHUPHGLDUH DOH LQWHURJ�ULORU vQ FXUVRDUH

SELECT DISTINCT DataFact ; INTO CURSOR Zile_P1 ; FROM PRODUSE INNER JOIN LINIIFACT ON PRODUSE.CodPr = LINIIFACT.CodPr ; INNER JOIN FACTURI ON LINIIFACT.Nrfact = FACTURI.NrFact ; WHERE DenPr = 'Produs 1' SELECT DISTINCT DataFact ; INTO CURSOR Zile_P2 ; FROM PRODUSE INNER JOIN LINIIFACT ON PRODUSE.CodPr = LINIIFACT.CodPr ; INNER JOIN FACTURI ON LINIIFACT.Nrfact = FACTURI.NrFact ; WHERE DenPr = 'Produs 2' SELECT * ; FROM Zile_P1 ; WHERE DataFact IN ; (SELECT DataFact ; FROM Zile_P2)

Prima interogare FUHHD]� FXUVRUXO Zile_P1 , FH FRQ LQH QXPDL ]LOHOH vQ FDUH V-a vândut

primul produs; al doilea cursor este populat cu zilele în care s-a vândut al doilea produs; XOWLPD LQWHURJDUH UHDOL]HD]� LQWHUVHF LD FHORU GRX� FXUVRDUH – figura 5.14.

)LJXUD �� &RQ LQXWXO FHORU GRX� FXUVRDUH úL UH]XOWDWXO LQWHURJ�ULL ILQDOH

Exemplul 22 &DUH HVWH MXGH XO FX YkQ]�UL LPHGLDW VXSHULRDUH MXGH XOXL 1HDP " 6ROX LD GLQ OLVWLQJXO �� DUH XQ GUDP VXSOLPHQWDU GH LQWHUHV� GHRDUHFH OD XOWLPD VD LQWHURJDUH�în afara clauzei WHERE, FDUH FRQ LQH GRX� VXEFRQVXOW�UL� VH HIHFWXHD]� WKHWD-MRQF LXQHDGLQWUH FHOH GRX� FXUVRDUH� cVinzari_Neamt úL cVinzari_Judete .


/LVWLQJ �� -XGH XO FX YkQ]�UL LPHGLDW SHVWH FHOH DOH MXGH XOXL 1HDP

* Vinzarile jud. Neamt SELECT SUM(Cantitate * PretUnit * (1+ProcTVA)) AS Vinzari ; FROM JUDETE J ; INNER JOIN LOCALITATI L ON J.Jud=L.Jud ; INNER JOIN CLIENTI C ON L.CodPost=C.CodPost ; INNER JOIN FACTURI F ON C.CodCl=F.CodCl ; INNER JOIN LINIIFACT LF ON F.NrFact=LF.NrFact ; INNER JOIN PRODUSE P ON LF.CodPr=P.CodPr ; INTO CURSOR cVinzari_Neamt ; WHERE Judet='Neamt' * Vinzarile fiecarui judet SELECT Judet, SUM(Cantitate * PretUnit * (1+ProcTVA)) AS Vinzari ; FROM JUDETE J ; INNER JOIN LOCALITATI L ON J.Jud=L.Jud ; INNER JOIN CLIENTI C ON L.CodPost=C.CodPost ; INNER JOIN FACTURI F ON C.CodCl=F.CodCl ; INNER JOIN LINIIFACT LF ON F.NrFact=LF.NrFact ; INNER JOIN PRODUSE P ON LF.CodPr=P.CodPr ; INTO CURSOR cVinzari_Judete ; GROUP BY Judet SELECT Judet, Vinzari ; FROM cVinzari_Judete ; WHERE Vinzari <= ALL ; (SELECT cVinzari_Judete.Vinzari ; FROM cVinzari_Judete, cVinzari_Neamt ; WHERE cVinzari_Judete.Vinzari > cVinzari_Neamt.Vinzari) ; AND Vinzari > ALL ; (SELECT Vinzari ; FROM cVinzari_Neamt)

Con LQXWXO FXUVRDUHORU úL DO UH]XOWDWXOXL ILQDO FRQVWLWXLH RELHFWXO ILJXULL ��

)LJXUD �� 'HWHUPLQDUHD MXGH XOXL FX YkQ]�UL LPHGLDW SHVWH FHOH DOH MXGH XOXL 1HDP

160 Visual FoxPro

Exemplul 22 Care este clientul cel mai mare datornic? /LVWLQJXO �� FRQ LQH SURJUDPXO FH U�VSXQGH OD DFHDVW� SUREOHP��

/LVWLQJ �� &OLHQWXO FX FHO PDL PDUH UHVW GH SODW�

* Valorile facturate, pe clienti SELECT CodCl, SUM(Cantitate * PretUnit * (1+ProcTVA)) AS Facturat ; FROM FACTURI F ; INNER JOIN LINIIFACT LF ON F.NrFact=LF.NrFact ; INNER JOIN PRODUSE P ON LF.CodPr=P.CodPr ; INTO CURSOR cFACTURAT ; GROUP BY CodCl * Valorile incasate, pe clienti SELECT CodCl, SUM(Transa) AS Incasat ; FROM FACTURI F ; INNER JOIN INCASFACT I ON F.NrFact=I.NrFact ; INTO CURSOR cINCASAT ; GROUP BY CodCl * Restul de plata, pe clienti SELECT cFACTURAT.CodCl, Facturat, NVL(Incasat,0) AS Incasat, ; Facturat - NVL(Incasat,0) AS De_Incasat ; FROM cFACTURAT LEFT OUTER JOIN cINCASAT ; ON cFACTURAT.CodCl=cINCASAT.CodCl ; INTO CURSOR cDe_INCASAT ; * Finalul apoteotic SELECT DenCl, cDe_INCASAT.* ; FROM cDe_INCASAT INNER JOIN CLIENTI ; ON cDe_INCASAT.CodCl=CLIENTI.CodCl ; WHERE De_Incasat >= ALL ; (SELECT De_Incasat ; FROM cDe_INCASAT)

5.6.2. 8WLOL]DUHD FXUVRDUHORU SHQWUX DFWXDOL]�UL

În paragraful anterior au fost introduse patru câmpuri calculate, dar nici unul nu a fost DGXV ÄOD ]L´� GLQ FDX]D OLPLW�ULORU FRPHQ]LL UPDATE vQ 9)3� 0RGDOLW� LOH vQ FDUH VH SRDWHUHGDFWD SURJUDPXO GH DFWXDOL]DUH D ILHF�UXLD GLQWUH DWULEXWH VXQW GHVWXO GH HWHURJHQH� GH ODVROX LL ÄFXUat xBase-LVWH´ OD VROX LL ÄSUHSRQGHUHQW 64/-LVWLFH´ �VFX]D L WHUPLQRORJLD �� ,DW�vQ OLVWLQJXO �� R YDULDQW� VLPSO� vQ FDUH YDORDUHD FX 79$ D ILHF�UHL OLQLL VH FDFXOHD]� úL VHVDOYHD]� vQ FXUVRUXO cVal � FXUVRU FDUH VH SDUFXUJH VHFYHQ LDO� SHQWUX ILHFDUH linie a sa operându-VH PRGLILFDUHD vQ vQUHJLVWUDUHD GLQ /,1,,)$&7 FRUHVSRQGHQW��

/LVWLQJ �� 3ULPD YDULDQW� GH DFWXDOL]DUH D DWULEXWXOXL ValCuTVA

* SELECT NrFact, LF.CodPr, Cantitate * PretUnit * (1+ProcTVA) AS ValCuTVA ; INTO CURSOR cVAL ; FROM LINIIFACT LF INNER JOIN PRODUSE P ON LF.CodPr=P.CodPr


SELECT cVal SCAN UPDATE LINIIFACT ; SET ValCuTVA = cVal.ValcuTVA ; WHERE NrFact = cVal.NrFact AND CodPr = cVal.CodPr ENDSCAN

3HQWUX FRPSDUD LH� vQ OLVWLQJXO �� HVWH SUH]HQWDW� R VROX LH FH FRPELQ� IRORVirea

cursorului cu indexul primar al tabelei LINIIFACT:

/LVWLQJ �� $ GRXD YDULDQW� GH DFWXDOL]DUH D DWULEXWXOXL ValCuTVA

* SELECT STR(NrFact,8)+STR(Linie,2) AS Cheie, Cantitate * PretUnit * (1+ProcTVA) AS ValCuTVA ; INTO CURSOR cVAL ; FROM LINIIFACT LF INNER JOIN PRODUSE P ON LF.CodPr=P.CodPr SELECT cVal SCAN SELECT LINIIFACT SEEK cVal.Cheie ORDER TAG primaru REPLACE ValCuTVA WITH cVal.ValCuTVA SELECT cVal ENDSCAN

LDU vQ OLVWLQJXO �� R YDULDQW� VWULFW [%DVH�

/LVWLQJ �� $ WUHLD YDULDQW� GH Dctualizare a atributului ValCuTVA

* IF !USED('LINIIFACT') USE LINIIFACT in 0 ENDIF IF !USED('PRODUSE') USE PRODUSE in 0 ENDIF SELECT LINIIFACT SCAN SELECT PRODUSE SEEK LINIIFACT.CodPr ORDER TAG CodPr SELECT LINIIFACT REPLACE ValCuTVA WITH Cantitate * PretUnit * (1+PRODUSE.ProcTVA) ENDSCAN

3URJUDPXO GLQ OLVWLQJXO �� HVWH FHO FDUH UHDOL]HD]� FDOFXOXO FHORU WUHL DWULEXWH�

LINIIFACT.ValCuTVA , FACTURI.ValTotala úL )$&785,�9DO,QFDVDW�. 3HQWUX D PDL FUHúWH IDFWRUXO LQWHUHV� YDORDUHD vQFDVDW� VH DFWXDOL]HD]� SULQ LQWHUPHGLXO XQXLtablou (masiv) – vIncasat .

162 Visual FoxPro

Listing 5.10. Totalul celor trei noi atribute

UPDATE facturi SET ValTotala = 0, ValIncasata = 0, Penalizari = 0, Reduceri = 0 UPDATE liniifact SET ValCuTva = 0 IF !USED('PRODUSE') USE PRODUSE IN 0 ENDIF SELECT LINIIFACT SCAN * se preia procentul TVA SELECT PRODUSE SEEK LINIIFACT.CodPr ORDER TAG CodPr * calculul si inlocuirea valorii cu TVA SELECT LINIIFACT REPLACE ValCuTVA WITH Cantitate * PretUnit * (1+PRODUSE.ProcTVA) * adaugarea valorii in tabela facturi SELECT FACTURI SEEK LINIIFACT.NrFact ORDER TAG NrFact REPLACE ValTotala WITH ValTotala + LINIIFACT.ValCuTVA SELECT LINIIFACT ENDSCAN * calculul valorii incasate pentru fiecare factura si salvarea intr-un masiv SELECT FACTURI.NrFact, SUM(NVL(Transa,0)) AS ValIncasata ; INTO ARRAY vIncasat ; FROM FACTURI LEFT OUTER JOIN INCASFACT ON FACTURI.NrFact=INCASFACT.NrFact ; GROUP BY FACTURI.NrFact * actualizarea valorii incasate in tabela FACTURI SELECT FACTURI SCAN pozitie = ASCAN (vIncasat, FACTURI.NrFact) REPLACE ValIncasata WITH vIncasat (pozitie + 1) ENDSCAN

5.6.3. $OWH FRQVLGHUD LL SULYLQG FXUVRDUHOH

8Q FXUVRU SRDWH IL FUHDW úL SULQ FRPDQGD CREATE CURSOR, caz în care tabela WHPSRUDU� HVWH DFWXDOL]DELO�� )RUPDWXO FRPHQ]LL SUH]LQW� FODX]H VLPLODUH CREATE TABLE:

CREATE CURSOR alias_name

(fname1 type [(precision [, scale]) [NULL | NOT NULL] [CHECK lExpression [ERROR cMessageText]] [DEFAULT eExpression] [UNIQUE] [NOCPTRANS]] [, fname2 ...]) | FROM ARRAY ArrayName


2 GDW� FUHDW úL GHFODUDWH UHVWULF LLOH� XQ FXUVRU SRDWH IL DFWXDOL]DW FD RULFH WDEHO�RELúQXLW�� SULQ FRPHQ]LOH 64/� INSERT, UPDATE úL DELETE� SUHFXP úL RULFH DOW�FRPDQG� GH HGLWDUH VSHFLILF� 9)3� 'H DVHPHQHD� RULFH vQF�OFDUH D YUHXQHL UHVWULF LL GHILQLWHYD IL VHPQDOL]DW�� 'XS� FXP YRP YHGHD vQ FDSLWROXO �� úL FXUVRDUHORr, similar tabelelor YLUWXDOH� OL VH SRW YL]XDOL]D úL FRQILJXUD SURSULHW� LOH SULQ IXQF LLOH CURSORGETPROP() úLCURSORSETPROP().

&RQ LQXWXO XQXL FXUVRU SRDWH IL DG�XJDW OD vQUHJLVWU�ULOH FXUHQWH DOH XQHL WDEHOH SULQcomanda APPEND FROM DBF(‘<NumeCursor>’) . $VWIHO� SXWHP UHGDFWD R YDULDQW�PDL VLPSO� GDU PDL SULPLWLY� GH FDOFXO DO YDORULORU FHORU WUHL DWULEXWH� 3ULQ IUD]H SELECT FRQVWUXLP FXUVRDUH FX DFHOHDúL VWUXFWXUL FD DOH WDEHOHORU )$&785, úL /,1,,)$&7� vQV� FXatributele ValCuTVA , ValTotala úL ValIncasata FDOFXODWH� $SRL úWHUJHP WRDWHOLQLLOH FHORU GRX� WDEHOH úL DG�XJ�P FRQ LQXWXO FXUVRDUHORU� 3URJUDPXO HVWH FHO GLQlistingul 5.11.

/LVWLQJ �� 2 DOW� YDULDQW� SHQWUX FDOFXOXO YDORULORU FHORU WUHL QRL DWULEXWH

* folosirea APPEND FROM DBF(<cursor>) * se construieste un cursor cu structura identica LINIIFACT * si cu toate atributele completate/calculate corect SELECT NrFact, Linie, LF.Codpr, Cantitate, ; PretUnit, Cantitate * PretUnit * (1+ProcTVA) AS ValCuTVA ; INTO CURSOR cLINIIFACT ; FROM LINIIFACT LF INNER JOIN PRODUSE P ON LF.CodPr=P.CodPr * se goleste tabela LINIIFACT si apoi se preia continutul cursorului DELETE FROM LINIIFACT SELECT LINIIFACT PACK APPEND FROM DBF('cLINIIFACT') * analog pentru tabela FACTURI SELECT FACTURI.NrFact, DataFact, Gestiune, CodCl, Obs, ; SUM(NVL(ValCuTVA,0)) AS ValTotala, ; Reduceri, Penalizari, ; SUM(NVL(Transa,0)) AS ValIncasata ; INTO CURSOR cFacturi ; FROM FACTURI ; LEFT OUTER JOIN LINIIFACT ON FACTURI.NrFact = LINIIFACT.NrFact ; LEFT OUTER JOIN INCASFACT ON FACTURI.NrFact=INCASFACT.NrFact ; GROUP BY FACTURI.NrFact DELETE FROM FACTURI SELECT FACTURI PACK APPEND FROM DBF('cFACTURI')

Cursoarele pot fi, în unele situa LL� XQ EXQ VXEVWLWXW DO YHFWRULORU� 6� OX�P XQ DVHPHQHD

FD]� FHO DO FDOFXO�ULL UHGXFHULORU SHQWUX SODWD UDSLG� D IDFWXULORU� 6� SUHVXSXQHP F�� SHQWUXFOLHQ LL EXQL SODWQLFL� VH DFRUG� UHGXFHUL OD SODW�� • SHQWUX WUDQúHOH GH IDFWXUL SO�WLWH �INCASARI.DataInc � vQ WHUPHQ GH SkQ� OD �� ]LOH�

DFRUG�P R UHGXFHUH GH ��

164 Visual FoxPro

• vQWUH �� úL �� ]LOH SURFHQWXO HVWH GH �� • vQWUH �� úL �� ]LOH – 8%; • peste 16 zile –QX VH DFRUG� UHGXFHUL �HYHQWXDO VH FDOFXOHD]� SHQDOL]�UL� GDU DVWD-L DOW�

poveste). 7UDQúHOH GH DFRUGDUH D UHGXFHULORU SRW IL VWRFDWH vQ FXUVRDUH úL� DVWIHO� IRORVLWH vQ

LQWHURJ�UL� ÌQ OLVWLQJXO �� HVWH SUH]HQWDW SURJUDPXO FDUH UHDOL]HD]� FDOFXOXO UHGXFHULORU úLactualizarea atributului FACTURI.Reduceri .

/LVWLQJ �� &DOFXOXO UHGXFHULORU DFRUGDWH SHQWUX vQFDV�UL

* se creaza si populeaza cursorul cu transele de reduceri CREATE CURSOR transe_red ( ; zile_min INTEGER, ; zile_max INTEGER, ; procent INTEGER ; ) INSERT INTO transe_red VALUES ( 0, 10, 10) INSERT INTO transe_red VALUES (11, 12, 9) INSERT INTO transe_red VALUES (13, 15, 8) INSERT INTO transe_red VALUES (16, 99999, 0) * pentru fiecare factura cu valoare peste zero se insereaza * in cursrul FACT_TRANSE cite o linie pentru fiecare transa * posibila de reduceri SELECT NrFact, DataFact, ValTotala, DataFact + zile_min AS LimInf, ; DataFact + zile_max AS LimSup, procent ; FROM FACTURI, TRANSE_RED ; INTO CURSOR FACT_TRANSE ; WHERE procent > 0 AND ValTotala > 0 * se calculeaza reducerile pe fiecare transa SELECT FACT_TRANSE.*, DataInc, Transa, INT(Procent * Transa / 100) AS Reducere ; INTO CURSOR reduceri_transe ; FROM INCASFACT INNER JOIN INCASARI ON INCASFACT.CodInc=INCASARI.CodInc ; INNER JOIN FACT_TRANSE ; ON INCASFACT.NrFact = FACT_TRANSE.NrFact ; WHERE INCASARI.DataInc >= FACT_TRANSE.LimInf AND ; INCASARI.DataInc <= FACT_TRANSE.LimSup * se actualizeaza atributul FACTURI.Reduceri DIME suma_ (1,1) SELECT facturi SCAN suma_ = 0 SELECT SUM(Reducere) ; INTO ARRAY suma_ ; FROM REDUCERI_TRANSE ; WHERE NrFact = facturi.Nrfact SELECT facturi REPLACE Reduceri WITH suma_(1,1) ENDSCAN


6ROX LD H GHVWXO GH VFXPS�� GDU RSHUD LRQDO�� 7UDQúHOH GH UHGXFHUL FRQVWLWXLH RELHFWXOcursorului actualizabil TRANSE_RED, actualizabil automat deoarece a fost creat prin comanda CREATE CURSOR úL QX SULQWU-un SELECT.

3ULPD IUD]� SELECT constituie unul dintUH SX LQHOH H[HPSOH GH IRORVLUH D SURGXVXOXLcartezian. Cursorul (Read-Only) FACT_TRANSE FRQ LQH� SHQWUX ILHFDUH IDFWXU� FXvaloare mai mare decât zero, toate intervalele calendaristice pentru care se pot acorda UHGXFHUL� �LQkQG FRQW GH OLQLLOH WDEHOHORU GLQ ED]D GH GDWH� LDW� vQ ILJXUD �� FRQ LQXWXOcursorului.

Figura 5.16. Cursorul FACT_TRANSE

$ GRXD IUD]� SELECTGHWHUPLQ� UHGXFHULOH HIHFWLYH� SH WUDQúH� FRQ LQXWXO FXUVRUXOXLcreat – REDUCERI_TRANSE – fiind prezentat în figura 5.17.

166 Visual FoxPro

Figura 5.17. Cursorul REDUCERI_TRANSE

ÌQ ILQDO� VH vQVXPHD]� WRDWH UHGXFHULOH SH IDFWXUL úL VH DFWXDOL]HD]� DWULEXWXOFACTURI.Reduceri .

9DULDQWD SUH]HQWDW� HVWH XQD H[HPSOLILFDWRDUH �VH REVHUY� F�� SHQWUX FkWHYD IDFWXUL�YDORDUHD vQFDVDW� SOXV UHGXFHULOH VXQW SHVWH YDORDUHD IDFWXULL�� GDU SRDWH IL DGDSWDW� ODSUDFWLFD URPkQHDVF��

5.7. �9DULDELOH�XWLOL]DWH�vQ�FRQVXOW�UL�� 0DFURVXEVWLWX LH�úL�64/

8WLOL]DUHD YDULDELOHORU vQ LQWHURJ�ULOH 64/ HVWH XWLO� PDL DOHV DWXQFL FkQG UDSRDUWHOH VDXinforma LLOH WUHEXLH ILOWUDWH FDOHndaristic, geografic etc.

5.7.1. 9DULDELOH SHQWUX ILOWUDUHD LQWHURJ�ULORU

6� vQFHSHP FX XQ H[HPSOX VLPSOX� ÌQWU-R DSOLFD LH DYHP GH OLVWDW IDFWXULOH HPLVH F�WUH XQ

client pentru un anumit interval de timp definit printr-R GDW� LQL LDO� úL R GDW� ILQDO�� 3HQWUX

aceasta se poate redacta un program l5_13.prg , care preia cei trei parametri (codul FOLHQWXOXL� GDWHOH LQL LDO� úL ILQDO� DOH LQWHUYDOXOXL FDOHQGDULVWLF� – listing 5.13.

Listing 5.13. Programul L5_13.PRG

PARAMETER codcl_, datai_, dataf_ SELECT DenCl AS Client, Nrfact, DataFact, Gestiune, ValTotala AS Valoare ; FROM CLIENTI C LEFT OUTER JOIN FACTURI F ON C.CodCl = F.CodCl ; WHERE F.CodCl = codcl_ AND Datafact BETWEEN datai_ AND dataf_

3HQWUX D RE LQH OLVWD IDFWXULORU HPLVH FOLHQWXOXL � �FRG �� vQ SHULRDGa 2-7 august

�� VH DSHOHD]� SURJUDPXO GH PDL VXV SULQ FRPDQGD� DO l5_13 WITH 1001, {^2001/08/02}, {^2001/08/07}

&X YDULDQWD GLQ OLVWLQJXO �� VH SRDWH DPHOLRUD DILúDUHD SH HFUDQ�


/LVWLQJ �� 2 DILúDUH XQ SLF PDL SO�FXW�

PARAMETER codcl_, datai_, dataf_ SELECT DenCl AS Client, Nrfact, DataFact, Gestiune, ValTotala AS Valoare ; INTO CURSOR c1 ; FROM CLIENTI C LEFT OUTER JOIN FACTURI F ON C.CodCl = F.CodCl ; WHERE F.CodCl = codcl_ AND Datafact BETWEEN datai_ AND dataf_ titlu_ = 'Lista facturilor pentru clientul '+ALLTRIM(c1.Client)+' in perioada '+; DTOC(datai_)+' - '+DTOC(dataf_) SELECT c1 BROWSE TITLE titlu_ USE

/D H[HFX LD FRPHQ]LL

DO l5_14 WITH 1001, {^2001/08/02}, {^2001/08/07} VH YD DILúD R IHUHDVWU� BROWSE de forma celei din figura 5.18.

)LJXUD �� ,QWHURJDUH SDUDPHWUL]DW� úL WLWOX GLQDPLF SHQWUX IHUHDVWUD BROWSE

6H PDL FXYLQH GH DG�XJDW F�� vQ DIDUD ILOWU�ULL úL GHQXPLULL ÄGLQDPLFH´ D IHUHVWUHL GH

YL]XDOL]DUH D UH]XOWDWHORU LQWHURJ�ULL� XOWLPXO SURJUDP ÄODV� FXUDW´ vQ XUPD VD� vQFKL]kQG

FXUVRUXO OD LHúLUHD GLQ BROWSE.

5.7.2. 0DFURVXEVWLWX LD úL IUD]H SELECT

6� SUHVXSXQHP F� R DFHHDúL OLVW� – a facturilor ce con LQH XUP�WRDUHOH GDWH� • QXP�UXO IDFWXULL� • data emiterii, • numele clientului, • localitatea în care-úL DUH VHGLXO FOLHQWXO� • YDORDUHD vQFDVDW� SkQ� vQ PRPHQWXO FXUHQW –

WUHEXLH RE LQXW� SHQWUX XQ DQXPLW LQWHUYDO FDOHQGDULVWLF� vQ RULFH RUGLQH GHFODUDW� SULQ toate FRPELQD LLOH SRVLELOH DOH FHORU FLQFL FkPSXUL� /LVWLQJXO �� FRQ LQH R SULP� YDULDQW� GH

program.

/LVWLQJ �� 3ULPD YDULQDW� GH RUGRQDUH GLQDPLF�

PARAMETER datai_, dataf_, cimp1_, cimp2_, cimp3_, cimp4_ SELECT DenCl AS Client, Loc AS Localitate, Nrfact, DataFact, Gestiune, ValTotala AS Valoare ; FROM CLIENTI C LEFT OUTER JOIN FACTURI F ON C.CodCl = F.CodCl ;

168 Visual FoxPro

INNER JOIN LOCALITATI L ON C.CodPost = L.CodPost ; WHERE Datafact BETWEEN datai_ AND dataf_ ; ORDER BY &cimp1_, &cimp2_, &cimp3_, &cimp4_

Clauza ORDER BY VH FRQVWUXLHúWH GLQDPLF FX DMXWRUXO PDFURVXEVWLWX LHL� ,QYRFDUHD

DFHVWXL SURJUDP VH IDFH FX R FRPDQG� DO de genul: DO l5_15 WITH {^2001/08/02}, {^2001/08/07}, 'Loc', 'DataFact', 'DenCl', 'ValTotala'

ÌQ FRQWLQXDUH Y� SURSXQHP R YDULDQW� PDL HOHJDQW�� &H-aU IL FD� vQ DIDUD FHORU GRX� GDWH

FDOHQGDULVWLFH� GH vQFHSXW úL GH VIkUúLW� RUGRQDUHD V� ILH SUHFL]DW� SULQWU-R OLVW� FDUH V�

FRQ LQ� XQXO� GRX�� WUHL« VDX FKLDU QLFL XQ DWULEXW" 3URJUDPXO GLQ OLVWLQJXO �� SUHLD XQ

SDUDPHWUX GH WLS úLU GH FDUDFWHUH FDUH este, de fapt, lista atributelor de ordonare, atribute VHSDUDWH SULQ YLUJXO�� $úD vQFkW SURJUDPXO DQDOL]HD]� YLUJXOHOH GLQ úLU úL H[WUDJH DWULEXWHOH

SH ED]D F�URUD VH YD FRQVWUXL� GLQDPLF� FODX]D WHERE.

/LVWLQJ �� 6H SUHLD QXPDL GDWD LQL LDO� úL ILQDO�, plus ordinea de prezentare, VXE IRUP� GH OLVW� GH DWULEXWH VHSDUDWH SULQ YLUJXO�

PARAMETER datai_, dataf_, ordinea_ i = 1 * se determina prima pozitie a virgulei (asta inseamna cu sunt macar * doua atribute de ordonare) poz_ = AT (',', ordinea_, i) IF poz_ > 0 && exista macar o virgula, deci sunt mai multe atribute de ordonare * se localizeaza toate virgulele si se extrag atributele cuprinse intre virgule poz_preced = 0 DO WHILE poz_ # 0 ii = str(i,1) cimp&ii = SUBSTR(ordinea_, poz_preced + 1, poz_ - poz_preced - 1) poz_preced = poz_ i = i + 1 poz_ = AT (',', ordinea_, i) ENDDO * a mai ramas atributul dintre ultima virgula si sfirsitul sirului de ordonare ii = str(i,1) cimp&ii = SUBSTR(ordinea_, poz_preced + 1, LEN(ordinea_) - poz_preced) * se construieste clauza ORDER BY order_by_ = ' ' FOR j = 1 TO i jj = STR(j,1) IF j > 1 order_by_ = order_by_ + ', ' ENDIF order_by_ = order_by_ + ALLTRIM(cimp&jj) ENDFOR ELSE && exista maximum un atribut de ordonare


IF LEN(ALLT(ordinea_)) = 0 && de fapt, nu exista nici un atribut de ordonare order_by_ = ' 1 ' && ordonarea se face dupa prima coloana ELSE order_by_ = ALLTRIM(ordinea_) && exista un singur atribut de ordonare ENDIF ENDIF * si acum, interogarea ! SELECT DenCl AS Client, Loc AS Localitate, Nrfact, DataFact, Gestiune, ValTotala AS Valoare ; FROM CLIENTI C LEFT OUTER JOIN FACTURI F ON C.CodCl = F.CodCl ; INNER JOIN LOCALITATI L ON C.CodPost = L.CodPost ; WHERE Datafact BETWEEN datai_ AND dataf_ ; ORDER BY &order_by_

IDW� úL FkWHYD PRGXUL GH DSHO D SURJUDPXOXL�

DO l5_16 WITH {^2000/08/02}, {^2001/08/20}, 'valtotala, dencl, nrfact'

DO l5_16 WITH {^2000/08/02}, {^2001/08/20}, 'nrfact' DO l5_16 WITH {^2000/08/02}, {^2001/08/20}, 'valtotala,

nrfact'

DO l5_16 WITH {^2000/08/02}, {^2001/08/20}, ''

5.7.3. Calculul automat al valorilor unor atribute agregate

3UDFWLFD GH]YROW�ULL DSOLFD LLORU GH OXFUX FX ED]H GH GDWH D FRQVDFUDW VLQWDJPD denorma-lizare. Frecvent, în tabele sunt incluse atribute redundante, calculate prin însumarea valorilor unor atribute din tabelele-FRSLO� )RU kQG XQ SLF OXFUXULOH� QH SURSXQHP V�

FRQVWUXLP R UXWLQ� JHQHUDOL]DW� F�UHLD V�-i trimitem ca parametri numele tabelelor-S�ULQWH úL

FRSLO� QXPHOH DWULEXWXOXL GLQ S�ULQWH FH WUHEXLH DFWXDOL]DW� QXPHOH DWULEXWXOui din copil pe ED]D F�UXLD VH IDFH DFWXDOL]DUHD� SUHFXP úL DWULEXWXO-cheie al tabelei-S�ULQWH�

/LVWLQJ �� *HQHUDOL]DUHD DFWXDOL]�ULL YDORULORU XQXL FkPS FDOFXODW

* PARAMETER parinte_, cheie_parinte_, cimp_parinte_, copil_, cheie_copil_, cimp_copil_ * se zerorizeaza cimpul ce urmeaza a fi calculat UPDATE (parinte_) SET &cimp_parinte_ = 0 * se declara masivul in care va fi preluata valoarea calculata DIME suma_ (1,1) SELECT (parinte_) SCAN suma_ = 0 * valoarea cheii pimare a liniei curente din tabela parinte val_cheie_parinte_ = &cheie_parinte_

170 Visual FoxPro

* insumarea liniilor-copil SELECT SUM(&cimp_copil_) INTO ARRAY suma_ FROM (copil_) ; WHERE &cheie_copil_ = val_cheie_parinte_ * inlocuirea valorii calculate in linia curenta a tabelei parinte SELECT (parinte_) REPLACE &cimp_parinte_ WITH suma_(1,1) ENDSCAN

Pentru calcularea valorilor atributului )$&785,�9DO7RWDO� pe baza liniilor-copil

din LINIIFACT, atributul-copil fiind LINIIFACT.ValCuTVA � VH LQYRF� SURJUDPXO GH

mai sus sub forma: do l5_17 with 'facturi', 'nrfact', 'valtotala', 'liniifact',

'nrfact', 'valcutva' ÌQVXPDUHD WUDQúHORU GH vQFDVDUH �INCASFACT.Transa � SHQWUX ILHFDUH IDFWXU� úL�

astfel, calcularea valorilor atributului FACTURI.ValIncasata se va face astfel: do l5_17 with 'facturi', 'nrfact', 'valincasata', 'incasfact', 'nrfact', 'transa'

5.7.4. 0DFURVXEVWLWX LH úL LWHUD LL 64/

7RW FX DMXWRUXO PDFURVXEVWLWX LHL VH SRW UH]ROYD VLWXD LL GLQWUH FHOH PDL GLILFLOH GH

RE LQHUH D XQRU UDSRDUWH SUHWHQ LRDVH� 6SUH H[HPSOLILFDUH� GRULP RE LQHUHD XQXL Uaport PDWULFHDO vQ FDUH� SH YHUWLFDO�� V� ILH SUH]HQWH WRDWH SURGXVHOH FRPHUFLDOL]DWH GH ILUP�� LDU

ÄRUGRQDWD´ V� ILH FRQVWLWXLW� GLQ SULPHOH SDWUX ]LOH DOH OXQLL DXJXVW �� – ca în figura 5.19.

)LJXUD �� 2 VLWXD LH PDWULFHDO�

Pentru facilitarea prezHQW�ULL� QH-am limitat la patru zile, dar programul din listingul 5.18 IXQF LRQHD]� SHQWUX RULFkWH ]LOH� VLQJXUD SUREOHP� ILLQG SRSXODUHD FXUVRUXOXL ZILE . În programul nostru, aceasta se face prin INSERT-XUL GLUHFWH� vQV� vQ DSOLFD LL GDWHOH LQL LDOH úL

finale ale intervalului se pot prelua printr-XQ IRUPXODU úL� DVWIHO� SRSXODUHD FXUVRUXOXL VH

automati]HD]��

/LVWLQJ �� 6LWXD LH PDWULFHDO� D YkQ]�ULL SURGXVHORU SH SULPHOH SDWUX ]LOH ale lunii august 2001

SET MARK TO '_' && separatorul ptr. data calendaristica CREATE CURSOR Zile (Zi DATE) INSERT INTO Zile VALUES ({^2001/08/01}) INSERT INTO Zile VALUES ({^2001/08/02})


INSERT INTO Zile VALUES ({^2001/08/03}) INSERT INTO Zile VALUES ({^2001/08/04}) SELECT DataFact, DenPr, LF.CodPr, SUM(INT(Cantitate * PretUnit * (1+ProcTVA))) AS Valoare ; INTO CURSOR Zile_Produse ; FROM FACTURI F INNER JOIN LINIIFACT LF ON F.NrFact = LF.NrFact ; INNER JOIN PRODUSE P ON LF.CodPr=P.CodPr ; WHERE DataFact IN ; (SELECT Zi ; FROM Zile) ; GROUP BY DataFact, DenPr, LF.CodPr SELECT DenPr, CodPr ; INTO CURSOR c1 ; FROM PRODUSE ; ORDER BY DenPr i = 1 SELECT Zile SCAN ii = ALLT(STR(i,2)) ii_plus_1 = ALLT(STR(i+1,2)) ziua_ = '_'+DTOC(Zile.Zi) SELECT c&ii..*, NVL(ZILE_PRODUSE.Valoare,0) AS Zi&ziua_ ; FROM c&ii LEFT OUTER JOIN ZILE_PRODUSE ; ON c&ii..CodPr = ZILE_PRODUSE.CodPr AND DataFact = Zile.Zi ; INTO CURSOR c&ii_plus_1 i = i + 1 SELECT Zile ENDSCAN SELECT c&ii_plus_1 BROWSE TITLE 'Vinzari - Produse/Zile' SELECT Zile USE SELECT Zile_Produse USE FOR j = 1 TO i ii = ALLT(STR(j,2)) SELECT c&ii USE ENDFOR

&kWHYD H[SOLFD LL Q-ar strica în acest moment. Cursorul ZILE FRQ LQH WRDWH GDWHOH

FDOHQGDULVWLFH FDUH WUHEXLH V� DSDU� GUHSW FRORDQH DOH UDSRUWXOXL� 9DORULOH YkQ]�ULORU SH

SURGXVH úL ]LOH VXQW FDOFXODWH úL VWRFDWH vQ FXUVRUXO ZILE_PRODUSE. Liniile raportului sunt DOF�WXLWH GLQ WRDWH SURGXVHOH ILUPHL� DVWIHO vQFkW FXUVRUXO ÄGH VWDUW´ – c1 – HVWH RE LQXW GLQ

tabela PRODUSE. 1XP�UXO LWHUD LLORU GHSLQGH GH QXP�UXO vQUHJLVW�ULORU GLQ FXUVRUXO ZILE – QXP�UXO

]LOHORU SHQWUX FDUH LQWHUVHD]� UDSRUWXO� &RUSXO EXFOHL FRQ LQH MRQF LXQHD FXUVRUXOXL ÄFXUHQW´

FX ]LXD FRUHVSXQ]�WRDUH vQUHJLVW�ULL FXUHQWH GLQ FXUVRUXO ZILE � $VWIHO� GDF� c1 nu are

172 Visual FoxPro

QHYRLH GH H[SOLFD LL� OD SULPD SDUFXUJHUH D FRUSXOXL EXFOHL VH RE LQH FXUVRrul c2 , care, pe OkQJ� FHOH GRX� FRORDQH GLQ c1 � FRQ LQH XQ DWULEXW FRUHVSXQ]�WRU SULPHL GDWH GLQ ZILE (1 august 2001) – vezi figura 5.20.

Figura 5.20. Cursorul c2

$ GRXD WUHFHUH SULQ EXFO� VH VROGHD]� FX XQ QRX FXUVRU – c3 –, vizualizat în figura 5.21.

Figura 5.21. Cursorul c3

8OWLPXO FXUVRU HVWH LGHQWLF FHOXL GLQ ILJXUD �� 'XS� BROWSE-ul vizualizator, se face FXU� HQLH vQ ]RQHOH GH OXFUX� vQFKL]kQGX-VH FXUVRDUHOH GHVFKLVH úL FDUH� OD XQ QXP�U PDUH GH

]LOH� RFXS� XQ YROXP LPSRUWDQW GH PHPRULH� Este GUHSW F� SURJUDPXO SRDWH IL VLPSOLILFDW IRORVLQG IDFLOLWDWHD VDOY�ULL UH]XOWDWHORU

FRQVXOW�ULL vQ DFHODúL FXUVRU GLQ FDUH VH H[WUDJ GDWHOH – vezi listingul 5.19.

/LVWLQJ �� 9HUVLXQHD � D VLWXD LHL PDWULFHDOH D YkQ]�ULL SURGXVHORU pe primele patru zile ale lunii august 2001

* * … identic cu l5.18 * SELECT DenPr, CodPr ; INTO CURSOR c1 ; FROM PRODUSE ; ORDER BY DenPr SELECT Zile SCAN ziua_ = '_'+DTOC(Zile.Zi) SELECT c1.*, NVL(ZILE_PRODUSE.Valoare,0) AS Zi&ziua_ ; FROM c1 LEFT OUTER JOIN ZILE_PRODUSE ; ON c1.CodPr = ZILE_PRODUSE.CodPr AND DataFact = Zile.Zi ; INTO CURSOR c1


SELECT Zile ENDSCAN SELECT c1 BROWSE LAST title 'Vinzari - Produse/Zile' SELECT Zile USE SELECT Zile_Produse USE

capitolul 5 sql în visual foxpro - zenovia coras · capitolul 5 sql în visual foxpro foxpro a...

Documents