wyklad3

Wykład 3 – Programowanie procedur
wbudowanych w języku PL/SQL
Bezpieczeństwo
Odtwarzanie bazy danych po awarii
Hurtownie danych
1





instrukcje warunkowe
pętle
zmienne dynamiczne
tworzenie obiektów (kursorów)
obsługa wyjątków
2
create procedure procedura([lista parametrow])
as
[atrybut typ]
-- dwie kreski to komentarz
[atrybut typ]
...
[kod funkcji]
/
3
SQL> execute nazwa_schematu.
nazwa_procedury(parametry);
SQL> execute nazwa_schematu.nazwa_pakietu.
nazwa_procedury_w_pakiecie(parametry);
4
create or replace package NAZWA_PAKIETU
[lista funkcji (same deklaracje)];
/
create or replace package body NAZWA_PAKIETU
[definicja funkcji 1
/
definicja funkcji 2
/
definicja funkcji 3
/];
5
funkcje samo-wyzwalające się, „uruchamiane
zdarzeniem”, np. na tabeli przy wstawianiu
create trigger NAZWA_TRIGERA
before insert on NAZWA_TABELI
begin
...
end;
6
CREATE OR REPLACE PACKAGE mojpakiet AS
x NUMBER(1);
slowo CHAR(100);
CURSOR kur IS SELECT * FROM tabela;
PROCEDURE mojaprocedura1;
PROCEDURE mojaprocedura2;
TYPE typwiersz IS RECORD
(imie tabela.imie%TYPE,
nazwisko nazwisko.imie%TYPE,
pesel pesel.imie%TYPE);
wiersz typwiersz;
END mojpakiet;
/
7
CREATE OR REPLACE PACKAGE BODY mojpakiet AS
PROCEDURE mojaprocedura1 AS
BEGIN
OPEN kur;
LOOP
FETCH kur INTO wiersz.imie, wiersz.nazwisko, wiersz.pesel;
EXIT WHEN kur%NOTFOUND;
mojpakiet.mojaprocedura2(wiersz);
END LOOP;
CLOSE kur;
EXCEPTION
WHEN OTHERS THEN ........
END;
8
PROCEDURE mojaprocedura2(w typwiersz) AS
BEGIN
...
END;
END;
/
9
Dwa aspekty:


ochrona przed niepowołanym dostępem
utrata danych, lub utrata ich spójności
10
Utworzenie nowego użytkownika
create user ANTEK identified by HASLO;
Informacje o użytkownikach – patrz perspektywy:
DBA_USERS
ALL_USERS
11
nadawanie
grant connect, resource to ANTEK;
odbieranie
revoke selectanytable from ANTEK;
zmiana hasła
alter user ANTEK identified by NOWEHASLO;
12
Rola jest zbiorem uprawnień
create role sprzedawca;
grant [nazwauprawnienia_1] to sprzedawca;
grant [nazwauprawnienia_2] to sprzedawca;
...
grant [nazwauprawnienia_n] to sprzedawca;
create role kierowca;
grant [nazwauprawnienia_1] to kierowca;
grant [nazwauprawnienia_2] to kierowca;
...
grant [nazwauprawnienia_m] to kierowca;
13
przydzielenie/odebranie roli użytkownikowi
grant sprzedawca to ANTEK;
revoke kierowca from ADAM;
14
profile są ograniczeniami uprawnień
nakładanymi na użytkowników
create profile NAZWA_PROFILU limit
session_per_user 2
connect_time 10
...;
nadanie profilu
alter user ANTEK profile NAZWA_PROFILU;
15
Podział blokad ze względu na blokowany obszar
 na tabeli
 na rekordzie
Typy blokad
 S (shareable) współdzielone
 X (exclusive) na wyłączność
16
określenie położenia plików
select status, enabled, name from v$datafile;
select * from v$logfile;
select * from v$controlfile;
 kolejność czynności
shutdown [normal]
kopiowanie wszystkich plików b.d. + pliku
parametrów
 startup

17
kopia zawartości przestrzeni tabel (bez plików
dziennika powtórzeń), użyteczne gdy baza
danych musi być dostępna 24 h na dobę i przez
7 dni w tygodniu, punkty kontrolne nie są w
czasie kopiowanie zapisywane do plików
przestrzeni
alter tablespace NAZWA_TBS begin backup;
przerwanie kopiowania
alter tablespace NAZWA_TBS end backup;
18
tryby pracy programu EXPORT
 table mode
 user mode
 database mode (incremental, cumulative, complete)
rodzaje eksportu
 dane i strukura
 tylko struktura
19
parametry programu exp.exe
USERID
FILE (*.dmp)
FULL (Y/N) (konieczne uprawnienie sysoper, lub sysdba)
OWNER
TABLES
INCTYPE
 otwarcie bazy danych w trybie restricted;
 narzędzie IMPORT – przenoszenie danych pomiędzy
serwerami pracującymi na różnych
systemachoperacyjnych

20
TNSNAMES.ORA – plik definiujący aliasy, zawierający
nazwy i adresy baz danych
BAZA=(ADDRESS=(PROTOCOL=TCP)(HOST=156.17.40
.40)(PORT=1521))CONNECT_DATA=(SOURCE_NAME
=baza)
Połączenie klienta
SQLPlus> connect antek@baza;
21
Narzędzie SQLLoader
sqlldr.exe ctl=’plik_sterujący’ log=’plik_raportu’ userid=scott/tiger]
Przykładowa postać pliku sterującego
LOAD DATA
INFILE *
INTO TABLE NAZWA_TABELI
FIELD TERMINATED BY ‘|’
OPTIONALLY ENCLOSED BY ‘”’
(kolumna1,kolumna2,kolumna3,kolumna4 DATE „DD-MonthYYYY”)
1|”Kowalski”|Jan|21-January-2001
2|Nowak|”Wojciech”|01-December-1999
...
22
Tryby pracy
NOARCHIVELOG – szybki, ale bez zapisywania śladów transakcji
ARCHIVELOG – wolniejszy (serwer zapisuje wszystkie zatwierdzone
transakcje w plikachv*.log i okresowo je archiwizuje do *.arc,
katalog docelowy plików śladu transakcji zawiera ustawienie
LOG_ARCHIVE_DEST)
23
Wybrane elementy/techniki


serwery lustrzane
partycjonowanie obiektów
24
Cel
Umożliwienie odtworzenia danych
 z momentu przed awarią
 bez przestoju (minimalizacja czasu niedostępności
bazy danych)
Postulaty
 dwa identyczne, w miarę niezależnie pracujące
komputery (production database, standby database)
 te same systemy operacyjne
 te same wersje Oracle Server
25



Każda partycja jest oddzielnym segmentem
i może być przechowywana w oddzielnym
TBS-ie (nawet na innym dysku). Zalety
łatwiejsze archiwizowanie i reorganizacja
lepsza efektywność dostępu
możliwość równoległych procesów DML
(współbieżność) – skrócenie czasu
przetwarzania
26



Każda partycja jest oddzielnym segmentem
i może być przechowywana w oddzielnym
TBS-ie (nawet na innym dysku). Zalety
łatwiejsze archiwizowanie i reorganizacja
lepsza efektywność dostępu
możliwość równoległych procesów DML
(współbieżność) – skrócenie czasu
przetwarzania
27
create table OSOBY
(nr_id number(10),
imie varchar2(15),
nazwisko verchar2(15),
data_ur date,
kod_pocztowy number(5)) partition by
range(kod_pocztowy)
(partition f1 values less than 10000 tablespace TBS1,
partition f2 values less than 20000 tablespace TBS2,
partition f3 values less than 30000 tablespace TBS);
28
Odwołanie do całej tabeli
select * from osoby;
Odwołanie do partycji
select * from osoby partition(f1);
Usunięcie partycji
alter table osoby drop partition f2;
29