4. CVIČENÍ

Upozornění !!!

Než začnete pracovat na příkladech, zadejte nejdříve tyto příkazy : 

    CREATE TABLE predmety  AS SELECT * FROM predmety;
    CREATE TABLE rozvrh    AS SELECT * FROM rozvrh;
    CREATE TABLE mistnosti AS SELECT * FROM mistnosti;

Vkládání záznamů : 

INSERT  INTO predmety 
        VALUES ( 99, 'DS', 'KIV', 'Distribuovane systemy', NULL, NULL, NULL );

INSERT  INTO predmety ( nazev, katedra, zkratka, kredity, id )
        VALUES ( 'Pocitace a programovani', 'KIV', 'PP', 5, 98 );

Aktualizace záznamů : 

UPDATE  predmety
   SET  kredity = 20
 WHERE  katedra = 'KIV'
   AND  zkratka = 'KP';

Rušení záznamů : 

DELETE
  FROM  rozvrh
 WHERE  poznamka like 'Seminar%';

Transakce :

Transakce začíná prvním DML příkazem. Transakci lze buď potvrdit příkazem COMMIT nebo zrušit příkazem ROLLBACK.

Pozor !

U některých DBMS vyvolá DDL příkaz (CREATE, ALTER, DROP) implicitně COMMIT.

V případě, že potřebujeme za určitých okolností odvolat pouze několik posledních operací, je výhodné použít tzv. SAVEPOINT : 

DELETE FROM predmety WHERE zkratka = 'HB';
SELECT zkratka FROM predmety;

SAVEPOINT prvni;

DELETE FROM predmety WHERE zkratka like 'P%';
SELECT zkratka FROM predmety;

ROLLBACK TO SAVEPOINT prvni;
SELECT zkratka FROM predmety;
Transakci lze nastavit jako READ ONLY nebo READ/WRITE : 

SET TRANSACTION READ ONLY;

SET TRANSACTION READ WRITE;

Zamykání tabulek

Zamknutí tabulky pro čtení (sdílené; ostatní transakce mohou číst, ale nemohou zapisovat): 

LOCK TABLE rozvrh IN SHARE MODE;
Zamknutí tabulky pro zápis (výlučné; ostatní transakce nemohou číst ani zapisovat) 
LOCK TABLE rozvrh IN EXCLUSIVE MODE;
Volání LOCK TABLE je blokující. Znamená to, že pokud má jiná transakce tabulku již zamknutou, volání příkazu neskončí, dokud není tabulka uvolněna. Nechceme-li na odekmnutí tabulky čekat, uvedeme na konci příkazu klauzuli NOWAIT, chceme-li počkat 5 sekund, použijeme WAIT 5. 
LOCK TABLE rozvrh IN EXCLUSIVE MODE NOWAIT;
Je-li tabulka zamknuta, pak volání skončí chybou: 

SQL> lock table rozvrh in exclusive mode nowait;
lock table rozvrh in exclusive mode nowait
           *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified

Zamknuté tabulky jsou automaticky odemknuty po spáchání (COMMIT) či odvolání (ROLLBACK) transakce.

Zamykání záznamů

Tabulky je možné zamykat až na úrovni jednotlivých záznamů. Záznamy určené k uzamknutí vyberem příkazem SELECT, ke kterému přidáme klauzuli FOR UPDATE.

Zamknutí rozvrhových akcí v zimním semestru: 

SELECT  *
  FROM  rozvrh
 WHERE  semestr = 'ZS'
FOR UPDATE;

Následující příkaz bude blokován, dokud transakce předchozího příkazu nedoběhne (COMMIT nebo ROLLBACK): 

UPDATE  rozvrh
   SET  poznamka = 'Pokus';

Tento příkaz proběhne okamžitě, protože neaktualizuje zamknuté záznamy: 

UPDATE  rozvrh
   SET  poznamka = 'Pokus'
 WHERE  semestr != 'ZS';

Příkazy UPDATE a DELETE blokují pokud je zamknut alespoň jeden záznam, kterého se operace týká nebo tabulka je zamknuta v SHARE nebo EXCLUSIVE módu. Příkaz INSERT blokuje pouze pokud je zamknuta tabulka v SHARE nebo EXCLUSIVE módu. Neblokuje, pokud je jeden nebo všechny záznamy zamknuty.

Příkazy UPDATE, DELETE a INSERT nepodporují klazuli WAIT, resp. NOWAIT. Předpokládá se, že tabulka, resp. jednolivé záznamy se zamknou pomocí SELECT ... FOR UPDATE.

Složitější operace

Dávkové narozvrhování všech předmětů katedry KIV v letním semestru: 

INSERT INTO ROZVRH (id_predm, semestr, poznamka)
  SELECT  id, 'LS', 'Garant: '||garant
    FROM  predmety
   WHERE  katedra = 'KIV';

V příkazu UPDATE může být i odkaz na sloupce z aktualizované tabulky: 

UPDATE  predmety
   SET  kredity = 3, pocet_studentu = pocet_studentu + 10;

Všimněte si, že u záznamů, kde má položka POCET_STUDENTU hodnotu NULL, nadále zůstává po této operaci hodnota NULL, nikoliv 10 !

V příkazu UPDATE můžeme použít i vnořený dotaz. Následující ukázka provede všech předmětů nastavení počtu kreditů na 5 a počtu studentů na součet kapacit místností, ve kterých je předmět vyučován: 

UPDATE  predmety p
   SET  (kredity, pocet_studentu) =
        (SELECT  5, SUM(m.kapacita)
           FROM  rozvrh r, mistnosti m
          WHERE  r.id_predm = p.id
            AND  r.id_mistn = m.id
         );

Vytvoření tabulky 

CREATE TABLE jméno_tabulky
  (jméno_sloupce datový_typ [NULL | NOT NULL] [,...]);
Základní podporované datové typy :

VARCHAR2(N)text dlouhý max. N znaků (1 <= N <= 2000)
CHAR(N)text dlouhý N znaků (je-li kratší, je doplněn mezerami na N)
NUMBER(C,D)číslo; C = počet cifer, D = počet desetinných míst
FLOATreálné číslo
DATEdatum / čas
LONGtext délky až 2 GB
LONG RAWneinterpretovaná data velikosti až 2 GB

Pozn.: 

CREATE TABLE osoby (
  os_cislo   NUMBER(5)    NOT NULL,
  jmeno      VARCHAR2(30) NOT NULL,
  prijmeni   VARCHAR2(30) NOT NULL,
  dat_naroz  DATE,
  pohlavi    CHAR(1)      NOT NULL,
  telefon    VARCHAR2(50)
);
Změna struktury tabulky

Může nastat případ kdy potřebujeme změnit strukturu již existující tabulky s daty. SQL standard umožňuje přidávat a rušit sloupce pomocí příkazu ALTER TABLE. 

ALTER TABLE pracoviste ADD ( adresa VARCHAR2(80) );


ALTER TABLE pracoviste DROP ( adresa );

Může se stát, že rušený sloupec je cizím klíčem v jiné tabulce. Potom by mohla být porušena referenční integrita dat, a proto je i zde možné použít jak restriktivní (implicitně daný), tak kaskádní přístup : 

ALTER TABLE pracoviste DROP ( cislo_prac  ) CASCADE;
Pozn.: rušení sloupců v tabulce není v RDBMS Oracle podporováno.

Zrušení tabulky 

DROP TABLE osoby;

Potřebujeme-li pouze smazat všechna data v tabulce a strukturu tabulky ponechat, je výhodnější použít příkaz TRUNCATE TABLE osoby;
V tomto případě však nelze původní obsah tabulky obnovit odvoláním transakce příkazem ROLLBACK.