Oracle SQL

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;

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

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;

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

Kasulikud lisamaterjalid

• Oracle Instant Client