Po co używać wyraźnych kursorów zamiast zwykłych pętli?

Od roku piszę podstawowe aplikacje internetowe (dla bazy danych Oracle), a ponieważ funkcje są dość proste, większość z nas trzyma regularne pętle FOR, aby uzyskać nasze dane:

for i in (select * from STUDENTS) loop
      htp.prn(i.student_last_name || ', ' || i.student_first_name || ' ' || i.student_dob);
end loop;

Ale kursory wydają się być „właściwym” sposobem robienia rzeczy. Mogę znaleźć wiele informacji o tym, czym są kursory i różne sposoby ich przewijania, ale nie mogę znaleźć solidnego powodu, aby używać ich w zwykłych pętlach FOR. Czy to zależy od potrzeb procedury? Czy są jakieś nieodłączne zalety, o których powinienem wiedzieć?

Kursor może być jawny lub niejawny, a dowolny typ może być użyty w pętli FOR. Są naprawdę dwa aspekty twojego pytania.

1. Po co używać jawnej pętli FOR kursora nad niejawną pętlą FOR kursora?

   • Użyj jawnej pętli kursora FOR, gdy zapytanie zostanie ponownie użyte, w przeciwnym razie preferowany jest kursor domyślny.

2. Po co używać pętli z FETCH zamiast pętli FOR, która nie ma jawnego FETCH?

   • Użyj FETCH wewnątrz pętli, gdy chcesz zbiorczo kolekcjonować lub gdy potrzebujesz dynamicznego SQL.

Oto kilka przydatnych informacji z dokumentacji.

Przykład niejawnego kursora dla pętli

BEGIN
   FOR vItems IN (
      SELECT last_name
      FROM employees
      WHERE manager_id > 120
      ORDER BY last_name
   ) 
   LOOP
      DBMS_OUTPUT.PUT_LINE ('Name = ' || vItems.last_name);
   END LOOP;
END;
/

Przykład jawnego kursora dla pętli

DECLARE
   CURSOR c1 IS
      SELECT last_name
      FROM employees
      WHERE manager_id > 120
      ORDER BY last_name;
BEGIN
   FOR vItems IN c1 LOOP
      DBMS_OUTPUT.PUT_LINE ('Name = ' || vItems.last_name);
   END LOOP;
END;
/

Domniemany Kursor

Kursor niejawny to kursor sesji, który jest konstruowany i zarządzany przez PL / SQL. PL / SQL otwiera niejawny kursor za każdym razem, gdy uruchamiasz instrukcję SELECT lub DML. Nie można kontrolować ukrytego kursora, ale można uzyskać informacje z jego atrybutów.

Ukryty kursor zamyka się po uruchomieniu powiązanej instrukcji; jednak jego wartości atrybutów pozostają dostępne do momentu uruchomienia innej instrukcji SELECT lub DML.

Ukryte atrybuty kursora to: SQL% ISOPEN, SQL% FOUND, SQL% NOTFOUND, SQL% ROWCOUNT, SQL% BULK_ROWCOUNT, SQL% BULK_EXCEPTIONS

Jawny kursor

Kursor jawny to kursor sesji, który tworzysz i którym zarządzasz. Musisz zadeklarować i zdefiniować wyraźny kursor, nadając mu nazwę i kojarząc go z zapytaniem (zwykle zapytanie zwraca wiele wierszy). Następnie możesz przetworzyć zestaw wyników zapytania na jeden z następujących sposobów:

Otwórz wyraźny kursor (za pomocą instrukcji OPEN), pobierz wiersze z zestawu wyników (za pomocą instrukcji FETCH) i zamknij wyraźny kursor (za pomocą instrukcji CLOSE).

Użyj jawnego kursora w instrukcji kursora FOR LOOP (patrz „Przetwarzanie zestawu wyników zapytania za pomocą kursora dla instrukcji LOOP”).

Nie można przypisać wartości do jawnego kursora, użyć jej w wyrażeniu ani użyć jako formalnego parametru podprogramu lub zmiennej hosta. Możesz to zrobić za pomocą zmiennej kursora (patrz „Zmienne kursora”).

W przeciwieństwie do niejawnego kursora, można odwoływać się do jawnego kursora lub zmiennej kursora według jego nazwy. Dlatego jawny kursor lub zmienna kursora nazywana jest kursorem nazwanym.

Instrukcje kursora dla pętli

Instrukcja kursora FOR LOOP pozwala uruchomić instrukcję SELECT, a następnie natychmiast przejść przez wiersze zestawu wyników. W tej instrukcji można użyć niejawnego lub jawnego kursora.

Kod, który opublikowałeś, używa kursora. Używa niejawnej pętli kursora.

Są przypadki, w których użycie jawnej pętli kursora (tj. Zadeklarowanie zmiennej CURSOR w sekcji deklaracji) daje czystszy kod lub lepszą wydajność

1. Jeśli masz bardziej złożone zapytania, których nie możesz przefiltrować do widoków, może to ułatwić odczyt kodu, jeśli twoja pętla będzie się powtarzać student_cursorzamiast zawierać 30-wierszową instrukcję SQL, która zawiera logikę. Na przykład, jeśli drukowałeś wszystkich studentów, którzy otrzymali pozwolenie na ukończenie studiów i którzy polegali na dołączeniu do tabel, które miały ich akademickie akta, wymagania dotyczące ich programu studiów, tabele z informacją o akademickich akademikach, tabele z informacjami o zaległych książkach bibliotecznych, w tabelach z informacjami o niezapłaconych opłatach, nadpisaniach administracyjnych itp. prawdopodobnie sensowne byłoby przefakturowanie kodu, aby zapytanie nie utknęło w środku kodu związanego z przedstawieniem listy użytkownikowi. Może to obejmować utworzenie widoku, który zawierałby całą tę logikę. Lub może obejmować utworzenie wyraźnego kursora, który został zadeklarowany jako część bieżącego bloku PL / SQL lub w jakimś bloku PL / SQL wyższego poziomu (tj. kursor zadeklarowany w pakiecie), dzięki czemu można go ponownie użyć. Lub może to wymagać wykonania czegoś innego w celu enkapsulacji i ponownego użycia (powiedzmy, tworząc zamiast tego potokową funkcję tabeli).
2. Jeśli chcesz korzystać z operacji masowych w PL / SQL, zazwyczaj chcesz używać jawnych kursorów. Oto wątek StackOverflow, który omawia różnice w wydajności między jawnymi i niejawnymi kursorami . Jeśli wszystko, co robisz, to dzwonisz htp.prn, to BULK COLLECTprawdopodobnie nic nie kupisz. W innych przypadkach może to jednak spowodować znaczną poprawę wydajności.

Widzę, że wielu programistów używa jawnych kursorów zamiast domyślnych kursorów ze starego nawyku. To dlatego, że w wersji 7 Oracle była to zawsze bardziej wydajna droga. W dzisiejszych czasach jest ogólnie na odwrót. Specjalnie z optymalizatorem, który w razie potrzeby może przepisać niejawny kursor dla pętli do kolekcjonowania zbiorczego.

Ostatnio musiałem przepisać kilka zapytań z niejawnej pętli FOR na wyraźne kursory. Powodem było to, że zapytania pobierały dane z zewnętrznej bazy danych przez łącze, a ta baza danych miała inne kodowanie niż nasza lokalna baza danych. Podczas przesyłania danych z niejawnego kursora do zdefiniowanego lokalnie typu rekordu wystąpiły tajemnicze błędy przerywane (tylko w niektórych określonych wierszach). Nasz DBA wyjaśnił nam to, że sami nie bylibyśmy w stanie dojść do sedna. Wygląda na to, że zgłoszono błąd w Oracle.

Zalecono nam przepisanie wszystkiego za pomocą wyraźnych kursorów, a błąd zniknął.

Nie jest to główny powód, dla którego możesz chcieć używać jawnych treści, ale warto to zanotować.

EDYCJA: Oracle 12c.