Oracle web-serveriä käyttävien www-sovellusten toteutus Informaatiojärjestelmien harjoitustyö -kurssilla

• Ohjeeseen liittyy kalvomateriaalia (15.1.98).
• Vinkkejä (12.3.98)
• Kaikki Oracle tietokantapalvelimen manuaalit löytyvät www:stä..
• Oracle Web Server user manual html-muodossa.
• Oracle Web Server User's Manual pdf-muodossa.
• Oracle Web Server FAQ of Frank Naude and Steve Kilbane.

Ympäristö

Tietokanta toteutetaan Helsingin yliopiston atk-keskuksen koneeseen kontti tietokantainstanssiin ttst käyttäen Oracle 7 -tietokantapalvelinta. 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 atk-keskuksen unix-laitteistoihin. Oracle-tunnus on muotoa ops$<unix_tunnus>, esim. ops$virtanen. Käyttöoikeus tunnukseen tarkistetaan unix-tunnuksen perusteella, joten Oracle-salasanaa ei tarvitse tietää.

Tietokannan käsittely sql:llä

• Alustustoimet (koneessa kontti):
• setup oracle7ttst ja
• setenv INFOTYO /home/group/infolab/tyo
Ylläolevat komennot voi sijoittaa .login tiedostoon, jolloin niitä ei tarvitse tehdä erikseen jokaisella käynnistyskerralla. Ympäristömuuttujaa INFOTYO tarvitaan myöhemmin komentotiedostojen käynnistykseen.
• Sql-käynnistyy alustustoimien jälkeen interaktiivisena komennolla: sqlplus / 
• Valmiin sql-skriptin skripti.sql voi ajaa komennolla sqlplus / @skripti 

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

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.showpage	varsinainen 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 vaikkapa WEBTechsin validointipalvelussa.

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.

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. Lupien hallintaa varten ovat käytettävissä unix-komentotiedostot:

Täsmällisempien oikeuksien määrittelyä varten on käytettävä SQL:n grant-lausetta.
Huomattakoon, että lupa proseduurin käyttöön on uudistettava aina käännöksen jälkeen.
Jos annoit oikeudet kaikkiin tietokantakomponentteihisi , niin työhakemistosi tiedostosta gluvat.sql näet, mitä oikeuksia tulit antaneeksi.

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:8889/ttst/owa/kayttaja.proseduurin_nimi 

Alkuosa ylläolevasta URL:sta (http://kontti.helsinki.fi:8889/ttst/owa/) 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
?parametrin1_nimi=parametrin1_arvo&parametrin2_nimi=parametrin2_arvo ... 

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 (html) ACTION määreenä. FORM-lausekkeessa esiintyvä METHOD määre kuvaa parametrien välitystavan: GET-tapa välittää parametrit ympäristömuuttujan QUERY_STRING arvona. POST-tapa välittää parametrit standardi syöttöjonon kautta. Proseduurin kannalta ei ole merkitystä sillä kumpaa välitystapaa käytetään. Ympäristömuuttujan koko on 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-lausekkeilla. 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ä, jonka 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:8889/ttst/owa/info1.esimerkki1" 
      METHOD="POST"> 
Nimi: <INPUT TYPE="text" 
       NAME="onimi" SIZE=40 MAXLENGTH=40 VALUE=""><P> 
<INPUT TYPE="submit" VALUE="HAE"> 
     </FORM> 
 
Tuottaa yksikenttäisen lomakkeen, jonka kenttään Nimi kirjoitettu arvo välittyy omistajan info1 proseduurin esimerkki1 parametrin onimi arvoksi. Proseduuri esimerkki1 on rakenteeltaan

create or replace procedure esimerkki1 (in onimi varchar2) is 
begin 
     tee_hommat; 
end; 

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.

Pakkauksiin sisältyvät proseduurit on kuvattu  pfd-muotoisessa manuaalissa, (Adobe Acrobat-muoto) samassa muodossa löytyy myös  20 sivuinen tiivistelmä (Adobe Acrobat -pdf).  Saatavilla on myös  HTML-muodossa oleva manuaali.


Kirjastopakkaus IHT (Infon HarjoitusTyö)

Kirjastopakkauksen IHT on Juhani Kuittisen proseduurikokoelman pohjalta laatinut Harri Laine. Pakkaus voi vielä muuttua. 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.

Paketin 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 form field, 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

Esimerkkejä:

1. 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ä 'Ville%'. Kysely kohdistuu Informaatiojärjestelmät kurssin esimerkkikantaan.

2. 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.
3. 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.





 

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  
execption  
        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 varchar2, 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, 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ä poikkeuksi . a, esim.

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.