PL / SQL - Szybki przewodnik

Język programowania PL / SQL został opracowany przez Oracle Corporation pod koniec lat 80. XX wieku jako rozszerzenie proceduralne języka SQL i relacyjnej bazy danych Oracle. Oto kilka ważnych faktów na temat PL / SQL -

• PL / SQL to całkowicie przenośny, wydajny język przetwarzania transakcji.

• PL / SQL zapewnia wbudowane, interpretowane i niezależne od systemu operacyjnego środowisko programistyczne.

• PL / SQL można również wywołać bezpośrednio z wiersza poleceń SQL*Plus interface.

• Bezpośrednie wywołanie można również wykonać z zewnętrznego języka programowania do bazy danych.

• Ogólna składnia PL / SQL jest oparta na języku programowania ADA i Pascal.

• Oprócz Oracle, PL / SQL jest dostępny w TimesTen in-memory database i IBM DB2.

Funkcje PL / SQL

PL / SQL ma następujące funkcje -

• PL / SQL jest ściśle zintegrowany z SQL.
• Oferuje obszerne sprawdzanie błędów.
• Oferuje wiele typów danych.
• Oferuje różnorodne struktury programistyczne.
• Obsługuje programowanie strukturalne za pomocą funkcji i procedur.
• Obsługuje programowanie obiektowe.
• Obsługuje tworzenie aplikacji internetowych i stron serwerowych.

Zalety PL / SQL

PL / SQL ma następujące zalety -

• SQL jest standardowym językiem baz danych, a PL / SQL jest silnie zintegrowany z SQL. PL / SQL obsługuje zarówno statyczny, jak i dynamiczny SQL. Statyczny SQL obsługuje operacje DML i kontrolę transakcji z bloku PL / SQL. W dynamicznym SQL, SQL umożliwia osadzanie instrukcji DDL w blokach PL / SQL.

• PL / SQL umożliwia wysyłanie do bazy danych całego bloku instrukcji jednocześnie. Zmniejsza to ruch w sieci i zapewnia wysoką wydajność aplikacji.

• PL / SQL zapewnia wysoką produktywność programistom, ponieważ może wyszukiwać, przekształcać i aktualizować dane w bazie danych.

• PL / SQL oszczędza czas na projektowaniu i debugowaniu dzięki mocnym funkcjom, takim jak obsługa wyjątków, hermetyzacja, ukrywanie danych i obiektowe typy danych.

• Aplikacje napisane w PL / SQL są w pełni przenośne.

• PL / SQL zapewnia wysoki poziom bezpieczeństwa.

• PL / SQL zapewnia dostęp do predefiniowanych pakietów SQL.

• PL / SQL zapewnia obsługę programowania obiektowego.

• PL / SQL zapewnia obsługę tworzenia aplikacji internetowych i stron serwera.

W tym rozdziale omówimy konfigurację środowiska PL / SQL. PL / SQL nie jest samodzielnym językiem programowania; jest to narzędzie w środowisku programistycznym Oracle.SQL* Plusto interaktywne narzędzie, które umożliwia wpisywanie instrukcji SQL i PL / SQL w wierszu poleceń. Te polecenia są następnie wysyłane do bazy danych w celu przetworzenia. Po przetworzeniu wyciągów wyniki są odsyłane i wyświetlane na ekranie.

Aby uruchamiać programy PL / SQL, na komputerze powinien być zainstalowany serwer Oracle RDBMS. To zajmie się wykonywaniem poleceń SQL. Najnowsza wersja Oracle RDBMS to 11g. Możesz pobrać wersję próbną Oracle 11g z następującego łącza -

Pobierz Oracle 11g Express Edition

Będziesz musiał pobrać 32-bitową lub 64-bitową wersję instalacji zgodnie z systemem operacyjnym. Zwykle są to dwa pliki. Pobraliśmy wersję 64-bitową. Wykonasz również podobne kroki w swoim systemie operacyjnym, nie ma znaczenia, czy jest to Linux czy Solaris.

• win64_11gR2_database_1of2.zip

• win64_11gR2_database_2of2.zip

Po pobraniu powyższych dwóch plików będziesz musiał rozpakować je w jednym katalogu database a pod nim znajdziesz następujące podkatalogi -

Krok 1

Uruchommy teraz Instalatora bazy danych Oracle przy użyciu pliku instalacyjnego. Poniżej znajduje się pierwszy ekran. Możesz podać swój identyfikator e-mail i zaznaczyć pole wyboru, jak pokazano na poniższym zrzucie ekranu. KliknijNext przycisk.

Krok 2

Zostaniesz przekierowany do następującego ekranu; usuń zaznaczenie pola wyboru i kliknijContinue aby kontynuować.

Krok 3

Po prostu wybierz pierwszą opcję Create and Configure Database używając przycisku opcji i kliknij Next aby kontynuować.

Krok 4

Zakładamy, że instalujesz Oracle w podstawowym celu nauki i że instalujesz go na swoim komputerze PC lub laptopie. Dlatego wybierz plikDesktop Class opcję i kliknij Next aby kontynuować.

Krok 5

Podaj lokalizację, w której zostanie zainstalowany serwer Oracle. Po prostu zmodyfikujOracle Basea inne lokalizacje zostaną ustawione automatycznie. Będziesz musiał również podać hasło; będzie to używane przez administratora systemu. Po podaniu wymaganych informacji kliknij plikNext aby kontynuować.

Krok 6

Ponownie kliknij Next aby kontynuować.

Krok 7

Kliknij Finishprzycisk, aby kontynuować; spowoduje to rozpoczęcie właściwej instalacji serwera.

Krok 8

Może to zająć kilka chwil, zanim Oracle rozpocznie wykonywanie wymaganej konfiguracji.

Krok 9

Tutaj instalacja Oracle skopiuje wymagane pliki konfiguracyjne. To powinno chwilę zająć -

Krok 10

Po skopiowaniu plików bazy danych pojawi się następujące okno dialogowe. Po prostu kliknijOK przycisk i wyjdź.

Krok 11

Po instalacji pojawi się następujące końcowe okno.

Ostatni krok

Nadszedł czas, aby zweryfikować instalację. W wierszu polecenia użyj następującego polecenia, jeśli używasz systemu Windows -

sqlplus "/ as sysdba"

Powinieneś mieć znak zachęty SQL, w którym napiszesz swoje polecenia i skrypty PL / SQL -

Edytor tekstu

Uruchamianie dużych programów z wiersza poleceń może spowodować nieumyślną utratę części pracy. Zawsze zaleca się używanie plików poleceń. Aby użyć plików poleceń -

• Wpisz swój kod w edytorze tekstu, na przykład Notepad, Notepad+, lub EditPlusitp.

• Zapisz plik z rozszerzeniem .sql rozszerzenie w katalogu domowym.

• Uruchom SQL*Plus command prompt z katalogu, w którym utworzyłeś plik PL / SQL.

• Rodzaj @file_name w wierszu polecenia SQL * Plus, aby uruchomić program.

Jeśli nie używasz pliku do wykonywania skryptów PL / SQL, po prostu skopiuj kod PL / SQL i kliknij prawym przyciskiem myszy czarne okno, w którym zostanie wyświetlony monit SQL; Użyjpastemożliwość wklejenia całego kodu w wierszu polecenia. Na koniec po prostu naciśnijEnter do wykonania kodu, jeśli nie został jeszcze wykonany.

W tym rozdziale omówimy podstawową składnię języka PL / SQL, która jest plikiem block-structuredjęzyk; Oznacza to, że programy PL / SQL są podzielone i napisane w logicznych blokach kodu. Każdy blok składa się z trzech części podrzędnych -

S.Nr	Sekcje i opis
1	Declarations Ta sekcja zaczyna się od słowa kluczowego DECLARE. Jest to sekcja opcjonalna i definiuje wszystkie zmienne, kursory, podprogramy i inne elementy, które mają być używane w programie.
2	Executable Commands Ta sekcja jest umieszczona między słowami kluczowymi BEGIN i ENDi jest to sekcja obowiązkowa. Składa się z wykonywalnych instrukcji PL / SQL programu. Powinien zawierać co najmniej jedną wykonywalną linię kodu, która może być po prostu plikiemNULL command aby wskazać, że nic nie powinno być wykonywane.
3	Exception Handling Ta sekcja zaczyna się od słowa kluczowego EXCEPTION. Ta opcjonalna sekcja zawieraexception(s) które obsługują błędy w programie.

Każda instrukcja PL / SQL kończy się średnikiem (;). Bloki PL / SQL można zagnieżdżać w innych blokach PL / SQL za pomocąBEGIN i END. Poniżej przedstawiono podstawową strukturę bloku PL / SQL -

DECLARE 
   <declarations section> 
BEGIN 
   <executable command(s)>
EXCEPTION 
   <exception handling> 
END;

Przykład „Hello World”

DECLARE 
   message  varchar2(20):= 'Hello, World!'; 
BEGIN 
   dbms_output.put_line(message); 
END; 
/

Plik end;linia sygnalizuje koniec bloku PL / SQL. Aby uruchomić kod z wiersza poleceń SQL, może być konieczne wpisanie / na początku pierwszej pustej linii po ostatniej linii kodu. Gdy powyższy kod jest wykonywany w wierszu polecenia SQL, daje następujący wynik -

Hello World  

PL/SQL procedure successfully completed.

Identyfikatory PL / SQL

Identyfikatory PL / SQL to stałe, zmienne, wyjątki, procedury, kursory i słowa zastrzeżone. Identyfikatory składają się z litery, opcjonalnie po której następuje więcej liter, cyfr, znaków dolara, znaków podkreślenia i cyfr i nie powinny przekraczać 30 znaków.

Domyślnie, identifiers are not case-sensitive. Więc możesz użyćinteger lub INTEGERdo reprezentowania wartości liczbowej. Nie możesz użyć zastrzeżonego słowa kluczowego jako identyfikatora.

Ograniczniki PL / SQL

Separator to symbol o specjalnym znaczeniu. Poniżej znajduje się lista ograniczników w PL / SQL -

Separator	Opis
+, -, *, /	Dodawanie, odejmowanie / negacja, mnożenie, dzielenie
%	Wskaźnik atrybutu
'	Ogranicznik ciągu znaków
.	Selektor komponentów
(,)	Ogranicznik wyrażenia lub listy
:	Wskaźnik zmiennej żywicielskiej
,	Separator pozycji
"	Cytowany ogranicznik identyfikatora
=	Operator relacyjny
@	Wskaźnik zdalnego dostępu
;	Terminator instrukcji
:=	Operator przypisania
=>	Operator stowarzyszenia
||	Operator łączenia
**	Operator potęgowania
<<, >>	Separator etykiety (początek i koniec)
/*, */	Wieloliniowy separator komentarzy (początek i koniec)
--	Jednowierszowy wskaźnik komentarza
..	Operator zakresu
<, >, <=, >=	Operatorzy relacyjni
<>, '=, ~=, ^=	Różne wersje NOT EQUAL

Komentarze PL / SQL

Komentarze do programu to wyjaśnienia, które mogą być zawarte w kodzie PL / SQL, który piszesz i które pomagają każdemu czytać jego kod źródłowy. Wszystkie języki programowania pozwalają na jakąś formę komentarzy.

PL / SQL obsługuje komentarze jednowierszowe i wieloliniowe. Wszystkie znaki dostępne w komentarzu są ignorowane przez kompilator PL / SQL. Jednowierszowe komentarze PL / SQL zaczynają się od separatora - (podwójny łącznik), a komentarze wieloliniowe są otoczone znakami / * i * /.

DECLARE 
   -- variable declaration 
   message  varchar2(20):= 'Hello, World!'; 
BEGIN 
   /* 
   *  PL/SQL executable statement(s) 
   */ 
   dbms_output.put_line(message); 
END; 
/

Gdy powyższy kod jest wykonywany w wierszu polecenia SQL, daje następujący wynik -

Hello World

PL/SQL procedure successfully completed.

Jednostki programu PL / SQL

Jednostka PL / SQL to dowolna z następujących -

• Blok PL / SQL
• Function
• Package
• Treść pakietu
• Procedure
• Trigger
• Type
• Wpisz treść

Każda z tych jednostek zostanie omówiona w kolejnych rozdziałach.

W tym rozdziale omówimy typy danych w PL / SQL. Zmienne, stałe i parametry PL / SQL muszą mieć prawidłowy typ danych, który określa format przechowywania, ograniczenia i prawidłowy zakres wartości. Skoncentrujemy się naSCALAR i LOBtypy danych w tym rozdziale. Pozostałe dwa typy danych zostaną omówione w innych rozdziałach.

S.Nr	Opis kategorii
1	Scalar Pojedyncze wartości bez komponentów wewnętrznych, takie jak plik NUMBER, DATE, lub BOOLEAN.
2	Large Object (LOB) Wskaźniki do dużych obiektów, które są przechowywane oddzielnie od innych elementów danych, takich jak tekst, obrazy graficzne, klipy wideo i przebiegi dźwiękowe.
3	Composite Elementy danych, które mają komponenty wewnętrzne, do których można uzyskać dostęp indywidualnie. Na przykład kolekcje i rekordy.
4	Reference Wskaźniki do innych elementów danych.

Typy i podtypy danych skalarnych PL / SQL

Typy i podtypy danych skalarnych PL / SQL należą do następujących kategorii -

S.Nr	Typ i opis daty
1	Numeric Wartości liczbowe, na których wykonywane są operacje arytmetyczne.
2	Character Wartości alfanumeryczne, które reprezentują pojedyncze znaki lub ciągi znaków.
3	Boolean Wartości logiczne, na których wykonywane są operacje logiczne.
4	Datetime Daty i godziny.

PL / SQL udostępnia podtypy typów danych. Na przykład typ danych NUMBER ma podtyp o nazwie INTEGER. Możesz użyć podtypów w swoim programie PL / SQL, aby zapewnić zgodność typów danych z typami danych w innych programach, jednocześnie osadzając kod PL / SQL w innym programie, takim jak program Java.

Numeryczne typy danych i podtypy PL / SQL

Poniższa tabela zawiera wstępnie zdefiniowane numeryczne typy danych PL / SQL i ich podtypy -

S.Nr	Typ i opis danych
1	PLS_INTEGER Liczba całkowita ze znakiem z zakresu od -2 147 483 648 do 2 147 483 647, reprezentowana w 32 bitach
2	BINARY_INTEGER Liczba całkowita ze znakiem z zakresu od -2 147 483 648 do 2 147 483 647, reprezentowana w 32 bitach
3	BINARY_FLOAT Liczba zmiennoprzecinkowa w formacie IEEE 754 o pojedynczej precyzji
4	BINARY_DOUBLE Liczba zmiennoprzecinkowa w formacie IEEE 754 o podwójnej precyzji
5	NUMBER(prec, scale) Liczba stałoprzecinkowa lub zmiennoprzecinkowa o wartości bezwzględnej w zakresie od 1E-130 do (ale nie włącznie) 1.0E126. Zmienna NUMBER może również reprezentować 0
6	DEC(prec, scale) Specyficzny dla ANSI typ stałoprzecinkowy z maksymalną precyzją 38 cyfr dziesiętnych
7	DECIMAL(prec, scale) Specyficzny dla IBM typ stałoprzecinkowy o maksymalnej precyzji 38 cyfr dziesiętnych
8	NUMERIC(pre, secale) Typ zmienny z maksymalną precyzją 38 cyfr dziesiętnych
9	DOUBLE PRECISION Typ zmiennoprzecinkowy specyficzny dla ANSI z maksymalną precyzją 126 cyfr binarnych (około 38 cyfr dziesiętnych)
10	FLOAT Typ zmiennoprzecinkowy specyficzny dla ANSI i IBM z maksymalną dokładnością 126 cyfr binarnych (około 38 cyfr dziesiętnych)
11	INT Typ liczb całkowitych specyficzny dla ANSI z maksymalną dokładnością 38 cyfr dziesiętnych
12	INTEGER Typ liczb całkowitych specyficznych dla ANSI i IBM z maksymalną dokładnością 38 cyfr dziesiętnych
13	SMALLINT Typ liczb całkowitych specyficznych dla ANSI i IBM z maksymalną dokładnością 38 cyfr dziesiętnych
14	REAL Typ zmiennoprzecinkowy z maksymalną precyzją 63 cyfr binarnych (około 18 cyfr dziesiętnych)

Poniżej znajduje się ważna deklaracja -

DECLARE 
   num1 INTEGER; 
   num2 REAL; 
   num3 DOUBLE PRECISION; 
BEGIN 
   null; 
END; 
/

Kiedy powyższy kod jest kompilowany i wykonywany, daje następujący wynik -

PL/SQL procedure successfully completed

Typy i podtypy znaków PL / SQL

Poniżej przedstawiono szczegóły wstępnie zdefiniowanych typów danych znakowych PL / SQL i ich podtypów -

S.Nr	Typ i opis danych
1	CHAR Ciąg znaków o stałej długości i maksymalnym rozmiarze 32767 bajtów
2	VARCHAR2 Ciąg znaków o zmiennej długości i maksymalnym rozmiarze 32767 bajtów
3	RAW Ciąg binarny lub bajtowy o zmiennej długości o maksymalnym rozmiarze 32767 bajtów, nieinterpretowany przez PL / SQL
4	NCHAR Ciąg znaków narodowych o stałej długości i maksymalnym rozmiarze 32767 bajtów
5	NVARCHAR2 Ciąg znaków narodowych o zmiennej długości i maksymalnym rozmiarze 32767 bajtów
6	LONG Ciąg znaków o zmiennej długości i maksymalnym rozmiarze 32760 bajtów
7	LONG RAW Ciąg binarny lub bajtowy o zmiennej długości o maksymalnym rozmiarze 32760 bajtów, nieinterpretowany przez PL / SQL
8	ROWID Fizyczny identyfikator wiersza, adres wiersza w zwykłej tabeli
9	UROWID Uniwersalny identyfikator wiersza (fizyczny, logiczny lub obcy identyfikator wiersza)

PL / SQL Boolean Typy danych

Plik BOOLEANtyp danych przechowuje wartości logiczne, które są używane w operacjach logicznych. Wartości logiczne to wartości logiczneTRUE i FALSE i wartość NULL.

Jednak SQL nie ma równoważnego typu danych z BOOLEAN. Dlatego wartości logiczne nie mogą być używane w -

• Instrukcje SQL
• Wbudowane funkcje SQL (takie jak TO_CHAR)
• Funkcje PL / SQL wywoływane z instrukcji SQL

PL / SQL Datetime i Interval Types

Plik DATEdatatype służy do przechowywania danych o stałej długości, które obejmują godzinę w sekundach od północy. Ważne daty obejmują okres od 1 stycznia 4712 rpne do 31 grudnia 9999 r.

Domyślny format daty jest ustawiany przez parametr inicjalizacji Oracle NLS_DATE_FORMAT. Na przykład domyślną wartością może być „DD-MON-RR”, która zawiera dwucyfrową liczbę oznaczającą dzień miesiąca, skrót nazwy miesiąca i dwie ostatnie cyfry roku. Na przykład 01-OCT-12.

Każda DATA zawiera wiek, rok, miesiąc, dzień, godzinę, minutę i sekundę. W poniższej tabeli przedstawiono prawidłowe wartości dla każdego pola -

Nazwa pola	Prawidłowe wartości daty i godziny	Prawidłowe wartości przedziałów
ROK	-4712 do 9999 (z wyłączeniem roku 0)	Dowolna niezerowa liczba całkowita
MIESIĄC	01 do 12	Od 0 do 11
DZIEŃ	01 do 31 (ograniczone wartościami MIESIĄCA i ROKU, zgodnie z regułami kalendarza dla regionu)	Dowolna niezerowa liczba całkowita
GODZINA	00 do 23	Od 0 do 23
MINUTA	00 do 59	Od 0 do 59
DRUGA	00 do 59,9 (n), gdzie 9 (n) jest dokładnością ułamków czasu w sekundach	0 do 59,9 (n), gdzie 9 (n) jest dokładnością ułamków sekund interwału
TIMEZONE_HOUR	-12 do 14 (zakres obejmuje zmiany czasu letniego)	Nie dotyczy
TIMEZONE_MINUTE	00 do 59	Nie dotyczy
TIMEZONE_REGION	Znaleziono w dynamicznym widoku wydajności V $ TIMEZONE_NAMES	Nie dotyczy
TIMEZONE_ABBR	Znaleziono w dynamicznym widoku wydajności V $ TIMEZONE_NAMES	Nie dotyczy

Typy danych PL / SQL Large Object (LOB)

Typy danych Large Object (LOB) odnoszą się do dużych elementów danych, takich jak tekst, obrazy graficzne, klipy wideo i przebiegi dźwiękowe. Typy danych LOB umożliwiają wydajny, losowy i fragmentaryczny dostęp do tych danych. Poniżej przedstawiono predefiniowane typy danych PL / SQL LOB -

Typ danych	Opis	Rozmiar
BFILE	Służy do przechowywania dużych obiektów binarnych w plikach systemu operacyjnego poza bazą danych.	Zależne od systemu. Nie może przekraczać 4 gigabajtów (GB).
KROPELKA	Służy do przechowywania dużych obiektów binarnych w bazie danych.	8 do 128 terabajtów (TB)
CLOB	Służy do przechowywania dużych bloków danych znakowych w bazie danych.	Od 8 do 128 TB
NCLOB	Służy do przechowywania dużych bloków danych NCHAR w bazie danych.	Od 8 do 128 TB

Podtypy PL / SQL zdefiniowane przez użytkownika

Podtyp jest podzbiorem innego typu danych, nazywanego jego typem podstawowym. Podtyp ma te same prawidłowe operacje, co jego typ podstawowy, ale tylko podzbiór jego prawidłowych wartości.

PL / SQL wstępnie definiuje kilka podtypów w pakiecie STANDARD. Na przykład PL / SQL wstępnie definiuje podtypyCHARACTER i INTEGER w następujący sposób -

SUBTYPE CHARACTER IS CHAR; 
SUBTYPE INTEGER IS NUMBER(38,0);

Możesz zdefiniować i używać własnych podtypów. Poniższy program ilustruje definiowanie i używanie podtypu zdefiniowanego przez użytkownika -

DECLARE 
   SUBTYPE name IS char(20); 
   SUBTYPE message IS varchar2(100); 
   salutation name; 
   greetings message; 
BEGIN 
   salutation := 'Reader '; 
   greetings := 'Welcome to the World of PL/SQL'; 
   dbms_output.put_line('Hello ' || salutation || greetings); 
END; 
/

Gdy powyższy kod jest wykonywany w wierszu polecenia SQL, daje następujący wynik -

Hello Reader Welcome to the World of PL/SQL 
 
PL/SQL procedure successfully completed.

NULL w PL / SQL

Reprezentują wartości NULL PL / SQL missing lub unknown datai nie są one liczbą całkowitą, znakiem ani żadnym innym określonym typem danych. Zwróć na to uwagęNULL nie jest tym samym, co pusty ciąg danych lub wartość znaku null '\0'. Można przypisać wartość null, ale nie można jej utożsamiać z niczym, łącznie z samym sobą.

W tym rozdziale omówimy zmienne w Pl / SQL. Zmienna to nic innego jak nazwa nadana obszarowi pamięci, którym nasze programy mogą manipulować. Każda zmienna w PL / SQL ma określony typ danych, który określa rozmiar i układ pamięci zmiennej; zakres wartości, które mogą być przechowywane w tej pamięci, oraz zestaw operacji, które można zastosować do zmiennej.

Nazwa zmiennej PL / SQL składa się z litery, po której opcjonalnie może następować więcej liter, cyfr, znaków dolara, podkreśleń i znaków liczbowych i nie powinna przekraczać 30 znaków. Domyślnie w nazwach zmiennych wielkość liter nie jest rozróżniana. Nie można użyć zastrzeżonego słowa kluczowego PL / SQL jako nazwy zmiennej.

Język programowania PL / SQL pozwala na definiowanie różnych typów zmiennych, takich jak typy danych daty i godziny, rekordy, zbiory itp., Które omówimy w kolejnych rozdziałach. W tym rozdziale przyjrzyjmy się tylko podstawowym typom zmiennych.

Deklaracja zmiennej w PL / SQL

Zmienne PL / SQL muszą być zadeklarowane w sekcji deklaracji lub w pakiecie jako zmienna globalna. Kiedy deklarujesz zmienną, PL / SQL alokuje pamięć dla wartości zmiennej, a miejsce przechowywania jest identyfikowane przez nazwę zmiennej.

Składnia deklarowania zmiennej to -

variable_name [CONSTANT] datatype [NOT NULL] [:= | DEFAULT initial_value]

Gdzie nazwa_zmiennej jest poprawnym identyfikatorem w PL / SQL, typ danych musi być prawidłowym typem danych PL / SQL lub dowolnym typem danych zdefiniowanym przez użytkownika, który omówiliśmy już w poprzednim rozdziale. Poniżej przedstawiono niektóre prawidłowe deklaracje zmiennych wraz z ich definicją -

sales number(10, 2); 
pi CONSTANT double precision := 3.1415; 
name varchar2(25); 
address varchar2(100);

Kiedy podajesz rozmiar, skalę lub limit precyzji z typem danych, jest to nazywane constrained declaration. Deklaracje ograniczone wymagają mniej pamięci niż deklaracje nieograniczone. Na przykład -

sales number(10, 2); 
name varchar2(25); 
address varchar2(100);

Inicjalizacja zmiennych w PL / SQL

Za każdym razem, gdy deklarujesz zmienną, PL / SQL przypisuje jej domyślną wartość NULL. Jeśli chcesz zainicjować zmienną o wartości innej niż wartość NULL, możesz to zrobić podczas deklaracji, używając jednego z następujących -

• Plik DEFAULT słowo kluczowe

• Plik assignment operator

Na przykład -

counter binary_integer := 0; 
greetings varchar2(20) DEFAULT 'Have a Good Day';

Możesz również określić, że zmienna nie powinna mieć rozszerzenia NULL wartość za pomocą NOT NULLprzymus. Jeśli używasz ograniczenia NOT NULL, musisz jawnie przypisać wartość początkową dla tej zmiennej.

Dobrą praktyką programistyczną jest prawidłowe inicjowanie zmiennych, w przeciwnym razie czasami programy mogą dawać nieoczekiwane rezultaty. Wypróbuj następujący przykład, który wykorzystuje różne typy zmiennych -

DECLARE 
   a integer := 10; 
   b integer := 20; 
   c integer; 
   f real; 
BEGIN 
   c := a + b; 
   dbms_output.put_line('Value of c: ' || c); 
   f := 70.0/3.0; 
   dbms_output.put_line('Value of f: ' || f); 
END; 
/

Wykonanie powyższego kodu daje następujący wynik -

Value of c: 30 
Value of f: 23.333333333333333333  

PL/SQL procedure successfully completed.

Zmienny zakres w PL / SQL

PL / SQL umożliwia zagnieżdżanie bloków, tj. Każdy blok programu może zawierać inny blok wewnętrzny. Jeśli zmienna jest zadeklarowana w bloku wewnętrznym, nie jest dostępna dla bloku zewnętrznego. Jeśli jednak zmienna jest zadeklarowana i dostępna dla bloku zewnętrznego, jest również dostępna dla wszystkich zagnieżdżonych bloków wewnętrznych. Istnieją dwa rodzaje zakresu zmiennych -

• Local variables - Zmienne zadeklarowane w bloku wewnętrznym i niedostępne dla bloków zewnętrznych.

• Global variables - Zmienne zadeklarowane w najbardziej zewnętrznym bloku lub pakiecie.

Poniższy przykład pokazuje użycie Local i Global zmienne w swojej prostej formie -

DECLARE 
   -- Global variables  
   num1 number := 95;  
   num2 number := 85;  
BEGIN  
   dbms_output.put_line('Outer Variable num1: ' || num1); 
   dbms_output.put_line('Outer Variable num2: ' || num2); 
   DECLARE  
      -- Local variables 
      num1 number := 195;  
      num2 number := 185;  
   BEGIN  
      dbms_output.put_line('Inner Variable num1: ' || num1); 
      dbms_output.put_line('Inner Variable num2: ' || num2); 
   END;  
END; 
/

Wykonanie powyższego kodu daje następujący wynik -

Outer Variable num1: 95 
Outer Variable num2: 85 
Inner Variable num1: 195 
Inner Variable num2: 185  

PL/SQL procedure successfully completed.

Przypisywanie wyników zapytania SQL do zmiennych PL / SQL

Możesz użyć SELECT INTOinstrukcja SQL w celu przypisania wartości do zmiennych PL / SQL. Dla każdego elementu wSELECT list, w pliku musi znajdować się odpowiednia zmienna zgodna z typem INTO list. Poniższy przykład ilustruje tę koncepcję. Utwórzmy tabelę o nazwie CUSTOMERS -

(For SQL statements, please refer to the SQL tutorial)

CREATE TABLE CUSTOMERS( 
   ID   INT NOT NULL, 
   NAME VARCHAR (20) NOT NULL, 
   AGE INT NOT NULL, 
   ADDRESS CHAR (25), 
   SALARY   DECIMAL (18, 2),        
   PRIMARY KEY (ID) 
);  

Table Created

Wstawmy teraz kilka wartości do tabeli -

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) 
VALUES (1, 'Ramesh', 32, 'Ahmedabad', 2000.00 );  

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) 
VALUES (2, 'Khilan', 25, 'Delhi', 1500.00 );  

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) 
VALUES (3, 'kaushik', 23, 'Kota', 2000.00 );
  
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) 
VALUES (4, 'Chaitali', 25, 'Mumbai', 6500.00 ); 
 
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) 
VALUES (5, 'Hardik', 27, 'Bhopal', 8500.00 );  

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) 
VALUES (6, 'Komal', 22, 'MP', 4500.00 );

Poniższy program przypisuje wartości z powyższej tabeli do zmiennych PL / SQL za pomocą SELECT INTO clause SQL -

DECLARE 
   c_id customers.id%type := 1; 
   c_name  customers.name%type; 
   c_addr customers.address%type; 
   c_sal  customers.salary%type; 
BEGIN 
   SELECT name, address, salary INTO c_name, c_addr, c_sal 
   FROM customers 
   WHERE id = c_id;  
   dbms_output.put_line 
   ('Customer ' ||c_name || ' from ' || c_addr || ' earns ' || c_sal); 
END; 
/

Wykonanie powyższego kodu daje następujący wynik -

Customer Ramesh from Ahmedabad earns 2000  

PL/SQL procedure completed successfully

W tym rozdziale omówimy constants i literalsw PL / SQL. Stała posiada wartość, która raz zadeklarowana nie zmienia się w programie. Stała deklaracja określa jej nazwę, typ danych i wartość oraz przydziela dla niej miejsce na dane. Deklaracja może również narzucićNOT NULL constraint.

Deklarowanie stałej

Stała jest deklarowana przy użyciu CONSTANTsłowo kluczowe. Wymaga wartości początkowej i nie pozwala na zmianę tej wartości. Na przykład -

PI CONSTANT NUMBER := 3.141592654; 
DECLARE 
   -- constant declaration 
   pi constant number := 3.141592654; 
   -- other declarations 
   radius number(5,2);  
   dia number(5,2);  
   circumference number(7, 2); 
   area number (10, 2); 
BEGIN  
   -- processing 
   radius := 9.5;  
   dia := radius * 2;  
   circumference := 2.0 * pi * radius; 
   area := pi * radius * radius; 
   -- output 
   dbms_output.put_line('Radius: ' || radius); 
   dbms_output.put_line('Diameter: ' || dia); 
   dbms_output.put_line('Circumference: ' || circumference); 
   dbms_output.put_line('Area: ' || area); 
END; 
/

Gdy powyższy kod jest wykonywany w wierszu polecenia SQL, daje następujący wynik -

Radius: 9.5 
Diameter: 19 
Circumference: 59.69 
Area: 283.53  

Pl/SQL procedure successfully completed.

Literały PL / SQL

Literał to jawna liczba, znak, ciąg znaków lub wartość logiczna, która nie jest reprezentowana przez identyfikator. Na przykład TRUE, 786, NULL, „tutorialspoint” to wszystkie literały typu Boolean, number lub string. PL / SQL, w literałach jest rozróżniana wielkość liter. PL / SQL obsługuje następujące rodzaje literałów -

• Literały numeryczne
• Literały postaci
• Literały strunowe
• Literały BOOLEAN
• Literały daty i czasu

Poniższa tabela zawiera przykłady ze wszystkich kategorii wartości literałów.

S.Nr	Typ i przykład literału
1	Numeric Literals 050 78-14 0 +32767 6,6667 0,0 -12,0 3,14159 +7800,00 6E5 1,0E-8 3,14159e0 -1E38 -9,5e-3
2	Character Literals 'A' '%' '9' '' 'z' '('
3	String Literals 'Witaj świecie!' „Punkt samouczków” „19-listopada-12”
4	BOOLEAN Literals TRUE, FALSE i NULL.
5	Date and Time Literals DATA „1978-12-25”; TIMESTAMP '2012-10-29 12:01:01';

Aby osadzić pojedyncze cudzysłowy w literale ciągu, umieść dwa pojedyncze cudzysłowy obok siebie, jak pokazano w poniższym programie -

DECLARE 
   message  varchar2(30):= 'That''s tutorialspoint.com!'; 
BEGIN 
   dbms_output.put_line(message); 
END; 
/

Gdy powyższy kod jest wykonywany w wierszu polecenia SQL, daje następujący wynik -

That's tutorialspoint.com!  

PL/SQL procedure successfully completed.

W tym rozdziale omówimy operatory w PL / SQL. Operator to symbol, który mówi kompilatorowi, aby wykonał określone operacje matematyczne lub logiczne. Język PL / SQL jest bogaty we wbudowane operatory i zapewnia następujące typy operatorów -

• Operatory arytmetyczne
• Operatorzy relacyjni
• Operatory porównania
• Operatory logiczne
• Operatory łańcuchowe

Tutaj zrozumiemy kolejno operatory arytmetyczne, relacyjne, porównawcze i logiczne. Operatory łańcuchowe zostaną omówione w kolejnym rozdziale -PL/SQL - Strings.

Operatory arytmetyczne

Poniższa tabela przedstawia wszystkie operatory arytmetyczne obsługiwane przez PL / SQL. Załóżmyvariable A mieści 10 i variable B mieści 5, a następnie -

Pokaż przykłady

Operator	Opis	Przykład
+	Dodaje dwa operandy	A + B da 15
-	Odejmuje drugi operand od pierwszego	A - B da 5
*	Mnoży oba operandy	A * B da 50
/	Dzieli licznik przez de-licznik	A / B da 2
**	Operator potęgowania podnosi jeden operand do potęgi drugiego	A ** B da 100000

Operatorzy relacyjni

Operatory relacyjne porównują dwa wyrażenia lub wartości i zwracają wynik logiczny. Poniższa tabela przedstawia wszystkie operatory relacyjne obsługiwane przez PL / SQL. Załóżmyvariable A mieści 10 i variable B mieści 20, a następnie -

Pokaż przykłady

Operator	Opis	Przykład
=	Sprawdza, czy wartości dwóch operandów są równe, czy nie, jeśli tak, warunek staje się prawdziwy.	(A = B) nie jest prawdą.
! = <> ~ =	Sprawdza, czy wartości dwóch operandów są równe, czy nie, jeśli wartości nie są równe, warunek staje się prawdziwy.	(A! = B) jest prawdą.
>	Sprawdza, czy wartość lewego operandu jest większa niż wartość prawego operandu, jeśli tak, warunek staje się prawdziwy.	(A> B) nie jest prawdą.
<	Sprawdza, czy wartość lewego operandu jest mniejsza niż wartość prawego operandu. Jeśli tak, warunek staje się prawdziwy.	(A <B) jest prawdą.
> =	Sprawdza, czy wartość lewego operandu jest większa lub równa wartości prawego operandu, jeśli tak, warunek staje się prawdziwy.	(A> = B) nie jest prawdą.
<=	Sprawdza, czy wartość lewego operandu jest mniejsza lub równa wartości prawego operandu, jeśli tak, warunek staje się prawdziwy.	(A <= B) jest prawdą

Operatory porównania

Operatory porównania służą do porównywania jednego wyrażenia z innym. Wynik jest zawsze alboTRUE, FALSE lub NULL.

Pokaż przykłady

Operator	Opis	Przykład
LUBIĆ	Operator LIKE porównuje znak, ciąg lub wartość CLOB ze wzorcem i zwraca wartość TRUE, jeśli wartość jest zgodna ze wzorcem, lub FALSE, jeśli nie.	Jeśli „Zara Ali”, jak „Z% A_i”, zwraca wartość logiczną true, podczas gdy „Nuha Ali”, podobnie jak „Z% A_i”, zwraca wartość logiczną fałsz.
POMIĘDZY	Operator BETWEEN sprawdza, czy wartość mieści się w określonym zakresie. x BETWEEN a AND b oznacza, że ​​x> = a i x <= b.	Jeśli x = 10, wtedy x między 5 a 20 zwraca prawdę, x między 5 a 10 zwraca prawdę, ale x między 11 a 20 zwraca fałsz.
W	Operator IN testuje członkostwo w zestawie. x IN (zbiór) oznacza, że ​​x jest równe dowolnemu elementowi zbioru.	Jeśli x = 'm', wtedy x in ('a', 'b', 'c') zwraca wartość logiczną fałsz, ale x in ('m', 'n', 'o') zwraca wartość logiczną prawda.
JEST NULL	Operator IS NULL zwraca wartość typu BOOLEAN TRUE, jeśli jego operand ma wartość NULL lub FALSE, jeśli nie ma wartości NULL. Porównania obejmujące wartości NULL zawsze dają NULL.	Jeśli x = „m”, to „x jest null” zwraca wartość logiczną fałsz.

Operatory logiczne

Poniższa tabela przedstawia operatory logiczne obsługiwane przez PL / SQL. Wszystkie te operatory działają na operandach boolowskich i dają wyniki boolowskie. Załóżmyvariable A jest prawdą i variable B ma fałsz, więc -

Pokaż przykłady

Operator	Opis	Przykłady
i	Nazywa się operatorem logicznym AND. Jeśli oba operandy są prawdziwe, warunek staje się prawdziwy.	(A i B) jest fałszem.
lub	Nazywa się operatorem logicznym OR. Jeśli którykolwiek z dwóch operandów jest prawdziwy, warunek staje się prawdziwy.	(A lub B) jest prawdą.
nie	Nazywany operatorem logicznym NOT. Służy do odwracania stanu logicznego argumentu. Jeśli warunek jest prawdziwy, operator logiczny NOT sprawi, że będzie fałszywy.	nie (A i B) jest prawdą.

Pierwszeństwo operatorów PL / SQL

Pierwszeństwo operatorów określa grupowanie terminów w wyrażeniu. Ma to wpływ na sposób oceny wyrażenia. Niektórzy operatorzy mają wyższy priorytet niż inni; na przykład operator mnożenia ma wyższy priorytet niż operator dodawania.

Na przykład, x = 7 + 3 * 2; tutaj,x jest przypisany 13, a nie 20, ponieważ operator * ma wyższy priorytet niż +, więc najpierw jest mnożony przez 3*2 a następnie dodaje do 7.

Tutaj operatory o najwyższym priorytecie pojawiają się na górze tabeli, a operatory o najniższym priorytecie - na dole. W wyrażeniu najpierw zostaną ocenione operatory o wyższym priorytecie.

Pierwszeństwo operatorów jest następujące: =, <,>, <=,> =, <>,! =, ~ =, ^ =, IS NULL, LIKE, BETWEEN, IN.

Pokaż przykłady

Operator	Operacja
**	potęgowanie
+, -	tożsamość, negacja
*, /	mnożenie, dzielenie
+, -, ||	dodawanie, odejmowanie, łączenie
porównanie
NIE	logiczna negacja
I	spójnik
LUB	włączenie

W tym rozdziale omówimy warunki w PL / SQL. Struktury decyzyjne wymagają, aby programista określił jeden lub więcej warunków, które mają być ocenione lub przetestowane przez program, wraz z instrukcją lub instrukcjami do wykonania, jeśli warunek zostanie określony jako prawdziwy, i opcjonalnie inne instrukcje do wykonania, warunek jest określany jako fałszywy.

Poniżej przedstawiono ogólną postać typowej warunkowej (tj. Decyzyjnej) struktury występującej w większości języków programowania -

Język programowania PL / SQL udostępnia następujące typy instrukcji decyzyjnych. Kliknij poniższe łącza, aby sprawdzić ich szczegóły.

S.Nr	Oświadczenie i opis
1	JEŚLI - TO instrukcja Plik IF statement wiąże warunek z sekwencją instrukcji zawartych w słowach kluczowych THEN i END IF. Jeśli warunek jest prawdziwy, instrukcje są wykonywane, a jeśli warunek jest fałszywy lub NULL, instrukcja IF nic nie robi.
2	Instrukcja IF-THEN-INSE IF statement dodaje słowo kluczowe ELSEpo którym następuje alternatywna sekwencja instrukcji. Jeśli warunek ma wartość false lub NULL, zostanie wykonana tylko alternatywna sekwencja instrukcji. Zapewnia wykonanie dowolnej sekwencji instrukcji.
3	Instrukcja IF-THEN-ELSIF Umożliwia wybór między kilkoma alternatywami.
4	Oświadczenie Case Podobnie jak instrukcja IF, plik CASE statement wybiera jedną sekwencję instrukcji do wykonania. Jednak aby wybrać sekwencję, instrukcja CASE używa selektora, a nie wielu wyrażeń logicznych. Selektor to wyrażenie, którego wartość służy do wyboru jednej z kilku alternatyw.
5	Szukana instrukcja CASE Szukana instrukcja CASE has no selector, a klauzule to WHEN zawierają warunki wyszukiwania, które dają wartości logiczne.
6	zagnieżdżone IF-THEN-INNE Możesz użyć jednego IF-THEN lub IF-THEN-ELSIF oświadczenie wewnątrz innego IF-THEN lub IF-THEN-ELSIF sprawozdania).

W tym rozdziale omówimy pętle w PL / SQL. Może zaistnieć sytuacja, w której trzeba będzie kilkakrotnie wykonać blok kodu. Ogólnie instrukcje są wykonywane sekwencyjnie: pierwsza instrukcja funkcji jest wykonywana jako pierwsza, po niej następuje druga i tak dalej.

Języki programowania zapewniają różne struktury kontrolne, które pozwalają na bardziej skomplikowane ścieżki wykonywania.

Instrukcja pętli pozwala nam wielokrotnie wykonać instrukcję lub grupę instrukcji, a następująca po niej jest ogólną postacią instrukcji pętli w większości języków programowania -

PL / SQL udostępnia następujące typy pętli do obsługi wymagań dotyczących pętli. Kliknij poniższe łącza, aby sprawdzić ich szczegóły.

S.Nr	Typ i opis pętli
1	PL / SQL Basic LOOP W tej strukturze pętli sekwencja instrukcji jest zawarta między instrukcjami LOOP i END LOOP. W każdej iteracji wykonywana jest sekwencja instrukcji, a następnie sterowanie wznawia się na początku pętli.
2	PL / SQL WHILE LOOP Powtarza instrukcję lub grupę instrukcji, gdy dany warunek jest prawdziwy. Testuje warunek przed wykonaniem treści pętli.
3	PL / SQL FOR LOOP Wykonuje sekwencję instrukcji wiele razy i skraca kod zarządzający zmienną pętli.
4	Zagnieżdżone pętle w PL / SQL Możesz użyć jednej lub więcej pętli wewnątrz dowolnej innej podstawowej pętli, pętli while lub for.

Etykietowanie pętli PL / SQL

Pętle PL / SQL można oznaczyć. Etykieta powinna być ujęta w podwójne nawiasy ostre (<< i >>) i pojawić się na początku instrukcji LOOP. Nazwa etykiety może również pojawić się na końcu instrukcji LOOP. Możesz użyć etykiety w instrukcji EXIT, aby wyjść z pętli.

Poniższy program ilustruje koncepcję -

DECLARE 
   i number(1); 
   j number(1); 
BEGIN 
   << outer_loop >> 
   FOR i IN 1..3 LOOP 
      << inner_loop >> 
      FOR j IN 1..3 LOOP 
         dbms_output.put_line('i is: '|| i || ' and j is: ' || j); 
      END loop inner_loop; 
   END loop outer_loop; 
END; 
/

Gdy powyższy kod jest wykonywany w wierszu polecenia SQL, daje następujący wynik -

i is: 1 and j is: 1 
i is: 1 and j is: 2 
i is: 1 and j is: 3 
i is: 2 and j is: 1 
i is: 2 and j is: 2 
i is: 2 and j is: 3 
i is: 3 and j is: 1 
i is: 3 and j is: 2 
i is: 3 and j is: 3  

PL/SQL procedure successfully completed.

Instrukcje sterujące pętlą

Instrukcje sterujące pętlą zmieniają wykonanie z jego normalnej sekwencji. Gdy wykonanie opuszcza zakres, wszystkie automatyczne obiekty utworzone w tym zakresie są niszczone.

PL / SQL obsługuje następujące instrukcje sterujące. Etykietowanie pętli pomaga również w przenoszeniu sterowania poza pętlę. Kliknij poniższe łącza, aby sprawdzić ich szczegóły.

S.Nr	Oświadczenie i opis kontroli
1	Instrukcja EXIT Instrukcja Exit kończy pętlę i sterowanie przechodzi do instrukcji bezpośrednio po zakończeniu pętli END LOOP.
2	Instrukcja CONTINUE Powoduje, że pętla pomija pozostałą część swojego ciała i natychmiast ponownie testuje swój stan przed ponownym powtórzeniem.
3	Instrukcja GOTO Przekazuje kontrolę do oznaczonej instrukcji. Chociaż nie jest zalecane używanie w programie instrukcji GOTO.

Łańcuch w PL / SQL jest w rzeczywistości sekwencją znaków z opcjonalną specyfikacją rozmiaru. Znaki mogą być numeryczne, litery, spacje, znaki specjalne lub kombinacja wszystkich. PL / SQL oferuje trzy rodzaje ciągów -

• Fixed-length strings- W takich łańcuchach programiści określają długość podczas deklarowania łańcucha. Ciąg jest dopełniony spacjami do tak określonej długości.

• Variable-length strings - W takich łańcuchach określona jest maksymalna długość do 32767 dla ciągu znaków i nie ma miejsca dopełnianie.

• Character large objects (CLOBs) - Są to ciągi o zmiennej długości, które mogą mieć do 128 terabajtów.

Łańcuchy PL / SQL mogą być zmiennymi lub literałami. Literał ciągu jest ujęty w cudzysłów. Na przykład,

'This is a string literal.' Or 'hello world'

Aby zawrzeć pojedynczy cudzysłów wewnątrz literału ciągu, musisz wpisać dwa pojedyncze cudzysłowy obok siebie. Na przykład,

'this isn''t what it looks like'

Deklarowanie zmiennych łańcuchowych

Baza danych Oracle udostępnia wiele typów danych typu string, takich jak CHAR, NCHAR, VARCHAR2, NVARCHAR2, CLOB i NCLOB. Typy danych poprzedzone przedrostkiem'N' są 'national character set' typy danych, które przechowują dane znaków Unicode.

Jeśli chcesz zadeklarować ciąg o zmiennej długości, musisz podać maksymalną długość tego ciągu. Na przykład typ danych VARCHAR2. Poniższy przykład ilustruje deklarowanie i używanie niektórych zmiennych łańcuchowych -

DECLARE 
   name varchar2(20); 
   company varchar2(30); 
   introduction clob; 
   choice char(1); 
BEGIN 
   name := 'John Smith'; 
   company := 'Infotech'; 
   introduction := ' Hello! I''m John Smith from Infotech.'; 
   choice := 'y'; 
   IF choice = 'y' THEN 
      dbms_output.put_line(name); 
      dbms_output.put_line(company); 
      dbms_output.put_line(introduction); 
   END IF; 
END; 
/

Gdy powyższy kod jest wykonywany w wierszu polecenia SQL, daje następujący wynik -

John Smith 
Infotech
Hello! I'm John Smith from Infotech.  

PL/SQL procedure successfully completed

Aby zadeklarować ciąg o stałej długości, użyj typu danych CHAR. Tutaj nie musisz określać maksymalnej długości dla zmiennej o stałej długości. Jeśli pominiesz ograniczenie długości, baza danych Oracle automatycznie zastosuje maksymalną wymaganą długość. Następujące dwie deklaracje są identyczne -

red_flag CHAR(1) := 'Y'; 
 red_flag CHAR   := 'Y';

Funkcje i operatory łańcuchów PL / SQL

PL / SQL oferuje operator konkatenacji (||)do łączenia dwóch strun. Poniższa tabela zawiera funkcje tekstowe zapewniane przez PL / SQL -

S.Nr	Funkcja i cel
1	ASCII(x); Zwraca wartość ASCII znaku x.
2	CHR(x); Zwraca znak o wartości x w ASCII.
3	CONCAT(x, y); Łączy ciągi x i y i zwraca dołączony ciąg.
4	INITCAP(x); Konwertuje początkową literę każdego słowa x na wielkie litery i zwraca ten ciąg.
5	INSTR(x, find_string [, start] [, occurrence]); Wyszukuje find_string inx i zwraca pozycję, w której występuje.
6	INSTRB(x); Zwraca lokalizację ciągu w innym ciągu, ale zwraca wartość w bajtach.
7	LENGTH(x); Zwraca liczbę znaków w x.
8	LENGTHB(x); Zwraca długość ciągu znaków w bajtach dla zestawu znaków jednobajtowych.
9	LOWER(x); Konwertuje litery x na małe i zwraca ten ciąg.
10	LPAD(x, width [, pad_string]) ; Naramienniki x ze spacjami po lewej stronie, aby zwiększyć całkowitą długość ciągu do znaków szerokości.
11	LTRIM(x [, trim_string]); Przycina znaki z lewej strony x.
12	NANVL(x, value); Zwraca wartość, jeśli x pasuje do wartości specjalnej NaN (nie liczby), w przeciwnym razie x jest zwracany.
13	NLS_INITCAP(x); Taka sama, jak funkcja INITCAP, z tą różnicą, że może używać innej metody sortowania określonej przez NLSSORT.
14	NLS_LOWER(x) ; Taka sama jak funkcja LOWER, z tą różnicą, że może używać innej metody sortowania określonej przez NLSSORT.
15	NLS_UPPER(x); Taka sama jak funkcja UPPER, z tą różnicą, że może używać innej metody sortowania określonej przez NLSSORT.
16	NLSSORT(x); Zmienia metodę sortowania znaków. Musi być określona przed jakąkolwiek funkcją NLS; w przeciwnym razie zostanie użyte domyślne sortowanie.
17	NVL(x, value); Zwraca wartość, jeśli xjest null; w przeciwnym razie zwracane jest x.
18	NVL2(x, value1, value2); Zwraca wartość1, jeśli x nie jest null; jeśli x ma wartość null, zwracana jest wartość2.
19	REPLACE(x, search_string, replace_string); Wyszukiwania x dla search_string i zamienia go na replace_string.
20	RPAD(x, width [, pad_string]); Naramienniki x w prawo.
21	RTRIM(x [, trim_string]); Wykończenia x z prawej strony.
22	SOUNDEX(x) ; Returns a string containing the phonetic representation of x.
23	SUBSTR(x, start [, length]); Returns a substring of x that begins at the position specified by start. An optional length for the substring may be supplied.
24	SUBSTRB(x); Same as SUBSTR except that the parameters are expressed in bytes instead of characters for the single-byte character systems.
25	TRIM([trim_char FROM) x); Trims characters from the left and right of x.
26	UPPER(x); Converts the letters in x to uppercase and returns that string.

Przeanalizujmy teraz kilka przykładów, aby zrozumieć koncepcję -

Przykład 1

DECLARE 
   greetings varchar2(11) := 'hello world'; 
BEGIN 
   dbms_output.put_line(UPPER(greetings)); 
    
   dbms_output.put_line(LOWER(greetings)); 
    
   dbms_output.put_line(INITCAP(greetings)); 
    
   /* retrieve the first character in the string */ 
   dbms_output.put_line ( SUBSTR (greetings, 1, 1)); 
    
   /* retrieve the last character in the string */ 
   dbms_output.put_line ( SUBSTR (greetings, -1, 1)); 
    
   /* retrieve five characters,  
      starting from the seventh position. */ 
   dbms_output.put_line ( SUBSTR (greetings, 7, 5)); 
    
   /* retrieve the remainder of the string, 
      starting from the second position. */ 
   dbms_output.put_line ( SUBSTR (greetings, 2)); 
     
   /* find the location of the first "e" */ 
   dbms_output.put_line ( INSTR (greetings, 'e')); 
END; 
/

Gdy powyższy kod jest wykonywany w wierszu polecenia SQL, daje następujący wynik -

HELLO WORLD 
hello world 
Hello World 
h 
d 
World 
ello World 
2  

PL/SQL procedure successfully completed.

Przykład 2

DECLARE 
   greetings varchar2(30) := '......Hello World.....'; 
BEGIN 
   dbms_output.put_line(RTRIM(greetings,'.')); 
   dbms_output.put_line(LTRIM(greetings, '.')); 
   dbms_output.put_line(TRIM( '.' from greetings)); 
END; 
/

Gdy powyższy kod jest wykonywany w wierszu polecenia SQL, daje następujący wynik -

......Hello World  
Hello World..... 
Hello World  

PL/SQL procedure successfully completed.

W tym rozdziale omówimy tablice w PL / SQL. Język programowania PL / SQL zapewnia strukturę danych zwanąVARRAY, który może przechowywać sekwencyjną kolekcję elementów tego samego typu o stałym rozmiarze. Zmienna służy do przechowywania uporządkowanego zbioru danych, jednak często lepiej jest myśleć o tablicy jako o zbiorze zmiennych tego samego typu.

Wszystkie warianty składają się z ciągłych miejsc pamięci. Najniższy adres odpowiada pierwszemu elementowi, a najwyższy adres ostatniemu elementowi.

Tablica jest częścią danych typu kolekcji i oznacza tablice o zmiennej wielkości. W następnym rozdziale przeanalizujemy inne typy kolekcji'PL/SQL Collections'.

Każdy element w varrayma przypisany indeks. Ma również maksymalny rozmiar, który można dynamicznie zmieniać.

Tworzenie typu Varray

Typ varray jest tworzony za pomocą CREATE TYPEkomunikat. Musisz określić maksymalny rozmiar i typ elementów przechowywanych w varray.

Podstawowa składnia tworzenia typu VARRAY na poziomie schematu to -

CREATE OR REPLACE TYPE varray_type_name IS VARRAY(n) of <element_type>

Gdzie,

• varray_type_name to poprawna nazwa atrybutu,
• n to liczba elementów (maksymalna) w varray,
• typ_elementu to typ danych elementów tablicy.

Maksymalny rozmiar varray można zmienić za pomocą ALTER TYPE komunikat.

Na przykład,

CREATE Or REPLACE TYPE namearray AS VARRAY(3) OF VARCHAR2(10); 
/ 

Type created.

Podstawowa składnia tworzenia typu VARRAY w bloku PL / SQL to -

TYPE varray_type_name IS VARRAY(n) of <element_type>

Na przykład -

TYPE namearray IS VARRAY(5) OF VARCHAR2(10); 
Type grades IS VARRAY(5) OF INTEGER;

Przeanalizujmy teraz kilka przykładów, aby zrozumieć koncepcję -

Przykład 1

Poniższy program ilustruje użycie varrays -

DECLARE 
   type namesarray IS VARRAY(5) OF VARCHAR2(10); 
   type grades IS VARRAY(5) OF INTEGER; 
   names namesarray; 
   marks grades; 
   total integer; 
BEGIN 
   names := namesarray('Kavita', 'Pritam', 'Ayan', 'Rishav', 'Aziz'); 
   marks:= grades(98, 97, 78, 87, 92); 
   total := names.count; 
   dbms_output.put_line('Total '|| total || ' Students'); 
   FOR i in 1 .. total LOOP 
      dbms_output.put_line('Student: ' || names(i) || ' 
      Marks: ' || marks(i)); 
   END LOOP; 
END; 
/

Gdy powyższy kod jest wykonywany w wierszu polecenia SQL, daje następujący wynik -

Total 5 Students 
Student: Kavita  Marks: 98 
Student: Pritam  Marks: 97 
Student: Ayan  Marks: 78 
Student: Rishav  Marks: 87 
Student: Aziz  Marks: 92 

PL/SQL procedure successfully completed.

Please note -

• W środowisku Oracle początkowy indeks varray wynosi zawsze 1.

• Elementy varray można zainicjować przy użyciu metody konstruktora typu varray, który ma taką samą nazwę jak varray.

• Warray to jednowymiarowe tablice.

• Zmienna jest automatycznie NULL, gdy jest zadeklarowana i musi zostać zainicjowana, zanim będzie można przywołać jej elementy.

Przykład 2

Elementy varray mogą być również% ROWTYPE dowolnej tabeli bazy danych lub% TYPE dowolnego pola tabeli bazy danych. Poniższy przykład ilustruje tę koncepcję.

Będziemy używać tabeli CUSTOMERS przechowywanej w naszej bazie danych jako -

Select * from customers;  

+----+----------+-----+-----------+----------+ 
| ID | NAME     | AGE | ADDRESS   | SALARY   | 
+----+----------+-----+-----------+----------+ 
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 | 
|  2 | Khilan   |  25 | Delhi     |  1500.00 | 
|  3 | kaushik  |  23 | Kota      |  2000.00 | 
|  4 | Chaitali |  25 | Mumbai    |  6500.00 | 
|  5 | Hardik   |  27 | Bhopal    |  8500.00 | 
|  6 | Komal    |  22 | MP        |  4500.00 | 
+----+----------+-----+-----------+----------+

Poniższy przykład wykorzystuje cursor, które szczegółowo przestudiujesz w osobnym rozdziale.

DECLARE 
   CURSOR c_customers is 
   SELECT  name FROM customers; 
   type c_list is varray (6) of customers.name%type; 
   name_list c_list := c_list(); 
   counter integer :=0; 
BEGIN 
   FOR n IN c_customers LOOP 
      counter := counter + 1; 
      name_list.extend; 
      name_list(counter)  := n.name; 
      dbms_output.put_line('Customer('||counter ||'):'||name_list(counter)); 
   END LOOP; 
END; 
/

Gdy powyższy kod jest wykonywany w wierszu polecenia SQL, daje następujący wynik -

Customer(1): Ramesh  
Customer(2): Khilan  
Customer(3): kaushik     
Customer(4): Chaitali  
Customer(5): Hardik  
Customer(6): Komal  

PL/SQL procedure successfully completed.

W tym rozdziale omówimy procedury w PL / SQL. ZAsubprogramto jednostka / moduł programu, który wykonuje określone zadanie. Te podprogramy są łączone w celu utworzenia większych programów. Zasadniczo nazywa się to „konstrukcją modułową”. Podprogram może być wywołany przez inny podprogram lub program, który nosi nazwęcalling program.

Można utworzyć podprogram -

• Na poziomie schematu
• W opakowaniu
• Wewnątrz bloku PL / SQL

Na poziomie schematu podprogram to plik standalone subprogram. Jest tworzony za pomocą instrukcji CREATE PROCEDURE lub CREATE FUNCTION. Jest przechowywany w bazie danych i można go usunąć za pomocą instrukcji DROP PROCEDURE lub DROP FUNCTION.

Podprogram utworzony w pakiecie to plik packaged subprogram. Jest przechowywany w bazie danych i można go usunąć tylko wtedy, gdy pakiet zostanie usunięty za pomocą instrukcji DROP PACKAGE. Pakiety omówimy w rozdziale'PL/SQL - Packages'.

Podprogramy PL / SQL nazywane są blokami PL / SQL, które mogą być wywoływane z zestawem parametrów. PL / SQL udostępnia dwa rodzaje podprogramów -

• Functions- Te podprogramy zwracają pojedynczą wartość; używany głównie do obliczania i zwracania wartości.

• Procedures- Te podprogramy nie zwracają wartości bezpośrednio; używany głównie do wykonywania akcji.

W tym rozdziale zostaną omówione ważne aspekty PL/SQL procedure. OmówimyPL/SQL function w następnym rozdziale.

Części podprogramu PL / SQL

Każdy podprogram PL / SQL ma nazwę i może mieć również listę parametrów. Podobnie jak anonimowe bloki PL / SQL, nazwane bloki będą również miały następujące trzy części -

S.Nr	Części i opis
1	Declarative Part Jest to część opcjonalna. Jednak deklaratywna część podprogramu nie rozpoczyna się słowem kluczowym DECLARE. Zawiera deklaracje typów, kursorów, stałych, zmiennych, wyjątków i zagnieżdżonych podprogramów. Pozycje te są lokalne dla podprogramu i przestają istnieć, gdy podprogram kończy wykonywanie.
2	Executable Part Jest to część obowiązkowa i zawiera stwierdzenia, które wykonują określoną czynność.
3	Exception-handling To znowu część opcjonalna. Zawiera kod, który obsługuje błędy w czasie wykonywania.

Tworzenie procedury

Procedura jest tworzona z CREATE OR REPLACE PROCEDUREkomunikat. Uproszczona składnia instrukcji CREATE OR REPLACE PROCEDURE jest następująca:

CREATE [OR REPLACE] PROCEDURE procedure_name 
[(parameter_name [IN | OUT | IN OUT] type [, ...])] 
{IS | AS} 
BEGIN 
  < procedure_body > 
END procedure_name;

Gdzie,

• nazwa-procedury określa nazwę procedury.

• Opcja [OR REPLACE] umożliwia modyfikację istniejącej procedury.

• Opcjonalna lista parametrów zawiera nazwę, tryb i typy parametrów. IN reprezentuje wartość, która zostanie przekazana z zewnątrz, a OUT reprezentuje parametr, który zostanie użyty do zwrócenia wartości poza procedurą.

• treść procedury zawiera część wykonywalną.

• Słowo kluczowe AS jest używane zamiast słowa kluczowego IS do tworzenia samodzielnej procedury.

Przykład

Poniższy przykład tworzy prostą procedurę, która wyświetla ciąg „Hello World!” na ekranie po wykonaniu.

CREATE OR REPLACE PROCEDURE greetings 
AS 
BEGIN 
   dbms_output.put_line('Hello World!'); 
END; 
/

Gdy powyższy kod zostanie wykonany przy użyciu znaku zachęty SQL, wygeneruje następujący wynik -

Procedure created.

Wykonywanie samodzielnej procedury

Oddzielną procedurę można wywołać na dwa sposoby -

• Używając EXECUTE słowo kluczowe

• Wywołanie nazwy procedury z bloku PL / SQL

Powyższa procedura o nazwie 'greetings' można wywołać słowem kluczowym EXECUTE jako -

EXECUTE greetings;

Powyższe wywołanie wyświetli -

Hello World

PL/SQL procedure successfully completed.

Procedurę można również wywołać z innego bloku PL / SQL -

BEGIN 
   greetings; 
END; 
/

Powyższe wywołanie wyświetli -

Hello World  

PL/SQL procedure successfully completed.

Usuwanie samodzielnej procedury

Samodzielna procedura jest usuwana z DROP PROCEDUREkomunikat. Składnia usuwania procedury to -

DROP PROCEDURE procedure-name;

Możesz porzucić procedurę powitania, używając następującej instrukcji -

DROP PROCEDURE greetings;

Tryby parametrów w podprogramach PL / SQL

Poniższa tabela przedstawia tryby parametrów w podprogramach PL / SQL -

S.Nr	Tryb parametrów i opis
1	IN Parametr IN umożliwia przekazanie wartości do podprogramu. It is a read-only parameter. W podprogramie parametr IN działa jak stała. Nie można mu przypisać wartości. Jako parametr IN można przekazać stałą, literał, zainicjowaną zmienną lub wyrażenie. Możesz także zainicjować go do wartości domyślnej; jednakże w tym przypadku jest pomijany w wywołaniu podprogramu.It is the default mode of parameter passing. Parameters are passed by reference.
2	OUT Parametr OUT zwraca wartość do programu wywołującego. W podprogramie parametr OUT działa jak zmienna. Możesz zmienić jego wartość i odwołać się do wartości po jej przypisaniu.The actual parameter must be variable and it is passed by value.
3	IN OUT Na IN OUTparametr przekazuje wartość początkową do podprogramu i zwraca zaktualizowaną wartość do programu wywołującego. Można mu przypisać wartość, a wartość można odczytać. Rzeczywisty parametr odpowiadający parametrowi formalnemu IN OUT musi być zmienną, a nie stałą lub wyrażeniem. Parametrowi formalnemu należy przypisać wartość.Actual parameter is passed by value.

Tryb IN & OUT Przykład 1

Ten program znajduje minimum dwie wartości. Tutaj procedura przyjmuje dwie liczby przy użyciu trybu IN i zwraca ich minimum przy użyciu parametrów OUT.

DECLARE 
   a number; 
   b number; 
   c number;
PROCEDURE findMin(x IN number, y IN number, z OUT number) IS 
BEGIN 
   IF x < y THEN 
      z:= x; 
   ELSE 
      z:= y; 
   END IF; 
END;   
BEGIN 
   a:= 23; 
   b:= 45; 
   findMin(a, b, c); 
   dbms_output.put_line(' Minimum of (23, 45) : ' || c); 
END; 
/

Gdy powyższy kod jest wykonywany w wierszu polecenia SQL, daje następujący wynik -

Minimum of (23, 45) : 23  

PL/SQL procedure successfully completed.

Tryb IN & OUT Przykład 2

Ta procedura oblicza kwadrat wartości przekazanej wartości. Ten przykład pokazuje, jak możemy użyć tego samego parametru do zaakceptowania wartości, a następnie zwrócenia innego wyniku.

DECLARE 
   a number; 
PROCEDURE squareNum(x IN OUT number) IS 
BEGIN 
  x := x * x; 
END;  
BEGIN 
   a:= 23; 
   squareNum(a); 
   dbms_output.put_line(' Square of (23): ' || a); 
END; 
/

Gdy powyższy kod jest wykonywany w wierszu polecenia SQL, daje następujący wynik -

Square of (23): 529 

PL/SQL procedure successfully completed.

Metody przekazywania parametrów

Rzeczywiste parametry można przekazać na trzy sposoby -

• Notacja pozycyjna
• Nazwana notacja
• Notacja mieszana

Notacja pozycyjna

W notacji pozycyjnej procedurę można wywołać jako -

findMin(a, b, c, d);

W notacji pozycyjnej pierwszy rzeczywisty parametr jest podstawiany za pierwszy parametr formalny; drugi rzeczywisty parametr zastępuje drugi parametr formalny i tak dalej. Więc,a jest zastąpiony x, b jest zastąpiony y, c jest zastąpiony z i d jest zastąpiony m.

Nazwana notacja

W notacji nazwanej rzeczywisty parametr jest skojarzony z parametrem formalnym przy użyciu arrow symbol ( => ). Wywołanie procedury będzie wyglądać następująco -

findMin(x => a, y => b, z => c, m => d);

Notacja mieszana

W notacji mieszanej można mieszać obie notacje w wywołaniu procedury; jednak notacja pozycyjna powinna poprzedzać notację nazwaną.

Następujące połączenie jest legalne -

findMin(a, b, c, m => d);

Jednak jest to niezgodne z prawem:

findMin(x => a, b, c, d);

W tym rozdziale omówimy funkcje w PL / SQL. Funkcja jest taka sama jak procedura, z tą różnicą, że zwraca wartość. Dlatego wszystkie dyskusje z poprzedniego rozdziału odnoszą się również do funkcji.

Tworzenie funkcji

Niezależna funkcja jest tworzona przy użyciu CREATE FUNCTIONkomunikat. Uproszczona składnia dlaCREATE OR REPLACE PROCEDURE oświadczenie jest następujące -

CREATE [OR REPLACE] FUNCTION function_name 
[(parameter_name [IN | OUT | IN OUT] type [, ...])] 
RETURN return_datatype 
{IS | AS} 
BEGIN 
   < function_body > 
END [function_name];

Gdzie,

• nazwa-funkcji określa nazwę funkcji.

• Opcja [OR REPLACE] umożliwia modyfikację istniejącej funkcji.

• Opcjonalna lista parametrów zawiera nazwę, tryb i typy parametrów. IN reprezentuje wartość, która zostanie przekazana z zewnątrz, a OUT reprezentuje parametr, który zostanie użyty do zwrócenia wartości poza procedurą.

• Funkcja musi zawierać return komunikat.

• RETURN klauzula określa typ danych idziesz do powrotu z funkcji.

• Treść funkcji zawiera część wykonywalną.

• Słowo kluczowe AS jest używane zamiast słowa kluczowego IS do tworzenia samodzielnej funkcji.

Przykład

Poniższy przykład ilustruje sposób tworzenia i wywoływania funkcji autonomicznej. Ta funkcja zwraca całkowitą liczbę KLIENTÓW w tabeli klientów.

Skorzystamy z tabeli CUSTOMERS, którą stworzyliśmy w rozdziale Zmienne PL / SQL -

Select * from customers; 
 
+----+----------+-----+-----------+----------+ 
| ID | NAME     | AGE | ADDRESS   | SALARY   | 
+----+----------+-----+-----------+----------+ 
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 | 
|  2 | Khilan   |  25 | Delhi     |  1500.00 | 
|  3 | kaushik  |  23 | Kota      |  2000.00 | 
|  4 | Chaitali |  25 | Mumbai    |  6500.00 | 
|  5 | Hardik   |  27 | Bhopal    |  8500.00 | 
|  6 | Komal    |  22 | MP        |  4500.00 | 
+----+----------+-----+-----------+----------+

CREATE OR REPLACE FUNCTION totalCustomers 
RETURN number IS 
   total number(2) := 0; 
BEGIN 
   SELECT count(*) into total 
   FROM customers; 
    
   RETURN total; 
END; 
/

Gdy powyższy kod zostanie wykonany przy użyciu znaku zachęty SQL, wygeneruje następujący wynik -

Function created.

Wywołanie funkcji

Tworząc funkcję, podajesz definicję tego, co funkcja ma robić. Aby użyć funkcji, będziesz musiał wywołać tę funkcję, aby wykonać określone zadanie. Gdy program wywołuje funkcję, sterowanie programem jest przekazywane do wywoływanej funkcji.

Wywołana funkcja wykonuje zdefiniowane zadanie i kiedy wykonywana jest jej instrukcja return lub gdy last end statement zostanie osiągnięty, przywraca sterowanie programem z powrotem do programu głównego.

Aby wywołać funkcję, wystarczy przekazać wymagane parametry wraz z nazwą funkcji, a jeśli funkcja zwraca wartość, można ją zapisać. Poniższy program wywołuje funkcjętotalCustomers z anonimowego bloku -

DECLARE 
   c number(2); 
BEGIN 
   c := totalCustomers(); 
   dbms_output.put_line('Total no. of Customers: ' || c); 
END; 
/

Gdy powyższy kod jest wykonywany w wierszu polecenia SQL, daje następujący wynik -

Total no. of Customers: 6  

PL/SQL procedure successfully completed.

Przykład

Poniższy przykład ilustruje deklarowanie, definiowanie i wywoływanie prostej funkcji PL / SQL, która oblicza i zwraca maksymalnie dwie wartości.

DECLARE 
   a number; 
   b number; 
   c number; 
FUNCTION findMax(x IN number, y IN number)  
RETURN number 
IS 
    z number; 
BEGIN 
   IF x > y THEN 
      z:= x; 
   ELSE 
      Z:= y; 
   END IF;  
   RETURN z; 
END; 
BEGIN 
   a:= 23; 
   b:= 45;  
   c := findMax(a, b); 
   dbms_output.put_line(' Maximum of (23,45): ' || c); 
END; 
/

Gdy powyższy kod jest wykonywany w wierszu polecenia SQL, daje następujący wynik -

Maximum of (23,45): 45   

PL/SQL procedure successfully completed.

Funkcje rekurencyjne PL / SQL

Widzieliśmy, że program lub podprogram może wywołać inny podprogram. Gdy podprogram wywołuje sam siebie, nazywa się to wywołaniem rekurencyjnym, a proces jest znany jakorecursion.

Aby zilustrować to pojęcie, obliczmy silnię liczby. Silnia liczby n jest definiowana jako -

n! = n*(n-1)! 
   = n*(n-1)*(n-2)! 
      ... 
   = n*(n-1)*(n-2)*(n-3)... 1

Poniższy program oblicza silnię podanej liczby, wywołując siebie rekurencyjnie -

DECLARE 
   num number; 
   factorial number;  
   
FUNCTION fact(x number) 
RETURN number  
IS 
   f number; 
BEGIN 
   IF x=0 THEN 
      f := 1; 
   ELSE 
      f := x * fact(x-1); 
   END IF; 
RETURN f; 
END;  

BEGIN 
   num:= 6; 
   factorial := fact(num); 
   dbms_output.put_line(' Factorial '|| num || ' is ' || factorial); 
END; 
/

Gdy powyższy kod jest wykonywany w wierszu polecenia SQL, daje następujący wynik -

Factorial 6 is 720 
  
PL/SQL procedure successfully completed.

W tym rozdziale omówimy kursory w PL / SQL. Oracle tworzy obszar pamięci, zwany obszarem kontekstu, do przetwarzania instrukcji SQL, który zawiera wszystkie informacje potrzebne do przetwarzania instrukcji; na przykład liczba przetworzonych wierszy itp.

ZA cursorjest wskaźnikiem do tego obszaru kontekstu. PL / SQL kontroluje obszar kontekstu za pomocą kursora. Kursor przechowuje wiersze (jeden lub więcej) zwróconych przez instrukcję SQL. Zestaw wierszy, które utrzymuje kursor, jest nazywanyactive set.

Kursor można nazwać tak, aby mógł się do niego odwoływać w programie do pobierania i przetwarzania wierszy zwracanych przez instrukcję SQL, po jednym na raz. Istnieją dwa rodzaje kursorów -

• Ukryte kursory
• Jawne kursory

Niejawne kursory

Niejawne kursory są tworzone automatycznie przez Oracle za każdym razem, gdy wykonywana jest instrukcja SQL, gdy nie ma dla niej jawnego kursora. Programiści nie mogą kontrolować ukrytych kursorów i zawartych w nich informacji.

Za każdym razem, gdy jest wydawana instrukcja DML (INSERT, UPDATE i DELETE), z nią skojarzony jest niejawny kursor. W przypadku operacji INSERT kursor przechowuje dane, które należy wstawić. W przypadku operacji UPDATE i DELETE kursor identyfikuje wiersze, których to dotyczy.

W PL / SQL możesz odwołać się do najnowszego niejawnego kursora jako SQL cursor, który zawsze ma takie atrybuty jak %FOUND, %ISOPEN, %NOTFOUND, i %ROWCOUNT. Kursor SQL ma dodatkowe atrybuty,%BULK_ROWCOUNT i %BULK_EXCEPTIONS, przeznaczony do użytku z FORALLkomunikat. Poniższa tabela zawiera opis najczęściej używanych atrybutów -

S.Nr	Atrybut i opis
1	%FOUND Zwraca wartość TRUE, jeśli instrukcja INSERT, UPDATE lub DELETE wpłynęła na jeden lub więcej wierszy lub instrukcja SELECT INTO zwróciła jeden lub więcej wierszy. W przeciwnym razie zwraca FALSE.
2	%NOTFOUND Logiczne przeciwieństwo% FOUND. Zwraca wartość TRUE, jeśli instrukcja INSERT, UPDATE lub DELETE nie wpłynęła na żadne wiersze lub instrukcja SELECT INTO nie zwróciła żadnych wierszy. W przeciwnym razie zwraca FALSE.
3	%ISOPEN Zawsze zwraca FALSE dla niejawnych kursorów, ponieważ Oracle automatycznie zamyka kursor SQL po wykonaniu powiązanej z nim instrukcji SQL.
4	%ROWCOUNT Zwraca liczbę wierszy, na które ma wpływ instrukcja INSERT, UPDATE lub DELETE, lub zwracanych przez instrukcję SELECT INTO.

Każdy atrybut kursora SQL będzie dostępny jako sql%attribute_name jak pokazano poniżej w przykładzie.

Przykład

Będziemy korzystać z tabeli CUSTOMERS, którą stworzyliśmy i używaliśmy w poprzednich rozdziałach.

Select * from customers;  

+----+----------+-----+-----------+----------+ 
| ID | NAME     | AGE | ADDRESS   | SALARY   | 
+----+----------+-----+-----------+----------+ 
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 | 
|  2 | Khilan   |  25 | Delhi     |  1500.00 | 
|  3 | kaushik  |  23 | Kota      |  2000.00 | 
|  4 | Chaitali |  25 | Mumbai    |  6500.00 | 
|  5 | Hardik   |  27 | Bhopal    |  8500.00 | 
|  6 | Komal    |  22 | MP        |  4500.00 | 
+----+----------+-----+-----------+----------+

Poniższy program zaktualizuje tabelę i zwiększy pensję każdego klienta o 500 i użyje SQL%ROWCOUNT atrybut określający liczbę wierszy, których to dotyczy -

DECLARE  
   total_rows number(2); 
BEGIN 
   UPDATE customers 
   SET salary = salary + 500; 
   IF sql%notfound THEN 
      dbms_output.put_line('no customers selected'); 
   ELSIF sql%found THEN 
      total_rows := sql%rowcount;
      dbms_output.put_line( total_rows || ' customers selected '); 
   END IF;  
END; 
/

Gdy powyższy kod jest wykonywany w wierszu polecenia SQL, daje następujący wynik -

6 customers selected  

PL/SQL procedure successfully completed.

Jeśli sprawdzisz rekordy w tabeli klientów, zobaczysz, że wiersze zostały zaktualizowane -

Select * from customers;  

+----+----------+-----+-----------+----------+ 
| ID | NAME     | AGE | ADDRESS   | SALARY   | 
+----+----------+-----+-----------+----------+ 
|  1 | Ramesh   |  32 | Ahmedabad |  2500.00 | 
|  2 | Khilan   |  25 | Delhi     |  2000.00 | 
|  3 | kaushik  |  23 | Kota      |  2500.00 | 
|  4 | Chaitali |  25 | Mumbai    |  7000.00 | 
|  5 | Hardik   |  27 | Bhopal    |  9000.00 | 
|  6 | Komal    |  22 | MP        |  5000.00 | 
+----+----------+-----+-----------+----------+

Jawne kursory

Jawne kursory to kursory zdefiniowane przez programistę w celu uzyskania większej kontroli nad context area. W sekcji deklaracji bloku PL / SQL należy zdefiniować wyraźny kursor. Jest tworzony w instrukcji SELECT, która zwraca więcej niż jeden wiersz.

Składnia tworzenia jawnego kursora to -

CURSOR cursor_name IS select_statement;

Praca z jawnym kursorem obejmuje następujące kroki -

• Deklaracja kursora do inicjalizacji pamięci
• Otwarcie kursora w celu przydzielenia pamięci
• Pobieranie kursora do pobierania danych
• Zamknięcie kursora w celu zwolnienia przydzielonej pamięci

Deklarowanie kursora

Zadeklarowanie kursora definiuje kursor z nazwą i powiązaną instrukcją SELECT. Na przykład -

CURSOR c_customers IS 
   SELECT id, name, address FROM customers;

Otwieranie kursora

Otwarcie kursora przydziela pamięć dla kursora i przygotowuje go do pobrania do niego wierszy zwróconych przez instrukcję SQL. Na przykład otworzymy wyżej zdefiniowany kursor w następujący sposób -

OPEN c_customers;

Pobieranie kursora

Pobieranie kursora obejmuje dostęp do jednego wiersza na raz. Na przykład pobierzemy wiersze z otwartego powyżej kursora w następujący sposób -

FETCH c_customers INTO c_id, c_name, c_addr;

Zamykanie kursora

Zamknięcie kursora oznacza zwolnienie przydzielonej pamięci. Na przykład zamkniemy powyższy kursor w następujący sposób -

CLOSE c_customers;

Przykład

Poniżej znajduje się kompletny przykład ilustrujący koncepcje kursorów jawnych & minua;

DECLARE 
   c_id customers.id%type; 
   c_name customer.name%type; 
   c_addr customers.address%type; 
   CURSOR c_customers is 
      SELECT id, name, address FROM customers; 
BEGIN 
   OPEN c_customers; 
   LOOP 
   FETCH c_customers into c_id, c_name, c_addr; 
      EXIT WHEN c_customers%notfound; 
      dbms_output.put_line(c_id || ' ' || c_name || ' ' || c_addr); 
   END LOOP; 
   CLOSE c_customers; 
END; 
/

Gdy powyższy kod jest wykonywany w wierszu polecenia SQL, daje następujący wynik -

1 Ramesh Ahmedabad  
2 Khilan Delhi  
3 kaushik Kota     
4 Chaitali Mumbai  
5 Hardik Bhopal   
6 Komal MP  
  
PL/SQL procedure successfully completed.

W tym rozdziale omówimy Rekordy w PL / SQL. ZArecordto struktura danych, która może zawierać elementy danych różnego rodzaju. Rekordy składają się z różnych pól, podobnie jak wiersz tabeli bazy danych.

Na przykład chcesz śledzić swoje książki w bibliotece. Możesz chcieć śledzić następujące atrybuty dotyczące każdej książki, takie jak tytuł, autor, temat, identyfikator książki. Rekord zawierający pole dla każdego z tych elementów umożliwia traktowanie KSIĄŻKI jako jednostki logicznej i pozwala lepiej organizować i przedstawiać zawarte w niej informacje.

PL / SQL może obsługiwać następujące typy rekordów -

• Table-based
• Rekordy oparte na kursorach
• Rekordy zdefiniowane przez użytkownika

Rekordy oparte na tabelach

Atrybut% ROWTYPE umożliwia programiście tworzenie table-based i cursorbased dokumentacja.

Poniższy przykład ilustruje koncepcję table-baseddokumentacja. Będziemy korzystać z tabeli CUSTOMERS, którą stworzyliśmy i używaliśmy w poprzednich rozdziałach -

DECLARE 
   customer_rec customers%rowtype; 
BEGIN 
   SELECT * into customer_rec 
   FROM customers 
   WHERE id = 5;  
   dbms_output.put_line('Customer ID: ' || customer_rec.id); 
   dbms_output.put_line('Customer Name: ' || customer_rec.name); 
   dbms_output.put_line('Customer Address: ' || customer_rec.address); 
   dbms_output.put_line('Customer Salary: ' || customer_rec.salary); 
END; 
/

Gdy powyższy kod jest wykonywany w wierszu polecenia SQL, daje następujący wynik -

Customer ID: 5 
Customer Name: Hardik 
Customer Address: Bhopal 
Customer Salary: 9000 
 
PL/SQL procedure successfully completed.

Rekordy oparte na kursorach

Poniższy przykład ilustruje koncepcję cursor-baseddokumentacja. Będziemy korzystać z tabeli CUSTOMERS, którą stworzyliśmy i używaliśmy w poprzednich rozdziałach -

DECLARE 
   CURSOR customer_cur is 
      SELECT id, name, address  
      FROM customers; 
   customer_rec customer_cur%rowtype; 
BEGIN 
   OPEN customer_cur; 
   LOOP 
      FETCH customer_cur into customer_rec; 
      EXIT WHEN customer_cur%notfound; 
      DBMS_OUTPUT.put_line(customer_rec.id || ' ' || customer_rec.name); 
   END LOOP; 
END; 
/

Gdy powyższy kod jest wykonywany w wierszu polecenia SQL, daje następujący wynik -

1 Ramesh 
2 Khilan 
3 kaushik 
4 Chaitali 
5 Hardik 
6 Komal  

PL/SQL procedure successfully completed.

Rekordy zdefiniowane przez użytkownika

PL / SQL udostępnia typ rekordu zdefiniowany przez użytkownika, który umożliwia definiowanie różnych struktur rekordów. Te rekordy składają się z różnych pól. Załóżmy, że chcesz śledzić swoje książki w bibliotece. Możesz chcieć śledzić następujące atrybuty dotyczące każdej książki -

• Title
• Author
• Subject
• Identyfikator książki

Definiowanie rekordu

Typ rekordu jest zdefiniowany jako -

TYPE 
type_name IS RECORD 
  ( field_name1  datatype1  [NOT NULL]  [:= DEFAULT EXPRESSION], 
   field_name2   datatype2   [NOT NULL]  [:= DEFAULT EXPRESSION], 
   ... 
   field_nameN  datatypeN  [NOT NULL]  [:= DEFAULT EXPRESSION); 
record-name  type_name;

Rekord Księgi jest zadeklarowany w następujący sposób -

DECLARE 
TYPE books IS RECORD 
(title  varchar(50), 
   author  varchar(50), 
   subject varchar(100), 
   book_id   number); 
book1 books; 
book2 books;

Dostęp do pól

Aby uzyskać dostęp do dowolnego pola rekordu, używamy kropki (.)operator. Operator dostępu do członka jest zakodowany jako kropka między nazwą zmiennej rekordu a polem, do którego chcemy uzyskać dostęp. Poniżej znajduje się przykład wyjaśniający użycie rekordu -

DECLARE 
   type books is record 
      (title varchar(50), 
      author varchar(50), 
      subject varchar(100), 
      book_id number); 
   book1 books; 
   book2 books; 
BEGIN 
   -- Book 1 specification 
   book1.title  := 'C Programming'; 
   book1.author := 'Nuha Ali ';  
   book1.subject := 'C Programming Tutorial'; 
   book1.book_id := 6495407;  
   -- Book 2 specification 
   book2.title := 'Telecom Billing'; 
   book2.author := 'Zara Ali'; 
   book2.subject := 'Telecom Billing Tutorial'; 
   book2.book_id := 6495700;  
  
  -- Print book 1 record 
   dbms_output.put_line('Book 1 title : '|| book1.title); 
   dbms_output.put_line('Book 1 author : '|| book1.author); 
   dbms_output.put_line('Book 1 subject : '|| book1.subject); 
   dbms_output.put_line('Book 1 book_id : ' || book1.book_id); 
   
   -- Print book 2 record 
   dbms_output.put_line('Book 2 title : '|| book2.title); 
   dbms_output.put_line('Book 2 author : '|| book2.author); 
   dbms_output.put_line('Book 2 subject : '|| book2.subject); 
   dbms_output.put_line('Book 2 book_id : '|| book2.book_id); 
END; 
/

Gdy powyższy kod jest wykonywany w wierszu polecenia SQL, daje następujący wynik -

Book 1 title : C Programming 
Book 1 author : Nuha Ali 
Book 1 subject : C Programming Tutorial 
Book 1 book_id : 6495407 
Book 2 title : Telecom Billing 
Book 2 author : Zara Ali 
Book 2 subject : Telecom Billing Tutorial 
Book 2 book_id : 6495700  

PL/SQL procedure successfully completed.

Rekordy jako parametry podprogramu

Rekord można przekazać jako parametr podprogramu, tak samo jak każdą inną zmienną. Możesz również uzyskać dostęp do pól rekordów w taki sam sposób, jak w powyższym przykładzie -

DECLARE 
   type books is record 
      (title  varchar(50), 
      author  varchar(50), 
      subject varchar(100), 
      book_id   number); 
   book1 books; 
   book2 books;  
PROCEDURE printbook (book books) IS 
BEGIN 
   dbms_output.put_line ('Book  title :  ' || book.title); 
   dbms_output.put_line('Book  author : ' || book.author); 
   dbms_output.put_line( 'Book  subject : ' || book.subject); 
   dbms_output.put_line( 'Book book_id : ' || book.book_id); 
END; 
   
BEGIN 
   -- Book 1 specification 
   book1.title  := 'C Programming'; 
   book1.author := 'Nuha Ali ';  
   book1.subject := 'C Programming Tutorial'; 
   book1.book_id := 6495407;
   
   -- Book 2 specification 
   book2.title := 'Telecom Billing'; 
   book2.author := 'Zara Ali'; 
   book2.subject := 'Telecom Billing Tutorial'; 
   book2.book_id := 6495700;  
   
   -- Use procedure to print book info 
   printbook(book1); 
   printbook(book2); 
END; 
/

Gdy powyższy kod jest wykonywany w wierszu polecenia SQL, daje następujący wynik -

Book  title : C Programming 
Book  author : Nuha Ali 
Book subject : C Programming Tutorial 
Book  book_id : 6495407 
Book title : Telecom Billing 
Book author : Zara Ali 
Book subject : Telecom Billing Tutorial 
Book book_id : 6495700  

PL/SQL procedure successfully completed.

W tym rozdziale omówimy Wyjątki w PL / SQL. Wyjątkiem jest stan błędu podczas wykonywania programu. PL / SQL wspiera programistów w wyłapywaniu takich warunków przy użyciuEXCEPTIONbloku w programie i podejmowana jest odpowiednia akcja przeciwko warunkowi błędu. Istnieją dwa rodzaje wyjątków -

• Wyjątki zdefiniowane przez system
• Wyjątki zdefiniowane przez użytkownika

Składnia obsługi wyjątków

Ogólna składnia obsługi wyjątków jest następująca. Tutaj możesz wymienić tyle wyjątków, ile możesz obsłużyć. Domyślny wyjątek będzie obsługiwany za pomocąWHEN others THEN -

DECLARE 
   <declarations section> 
BEGIN 
   <executable command(s)> 
EXCEPTION 
   <exception handling goes here > 
   WHEN exception1 THEN  
      exception1-handling-statements  
   WHEN exception2  THEN  
      exception2-handling-statements  
   WHEN exception3 THEN  
      exception3-handling-statements 
   ........ 
   WHEN others THEN 
      exception3-handling-statements 
END;

Przykład

Napiszmy kod ilustrujący koncepcję. Będziemy korzystać z tabeli CUSTOMERS, którą stworzyliśmy i używaliśmy w poprzednich rozdziałach -

DECLARE 
   c_id customers.id%type := 8; 
   c_name customerS.Name%type; 
   c_addr customers.address%type; 
BEGIN 
   SELECT  name, address INTO  c_name, c_addr 
   FROM customers 
   WHERE id = c_id;  
   DBMS_OUTPUT.PUT_LINE ('Name: '||  c_name); 
   DBMS_OUTPUT.PUT_LINE ('Address: ' || c_addr); 

EXCEPTION 
   WHEN no_data_found THEN 
      dbms_output.put_line('No such customer!'); 
   WHEN others THEN 
      dbms_output.put_line('Error!'); 
END; 
/

Gdy powyższy kod jest wykonywany w wierszu polecenia SQL, daje następujący wynik -

No such customer!  

PL/SQL procedure successfully completed.

Powyższy program wyświetla nazwę i adres klienta, któremu nadano identyfikator. Ponieważ w naszej bazie danych nie ma klienta o identyfikatorze 8, program zgłasza wyjątek czasu wykonywaniaNO_DATA_FOUND, który jest przechwytywany w EXCEPTION block.

Podnoszenie wyjątków

Wyjątki są zgłaszane przez serwer bazy danych automatycznie za każdym razem, gdy wystąpi jakikolwiek wewnętrzny błąd bazy danych, ale wyjątki mogą być jawnie zgłaszane przez programistę za pomocą polecenia RAISE. Poniżej znajduje się prosta składnia zgłaszania wyjątku -

DECLARE 
   exception_name EXCEPTION; 
BEGIN 
   IF condition THEN 
      RAISE exception_name; 
   END IF; 
EXCEPTION 
   WHEN exception_name THEN 
   statement; 
END;

Możesz użyć powyższej składni do podniesienia standardowego wyjątku Oracle lub dowolnego wyjątku zdefiniowanego przez użytkownika. W następnej sekcji podamy przykład zgłaszania wyjątku zdefiniowanego przez użytkownika. W podobny sposób można podnieść standardowe wyjątki Oracle.

Wyjątki zdefiniowane przez użytkownika

PL / SQL pozwala zdefiniować własne wyjątki zgodnie z potrzebami Twojego programu. Wyjątek zdefiniowany przez użytkownika musi zostać zadeklarowany, a następnie zgłoszony jawnie za pomocą instrukcji RAISE lub proceduryDBMS_STANDARD.RAISE_APPLICATION_ERROR.

Składnia deklarowania wyjątku to -

DECLARE 
   my-exception EXCEPTION;

Przykład

Poniższy przykład ilustruje tę koncepcję. Ten program pyta o identyfikator klienta, gdy użytkownik wprowadza nieprawidłowy identyfikator, wyjątekinvalid_id jest podniesiony.

DECLARE 
   c_id customers.id%type := &cc_id; 
   c_name customerS.Name%type; 
   c_addr customers.address%type;  
   -- user defined exception 
   ex_invalid_id  EXCEPTION; 
BEGIN 
   IF c_id <= 0 THEN 
      RAISE ex_invalid_id; 
   ELSE 
      SELECT  name, address INTO  c_name, c_addr 
      FROM customers 
      WHERE id = c_id;
      DBMS_OUTPUT.PUT_LINE ('Name: '||  c_name);  
      DBMS_OUTPUT.PUT_LINE ('Address: ' || c_addr); 
   END IF; 

EXCEPTION 
   WHEN ex_invalid_id THEN 
      dbms_output.put_line('ID must be greater than zero!'); 
   WHEN no_data_found THEN 
      dbms_output.put_line('No such customer!'); 
   WHEN others THEN 
      dbms_output.put_line('Error!');  
END; 
/

Gdy powyższy kod jest wykonywany w wierszu polecenia SQL, daje następujący wynik -

Enter value for cc_id: -6 (let's enter a value -6) 
old  2: c_id customers.id%type := &cc_id; 
new  2: c_id customers.id%type := -6; 
ID must be greater than zero! 
 
PL/SQL procedure successfully completed.

Wstępnie zdefiniowane wyjątki

PL / SQL zapewnia wiele predefiniowanych wyjątków, które są wykonywane, gdy program narusza dowolną regułę bazy danych. Na przykład predefiniowany wyjątek NO_DATA_FOUND jest zgłaszany, gdy instrukcja SELECT INTO nie zwraca żadnych wierszy. W poniższej tabeli wymieniono kilka ważnych, wstępnie zdefiniowanych wyjątków -

Wyjątek	Błąd Oracle	SQLCODE	Opis
ACCESS_INTO_NULL	06530	-6530	Jest podnoszony, gdy obiekt zerowy ma automatycznie przypisaną wartość.
CASE_NOT_FOUND	06592	-6592	Jest on zgłaszany, gdy nie wybrano żadnego z wyborów w klauzuli WHEN instrukcji CASE i nie ma klauzuli ELSE.
COLLECTION_IS_NULL	06531	-6531	Jest wywoływany, gdy program próbuje zastosować metody kolekcji inne niż EXISTS do niezainicjowanej tabeli zagnieżdżonej lub zmiennej lub program próbuje przypisać wartości do elementów niezainicjowanej tabeli zagnieżdżonej lub zmiennej.
DUP_VAL_ON_INDEX	00001	-1	Jest podnoszony, gdy zduplikowane wartości mają być przechowywane w kolumnie z unikalnym indeksem.
INVALID_CURSOR	01001	-1001	Jest podnoszony, gdy podejmowane są próby wykonania operacji kursora, która jest niedozwolona, ​​na przykład zamknięcie nieotwartego kursora.
NIEPRAWIDŁOWY NUMER	01722	-1722	Jest zgłaszany, gdy konwersja ciągu znaków na liczbę nie powiedzie się, ponieważ ciąg nie reprezentuje prawidłowej liczby.
LOGIN_DENIED	01017	-1017	Jest wywoływany, gdy program próbuje zalogować się do bazy danych przy użyciu nieprawidłowej nazwy użytkownika lub hasła.
NIE ZNALEZIONO DANYCH	01403	+100	Jest podnoszony, gdy instrukcja SELECT INTO nie zwraca żadnych wierszy.
NOT_LOGGED_ON	01012	-1012	Jest wywoływany, gdy wywołanie bazy danych jest wykonywane bez połączenia z bazą danych.
PROGRAM_ERROR	06501	-6501	Jest podnoszony, gdy PL / SQL ma wewnętrzny problem.
ROWTYPE_MISMATCH	06504	-6504	Jest podnoszony, gdy kursor pobiera wartość w zmiennej o niezgodnym typie danych.
SELF_IS_NULL	30625	-30625	Jest wywoływana, gdy wywoływana jest metoda składowa, ale wystąpienie typu obiektu nie zostało zainicjowane.
STORAGE_ERROR	06500	-6500	Jest wywoływany, gdy PL / SQL zabrakło pamięci lub pamięć została uszkodzona.
TOO_MANY_ROWS	01422	-1422	Jest zgłaszany, gdy instrukcja SELECT INTO zwraca więcej niż jeden wiersz.
VALUE_ERROR	06502	-6502	Jest zgłaszany, gdy wystąpi błąd arytmetyczny, konwersji, obcinania lub ograniczenia rozmiaru.
ZERO_DIVIDE	01476	1476	Jest podnoszony, gdy podejmowana jest próba podzielenia liczby przez zero.

W tym rozdziale omówimy wyzwalacze w PL / SQL. Wyzwalacze to zapisane programy, które są automatycznie wykonywane lub uruchamiane, gdy wystąpią jakieś zdarzenia. Wyzwalacze są w rzeczywistości napisane do wykonania w odpowiedzi na którekolwiek z następujących zdarzeń -

• ZA database manipulation (DML) instrukcja (DELETE, INSERT lub UPDATE)

• ZA database definition (DDL) instrukcja (CREATE, ALTER lub DROP).

• ZA database operation (SERVERERROR, LOGON, LOGOFF, STARTUP lub SHUTDOWN).

Wyzwalacze można zdefiniować w tabeli, widoku, schemacie lub bazie danych, z którą skojarzone jest zdarzenie.

Korzyści z wyzwalaczy

Wyzwalacze można napisać w następujących celach -

• Automatyczne generowanie niektórych wartości kolumn pochodnych
• Wymuszanie więzów integralności
• Rejestrowanie zdarzeń i przechowywanie informacji o dostępie do tabeli
• Auditing
• Synchroniczna replikacja tabel
• Nakładanie uprawnień bezpieczeństwa
• Zapobieganie nieprawidłowym transakcjom

Tworzenie wyzwalaczy

Składnia tworzenia wyzwalacza to -

CREATE [OR REPLACE ] TRIGGER trigger_name  
{BEFORE | AFTER | INSTEAD OF }  
{INSERT [OR] | UPDATE [OR] | DELETE}  
[OF col_name]  
ON table_name  
[REFERENCING OLD AS o NEW AS n]  
[FOR EACH ROW]  
WHEN (condition)   
DECLARE 
   Declaration-statements 
BEGIN  
   Executable-statements 
EXCEPTION 
   Exception-handling-statements 
END;

Gdzie,

• CREATE [OR REPLACE] TRIGGER nazwa_trigera - tworzy lub zastępuje istniejący wyzwalacz nazwą wyzwalacza .

• {PRZED | PO | INSTEAD OF} - określa, kiedy wyzwalacz zostanie wykonany. Klauzula INSTEAD OF służy do tworzenia wyzwalacza w widoku.

• {WSTAWIĆ [LUB] | UPDATE [LUB] | DELETE} - określa operację DML.

• [OF nazwa_kolumny] - określa nazwę kolumny, która będzie aktualizowana.

• [ON nazwa_tabeli] - określa nazwę tabeli powiązanej z wyzwalaczem.

• [REFERENCING STARE AS lub NEW AS n] - Umożliwia to odwoływanie się do nowych i starych wartości dla różnych instrukcji DML, takich jak INSERT, UPDATE i DELETE.

• [FOR EACH ROW] - określa wyzwalacz na poziomie wiersza, tj. Wyzwalacz zostanie wykonany dla każdego wiersza, którego dotyczy. W przeciwnym razie wyzwalacz zostanie wykonany tylko raz, gdy zostanie wykonana instrukcja SQL, co jest nazywane wyzwalaczem na poziomie tabeli.

• KIEDY (warunek) - zapewnia warunek dla wierszy, dla których zostanie uruchomiony wyzwalacz. Ta klauzula jest ważna tylko dla wyzwalaczy na poziomie wiersza.

Przykład

Na początek będziemy korzystać z tabeli CUSTOMERS, którą stworzyliśmy i używaliśmy w poprzednich rozdziałach -

Select * from customers;  

+----+----------+-----+-----------+----------+ 
| ID | NAME     | AGE | ADDRESS   | SALARY   | 
+----+----------+-----+-----------+----------+ 
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 | 
|  2 | Khilan   |  25 | Delhi     |  1500.00 | 
|  3 | kaushik  |  23 | Kota      |  2000.00 | 
|  4 | Chaitali |  25 | Mumbai    |  6500.00 | 
|  5 | Hardik   |  27 | Bhopal    |  8500.00 | 
|  6 | Komal    |  22 | MP        |  4500.00 | 
+----+----------+-----+-----------+----------+

Poniższy program tworzy plik row-levelwyzwalacz dla tabeli klientów, który byłby uruchamiany w przypadku operacji INSERT, UPDATE lub DELETE wykonywanych na tabeli CUSTOMERS. Ten wyzwalacz wyświetli różnicę wynagrodzeń między starymi a nowymi wartościami -

CREATE OR REPLACE TRIGGER display_salary_changes 
BEFORE DELETE OR INSERT OR UPDATE ON customers 
FOR EACH ROW 
WHEN (NEW.ID > 0) 
DECLARE 
   sal_diff number; 
BEGIN 
   sal_diff := :NEW.salary  - :OLD.salary; 
   dbms_output.put_line('Old salary: ' || :OLD.salary); 
   dbms_output.put_line('New salary: ' || :NEW.salary); 
   dbms_output.put_line('Salary difference: ' || sal_diff); 
END; 
/

Gdy powyższy kod jest wykonywany w wierszu polecenia SQL, daje następujący wynik -

Trigger created.

Należy wziąć pod uwagę następujące kwestie:

• Odwołania STARE i NOWE nie są dostępne dla wyzwalaczy na poziomie tabeli, ale można ich użyć do wyzwalaczy na poziomie rekordu.

• Jeśli chcesz odpytać tabelę w tym samym wyzwalaczu, powinieneś użyć słowa kluczowego AFTER, ponieważ wyzwalacze mogą wysyłać zapytania do tabeli lub zmieniać ją ponownie dopiero po zastosowaniu początkowych zmian i przywróceniu spójnego stanu tabeli.

• Powyższy wyzwalacz został napisany w taki sposób, że będzie uruchamiany przed jakąkolwiek operacją DELETE lub INSERT lub UPDATE na tabeli, ale możesz zapisać swój wyzwalacz na jednej lub wielu operacjach, na przykład BEFORE DELETE, która zostanie uruchomiona za każdym razem, gdy rekord zostaną usunięte za pomocą operacji DELETE w tabeli.

Wyzwalanie wyzwalacza

Wykonajmy kilka operacji DML na tabeli CUSTOMERS. Oto jedna instrukcja INSERT, która utworzy nowy rekord w tabeli -

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) 
VALUES (7, 'Kriti', 22, 'HP', 7500.00 );

Kiedy rekord jest tworzony w tabeli CUSTOMERS, powyższe tworzy wyzwalacz, display_salary_changes zostanie odpalony i wyświetli następujący wynik -

Old salary: 
New salary: 7500 
Salary difference:

Ponieważ jest to nowy rekord, stare wynagrodzenie nie jest dostępne, a powyższy wynik jest zerowy. Wykonajmy teraz jeszcze jedną operację DML na tabeli CUSTOMERS. Instrukcja UPDATE zaktualizuje istniejący rekord w tabeli -

UPDATE customers 
SET salary = salary + 500 
WHERE id = 2;

Kiedy rekord jest aktualizowany w tabeli CUSTOMERS, powyższe tworzy wyzwalacz, display_salary_changes zostanie odpalony i wyświetli następujący wynik -

Old salary: 1500 
New salary: 2000 
Salary difference: 500

W tym rozdziale omówimy pakiety w PL / SQL. Pakiety to obiekty schematów, które grupują logicznie powiązane typy PL / SQL, zmienne i podprogramy.

Pakiet będzie miał dwie obowiązkowe części -

• Specyfikacja pakietu
• Treść lub definicja pakietu

Specyfikacja pakietu

Specyfikacja to interfejs do pakietu. To tylkoDECLAREStypy, zmienne, stałe, wyjątki, kursory i podprogramy, do których można się odwoływać spoza pakietu. Innymi słowy, zawiera wszystkie informacje o zawartości pakietu, ale wyklucza kod dla podprogramów.

Wszystkie obiekty umieszczone w specyfikacji są wywoływane publicobiekty. Każdy podprogram, którego nie ma w specyfikacji pakietu, ale jest zakodowany w treści pakietu, nazywany jest plikiemprivate obiekt.

Poniższy fragment kodu przedstawia specyfikację pakietu mającą jedną procedurę. W pakiecie można zdefiniować wiele zmiennych globalnych i wiele procedur lub funkcji.

CREATE PACKAGE cust_sal AS 
   PROCEDURE find_sal(c_id customers.id%type); 
END cust_sal; 
/

Gdy powyższy kod jest wykonywany w wierszu polecenia SQL, daje następujący wynik -

Package created.

Treść pakietu

W treści pakietu znajdują się kody różnych metod zadeklarowanych w specyfikacji pakietu oraz inne deklaracje prywatne, które są ukryte przed kodem na zewnątrz pakietu.

Plik CREATE PACKAGE BODYInstrukcja służy do tworzenia treści pakietu. Poniższy fragment kodu przedstawia deklarację treści pakietu dlacust_salpakiet utworzony powyżej. Założyłem, że mamy już utworzoną tabelę CUSTOMERS w naszej bazie danych, jak wspomniano w rozdziale PL / SQL - Zmienne .

CREATE OR REPLACE PACKAGE BODY cust_sal AS  
   
   PROCEDURE find_sal(c_id customers.id%TYPE) IS 
   c_sal customers.salary%TYPE; 
   BEGIN 
      SELECT salary INTO c_sal 
      FROM customers 
      WHERE id = c_id; 
      dbms_output.put_line('Salary: '|| c_sal); 
   END find_sal; 
END cust_sal; 
/

Gdy powyższy kod jest wykonywany w wierszu polecenia SQL, daje następujący wynik -

Package body created.

Korzystanie z elementów pakietu

Dostęp do elementów pakietu (zmiennych, procedur lub funkcji) uzyskuje się za pomocą następującej składni -

package_name.element_name;

Weź pod uwagę, że utworzyliśmy już powyższy pakiet w naszym schemacie bazy danych, następujący program używa find_sal metoda cust_sal pakiet -

DECLARE 
   code customers.id%type := &cc_id; 
BEGIN 
   cust_sal.find_sal(code); 
END; 
/

Gdy powyższy kod jest wykonywany po znaku zachęty SQL, prosi o wprowadzenie identyfikatora klienta, a po wprowadzeniu identyfikatora wyświetla odpowiednie wynagrodzenie w następujący sposób -

Enter value for cc_id: 1 
Salary: 3000 

PL/SQL procedure successfully completed.

Przykład

Poniższy program zawiera bardziej kompletny pakiet. Będziemy korzystać z tabeli KLIENCI przechowywanej w naszej bazie danych z następującymi rekordami -

Select * from customers;  

+----+----------+-----+-----------+----------+ 
| ID | NAME     | AGE | ADDRESS   | SALARY   | 
+----+----------+-----+-----------+----------+ 
|  1 | Ramesh   |  32 | Ahmedabad |  3000.00 | 
|  2 | Khilan   |  25 | Delhi     |  3000.00 | 
|  3 | kaushik  |  23 | Kota      |  3000.00 | 
|  4 | Chaitali |  25 | Mumbai    |  7500.00 | 
|  5 | Hardik   |  27 | Bhopal    |  9500.00 | 
|  6 | Komal    |  22 | MP        |  5500.00 | 
+----+----------+-----+-----------+----------+

Specyfikacja pakietu

CREATE OR REPLACE PACKAGE c_package AS 
   -- Adds a customer 
   PROCEDURE addCustomer(c_id   customers.id%type, 
   c_name  customerS.No.ame%type, 
   c_age  customers.age%type, 
   c_addr customers.address%type,  
   c_sal  customers.salary%type); 
   
   -- Removes a customer 
   PROCEDURE delCustomer(c_id  customers.id%TYPE); 
   --Lists all customers 
   PROCEDURE listCustomer; 
  
END c_package; 
/

Gdy powyższy kod jest wykonywany po znaku zachęty SQL, tworzy powyższy pakiet i wyświetla następujący wynik -

Package created.

Tworzenie treści pakietu

CREATE OR REPLACE PACKAGE BODY c_package AS 
   PROCEDURE addCustomer(c_id  customers.id%type, 
      c_name customerS.No.ame%type, 
      c_age  customers.age%type, 
      c_addr  customers.address%type,  
      c_sal   customers.salary%type) 
   IS 
   BEGIN 
      INSERT INTO customers (id,name,age,address,salary) 
         VALUES(c_id, c_name, c_age, c_addr, c_sal); 
   END addCustomer; 
   
   PROCEDURE delCustomer(c_id   customers.id%type) IS 
   BEGIN 
      DELETE FROM customers 
      WHERE id = c_id; 
   END delCustomer;  
   
   PROCEDURE listCustomer IS 
   CURSOR c_customers is 
      SELECT  name FROM customers; 
   TYPE c_list is TABLE OF customers.Name%type;  
   name_list c_list := c_list(); 
   counter integer :=0; 
   BEGIN 
      FOR n IN c_customers LOOP 
      counter := counter +1; 
      name_list.extend; 
      name_list(counter) := n.name; 
      dbms_output.put_line('Customer(' ||counter|| ')'||name_list(counter)); 
      END LOOP; 
   END listCustomer;
   
END c_package; 
/

Powyższy przykład wykorzystuje rozszerzenie nested table. W następnym rozdziale omówimy koncepcję tabeli zagnieżdżonej.

Gdy powyższy kod jest wykonywany w wierszu polecenia SQL, daje następujący wynik -

Package body created.

Korzystanie z pakietu

Poniższy program używa metod zadeklarowanych i zdefiniowanych w pakiecie c_package .

DECLARE 
   code customers.id%type:= 8; 
BEGIN 
   c_package.addcustomer(7, 'Rajnish', 25, 'Chennai', 3500); 
   c_package.addcustomer(8, 'Subham', 32, 'Delhi', 7500); 
   c_package.listcustomer; 
   c_package.delcustomer(code); 
   c_package.listcustomer; 
END; 
/

Gdy powyższy kod jest wykonywany w wierszu polecenia SQL, daje następujący wynik -

Customer(1): Ramesh  
Customer(2): Khilan  
Customer(3): kaushik     
Customer(4): Chaitali  
Customer(5): Hardik  
Customer(6): Komal 
Customer(7): Rajnish 
Customer(8): Subham 
Customer(1): Ramesh  
Customer(2): Khilan  
Customer(3): kaushik     
Customer(4): Chaitali  
Customer(5): Hardik  
Customer(6): Komal
Customer(7): Rajnish 

PL/SQL procedure successfully completed

W tym rozdziale omówimy kolekcje w PL / SQL. Kolekcja to uporządkowana grupa elementów o tym samym typie danych. Każdy element jest identyfikowany przez unikalny indeks dolny, który reprezentuje jego pozycję w kolekcji.

PL / SQL udostępnia trzy typy kolekcji -

• Tabele indeksowane lub tablice asocjacyjne
• Tabela zagnieżdżona
• Tablica o zmiennej wielkości lub Varray

Dokumentacja Oracle zawiera następujące cechy dla każdego typu kolekcji -

Typ kolekcji	Liczba elementów	Typ indeksu	Gęsty lub Rzadki	Gdzie utworzono	Może być atrybutem typu obiektu
Tablica asocjacyjna (lub tablica indeksu)	Bezgraniczny	Ciąg lub liczba całkowita	Zarówno	Tylko w bloku PL / SQL	Nie
Tabela zagnieżdżona	Bezgraniczny	Liczba całkowita	Zaczyna się gęsty, może stać się rzadki	Albo w bloku PL / SQL, albo na poziomie schematu	tak
Tablica Variablesize (Varray)	Zobowiązany	Liczba całkowita	Zawsze gęsty	Albo w bloku PL / SQL, albo na poziomie schematu	tak

Omówiliśmy już varray w rozdziale 'PL/SQL arrays'. W tym rozdziale omówimy tabele PL / SQL.

Oba typy tabel PL / SQL, tj. Tabele indeksowane i tabele zagnieżdżone, mają taką samą strukturę, a dostęp do ich wierszy uzyskuje się za pomocą notacji z indeksem dolnym. Jednak te dwa typy tabel różnią się pod jednym względem; tabele zagnieżdżone mogą być przechowywane w kolumnie bazy danych, a tabele indeksowane nie mogą.

Indeks według tabeli

Na index-by table (nazywany również associative array) jest zbiorem key-valuepary. Każdy klucz jest unikalny i służy do lokalizowania odpowiedniej wartości. Klucz może być liczbą całkowitą lub łańcuchem.

Tabela indeksu według jest tworzona przy użyciu następującej składni. Tutaj tworzymy plikindex-by tabela o nazwie table_name, których klucze będą miały typ indeks_indeksowy, a powiązane wartości będą miały typ_elementu

TYPE type_name IS TABLE OF element_type [NOT NULL] INDEX BY subscript_type; 
 
table_name type_name;

Przykład

Poniższy przykład pokazuje, jak utworzyć tabelę do przechowywania wartości całkowitych wraz z nazwami, a później drukuje tę samą listę nazw.

DECLARE 
   TYPE salary IS TABLE OF NUMBER INDEX BY VARCHAR2(20); 
   salary_list salary; 
   name   VARCHAR2(20); 
BEGIN 
   -- adding elements to the table 
   salary_list('Rajnish') := 62000; 
   salary_list('Minakshi') := 75000; 
   salary_list('Martin') := 100000; 
   salary_list('James') := 78000;  
   
   -- printing the table 
   name := salary_list.FIRST; 
   WHILE name IS NOT null LOOP 
      dbms_output.put_line 
      ('Salary of ' || name || ' is ' || TO_CHAR(salary_list(name))); 
      name := salary_list.NEXT(name); 
   END LOOP; 
END; 
/

Gdy powyższy kod jest wykonywany w wierszu polecenia SQL, daje następujący wynik -

Salary of James is 78000 
Salary of Martin is 100000 
Salary of Minakshi is 75000 
Salary of Rajnish is 62000  

PL/SQL procedure successfully completed.

Przykład

Elementami tabeli indeksowanej mogą być również pliki %ROWTYPE dowolnej tabeli bazy danych lub %TYPEdowolnego pola tabeli bazy danych. Poniższy przykład ilustruje tę koncepcję. Będziemy używaćCUSTOMERS tabela przechowywana w naszej bazie danych jako -

Select * from customers;  

+----+----------+-----+-----------+----------+ 
| ID | NAME     | AGE | ADDRESS   | SALARY   | 
+----+----------+-----+-----------+----------+ 
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 | 
|  2 | Khilan   |  25 | Delhi     |  1500.00 | 
|  3 | kaushik  |  23 | Kota      |  2000.00 | 
|  4 | Chaitali |  25 | Mumbai    |  6500.00 | 
|  5 | Hardik   |  27 | Bhopal    |  8500.00 | 
|  6 | Komal    |  22 | MP        |  4500.00 | 
+----+----------+-----+-----------+----------+

DECLARE 
   CURSOR c_customers is 
      select name from customers; 

   TYPE c_list IS TABLE of customers.Name%type INDEX BY binary_integer; 
   name_list c_list; 
   counter integer :=0; 
BEGIN 
   FOR n IN c_customers LOOP 
      counter := counter +1; 
      name_list(counter) := n.name; 
      dbms_output.put_line('Customer('||counter||'):'||name_lis t(counter)); 
   END LOOP; 
END; 
/

Gdy powyższy kod jest wykonywany w wierszu polecenia SQL, daje następujący wynik -

Customer(1): Ramesh  
Customer(2): Khilan  
Customer(3): kaushik     
Customer(4): Chaitali  
Customer(5): Hardik  
Customer(6): Komal  

PL/SQL procedure successfully completed

Tabele zagnieżdżone

ZA nested tablejest jak jednowymiarowa tablica z dowolną liczbą elementów. Jednak tabela zagnieżdżona różni się od tablicy w następujących aspektach -

• Tablica ma zadeklarowaną liczbę elementów, ale tabela zagnieżdżona nie. Rozmiar tabeli zagnieżdżonej może się dynamicznie zwiększać.

• Tablica jest zawsze gęsta, tj. Zawsze ma następujące po sobie indeksy. Zagnieżdżona tablica jest początkowo gęsta, ale może stać się rzadka po usunięciu z niej elementów.

Zagnieżdżona tabela jest tworzona przy użyciu następującej składni -

TYPE type_name IS TABLE OF element_type [NOT NULL]; 
 
table_name type_name;

Ta deklaracja jest podobna do deklaracji index-by stół, ale nie ma INDEX BY klauzula.

Zagnieżdżona tabela może być przechowywana w kolumnie bazy danych. Można go ponadto wykorzystać do uproszczenia operacji SQL, w których łączysz tabelę jednokolumnową z większą tabelą. Tablica asocjacyjna nie może być przechowywana w bazie danych.

Przykład

Poniższe przykłady ilustrują użycie tabeli zagnieżdżonej -

DECLARE 
   TYPE names_table IS TABLE OF VARCHAR2(10); 
   TYPE grades IS TABLE OF INTEGER;  
   names names_table; 
   marks grades; 
   total integer; 
BEGIN 
   names := names_table('Kavita', 'Pritam', 'Ayan', 'Rishav', 'Aziz'); 
   marks:= grades(98, 97, 78, 87, 92); 
   total := names.count; 
   dbms_output.put_line('Total '|| total || ' Students'); 
   FOR i IN 1 .. total LOOP 
      dbms_output.put_line('Student:'||names(i)||', Marks:' || marks(i)); 
   end loop; 
END; 
/

Gdy powyższy kod jest wykonywany w wierszu polecenia SQL, daje następujący wynik -

Total 5 Students 
Student:Kavita, Marks:98 
Student:Pritam, Marks:97 
Student:Ayan, Marks:78 
Student:Rishav, Marks:87 
Student:Aziz, Marks:92  

PL/SQL procedure successfully completed.

Przykład

Elementy a nested table może być również %ROWTYPEdowolnej tabeli bazy danych lub% TYPE dowolnego pola tabeli bazy danych. Poniższy przykład ilustruje tę koncepcję. Będziemy używać tabeli CUSTOMERS przechowywanej w naszej bazie danych jako -

Select * from customers;  

+----+----------+-----+-----------+----------+ 
| ID | NAME     | AGE | ADDRESS   | SALARY   | 
+----+----------+-----+-----------+----------+ 
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 | 
|  2 | Khilan   |  25 | Delhi     |  1500.00 | 
|  3 | kaushik  |  23 | Kota      |  2000.00 | 
|  4 | Chaitali |  25 | Mumbai    |  6500.00 | 
|  5 | Hardik   |  27 | Bhopal    |  8500.00 | 
|  6 | Komal    |  22 | MP        |  4500.00 | 
+----+----------+-----+-----------+----------+

DECLARE 
   CURSOR c_customers is  
      SELECT  name FROM customers;  
   TYPE c_list IS TABLE of customerS.No.ame%type; 
   name_list c_list := c_list(); 
   counter integer :=0; 
BEGIN 
   FOR n IN c_customers LOOP 
      counter := counter +1; 
      name_list.extend; 
      name_list(counter)  := n.name; 
      dbms_output.put_line('Customer('||counter||'):'||name_list(counter)); 
   END LOOP; 
END; 
/

Gdy powyższy kod jest wykonywany w wierszu polecenia SQL, daje następujący wynik -

Customer(1): Ramesh  
Customer(2): Khilan  
Customer(3): kaushik     
Customer(4): Chaitali  
Customer(5): Hardik  
Customer(6): Komal  

PL/SQL procedure successfully completed.

Metody zbierania

PL / SQL udostępnia wbudowane metody kolekcji, które ułatwiają korzystanie z kolekcji. W poniższej tabeli wymieniono metody i ich przeznaczenie -

S.Nr	Nazwa i cel metody
1	EXISTS(n) Zwraca wartość TRUE, jeśli n-ty element kolekcji istnieje; w przeciwnym razie zwraca FALSE.
2	COUNT Zwraca liczbę elementów, które aktualnie zawiera kolekcja.
3	LIMIT Sprawdza maksymalny rozmiar kolekcji.
4	FIRST Zwraca pierwsze (najmniejsze) liczby indeksu w kolekcji, w której są używane indeksy całkowite.
5	LAST Zwraca ostatnie (największe) liczby indeksu w kolekcji używającej indeksów całkowitych.
6	PRIOR(n) Zwraca numer indeksu poprzedzający indeks n w kolekcji.
7	NEXT(n) Zwraca numer indeksu, który następuje po indeksie n.
8	EXTEND Dołącza jeden element o wartości null do kolekcji.
9	EXTEND(n) Dołącza null elementów do kolekcji.
10	EXTEND(n,i) Dołącza nkopie i- tego elementu do kolekcji.
11	TRIM Usuwa jeden element z końca kolekcji.
12	TRIM(n) Usuwa n elementy z końca kolekcji.
13	DELETE Usuwa wszystkie elementy z kolekcji, ustawiając COUNT na 0.
14	DELETE(n) Usuwa nthelement z tablicy asocjacyjnej z kluczem numerycznym lub zagnieżdżoną tabelą. Jeśli tablica asocjacyjna ma klucz łańcuchowy, element odpowiadający wartości klucza jest usuwany. Gdybyn jest zerowy, DELETE(n) nic nie robi.
15	DELETE(m,n) Usuwa wszystkie elementy z zakresu m..nz tablicy asocjacyjnej lub tabeli zagnieżdżonej. Gdybym jest większy niż n albo jeśli m lub n jest zerowy, DELETE(m,n) nic nie robi.

Wyjątki dotyczące kolekcji

W poniższej tabeli przedstawiono wyjątki kolekcji i kiedy są one zgłaszane -

Wyjątek dotyczący kolekcji	Wychowany w sytuacjach
COLLECTION_IS_NULL	Próbujesz operować na kolekcji atomowo zerowej.
NIE ZNALEZIONO DANYCH	Indeks dolny oznacza usunięty element lub nieistniejący element tablicy asocjacyjnej.
SUBSCRIPT_BEYOND_COUNT	Indeks dolny przekracza liczbę elementów w kolekcji.
SUBSCRIPT_OUTSIDE_LIMIT	Indeks dolny jest poza dozwolonym zakresem.
VALUE_ERROR	Indeks dolny jest pusty lub nie można go zamienić na typ klucza. Ten wyjątek może wystąpić, jeśli klucz jest zdefiniowany jako plikPLS_INTEGER zakres, a indeks dolny jest poza tym zakresem.

W tym rozdziale omówimy transakcje w PL / SQL. Baza danychtransactionto niepodzielna jednostka pracy, która może składać się z jednej lub więcej powiązanych instrukcji SQL. Nazywa się to niepodzielną, ponieważ modyfikacje bazy danych wywołane przez instrukcje SQL, które stanowią transakcję, mogą być zbiorczo zatwierdzone, tj. Trwale w bazie danych lub wycofane (cofnięte) z bazy danych.

Pomyślnie wykonana instrukcja SQL i zatwierdzona transakcja to nie to samo. Nawet jeśli instrukcja SQL zostanie wykonana pomyślnie, chyba że transakcja zawierająca instrukcję zostanie zatwierdzona, można ją wycofać, a wszystkie zmiany wprowadzone przez instrukcję (y) można cofnąć.

Rozpoczęcie i zakończenie transakcji

Transakcja ma rozszerzenie beginning i end. Transakcja rozpoczyna się, gdy ma miejsce jedno z następujących zdarzeń -

• Pierwsza instrukcja SQL jest wykonywana po połączeniu się z bazą danych.

• Przy każdej nowej instrukcji SQL wydanej po zakończeniu transakcji.

Transakcja kończy się, gdy ma miejsce jedno z następujących zdarzeń -

• ZA COMMIT lub a ROLLBACK wydano oświadczenie.

• ZA DDL oświadczenie, takie jak CREATE TABLEwydano oświadczenie; ponieważ w tym przypadku polecenie COMMIT jest wykonywane automatycznie.

• ZA DCL oświadczenie, takie jak GRANTwydano oświadczenie; ponieważ w tym przypadku polecenie COMMIT jest wykonywane automatycznie.

• Użytkownik rozłącza się z bazą danych.

• Użytkownik wychodzi z SQL*PLUS wydając EXIT polecenie, polecenie COMMIT jest wykonywane automatycznie.

• SQL * Plus kończy działanie nieprawidłowo, a ROLLBACK jest wykonywana automatycznie.

• ZA DMLinstrukcja kończy się niepowodzeniem; w takim przypadku ROLLBACK jest wykonywana automatycznie w celu cofnięcia tej instrukcji DML.

Zatwierdzenie transakcji

Transakcja jest trwała przez wydanie polecenia SQL COMMIT. Ogólna składnia polecenia COMMIT to -

COMMIT;

Na przykład,

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) 
VALUES (1, 'Ramesh', 32, 'Ahmedabad', 2000.00 ); 

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) 
VALUES (2, 'Khilan', 25, 'Delhi', 1500.00 ); 

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) 
VALUES (3, 'kaushik', 23, 'Kota', 2000.00 ); 

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) 
VALUES (4, 'Chaitali', 25, 'Mumbai', 6500.00 ); 

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) 
VALUES (5, 'Hardik', 27, 'Bhopal', 8500.00 ); 

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) 
VALUES (6, 'Komal', 22, 'MP', 4500.00 ); 

COMMIT;

Wycofywanie transakcji

Zmiany wprowadzone w bazie danych bez polecenia COMMIT można cofnąć za pomocą polecenia ROLLBACK.

Ogólna składnia polecenia ROLLBACK to -

ROLLBACK [TO SAVEPOINT < savepoint_name>];

Kiedy transakcja zostaje przerwana z powodu jakiejś bezprecedensowej sytuacji, takiej jak awaria systemu, cała transakcja od momentu zatwierdzenia jest automatycznie wycofywana. Jeśli nie używaszsavepoint, a następnie użyj poniższej instrukcji, aby cofnąć wszystkie zmiany -

ROLLBACK;

Savepoints

Savepoints to rodzaj znaczników, które pomagają w podzieleniu długiej transakcji na mniejsze jednostki poprzez ustawienie niektórych punktów kontrolnych. Ustawiając punkty zapisu w ramach długiej transakcji, możesz w razie potrzeby cofnąć się do punktu kontrolnego. Odbywa się to poprzez wydanieSAVEPOINT Komenda.

Ogólna składnia polecenia SAVEPOINT to -

SAVEPOINT < savepoint_name >;

Na przykład

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) 
VALUES (7, 'Rajnish', 27, 'HP', 9500.00 ); 

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) 
VALUES (8, 'Riddhi', 21, 'WB', 4500.00 ); 
SAVEPOINT sav1;
  
UPDATE CUSTOMERS 
SET SALARY = SALARY + 1000; 
ROLLBACK TO sav1;
  
UPDATE CUSTOMERS 
SET SALARY = SALARY + 1000 
WHERE ID = 7; 
UPDATE CUSTOMERS 
SET SALARY = SALARY + 1000 
WHERE ID = 8; 

COMMIT;

ROLLBACK TO sav1 - Ta instrukcja cofa wszystkie zmiany do momentu, w którym zaznaczyłeś punkt zapisu sav1.

Następnie rozpoczną się nowe zmiany, które wprowadzisz.

Automatyczna kontrola transakcji

Aby wykonać COMMIT automatycznie, gdy plik INSERT, UPDATE lub DELETE jest wykonywane, możesz ustawić AUTOCOMMIT zmienna środowiskowa jako -

SET AUTOCOMMIT ON;

Możesz wyłączyć tryb automatycznego zatwierdzania za pomocą następującego polecenia -

SET AUTOCOMMIT OFF;

W tym rozdziale omówimy datę i godzinę w PL / SQL. Istnieją dwie klasy typów danych związanych z datą i godziną w PL / SQL -

• Typy danych typu data i godzina
• Typy danych interwałowych

Typy danych Datetime to -

• DATE
• TIMESTAMP
• TIMESTAMP ZE STREFĄ CZASOWĄ
• ZNAK CZASOWY Z LOKALNĄ STREFĄ CZASOWĄ

Typy danych Interval to -

• ODSTĘP OD ROKU DO MIESIĄCA
• ODSTĘP DZIEŃ DO DRUGI

Wartości pól dla typów danych typu Datetime i Interval

Obie datetime i interval typy danych składają się z fields. Wartości tych pól określają wartość typu danych. W poniższej tabeli wymieniono pola i ich możliwe wartości dla dat i interwałów.

Nazwa pola	Prawidłowe wartości daty i godziny	Prawidłowe wartości przedziałów
ROK	-4712 do 9999 (z wyłączeniem roku 0)	Dowolna niezerowa liczba całkowita
MIESIĄC	01 do 12	Od 0 do 11
DZIEŃ	01 do 31 (ograniczone wartościami MIESIĄCA i ROKU, zgodnie z regułami kalendarza dla regionu)	Dowolna niezerowa liczba całkowita
GODZINA	00 do 23	Od 0 do 23
MINUTA	00 do 59	Od 0 do 59
DRUGA	00 do 59,9 (n), gdzie 9 (n) jest dokładnością ułamków czasu w sekundach Część 9 (n) nie dotyczy DATE.	0 do 59,9 (n), gdzie 9 (n) jest dokładnością ułamków sekund interwału
TIMEZONE_HOUR	-12 do 14 (zakres obejmuje zmiany czasu letniego) Nie dotyczy DATE ani TIMESTAMP.	Nie dotyczy
TIMEZONE_MINUTE	00 do 59 Nie dotyczy DATE ani TIMESTAMP.	Nie dotyczy
TIMEZONE_REGION	Nie dotyczy DATE ani TIMESTAMP.	Nie dotyczy
TIMEZONE_ABBR	Nie dotyczy DATE ani TIMESTAMP.	Nie dotyczy

Typy danych i funkcje daty i godziny

Poniżej przedstawiono typy danych typu data i godzina -

DATA

Przechowuje informacje o dacie i godzinie zarówno w postaci znaków, jak i liczb. Zawiera informacje dotyczące wieku, roku, miesiąca, daty, godziny, minuty i sekundy. Jest określony jako -

ZNAK CZASU

Jest to rozszerzenie typu danych DATE. Przechowuje rok, miesiąc i dzień typu danych DATE, wraz z wartościami godzin, minut i sekund. Jest to przydatne do przechowywania dokładnych wartości czasu.

TIMESTAMP ZE STREFĄ CZASOWĄ

Jest to wariant TIMESTAMP, który zawiera w swojej wartości nazwę regionu strefy czasowej lub przesunięcie strefy czasowej. Przesunięcie strefy czasowej to różnica (w godzinach i minutach) między czasem lokalnym a czasem UTC. Ten typ danych jest przydatny do zbierania i oceny informacji o dacie w różnych regionach geograficznych.

ZNAK CZASOWY Z LOKALNĄ STREFĄ CZASOWĄ

Jest to kolejny wariant TIMESTAMP, który zawiera przesunięcie strefy czasowej w swojej wartości.

Poniższa tabela zawiera funkcje daty i godziny (gdzie x ma wartość datetime) -

S.Nr	Nazwa i opis funkcji
1	ADD_MONTHS(x, y); Dodaje y miesięcy do x.
2	LAST_DAY(x); Zwraca ostatni dzień miesiąca.
3	MONTHS_BETWEEN(x, y); Zwraca liczbę miesięcy między x i y.
4	NEXT_DAY(x, day); Zwraca datę i godzinę następnego dnia pox.
5	NEW_TIME; Zwraca wartość godziny / dnia ze strefy czasowej określonej przez użytkownika.
6	ROUND(x [, unit]); Rundy x.
7	SYSDATE(); Zwraca bieżącą datę i godzinę.
8	TRUNC(x [, unit]); Obcina x.

Funkcje datownika (gdzie x ma wartość datownika) -

S.Nr	Nazwa i opis funkcji
1	CURRENT_TIMESTAMP(); Zwraca TIMESTAMP WITH TIME ZONE zawierającą aktualny czas sesji oraz strefę czasową sesji.
2	EXTRACT({ YEAR | MONTH | DAY | HOUR | MINUTE | SECOND } | { TIMEZONE_HOUR | TIMEZONE_MINUTE } | { TIMEZONE_REGION | } TIMEZONE_ABBR ) FROM x) Wyodrębnia i zwraca rok, miesiąc, dzień, godzinę, minutę, sekundę lub strefę czasową z x.
3	FROM_TZ(x, time_zone); Konwertuje TIMESTAMP x i strefę czasową określoną przez time_zone na TIMESTAMP WITH TIMEZONE.
4	LOCALTIMESTAMP(); Zwraca TIMESTAMP zawierający czas lokalny w strefie czasowej sesji.
5	SYSTIMESTAMP(); Zwraca TIMESTAMP WITH TIME ZONE zawierającą bieżący czas bazy danych wraz ze strefą czasową bazy danych.
6	SYS_EXTRACT_UTC(x); Konwertuje TIMESTAMP WITH TIMEZONE x na TIMESTAMP zawierający datę i godzinę w UTC.
7	TO_TIMESTAMP(x, [format]); Konwertuje ciąg x na TIMESTAMP.
8	TO_TIMESTAMP_TZ(x, [format]); Konwertuje ciąg x na TIMESTAMP WITH TIMEZONE.

Przykłady

Poniższe fragmenty kodu ilustrują użycie powyższych funkcji -

Example 1

SELECT SYSDATE FROM DUAL;

Output -

08/31/2012 5:25:34 PM

Example 2

SELECT TO_CHAR(CURRENT_DATE, 'DD-MM-YYYY HH:MI:SS') FROM DUAL;

Output -

31-08-2012 05:26:14

Example 3

SELECT ADD_MONTHS(SYSDATE, 5) FROM DUAL;

Output -

01/31/2013 5:26:31 PM

Example 4

SELECT LOCALTIMESTAMP FROM DUAL;

Output -

8/31/2012 5:26:55.347000 PM

Typy i funkcje danych przedziałów czasu

Poniżej przedstawiono typy danych Interval -

• IINTERVAL YEAR TO MONTH - Przechowuje okres czasu przy użyciu pól YEAR i MONTH datetime.

• INTERVAL DAY TO SECOND - Przechowuje okres czasu w postaci dni, godzin, minut i sekund.

Funkcje interwałowe

S.Nr	Nazwa i opis funkcji
1	NUMTODSINTERVAL(x, interval_unit); Konwertuje liczbę x na INTERVAL DAY TO SECOND.
2	NUMTOYMINTERVAL(x, interval_unit); Konwertuje liczbę x na INTERVAL YEAR TO MONTH.
3	TO_DSINTERVAL(x); Konwertuje ciąg x na INTERVAL DAY TO SECOND.
4	TO_YMINTERVAL(x); Konwertuje ciąg x na INTERVAL YEAR TO MONTH.

W tym rozdziale omówimy wyjście DBMS w PL / SQL. PlikDBMS_OUTPUTto wbudowany pakiet, który umożliwia wyświetlanie danych wyjściowych, debugowania informacji i wysyłanie komunikatów z bloków PL / SQL, podprogramów, pakietów i wyzwalaczy. Korzystaliśmy już z tego pakietu w całym naszym samouczku.

Spójrzmy na mały fragment kodu, który wyświetli wszystkie tabele użytkowników w bazie danych. Wypróbuj w swojej bazie danych, aby wyświetlić listę wszystkich nazw tabel -

BEGIN 
   dbms_output.put_line  (user || ' Tables in the database:'); 
   FOR t IN (SELECT table_name FROM user_tables) 
   LOOP 
      dbms_output.put_line(t.table_name); 
   END LOOP; 
END; 
/

DBMS_OUTPUT Podprogramy

Pakiet DBMS_OUTPUT ma następujące podprogramy -

S.Nr	Podprogram i cel
1	DBMS_OUTPUT.DISABLE; Wyłącza wysyłanie wiadomości.
2	DBMS_OUTPUT.ENABLE(buffer_size IN INTEGER DEFAULT 20000); Włącza wysyłanie wiadomości. NULL wartośćbuffer_size reprezentuje nieograniczony rozmiar bufora.
3	DBMS_OUTPUT.GET_LINE (line OUT VARCHAR2, status OUT INTEGER); Pobiera pojedynczą linię zbuforowanych informacji.
4	DBMS_OUTPUT.GET_LINES (lines OUT CHARARR, numlines IN OUT INTEGER); Pobiera tablicę wierszy z bufora.
5	DBMS_OUTPUT.NEW_LINE; Umieszcza znacznik końca linii.
6	DBMS_OUTPUT.PUT(item IN VARCHAR2); Umieszcza częściową linię w buforze.
7	DBMS_OUTPUT.PUT_LINE(item IN VARCHAR2); Umieszcza linię w buforze.

Przykład

DECLARE 
   lines dbms_output.chararr; 
   num_lines number; 
BEGIN 
   -- enable the buffer with default size 20000 
   dbms_output.enable; 
   
   dbms_output.put_line('Hello Reader!'); 
   dbms_output.put_line('Hope you have enjoyed the tutorials!'); 
   dbms_output.put_line('Have a great time exploring pl/sql!'); 
  
   num_lines := 3; 
  
   dbms_output.get_lines(lines, num_lines); 
  
   FOR i IN 1..num_lines LOOP 
      dbms_output.put_line(lines(i)); 
   END LOOP; 
END; 
/

Gdy powyższy kod jest wykonywany w wierszu polecenia SQL, daje następujący wynik -

Hello Reader! 
Hope you have enjoyed the tutorials! 
Have a great time exploring pl/sql!  

PL/SQL procedure successfully completed.

W tym rozdziale omówimy język PL / SQL zorientowany obiektowo. PL / SQL umożliwia zdefiniowanie typu obiektu, co pomaga w projektowaniu obiektowej bazy danych w Oracle. Typ obiektu umożliwia tworzenie typów złożonych. Korzystanie z obiektów pozwala na implementację obiektów świata rzeczywistego o określonej strukturze danych i metodach ich obsługi. Obiekty mają atrybuty i metody. Atrybuty są właściwościami obiektu i służą do przechowywania stanu obiektu; i metody są używane do modelowania jego zachowania.

Obiekty są tworzone za pomocą instrukcji CREATE [OR REPLACE] TYPE. Poniżej znajduje się przykład tworzenia prostego plikuaddress obiekt składający się z kilku atrybutów -

CREATE OR REPLACE TYPE address AS OBJECT 
(house_no varchar2(10), 
 street varchar2(30), 
 city varchar2(20), 
 state varchar2(10), 
 pincode varchar2(10) 
); 
/

Gdy powyższy kod jest wykonywany w wierszu polecenia SQL, daje następujący wynik -

Type created.

Stwórzmy jeszcze jeden obiekt customer gdzie będziemy zawijać attributes i methods razem, aby mieć poczucie zorientowania na obiekt -

CREATE OR REPLACE TYPE customer AS OBJECT 
(code number(5), 
 name varchar2(30), 
 contact_no varchar2(12), 
 addr address, 
 member procedure display 
); 
/

Gdy powyższy kod jest wykonywany w wierszu polecenia SQL, daje następujący wynik -

Type created.

Tworzenie wystąpienia obiektu

Zdefiniowanie typu obiektu zapewnia plan dla obiektu. Aby użyć tego obiektu, musisz utworzyć instancje tego obiektu. Dostęp do atrybutów i metod obiektu można uzyskać za pomocą nazwy instancji ithe access operator (.) w następujący sposób -

DECLARE 
   residence address; 
BEGIN 
   residence := address('103A', 'M.G.Road', 'Jaipur', 'Rajasthan','201301'); 
   dbms_output.put_line('House No: '|| residence.house_no); 
   dbms_output.put_line('Street: '|| residence.street); 
   dbms_output.put_line('City: '|| residence.city); 
   dbms_output.put_line('State: '|| residence.state); 
   dbms_output.put_line('Pincode: '|| residence.pincode); 
END; 
/

Gdy powyższy kod jest wykonywany w wierszu polecenia SQL, daje następujący wynik -

House No: 103A 
Street: M.G.Road 
City: Jaipur 
State: Rajasthan 
Pincode: 201301  

PL/SQL procedure successfully completed.

Metody członkowskie

Member methods są używane do manipulowania plikiem attributesobiektu. Deklarację metody składowej podaje się podczas deklarowania typu obiektu. Treść obiektu definiuje kod metod składowych. Treść obiektu jest tworzona za pomocą instrukcji CREATE TYPE BODY.

Constructorsto funkcje, które zwracają nowy obiekt jako jego wartość. Każdy obiekt ma zdefiniowaną przez system metodę konstruktora. Nazwa konstruktora jest taka sama, jak typ obiektu. Na przykład -

residence := address('103A', 'M.G.Road', 'Jaipur', 'Rajasthan','201301');

Plik comparison methodssłużą do porównywania obiektów. Istnieją dwa sposoby porównywania obiektów -

Metoda mapy

Plik Map methodjest funkcją zaimplementowaną w taki sposób, że jej wartość zależy od wartości atrybutów. Na przykład dla obiektu klienta, jeśli kod klienta jest taki sam dla dwóch klientów, obaj klienci mogą być tacy sami. Zatem związek między tymi dwoma obiektami zależałby od wartości kodu.

Sposób zamawiania

Plik Order methodimplementuje pewną wewnętrzną logikę do porównywania dwóch obiektów. Na przykład dla obiektu prostokątnego prostokąt jest większy niż inny prostokąt, jeśli oba jego boki są większe.

Korzystanie z metody Map

Spróbujmy zrozumieć powyższe pojęcia, używając następującego obiektu prostokąta -

CREATE OR REPLACE TYPE rectangle AS OBJECT 
(length number, 
 width number, 
 member function enlarge( inc number) return rectangle, 
 member procedure display, 
 map member function measure return number 
); 
/

Gdy powyższy kod jest wykonywany w wierszu polecenia SQL, daje następujący wynik -

Type created.

Tworzenie treści typu -

CREATE OR REPLACE TYPE BODY rectangle AS 
   MEMBER FUNCTION enlarge(inc number) return rectangle IS 
   BEGIN 
      return rectangle(self.length + inc, self.width + inc); 
   END enlarge;  
   MEMBER PROCEDURE display IS 
   BEGIN  
      dbms_output.put_line('Length: '|| length); 
      dbms_output.put_line('Width: '|| width); 
   END display;  
   MAP MEMBER FUNCTION measure return number IS 
   BEGIN 
      return (sqrt(length*length + width*width)); 
   END measure; 
END; 
/

Gdy powyższy kod jest wykonywany w wierszu polecenia SQL, daje następujący wynik -

Type body created.

Teraz używając obiektu rectangle i jego funkcji składowych -

DECLARE 
   r1 rectangle; 
   r2 rectangle; 
   r3 rectangle; 
   inc_factor number := 5; 
BEGIN 
   r1 := rectangle(3, 4); 
   r2 := rectangle(5, 7); 
   r3 := r1.enlarge(inc_factor); 
   r3.display;  
   IF (r1 > r2) THEN -- calling measure function 
      r1.display; 
   ELSE 
      r2.display; 
   END IF; 
END; 
/

Gdy powyższy kod jest wykonywany w wierszu polecenia SQL, daje następujący wynik -

Length: 8 
Width: 9 
Length: 5 
Width: 7  

PL/SQL procedure successfully completed.

Korzystanie z metody zamawiania

Teraz same effect could be achieved using an order method. Odtwórzmy prostokątny obiekt za pomocą metody zamówienia -

CREATE OR REPLACE TYPE rectangle AS OBJECT 
(length number, 
 width number, 
 member procedure display, 
 order member function measure(r rectangle) return number 
); 
/

Gdy powyższy kod jest wykonywany w wierszu polecenia SQL, daje następujący wynik -

Type created.

Tworzenie treści typu -

CREATE OR REPLACE TYPE BODY rectangle AS 
   MEMBER PROCEDURE display IS 
   BEGIN 
      dbms_output.put_line('Length: '|| length); 
      dbms_output.put_line('Width: '|| width); 
   END display;  
   ORDER MEMBER FUNCTION measure(r rectangle) return number IS 
   BEGIN 
      IF(sqrt(self.length*self.length + self.width*self.width)> 
         sqrt(r.length*r.length + r.width*r.width)) then 
         return(1); 
      ELSE 
         return(-1); 
      END IF; 
   END measure; 
END; 
/

Gdy powyższy kod jest wykonywany w wierszu polecenia SQL, daje następujący wynik -

Type body created.

Korzystanie z obiektu prostokątnego i jego funkcji składowych -

DECLARE 
   r1 rectangle; 
   r2 rectangle; 
BEGIN 
   r1 := rectangle(23, 44); 
   r2 := rectangle(15, 17); 
   r1.display; 
   r2.display; 
   IF (r1 > r2) THEN -- calling measure function 
      r1.display; 
   ELSE 
      r2.display; 
   END IF; 
END; 
/

Gdy powyższy kod jest wykonywany w wierszu polecenia SQL, daje następujący wynik -

Length: 23 
Width: 44 
Length: 15 
Width: 17 
Length: 23 
Width: 44 

PL/SQL procedure successfully completed.

Dziedziczenie dla obiektów PL / SQL

PL / SQL umożliwia tworzenie obiektów z istniejących obiektów bazowych. Aby zaimplementować dziedziczenie, obiekty bazowe powinny być zadeklarowane jakoNOT FINAL. Wartość domyślna toFINAL.

Poniższe programy ilustrują dziedziczenie w obiektach PL / SQL. Stwórzmy kolejny obiekt o nazwieTableTop, jest to dziedziczone z obiektu Rectangle. W tym celu musimy utworzyć podstawowy obiekt prostokątny -

CREATE OR REPLACE TYPE rectangle AS OBJECT 
(length number, 
 width number, 
 member function enlarge( inc number) return rectangle, 
 NOT FINAL member procedure display) NOT FINAL 
/

Gdy powyższy kod jest wykonywany w wierszu polecenia SQL, daje następujący wynik -

Type created.

Tworzenie treści typu podstawowego -

CREATE OR REPLACE TYPE BODY rectangle AS 
   MEMBER FUNCTION enlarge(inc number) return rectangle IS 
   BEGIN 
      return rectangle(self.length + inc, self.width + inc); 
   END enlarge;  
   MEMBER PROCEDURE display IS 
   BEGIN 
      dbms_output.put_line('Length: '|| length); 
      dbms_output.put_line('Width: '|| width); 
   END display; 
END; 
/

Gdy powyższy kod jest wykonywany w wierszu polecenia SQL, daje następujący wynik -

Type body created.

Tworzenie blatu obiektu potomnego -

CREATE OR REPLACE TYPE tabletop UNDER rectangle 
(   
   material varchar2(20), 
   OVERRIDING member procedure display 
) 
/

Gdy powyższy kod jest wykonywany w wierszu polecenia SQL, daje następujący wynik -

Type created.

Tworzenie treści typu dla blatu obiektu podrzędnego

CREATE OR REPLACE TYPE BODY tabletop AS 
OVERRIDING MEMBER PROCEDURE display IS 
BEGIN 
   dbms_output.put_line('Length: '|| length); 
   dbms_output.put_line('Width: '|| width); 
   dbms_output.put_line('Material: '|| material); 
END display; 
/

Gdy powyższy kod jest wykonywany w wierszu polecenia SQL, daje następujący wynik -

Type body created.

Korzystanie z obiektu blatu i jego funkcji składowych -

DECLARE 
   t1 tabletop; 
   t2 tabletop; 
BEGIN 
   t1:= tabletop(20, 10, 'Wood'); 
   t2 := tabletop(50, 30, 'Steel'); 
   t1.display; 
   t2.display; 
END;
/

Gdy powyższy kod jest wykonywany w wierszu polecenia SQL, daje następujący wynik -

Length: 20 
Width: 10 
Material: Wood 
Length: 50 
Width: 30 
Material: Steel  

PL/SQL procedure successfully completed.

Obiekty abstrakcyjne w PL / SQL

Plik NOT INSTANTIABLEklauzula umożliwia zadeklarowanie abstrakcyjnego obiektu. Nie możesz używać abstrakcyjnego obiektu takim, jakim jest; będziesz musiał stworzyć podtyp lub typ podrzędny takich obiektów, aby móc korzystać z ich funkcjonalności.

Na przykład,

CREATE OR REPLACE TYPE rectangle AS OBJECT 
(length number, 
 width number, 
 NOT INSTANTIABLE NOT FINAL MEMBER PROCEDURE display)  
 NOT INSTANTIABLE NOT FINAL 
/

Gdy powyższy kod jest wykonywany w wierszu polecenia SQL, daje następujący wynik -

Type created.