CAPITOLUL 12
                                       
   
   
Crearea si Definirea de structuri tabelare

   
   
   In acest capitol, discutam aspectele logice ale tabelelor si
   coloanelor si commenzile necesare pentru a construi tabele cu
   Constrangeri de Integri- tate. Acesta este primul din doua capitole
   care acopera subsetul comenzilor SQL cunoscut ca Limbajul de Definire
   a Datelor(DDL).
   
  Structuri de date ORACLE
  
   
   
    In acest curs vom discuta aspectele logice de creare a
   tabelelor.Consi- deratiile fizice sunt acoperite in celelalte
   cursuri.Desi tabelele pot fi create fara a da informatii de asezare
   fizice, ar trebui notat ca un utili- zator ORACLE trebuie sa fi primit
   privilegiul de CREATE TABLE de catre admi- nistratorul bazei de date
   si sa aiba alocat ceva spatiu de tabela pentru a crea tabele.
   
   In general, structurile de date ORACLE pot fi rezumate dupa cum
   urmeaza.
     * Tabelele pot fi create oricand, chiar cu utilizatori folosind baza
       de date.
     * Nu este necesar sa specificati dimensiunea nici unei tabele.
       Aceasta este definita ultima prin cat spatiu a fost alocat bazei
       de date ca un intreg. Oricum, este important sa estimati cat de
       mult spatiu va utiliza o tabela.
     * Structurile pot fi modificate online.
     * Tabelele pot capata automat mai mult spatiu daca dimensiunea i-
       nitiala este umpluta. 
       
    Limbajul de Definire a Datelor (LDD)
   
       
       LDD este un subset al comenzilor SQL folosit pentru a crea,
       modifica sau muta structurile bazei de date ORACLE, si deasemenea
       sa inregistreze informatii in Dictionarul de Date (acesta este
       discutat mai tarziu).
       
      Denumirea unei tabele
   
       
        Numele pe care-l alegeti pentru o tabela trebuie sa urmeze
       regulile standard pentru numirea unui obiect al unei baze de date
       ORACLE.
         1. Numele trebuie sa inceapa cu o litera, A-Z sau a-z.
         2. Poate contine litere, numerale si caracterele speciale
            underscore (_). Caracterele $ si # sunt deasemenea legale,
            dar folosirea lor este descurajata.
         3. Numele este acelasi indiferent daca sunt folosite litere mari
            sau mici, de exemplu, EMP, emp, si eMp sunt toate aceeasi
            tabela.
         4. Poate fi de maxim 30 caractere in lungime.
         5. Numele nu trebuie sa duplice numele altui obiect din contul
            dumneavoastra.
         6. Numele nu trebuie sa fie un cuvant rezervat SQL.

    NUME                       VALID ?
    ----                       -------

    EMP85                       da

    85EMP                       nu; nu incepe cu o litera

    FIXED_ASSETS                da

    FIXED ASSETS                nu; contine un blank

    UP DATE                     nu; cuvant rezervat SQL

   
   
   Ar trebui sa folositi nume de descriere pentru tabele si alte obiecte
   ale bazei de date. Folositi acelasi nume sa descrie aceeasi entitate
   in doua tabele diferite. De exemplu, coloana cu numarul
   departamentului este numita DEPTNO in ambele EMP si DEPT. 
   
      Crearea unei tabele
      
   
   
   Creati o noua tabela folosind comanda CREATE TABLE. Una dintre cele
   mai simple forme a acestei comenzi este cand informatia de baza pentru
   fiecare coloana este definita impreuna cu tipul ei de data si
   dimensiunea.

  Sintaxa: CREATE TABLE nume tabela
           (nume coloana tip(dimensiune),
            nume coloana tip(dimensiune),
            ...);

  Exemplu: CREATE TABLE DEPT
           (DEPTNO NUMBER(2),
           DNAME   VARCHAR2(12),
           LOC     VARCHAR2(12));

   
   
   Numele coloanelor intr-o tabela trebuie sa fie unice.
   
      Tipurile coloanelor
      
   
   
    Cand creati o tabela trebuie sa specificati fiecare tip de data al
   co- loanei. Tabela de mai jos arata cele mai importante tipuri de
   date.
   
   Tipul de data poate fi urmat de unul sau mai multe numere in paranteze
   care dau informatii despre latimea coloanei. Latimea coloanei
   determina la- timea maxima pe care valorile in coloana pot s-o aiba.
   VARCHAR2 trebuie sa aiba o dimensiune, dar cele implicite sunt
   disponibile.
   
   Tabela de mai jos arata tipurile de date principale in ORACLE7.

   Tip de date                        Descriere
   -----------                        ---------

   VARCHAR2(w)                    Sir de caractere de lungime maxim w. Lungi-
                                  mea maxima este de 2000 caractere.

   CHAR(w)                        Sir de lungime fixa w. Lungimea implicita
                                  este 1. Lungimea maxima este 255.

   NUMBER                         Numere in virgula mobila cu precizie : 38
                                  de cifre semnificative.

   NUMBER(w)                      Numere intregi de precizie w.

   NUMBER(w,s)                    Numere cu precizia w si scala s. Precizia
                                  reprezinta numarul maxim de cifre semnifi-
                                  cative permise care nu pot depasi 38. Scala
                                  este numarul de pozitii zecimale inregistra-
                                  te in dreapta punctului.

   DATE                           Valorile datei din 1 Ianuarie 4712 inainte
                                  de Hristos pana in 31 Decembrie 4712 dupa
                                  Hristos. Informatia de timp este deasemenea
                                  stocata.

   LONG                           Sir de caractere de lungime variabila de
                                  lungime 2 Gb, sau 2 la puterea 31 minus 1.
                                  Este permisa o singura coloana de tipul LONG
                                  pe tabela.

   RAW si                         Echivalent cu VARCHAR2 si respectiv LONG,
   LONG RAW                       dar folosit pentru a stoca date binare ca
                                  imagini grafice sau sunete digitizate.

        NOTE:
     * Tipul de date VARCHAR2 al Oracle7 este sinonim cu versiunea 6 a
       tipu- rilor de date 'VARCHAR' si 'CHAR'.
     * ORACLE foloseste semantica prin comparatie non-padded daca o
       valoare VARCHAR2 este folosita intr-o comparatie(vezi Capitolul 2
       pentru de- talii).
     * Daca o valoare scrisa intr-o coloana depaseste scala coloanei,
       atunci va apare o rotunjire.
       
   
   
   Tabela de mai jos arata exemple de specificatii de coloane:

  NUMBER(4)                    Poate contine toate numerele pana la 4 cifre.

  NUMBER(8,3)                  Poate contine pana la 8 cifre, dintre care 3
                               pot fi in dreapta punctului zecimal.

  VARCHAR2(1000)               Valorile pot contine pana la 1000 caractere.

  CHAR(80)                     Siruri de caractere de lungime fixa egala cu
                               80. Valorile mai scurte sunt inlocuite cu
                               blank-uri.

      Crearea tabelei EMP
      
   
   
    Tabela noastra demonstrativa, EMP, ar fi putut fi creata de o comanda
   CREATE TABLE ca mai jos:

      CREATE TABLE EMP
      (EMPNO       NUMBER(4) NOT NULL,
      ENAME        VARCHAR2(10),
      JOB          VARCHAR2(10),
      MGR          NUMBER(4),
      HIREDATE     DATE,
      SAL          NUMBER (7,2),
      COMM         NUMBER (7,2),
      DEPTNO       NUMBER (2) NOT NULL);


   
   
   Comanda DESCRIBE a SQL*PLUS-ului poate fi folosita pentru a lista
   detal ale coloanelor create intr-o tabela:

                                    DESCRIBE EMP
 

      Constrangerea NOT NULL
      
   
   
    In exemplul de mai sus, veti observa ca definitiile pentru coloanele
   EMPNO si DEPTNO sunt urmate de NOT NULL. Aceasta ne asigura ca valoril
   nule sunt permise pentru aceste coloane, de cand aceste coloane
   servesc ca chei pentru operatii pe aceasta tabela. Coloanele fara
   constrangerea NOT NULL permit valori nule.
   
   NOT NULL este una dintre constrangerile de integritate care pot fi
   defi- nite.
   
    OPTIUNEA DEFAULT
    
   
   
    Unei coloane ii poate fi data o valoare implicita prin optiunea
   DEFAULT. Aceasta previne aparitia de null-uri (sau erori, daca NOT
   NULL este specifi- cata) daca o linie este inserata fara o valoare din
   coloana.Valorile implici- te pot fi literali, o expresie, dar nu
   numele altei coloane. Functii ca SYSDATE si USER sunt valide.
   
   De exemplu:

         HIREDATE DATE DEFAULT SYSDATE,
         SAL NUMBER (7,2) DEFAULT 0


      Constrangeri de integritate
      
   
   
    Oracle permite constrangerilor de integritate sa fie definite pentru
   ta- bele si coloanelor sa forteze reguli sigure, inauntrul unei tabele
   sau intre tabele.
   
   Constrangerile sunt folosite:
     * de serverul Oracle7 sa forteze reguli la nivelul tabelei oricand
       este inserata o linie, actualizata sau stearsa din acea tabela.
       Constrange- rea trebuie sa fie satisfacuta pentru ca operatiile sa
       reuseasca.
     * pentru a preveni stergerea unei tabele daca sunt posesiuni din
       alte tabele.
     * prin unelte sigure Oracle, ca Oracle Forms, pentru a furniza
       reguli pentru utilizarea intr-o aplicatie.
       
   
   
   Constrangerile sunt clasate dupa cum urmeaza:
   
          Constrangeri de tabela
          
   Acestea pot referi una sau mai multe coloane si sunt definite SEPARAT
          de definitiile coloanelor din tabela.
          Constrangeri de coloana
          
   Acestea refera o singura coloana si sunt definite INAUNTRUL
          specificatiei pentru coloana posesoare.
          
   
   
   Constrangerile pot fi adaugate unei tabele dupa crearea ei si
   deasemenea temporar dezactivate (vezi comanda ALTER TABLE in capitolul
   urmator).Toate detaliile despre constrangeri sunt stocate in
   Dictionarul de Date. Fiecarei constrangeri ii este repartizat un nume.
   Iti este mai usor sa suplimentezi una tu singur, astfel ca poate fi
   mai usor referita mao tarziu, dar daca nu, atunci un nume este generat
   automat pe forma:

        SYS_Cn

   
   unde n este un numar unic. Cuvantul cheie CONSTRAINT iti permite sa
   numesti o noua constrangere tu insuti.
   
    Tipuri de constrangeri
    
   
   
    Puteti defini urmatoarele tipuri de constrangeri:
     * NULL/NOT NULL
     * UNIQUE
     * PRIMARY KEY
     * FOREIGN KEY(integritatea de referinta)
     * CHECK
       
      Constrangerea UNIQUE
      
   
   
    Aceasta desemneaza o coloana sau o combinatie de coloane ca o cheie
   uni- ca.Doua linii in aceeasi tabela nu pot avea aceeasi valoare
   pentru aceasta cheie.NULL-urile sunt permise daca cheia unica este
   bazata pe o singura co- loana.
   
   Sintaxa constrangerii de tabela :

       [CONSTRAINT nume constrangere] UNIQUE (Coloana, Coloana, ...)

   
   
   Sintaxa constrangerii de coloana :
       [CONSTRAINT nume constrangere] UNIQUE

   
   
    De exemplu, pentru a va asigura ca nu sunt 2 nume de departamente
   identice la o singura locatie:

       CREATE TABLE DEPT
       (DEPTNO NUMBER, DNAME VARCHAR2(9),
       LOC VARCHAR2(10),
       CONSTRAINT UNQ_DEPT_LOC UNIQUE(DNAME,LOC))


   
   
   In exemplul de mai sus, constrangerea UNQ_DEPT_LOC este o constrangere
   de ta- bela. Notati ca virgula precede detaliile. O constrangere unica
   il face pe ORACLE sa creeze un singur index pentru a manui regula.
   Indecsii sunt discu- tati mai tarziu.
   
      Constrangere de cheie primara
      
   
   
    Ca si la cheile unice, o cheie primara forteaza unicitatea unei
   coloane sau combinatii de coloane implicate si un index unic este
   creat pentru a con- duce aceasta. Totusi poate fi o singura cheie
   primara pe o tabela, si aceasta este cunoscuta ca fiind cheia
   definitiva prin care liniile in tabela sunt i- dentificate individul.
   NULL-urile nu sunt permise in coloanele de chei prima- re.
   
   Sintaxa constrangerii de tabela :
       [CONSTRAINT nume constrangere] PRIMARY KEY (Coloana, Coloana, ...)

   
   
    Sintaxa constrangerii de coloana :
       [CONSTRAINT nume constrangere] PRIMARY KEY

   
   
    Notati ca aceeasi combinatie de coloane nu poate fi folosita si
   pentru o cheie primara si pentru una unica. Urmatorul exempludefineste
   DEPTNO ca o cheie primara folosind o constrangere de coloana:

         CREATE TABLE DEPT
         (DEPTNO NUMBER(2) CONSTRAINT DEPT_PRIM PRIMARY KEY, ...)


      Constrangere de cheie externa
      
   
   
    Cheile externe furnizeaza reguli de integritate de referinta
   inauntrul unei tabele sau intre tabele. O cheie exeterna este folosita
   intr-o relatie cu fiecare cheie primara sau unica oriunde si poate fi
   folosita, de exemplu, pentru a preveni stergerea unui departament in
   DEPT daca angajatii exista cu acelasi numar de departament in EMP.
   
   Sintaxa constrangerii de tabela :
       [CONSTRAINT nume constrangere] FOREIGN KEY (Coloana, Coloana, ...)
       REFERENCES tabela (Coloana, Coloana, ...)

   
   
   Sintaxa constrangerii de coloana :
       [CONSTRAINT nume constrangere] REFERENCES tabela (Coloana)

   
   
    Notati ca, cuvintele 'FOREIGN KEY' nu sunt folosite versiunea
   constrangerii de coloana a sintaxei.

                      ------------
  Exemplul 1          |   DEPT   |  (parinte)
                      ------------
                           |
                           |
                          /|\    DEPTNO
                      ------------
                      |   EMP    |   (fiu)
                      ------------

   
   
   Pentru a stabili relatia dintre EMP si DEPT astfel incat EMP.DEPTNO
   este che- ia externa, si fiecare angajat trebuie sa aiba un numar
   valid de departament care este cunoscut in DEPT:

     CONSTRAINT FK_DEPTNO FOREIGN KEY (DEPTNO)
     REFERENCES DEPT(DEPTNO)



    Optiunea ON DELETE CASCADE
    
   
   
    Ca rezultat al constrangerii de tabela de mai sus, (care ar fi putut
   la fel de bine sa fie definita ca o constrangere de coloana), un
   departament in DEPT nu ar fi putut fi sters daca liniile exista in EMP
   cu aceeasi valoare DEPTNO. Alternativ, puteti cere ca angajatii
   corespunzatori sa fie stersi a- utomat daca departamentul parinte in
   DEPT este sters. Aceasta este realizata adaugand clauza ON DELETE
   CASCADE.

     CONSTRAINT FK_DEPTNO FOREIGN KEY (DEPTNO)
     REFERENCES DEPT(DEPTNO) ON DELETE CASCADE



 Exemplul 2          --------------   EMPNO
                     |    EMP     | . .
                     --------------     .
                           \|/           .
                            .            .
                             .          .
                      MGR     .       .
                                .  .




 Pentru a va asigura ca fiecarei linii de angajat in EMP ii este dat un numar
 de manager (MGR) pentru un angajat existent valid:

        CREATE TABLE EMP
        (EMPNO NUMBER(4) PRIMARY KEY, ...
        MGR NUMBER(4) CONSTRAINT EMP_MGR
        REFERENCES EMP(EMPNO), ...


      Constrangerea de verificare (CHECK)
      


      Constrangerea CHECK defineste explicit o conditie pe care fiecare linie
 trebuie sa o satisfaca(sau sa o faca necunoscuta datorita unui NULL). Condi-
 tia poate folosi aceleasi constructii ca acelea intr-o restrictie de cerere,
 cu urmatoarele exceptii:

     * subcererile nu sunt permise
     * referirile la pseudo-coloane ca SYSDATE nu sunt permise
   


 Sintaxa:

          [CONSTRAINT nume constrangere] CHECK (conditie)


      Alte optiuni ale constrangerilor

  DISABLE                Adaugand DISABLE unei definitii de constrangere
                         inseamna ca ORACLE nu o forteaza. Constrangerea poate
                         fi inca citita de uneltele ORACLE pentru a construi
                         reguli intr-o aplicatie si puteti face posibila con-
                         strangerea mai tarziu prin comanda ALTER TABLE.
     
     CREATE TABLE EMP
     ( . . . . . ,
     ENAME VARCHAR2(10) CONSTRAINT CHK_UPP_NAM CHECK(ENAME-UPPER(ENAME))
     DISABLE,
     . . . . . . . ) ;
    
  EXCEPTIONS             Identifica o tabela existenta unde este plasata
  INTO nume tabela       informatia despre liniile care incalca constrangerea.
   
   CREATE TABLE EMP
     ( . . . . . ,
     ENAME VARCHAR2(10) CONSTRAINT CHK_UPP_NAM CHECK(ENAME-UPPER(ENAME))
     EXCEPTIONS INTO CON_VIOLATE,
     . . . . . . . ) ;
   




 In final, notati ca tabelele referite intr-o constrangere trebuie sa existe
 in aceeasi baza de date. Daca ele apartin unui utilizator diferit atunci po-
 sesorul trebuie specificat ca un prefix.



 Iata un exemplu complet al constructiei tabelei EMP cu constrangeri :

   CREATE TABLE EMP

   (EMPNO NUMBER(4)    CONSTRAINT EMP_PRIM PRIMARY KEY,

   ENAME VARCHAR2(10)  CONSTRAINT ENAME_CONS
                       CHECK(ENAME=UPPER(ENAME)),

   JOB VARCHAR2(10),

   MGR NUMBER(4)       CONSTRAINT EMP_MGR
                       REFERENCES EMP(EMPNO),

   HIREDATE DATE DEFAULT SYSDATE,

   SAL NUMBER(7,2)     CONSTRAINT SAL_CONS
                       NOT NULL,

   COMM NUMBER(7,2),

   DEPTNO NUMBER(2)    CONSTRAINT DEPTNO_CONS
                       NOT NULL,

   CONSTRAINT          EMP_DEPT FOREIGN KEY (DEPTNO)
                       REFERENCES DEPT(DEPTNO))


    Crearea unei tabele cu linii din alta tabela
    


      Exista o a doua forma a declaratiei CREATE TABLE in care tabela este
 creata cu linii potrivite, derivate din alta tabela:

        CREATE TABLE DEPT
        [(nume-coloana ,. . . .)]
        AS SELECT declaratie

     * Tabela va fi creata cu coloane specificate si linii recuperate din
       de- claratia SELECT inserata.
     * Numai constrangerile NULL/NOT NULL sunt mostenite din tabela
       selecta- ta.
     * Daca toata coloanele in declaratia SELECT au nume bine definite
       (nu sunt expresii s.a.m.d.) specificatiile coloanei pot fi omise.
     * Daca sunt date specificatiile coloanei, atunci numarul de coloane
       tre- buie sa fie egal cu numarul de articole in lista SELECT.


 Pentru a crea o tabela DEPT30 care tine numerele angajatilor, nume, job-uri
 si salariile angajatilor din departamentul 30, introduceti:

      CREATE TABLE DEPT30
      AS
      SELECT       EMPNO,ENAME,JOB,SAL
      FROM         EMP
      WHERE        DEPTNO = 30;



      Table created.



 Pentru a vedea descrierea lui DEPT30, introduceti:

     DESC DEPT30





 Pentru a crea o tabela tinand numele angajatului, salariul si detalii de
 grad, introduceti:

    CREATE TABLE EMP_SALS
    (NAME,SALARY,GRADE)
    AS
    SELECT       ENAME, SAL, GRADE
    FROM         EMP, SALGRADE
    WHERE        EMP.SAL BETWEEN LOSAL AND HISAL;



    Table created.

    DESC EMP_SALS;




 Pentru a afisa continutul tabelei EMP SALS, introduceti:

         SELECT      *
         FROM        EMP_SALS;
,/pre>




        CAPITOLUL 12 EXERCITII - CREAREA DE TABELE SI DE CONSTRANGERI
                                       
                                       


      Atelier
    1. Creati o tabela numita PROJECTS, cu coloanele specificate ca mai
       jos.Pe langa aceasta , definiti PROJID ca, coloana de CHEIE
       PRIMARA , si asigura- ti-va ca datele P_END_DATE nu sunt mai
       recente decat datele P_START_DATE.
    2. Creati o a doua tabela, ASSIGNMENTS, ca cea de mai jos. Definiti-i
       coloana PROJID ca o cheie externa care refera tabela PROJECTS.
       Coloana EMPNO a ta- belei dumneavoastra este o viitoare cheie
       externa a lui EMP. Aceste doua coloane nu ar trebui sa permita
       valori NULL (PROJID si EMPNO).
       
       Sunt alte constrangeri pe care ar trebui sa le angajam in aceste
       tabele. Acestea vor fi adaugate intr-un viitor atelier.
    3. Folositi comanda DESCRIBE pentru a verifica definitiile
       coloanelor. (Ne vom uita la constrangeri mai tarziu).



CAPITOLUL 12 -Solutii





 Fiecare din constrangerile aratate mai jos ar fi putut fi definite ca orica-
 re constrangere de tabela sau constrangere de coloana.
    1.

      CREATE TABLE PROJECTS
      ( PROJID           NUMBER(4) CONSTRAINT PROJ_PRIM PRIMARY KEY,
      P_DESC             VARCHAR2(20),
      P_START_DATE       DATE,
      P_END_DATE         DATE,
      BUDGET_AMOUNT      NUMBER(7,2),
      MAX_NO_STAFF       NUMBER(2),
      CONSTRAINT  P_DATE_RULE CHECK(P_START_DATE 
    2.

        CREATE TABLE ASSIGNMENTS
      (PROJID            NUMBER(4) NOT NULL REFERENCES PROJECTS (PROJID),
      EMPNO              NUMBER(4) NOT NULL REFERENCES EMP (EMPNO),
      A_START_DATE       DATE,
      A_END_DATE         DATE,
      BILL_RATE          NUMBER(4,2),
      ASSIGN_TYPE        VARCHAR2(2))

Inapoi la cuprins.

BogSoft 1999