Oracle SQL

Allikas: Kuutõrvaja

Sissejuhatus

Andmebaasi kasutamise eesmärgiks on saada andmeid salvestada, sälitada ja teha kättesaadavaks. SQL (Structured Query Languag) keel võimaldab kasutada relatsioonilist andmebaasisüsteemi (RDBMS, relational database management system).

Andmebaasiga ühendumine

Andmebaasi kasutamise eelduseks on, et

  • kasutajal on sobiv klientprogramm
  • kasutajale on usaldatud ligipääs andmebaasile

Tavaliselt toimub andmebaasi kasutamine üle võrgu, st kasutaja töötab andmebaasi klientprogrammiga ühes ja andmebaas töötab teises arvutis. Andmebaasi klientprogrmm ja andmebaasi server suhtlevad omavahel kasutades spetsiaalset selleks ettenähtud protokolli, mis sõltub konkreetsest andmebaasi tarkvarast. Reeglina sisaldab andmebaasitarkvara lisaks andmebaasi serverile ka mingit terminalil kasutatavat nö käsurea klientprogrammi, Oracle puhul on selleks programm sqlplus.

Andmebaasiühenduse moodustamiseks sobib Oracle puhul kasutades sqlplus programmi öelda 

$ sqlplus kasutajanimi/parool@10.0.13.66:1521/servicename

SQL*Plus: Release 11.2.0.1.0 Production on Wed Mar 31 17:21:06 2010
Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security
and Oracle Database Vault options

SQL>

Ilma, et oleks vaja andmebaasis andmeid, saab andmebaasi kasutada nt selliselt

  • andmebaasi kliendi kasutamine kalkulaatorina
SQL> select 5*5 from dual;
  • andmebaasi klient töötab sõnedega
SQL> select 'Hello ' || 'World' from dual;
  • arvutada arvu ruutjuur
SQL> select sqrt(25) from dual;

'from dual' konstruktsiooni tuleb kasutada juhtumil, kui ei töötata nö päris tabeliga, dual on pseudotabel.

sqlplus kasutamisel tuleb arvestada, et väljade laiused esitatakse nö täis pikkuses st nii pikad kui pikk vastava välja kirjeldus on, kusjuures arvestades sqlplusis seadistatud rea pikkust, millest edasi esitatakse tekst järgmisel real. Aitab öelda nt 

sql> set linesize 250

Andmebaasi objektide hierarhia

TODO

Tabel

Andmebaasis paiknevad andmed asuvad tabelites. Tabelis esitatakse andmeid ridade kaupa, kusjuures iga rida koosneb mitmest veerust ehk väljast. Kirjeldades tabelit sellise konkreeste näite alusel 

id      isikukood    eesnimi  perekonnanimi       synniaeg        sissekande_aeg 
 1    37802249999      Priit           Kask     1978-02-24   2010-03-29 15:45:20
 2    46406259999      Maila            Laa     1964-06-25   2010-03-29 15:46:24
 3    38612249999       Mart           Kask     1886-12-24   2010-03-29 15:47:44

võib öelda

  • ühte tabelisse kantakse sama tüüpi objektide erinevate eksemplaride kohta käivaid andmeid
  • tabelil on nimi, näiteks võiks see olla inimene
  • tabelis esineb viis välja (ingl. k. field), öeldakse ka, et viis atribuuti: id, isikukood, eesnimi, perekonnanimi, synniaeg ja sissekande_aeg
  • igal rea väljal on väärtus
  • tabelis on kolm rida (ingl. k. row) ehk kirjet (ingl. k. record)
  • tabeli atribuutid on alati mingit kindlat tüüpi, nt täisarv, määratletud või määratlemata pikkusega tekst, kuupäev
  • põhimõtteliselt ei ole tabelis read järjestatud
  • põhimõtteliselt ei ole reas väljad järjestatud
  • reeglina on tabelid hästi pikad ja kitsad, st tabelis on suhteliselt palju ridu ja vähe veerge

Relatsioonilises andmebaasis on tabeliga seotud lisaks seal paiknevatele andmete ka mitmeid lisaomadusi, nt

  • tabeli read on unikaalsed
  • primaarne võti (ingl. k. primary key) - võtmega seotud välja väärtuse järgi saab rida identifitseerida
  • välisvõti (ingl. k. foreign key) - kasutatakse seose loomiseks antud tabeli mõne välja ja mõne teise tabeli samatüübilise välja vahel
  • võimalused mitmete tabelite andmete kooskõlalise (ingl. k. integrity) tagamiseks

Kuna tabeli id väli peab automaatselt suurenema, siis esmalt tuleb tekitada loendur 

sql> create sequence seq_inimene;

Tabeli moodustamiseks sobib öelda 

sql> CREATE
 TABLE "INIMENE"
 (
   "ID"            integer,
   "ISIKUKOOD"     CHAR(11 BYTE),
   "EESNIMI"       VARCHAR2(50 BYTE),
   "PEREKONNANIMI" VARCHAR2(50 BYTE),
   "SYNNIAEG" DATE,
   "SISSEKANDE_AEG" TIMESTAMP default current_timestamp);

Tabeli kasutamine

Põhiliselt seisneb andmebaasi tabeli kasutamine kolme sorti tegevustes

  • andmete lisamine (insert) - tabelisse lisatakse rida
  • andmete lugemine (select) - tabelist loetakse andmeid
  • andmete muutmine (update) - tabeli olemasoleva rea ühe või enama välja väärtusi muudetakse
  • andmete kustutamine (delete) - tabelist kustutatakse rida

Andmete lisamine

Tabelisse kirje lisamiseks on käsk insert, nt selliselt 

sql> insert into inimene (id, isikukood, eesnimi, perekonnanimi, synniaeg) values (seq_inimene.nextval, '46705079999', 'Marta', 'Maara', '07-May-67')

Andmete lugemine

Tabelist andmete lugemiseks on käsk select, nt küsides rea, kus isikukoodi väärtus on 46705079999 

sql> select isikukood, perekonnanimi from inimene where isikukood = '46705079999';

ISIKUKOOD   PEREKONNANIMI
----------- --------------------
46705079999 Maara

SQL skripti kasutamine

sql> start /tmp/skript.sql

Tabelite vahelised seosed

Tabelite vahelisi seoseid kasutatakse andmete kooskõlalisuse kehtestamiseks ja neid kirjeldatakse võtmete abil.

Primaarne võti

Primaarse võtmega (ingl. k. primary key) tabeli moodustamine 

sql> CREATE
TABLE "INIMENE"
(
  "ID"            integer,
  "ISIKUKOOD"     CHAR(11 BYTE),
  "EESNIMI"       VARCHAR2(50 BYTE),
  "PEREKONNANIMI" VARCHAR2(50 BYTE),
  "SYNNIAEG" DATE,
  "SISSEKANDE_AEG" TIMESTAMP default current_timestamp);

Olemasolevale tabelile võtme lisamine, eeldusel, et tabelil juba ei ole primaarset võtit 

sql> ALTER TABLE inimene ADD CONSTRAINT inimene_id_pk PRIMARY KEY (inimene_id);

Primaarse võtme eemaldamiseks sobib öelda 

sql> ALTER TABLE inimene DROP CONSTRAINT inimene_id_pk;

Välisvõti

Olgu ülesanne kanda tabelisse loom sisse andmed loomade kohta, sh peremehe isikukood. Sellisel juhtumil on sobiv siduda loom tabeli atribuuti omaniku_isikukood välisvõtmena (ingl. k. foreign key) tabeli inimene atribuudiga isikukood. Loom tabel võiks olla nt selline 

sql> CREATE TABLE loom 
     (id INTEGER, loomakood CHAR(11), 
      nimi VARCHAR2(20), 
      omaniku_isikukood CHAR(11));

Olemasolevale tabelile välisvõtme lisamiseks sobib öelda 

sql> ALTER TABLE loom ADD CONSTRAINT loom_to_inimene_fk FORGEIN KEY (omaniku_isikukood) REFERENCES inimene (isikukood);

Välisvõtme kustutamiseks sobib öelda 

sql> ALTER TABLE loom DROP FORGEIN KEY loom_to_inimene_fk;

Antud näites viitab välisvõti tabeli inimene atribuudile isikukood, mis peab seega olema iseenesest unikaalne, soovitavalt primaarne võti. Praktiliselt ei ole see tark valik, kuid näite selguse huvides on tehtud nii.

Indeks

Indeks (ingl. k. index) on andmebaasi instrument, mida kasutatakse selleks, et kiirendada päringuid. Põhimõtteliselt on andmebaasi indeks saranane raamatu lõpus kasutatavale indeksile, kuna indeksis on sissekanded esitatud tähestikulises järjekorras, saab leida suhteliselt kiire huvipakkuva sissekande ning seejärel lehekülje numbri järgi koha raamatu tekstis endas.

Andmebaasis indekseeritakse tavaliselt neid tabeli välju, mida kasutatakse tavaliselt tabelist päringute tegemisel nö where järele. Kuna indeksi pidamisega kaasneb täiendav ressursikulu, siis ei maksa indeksitega ka liialdada

  • indeks võtab salvestusseadmel märkimisväärselt ruumi
  • indeksi moodustamisel on tabel reeglina kirjutamiste jaoks lukustatud olekus
  • kuna tabeli andmete muutmisel tuleb muuta vastavalt ka indeksit, siis tuleb andmebaasil selleks teha lisatööd

Tabeli inimene atribuudile isikukood indeksi moodustamiseks sobib öelda 

sql> create index isikukood_idx on inimene (isikukood);

Indeksi eemaldamiseks sobib öelda 

sql> drop index isikukood_idx;

Vaade

Vaade (ingl. k. view) on virtuaalne tabel, mis on moodustatakse igal vaate poole pöördumisel vaate kirjeldamisel kasutatud sql päringu alusel. 

sql> create view v_inimesed_vanemad_kui_1980 as select * from inimene where synniaeg > to_timestamp('31-12-1980');

Vaate kasutamiseks sobib öelda nt 

sql> select * from v_inimesed_vanemad_kui_1980;

PL/SQL

Funktsioonid

Funktsiooni moodustamiseks tuleb öelda (ja lõpetada sisestus / märgiga viimasel tühjal real) 

sql> create or replace function liitja
       (liidetav1 IN integer,
       liidetav2 IN integer)
       return integer
     as
     begin
       return liidetav1 + liidetav2;
     end;

Funktsiooni kasutamiseks tuleb öelda 

sql> select liitja(5, 11) from dual;

Protseduurid

TODO

Päästik

TODO

Kasulikud lisamaterjalid

Pärit leheküljelt "https://kuutorvaja.eenet.ee/w/index.php?title=Oracle_SQL&oldid=18989"