Kurzor PL / SQL

Shrnutí: v tomto výukovém programu se dozvíte o kurzoru PL / SQL a jeho použití.

Kurzor je ukazatel, který ukazuje na výsledek dotazu. PL / SQL má dva typy kurzorů: implicitní kurzory a explicitní kurzory.

Implicitní kurzory

Kdykoli Oracle provede příkaz SQL, například SELECT INTO, INSERT, UPDATE a DELETE automaticky vytvoří implicitní kurzor.

Oracle interně spravuje celý prováděcí cyklus implicitních kurzorů a odhaluje pouze informace a stavy kurzoru, například SQL%ROWCOUNT, SQL%ISOPEN, SQL%FOUND a SQL%NOTFOUND.

Implicitní kurzor není elegantní, když dotaz vrátí nulu nebo více řádků které způsobují NO_DATA_FOUND nebo TOO_MANY_ROWS výjimku.

Explicitní kurzory

Explicitní kurzor je prohlášení SELECT výslovně deklarované v sekci deklarace aktuálního bloku nebo ve specifikaci balíčku.

U explicitního kurzoru máte kont převést jeho prováděcí cyklus z OPEN, FETCH a CLOSE.

Oracle definuje prováděcí cyklus, který provádí příkaz SQL a přidruží k němu kurzor.

Následující obrázek ukazuje prováděcí cyklus explicitního kurzoru:

Podívejme se podrobně na každý krok.

Deklarovat kurzor

Před použitím explicitního kurzoru jej musíte deklarovat v sekci deklarace bloku nebo balíčku následujícím způsobem:

V této syntaxi :

• Nejprve zadejte název kurzoru za klíčovým slovem CURSOR.
• Zadruhé, definujte dotaz pro načtení dat za klíčovým slovem IS.

Otevřete kurzor

Než začnete načítat řádky z kurzoru, musíte jej otevřít. Chcete-li otevřít kurzor, použijte následující syntaxi:

V této syntaxi je cursor_name název kurzoru deklarovaného v sekci deklarace.

Když otevřete kurzor, Oracle analyzuje dotaz, váže proměnné a provede související příkaz SQL.

Oracle také určí plán provádění , přidruží hostitelské proměnné a parametry kurzoru se zástupnými symboly v příkazu SQL, určí sadu výsledků a nastaví kurzor na první řádek v sadě výsledků.

Více o parametrizovaném kurzoru v dalším výukovém programu.

Načíst z kurzoru

Příkaz FETCH umístí obsah aktuálního řádku do proměnných. Syntaxe příkazu FETCH je následující:

Chcete-li načíst všechny řádky v sadě výsledků, potřebujete načíst každý řádek až do posledního.

Zavření kurzoru

Po načtení všech řádků musíte zavřít kurzor pomocí CLOSE statement:

Zavření kurzoru dá Oracle pokyn k uvolnění přidělené paměti ve vhodnou dobu.

Pokud deklarujete kurzor v anonymní blok, procedura nebo funkce, kurzor se automaticky zavře, až skončí provádění těchto objektů.

Musíte však explicitně zavřít kurzorové balíčky. Pokud zavřete kurzor, který se dosud neotevřel, Oracle vyvolá INVALID_CURSOR výjimku.

Explicitní atributy kurzoru

Kurzor má čtyři atributy, na které můžete odkazovat v následujícím formátu:

kde cursor_name je název explicitní kurzor.

1)% ISOPEN

Tento atribut je TRUE, pokud je kurzor otevřený, nebo FALSE pokud tomu tak není.

2)% FOUND

Tento atribut má čtyři hodnoty:

• NULL před prvním načtením
• TRUE pokud byl záznam úspěšně načten
• FALSE pokud není vrácen žádný řádek
• INVALID_CURSOR pokud není otevřen kurzor

3)% NOTFOUND

Tento atribut má čtyři hodnoty:

• NULL před prvním načtením
• FALSE pokud byl záznam načten úspěšně záměrně
• TRUE pokud není vrácen žádný řádek
• INVALID_CURSOR pokud kurzor není otevřen

3)% ROWCOUNT

Atribut %ROWCOUNT vrací počet řádků načtených z kurzoru. Pokud se kurzor neotevře, vrátí tento atribut INVALID_CURSOR.

Příklad kurzoru PL / SQL

Použijeme tabulky orders a order_items z ukázková databáze pro demonstraci.

Následující prohlášení vytváří pohled, který vrací tržby zákazníků:

Hodnoty ve sloupci kreditu jsou 5% z celkových výnosů z prodeje.

Předpokládejme, že je třeba vytvořit anonymní zablokujte to:

1. Resetujte kreditní limity všech zákazníků na nulu.
2. Načtěte zákazníky seřazené podle prodeje v sestupném pořadí a dejte jim nové kreditní limity z rozpočtu 1 milion.

Následující anonymní blok ilustruje logiku:

V sekci deklarace deklarujeme tři proměnné.

První je l_budget jehož počáteční hodnota je 1,000,000.

Druhá proměnná je explicitní proměnná kurzoru s názvem c_sales jehož načte data z sales zobrazení:

Třetí proměnná je a kurzorový záznam s názvem c_sales.

V sekci provádění provedeme následující:

• Nejprve resetujeme kreditní limity všech zákazníků na nulu pomocí příkazu UPDATE.
• Zadruhé otevřete kurzor c_sales.
• Zatřetí, načtěte každý řádek z kurzoru. V každé iteraci smyčky aktualizujeme kreditní limit a snížili jsme rozpočet. Smyčka končí, když není třeba načíst žádný řádek nebo je vyčerpán rozpočet.
• Nakonec zavřete kurzor.

Následující dotaz načte data z customers tabulka k ověření aktualizace:

Výsledek:

Z výsledku jasně vyplývá, že kreditní limity má pouze několik prvních zákazníků. Pokud sečtete všechny úvěrové limity, celková částka by měla být 1 milion, jak je uvedeno níže:

Nyní , měli byste rozumět kurzorům PL / SQL včetně implicitních a explicitních kurzorů a jak je efektivně používat ke zpracování dat, řádek po řádku, z tabulky.