3678


Spis treści

Wstęp

Literatura

  1. ORACLE SQL Language Reference Manual

  2. ORACLE PL/SQL User's Guide and Reference

  3. ORACLE SQL*Plus User's Guide and Reference

  4. Oracle9i Database Online Documentation

Wymagane oprogramowanie

System operacyjny: MS Windows NT4 / 2000 / XP

Baza danych: Oracle 8i / 9i

Edytor PL/SQL: PL/SQL Developer / SQL Navigator / inny analogiczny

Wprowadzenie

SQL - Structured Query Language - strukturalny język zapytań. Jest językiem unormowanym przez ANSI i ISO.

PL/SQL jest rozszerzeniem proceduralnym do języka SQL opracowanym przez Oracle. Może korzystać z większości poleceń SQL.

Środowisko uruchomieniowe PL/SQL może być zainstalowane na serwerze jak i w aplikacji developerskiej.

Motor PL/SQL akceptuje na wejściu bloki PL/SQL-owe. Przetwarza polecenie proceduralne natomiast polecenia SQL-owe przesyła na serwer.

0x01 graphic

Grupy poleceń należących do języka SQL:

Tworzenie bazy danych

Utworzenie skryptów generujących bazę danych - proces tworzenia bazy, ale na końcu zamiast tworzyć bazę wygenerować skrypty. Analiza utworzonych skryptów.

Pliki składające się na bazę danych i wykorzystywane podczas jej obsługi:

Użytkownik i prawa

Użytkownik, schemat

Tworzenie użytkownika:

CREATE USER użytkownik IDENTIFIED BY hasło;

Zmian parametrów użytkownika (np. hasła):

ALTER USER użytkownik IDENTIFIED BY hasło;

Usunięcie użytkownika:

DROP USER użytkownik [CASCADE];

Z każdym użytkownikiem bazy tworzony jest jego schemat, czyli zbiór obiektów należących do użytkownika. Do schematu mogą należeć następujące typy obiektów:

Dodatkowo aby tworzyć własne obiekty użytkownik musi posiadać prawa do przestrzeni tabel.

Nadanie praw do tworzenia obiektów w przestrzeni tabel:

ALTER USER użytkownik

QUOTA UNLIMITED ON przestrzeń_tabel;

Prawa

Aby użytkownik mógł cokolwiek wykonać musi posiadać stosowne prawa.

Prawa można podzielić na:

Rodzaj prawa obiektowego

Typ obiektu

TABELA

PERSPEKTYWA

SEKWENCJA

PROCEDURA

FUNKCJA

PAKIET

SELECT

X

X

X

INSERT

X

X

UPDATE

X

X

DELETE

X

X

ALTER

X

X

EXECUTE

X

INDEX

X

REFERENCES

X

Nadawanie praw:

GRANT prawo_systemowe TO użytkownik;

GRANT prawo_do_obiektu obiekt TO użytkownik;

Na przykład:

GRANT

CREATE SESSION,

CREATE TABLE,

CREATE VIEW,

CREATE TRIGGER,

CREATE PROCEDURE,

CREATE SEQUENCE

TO  użytkownik;

GRANT CREATE PUBLIC SYNONYM TO użytkownik;

Możliwe jest nadanie praw wszystkim użytkownikom:

GRANT prawo TO PUBLIC;

Role

Role to nazwa grupy praw, która może być przydzielona użytkownikowi lub innej roli.

Role ułatwiają administrowanie prawami.

Nadawanie ról:

GRANT rola TO użytkownik;

Istnieją role predefiniowanie:

GRANT CONNECT, RESOURCE TO użytkownik; -- pozwala nadać większość potrzebnych praw nowemu użytkownikowi

Odebranie praw

REVOKE prawo_systemowe FROM użytkownik;

REVOKE rola FROM użytkownik;

REVOKE prawo_do_obiektu obiekt FROM użytkownik;

Typy danych

NUMBER(ilość_znaków, skala)

Zmienna liczbowa (dziesiętna)

ilość_znaków = łączna liczba cyfr znaczących; od 1 do 38

skala = liczba cyfr po prawej stronie znaku dziesiętnego; od -84 do 127

Domyślnie przyjmowane są maksymalne wartości ilość_znaków i skala.

BINARY_INTEGER

Zmienna liczbowa (całkowita)

od -2^31 do 2^31

VARCHAR2(rozmiar)

Zmienna ilość znaków, maksymalnie < rozmiar > znaków

CHAR(rozmiar)

Stała ilość <rozmiar> znaków

LONG

Ciągi znaków do 2 GB (231 -1 bajtów)

Przestarzały typ, preferowana zmiana na typ LOB

DATE

Data od 01-01-4712 BC do 31-12-9999 AD

Zawiera zarówno datę jak i czas z dokładnością

CLOB

character large object - zmienna typu LOB, zapisywana w trybie znakowym

do 4 GB

BLOB

binary large object - zmienna typu LOB, zapisywana w trybie binarnym

do 4 GB

BOOLEAN

wartość logiczna, przyjmuje jedną z wartości: TRUE, FALSE, NULL

Konwersja typów

Z typu

Do typu

char

number

date

char

TO_NUMBER( tekst [, format])

TO_DATE (tekst [, format])

number

TO_CHAR (numer [, format])

TO_DATE (numer, `J')

date

TO_CHAR (data [, format])

niemożliwa

Format liczb

Format

Opis

9

Cyfra nieobowiązkowa

0

Cyfra obowiązkowa

.

Kropka dziesiętna

,

Przecinek dziesiętny

MI

Znak minus z prawej strony (dla wartości ujemnych)

D

Domyślny znak dziesiętny

G

Domyślny separator tysięcy

Format zapisu daty i czasu

Format

Opis

SCC lub CC

Wiek. Jeśli użyto S, to daty ujemne są poprzedzone przez BC

YYYY lub SYYYY

Rok. Jeśli użyto S, to daty ujemne są poprzedzone przez BC

Zamiennie można użyć liter `R'

YYY lub YY lub Y

Ostatnie 3, 2, 1 cyfry roku

IYYY, IYY, IY, I

4-, 3-, 2-, 1- cyfrowy rok zgodnie ze standardem ISO

SYEAR lub YEAR

Rok słownie. Jeśli użyto S, to daty ujemne są poprzedzone przez BC

BC lub AD

Oznacznik BC/AD

Q

Kwartał roku

MM

Miesiąc dwucyfrowo

MONTH

Nazwa miesiąca uzupełniona odstępami do dziewięciu znaków

MON

Trzyliterowy skrót nazwy miesiąca

RM

Miesiąc cyframi rzymskimi

WW lub W

Tydzień roku lub miesiąca

DDD, DD lub D

Dzień roku, miesiąca lub tygodnia

DAY

Nazwa dnia tygodnia uzupełniona odstępami do dziewięciu znaków

DY

Trzyliterowy skrót nazwy dnia tygodnia

J

Dzień wg kalendarz juliańskiego; liczba dni do 31-12-4713 p.n.e.

HH, HH12 lub HH24

Godzina dnia: wg lokalnych ustawien, 1-12, lub 0-23

MI

Minuta (0-59)

SS

Sekunda (0-59)

SSSSS

Liczba sekund od północy (0-86399)

Przykłady:

select ... from dual;

wynik

to_number('10,25')

liczba: 10,25

to_number('10.25','99.99')

liczba: 10,25

to_date('01-01-2005','DD-MM-YYYY')

data: 01-01-2005

to_date(2453471,'J')

data: 10-04-2005

to_char(123.40)

tekst: `123,4'

to_char(123.40,'0999.00')

tekst: `0123.40'

to_char(123456.78,'999G999D00')

tekst: `123.456,78'

to_char(sysdate,'DD-MM-RRRR DAY HH24:MI:SS')

tekst: `10-04-2005 CZWARTEK 23:15:34'

Funkcje SQL

Funkcja

Opis

Przykład

Funkcje znakowe

CHR(numer)

Znak o podanym kodzie

CHR(65) = `A'

ASCII(tekst)

Zwraca kod ASCII pierwszej litery w podanym ciągu znaków

ASCII(`A') = 65

LENGTH(tekst)

Długość tekstu (liczba znaków)

LENGTH(`aaa') = 3

LOWER(tekst)

Zamienia znaki na małe

LOWER(`AbC') = `abc'

UPPER(tekst)

Zamienia znaki na duże

UPPER(`AbC') = `ABC'

INITCAP(tekst)

Pierwszą literę w wyrazie zamienia na dużą a pozostałe na małe

INITCAP(`AlA Ma KOTa') = `Ala Ma Kota'

LPAD(tekst,n,uzup)

Uzupełnia tekst z lewej strony do n znaków tekstem uzup

LPAD(`aaa',5,'X') = `XXaaa'

RPAD(tekst,n,uzup)

Uzupełnia tekst z prawej strony do n znaków tekstem uzup

RPAD(`aaa',5,'X') = `aaaXX'

RTRIM(tekst [,zbior])

Usuwa z tekstu od prawej strony wszystkie znaki aż do napotkania znaku niewymienionego w zbior.

Jeżeli nie zostanie podany zbiór, to usuwane są wszystkie spacje.

RTRIM(`cdecaba','ab') = `cdec'

RTRIM(`aaa ') = `aaa'

LTRIM(tekst [,zbior])

Usuwa z tekstu od lewej strony wszystkie znaki aż do napotkania znaku niewymienionego w zbior.

Jeżeli nie zostanie podany zbiór, to usuwane są wszystkie spacje.

LTRIM(`aabcdec','ab') = `cdec'

LTRIM(` aaa') = `aaa'

REPLACE(tekst,t_z,t_na)

Zamienia w tekst wszystkie wystąpienia ciągów t_z na ciągi t_na

REPLACE(`xx / xx = 1','xx','22') = '22 / 22 = 1'

SUBSTR(tekst, m [,n])

Przekazuje n znaków z ciągu tekst począwszy od pozycji m (włącznie).
Jeśli m jest ujemne, odliczanie rozpoczyna się od końca ciągu tekst.

Jeśli n pominięto, przekazywane są wszystkie znaki od pozycji m.

SUBSTR(`abcdef',3,2) = `cd'

SUBSTR(`abcdef',3) = `cdef'

SUBSTR(`abcdef',-3,2) = `de'

INSTR(

tekst1,

tekst2,

[,n [,m]]

)

Zwraca pozycję m-tego wystąpienia tekst2 w tekst1, jeżeli szukanie rozpoczęto od pozycji n.

Domyślnie n = 1 (szukanie od początku)

Domyślnie m = 1 (szukanie pierwszego wystąpienia)

INSTR(`abcdc','c') = 3

INSTR(`abcdc','c',4) = 5

INSTR(`abcdc','c',1,2) = 5

Funkcje na datach

data + liczba

data - liczba

Dodaje / odejmuje do / od daty podaną liczbę dni

'01-01-2005' - 1 = '31-12-2004'

data - data

Przekazuje liczbę dni od pierwszej daty do drugiej daty

'09-01-2005' - '01-01-2005' = 9

MONTHS_BETWEEN(d1, d2)

Przekazuje liczbę miesięcy między d1 i d2. Wynik może być dodatni albo ujemny. Wynik nie musi być liczbą całkowitą. Ułamek odpowiada części miesiąca.

MONTHS_BETWEEN

('01-01-2005','10-04-2005') =

-3,29032258064516

ADD_MONTHS(data, n)

Dodaje n kalendarzowych miesięcy do daty. Liczba n musi być całkowita, ale może być ujemna.

ADD_MONTHS('31-01-2005',1) =

'28-02-2005'

NEXT_DAY(data, 'znak')

Znajduje następny dzień po dacie, który jest dniem tygodnia wskazanym przez 'znak'. 'znak' może być liczbą lub nazwą dnia tygodnia.

NEXT_DAY('10-04-2005','NIEDZIELA') =

'17-04-2005'

LAST_DAY(data)

Znajduje datę ostatniego dnia miesiąca zawierającego datę.

LAST_DAY('10-04-2005') = '30-04-2005'

ROUND(data[,'fmt'])

Przekazuje zaokrąglenie daty do jednostki określonej przez model formatu 'fmt'. Jeśli nie podano 'fmt' zaokrąglenie następuje do najbliższej pełnej daty.

ROUND(

to_date('20-04-2005','DD-MM-RRRR'),

'MM') =

'01-05-2005'

TRUNC(data[, 'fmt'])

Przekazuje obcięcie daty do jednostki określonej przez model formatu 'fmt'. Jeśli nie podano 'fmt' obcięcie nastepuje do najbliższej pełnej daty.

TRUNC(

to_date('20-04-2005','DD-MM-RRRR'),

'MM') =

'01-04-2005'

SYSDATE

Zwraca aktualną datę i czas

Funkcje numeryczne

ABS(n)

Zwraca wartość bezwzględną

ABS(-1) = 1

CEIL(n)

Zwraca najmniejszą liczbę całkowitą większą lub równą n

CEIL(4.8) = 5

FLOOR(n)

Zwraca największą liczbę całkowitą mniejszą lub równą n

FLOOR(4.8) = 4

MOD(m, n)

Przekazuje resztę z dzielenia m przez n

MOD(5, 2) = 1

POWER(m, n)

Podnosi m do potęgi n

POWER(3, 4) = 81

SQRT(n)

Pierwiastek kwadratowy z liczby n

SQRT(9) = 3

ROUND(e, [n])

Przekazuje zaokrąglenie e do n miejsc po przecinku.
Jeśli pominięto n, jest to zaokrąglenie do zera miejsc po przecinku.
Jeśli n jest ujemne, zaokrągla się cyfry na lewo od przecinka dziesiętnego.

ROUND(16.89) = 17

ROUND(16.89,1) = 16,9

ROUND(16.89,-1) = 20

TRUNC(e, [n])

Przekazuje obcięcie e do n miejsc po przecinku.
Jeśli pominięto n, jest to obcięcie do zera miejsc po przecinku.
Jeśli n jest ujemne, obcina się cyfry na lewo od przecinka dziesiętnego.

TRUNC(16.89) = 16

TRUNC (16.89,1) = 16,8

TRUNC (16.89,-1) = 10

SIGN(n)

Zwraca 0 jeżeli n jest równe 0.

Zwraca -1 jeżeli n jest mniejsze od 0.

Zwraca 1 jeżeli n jest większe od 0.

SIGN(-10) = -1

Funkcje inne

NVL(x, y)

Jeśli x nie jest NULL, to przekazuje x.
Jeśli x jest NULL, to przekazuje y.

NVL(10,1) = 10

NVL(null, `aaa') = `aaa'

DECODE(e, s1, r1 [, s2, r2,...,] [, defautl])

Jeśli e = s1, to przekazuje r1.
Jeśli e = s2, to przekazuje r2.
...
w przeciwnym przypadku przekazuje default (jeśli nie podano default, to przekazuje NULL).

DECODE(`x','a',1,'x',2,3) = 2

DECODE(`b','a',1,'x',2,3) = 3

GREATEST(x1 [,x2]...)

Wybiera największą wartość z x1, x2, ...

GREATEST(10,9,12) = 12

LEAST(x1 [,x2]...)

Wybiera najmniejszą wartość z x1, x2, ...

LEAST(10,9,12) = 9

USER

Zwraca nazwę użytkownika

Logika trójwartościowa

Wyrażenie: wynik = NOT <wartość>

Wynik

Wartość

TRUE

FALSE

UNKNOWN

FALSE

TRUE

UNKNOWN

Wyrażenie: wynik = <wartość1> AND <wartość2>

Wynik

Wartość1

TRUE

FALSE

UNKNOWN

Wartość2

TRUE

TRUE

FALSE

UNKNOWN

FALSE

FALSE

FALSE

FALSE

UNKNOWN

UNKNOWN

FALSE

UNKNOWN

Wyrażenie: wynik = <wartość1> OR <wartość2>

Wynik

Wartość1

TRUE

FALSE

UNKNOWN

Wartość2

TRUE

TRUE

TRUE

U TRUE

FALSE

TRUE

FALSE

UNKNOWN

UNKNOWN

TRUE

UNKNOWN

UNKNOWN

Obiekty bazy danych (elementy DDL)

Tabela (table)

Tabela to obiekt, w którym fizycznie przechowywane są dane.

Informacje o tabelach w bazie mogą zostać odczytane z perspektyw:

Tworzenie:

CREATE TABLE [schemat.]tabela

({kolumna typ [DEFAULT wyrażenie] [table_constraint]} [,{...}])

[własności_fizyczne_tabeli]

[inne_własności_tabeli]

;

schemat

schemat, w którym zostanie utworzona tabela, określa właściciela tabeli, domyślnie schemat, do którego należy użytkownik tworzący tabelę

tabela

Nazwa tworzonej tabeli

kolumna

Nazwa kolumny

typ

typ danych w kolumnie

wyrażenie

Domyślna wartość pola w kolumnie, ustawiana jeżeli podczas wstawiania rekordu do tabeli wartości dla danej kolumny nie zostanie określona.

table_constraint

ograniczenie na wartości w kolumnie, przykłady:

NOT NULL

UNIQUE

PRIMARY KEY

CHECK (wyrażenie)

...

własności_fizyczne­_tabeli

informacje o położeniu tabeli w plikach bazy danych, sposobie rozszerzania przestrzeni dla tabeli, ...

inne_własności_tabeli

pozostałe właściwości tabeli

między innymi treść zapytania, którego wynikiem można wypełnić zakładaną tabelę

Przykład:

CREATE TABLE pracownicy(

nr_pracownika NUMBER NOT NULL PRIMARY KEY,

imie CHAR(15) NOT NULL CHECK (imie = UPPER(imie)),

nazwisko CHAR(25) NOT NULL CHECK (nazwisko = UPPER(nazwisko)),

nr_wydzialu NUMBER (3) NOT NULL

);

Usuwanie tabeli:

DROP TABLE [schemat.]tabela [CASCADE CONSTRAINTS];

Zmiana tabeli:

Umożliwia zmianę parametrów tabeli

ALTER TABLE [schemat.]tabela

[inne_właściwości_tabeli]

;

pozwala:

Więzy (constraint)

Więzy dbają o spójność logiczną danych na poziomie bazy danych, niezależnie od kontroli spójności na poziomie aplikacji.

Typy więzów:

Zmiana zestawu więzów:

ALTER TABLE [schemat.]tabela

{ ADD CONSTRAINT nazwa definicja [ENABLE | DISABLE] [USING INDEX]

| MODIFY CONSTRAINT nazwa [ENABLE | DISABLE] [USING INDEX]

| RENAME CONSTRAINT stara_nazwa TO nowa_nazwa

| DROP CONSTRAINT nazwa [CASCADE]

};

definicja oznacza:

{ [NOT] NULL

| UNIQUE (kolumny)

| PRIMARY KEY (kolumny)

| FOREIGN KEY (kolumny) REFERENCES tabela (kolumny) [ON DELETE CASCADE]

| CHECK ( warunek )

}

Opcja USING INDEX może być wykorzystana dla klucza głównego i unikalnego.

Prespektywa (view)

Definiowana prezentacja pobierana z tabel lub innych perspektyw.

Tworzenie:

CREATE [OR REPLACE] VIEW [schemat.]perspektywa

AS

SELECT ...;

Usuwanie:

DROP VIEW [schemat.]perspektywa;

Sekwencja (sequence)

Generatory kolejnych liczb zapewniające unikalność numerów przy dostępie równoległym.

Informacje o sekwencjach w bazie mogą zostać odczytane z perspektyw:

CREATE SEQUENCE [schemat.]sekwencja

[INCREMENT BY liczba]

[START WITH wartość_początkowa]

[CYCLE/NOCYCLE];

Odczyt wartości sekwencji realizujemy za pomocą następującego zapytania:

sekwencja.NEXTVAL

Obecną wartość licznika bez zwiększania jego wartości można odczytać następująco:

sekwencja.CURRVAL

Usuwanie: DROP SEQUENCE

Zmiana: ALTER SEQUENCE

Synonim (synonym)

Aliasy (alternatywne nazwy) do innych obiektów w bazie.

Do wykorzystania na przykład aby móc pominąć wskazywanie nazwy schematu przy odwołaniu do tabeli.

Tworzenie:

CREATE [OR REPLACE] [PUBLIC] SYNONYM [schemat.]nazwa FOR [schemat.]obiekt;

Usunięcie:

DROP [PUBLIC] SYNONYM [schemat.]nazwa;

OR REPLACE - powoduje, że polecenie nie spowoduje błędu jeżeli dany synonim już istnieje.

PUBLIC - utowrzony symonim będzie dostępny dla wszystkich użytkowników. W przeciwnym przypadku dostępny jest tylko wewnątrz schematu.

Indeks (index)

Struktury związane z tabelami, mają na celu przyspieszenie wykonywania zapytań. Odbywa się to kosztem obszaru pamięci potrzebnego na zapamiętanie indeksów oraz spowolnienia modyfikacji danych w indeksowanych kolumnach.

CREATE [UNIQUE] INDEX [schemat.]indeks

ON [schemat.]tabela (atrybut [,…])

[NOSORT]

[własności fizyczne indeksu]

;

UNIQUE - wymusza unikalność wartości w kolumnie, której indeks dotyczy.

NOSORT - wskazuje, że rekordy w tablicy są już posortowane

Informacje o istniejących indeksach przechowywane są w tabelach:

Procedura (procedure)

Składowane w bazie danych fragmenty kodu, nie zwracają wyniku

CREATE [OR REPLACE] PROCEDURE [schemat.]procedura (p1 IN typ [, ...])

AS

treść;

Funkcja (function)

Składowane w bazie danych fragmenty kodu, zwracają wynik

CREATE [OR REPLACE] FUNCTION [schemat.]funkcja (p1 IN typ [, ...])

RETURN typ_wyniku

AS

treść;

Pakiet (package)

Składowane w bazie danych fragmenty kodu, zbiory procedur i funkcji.

Każda funkcja i procedura wywoływana z pakietu musi być poprzedzona nazwą pakietu i znakiem `.'.

CREATE [OR REPLACE] PACKAGE [schemat.]pakiet

AS

wykaz nagłówków funkcji i procedur;

END pakiet;

CREATE [OR REPLACE] PACKAGE BODY [schemat.]pakiet

AS

wykaz ciał funkcji i procedur;

END pakiet;

Wyzwalacz (trigger)

Programy składowane w bazie, uruchamiane automatycznie w momencie wystąpienia określonego zdarzenia.

Triggery mogą być wywoływane przy okazji zaistnienia zdarzeń typu:

Tworzenie triggera dla zdarzeń DML:

CREATE [OR REPLACE] TRIGGER [schemat.]trigger

{BEFORE | AFTER}

{ INSERT

| DELETE

| UPDATE OF kolumna [, ...]

} [OR {...}]

ON [schemat.]tabela

[FOR EACH ROW]

[WHEN (warunek)]

pl/sql_kod

BEFORE

trigger wywoływany przed wykonaniem zdarzenia

AFTER

trigger wywoływany po wykonaniu zdarzenia

INSERT|DELETE|UPDATE

rodzaj zdarzenia DML, z którego wykonaniem jest związany trigger;

jeden trigger może dotyczyć kilku rodzajów zdarzeń - wtedy zdarzenia łączy się słowem kluczowym OR

FOR EACH ROW

określa, że trigger ma być wykonywany dla każdego wiersza

WHEN

określa warunek, który musi być spełniony aby został uruchomiony trigger;

w warunku można używać słów kluczowych OLD.nazwa_pola i NEW.nazwa_pola odnoszących się starej i nowe wartości wskazanego pola

pl/sql_kod

treść kody wykonywanego w triggerze;

nie można w nim używać poleceń typu DCL (COMMIT, ROLLBACK, ...);

w treści można używać wyrażeń :OLD.nazwa_pola, :NEW.nazwa_pola odwołujące się do starej i nowej wartości pól

Zapytania (SELECT)

Polecenie SELECT służy do wykonywania zapytań do bazy danych.

Uzyskane dane mogą zostać wykorzystane do:

Poza tym polecenie SELECT wykorzystywane jest do:

SELECT definicja_atrybutów_relacji

FROM źródło_danych

[WHERE ograniczenie_selekcji_krotek]

[GROUP BY atrybuty_grupujące [HAVING warunek_na_grupy]]

[ORDER BY uporządkowanie_krotek]

;

SELECT (selekcja atrybutów relacji)

Jako definicja atrybutów relacji możne wystąpić:

select * from wydatki;

select data, opis from wydatki;

Jak specyfikacja atrybutów może wystąpić:

select data, opis from wydatki;

select trunc(sysdate)-data, opis, kwota*100||' groszy'

from wydatki;

select `Dziś mamy:', trunc(sysdate), `Wydatek z dnia:', data, opis

from wydatki;

select sysdate from dual;

select exp(ln(1000)/3) from dual;

select data, opis, (select opis from osoby where symbol_kto=symbol)

from wydatki;

Definiując atrybuty do pokazania można ograniczyć zakres pokazywanych krotek tylko do wartości nieunikalnych (poprzez słowo kluczowe DISTINCT lub UNIQE):

select DISTINCT(rodzaj)

from wydatki;

Poszczególne specyfikacje atrybutów można nazwać (określić aliasy). Aliasy pokazywane są w wynikach zapytania jako nagłówki kolumn. Aliasy atrybutów mogą być wykorzystane podczas uporządkowania krotek relacji.

select trunc(sysdate)-data jak_dawno, opis, kwota

from wydatki

order by jak_dawno;

ORDER BY (porządkowanie krotek relacji)

Porządkowanie krotek relacji może być wykonywane w oparciu o:

select sysdate-data jak_dawno, opis, kwota

from wydatki

order by sysdate-data;

select sysdate-data jak_dawno, opis, kwota

from wydatki

order by jak_dawno;

select sysdate-data jak_dawno, opis, kwota

from wydatki

order by 1;

select sysdate-data jak_dawno, opis, kwota

from wydatki

order by symbol_kto;

Porządkowanie może odbywać się według wielu atrybutów. Można określić kolejność sortowania według każdego z atrybutów oraz określić umiejscowienie krotek o wartości pustej.

ORDER BY

{ atrybut | pozycja_atrybutu | alias_atrybutu }

[ ASC | DESC ]

[ NULLS FIRST | NULLS LAST ]

[, ...]

select sysdate-data jak_dawno, opis, kwota, symbol_kto

from wydatki

order by symbol_kto, jak_dawno desc, 2;

select * from platnosci

order by konto desc nulls last;

WHERE (selekcja krotek relacji)

W celu ograniczenia pokazywanych krotek relacji należy określić warunki jakie musi spełniać krotka, aby została pokazana. Pokazywane są te krotki, dla których zdefiniowany warunek jest prawdziwy. W warunku można odwoływać się do wartości pól dla:

select sysdate-data jak_dawno, opis, kwota

from wydatki

where sysdate-data > 10;

select sysdate-data jak_dawno, opis, kwota

from wydatki

where symbol_kto <> symbol_za_kogo;

Budując warunki można wykorzystywać:

Maska to ciąg znaków, który jest dopasowywany do wartości pola. W masce wykorzystywane są znaki wieloznaczne:

W pierwszej kolejności uwzględniany jest operator AND następnie OR.

W ramach warunku można wykonywać podzapytania i następnie odwoływać się do wyników tych zapytań. Takie rozwiązanie ma sens jeżeli w podzapytaniu wykorzystujemy jakiś element krotki z podzapytania nadrzędnego.

Wyniki zapytania podrzędnego można wykorzystać do:

select w.*

from wydatki w

where w.kwota > (select avg(w2.kwota) from wydatki w2);

Można stosować operatory:

Stosowany jest operator [NOT] EXISTS

select w.* from wydatki w

where exists (select * from platnosci p

where w.id_platnosci = p.id and p.konto is not null);

FROM (Określanie źródła danych)

Jako źródło danych można podawać:

select * from wydatki;

select * from dane;

select .*

from (select kwota, opis from wydatki) wyd2;

Dla każdego źródła danych można określić alias, przy pomocy którego można rozróżnić źródła w innych elementach zapytania.

select w.opis, w.kwota, p.rodzaj

from wydatki w, platnosci p

where w.id_platnosci = p.id;

Poszczególne źródła oddzielane są przecinkami.

Relacje pomiędzy źródłami określane są poprzez warunek WHERE.

Domyślnie tworzony jest iloczyn kartezjański z obu źródeł:

TAB_1:

ID

KTO

1

Ala

2

Stefan

TAB_2:

KTO

CO

Ala

kwiaty

Stefan

piwo

select * from TAB_1 t1, TAB_2 t2;

t1.ID

t1.KTO

t2.KTO

t2.CO

1

Ala

Ala

kwiaty

1

Ala

Stefan

piwo

2

Stefan

Ala

kwiaty

2

Stefan

Stefan

piwo

select * from TAB_1 t1, TAB_2 t2 where t1.KTO = t2.KTO;

t1.ID

t1.KTO

t2.KTO

t2.CO

1

Ala

Ala

kwiaty

2

Stefan

Stefan

piwo

Połączenie zewnętrze stosuje się jeżeli w jednym ze źródeł może nie być wartości wiążącej z drugim źródłem, a chcemy pokazać wartości nie powiązane.

select *

from platnosci p, konta k

where p.konto = k.id(+);

(+) stawiamy przy polu ze źródła, w którym nie występuje odpowiednik. W wyniku zapytania zamiast danych z tego źródła pokazane zostaną wartości puste. Czyli pokazujemy wszystko ze źródła bez „plusa” i doczytujemy co nam się uda ze źródła z „plusem”.

GROUP BY (grupowanie krotek)

Grupowanie pozwala na zebranie wielu krotek w oparciu o określone atrybut i wyznaczenie dla danej grupy pewnych wielkości. Dodatkowo słowo kluczowe HAVING pozwala na ograniczenie zapytania tylko do grup spełniających określony warunek.

Jeżeli w zapytaniu użyte są słowa kluczowe ORDER BY, to po SELECT można wymienić:

select rodzaj,

count(*) ilość, avg(kwota) średnio, min(kwota) min, max(kwota) max

from wydatki

group by rodzaj

having count(*)>1;

Łączenie zapytań

Możliwe jest wykonywanie operacji na zbiorach wyników wielu zapytań. Elementami zbioru są krotki zapytań.

Dostępne operacje:

Aby działania były możliwe typy atrybutów składowych zapytań muszą być takie same.

select 'OPIS_WYDATKU', 'KWOTA' from dual

UNION

select opis, to_char(kwota) from wydatki;

DCL - Data Control Language

Każde polecenie DML powoduje wprowadzanie zmian do bazy i równocześnie zapisanie stanu danych sprzed zmian w odrębnym obszarze bazy nazywanym segmentem wycofania (rollback segment).

Zatwierdzenie transakcji powoduje oznaczenie danych w bazie jako obowiązujących.

Wycofanie zmian, w tym całej transakcji, powoduje przepisanie danych z segmentu wycofania do bazy.

Każde polecenie DDL powoduje automatyczne zatwierdzenie transakcji przed i po swoim wykonaniu, niezależnie od tego czy się powiedzie.

Prawidłowe wylogowanie z bazy powoduje zatwierdzenie transakcji.

Zakończenie sesji bazodanowej na skutek błędu powoduje wycofanie transakcji.

COMMIT

kończy aktualną transakcję i zatwierdza zmiany. Powoduje, że zmiany są widoczne w innych transakcjach.

ROLLBACK

kończy aktualną transackję i wycofuje zmiany wykonane w transakcji

SAVEPOINT

oznaczenie momentu w transakcji, do którego mogą zostać wycofane zmiany

w transakcji można określić określoną liczbę savepointów

ROLLBACK TO SAVEPOINT nazwa_savepoint

wycofuje zmiany wprowadzone od momentu oznaczonego jako savepoint o podanej nazwie

nie kończy transakcji

SET TRANSACTION READ ONLY

musi być wykonane jako pierwsze polecenie w transakcji

oznacza, że w transakcji mogą być wykonane tylko zapytania; system nie pozwoli na wykonanie poleceń DML

DML - Data Manipulation Language

Wstawianie krotek (INSERT)

Polecenie INSERT pozwala na dodanie do tabeli pojedynczego wiersza lub wielu wierszy (uzyskanych w wyniku zapytania).

Podanie pojedynczego wiersza:

INSERT INTO tabela [(columna [, columna] ...)]

VALUES (wyrazenie [,wyrazenie ...])

;

np.:

INSERT INTO rodzaje_wydatkow (rodzaj, opis) VALUES ('X', 'Różne');

Jeżeli nie zostaną podane nazwy kolumn po nazwie tabeli, to system będzie wartości przypisywał do kolumn w kolejności wynikającej z wewnętrznego uporządkowania kolumn. Bezpieczniej i czytelniej jest zawsze wskazywać listę kolumn.

Do nie wymienionych kolumn wstawiane będą wartości NULL.

Dodanie wielu wierszy:

INSERT INTO tabela [(columna [, columna] ...)]

zapytanie

;

create table wydatki_suma
( stan_na date,
dzien date,
kwota number
)
;

insert into wydatki_suma(stan_na, dzien, kwota)
select sysdate, data, sum(kwota) from wydatki
group by data
;

Zmiana wartości pól (UPDATE)

UPDATE tabela

SET kolumna = {wartosc | (zapytania)} [,kolumna = {wartosc | (zapytania)}] ...

WHERE warunek

;

UPDATE tabela

SET (kolumna [, kolumna] ...) = (zapytanie)

WHERE warunek

;

Jeżeli wartość kolumny wyliczana jest na podstawie zapytania, to zapytanie musi zwrócić jeden wiersz. Jeżeli zapytanie nie zwróci żadnego wiersza, to do kolumn zostanie przypisana wartość NULL.

Modyfikowane będą krotki spełniające warunek podany po słownie WHERE.

UPDATE wydatki

SET data = to_date('10-03-2005','DD-MM-RRRR')

WHERE id in (5,6);

UPDATE wydatki w
SET w.opis = w.opis || ' - zmiana',
w.kwota = (SELECT avg(w2.kwota)

FROM wydatki w2

WHERE w2.rodzaj = w.rodzaj)
WHERE w.rodzaj NOT IN ('I')
;

Usuwanie krotek (DELETE)

DELETE FROM tabela

WHERE warunek

;

Polecenie usuwa krotki spełniające warunek podany po słownie WHERE.

Blokowanie krotek (SELECT ... FOR UPDATE)

W wyniku wykonania polecenia DML blokowane są modyfikowane krotki. Ich odblokowanie następuje po zatwierdzeniu lub wycofaniu transakcji.

Zablokowanie krotki może zostać również wykonane poprzez zapytanie (SELECT ...) z klauzulą FOR UPRADE.

zapytanie [FOR UPDATE [{NOWAIT | WAIT ile_sekund}]]

NOWAIT - jeżeli zablokowanie się nie uda, od razu pokazywany jest komunikat od błędzie

WAIT ile_sekund - próbuje zablokować krotki przez podaną ilość sekund. Jeżeli po tym czasie się nie uda, to wyświetla komunikat o błędzie.

Jeżeli żadna z opcji nie zostanie wskazana, to domyślnie system czeka aż zapytanie będzie możliwe (bez ograniczenia czasowego!).

Język PL/SQL

Komentarze:

-- komentarz dotyczący jednej linii

/* komentarz

dotyczący

wieli

linii*/

Struktura programu

Podstawową jednostką programu są bloki.

Struktura bloku:

[DECLARE

deklaracje_zmiennych_stałych_kursorów

]

BEGIN

część_wykonywalna_programu

[EXCEPTION

obsługa­_wyjątków

]

END;

Bloki mogą być zagnieżdżone.

W programie PL/SQL mogą być użyte polecenie DML i DCL.

Zmienne, stałe

Typy zmiennych:

0x01 graphic
Źródło: Dokumentacja ORACLE 9i

Deklaracja zmiennej / stałej:

nazwa_zmiennej typ [CONSTANT] [NOT NULL] [:=wartosc];

CONSTANT - zmienną traktujemy jako stałą

NOT NULL - zmienna nie może mieć wartości pustej

Możliwe jest zadeklarowanie zmiennej, której typ określany na podstawie typów kolumna tabel bazodanowych.

Zmienna wierszowa, przechowująca cały wiersz tabeli:

nazwa_zmiennej tabela%ROWTYPE;

Zmienna, przechowująca pojedyncze pole tabeli:

nazwa_zmiennej tabela.kolumna%TYPE;

Przypisanie wartości zmiennej

zmienna := wartosc;

:zmienna

&zmienna

Polecenie SELECT ... INTO ...

Pozwala odczytać dane z bazy i zapisać ich wartość do zmiennych.

Przykład:

DECLARE

z_kwota number;

z_data date;

BEGIN

SELECT kwota, data

INTO z_kwota, z_data

FROM wydatki

WHERE id = 1;

END;

Zapytanie musi zwrócić jeden wiersz. W przeciwnym przypadku zostanie wywołany jeden z wyjątków:

Wyświetlenie danych na ekran

Wyświetlanie na ekran:

dbms_output.put_line(tekst);

W aplikacji SQL*Plus konieczne jest dodatkowo wywołanie polecenia:

SET SERVEROUTPUT ON [SIZE rozmiar]

Instrukcja warunkowa (IF)

IF warunek THEN

instrukcje;

[ELSIF warunek THEN

instrukcje; ]

[ELSIF ...]

[ELSE

instrukcje;]

END IF;

Instrukcja CASE

CASE wyrazenie

WHEN wartosc_1 THEN instrukcje_1;

WHEN wartosc_2 THEN instrukcje_2;

[...]

[ELSE instrukcje_else;]

END CASE;

Pętla LOOP

LOOP

[instrukcje;]

{EXIT | EXIT WHEN warunek;}

[instrukcje;]

END LOOP;

Pętla wykonywana jest do momentu natrafienie na polecenie EXIT lub EXIT WHEN ze spełnionym warunkiem przerwania pętli.

Pętla WHILE LOOP

WHILE warunek LOOP

instrukcje;

END LOOP;

Pętla jest wykonywana dopóki warunek jest prawdziwy.

Pętla FOR

FOR licznik IN [REVERSE] dolna_granica..gorna_granica LOOP

instrukcje;

END LOOP;

Instrukcja GOTO

...

GOTO etykieta;

...

<etykieta>

...

Wykorzystanie instrukcji GOTO nie powinno być stosowane nadmiernie. Zawsze można je zastąpić przez inne instrukcje.

Istnieje szereg ograniczeń na miejsce, w które GOTO może przekazywać sterowanie. Nie może spowodować przejścia do fragmentu programu:

Instrukcja pusta

Czasami przydaje się instrukcja, która nic nie wykonuje. Jest używana tam, gdzie składnia wymaga użycia instrukcji a programista nie chce tam nic wykonywać.

NULL;

np.:

...

EXCEPTION

WHEN ZERO_DIVIDE THEN

ROLLBACK;

WHEN VALUE_ERROR THEN

INSERT INTO errors VALUES ...

COMMIT;

WHEN OTHERS THEN

NULL;

END;

Kursory

Kursor to identyfikator wskazujący na przestrzeń w której przechowywane są wyniki zapytań. Poprzez kursor można odwoływać się tej przestrzeni.

Wyróżniane są kursory:

Kursor jawny musi zostać zadeklarowany. Zadeklarowanie nie powoduje wykonania zapytania. Dzięki temu zapytania mogą być sparametryzowane - uzależnione od innych zmiennych, których wartość jest ustawiana w programie.

Deklaracja:

CURSOR nazwa IS zapytanie;

--kursor bez parametrów

CURSOR nazwa (parametr typ [, ...]) IS zapytanie;

--kursor z parametrami

np.:

CURSOR wydatki_cur IS

select id, data, kwota, opis

from wydatki

where za_kogo = `W'

order by data;

CURSOR wydatki_cur (rodz varchar2) IS

select id, data, kwota, opis

from wydatki

where za_kogo = `W'

and rodzaj = rodz

order by data;

Korzystanie:

Z kursora można korzystać na dwa sposoby:

Obsługa poprzez pętlę FOR nie wymaga otwierania i zamykania kursora:

FOR rekord_nazwa IN kursor_nazwa LOOP

. . .

END LOOP;

Przykład:

DECLARE
CURSOR wydatki_cur IS select * from wydatki where rodzaj = 'J';
BEGIN
FOR w_rec IN wydatki_cur LOOP
dbms_output.put_line(w_rec.data||' '|| w_rec.kwota||' '|| w_rec.opis);
END LOOP;
END;

Przy pętli FOR nie jest konieczne deklarowanie kursora jawnego. Można wykorzystać kursor domyślny (o nazwie SQL) i w pętli FOR podać od razu zapytanie:

FOR rekord_nazwa IN (podzapytanie_SQL) LOOP

. . .

END LOOP;

Przykład:

BEGIN
FOR w_rec IN (select * from wydatki where rodzaj = 'J') LOOP
dbms_output.put_line(w_rec.data||' '|| w_rec.kwota||' '|| w_rec.opis);
END LOOP;
END;

Własne zarządzenie kursorem wymaga:

OPEN kursor_nazwa;

FETCH kursor_nazwa INTO zmienna,...; -- odczytanie wiersza

EXIT WHEN kursor_nazwa%NOTFOUND; -- wyjście z pętli

CLOSE kursor_nazwa;

Przykład 1 (dane z wiersza kursora odczytane do zmiennej wierszowej):

DECLARE
CURSOR wydatki_cur IS select * from wydatki where rodzaj = 'J';
w_rec wydatki%ROWTYPE;
BEGIN
OPEN wydatki_cur;
LOOP
FETCH wydatki_cur INTO w_rec;
EXIT WHEN wydatki_cur %NOTFOUND;
dbms_output.put_line(w_rec.data||' '|| w_rec.kwota||' '|| w_rec.opis);
END LOOP;

CLOSE wydatki_cur;
END;

Przykład 2 (dane z wiersza kursora odczytane do prostych zmiennych):

DECLARE
CURSOR wydatki_cur IS select data, kwota, opis from wydatki where rodzaj = 'J';
r_data wydatki.data%TYPE;
r_kwota wydatki.kwota%TYPE;
r_opis wydatki.opis%TYPE;
BEGIN
OPEN wydatki_cur;
LOOP
FETCH wydatki_cur INTO r_data, r_kwota, r_opis;
EXIT WHEN wydatki_cur %NOTFOUND;
dbms_output.put_line(r_data||' '||r_kwota||' '||r_opis);
END LOOP;

CLOSE wydatki_cur;
END;

Atrybuty kursora:

W trakcie obsługi kursora (domyślnego lub jawnego, niezależnie od metody obsługi) można korzystać z aktrybutów kursora. Wykaz atrybutów:

kursor_nazwa%FOUND

zwraca TRUE jeżeli udało się odczytanie wiersza z kursora

kursor_nazwa%NOTFOUND

zwraca TRUE jeżeli udało się odczytanie wiersza z kursora, np. z powodu osiągnięcia końca kursora

kursor_nazwa%ISOPEN

zwraca TRUE jeżeli kursor jest otwarty

kursor_nazwa%ROWCOUNT

zwraca liczbę wierszy kursora

Odczytanie atrybutów kursora domyślnego (SQL) pozwala badać efekty ostatniego wykonanego polecenia DML.

Przykład:

BEGIN
update wydatki set kwota = round(kwota,2) where rodzaj = 'J';
IF SQL%ROWCOUNT >0 THEN
dbms_output.put_line('Zmodyfikowano '||SQL%ROWCOUNT||' wierszy.');
END IF;
END;

Blokowanie przez otwarty kursor

Jeżeli chcemy modyfikować grupę wierszy w bazie, to otwierając kursor można od razu zarezerwować wiersze stanowiące wynik zapytania w kursorze.

DECLARE
CURSOR wydatki_cur IS select * from wydatki where rodzaj = 'J' FOR UPDATE;
w_rec wydatki%ROWTYPE;
i number := 0;
BEGIN
OPEN wydatki_cur;
LOOP
FETCH wydatki_cur INTO w_rec;
EXIT WHEN wydatki_cur %NOTFOUND;
i := i+1;
update wydatki set opis = i||': '||w_rec.opis where CURRENT OF wydatki_cur;
END LOOP;
CLOSE wydatki_cur;
END;

Zmienne kursorowe

Wykorzystywane jeżeli konieczne jest przekazanie otwartego kursora pomiędzy podprogramami.

Deklaracja typu “kursorowego”:

TYPE nazwa_typu_kursorowego IS REF CURSOR RETURN tabela%ROWTYPE;

Wykorzystanie zmiennej typu kursorowego w parametrach wywołania procedury:

PROCEDURE proc_nazwa(zmianna_kursorowa IN OUT nazwa_typu_kursorowego) IS

BEGIN

OPEN zmienna_kursorowa FOR zapytanie;

END proc_nazwa;

Obsługa wyjątków

Wyjątek, to zdarzenie wywołujące błąd występujący w trakcie wykonania programu, który powoduje przerwanie aktualnie wykonywanego polecenia. Mówi się, że „wykonanie polecenia zwróciło błąd / wyjątek”.

Wyróżniane są dwa rodzaje wyjątków:

Przykłady wyjątków systemowych, posiadających nazwy:

CURSOR_ALREADY_OPEN

powstaje w czasie próby otwarcia kursora już otwartego; np. otwarcie kursora CUR w pętli FOR rekord IN CUR

INVALID CURSOR

powstaje w czasie próby wykonania niedozwolonej operacji na kursorze, na przykład zamknięcia kursora nie otwartego

INVALID_NUMBER

podanie wartości nie liczbowej tam, gdzie wymagana jest wartość liczbowa, lub liczbowej nie pasującej do zakresu zmiennej

NO_DATA_FOUND

jeżeli polecenie DML nie znajduje żadnego wiersza

TOO_MANY_ROWS

jeżeli polecenie SELECT INTO zwróci więcej niż jeden wiersz

VALUE_ERROR

powstaje z powodu błędów arytmetycznych i konwersyjnych w sytuacjach, których nie obejmuje wyjątek INVALID_NUMBER

ZERO_DIVIDE

powstaje przy próbie dzielenia liczby przez zero

Każdy błąd opisany jest poprzez dwie wartości:

- kod liczbowy; kod ostatniego błędu zapisany jest w zmiennej SQLCODE

- opis - opis błędu zapisany jest w zmiennej SQLERRM

Deklarowanie własnego wyjątku

Wyjątek użytkownika musi zostać zadeklarowany w bloku programu, w którym ma zostać użyty, w sekcji deklaracji:

nazwa_bledu EXCEPTION;

Wywołanie wyjątku przez programistę

RAISE nazwa_bledu; - - wywołanie własnego wyjątku

RAISE TOO_MANY_ROWS; - - wywołanie wyjątku systemowego

Wyjątki zdefiniowane przez użytkownika zwracają błąd o kodzie 1 i opisie „User-Defined Exception”.

Poza tym w programie można wywołać błąd o określonym kodzie i określonym opisie:

RAISE_APPLICATION_ERROR (numer_błędu, 'tekst błędu');

Polecenie to zatrzymuje działanie podprogramu, wycofuje wszelkie zmiany dokonane przez transakcję, w ramach której podprogram jest wykonywany, ustawia zmienne SQLERRM i SQLCODE wartościami podanymi jako parametry wywołania. Numer błędu musi być z przedziału -20000 do -20999.

Przechwycenie wyjątku / błędu

Przechwycenie obsługi błędu następuje w sekcji obsługi błędów (po słownie kluczowym EXCEPTION).

EXCEPTION

WHEN wyjątek1 THEN

-- instrukcje

WHEN wyjątek2 THEN

-- instrukcje

WHEN OTHERS THEN

-- instrukcje

Możliwe jest łączenie obsługi wyjątków:

WHEN wyjątek1 OR wyjątek2 THEN

-- instrukcje

WHEN OTHERS THEN

-- instrukcje

Słowo OTHERS oznacza dowolny błąd, nie obsłużony przez wcześniejsze wyjątki. Jego obsługa nie może być łączona z innymi wyjątkami.

Wystąpienie błędu powoduje przerwanie wykonywanej instrukcji i przekazanie sterowania dla sekcji obsługi wyjątków w aktualnym bloku programu. Jeśli tam nie nastąpi jego obsługa to przekazanie nastąpi do sekcji nadrzędnej itd. Jeśli nikt nie obsłuży wyjątku to zostanie przekazany do aplikacji zewnętrznej. Ten mechanizm nazywany jest propagacją wyjątków.

BEGIN

…

BEGIN

IF X = 1 THEN

RAISE E1;

ELSIF X = 2 THEN

RAISE E2;

ELSE

RAISE E3;

END IF;

EXCEPTION

WHEN E1 THEN …

END;

…

EXCEPTION

WHEN E2 THEN …

END;

Rekordy

Rekord to struktura składająca się z wielu pól różnego typu.

W PL/SQL najczęściej rekordy wykorzystywane są do przechowywania wierszy z tabeli. Najłatwiej do tego celu użyć słowa kluczowego %ROWTYPE. Można jednak zdefiniować typ rekordowy o własnej strukturze (według własnych potrzeb).

Deklaracja typu rekordowego:

TYPE nazwa_typu_rekordowego IS RECORD

(

nazwa_pola_1 typ_pola_1,

nazwa_pola_2 typ_pola_2,

...

);

Deklaracja zmiennej rekordowej:

zmienna_rekordowa nazwa_typu_rekordowego;

Odwołanie do wartości pola rekordu:

zmienna_rekordowa.nazwa_pola_n

Przykład:

declare
type rek is record
(
d date,
k number,
o varchar2(100)
);
r rek;

begin
-- wczytanie wartości do zmiennej rekordowej

select data, kwota, opis into r from wydatki where id = 1;

-- wyświetlenie pól zmiennej rekordowej
dbms_output.put_line(r.d||' '||r.k||' '||r.o);

...
end;

Typ tablicowy

W PL/SQL dostępne są zmienne tablicowe (nie mylić z tabelami bazodanowymi). Dostępne są dwuwymiarowe, dwukolumnowe tablice. Pierwsza kolumna przechowuje identyfikator wiersza (indeks typu BINARY_INTEGER). Druga kolumna przechowuje wartość. Jeżeli istnieje konieczność wykorzystania wielu kolumn w zmiennej tablicowej, to należy zrobić tablicę, której elementami są rekordy. Tablice mogą być przekazywane jako parametry procedur i funkcji.

Deklaracja typu tablicowego:

TYPE typ_tablicowy IS

TABLE OF typ_danych [NOT NULL]

INDEX BY BINARY_INTEGER;

Deklaracja zmiennej tablicowej:
zmienna_tablicowa typ_tablicowy;

Odwołanie do zmiennej tablicowej - zwraca wartość z drugiej kolumny i-tego wiersza:

zmienna_tablicowa(i)

Zmienna tablicowa pozwala na odwoływanie się do jej atrybutów. Dostępne są następujące atrybuty:

zmienna_tablicowa.EXISTS(i)

sprawdza czy i-ty wiersz jest określony; zwraca TRUE jeżeli jest

zmienna_tablicowa.COUNT

zwraca liczbę niepustych wierszy w tablicy

zmienna_tablicowa.FIRST

zwraca numer pierwszego niepustego wiersza

zmienna_tablicowa.LAST

zwraca numer ostatniego niepustego wiersza

zmienna_tablicowa.PRIOR(i)

zwraca numer poprzedniego niepustego wiersza względem i-tego

zmienna_tablicowa.NEXT(i)

zwraca numer poprzedniego niepustego wiersza względem i-tego

zmienna_tablicowa.DELETE

czyści zawartość tabeli

zmienna_tablicowa.DELETE(i)

czyści i-ty wiersz tabeli

zmienna_tablicowa.DELETE(i,j)

czyści wiersze od i-tego do j-tego

Przykład

declare
type rek is record
(
d date,
k number,
o varchar2(100)
);

type tab is table of rek
index by binary_integer;

t tab;
i binary_integer := 0;

begin
for r in (select data, kwota, opis from wydatki) loop
i := i + 1;
t(i) := r;
end loop;

for i in 1 .. t.count loop
dbms_output.put_line(t(i).d||' '||t(i).k||' '||t(i).o);
end loop;
end;

Dynamiczny SQL

PL/SQL umożliwia budowania treści kolejnych poleceń do wykonania w trakcie wykonywania programu - dynamicznie.

Zwiększa to elastyczność kodu.

Poza tym umożliwia wykonywania w bloku PL/SQL wykonywania poleceń DDL i DCL.

Składnia uruchamiająca polecenie:

EXECUTE IMMEDIATE polecenie_sql

[INTO zmienna_1 [,...]

[USING [IN | OUT | IN OUT] parametr_1 [,...]]

;

polecenie_sql

tekst zawierające polecenie SQL lub blok PL/SQL do wykonania;

w tekście można używać nazw zmiennych poprzedzonych dwukropkiem; wartości tych zmiennych są określane po słowie kluczowym USING

INTO

można wykorzystać jeżeli uruchamiane polecenie_sql zwraca jakiś wynik; podawane są zmienne, do których ma zostać zapisany wynik uruchomionego polecenia

USING

podawane są wartości zmiennych wykorzystywanych w uruchamianym poleceniu_sql

Przykłady:

DECLARE
p_sql VARCHAR2(1000);
w wydatki%ROWTYPE;
BEGIN
p_sql := 'select * from wydatki where id = :wid';
EXECUTE IMMEDIATE p_sql INTO w USING 1;
dbms_output.put_line(w.data||' '||w.kwota);
END;

DECLARE
p_sql VARCHAR2(1000);
BEGIN
p_sql := 'insert into osoby values(''X'',''ciocia Klocia'')';
EXECUTE IMMEDIATE p_sql;
p_sql := 'insert into osoby values(:z1,:z2)';
EXECUTE IMMEDIATE p_sql USING `Y', `wujek Zdzich';
END;

DECLARE
polecenie varchar2(50);
tabela varchar2(20);
warunek varchar2(50);
wynik_t varchar2(50);
nazwa_pola varchar2(20);
BEGIN
polecenie := 'select * from';
tabela := 'konta';
warunek := 'id = :z1';
nazwa_pola := 'numer';
EXECUTE IMMEDIATE
replace(polecenie,'*',nazwa_pola)||' '||tabela||' where '||warunek INTO wynik_t USING 1;
dbms_output.put_line(wynik_t);
END;

Wykorzystanie aplikacji SQL*Plus

Kompilacja obiektów

Wywołanie polecenie CREATE OR REPLACE automatycznie powoduje kompilację obiektu.

Jeżeli w wyniku kompilacji ujawnią się błędy, to obiekt ma status INVALID.

Informacje o błędnych obiektach można uzyskać poprzez zapytanie:

select * from all_objects where status = 'INVALID';

Listę błędów można listować przy pomocy polecenia:

SHOW ERRORS;

Wywołanie ponownej kompilacji wykonywane jest przez polecenia:

ALTER PROCEDURE nazwa COMPILE;

ALTER FUNCTION nazwa COMPILE;

ALTER PACKAGE nazwa COMPILE [PACKAGE];

ALTER PACKAGE nazwa COMPILE BODY;

Uruchomienie procedury / funkcji składowanej

W celu uruchomienia procedury / funkcji wykonywane jest polecenie:

EXEC nazwa_programu(parametry_wywołania);

lub

START nazwa_programu(parametry_wywołania);

Operator musi posiadać prawo EXECUTE dla obiektu.

Wyświetlanie wyników na ekran / do pliku

Aby wyniki działania programu (polecenie dmbs_output) były pokazywane na ekranie należy przed uruchomieniem programu wywołać polecenie:

SET SERVEROUTPUT ON [SIZE rozmiar]

rozmiar - określa ilość bajtów, która może zostać wyświetlona.

Aby wyniki wyświetlane na ekran były zapisywane również do pliku należy wykonać polecenie:

SPOOL ścieżna_do_pliku

wyłaczenie, przesyłania danych do pliku wywoływane jest poprzez polecenie:

SPOOL OFF

ORACLE / Programowanie w PL/SQL 2005-05-22

Łukasz Murowaniecki Strona 28 z 31


Wyszukiwarka

Podobne podstrony:
3678
3678
3678
200413 3678
3678
3678
3678
3678, Scenariusze Jasełek
3678
3678
3678
200413 3678

więcej podobnych podstron