Podstawy PL/SQL
Pierwszy program w PL/SQL
Przykład 8.1
Napisz program (blok anonimowy) w PL/SQL, który wypisze na ekran "Hello World!".
begin
dbms_output.put_line('Hello World!');
end;
Jak można łatwo zauważyć, że nic nie zostało wypisane na ekran. Powodem takiego zachowania jest domyślna konfiguracja narzędzi Oracle'a, które wypisywanie na ekran mają domyślnie wyłączone.
Przykład 8.2
Włącz wypisywanie na konsolę poprzez instrukcję SET SERVEROUTPUT ON i wykonaj popnownie blok anonimowy z Przykładu 8.1
set serveroutput on
begin
dbms_output.put_line('Hello World!');
end;
Oracle pozwala na skorzystanie z komendy EXECUTE (w skrócie EXEC) w celu wykonania pojedyńczej funkcji lub procedury PL/SQL, co pozwala nam zaoszczędzić czas na pisanie BEGIN oraz END.
Przykład 8.3
Wypisz na ekran Hello World! korzystając z komendy EXECUTE.
exec dbms_output.put_line('Hello World!');
Deklarowanie zmiennych
Zmienne w programach PL/SQL deklarujemy w bloku DECLARE dla bloków anonimowych lub bezpośrednio za sekcją nagłówkową w przypadku procedur oraz funkcji. (UWAGA: Są wyjątki od tej zasady).
Składnia do zadeklarowania zmiennej:
variable_name datatype [NOT NULL := value ];
Przykład 8.4
declare
l_name varchar2(20);
begin
l_name := 'Krystian';
dbms_output.put_line(l_name);
end;
Przykład 8.5
declare
l_emp_id number(6) := 1194;
l_salary number(6) := 4500;
begin
dbms_output.put_line('Pracownik ' || l_emp_id || ' zarabia ' || l_salary || ' PLN');
end;
Przykład 8.6
Zadeklaruj zmienną jako NOT NULL, ale nie przypisuj jej wartości.
declare
l_emp_id number(6) not null;
l_salary number(6) := 4500;
begin
dbms_output.put_line('Pracownik ' || l_emp_id || ' zarabia ' || l_salary || ' PLN');
end;
Rezultat:
Error report -
ORA-06550: line 2, column 12:
PLS-00218: a variable declared NOT NULL must have an initialization assignment
06550. 00000 - "line %s, column %s:\n%s"
*Cause: Usually a PL/SQL compilation error.
*Action:
Przykład 8.7
Popraw przykład 8.6 korzystajać ze słowa kluczowego DEFAULT.
declare
l_emp_id number(6) not null default 2244;
l_salary number(6) := 4500;
begin
dbms_output.put_line('Pracownik ' || l_emp_id || ' zarabia ' || l_salary || ' PLN');
end;
W celu pobrania pojedyńczej wartości z bazy danych i przypisanie jej do zmiennej możemy posłużyć się instrukcją SELECT INTO.
Przykład 8.8
declare
l_emp_id number(6) not null := 170;
l_salary number(6);
begin
select salary
into l_salary
from employees
where employee_id = l_emp_id;
dbms_output.put_line('Pracownik ' || l_emp_id || ' zarabia ' || l_salary || ' PLN');
end;
Przykład 8.9
Znajdź oraz wyświetl numer działu o nazwie IT.
declare
l_department_name varchar2(30) not null := 'IT';
l_department_id number(6);
begin
select department_id
into l_department_id
from departments
where department_name = l_department_name;
dbms_output.put_line('Numer dzialu ' || l_department_name || ' to ' || l_department_id);
end;
Korzystając z konstrukcji SELECT INTO można pobrać w tym samym czasie więcej niż jedną wartość z tabeli.
Przykład 8.10
declare
l_last_name varchar2(30) not null := 'Gietz';
l_employee_id number(6);
l_salary number(6);
begin
select employee_id, salary
into l_employee_id, l_salary
from employees
where last_name = l_last_name;
dbms_output.put_line(l_last_name || ' ma numer ' || l_employee_id || ' oraz zarabia ' || l_salary);
end;
Stałe w PL/SQL
PL/SQL pozwala również na deklaracje stałych. Stała jest to zmienna, która nie może zmieniać wartości.
Przykład 8.11
Zadeklaruj stałą liczba pi o wartości 3.14 i oblicz pole koła o promieniu 5.
declare
c_pi constant number(3,2) := 3.14;
l_radius number(1) := 5;
begin
dbms_output.put_line('Pole kola o promieniu ' || l_radius || ' wynosi ' || c_pi * l_radius * l_radius);
end;
Przykład 8.12
declare
c_constant constant number(3,2);
begin
dbms_output.put_line(c_constant);
end;
Rezultat:
ORA-06550: line 2, column 3:
PLS-00322: declaration of a constant 'C_CONSTANT' must contain an initialization assignment
ORA-06550: line 2, column 14:
Przykład 8.13
declare
c_constant constant number(3,2) := 100;
begin
c_constant := 200;
dbms_output.put_line(c_constant);
end;
Rezultat:
ORA-06550: line 4, column 3:
PLS-00363: expression 'C_CONSTANT' cannot be used as an assignment target
ORA-06550: line 4, column 3:
Typ rekordowy w Oracle
Typ rekordowy w Oracle przypomina obiekt lub wiersz z tabeli. Typ rekordowy posiada pola do przechowywania danych. Aby móc użyć typu rekordowego w Oracle najpierw musimy go zdefiniować.
Składnia definicji typu rekordowego:
TYPE record_type_name IS RECORD
(
first_column datatype,
second_column datatype,
...
);
Przykład 8.14
declare
type region is record
(
region_id number,
region_name varchar2(25)
);
l_region region;
l_region_id number not null := 1;
begin
select region_id, region_name
into l_region
from regions
where region_id = l_region_id;
dbms_output.put_line('Region Id: ' || l_region.region_id);
dbms_output.put_line('Region Name: ' || l_region.region_name);
end;
Należy pamiętać, że typ rekordowy nie musi odpowiadać liczbie kolumn w tabeli, a nawet może być całkowicie niezwiązany z żadną tabelą.
Przykład 8.15
declare
type emp is record
(
first_name varchar2(30),
last_name varchar2(30),
salary number,
info varchar2(100)
);
l_emp emp;
l_employee_id number not null := 100;
begin
select first_name, last_name, salary
into l_emp.first_name, l_emp.last_name, l_emp.salary
from employees
where employee_id = l_employee_id;
l_emp.info := 'Zostanie zwolniony';
dbms_output.put_line('Pracownik: ' || l_emp.first_name || ' '
|| l_emp.last_name || ' z pensja ' || l_emp.salary);
dbms_output.put_line(l_emp.info);
end;
Jeżeli chcemy wykorzystać typ rekordowy do pobrania danych z tabeli, nie musimy definiować wszystkich kolumn możemy w definicji posłużyć się %ROWTYPE.
Przykład 8.16
declare
l_region regions%rowtype;
l_region_id number not null := 2;
begin
select *
into l_region
from regions
where region_id = l_region_id;
dbms_output.put_line('Region Id: ' || l_region.region_id || ' '
|| ' Region Name: ' || l_region.region_name);
end;
Wyrażenia warunkowe w PL/SQL
PL/SQL wspiera jak większość języków programowania intrukcje warunkową IF ELSEIF ELSE (UWAGA: w kodzie źródłowym ELSEIF zapisujemy jako ELSIF).
Przykład 8.17
declare
l_salary number;
l_employee_id number := 100;
begin
select salary
into l_salary
from employees
where employee_id = l_employee_id;
if l_salary > 5000
then
dbms_output.put_line('Powyzej sredniej krajowej: ' || l_salary);
end if;
end;
Przykład 8.18
declare
l_salary number;
l_employee_id number := 200;
begin
select salary
into l_salary
from employees
where employee_id = l_employee_id;
if l_salary > 5000
then
dbms_output.put_line('Powyzej sredniej krajowej: ' || l_salary);
else
dbms_output.put_line('Ponizej sredniej krajowej: ' || l_salary);
end if;
end;
Przykład 8.19
declare
l_salary number;
l_employee_id number := 200;
begin
select salary
into l_salary
from employees
where employee_id = l_employee_id;
if l_salary < 3400
then
dbms_output.put_line('Ponizej sredniej krajowej: ' || l_salary);
elsif l_salary > 3400
then
dbms_output.put_line('Powyzej sredniej krajowej: ' || l_salary);
else
dbms_output.put_line('Srednia krajowa: ' || l_salary);
end if;
end;
Pętle w PL/SQL
Simple Loop
Najprostszą konstrukcją pętli w PL/SQLjest Simple Loop.
LOOP
instrukcje do wykonania
[EXIT]
[EXIT WHEN warunek]
END LOOP;
Tą pętle wykorzystujemy gdy nie wiemy ile razy chcemy wykonać pętle, ale jesteśmy pewni, że chcemy aby ciało pętli zostało wykonane conajmniej raz. Jeżeli pętla nie będzie zawierała słowa kluczowego EXIT lub EXIT WHEN z warunkiem zakończenia pętli, będzie ona działać w nieskończonść.
Przykład 8.20
declare
l_start_balance number := 2150;
l_end_balance number := 1000;
begin
loop
l_start_balance := l_start_balance - 100;
dbms_output.put_line('Biezacy balans: ' || l_start_balance);
if l_start_balance < l_end_balance
then
exit;
end if;
end loop;
end;
Przykład 8.21
declare
l_start_balance number := 2150;
l_end_balance number := 1000;
begin
loop
l_start_balance := l_start_balance - 100;
dbms_output.put_line('Biezacy balans: ' || l_start_balance);
exit when l_start_balance < l_end_balance;
end loop;
end;
While Loop
Innym rodzajem pętli jest pętla WHILE. W tej pętli nie mamy gwarancji, że ciał zostanie wykonane conajmniej raz.
WHILE warunek
LOOP
instrukcje
END LOOP;
Przykład 8.22
declare
l_number number := 1;
l_count number := 0;
begin
while l_count < 10
loop
if mod(l_number, 2) != 0
then
dbms_output.put_line('liczba nieparzysta: ' || l_number);
l_count := l_count + 1;
end if;
l_number := l_number + 1;
end loop;
end;
FOR Loop
FOR index IN [REVERSE] start .. koniec
LOOP
instrukcje
END LOOP;
Przykład 8.23
begin
for i in 1..10
loop
dbms_output.put_line('index: ' || i);
end loop;
end;
Przykład 8.24
begin
for i in reverse 1..10
loop
dbms_output.put_line('index: ' || i);
end loop;
end;
Pętla FOR wspiera również interacje po rekordach zwróconych przez instrukcje SELECT.
Przykład 8.25
begin
for region_rec in (select * from regions)
loop
dbms_output.put_line('Region Id: ' || region_rec.region_id);
dbms_output.put_line('Region Name: ' || region_rec.region_name);
dbms_output.put_line('');
end loop;
end;
Przykład 8.26
begin
for emp_rec in (select last_name, salary, department_id from employees where department_id in (10,30))
loop
dbms_output.put_line('Last Name: ' || emp_rec.last_name);
dbms_output.put_line('Salary: ' || emp_rec.salary);
dbms_output.put_line('Department Id: ' || emp_rec.department_id);
dbms_output.put_line('');
end loop;
end;
Kursory
W Oracle występują dwa rodzaje kursorów implicit oraz explicit. Kursory implicit są tworzone automatycznie, podczas wykonywania instrukcji DML w kodzie PL/SQL lub podczas wykonanie instrukcji SELECT, która zwraca tylko jeden rekord.
Przykład 8.27
declare
l_rows number;
begin
update employees2
set salary = salary + 1000
where department_id = 999;
--where department_id = 30;
if sql%notfound
then
dbms_output.put_line('Brak podwyzek');
elsif sql%found
then
l_rows := sql%rowcount;
dbms_output.put_line('Podwyzka dla ' || l_rows || ' pracownikow');
end if;
end;
Kursory typu explicit musimy zdefiniować samodzielnie oraz samodzielnie obsłużyć.
Przykład 8.28
declare
l_emp_rec employees%rowtype;
cursor emp_cur is
select *
from employees
where salary > 5000;
begin
open emp_cur;
fetch emp_cur into l_emp_rec;
dbms_output.put_line (l_emp_rec.first_name || ' ' || l_emp_rec.last_name
|| ' ' || l_emp_rec.salary);
close emp_cur;
end;
Jak widać powyższy kod zwrócił tylko jeden rekord, powodem takiego zachowania jest to, że kursory typu explicit powinny być przetwarzane w pętli.
Przykład 8.29
declare
l_emp_rec employees%rowtype;
cursor emp_cur is
select *
from employees
where salary > 5000;
begin
open emp_cur;
loop
fetch emp_cur into l_emp_rec;
exit when emp_cur%notfound;
dbms_output.put_line (l_emp_rec.first_name || ' ' || l_emp_rec.last_name
|| ' ' || l_emp_rec.salary);
end loop;
close emp_cur;
end;
Wyjątki
Przykład 8.30
begin
dbms_output.put_line('Rezultat: ' || 100 / 0);
exception
when zero_divide
then
dbms_output.put_line('Nie dziel przez zero');
end;
8.31
declare
l_count number := 0;
l_department_id number := 90;
still_has_employees exception;
begin
select count(*)
into l_count
from employees
where department_id = l_department_id;
if l_count > 0
then
raise still_has_employees;
end if;
exception
when still_has_employees
then
dbms_output.put_line('Dzial ma jeszcze pracownikow nie mozna go usunac');
end;
Przykład 8.32
begin
dbms_output.put_line('Rezultat: ' || 100 / 0);
exception
when others
then
dbms_output.put_line('Nie zdefiniowany wyjatek');
end;
Procedury
Przykład 8.33
create procedure pole_kola(radius in number)
is
c_pi constant number := 3.14;
begin
dbms_output.put_line('Rezultat: ' || c_pi * radius * radius);
end;
Wykonanie procedury:
exec pole_kola(5)
exec pole_kola(10)