Powtrzenie wykadu 7 Oprogramowanie strony serwera Cz 2

Powtórzenie wykładu 7 Oprogramowanie strony serwera Część 2 SBD, L. Banachowski 1

Kursor: dostęp do obszarów roboczych instrukcji SELECT • • CURSOR nazwa_kursora IS instrukcja_SELECT; OPEN nazwa_kursora; FETCH nazwa_kursora INTO zmienna, …; CLOSE nazwa_kursora; SBD, L. Banachowski 2

Przykład DECLARE 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 3 SBD, L. Banachowski zarobki = '||zarobki);

Aktualizacja wierszy za pomocą kursora DECLARE CURSOR kursor_osoba IS SELECT Ename, Sal FROM Emp FOR UPDATE OF Sal; rek_osoby kursor_osoba%ROWTYPE; BEGIN OPEN kursor_osoba; LOOP FETCH kursor_osoba INTO rek_osoby; …. UPDATE Emp SET Sal = Sal * 1. 1 WHERE CURRENT OF kursor_osoba; …. END LOOP; CLOSE kursor_osoba; …. END; SBD, L. Banachowski 4

Tabela PL/SQL • TYPE Emp. Tab. Typ IS TABLE OF Emp%ROWTYPE INDEX BY BINARY_INTEGER; • emp_tab Emp. Tab. Typ; • i: =0; LOOP i: =i+1; FETCH kursor_osoba emp_tab(i); EXIT WHEN c 1%NOTFOUND; END LOOP; SBD, L. Banachowski INTO 5

Definicja procedury CREATE OR REPLACE PROCEDURE Credit (acc_no IN NUMBER, amount IN NUMBER) AS BEGIN UPDATE Account SET Balance = Balance + amount WHERE Account_id = acc_no; END; • Wywołanie procedury: • CALL Credit(10112, 1000); -- w SQL • Credit(10112, 1000); -- w PL/SQL • EXECUTE Credit(10112, 1000); -- w SQL*Plus SBD, L. Banachowski 6

Definicja funkcji CREATE OR REPLACE FUNCTION Saldo (acc_no IN NUMBER) RETURN NUMBER IS v_balance NUMBER; BEGIN SELECT Balance INTO v_balance FROM Account WHERE Account_id = acc_no; RETURN v_balance; END; SBD, L. Banachowski 7

Przykład – użycie funkcji • W SQL: SELECT Saldo(Account_id) FROM Account; • W PL/SQL: x : = Saldo(10112); • W SQL*Plus: VARIABLE p NUMBER EXECUTE : p : = Saldo(10112) PRINT p SBD, L. Banachowski 8

Informacja o parametrach procedury lub funkcji • DESCRIBE Aktualizuj_zarobki • DESCRIBE Saldo SBD, L. Banachowski 9

Informacja o tekście procedury lub funkcji COLUMN Line FORMAT 99999 COLUMN Text FORMAT A 80 SET PAGESIZE 23 SET PAUSE ON SELECT Line, Text FROM User_Source WHERE Name = ‘Nazwa_procedury’; Nazwę procedury (ewentualnie funkcji lub pakietu) należy wpisać dużymi literami! SBD, L. Banachowski 10

Wykrywanie i diagnostyka błędów • show errors • Dbms_output. Put_line('Pracownik: '||v_nazwisko|| ' Zarobki: ' ||To_char(v_zarobki)) SBD, L. Banachowski 11

Uprawnienie EXECUTE Uprawnienia do użycia procedury GRANT EXECUTE ON Oblicz_zarobki TO Moja_księgowa; SBD, L. Banachowski 12

Pakiet W ramach pakietu możemy globalnie dla niego zdefiniować: • kursory, • zmienne i stałe, • wyjątki. Każda sesja ma swoją własną wersję pakietu. Zmienne i stałe zachowują swoje wartości na czas trwania sesji. SBD, L. Banachowski 13

CREATE PACKAGE Obsługa_prac AS PROCEDURE Zatrudnij(numer_prac NUMBER, nazwisko VARCHAR 2, zarobki numer_działu NUMBER, NUMBER); PROCEDURE Zwolnij(numer_prac NUMBER); ile_przyjętych NUMBER; ile_zwolnionych NUMBER; END Obsługa_prac; SBD, L. Banachowski 14

CREATE PACKAGE BODY Obsługa_prac AS PROCEDURE Zatrudnij( numer_prac NUMBER, nazwisko VARCHAR 2, zarobki NUMBER, numer_działu NUMBER) IS BEGIN INSERT INTO Pracownicy VALUES (numer_prac, nazwisko, zarobki, numer_działu); COMMIT; ile_przyjętych : = ile_przyjętych +1; END; PROCEDURE zwolnij(numer_prac NUMBER) IS BEGIN DELETE FROM Pracownicy WHERE Id_prac = numer_prac; COMMIT; ile_zwolnionych : = ile_zwolnionych +1; END; BEGIN ile_przyjętych : = 0; ile_zwolnionych : = 0; END Obsługa_prac; SBD, L. Banachowski 15

Użycie Z zewnątrz dostęp do obiektów publicznych pakietu Obsługa_prac. Zatrudnij(1000, 'Kowalski ', 2000, 12); SBD, L. Banachowski 16

Przeładowanie nazw funkcji i procedur Tej samej nazwy można użyć dla różnych wersji tej samej procedury lub funkcji. PROCEDURE Zwolnij(Numer_prac NUMBER); PROCEDURE Zwolnij(Nazwisko VARCHAR 2); SBD, L. Banachowski 17

Usuwanie DROP PROCEDURE procedura; DROP FUNCTION funkcja; DROP PACKAGE pakiet; SBD, L. Banachowski 18

Dynamiczny SQL VARIABLE sql_stmt VARCHAR 2(100) …… BEGIN EXECUTE IMMEDIATE : sql_stmt; -- dowolna instrukcja SQL END; / SBD, L. Banachowski 19

Dynamiczny SELECT DECLARE TYPE Emp. Cur. Typ IS REF CURSOR; emp_cv Emp. Cur. Typ; emp_rec Emp%ROWTYPE; sql_stmt VARCHAR 2(100); my_job VARCHAR 2(15) : = 'CLERK'; BEGIN sql_stmt : = 'SELECT * FROM Emp WHERE job = : j'; OPEN emp_cv FOR sql_stmt USING my_job; LOOP FETCH emp_cv INTO emp_rec; EXIT WHEN emp_cv%NOTFOUND; -- przetwórz rekord END LOOP; CLOSE emp_cv; END; SBD, L. Banachowski 20