2007-01-17 Kolos Sbd

Wstęp

Kolos z sbd , można mieć jeden (duży) plik z materiałami.

Może przygotujemy go wspólnie np. tutaj , poniżej ?

Jakby co materialy 2006-12-sdb-plsql-materialy

Pozdrawiam.

Ściąga

Operacje na danych.

Wstawianie wartosci do tabeli:
INSERT INTO dzial VALUES (2,'Sprzedaz', 'Warszawa');

Modyfikacja danych:
UPDATE dzial SET lokalizacja = 'Gdańsk' WHERE id_dzialu = 1;

Usuwanie danych:
DELETE FROM dzial WHERE lokalizacja = 'Gdańsk';

Zatwierdzanie zmian:
COMMIT;

Wycofanie zmian:
ROLLBACK;

Przydatne instrukcje:

Wszystkie obiekty użytkownika:
COLUMN object_name format a30
COLUMN object_type format a30
SELECT object_name, object_type from user_objects;

Wypisanie więzów dla tabeli PRACOWNIK:
set linesize 200
column COLUMN_NAME format A30
select CONSTRAINT_NAME, COLUMN_NAME from user_cons_columns
where table_name = 'PRACOWNIK';

Użycie sekwencji:
Create sequence Numery
Increment by 10
Start with 10;

Insert into DZIAL(ID_DZIALU, NAZWA_DZIALU , LOKALIZACJA)
(Numery.NextVal,'Kadry','Warszawa');

Nazwy i typy danych kolumn z tabeli DZIAL:
select COLUMN_NAME, DATA_TYPE from USER_TAB_COLUMNS
where TABLE_NAME = 'DZIAL';

Procedury:

Wywołanie treści procedury:
column LINE format 99999
column TEXT format A80
set linesize 100
SET PAGESIZE 23
SET PAUSE ON
Select LINE, TEXT
From USER_SOURCE
Where NAME = 'NazwaProcedury';

Pierwsza procedura:
dodaj_goscia.JPG

Zapytania na tableach

Podzapytania skorelowane

Podstawowe polecenia PLSQL

Blok Anonimowy

DECLARE
deklaracja zmiennych
BEGIN
Ciąg instrukcji
EXCEPTION
Obsługa wyjątków
END

Bloki: DECLARE i EXCEPTION są opcjonalne.

Ciąg instrukcji może zawierać:
SELECT, INSERT, UPDATE, DELETE, COMMIT i ROLLBACK

Zmienne

Deklaracja:
DECLATE
nazwa_zmiennej typ_zmiennej

Zapis do zmiennej:
SELECT costam INTO nazwa_zmiennej FROM nazwa_tabeli WHERE warunek
(zapisy ale jak jest tylko pojedynczy rekord w wyniku zapytania )

Typy zmiennych:

  • NUMBER(5)
  • VARCHAR2(ilosc_znakow)

Zmienne wierszowe

DECLARE
rek_osoby Emp%ROWTYPE; /* Typ wierszowy */
BEGIN
SELECT * INTO rek_osoby
FROM Emp WHERE Ename = 'KOWALSKI';
rek_osoby.Sal := 1.1*rek_osoby.Sal;
INSERT INTO Dziennik
VALUES (rek_osoby.Ename, rek_osoby.Job,
rek_osoby.Sal, SYSDATE);
END;

Zmiennej wierszowej nie można użyć bezpośrednio po słowie kluczowym VALUES w instrukcji INSERT INTO!

Przykład z wykładu:

DECLARE
v_stan NUMBER(5);
BEGIN
SELECT Stan INTO v_stan FROM Magazyn
WHERE Produkt = 'Fiat';
IF v_stan > 0 THEN
UPDATE Magazyn SET Stan = Stan - 1
WHERE Produkt = 'Fiat';
INSERT INTO Zakupy
VALUES ('Kupiono Fiata', Sysdate);
ELSE
INSERT INTO Zakupy
VALUES ('Brak Fiatów', Sysdate);
END IF;
COMMIT;
EXCEPTION
WHEN no_data_found THEN
INSERT INTO dziennik
VALUES('Nie znaleziono FIATa');
END;

Wprowadzanie danych z klawiatury i wypisywanie na ekran

SET ServerOutput ON
ACCEPT rocz_zarob PROMPT 'Podaj roczne zarobki: '

DECLARE
mies NUMBER(9,2) := &rocz_zarob;
BEGIN
mies := mies/12;
DBMS_OUTPUT.PUT_LINE ('Miesięczne zarobki = ' ||mies);
END;

Instrukcje warunkowe

IF warunek THEN ciąg_instrukcji END IF;

IF warunek THEN
ciąg_instrukcji
ELSE
ciąg_instrukcji
END IF;

IF warunek THEN
ciąg_instrukcji
ELSIF warunek THEN
ciąg_instrukcji
END IF;

Pętle - Instrukcje iteracji

LOOP
ciąg instrukcji (w tym EXIT lub EXIT WHEN warunek)
END LOOP;

FOR zmienna IN wartość1 .. wartość2
LOOP … END LOOP;

WHILE warunek
LOOP … END LOOP;

Kursory

Kursor - bufor, do którego są zapisywane, kolejno sprowadzane z bazy danych, wiersze z wynikami zapytania.
Definicja kursora (przyporządkowanie instrukcji SELECT)

CURSOR nazwa_kursora IS
instrukcja_SELECT; — (bez INTO!)

Otwarcie kursora (wykonanie instrukcji SELECT)

OPEN nazwa_kursora;

Pobieranie kolejnych wierszy

FETCH nazwa_kursora INTO zmienna, …;

Wyjście z pętli po sprowadzeniu wszystkich wierszy

EXIT WHEN nazwa_kursora%NOTFOUND;

Zamknięcie kursora

CLOSE nazwa_kursora;

Materiały z pliku mrowki

PL SQL
Blok PL/SQL:

DECLARE
deklaracje
BEGIN
instrukcje
EXCEPTION
obsługa wyjątków
END;
Deklaracje zmiennych:

Identyfikator TYP [NOT NULL] [:=wyrażenie];
np.: nazwisko VARCHAR2(30) := ???Kowalski???;
SELECT w PL/SQL:

SELECT wyrażenie1, wyrażenie2, …
INTO zmienna1, zmienna2, …
FROM …

Zapytanie musi zwracać 1 wiersz.
Warunki:

IF warunek THEN
instrukcje
ELSIF warunek THEN
instrukcje
ELSE
instrukcje
END IF
Włączenie wyświetlania komunikatów:

SET ServerOutput ON
Wyświetlenie komunikatu:

DBMS_OUTPUT.PUT_LINE(napis);
Wywołanie błędu:

Np. raise_application_error(-20500, ???komunikat???);
Zmienne systemowe:

SQL%ROWCOUNT - ilość zwróconych wierszy
SQL%FOUND - czy zapytanie zwróciło wiersz
SQLERRM - komunikat o błędzie
Uruchomienie procedury:

EXECUTE procedura (parametry)
Zmienne typu wiersza i kolumny:

zmienna emp.ename%type - zmienna tego samego typu co ename
zmienna emp%rowtype - zmienna typu wierszowego
Deklaracja i użycie kursora:

W DECLARE:
CURSOR nazwa IS SELECT
Po BEGIN:
OPEN nazwa; - otwarcie kursora
FETCH nazwa INTO zmienna - ściągnięcie kolejnego wiersza
CLOSE nazwa - zamknięcie kursora

Kursorów używamy zwykle w pętli np.:
LOOP
FETCH nazwa INTO zmienna;
EXIT WHEN nazwa%NOTFOUND;

END LOOP

Atrybuty kursora:

nazwa%FOUND - ostatnia instrukcja FETCH zwróciła wiersz
nazwa%NOTFOUND - ostatnia instrukcja FETCH nie zwróciła wiersza
nazwa%ROWCOUNT - ilość zwróconych dotychczas wierszy
nazwa%ISOPEN - określa, czy kursor jest otwarty
Procedury:

CREATE [OR REPLACE] PROCEDURE nazwa (par [IN/OUT/INOUT] TYP, …) AS
deklaracje
BEGIN
kod procedury
END;
Funkcje:

CREATE [OR REPLACE] FUNCTION nazwa (par [IN/OUT/INOUT] TYP, …)
RETURN TYP AS
deklaracje
BEGIN
kod funkcji
RETURN wyrażenie;
END;
Wyzwalacze:

CREATE [OR REPLACE] TRIGGER nazwa
{BEFORE / AFTER} specyfikacja instrukcji
ON tabela
[FOR EACH ROW]
blok PL/SQL

Specyfikacje instrukcji:
INSERT, DELETE, UPDATE
Można je łączyć np. BEFORE INSERT OR UPDATE
Dla UPDATE można określić pola, których modyfikowanie spowoduje uruchomienie triggera np. UPDATE OF pole1, pole2

Odwołanie do starej/nowej wartości w wyzwalaczu:

:OLD.pole
:NEW.pole

Zmienne logiczne określające, co spowodowało uruchomienie wyzwalacza:

UPDATING, INSERTING, DELETING
Tworzenie sekwensji:

CREATE SEQUENCE nazwa_sekwencji
[INCREMENT BY k]
[START WITH n]

Bieżąca i kolejna wartości sekwencji:

nazwa_sekwencji.currval
nazwa_sekwencji.nextval

Przykłady użycia

Fetch

CREATE OR REPLACE PROCEDURE SumujZarobki AS zarobki REAL:=0;
CURSOR kursor_osoba IS
SELECT * FROM Emp;
rek_osoby kursor_osoba%ROWTYPE;
BEGIN
OPEN kursor_osoba;
LOOP
FETCH kursor_osoba INTO rek_osoby;
EXIT WHEN kursor_osoba%NOTFOUND;
zarobki := zarobki + rek_osoby.Sal;
END LOOP;
Dbms_output.Put_line('W sumie zarobki = '||zarobki);
CLOSE kursor_osoba;
END;

Wyzwalacz

Napisz wyzwalacz, sprawdzający czy wstawiana do tabeli GOSCIE data urodzenia nie jest większa niż data bieżąca.

CREATE OR REPLACE TRIGGER DATA_UTODZENIA
BEFORE INSERT OR UPDATE OF DATA_UR
ON GOSCIE
FOR EACH ROW
BEGIN
IF( :NEW.DATA_UR >= SYSDATE ) THEN
DBMS_OUTPUT.PUT_LINE('ZŁA DATA URODZENIA');
RAISE_APPLICATION_ERROR(-20556,'ZŁA DATA URODZENIA');
END IF;
end;
/

Napisz wyzwalacz, sprawdzający poprawność dat przy wprowadzaniu rezerwacji hotelowej do tabeli REZERWACJE .

CREATE OR REPLACE TRIGGER DATY
BEFORE INSERT OR UPDATE OF REZ_OD, REZ_DO
ON REZERWACJE
FOR EACH ROW
BEGIN
sprawdzamy, czy data końca rezerwacji nie jest wczesniejsza niz data poczatku
IF( :NEW.REZ_OD > :NEW.REZ_DO) THEN
DBMS_OUTPUT.PUT_LINE('ZLY ZAKRES DATY');
RAISE_APPLICATION_ERROR(-20555,'ZŁY ZAKRES DATY');
END IF;
sprawdzamy, czy data poczatku rezerwacji nie jest wczesniejsza niz aktualna.
IF( :NEW.REZ_OD < SYSDATE) THEN
DBMS_OUTPUT.PUT_LINE('POMYLKA, DZIS jest '||SYSDATE);
RAISE_APPLICATION_ERROR(-20666,' ZLA DATA POCZATKU');
END IF;
END;
/

Napisz wyzwalacze realizujące więzy spójności referencyjnej typu ustaw NULL dla DELETE i UPDATE. między tabelami REZERWACJE i MELDUNEK.

CREATE OR REPLACE TRIGGER DELETEREZERWACJE
AFTER DELETE
ON REZERWACJE
FOR EACH ROW
BEGIN
/* TRIGGER FOR ON DELETE TO PARENT SET NULL */
UPDATE MELDUNEK
SET MELDUNEK.ID_REZERWACJI = NULL
WHERE MELDUNEK.ID_REZERWACJI = :OLD.ID_REZERWACJI;
END;
/

Utwórzmy wyzwalacz generujący ID_KLIENTA podczas wstawiania nowego klienta do tabeli KLIENT.

CREATE TRIGGER GENERUJ_ID_KLIENTA
BEFORE INSERT ON KLIENT
FOR EACH ROW
BEGIN
SELECT NVL(MAX(ID_KLIENTA)+1,1)
INTO :NEW.ID_KLIENTA
FROM KLIENT;
END;

Materiały od mrówki

Zapytania SQL

Plik z zapytaniami SQL od mrówki.
Jak stweirdzicie że dobrze do tutaj wrzucić to wystarczy wpisać tutaj kod wiki:
[[include 2007-01-17-kolos-sbd-emrowka-zapytania-sql]]

Linki do reszty materiałów zewnetrznych

Oracle/PLSQL Topics pozdr Mruff ;)
wyklady z plsql-a
Materialy od mrowki

Tip #1474: VIM and PL/SQL programming
Tip #1475: VIM and PL/SQL programming

licencja

Wszystkie prawa zastrzerzone do tej podstorny.

O ile nie zaznaczono inaczej, treść tej strony objęta jest licencją Creative Commons Attribution-Share Alike 2.5 License.