Oracle PL/SQL lisää, päivitä, poista ja valitse [esimerkki]

DML-tapahtumat PL/SQL:ssä

DML on lyhenne sanoista Data Manipulation Language, joka on kielten ryhmä. SQL komentoja, jotka muuttavat taulukkoon tallennettuja tietoja. Taulukon sisällä PL/SQL-lohkoNämä komennot suorittavat manipulointityön, kun taas PL/SQL tarjoaa ympäröivän logiikan. DML käsittelee alla olevat operaatiot.

• Tietojen lisäys
• Tietojen päivitys
• Tietojen poisto
• Tietojen valinta

PL/SQL:ssä datan käsittely suoritetaan vain SQL-komentojen avulla.

Tietojen lisäys

PL/SQL:ssä rivit lisätään taulukkoon SQL-komennolla INSERT INTO. Tämä komento ottaa syötteeksi taulukon nimen, kohdesarakkeet ja sarakearvot ja lisää sitten arvon perustaulukkoon.

INSERT-komento voi myös ottaa arvot suoraan toisesta taulukosta SELECT-lauseen avulla sen sijaan, että antaisi arvot kullekin sarakkeelle. SELECT-lauseen avulla voidaan lisätä kerralla niin monta riviä kuin lähdetaulukko sisältää.

Syntaksi:

BEGIN
INSERT INTO <table_name>(<column1>,<column2>,...,<column_n>)
VALUES(<value1>,<value2>,...,<value_n>);
END;

Yllä oleva syntaksi esittää INSERT INTO -komennon. Taulukon nimi ja arvot ovat pakollisia kenttiä, kun taas sarakenimet ovat valinnaisia, kun INSERT-lauseke antaa arvot taulukon jokaiselle sarakkeelle. Avainsana VALUES on pakollinen, kun arvot annetaan erikseen, kuten yllä on esitetty.

Syntaksi:

BEGIN
INSERT INTO <table_name>(<column1>,<column2>,...,<column_n>)
SELECT <column1>,<column2>,...,<column_n> FROM <table_name2>;
END;

Tämä INSERT INTO -funktion toinen muoto ottaa arvot suoraan käyttämällä SELECT-komentoa. Avainsanaa VALUES ei saa olla tässä, koska arvoja ei anneta erikseen.

Tietojen päivitys

Datan päivittäminen tarkoittaa olemassa olevan rivin sarakkeen arvon muuttamista. Tämä tehdään UPDATE-lausekkeella, joka ottaa syötteeksi taulukon nimen, sarakkeen nimen ja uuden arvon ja päivittää tiedot.

Syntaksi:

BEGIN
UPDATE <table_name>
SET <column1>=<value1>,<column2>=<value2>,<column_n>=<value_n>
WHERE <condition that uniquely identifies the record that needs to be updated>;
END;

Yllä oleva syntaksi näyttää UPDATE-lausekkeen. Avainsana SET käskee PL/SQL-moottoria päivittämään sarakkeen annetulla arvolla. WHERE-lauseke on valinnainen; jos sitä ei anneta, mainitun sarakkeen arvo päivitetään koko taulukkoon.

Tietojen poisto

Tiedon poistaminen tarkoittaa yhden kokonaisen tietueen poistamista tietokantataulukosta. Tähän tarkoitukseen käytetään DELETE-komentoa.

Syntaksi:

BEGIN
DELETE FROM <table_name>
WHERE <condition that uniquely identifies the record that needs to be deleted>;
END;

Yllä oleva syntaksi esittää DELETE-komennon. Avainsana FROM on valinnainen, ja komento toimii samalla tavalla FROM-lausekkeen kanssa tai ilman sitä. WHERE-lauseke on valinnainen; jos sitä ei anneta, koko taulukko tyhjennetään.

Tietojen valinta

Datan projektio eli nouto tarkoittaa tarvittavien tietojen hakemista tietokantataulukosta. Tämä saavutetaan SELECT-komennolla yhdessä INTO-lauseen kanssa. SELECT-komento noutaa arvot tietokannasta, ja INTO-lauseke määrittää nämä arvot PL/SQL-lohkon paikallisiin muuttujiin.

Seuraavat seikat on otettava huomioon käytettäessä SELECT-lauseketta INTO-lausekkeen kanssa:

• SELECT-lauseen tulisi palauttaa vain yksi tietue INTO-lausetta käytettäessä, koska yksi muuttuja voi sisältää vain yhden arvon. Jos SELECT palauttaa useamman kuin yhden rivin, TOO_MANY_ROWS poikkeus on nostettu.
• SELECT-lauseke määrittää arvon INTO-lausekkeen muuttujalle, joten se tarvitsee vähintään yhden tietueen arvon täyttämiseksi. Jos se ei löydä yhtään tietuetta, syntyy NO_DATA_FOUND-poikkeus.
• SELECT-lauseen sarakkeiden lukumäärän ja niiden tietotyyppien tulee vastata INTO-lauseen muuttujien lukumäärää ja niiden tietotyyppiä.
• Arvot haetaan ja täytetään samassa järjestyksessä kuin lausunnossa mainitaan.
• WHERE-lauseke on valinnainen ja sen avulla voit asettaa enemmän rajoituksia noudettaville tietueille.
• SELECT-lauseketta voidaan käyttää muiden DML-lausekkeiden WHERE-ehdossa ehtojen arvojen määrittämiseen.
• INSERT-, UPDATE- tai DELETE-lausekkeiden sisällä käytetyn SELECT-lauseen ei tulisi sisältää INTO-lausetta, koska se ei täytä mitään muuttujaa näissä tapauksissa.

Syntaksi:

BEGIN
SELECT <column1>,...,<column_n> INTO <variable1>,...,<variable_n>
FROM <table_name>
WHERE <condition to fetch the required records>;
END;

Yllä oleva syntaksi esittää SELECT-INTO-komennon. Avainsana FROM on pakollinen ja määrittää taulukon, josta tiedot haetaan. WHERE-lauseke on valinnainen; jos sitä ei anneta, tiedot haetaan koko taulukosta.

Esimerkki 1: Tässä esimerkissä näemme, miten DML-operaatioita suoritetaan PL/SQL:ssä. Lisäämme alla olevat neljä tietuetta emp-taulukkoon.

Sitten päivitämme palkan 'XXX' arvoon 15 000, poistamme työntekijätietueen 'ZZZ' ja lopuksi projisoimme työntekijän 'XXX' tiedot.

Alla oleva kuvakaappaus näyttää tässä esimerkissä käytetyn koko PL/SQL-lohkon.

DECLARE
l_emp_name VARCHAR2(250);
l_emp_no NUMBER;
l_salary NUMBER;
l_manager VARCHAR2(250);
BEGIN
INSERT INTO emp(emp_name,emp_no,salary,manager)
VALUES('BBB',1000,25000,'AAA');
INSERT INTO emp(emp_name,emp_no,salary,manager)
VALUES('XXX',1001,10000,'BBB');
INSERT INTO emp(emp_name,emp_no,salary,manager)
VALUES('YYY',1002,10000,'BBB');
INSERT INTO emp(emp_name,emp_no,salary,manager)
VALUES('ZZZ',1003,7500,'BBB');
COMMIT;
Dbms_output.put_line('Values Inserted');
UPDATE EMP
SET salary=15000
WHERE emp_name='XXX';
COMMIT;
Dbms_output.put_line('Values Updated');
DELETE emp WHERE emp_name='ZZZ';
COMMIT;
Dbms_output.put_line('Values Deleted');
SELECT emp_name,emp_no,salary,manager INTO l_emp_name,l_emp_no,l_salary,l_manager FROM emp WHERE emp_name='XXX';
Dbms_output.put_line('Employee Detail');
Dbms_output.put_line('Employee Name:'||l_emp_name);
Dbms_output.put_line('Employee Number:'||l_emp_no);
Dbms_output.put_line('Employee Salary:'||l_salary);
Dbms_output.put_line('Employee Manager Name:'||l_manager);
END;
/

lähtö:

Values Inserted
Values Updated
Values Deleted
Employee Detail
Employee Name:XXX
Employee Number:1001
Employee Salary:15000
Employee Manager Name:BBB

Code Selitys:

• Code rivi 2–5: Muuttujien deklarointi.
• Code rivi 7–14: Tietueiden lisääminen emp-taulukkoon.
• Code rivi 15: Lisäystapahtumien vahvistaminen.
• Code rivi 17–19: Työntekijän 'XXX' palkkaa päivitetään 15 000:een.
• Code rivi 20: Päivitystapahtuman vahvistaminen.
• Code rivi 22: Poistetaan 'ZZZ'-tietue.
• Code rivi 23: Poistotapahtuman vahvistaminen.
• Code rivi 25: Valitsemalla tietueen 'XXX' ja täyttämällä muuttujat l_emp_name, l_emp_no, l_salary ja l_manager.
• Code rivi 26–30: Näyttää noudetut tietuearvot.

UKK