Kursor PL / SQL

Podsumowanie: w tym samouczku poznasz kursor PL / SQL i jego użycie.

Kursor to wskaźnik wskazujący na wynik zapytania. PL / SQL ma dwa typy kursorów: kursory niejawne i kursory jawne.

Kursory niejawne

Zawsze, gdy Oracle wykonuje instrukcję SQL, taką jak SELECT INTO, INSERT, UPDATE i DELETE, automatycznie tworzy niejawny kursor.

Oracle wewnętrznie zarządza całym cyklem wykonywania niejawnych kursorów i ujawnia tylko informacje i statusy kursora, takie jak SQL%ROWCOUNT, SQL%ISOPEN, SQL%FOUND i SQL%NOTFOUND.

Niejawny kursor nie jest elegancki, gdy zapytanie zwraca zero lub wiele wierszy co powoduje odpowiednio NO_DATA_FOUND lub TOO_MANY_ROWS wyjątek.

Jawne kursory

Jawny kursor jest instrukcja SELECT zadeklarowana jawnie w sekcji deklaracji bieżącego bloku lub specyfikacji pakietu.

W przypadku kursora jawnego masz ciąg rol w cyklu wykonywania od OPEN, FETCH i CLOSE.

Oracle definiuje cykl wykonywania, który wykonuje instrukcję SQL i kojarzy z nią kursor.

Poniższa ilustracja przedstawia cykl wykonywania jawnego kursora:

Przeanalizujmy szczegółowo każdy krok.

Zadeklaruj kursor

Przed użyciem jawnego kursora musisz zadeklarować go w sekcji deklaracji bloku lub pakietu w następujący sposób:

W tej składni :

• Najpierw określ nazwę kursora po słowie kluczowym CURSOR.
• Po drugie, zdefiniuj zapytanie w celu pobrania danych po słowie kluczowym IS.

Otwórz kursor

Przed rozpoczęciem pobierania wierszy z kursora musisz go otworzyć. Aby otworzyć kursor, użyj następującej składni:

W tej składni cursor_name to nazwa kursora zadeklarowanego w sekcji deklaracji.

Gdy otwierasz kursor, Oracle analizuje zapytanie, wiąże zmienne i wykonuje powiązaną instrukcję SQL.

Oracle określa również plan wykonania , wiąże zmienne języka macierzystego i parametry kursora z symbolami zastępczymi w instrukcji SQL, określa zestaw wyników i ustawia kursor w pierwszym wierszu zestawu wyników.

Więcej o sparametryzowanym kursorze w następnym samouczku.

Pobierz z kursora

Instrukcja FETCH umieszcza zawartość bieżącego wiersza w zmiennych. Składnia instrukcji FETCH jest następująca:

Aby pobrać wszystkie wiersze w zestawie wyników, potrzebujesz aby pobrać każdy wiersz aż do ostatniego.

Zamykanie kursora

Po pobraniu wszystkich wierszy musisz zamknąć kursor za pomocą CLOSE instrukcja:

Zamknięcie kursora nakazuje Oracle zwolnienie przydzielonej pamięci w odpowiednim czasie.

Jeśli zadeklarujesz kursor w anonimowy blok, procedura lub funkcja, kursor zostanie automatycznie zamknięty po zakończeniu wykonywania tych obiektów.

Jednak musisz jawnie zamknąć kursory oparte na pakietach. Zwróć uwagę, że jeśli zamkniesz kursor, który jeszcze się nie otworzył, Oracle zgłosi wyjątek INVALID_CURSOR.

Jawne atrybuty kursora

Kursor ma cztery atrybuty, do których możesz się odwołać w następującym formacie:

gdzie cursor_name to nazwa wyraźny kursor.

1)% ISOPEN

Ten atrybut to TRUE, jeśli kursor jest otwarty lub FALSE jeśli nie.

2)% FOUND

Ten atrybut ma cztery wartości:

• NULL przed pierwszym pobraniem
• TRUE jeśli rekord został pobrany pomyślnie
• FALSE jeśli żaden wiersz nie został zwrócony
• INVALID_CURSOR jeśli kursor nie jest otwarty

3)% NOTFOUND

Ten atrybut ma cztery wartości:

• NULL przed pierwszym pobraniem
• FALSE jeśli rekord został pobrany pomyślnie zasadniczo
• TRUE, jeśli żaden wiersz nie został zwrócony
• INVALID_CURSOR, jeśli kursor nie jest otwarty

3)% ROWCOUNT

Atrybut %ROWCOUNT zwraca liczbę wierszy pobranych z kursora. Jeśli kursor nie jest otwarty, ten atrybut zwraca INVALID_CURSOR.

Przykład kursora PL / SQL

Będziemy używać tabel orders i order_items z przykładowa baza danych do demonstracji.

Poniższe zestawienie tworzy widok, który zwraca przychody ze sprzedaży przez klientów:

Wartości w kolumnie kredytu to 5% całkowitych przychodów ze sprzedaży.

Załóżmy, że musisz stworzyć anonimową zablokuj to:

1. Zresetuj limity kredytowe wszystkich klientów do zera.
2. Pobierz klientów posortowanych według sprzedaży w porządku malejącym i przydziel im nowe limity kredytowe z budżetu 1 mln.

Poniższy anonimowy blok ilustruje logikę:

W sekcji deklaracji deklarujemy trzy zmienne.

Pierwsza to l_budget którego wartość początkowa to 1,000,000.

Druga zmienna to zmienna jawna kursora o nazwie c_sales którego pobiera dane z widoku sales:

Trzecia zmienna to rekord oparty na kursorze o nazwie c_sales.

W sekcji wykonania wykonujemy następujące czynności:

• Najpierw zresetuj limity kredytowe wszystkich klientów do zera za pomocą instrukcji UPDATE.
• Po drugie, otwórz kursor c_sales.
• Po trzecie, pobierz każdy wiersz z kursora. W każdej iteracji pętli aktualizujemy limit kredytowy i zmniejszamy budżet. Pętla kończy się, gdy nie ma wiersza do pobrania lub budżet się wyczerpał.
• Na koniec zamknij kursor.

Poniższe zapytanie pobiera dane z customers do weryfikacji aktualizacji:

Wynik:

Jak widać wyraźnie z wyniku, tylko kilku pierwszych klientów ma limity kredytowe. Jeśli zsumujesz wszystkie limity kredytowe, suma powinna wynosić 1 milion, jak pokazano poniżej:

Teraz , powinieneś zrozumieć kursory PL / SQL, w tym kursory niejawne i jawne, i jak skutecznie ich używać do przetwarzania danych wiersz po wierszu z tabeli.