DECLARE CURSOR (Transact-SQL) (Polski)

• 14.03.2017
• 13 minut na przeczytanie
• • c
  • r
  • i
  • M
  • m
  • +6

Dotyczy: SQL Server (wszystkie obsługiwane wersje ) Azure SQL Database

Definiuje atrybuty kursora serwera Transact-SQL, takie jak jego zachowanie przewijania i kwerenda używana do tworzenia zestawu wyników, na podstawie którego kursor działa. DECLARE CURSOR akceptuje zarówno składnię opartą na standardzie ISO, jak i składnię używającą zestawu rozszerzeń Transact-SQL.

Konwencje składni języka Transact-SQL

Składnia

Argumenty

nazwa_kursora
Jest to nazwa kursora serwera Transact-SQL zdefiniowana. nazwa_kursora musi być zgodna z regułami dotyczącymi identyfikatorów.

NIEWRAŻLIWY
Definiuje kursor, który tworzy tymczasową kopię danych, które mają być używane przez kursor. Odpowiedzi na wszystkie żądania wysyłane do kursora pochodzą z tej tabeli tymczasowej w tempdb; w związku z tym modyfikacje dokonane w tabelach bazowych nie są odzwierciedlane w danych zwracanych przez pobieranie do tego kursora, a ten kursor nie pozwala na modyfikacje. Gdy używana jest składnia ISO, jeśli pominięto INSENSITIVE, zatwierdzone usunięcia i aktualizacje dokonane w bazowych tabelach (przez dowolnego użytkownika) są odzwierciedlane w kolejnych pobraniach.

PRZEWIŃ
Określa, że wszystkie opcje pobierania (FIRST, LAST, PRIOR, NEXT, RELATIVE, ABSOLUTE) są dostępne. Jeśli SCROLL nie jest określony w ISO DECLARE CURSOR, NEXT jest jedyną obsługiwaną opcją pobierania . Nie można określić SCROLL, jeśli określono również FAST_FORWARD. Jeśli SCROLL nie jest określony, dostępna jest tylko opcja pobierania NEXT, a kursor staje się FORWARD_ONLY.

select_statement
Jest standardową instrukcją SELECT, która definiuje zestaw wyników kursora. Słowa kluczowe FOR BROWSE i INTO nie są dozwolone w select_statement deklaracji kursora.

SQL Server niejawnie konwertuje kursor na inny typ, jeśli klauzule w select_statement są sprzeczne z funkcjonalnością żądanego typu kursora.

TYLKO DO ODCZYTU
Zapobiega aktualizacjom dokonywanym przez ten kursor. Nie można odwołać się do kursora w klauzuli WHERE CURRENT OF w instrukcji UPDATE lub DELETE. Ta opcja przesłania domyślną możliwość aktualizacji kursora.

nazwa_kursora
Jest to nazwa kursora serwera Transact-SQL zdefiniowana. nazwa_kursora musi być zgodna z regułami dotyczącymi identyfikatorów.

LOKALNY
Określa, że zasięg kursora jest lokalny dla partii, procedury składowanej lub wyzwalacza, w którym został utworzony kursor. Nazwa kursora obowiązuje tylko w tym zakresie. Do kursora mogą się odwoływać lokalne zmienne kursora w parametrze wsadowym, procedurze składowanej, wyzwalaczu lub procedurze składowanej OUTPUT. Parametr OUTPUT służy do przekazywania lokalnego kursora z powrotem do wywołującej partii, procedury składowanej lub wyzwalacza, który może przypisać parametr do zmiennej kursora, aby odwołać się do kursora po procedurze składowanej kończy się. Kursor jest niejawnie zwalniany po zakończeniu przetwarzania wsadowego, procedury składowanej lub wyzwalacza, chyba że kursor został przekazany z powrotem w parametrze OUTPUT. Jeśli jest przekazywana z powrotem w parametrze OUTPUT, kursor jest zwalniany, gdy ostatnia zmienna, do której się odwołuje, zostaje cofnięta lub wykracza poza zakres.

GLOBALNY
Określa, że zasięg kursora jest globalny dla połączenia. Do nazwy kursora można się odwoływać w dowolnej procedurze składowanej lub partii wykonywanej przez połączenie. Kursor jest tylko niejawnie zwalniany przy rozłączaniu.

FORWARD_ONLY
Określa, że kursor może poruszać się tylko do przodu i być przewijany z od pierwszego do ostatniego rzędu. FETCH NEXT to jedyna obsługiwana opcja pobierania. Wszystkie instrukcje wstawiania, aktualizowania i usuwania wykonane przez bieżącego użytkownika (lub zatwierdzone przez innych użytkowników), które mają wpływ na wiersze w zestawie wyników, są widoczne podczas pobierania wierszy.Ponieważ kursora nie można przewijać wstecz, zmiany wprowadzone w wierszach w bazie danych po pobraniu wiersza nie są widoczne przez kursor. Kursory tylko do przodu są domyślnie dynamiczne, co oznacza, że wszystkie zmiany są wykrywane podczas przetwarzania bieżącego wiersza. Zapewnia to szybsze otwieranie kursora i umożliwia zestawowi wyników wyświetlanie aktualizacji dokonanych w bazowych tabelach. Chociaż kursory tylko do przodu nie obsługują przewijania do tyłu, aplikacje mogą powrócić na początek zestawu wyników przez zamknięcie i ponowne otwarcie kursora. Jeśli FORWARD_ONLY jest określony bez STATIC, KEYSET lub DYNAMIC, kursor działa jak kursor dynamiczny. Jeśli nie określono FORWARD_ONLY ani SCROLL, FORWARD_ONLY jest wartością domyślną, chyba że słowa kluczowe STATIC, KEYSET lub DYNAMIC. Domyślnie kursory STATIC, KEYSET i DYNAMIC to SCROLL. W przeciwieństwie do interfejsów API baz danych, takich jak ODBC i ADO, FORWARD_ONLY jest obsługiwany przez STATIC, KEYSET, i DYNAMIC Kursory języka Transact-SQL.

STATIC
Określa, że kursor zawsze wyświetla zestaw wyników w takiej postaci, w jakiej był po pierwszym otwarciu kursora, tymczasowa kopia danych, które mają być używane przez kursor. Odpowiedzi na wszystkie żądania wysyłane do kursora pochodzą z tej tabeli tymczasowej w tempdb. Dlatego wstawianie, aktualizowanie i usuwanie dokonywane w tabelach podstawowych nie jest odzwierciedlane w danych zwracanych przez pobieranie do tego kursora, a ten kursor nie wykrywa zmian dokonanych w przynależności, kolejności lub wartościach zestawu wyników po otwarciu kursora . Kursory statyczne mogą wykrywać własne aktualizacje, usunięcia i wstawienia, chociaż nie są do tego wymagane. Załóżmy na przykład, że statyczny kursor pobiera wiersz, a następnie inna aplikacja aktualizuje ten wiersz. Jeśli aplikacja ponownie pobiera wiersz ze statycznego kursora, wartości, które widzi, pozostają niezmienione, pomimo zmian wprowadzonych przez inną aplikację. Obsługiwane są wszystkie typy przewijania.

ZESTAW KLUCZY
Określa, że członkostwo i kolejność wierszy w kursorze są ustalane po otwarciu kursora. Zestaw kluczy, które jednoznacznie identyfikują wiersze, jest wbudowany w tabelę w tempdb zwaną zestawem kluczy. Ten kursor zapewnia funkcjonalność pomiędzy statycznym i dynamicznym kursorem, umożliwiając wykrywanie zmian. Podobnie jak kursor statyczny, nie zawsze wykrywa zmiany w przynależności i kolejności zestawu wyników. Podobnie jak kursor dynamiczny, wykrywa zmiany wartości wierszy w zestawie wyników. Kursory sterowane zestawem kluczy są kontrolowane przez zestaw unikatowych identyfikatorów (kluczy) zwanych zestawem kluczy. Klucze są zbudowane z zestawu kolumn, które jednoznacznie identyfikują wiersze w zestawie wyników. Zestaw kluczy to zestaw wartości kluczy ze wszystkich wierszy zwróconych przez instrukcję zapytania. W przypadku kursorów sterowanych zestawem kluczy dla każdego wiersza kursora jest budowany i zapisywany klucz oraz przechowywany na stacji roboczej klienta lub na serwerze. Gdy uzyskujesz dostęp do każdego wiersza, przechowywany klucz jest używany do pobierania bieżących wartości danych ze źródła danych. W przypadku kursora sterowanego zestawem kluczy członkostwo w zestawie wyników jest blokowane, gdy zestaw kluczy jest w pełni zapełniony. Następnie dodatki lub aktualizacje, które mają wpływ na członkostwo, nie są częścią zestawu wynikowego, dopóki nie zostanie ponownie otwarty. Zmiany wartości danych (wprowadzone przez właściciela zestawu kluczy lub inne procesy) są widoczne, gdy użytkownik przewija zestaw wyników:

• Jeśli wiersz zostanie usunięty, próba pobrania wiersza zwraca @@FETCH_STATUS równe -2, ponieważ usunięty wiersz pojawia się jako luka w zestawie wyników. Klucz dla wiersza istnieje w zestawie kluczy, ale wiersz nie istnieje już w zestawie wyników.
• Wstawienia wykonane poza kursorem (przez inne procesy) są widoczne tylko wtedy, gdy kursor jest zamknięty i ponownie otwarty. Wstawienia wykonane z wnętrza kursora są widoczne na końcu zestawu wyników.
• Aktualizacje kluczowych wartości spoza kursora przypominają usunięcie starego wiersza, po którym następuje wstawienie nowego wiersza. Wiersz z nowymi wartościami nie jest widoczny, a próby pobrania wiersza ze starymi wartościami zwracają @@FETCH_STATUS równe -2. Nowe wartości są widoczne, jeśli aktualizacja odbywa się za pomocą kursora przez określenie klauzuli WHERE CURRENT OF.

DYNAMICZNY
Definiuje kursor, który odzwierciedla wszystkie zmiany danych wprowadzone do wiersze w swoim zestawie wyników podczas przewijania kursora i pobierania nowego rekordu, niezależnie od tego, czy zmiany nastąpiły wewnątrz kursora, czy przez innych użytkowników poza kursorem. Dlatego wszystkie instrukcje wstawiania, aktualizowania i usuwania składane przez wszystkich użytkowników są widoczne przez kursor.Wartości danych, kolejność i przynależność do wierszy mogą się zmieniać przy każdym pobieraniu. Opcja pobierania ABSOLUTE nie jest obsługiwana w przypadku kursorów dynamicznych. Aktualizacje dokonane poza kursorem nie są widoczne, dopóki nie zostaną zatwierdzone (chyba że poziom izolacji transakcji kursora jest ustawiony na UNCOMMITTED). Załóżmy na przykład, że dynamiczny kursor pobiera dwa wiersze, a następnie inną aplikację aktualizuje jeden z tych wierszy i usuwa drugi. Jeśli dynamiczny kursor następnie pobierze te wiersze, nie znajdzie usuniętego wiersza, ale wyświetli nowe wartości zaktualizowanego wiersza.

FAST_FORWARD
Określa FORWARD_ONLY, READ_ONLY kursor z włączoną optymalizacją wydajności. Nie można określić FAST_FORWARD, jeśli określono również SCROLL lub FOR_UPDATE. Ten typ kursora nie pozwala na modyfikację danych z wnętrza kursora.

READ_ONLY
Zapobiega aktualizacjom dokonywanym za pomocą tego kursora. Nie można odwołać się do kursora w klauzuli WHERE CURRENT OF w instrukcji UPDATE lub DELETE. Ta opcja przesłania domyślną możliwość aktualizacji kursora.

SCROLL_LOCKS
Określa, że aktualizacje umieszczone lub usunięte za pośrednictwem kursora są gwarantowane. SQL Server blokuje wiersze podczas wczytywania ich do kursora, aby zapewnić ich dostępność do późniejszych modyfikacji. Nie można określić SCROLL_LOCKS, jeśli określono również FAST_FORWARD lub STATIC.

OPTYMISTYCZNE
Określa, że pozycjonowane aktualizacje lub usunięcia wykonane za pomocą kursora nie powiodą się, jeśli wiersz został zaktualizowany od czasu wczytania go do kursora. SQL Server nie blokuje wierszy, gdy są wczytywane do kursora. Zamiast tego używa porównania wartości kolumny datownika lub wartości sumy kontrolnej, jeśli tabela nie ma kolumny znacznika czasu, aby określić, czy wiersz został zmodyfikowany po wczytaniu go do kursora. Jeśli wiersz został zmodyfikowany, próba umieszczenia aktualizacji lub usunięcia nie powiedzie się. Nie można określić OPTIMISTIC, jeśli określono również FAST_FORWARD.

TYPE_WARNING
Określa, że wysyłane jest ostrzeżenie do klienta, gdy kursor jest niejawnie konwertowany z żądanego typu na inny.

select_statement
Jest standardową instrukcją SELECT, która definiuje zestaw wyników kursora. Słowa kluczowe COMPUTE, COMPUTE BY, FOR BROWSE i INTO nie są dozwolone w ramach select_statement deklaracji kursora.

SQL Server niejawnie konwertuje kursor na inny typ, jeśli klauzule w select_statement powodują konflikt z funkcjonalnością żądanego typu kursora. Aby uzyskać więcej informacji, zobacz Niejawne konwersje kursora.

Uwagi

DECLARE CURSOR definiuje atrybuty kursora serwera Transact-SQL, takie jak jego zachowanie przewijania i zapytanie użyte do zbudowania zestawu wyników, na którym działa kursor. Instrukcja OPEN zapełnia zestaw wyników, a FETCH zwraca wiersz z zestawu wyników. Instrukcja CLOSE zwalnia bieżący zestaw wyników skojarzony z kursorem. Instrukcja DEALLOCATE zwalnia zasoby używane przez kursor.

Pierwsza postać instrukcji DECLARE CURSOR używa ISO składnia do deklarowania zachowań kursora. Druga forma DECLARE CURSOR korzysta z rozszerzeń języka Transact-SQL, które umożliwiają definiowanie kursorów przy użyciu tych samych typów kursorów, co w funkcjach kursora interfejsu API bazy danych ODBC lub ADO.

Nie można mieszać tych dwóch form. Jeśli określisz słowo kluczowe SCROLL lub INSENSITIVE przed słowem kluczowym CURSOR, nie możesz użyć żadnego słowa kluczowe między słowami kluczowymi CURSOR a FOR <select_statement>. Jeśli określisz jakiekolwiek słowa kluczowe między słowami kluczowymi CURSOR i FOR <select_statement>, nie możesz określić SCROLL ani INSENSITIVE przed słowem kluczowym CURSOR.

Jeśli DECLARE CURSOR używający składni Transact-SQL nie określa READ_ONLY, OPTIMISTIC lub SCROLL_LOCKS, wartość domyślna jest następująca:

• Jeśli SELECT nie obsługuje aktualizacji (niewystarczające uprawnienia, dostęp do zdalnych tabel, które nie obsługują aktualizacji itd.), kursor to READ_ONLY.

• STATIC i FAST_FORWARD domyślnie kursory READ_ONLY.

• DYNAMIC i KEYSET domyślnie kursory OPTIMISTIC .

Do nazw kursorów można się odwoływać tylko w innych instrukcjach języka Transact-SQL. Nie mogą się do nich odwoływać funkcje API bazy danych. Na przykład po zadeklarowaniu kursora nie można odwoływać się do nazwy kursora z funkcji lub metod OLE DB, ODBC lub ADO. Wierszy kursora nie można pobrać za pomocą funkcji pobierania ani metod interfejsów API; wiersze można pobrać tylko za pomocą instrukcji Transact-SQL FETCH.

Po zadeklarowaniu kursora te systemowe procedury składowane mogą być użyte do określenia właściwości kursora.

Systemowe procedury składowane	Opis
sp_cursor_list	Zwraca listę kursorów aktualnie widocznych w połączeniu i ich atrybutów.
sp_describe_cursor	Opisuje atrybuty kursora, takie jak czy jest to kursor tylko do przodu czy przewijany.
sp_describe_cursor_columns	Opisuje atrybuty kolumn w zestawie wyników kursora.
sp_describe_cursor_tables	Opisuje podstawowe tabele, do których uzyskano dostęp za kursorem.

Zmienne mogą być używane jako część select_statement, które deklaruje kursor. Wartości zmiennych kursora nie zmieniają się po zadeklarowaniu kursora.

Uprawnienia

Uprawnienia DECLARE CURSOR domyślnie dla każdego użytkownika, który ma SELECT uprawnień do widoków, tabel i kolumn używanych w kursorze.

Ograniczenia i ograniczenia

Nie można używać kursorów ani wyzwalaczy w tabeli z klastrowany indeks magazynu kolumn. To ograniczenie nie dotyczy nieklastrowanych indeksów magazynu kolumn; możesz używać kursorów i wyzwalaczy w tabeli z nieklastrowanym indeksem magazynu kolumn.

Przykłady

A. Używanie prostego kursora i składni

Zestaw wyników wygenerowany przy otwarciu tego kursora zawiera wszystkie wiersze i wszystkie kolumny w tabeli. Ten kursor można aktualizować, a wszystkie aktualizacje i usunięcia są reprezentowane w pobieraniach wykonywanych względem tego kursora. FETCH NEXT to jedyne dostępne pobieranie, ponieważ opcja SCROLL nie została określona.

B. Używanie zagnieżdżonych kursorów do tworzenia wyników raportu

Poniższy przykład pokazuje, jak można zagnieżdżać kursory w celu tworzenia złożonych raportów. Kursor wewnętrzny jest zadeklarowany dla każdego dostawcy.

Zobacz też