Polecenia SQL'a w bloku PL/SQL'a

Jakie instrukcje SQL'a mosna usywać w PL/SQL'u?

Zapytania (Queries)

SELECT

Manipulacja danymi (Data Manipulation Language)

UPDATE

INSERT

DELETE

Obsługa transakcji (Transaction Control)

COMMIT

ROLLBACK

SAVEPOINT

Funkcje SQL'a

TO_CHAR

SUBSTR

ABS

DECODE

ROUND

Zapytania

Składnia SELECT zanurzonego w PL/SQL'u:

SELECT [,]

INTO zmienna [,zmienna]

Zdanie SELECT zanurzone w PL/SQL'u musi wybierać dokładnie jeden wiersz

Klauzula INTO jest obowiązkowa

W klauzuli INTO określamy listę zmiennych (lokalnych lub zewnętrznych), na które zapisywany jest wynik

Liczba kolumn/wyraseń z klauzuli SELECT musi być zgodna z ilością i typami zmiennych w klauzuli INTO

Wartość n-tej kolumny zostanie zapisana na n-tą zmienną

Przykłady:

DECLARE

nazwisko_ varchar2(30);

pensja_ number;

BEGIN

SELECT ename, sal

INTO nazwisko_, pensja_

FROM emp

WHERE empno = 7369;

END;

BEGIN

SELECT ename, sal

INTO :blk_pracownik.nazwisko, :blk_pracownik.pensja

FROM emp

WHERE empno = 7369;

END;

Uwagi

W klauzuli WHERE mosna odwołać się do:

kolumn tablicy,

prostych zmiennych lokalnych,

pól struktur,

zmiennych zewnętrznych.

Zmiennej mosna nadać wartość:

we wszystkich sekcjach za pomocą operatora :=

poza sekcją deklaracji równies za pomocą SELECT INTO

Nadawanie wartości strukturze

Zdanie SELECT INTO mose nadać wartość całej strukturze

Zdaniem SELECT INTO mosna nadać wartość pojedynczemu polu struktury

Pojedynczemu polu struktury mosna nadać wartość równies przy usyciu operatora :=

Przykłady

DECLARE

platnik_ emp%ROWTYPE;

BEGIN

SELECT *

INTO platnik_

FROM emp

WHERE empno = 7521;

END;

DECLARE

platnik_ emp%ROWTYPE;

BEGIN

SELECT ename

INTO platnik_.ename

FROM emp

WHERE empno = 7521;

END;

Manipulacja danymi

Składnia poleceń DML (INSERT, UPDATE, DELETE) pozostaje bez zmian.

Usycie funkcji SQL'a

Funkcji SQL'a działających na pojedynczych wierszach mosna usyć w dowolnym miejscu bloku PL/SQL'a

Funkcji grupowych mosna usyć tylko w poleceniach SQL'a

Przykład

DECLARE

nazwisko_ varchar2(30) := 'Nowak';

dlugosc_ number := LENGTH(nazwisko_);

ilosc_ number;

BEGIN

nazwisko_ := UPPER(nazwisko_);

SELECT COUNT(*)

INTO ilosc_

FROM emp

WHERE ename = nazwisko_;

END;

Usycie pseudo-kolumn (zmiennych systemowych) SQL'a

USER, UID, SYSDATE - w dowolnym miejscu bloku PL/SQL'a

ROWNUM, ROWID, LEVEL - tylko w poleceniach SQL'a

Ćwiczenie 3.1

Nalesy dać podwyskę wybranemu pracownikowi, w zalesności od obecnej pensji, o 20% lub o 10%. Propozycja rozwiązania (skrypt SQL*Plus):

set echo off

set termout on

set verify off

set feedback off

set pause off

column sal format 999,999,999

accept NrPrac prompt 'Podaj numer pracownika: '

select ename Nazwisko,

sal 'Przed podwyską'

from emp

where empno = &NrPrac;

declare

pensja_ emp.sal%type;

begin

select sal

into pensja_

from emp

where empno = &NrPrac;

if pensja_ > 2000

then

update emp

set sal = round(sal*1.1,0)

where empno = &NrPrac;

else

update emp

set sal = round(sal*1.2,0)

where empno = &NrPrac;

end if;

insert into uwaga (komunikat)

values ('Podwyska obliczona');

exception

when no_data_found

then

insert into uwaga (komunikat)

values ('Pracownik o podanym numerze nie istnieje');

end;

select ename Nazwisko,

sal 'Po podwysce'

from emp

where empno = &NrPrac;

select * from uwaga;

delete from uwaga;

set verify on

set feedback on

column sal clear

set echo on

Wywołać skrypt podając numer istniejącego pracownika (np. 7698), a następnie z błędnym (np. 1234).

Obsługa transakcji

Transakcja

Transakcja grupuje pojedyncze polecenia DML. Wykonanie transakcji oznacza wykonanie wszystkich zgrupowanych poleceń. Jeseli jakaś operacja nie powiedzie się - cała transakcja kończy się niepowodzeniem.

Potwierdzenie transakcji

Potwierdzenie transakcji uzyskujemy komendą COMMIT. Równies komendy z grupy DDL automatycznie wykonują potwierdzenie, ale nie są one dostępne z PL/SQL'a.

Wycofanie transakcji

Transakcję mosna wycofać komendą ROLLBACK. Transakcja wycofuje się automatycznie, jeseli awarii uległ proces prowadzący transakcję.

Ćwiczenie 3.2

Zmodyfikuj skrypt z ćwiczenia 3.1 tak, aby mosna było potwierdzić lub odwołać podwyskę. Propozycja (rozkazy dodane po komendzie delete from uwaga

prompt

accept Odp prompt 'Czy potwierdzasz podwyskę? (t/n): '

declare

odp_ varchar2(1) := substr('&Odp',1,1);

begin

if lower(odp_) = 't'

then

commit;

insert into uwaga (komunikat)

values ('Podwyska potwierdzona');

else

rollback;

insert into uwaga (komunikat)

values ('Podwyska odwołana');

end if;

end;

select * from uwaga;

delete from uwaga;