Yliopiston etusivulle Suomeksi Inte på svenska No english version available
Helsingin yliopisto Tietojenkäsittelytieteen laitos
 

Tietojenkäsittelytieteen laitos

WWW-sovellusten toteutus Oracle-tietokantaa käyttäen Tietokantasovellusten harjoitustyö -kurssilla

Tässä ohjeessa kuvataan WWW-sovellusten  Helsingin yliopiston tietojenkäsittelytieteen laitoksen Tietokantasovellusten harjoitustyö- kurssin yhteydessä.

Sisältö:
Manuaalit
Toteutusympäristö
Tietokannan käsittely SQL:llä
Tietokantaproseduurit
Tietokannan WWW-kytkentä
   Viittaaminen tauluihin ja proseduureihin
   WWW-käyttöoikeuksien antaminen
   Tietokantaproseduurin kytkeminen sivuun
   HTML:n generointi tietokantaproseduurissa
     Kirjastopakkaus IHT
     Uusi kirjastopakkaus IHT2
   Esimerkkejä
Johdatus PL/SQL -kieleen
Kalvomateriaalia
Vinkkejä
Tämän ohjesivun vanha versio
Instructions in English

Manuaalit ja ohjekirjat

 

Alkuun

Toteutusympäristö

Tietokanta toteutetaan joko 

Kukin opiskelija saa oman Oracle-käyttäjätunnuksen, joka on voimassa ryhmän toiminta-ajan. Opiskelijalla on ennen tunnuksen hankkimista oltava voimassaoleva käyttäjätunnus tietojenkäsittelytieteen laitoksen Linux ympäristöön. Jos kanta halutaan toteuttaa atk-osaston palvelimeen tarvitaan lisäksi atk-osaston unix-tunnus.Saatava Oracle-tunnus on muotoa ops$<unix_tunnus>, esim. ops$virtanen. Salasana kerrotaan erikseen.

Alkuun

Tietokannan käsittely sql:llä

  • Alustustoimet koneessa kontti:
    • Komennoilla 
          setup o8
    setenv ORACLE_SID tktb
luodaan tietokannan käyttöön tarvittava ympäristö

       

    Ylläolevat komennot voi sijoittaa .login tiedostoon, jolloin niitä ei tarvitse tehdä erikseen jokaisella käynnistyskerralla. 

     

  • Alustustoimet koneessa bodbacka ovat seuraavat. Nämä rivit voi sijoittaa kyseisen koneen .bashrc tiedostoon.


    • export CLASSPATH=/home/tkt_orac/OraHome1/JRE/lib
    export ORACLE_HOME=/home/tkt_orac/OraHome1
    export LD_LIBRARY_PATH=/home/tkt_orac/OraHome1/lib:$LD_LIBRARY_PATH
    export TWO_TASK=test
    export PATH=$PATH:/home/tkt_orac/OraHome1/bin


     

     

  • Sql-käynnistyy alustustoimien jälkeen kontissa interaktiivisena komennolla: sqlplus / . Koneessa bodbacka on annettava käynnistyskomento muodossa sqlplus,  jolloin järjestelmä kysyy Oracle-käyttäjätunnusta sekä salasanaa. Komennon voisi antaa myös muodossa sqlplus oracletunnus/salasana, mutta tällöin salasana saattaa näkyä joissakin unix-prosessilistauksissa.

    • Jos tämän komennon annettuasi saat ilmoituksen siitä, ettei komentoa sqlplus löydy, et ole suorittanut ylläesitettyjä alustustoimintoja.

    Kun komentotulkki on käynnistynyt voit antaa sql komentoja tai suoritaa valmiiksi laatimiasi komentotiedostoja. Esimerkiksi tiedostoon skripti.sql kirjoitetut komennot suoritetaan käskyllä

    start skripti.sql    

    tai 

    @skripti.sql

  • Valmiin sql-skriptin skripti.sql voit ajaa komennolla sqlplus / @skripti.sql  (tai  sqlplus oracletunnus /salasana @skripti.sql ). Jos skripti loppuu exit-komentoon, palaa kontrolli unix-komentotulkin tasolle, muuten kontrolli jää SQL-komentotulkille (prompti 'SQL>').

Alkuun

Tietokantaproseduurit

Oracle Web Serveriä käytettäessä www-sovelluksen tietokantakytkentä toteutetaan tietokantaproseduurien avulla. Tietokantaproseduuri on tietokantaan talletettu käännetty tietokannan käsittelyohjelma. Proseduurilla voi olla parametreja. Proseduurien laatimiseen käytetään PL/SQL-ohjelmointikieltä. Kielestä löytyy laaja  englanninkielinen manuaalimateriaali. Tähän dokumenttiin sisätyy  lyhyt  suomenkielinen tiivistelmä PL/SQL:n proseduureista .  WWW-sovelluksissa tietokantaproseduureja käytetään tietokannan tietoja sisältävien WWW-sivujen tuottamiseen tai käsittelyyn, esimerkiksi
  • raporttien laadintaan
  • päivityslomakkeita sisältävien www-sivujen tuottamiseen
  • www-sivulla olevan lomakkeen tietojen käsittelyyn

HUOM: REKURSIIVISTEN PROSEDUURIEN KÄYTTÖ ON KIELLETTYÄ!

Proseduurit voidaan toteuttaa esimerkiksi siten, että jokainen proseduuri kirjoitetaan erilliseen tiedoston ja proseduurit käännetään erikseen. Tiedoston rakenne on tällöin seuraava: 


   create or replace procedure proseduurin_nimi
       ( < parametrit > ) is
        paikalliset tietorakenteet  
   begin
      proseduurin_runko 
   end;
   /
   show errors
   EXIT
jos tiedoston nimi olisi pros1.pl se käännettäisiin komennolla:  sqlplus / @pros1.pl 

Proseduurin toimivuutta voi testata sqlplus-ympäristössä seuraavasti:

SQL> set serveroutput on tk-palvelin tulostakoon
SQL> spool tiedosto.joku ohjataan tulostus tiedostoon
SQL> execute proseduurin_nimi(parametrit)suoritetaan proseduuri
SQL> execute owa_util.showpagevarsinainen tulostus
Tulosta voi sitten ihailla tiedostosta (tiedosto.joku yllä). Tulos tulee myös näytölle ellei sitä erikseen estetä. Syntyneen html-sivun voi tarkistuttaa jossakin www-sivujen validointipalvelussa. Sopivan voi valita vaikkapa Heikki Kantolan listasta.

Alkuun

Tietokannan WWW-kytkentä

Viittaukset omiin tauluihin ja proseduureihin

Harjoitustöissä käytettävä WWW-kytkentä on toteutettu siten, että viitattaessa proseduureissa tietokannan tauluun täytyy viittaukseen aina sisällyttää myös taulun omistajan Oracle-tunnus, esim. 

    select sarake1 into muuttuja
    from ops$virtanen.testi
    where avain=123

Omistajan Oracle-tunnus on sisällytettävä myös proseduurikutsuihin, esim. ops$virtanen.proseduuri1.

Alkuun

Oikeudet WWW-käyttöön

Harjoitustyön yhteydessä tapahtuvaan taulun käsittelyyn ja tietokantaproseduurien suoritukseen WWW:n kautta täytyy antaa lupa käyttäjälle www_user. Proseduuriin liittyvä suorituslupa on yksinkertaisinta antaa proseduurin koodin sisältävään tiedostoon kauttaviivarivin jälkeen sijoitettavalla lauseella

grant execute on proseduurin_nimi to www_user;

www_user:lle on annettava myös taulujen käsittelyoikeudet. Taulun lukuoikeuden voi antaa sql-komennolla:

grant select on taulun_nimi to www_user;.

Lupien hallintaa varten ovat käytettävissä myös unix-komentotiedostot:

$INFOTYO/sallikaikki  Sallii www_user:lle kaikki operaatiot kaikkiin tauluihin ja antaa suoritusoikeudet kaikkiin käyttäjän proseduureihin 
$INFOTYO/sallitaulu taulu  Sallii kaikki operaatiot tauluun taulu 
$INFOTYO/salliluku taulu  Salliin vain lukuoperaatiot tauluun taulu.
$INFOTYO/sallisuoritus proseduuri  Sallii proseduurin proseduuri suorituksen;
$INFOTYO/estakaikki  Peruu kaikki myönnetyt oikeudet 
$INFOTYO/estataulu taulu   Peruu kaikki oikeudet tauluun taulu 

Huomattakoon, että lupa proseduurin käyttöön on uudistettava aina käännöksen jälkeen. Jos annoit sallikaikki-skriptillä oikeudet kaikkiin tietokantakomponentteihisi, niin työhakemistosi tiedostosta gluvat.sql näet, mitä oikeuksia tulit antaneeksi.

Alkuun

Tietokantaproseduurin liittäminen WWW-sivuun

  • Tietokantaproseduuriin viittava URL

    Tietokantaproseduuri liitetään www-sivuun Oracle Web Serverin kautta käyttämällä viittausta

        http://kontti.helsinki.fi:8011/tktb/plsql/kayttaja.proseduurin_nimi 

    Alkuosa ylläolevasta URL:sta (http://kontti.helsinki.fi:8011/tktb/plsql/) identifioi käytettävän tietokantayhteyden. Tämä osa on sama kaikissa linkeissä. Loppuosassa ilmoitetaan käynnistettävän tietokantaproseduurin nimi, esim ops$virtanen.prossu1. 
    Tämän muotoista viittausta voidaan käyttää millä tahansa www-sivulla. Generoitaessa tietokantaproseduureissa samaan tietokantayhteyteen perustuvia www-sivuja voi suhteellisena viittauksena käyttää muotoa käyttäjä.proseduurin_nimi.

  • Parametriluettelo URL:ssa

    Jos kutsuttavalla proseduurilla on parametreja, ne voidaan välittää proseduurille liittämällä linkin perään parametriluettelo. Parametriluettelon muoto on

      ?pNimi1=arvo1&pNimi2=arvo2 ... 

    Parametrin nimen täytyy olla tietokantaproseduurin määrittelyssä esiintyvä parametrinimi. Sen arvon täytyy sopia yhteen proseduurin määrityksessä annettun tietotyypin kanssa.  Parametrien järjestyksen ei tarvitse olla sama, missä parametrit esiintyvät proseduurimäärityksessä. Jos proseduurin parametreilla ei ole oletusarvoja, on kaikille parametreille annettava kutsussa arvo.

  • Parametrit HTML-lomakkeelta

    Parametrit voidaan välittää myös HTML-lomakkeen kautta. Tällöin viittaus käynnistettävään proseduuriin annetaan FORM-lausekkeen ACTION määreenä. FORM-lausekkeessa esiintyvä METHOD määre kuvaa parametrien välitystavan: GET-tapa välittää parametrit ympäristömuuttujan QUERY_STRING arvona (tällöin parametrit myös näkyvät selaimen osoite-kentässä). POST-tapa välittää parametrit standardisyöttöjonon kautta. Proseduurin kannalta ei ole merkitystä sillä kumpaa välitystapaa käytetään. Ympäristömuuttujan koko on kuitenkin rajoitettu, joten, jos parametreja on monta ja niiden arvot  voivat olla pitkiä, on syytä käyttää POST-tapaa.

    Varsinaisia parametreja varten lomakkeeseen määritellään kenttiä. Kenttä määritellään HTML:n INPUT-, TEXTAREA- tai  SELECT-lausekkeella. Lausekkeeseen sisältyvä NAME-määre kytkee kentän tietokantaproseduurin parametriin. NAME-määreen arvona on oltava parametrin nimi. HTML:ssa on eri tyyppisiä syöttökenttiä. INPUT-lausekkeen TYPE-määre määrää syöttökentän tyypin. Mahdollisia tyyppejä ovat:

    • checkbox (valintanappi)
    • image (kuvallinen lähetysnappi)
    • hidden (piilokenttä, josta ei näy mitään (paitsi lähdemuodossa))
    • password (tekstikenttä, johon kirjoitettava arvo ei näy)
    • radio (poissulkeva valintanappi)
    • text (yksirivinen tekstikenttä)
    • submit (lähetysnappi)
    Parametrien arvona välitetään kentän VALUE-määreen arvo tai, jos kenttään on kirjoitettu jotain, niin kirjoitettu arvo.INPUT-kenttien lisämääreitä on syytä katsoa HTML-ohjeista, esim.  Jukka Packalenin HTLM-opas. 


    Esimerkki:  

    
<FORM 
ACTION="http://kontti.helsinki.fi:8011/tktb/plsql/info1.esim1"
METHOD="POST">
<INPUT TYPE="text" NAME="onimi" SIZE=40 MAXLENGTH=40 
VALUE="">
<INPUT TYPE="submit" VALUE="HAE">
</FORM>
    Tuottaa yksikenttäisen lomakkeen, jonka kenttään Nimi kirjoitettu arvo välittyy omistajan info1 proseduurin esim1 parametrin onimi arvoksi. Proseduuri esim1 on rakenteeltaan 
    create or replace procedure esim1 (
   in onimi varchar2) is
begin
   tee_hommat;
end;

    Proseduurille voi välittää myös taulukkomuotoisia parametreja. Näiden käytöstä löytyy lisäohjeita vinkkisivulta.

Alkuun

Sivun generointi tietokantaproseduurissa

    Tietokantaproseduurin tarkoituksena on usein tuottaa uusi www-sivu, joka sisältää tietokannan tietoja, esimerkiksi kyselyn vastauksen. Tällaisten sivujen tuottamista varten on käytettävissä joukko kirjastorutiineja. Kirjastorutiinit on koottu pakkauksiksi. Keskeisimmin tarvittava pakkaus on htp, joka sisältää html-elementtien  tuottamiseen tulossivulle tarvittavia proseduureja. htp-pakkauksessa on oma proseduuri kutakin html-lauseketyyppiä varten.

    Linkki HTP-pakkauksen manuaaliin löytyy sivun alun manuaaliosasta.

    Lisäohjeita HTML:n tuottamisesta PL/SQL-kielellä löytyy vinkkisivulta.

    Alkuun

    Kirjastopakkaus IHT (Infon HarjoitusTyö)

    Kirjastopakkauksen IHT on Juhani Kuittisen proseduurikokoelman pohjalta laatinut Harri Laine. Se sisältää joukon hyödyllisiä proseduureja erityisesti lomakkeiden käsittelyyn. Näitä proseduureja käyttämällä säästyy kirjoitusvaivaa. Prodeduureista on hieman seuraavaa taulukkoa laveampi kuvaus kalvomateriaalissa. (Huom kalvoissa URL on väärin)

    IHT-pakkauksen asemasta voi keväästä 1999 alkaen käyttää uutta IHT2-pakkausta, joka on hieman IHT-pakkausta selkeämpi ja tarjoaa monipuolisempia apuvälineitä, mm. JavaScript-tarkistusten generoinnin.

    Pakkauksen lähdekoodista voit katsoa proseduurien määrittelyitä. Proseduurit ovat:

    back(n int := -1);
    Liittää dokumenttiin Javascript linkit historiassa edelliseen ja seuraavaan dokumentiin siirtymiseksi. Tätä vastaavat napit tulevat mukaan header-proseduurilla, joten tätä ei erikseen pitäisi tarvita.
    footer( cdate varchar2, csignature varchar2);
    Päiväys ja csignature lopputeksti pienellä sivun alalaitaan
    formFieldsOpen(curl varchar2, cmethod varchar2);
    Aloittaa lomakkeen sekä liittää siihen käsittelijän (curl) ja parametrinvälitystavan (get,post - jälkimmäistä suositellaan)
    formfieldsClose;
    Lopettaa lomakkeen kenttäosuuden. Napit kannattaa laittaa erilliseksi nappiriviksi tämän jälkeen. Lomake lopetettava formEnd käskyllä.
    formField(cprompt varchar2, cname varchar2, csize integer, cmaxlength integer, cvalue varchar2);
    Määrittelee lomakkeen kentän. cprompt on kentän vasemmalle puolelle tuleva prompti, cname kentän nimi ja cvalue alkuarvo.
    formStandardButtons2(csubmit varchar2, creset varchar2);
    Määrittelee kaksi nappia submit ja reset ja niiden tekstit
    formButtonRowOpen
    Aloittaa nappirivistön
    formButtonRowItem(ctype varchar2, cname varchar2, cvalue varchar2);
    Nappi nappirivistöön: ctype ilmoittaa napin tyypin (submit/reset), cname on napin nimi (jos submit napille annetaan nimi aiheuttaa nappi parametrin lähetyksen kutsuttavalle proseduurille - voidaan vättää antamalla cname parametrille arvo NULL), cvalue on napin teksti ja mahdollisesti syntyvän parametrin arvo
    formButtonRowClose
    Sulkee nappirivistön
    formEnd
    Lopettaa lomakkeen
    formFieldText(cprompt varchar2, cname varchar2, cvalue varchar2, nrows int := 3, ncols int := 40);
    Kuten formField, määrittelee monirivisen syöttökentän
    formStandardButtons4;
    Määrittelee neljä nappia, yksi reset( alkuarvo) ja kolme submit-nappia (name=optype, value= Päivitä | Uusi tietue | Poista) Submit nappia vastaava optype-parametri tulee mukaan kutsuun.
    header(ctitle varchar2, cinst_url varchar2, cbgr varchar2);
    Määrittelee lomakkeen otsakkeen. ctitle on otsaketeksti. cinst_url linkki avustustekstiin, ja cbgr linkki taustakuvaan. Liittää dokumenttiin kuvalinkit historiassa edelliseen ja seuraavaan dokumenttii.
    shortMessage(cmsg varchar2);
    ilmoitus lihavoituna

    Alkuun

    Pakkaus IHT2

    IHT2 on parannettu ja laajennettu versio IHT-pakkauksesta. Lisäpiirteitä ovat JavaScriptiin perustuvat toiminnot tuotettavalla HTML- sivulla. Pakkauksen versio 0.1 on valmistunut 14.2.1999.

    Pakkaus tarjoaa selaimessa JavaScriptilla tarkistettavina lomakkeen kenttätyyppeinä numeerisen kentän, numeerisen arvovälirajoitetun kentän sekä päiväyskentän. Näiden kenttien arvo tarkastetaan kentästä poistuttaessa (onBlur). Kentästä ei siis pääse pois, jos arvo on virheellinen. Jotta järjestelmä ei jumiutuisi, täytyy tällöin kuitenkin hyväksyä myös tyhjä kenttä. Pakollisten kenttien tarkistusta varten tulisi lomakkeeseen liittää lähetyksen yhteydessä tehtävä tarkistus (onSubmit). Koska kentät tarkistetaan erikseen, riittää lähetysvaiheessa tarkistaa, että kentässä on jokin arvo (tämä perustuu siihen oletukseen, että alkuarvot ovat kelvollisia). Kentän pakollisuustestin generointiin on tarjolla funktio, samoin tarkistusten kokoamiseen tarkistusohjelmaksi.

    Pakkaus tarjoaa myös joitain sellaisia lomakkeen kenttätyyppejä, joihin ei liity tarkistuksia. Kenttiä voidaan sijoitella eri tyylisesti lomakkeelle. Pakkaukseen sisältyy myös apufunktioita (esim. form_field, add_any_js), joita voi käyttää omien laajennustoimintojen toteutuksessa.

    Oheen on liitetty pakkauksen lähdekoodi toisaalta kuvaamaan yksityiskohtaisesti pakkauksen funktiot, toisaalta pohjaksi ja malliksi omien laajennusten tekemiselle. Pakkausta on testattu yleisimmin käytettäviksi oletettujen toimintojen osalta. Kuitenkin joitain virheitä on saattanut jäädä. Ilmoita siis havaitsemistasi virhetoiminnoista pakkauksen laatijalle (Harri.Laine@cs.helsinki.fi). Viat yritetään korjata mahdollisimman pian. Ilmoittele myös, jos mieleesi tulee joitain yleiskäyttöisiä funktioita, joilla pakkausta voisi laajentaa.

Alkuun

Esimerkkejä:

  • Seuraavat esimerkit eivät ole esimerkkejä siitä, miten käyttöliittymä pitäisi tehdä. Niiden tarkoitus on näyttää miten proseduureja kytketään www- sivuille ja miten tietokannan tiedot saadaan sivulle

  • Opiskelijoiden haku nimen perusteella kyselylomakkella  Ophaku.html. Sivun lähdekoodin näet valitsemalla edellisen linkin ja katsomalla lähdemuodon selaimen lähdekoodi-toiminnon avulla.

    Kyselylomakkeen käsittelee tietokantaproseduuri  opiskelija_data . Proseduuri tuottaa tulossivun, jossa näkyvät hakuehdon täyttävien opiskelijoiden tiedot. Kokeile vaikkapa hakua nimellä 'Va%'. Kysely kohdistuu Informaatiojärjestelmät kurssin esimerkkikantaan.

  • Kyselylomake ophaku2.html on muuten samanlainen kuin kohdassa 1, mutta kyselyn käsittelevä proseduuri   opiskeija_sel  täydentää alkuperäisen sivun opiskelijoiden nimet sisältävällä linkkilistalla. Klikkaus nimeen aiheuttaa tietokantaproseduurin   opiskelija_data kutsun.

  • Esimerkki kirjastopakkauksen IHT (Infon HarjoitusTyö) käytöstä.

    Kyselylomake ophaku3.html on samanlainen kuin kohdassa 2, mutta kyselyn käsittelevä proseduuri opiskelija_sel3 täydentää alkuperäisen sivun linkkilistalla löytyneistä opiskelijoista. Listasta valitsemisen käsittelee proseduuri opiskelija_crf, joka luo ylläpitolomakkeen valitun opiskelijan tietojen ylläpitoa varten. Tiedot lomakkeelle haetaan linkkiin liitetyn opiskelijatunnuksen perusteella.

    Lomakkeeen lopussa on nappirivistö, jossa on yksi reset-nappi ja kolme submit-nappia. Submit-napin (päivitä, uusi tietue, poista) painamisen käsittelee proseduuri opiskelija_upd. Tämä tutkii, mitä submit-napeista on painettu ja suorittaa sen perusteella toiminnon. Poisto ei onnistu, muutos onnistuu. Proseduuri näyttää käsiteltävän rivin apuproseduurilla opiskelija_nayta.

    Lisäystä varten luodaan uusi lomake proseduurilla opiskelija_uusi. Täytetty lomake käsitellään proseduurilla opiskelija_ins, joka vie tiedot kantaan.

Alkuun

PL/SQL -proseduurit

    Tässä esitetään lyhyt tiivistelmä PL/SQL-kielen piirteistä. Tarkastelun kohteena on proseduurin määrittely.
    Lisää tietoa löytyy englanninkielisestä manuaalimateriaalista.

    Proseduuri jakautuu 5 osaan
    • proseduurin nimi
    • parametrien esittely
    • paikallisten tietorakenteiden esittely
    • toimintaosa
    • poikkeukset

    
     create or replace procedure proseduurin_nimi
        ( parametrit ) is
        paikalliset tietorakenteet 
     begin
        toiminta_osa
     exception
        poikkeukset
     end;

    Näistä osista parametrit, paikalliset tietorakenteet ja poikkeusosa voivat puuttua.

    Parametrit

    Yleisesti PL/SQL-proseduurin parametrien voivat olla syote-, tulos- tai sekä syöte- että tulosparametreja.
    Parametrin esittelyssä määritellään kunkin parametrin osalta käyttötapa (in / out / in out), nimi ja tietotyyppi.
    Tietotyyppeinä tulevat kyseeseen SQL:n tietotyypit sekä PL/SQL:n omat tietotyypit. 
    
    create or replace procedure testi (teksti1 in char,
       luku1 in integer) is ....

    Paikalliset tietorakenteet

    Paikallisina tietorakenteina voidaan määritellä yksinkertaisia muuttujia, tietueita, taulukoita ja kursoreita.
    Yksinkertaisen muuttujan määrittelyssä annetaan muuttujan nimi ja tietotyyppi. Tietotyyppejä ovat SQL:n tietotyypit integer, number, char, varchar ja date. Ne määritellä kuten SQL:ssä. Lisäksi on käytettävissä Boolean tietotyyppi.

    Tietotyyppi voidaan määritellä myös toisen muuttujan tai taulun sarakeen tietotyypin avulla.
    Lausekkeella
         s1kopio ops$virtanen.tauluA.sarake1%TYPE 
    määritellään muttuja s1kopio saman tyyppiseksi kuin käyttäjän ops$virtanen  omistaman taulun tauluA sarake sarake1.

    Tietuerakenne voidaan määritellä tauluun tai kyselyyn perusteuen.

        rivi1 ops$virtanen.TauluA%ROWTYPE 
        cursor c1 is
                select * from ops$virtanen.TauluB; 
        rivi2 c1%ROWTYPE; 

    Kyselyt määritellään kursoreiden avulla, kuten upotetussa sql:ssä. PL/SQL tarjoa kursoreiden läpikäyntiin kursorisilmukan, joka yksinkertaistaa käsittelyä upotettuun sql:ään verrattuna.

    Toimintaosa

    PL/SQL -kielen toimintaosassa ovat käytettävissä tavanomaiset ohjelmointikielen operaatiot ja rakenteet
    • sijoitusoperaatio (luku := 19 )
    • lausekkeet, ( luku * 2 + toinen_luku )
    • perusaritmetiikka  (luku :=luku * 2 + toinen_luku )
    • merkkijonojen liittäminen (Nimi:= etunimi || ' ' || sukunimi)
    • proseduureja ja funktioita, mm. kaikki Oraclen SQL:n funktiot ohjelmointikielen funktioina
      
    etukirjain := substr(Sukunimi,1,1);
    viikonpaiva:= to_char(sysdate,’DAY’);
    • ehtorakenne if-lausekkeina
      
    if ehto then
       toiminto;
    end if;

    if ehto then
       toiminto1;
    else
       toiminto2;
       toiminto3;
    end if;

    if ehto1 then
       toiminto1;
    elsif ehto2 then
       toiminto2;
       toiminto3;
    else
       toiminto4;
    end if;
      Ehtorakenteita voi kerrostaa kuten yleensä ohjelmointikielissä. 
      
    if ehto then
       if ehto2 then
          toiminta1;
       elsif ehto3 then
          toiminta2;
          toiminta3;
       end if;
    else
       toiminta4;
    end if;
    • Silmukat, mm
      • while-silmukka - suoritetaan, niin kauan kun ehto on tosi
        • 
   while ehto loop
       toiminta;
   end loop;
      • for-silmukka - suoritetaan kaikilla arvovälin arvoilla
        • 
   for muuttuja in alaraja .. yläraja loop
     toiminta;
   end loop;
      • kursorisilmukka - suoritetaan jokaiselle kyselyn tulosriville
        • 
    for rivimuuttuja in kursori loop
       toiminta;
    end loop;
        Kursorisilmukassa suoritetaan aluksi kysely, suoritetaan toiminta jokaiselle riville ja lopuksi suljetaan kursori. Rivimuuttuja osoittaa kyselyn tulosriviin eikä sitä tarvitse erikseen määritellä. Rivin sarakkeisiin viitataan muodossa rivimuuttuja.sarakenimi. 
        
    cursor kkursori is select jotakin from jostakin

    for rivi in kkursori loop
       a:= rivi.kentta1;
       ...
    end loop;
        Kyselyn tulosrivejä voi käydä läpi myös while- silmukassa. Tällöin kursori on ensin avattava Open-lauseella ja rivillä olevat arvot täytyy hakea muuttujiin Fetch-lauseella kuten upotetussa SQL:ssä.

    Poikkeukset

      Erilaiset virhetilanteet voidaan käsitellä poikkeuksina, jolloin varsinainen ohjelmalogiikka pysyy ehkä selvempänä. Poikkeustilanteina voidaan käsitellä valmiiksi määriteltyjä poikkeuksia, joita on lueteltu allaolevassa taulukossa.

      CURSOR_ALREADY_OPEN is raised if you try to OPEN an already open cursor
      DUP_VAL_ON_INDEX is raised if you try to store duplicate values in a database column that is constrained by a unique index. 
      INVALID_CURSOR is raised if you try an illegal cursor operation. For example, if you try to CLOSE an unopened cursor. 
      INVALID_NUMBER is raised in a SQL statement if the conversion of a character string to a number fails.
      LOGIN_DENIED is raised if you try logging on to ORACLE with an invalid username/password. 
      NO_DATA_FOUND is raised if a SELECT INTO statement returns no rows or if you reference an uninitialized row in a PL/SQL table
      NOT_LOGGED_ON is raised if your PL/SQL program issues a database call without being logged on to ORACLE
      PROGRAM_ERROR is raised if PL/SQL has an internal problem. 
      TIMEOUT_ON_RESOURCE is raised if a timeout occurs while ORACLE is waiting for a resource. 
      TOO_MANY_ROWS is raised if a SELECT INTO statement returns more than one row. 
      VALUE_ERROR is raised if an arithmetic, conversion, truncation, or constraint error occurs.
      ZERO_DIVIDE is raised if you try to divide a number by zero. 


      Poikkeukset kirjataan proseduurin exception osaan muodossa: 

      
   when poikkeus1 then
     poikkeuskäsittelijän koodi
   when poikkeus2 then
     poikkeuskäsittelijän koodi

      Myös omia poikkeuksia voi määritellä. Poikkeus aiheutetaan raise poikkeus lauseella. Katso lisää manuaalista.

      Proseduurin loppuun on aina syytä sijoittaa poikkeuskäsittelijä tilanteelle others. Tämä suoritetaan ellei poikkeuskohtaista käsittelijää ole määritelty. Käsittelijä voisi tulostaa virheilmoituksen, vaikkapa seuraavasti: 

      
exception
  when others then 
     htp.p('<h3>Proseduurin pnimi suoritus 
         päättyi virheeseen:</h3>'); 
     htp.p('<font color=red>'|| sqlerrm || 
        '</font>');

      Alkuun

      Harri.Laine@cs.Helsinki.FI
 
Palaute laitokselle | Tiedekunnan etusivulle | Yliopiston etusivulle