Tietokannan ylläpito

SQL provides three database manipulation operations to change the data in the database.These operations are:

• insert - for adding new rows
• update - for changing existing rows, and
• delete - for deleting existing rows

Each of these operations affects on one table only. 

Database transaction

Database manipulation operations may be collected as transactions.  A database transaction consists of a sequence of database manipulation operations that is treated as a single unit.  A transaction 

• is either executed fully or not at all, but never only some parts of it,
• has its effects published as a whole,
• may proceed in parallel with other transactions without ......... distroying or confusing the effects of these transactions
• stores its effects permanently in the database when succesfully completed
• may be cancelled until completed.

In SQL database maintenance operations are collected into transactions with COMMIT statetments. A COMMIT statement indicates that the transaction is ready and should be completed. Thus it ends one transaction and starts another one. When the database management system receives a commit request it tries to complete the transaction and make the changes made during the transaction permanent. Users may rely on that committed transactions have been executed completely. If we want the changes to be registered permanently on the database we muts always issue the COMMIT command. 

Instead of commiting, a transaction may be ended by canceling its effects with ROLLBACK statement..This causes all the changes made on the database during the transaction to be cancelled. 

Insertion 

New rows may be added on the database with INSERT-statements. This staement has two forms: constant value based single row insertion and multiple row insert based on a query. lisäys.

Single row insert:

insert into table_name [(column_name1 [, ...])]
   values (value1 [, ...])

insert into producttype


   values('P08',)


 

The list of column names must be included if the rows to be  inserted are given only partially, i.e. some column values are missing. If the list is inot included the column values for the entire row must be given in the same order as the columns are defined in the table definition..Missing colums are assigned the default value. This is null if it is not defined

Insert operation that would violate the integrity constraints is not accepted..  Consider the following insert.Why is it not accpetable?

A) too many columns

B) the promarry key already exists:

c) A column defined to be not null is not included

insert into opintojakso (kurssikoodi,nimi_suomeksi)
values ('45678','Testauksen jatkokurssi')

Feedback: c is correct. NOT NULL is a contraint and this violates it.

An already ecxisting primary key, and a missing primary key to refer to with a foreign key are also typical constraints an insert might violate. 

The rows to be added may also be defined with a query. In this case the insert statent is the following

insert into table_name [(column_name1 [, ...])]
   query

insert into kurssi
  select kurssikoodi,2001,'S',1,'1.9.2001','15.10.2001'
  from opintojakso
  where nimi_suomeksi='Testaus'

The query that is embedded within the insert must produce the same number of column as the are in the column list or if it is missing in the table definition.. Each result row is to be added into the target table. There is only one target table in an insert statement.

insert into ordered
select max(orderId)+1,2,
  'Testauksen jatkokurssi',null)
  from ordered

This example adds a new order in table ordered. The orderId wil be the greatest used orderId plus one.  Generating keys this way might not be very efficient if there are a lot of inserts, because finding the greatest value may disturb concurrent processing of transactions. Most database management systems provide more efficient sequence generators for generating idetifiers. In  Oracle the generator is called  SEQUENCE. Next example in Oracle's SQL forst defines a sequence generator for Customer identifiers and then uses it.. Function nextval increases the counter by one and then gives the increased value. This example used the  table  SYS.DUAL. This is a predefined Oraclessa table that has one column and one row. It is intended for uses where no actual table need to be queried.

create sequence curtomer_numbers
  increment 1;

         ...

insert into customer
   select customer_numbers.nextval,2,
  'Testauksen jatkokurssi',null)
  from sys.dual

Query based inserts are commonly used when the new rows have some colums that are to be decided based on the contents of the database,  If we want to copy rows from one table to another we have to use this operation.. Let the table OLD_CUSTOMERS have the same structure than table Customer. Our next exaple copies the rows of those customers that have not ordered anything in year 2002 into table old_customers.

insert into old_customers
   select * from customer
   where customerId not in 
    (select Customer from ordered 
     where whenmade>'31.1.2002';

Update

Updates change existing rows. One operation affects directly on only one table. It may change many rows in that table. Muutoksen kohteena olevat rivit määritellään valintaehdolla.

Esimerkki 8.7. (ks. esimerkkikannan kaavio)

update table_name


   set column_name_1= expression_1 


     [, column_name2= expression_2 [, ...]]


   [where select_condition ]

update opiskelija


  set sukunimi='Virtanen',


      osoite= 'Kivikatu 6, 00230 Helsinki'


  where opiskelijanumero=1234

Yllä muutetaan opiskelijan 1234 sukunimi ja osoite samalla operaatiolla. 

Jos lauseesta puuttuu valintaehto tehdään muutos kaikkiin taulun riveihin. Seuraavassa esimerkissä devalvoidaan opintoviikko 20 prosentilla.

Esimerkki 8.8. (ks. esimerkkikannan kaavio)

update opintojakso


   set opintoviikot= 1.2 * opintoviikot

Kun halutun ylläpitotoiminnon suorittamiseksi tarvitaan useita operaatioita on syytä tarkoin miettiä operaatioiden järjestys, jotta operaatiot eivät sotkisi toistensa kohdejoukkoja.

Alkuun

Poisto

Poisto-operaatiolla poistetaan valintaehdon täyttävät rivit taulusta. Jos valintaehto puuttuu poistetaan taulun kaikki rivit.

Esimerkki 8.9. (ks. esimerkkikannan kaavio)

delete from table_name


   [where select_condition]

delete from opetustehtava


where tyyppi ='harjoitus' and lukuvuosi=2001 


  and lukukausi='K' and


 (kurssikoodi,lukuvuosi,lukukausi,kurssinumero,opetusnumero) 


    not in


  (select kurssikoodi,lukuvuosi,lukukausi,


     kurssinumero,opetusnumero


  from ilmoittautuminen


  where perumis_pvm is null)

Edellisessä esimerkissä yritetään poistaa kaikki sellaiset kevään 2001 harjoitustyyppiset opetustehtävät, joihin ei ole ilmoittautunut ketään. Koska tauluun TYO on määritelty tauluun opetustehtava viittaava viiteavain ja siihen cascade-määre aiheuttaa tämä poisto sivuvaikutuksenaan myös kyseisiin harjoituksiin liittyvien työtehtävien poiston.

Alkuun

Operaatiosarjat

Koska SQL:n ylläpito-operaatiot ovat hyvin yksinkertaisia joudutaan tietokannan ylläpitotoimet usein suorittamaan useiden operaatioiden sarjoina. Esimerkiksi tilisiirtoa, jossa rahaa siirretään yhdeltä tililtä toiselle ei voi suorittaa yhdellä TILI-tauluun kohdistuvalla update-operaatiolla Tarvitaan kaksi operaatiota tililtäotto ja tilillepano. Nämä yhdessä muodostavat tietokantatransaktion.

Esimerkki 8.10. (ks. esimerkkikannan kaavio)

commit;


update tili 


  set saldo=saldo-500


  where tilinro=1234567;


update tili 


  set saldo=saldo+500


  where tilinro=4567890;


commit;

 Tässä tapauksessa tililtäoton tai tilillepanon järjestyksellä ei ole merkitystä. Transaktion molemmat osat on suoritettava onnistuneesti. Ylläpito-operaation onnistumiseen vaikuttavat myös kantaan liittyvät eheysehdot. Esimerkiksi TILI-taulun sarakkeeseen SALDO voisi liittyä eheysehto, jonka mukaan sarakkeen arvon on oltava suurempi tai yhtäsuuri kuin nolla eli tili ei saa mennä miinukselle. Tililtäotto-operaatio epäonnistuu tämän eheysehdon johdosta, jos tililtä yritetään ottaa saldoa suurempi rahamäärä. Jos transaktion mikä tahansa osa epäonnistuu, epäonnistuu koko transaktio ja mahdollisesti jo tehdyt muutokset peruutetaan.

Transaktion suorituksen aikana tietokantaan tehdään muutoksia. Muutokset näkyvät transaktion ulkopuolelle vasta transaktion päätyttyä. Transaktion sisäisesti operaation aikaansaamat muutokset kuitenkin näkyvät heti.

Transaktio 1	Transaktio2	Muutosten näkyvyys
Operaatio 1.1
Operaatio 1.2		Näkee operaation 1.1 muutokset
	Operaatio 2.1	Näkee 1.1:n ja 1.2:n muutokset vasta transaktion 1 päätyttyä. Jää tarvittaessa odottamaan.
Operaatio 1.3		Näkee 1.1:n ja 1.2:n tekemät muutokset heti, mutta 2.1:n tekemät muutokset vasta transaktion 2 päätyttyä. Jää tarvittaessa odottamaan. Jos käy niin, että transaktio 1 jää odottamaan transaktiota 2 ja päinvastoin, on syntynyt lukkiutuma (deadlock), joka hoidetaan peruuttamalla toinen transaktioista ja käynnistämällä se myöhemmin uudelleen

Usein tulee myös tilanteita, joihin ei ole helposti laadittavissa pelkästään SQL-operaatioista koostuvaa operaatiosarjaa ylläpitotehtävän hoitamiseksi. Tarkastellaan esimerkkinä taulua TYONTEKIJA(tt_numero, ..., palkka), joka sisältää työntekijän perustiedot. Työntekijöille on päätetty antaa palkankorotus ja korotusmäärät on  kirjattu tauluun KOROTUS(tt_numero,lisays). Korotukset pitäisi saada päivitetyksi tauluun TYONTEKIJA. Standardin mukaisella Update-lauseella päivitys ei onnistu. Tavittaan erillinen update-lause jokaista korotuksen saavaa työntekijää kohti. Operaatiosarja voidaan toki tuottaa SQL:llä seuraavasti:

Esimerkki 8.11. (ks. esimerkkikannan kaavio)

select 


   'update tyontekija set palkka= palkka+'||


   to_char(lisays)||


   'where tt_numero='||


   to_char(tt_numero)'||';' lause


from korotus

Tämä kysely generoi tarvittavat update-lauseet. Tulos voidaan ohjata tiedostoon ja suorittaa sieltä. Tällaisen generoinnin asemasta voitaisiin kirjoittaa jollain ohjelmointikielellä ohjelma, joka hoitaisi päityksen. Tätä vaihtoehtoa tarkastellaan luvussa 'Tietokantaohjelmointi ja Java-tietokantaliittymä'. Oraclella on vaihtoehtoinen update-rakenne, jolla edellä käsitelty ylläpito onnistuu yhdellä operaatiolla:

Esimerkki 8.12. (ks. esimerkkikannan kaavio)

update tyontekija


   set (palkka) = 


       (select palkka+ lisays 


        from korotus


        where tyontekija.tt_numero=korotus.tt_numero)


where tyontekija.tt_numero in


   (select tt_numero from korotus)

Tässä muunnelmassa sijoitettavan arvon tuottavan alikyselyn on tuotettava yksi arvo kutakin muutettavaa riviä kohti.

   Alkuun