Oracle/PLSQL анонимные блоки — Oracle PL/SQL •MySQL •MariaDB •SQL Server •SQLite

В этом учебном материале вы узнаете, как использовать блоки в Oracle PL/SQL с синтаксисом и примерами.

Описание

Структуры блоков PL/SQL могут быть включены в определения процедур, функций или триггеров PL/SQL или выполняться независимо в форме анонимного блока.

Структура блока

Структура блока PL/SQL:

DECLARE
--объявление переменных
BEGIN
--исполняемые операторы
EXCEPTION
--обработка исключений
END;

Описание структуры блока PL/SQL

DECLARE: Необязательный. Раздел деклариорвания переменных, курсоров, типов, процедур и функций для использования в блоке. Область действия является локальной для блока. Каждое объявление должно заканчиваться точкой с запятой.
BEGIN - END: Обязательный. Раздел в котором исполняются один или несколько операторов PL/SQL. Должен быть как минимум один исполняемый оператор. Каждый оператор должен заканчиваться точкой с запятой. Блок BEGIN-END может содержать вложенные блоки BEGIN-END.
EXCEPTION: Необязательный. Раздел обработки исключительных ситуаций или "отлова" ошибок.

Примеры

В приведенном иже примере показан самый простейший допустимый блок, который ничего не делает.

BEGIN
- простейший блок SQL
  NULL;
END;

BEGIN

- простейший блок SQL

NULL;

END;

Пример блока PL/SQL с одной переменной

DECLARE
  l_var NUMBER;
BEGIN
  -- простой блок с одной переменной
  l_var := 1;
  DBMS_OUTPUT.PUT_LINE('l_var = ' || l_var);
END;
--Результат: l_var = 1

DECLARE

l_var NUMBER;

BEGIN

-- простой блок с одной переменной

l_var := 1;

DBMS_OUTPUT.PUT_LINE('l_var = ' || l_var);

END;

--Результат: l_var = 1

Пример встроенного блока PL/SQL

BEGIN
  DBMS_OUTPUT.PUT_LINE('Первый блок');
  BEGIN
    --встроенный блок
    DBMS_OUTPUT.PUT_LINE('Второй блок');
  END;
END;
--Результат:
Первый блок
Второй блок

BEGIN

DBMS_OUTPUT.PUT_LINE('Первый блок');

BEGIN

--встроенный блок

DBMS_OUTPUT.PUT_LINE('Второй блок');

END;

--Результат:

Первый блок

Второй блок

Пример встроенного блока PL/SQL с переменной

DECLARE
  -- встроенный блок и переменные
  l_outer_var NUMBER := 1;
BEGIN
  DECLARE
    l_inner_var NUMBER := 2;
  BEGIN
    DBMS_OUTPUT.PUT_LINE(l_outer_var || ' ' || l_inner_var);
  END;
END;
--Результат: 1 2

DECLARE

-- встроенный блок и переменные

l_outer_var NUMBER := 1;

BEGIN

DECLARE

l_inner_var NUMBER := 2;

BEGIN

DBMS_OUTPUT.PUT_LINE(l_outer_var || ' ' || l_inner_var);

END;

--Результат: 1 2

Пример встроенный блок PL/SQL и повторяющиеся имена переменных

DECLARE
-- встроенный блок и повторяющиеся имена переменных
  l_outer_var NUMBER := 1;
BEGIN
  DECLARE
    l_outer_var NUMBER := 2;
  BEGIN
    DBMS_OUTPUT.PUT_LINE(l_outer_var);
  END;
  DBMS_OUTPUT.PUT_LINE(l_outer_var);
END;
--Результат:
2
1

DECLARE

-- встроенный блок и повторяющиеся имена переменных

l_outer_var NUMBER := 1;

BEGIN

DECLARE

l_outer_var NUMBER := 2;

BEGIN

DBMS_OUTPUT.PUT_LINE(l_outer_var);

END;

DBMS_OUTPUT.PUT_LINE(l_outer_var);

END;

--Результат:

Пример видимости переменных в блоках PL/SQL

DECLARE
  -- встроенный блок и переменные
  l_outer_var NUMBER := 1;
BEGIN
  DECLARE
    l_inner_var NUMBER := 2;
  BEGIN
    DBMS_OUTPUT.PUT_LINE(l_outer_var || ' ' || l_inner_var);
  END;
 -- выдаст ошибку, поскольку l_inner_var недоступна в этом блоке
  DBMS_OUTPUT.PUT_LINE(l_outer_var || ' ' || l_inner_var);
END;

DECLARE

-- встроенный блок и переменные

l_outer_var NUMBER := 1;

BEGIN

DECLARE

l_inner_var NUMBER := 2;

BEGIN

DBMS_OUTPUT.PUT_LINE(l_outer_var || ' ' || l_inner_var);

END;

-- выдаст ошибку, поскольку l_inner_var недоступна в этом блоке

DBMS_OUTPUT.PUT_LINE(l_outer_var || ' ' || l_inner_var);

END;

В этом примере возникнет исключительная ситуация по причине того, что переменная l_inner_var объявлена во встроенном блоке и для внешнего блока не видна. Тогда, как переменная l_outer_var из внешнего блока видна в обоих.

Пример блока с вызовом процедуры

CREATE OR REPLACE PROCEDURE hello AS
  -- структура блока внутри процедуры
BEGIN
  DBMS_OUTPUT.PUT_LINE('Привет страна!');
END;

BEGIN
 -- структура блока при вызове процедуры hello
  hello;
END;
--Результат: Привет страна!

CREATE OR REPLACE PROCEDURE hello AS

-- структура блока внутри процедуры

BEGIN

DBMS_OUTPUT.PUT_LINE('Привет страна!');

END;

BEGIN

-- структура блока при вызове процедуры hello

hello;

END;

--Результат: Привет страна!

В этом примере мы создали процедуру hello, которая выводит на экран простое приветствие. Затем в следующем блоке мы вызываем эту процедуру. Если вам процедура hello больше не понадобиться, то ее можно удалить командой DROP PROCEDURE hello;

Пример с многими вложенными блоками PL/SQL

В следующем примере показаны вложенные блоки.

DECLARE 
v_char varchar2(12):='1';
BEGIN   
  DECLARE 
  v_char varchar2(12):='2';
  BEGIN 
    DECLARE 
    v_char varchar2(12):='3';
    BEGIN
    DBMS_OUTPUT.PUT_LINE(v_char);
    END;
  DBMS_OUTPUT.PUT_LINE(v_char);
  END;
DBMS_OUTPUT.PUT_LINE(v_char);
END; 
--Результат: 
3
2
1

DECLARE

v_char varchar2(12):='1';

BEGIN

DECLARE

v_char varchar2(12):='2';

BEGIN

DECLARE

v_char varchar2(12):='3';

BEGIN

DBMS_OUTPUT.PUT_LINE(v_char);

END;

DBMS_OUTPUT.PUT_LINE(v_char);

END;

DBMS_OUTPUT.PUT_LINE(v_char);

END;

--Результат:

В приведенном выше примере, PL/SQL блоки выполняются начиная с внутренних и заканчивая наружными.

Пример блока с разделом EXCEPTION

В следующем примере рассмотрим блок с разделом обработки исключительных ситуаций.

Declare
  l_date  Date;
Begin
  Select Sysdate Into l_date From dual Where 1=0;
  -- запрос выдаст ошибку
  --ORA-01403: no data found
  --ORA-06512: at line 4  
End;

Declare

l_date Date;

Begin

Select Sysdate Into l_date From dual Where 1=0;

-- запрос выдаст ошибку

--ORA-01403: no data found

--ORA-06512: at line 4

End;

Чтобы в будущем не возникало ошибок, мы добавим раздел обработки ошибок EXCEPTION.
С помощью встроенной исключительной ситуации NO_DATA_FOUND наш пример будет выглядеть так:

Declare
  l_date  Date;
Begin
  Select Sysdate Into l_date From dual Where 1=0;
  Exception 
  When NO_DATA_FOUND Then
    Null;
End;

Declare

l_date Date;

Begin

Select Sysdate Into l_date From dual Where 1=0;

Exception

When NO_DATA_FOUND Then

Null;

End;

В этом примере ошибка перехватывается обработчиком исключений, что позволит успешно выполнить код.

Пример блока который выводит символьный треугольник

В следующем примере с помощью функции CHR(), мы построим символьный треугольник.

DECLARE
l_var varchar2(2000);
BEGIN
  l_var:=chr(65);
  DBMS_OUTPUT.PUT_LINE(l_var);  
  FOR cnt in 1..10 LOOP
	l_var:=  l_var||' '|| CHR(65+cnt);
	DBMS_OUTPUT.PUT_LINE(l_var);
  END LOOP;  
END;
--Результат:
A
A B
A B C
A B C D
A B C D E
A B C D E F
A B C D E F G
A B C D E F G H
A B C D E F G H I
A B C D E F G H I J
A B C D E F G H I J K

DECLARE

l_var varchar2(2000);

BEGIN

l_var:=chr(65);

DBMS_OUTPUT.PUT_LINE(l_var);

FOR cnt in 1..10 LOOP

l_var:= l_var||' '|| CHR(65+cnt);

DBMS_OUTPUT.PUT_LINE(l_var);

END LOOP;

END;

--Результат:

A B

A B C

A B C D

A B C D E

A B C D E F

A B C D E F G

A B C D E F G H

A B C D E F G H I

A B C D E F G H I J

A B C D E F G H I J K