Основы языка PL/SQL

Введение в программирование на PL/SQL для OracleХотя язык SQL и является легким в изучении и обладает массой мощных функциональных возможностей, он не позволяет создавать такие процедурные конструкции, которые возможны в языках третьего поколения вроде C. Язык PL/SQL является собственным расширением языка SQL от Oracle и предлагает функциональность серьезного языка программирования. Одно из главных его преимуществ состоит в том, что он позволяет использовать в базе данных такие программные единицы, как процедуры и пакеты, и тем самым увеличивать возможность повторного использования кода и его производительность.



Базовый блок PL/SQL

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

  • DECLARE. Этот оператор является необязательным и представляет собой то место, в котором при желании объявляются переменные и курсоры программы.
  • BEGIN. Этот оператор является обязательным и указывает, что далее будут идти операторы SQL и PL/SQL, т.е. обозначает начало блока кода PL/SQL.
  • EXCEPTION. Этот оператор является необязательным и описывает методы обработки ошибок.
  • END. Этот оператор является обязательными и обозначает конец блока кода PL/SQL.

Ниже приведен пример простого блока кода PL/SQL:


SQL> 
DECLARE isbn NUMBER(9)
BEGIN
   isbn := 123456789;
   insert into book values (isbn, 'databases', 59.99);
   COMMIT;
END;
SQL>

 

Объявление переменных в PL/SQL

В операторе DECLARE можно объявлять как переменные, так и константы. Прежде чем использовать какую-либо переменную ее нужно обязательно объявить. Переменная в PL/SQL может представлять собой как переменную встроенного типа, такого как DATE, NUMBER, VARCHAR2 или CHAR, так и составного вроде VARRAY. Помимо этого, в PL/SQL еще применяются такие типы данных, как BINARY_INTEGER и BOOLEAN.

Ниже приведены некоторые типичные примеры объявления переменной в PL/SQL:

hired_date DATE;
emp_name VARCHAR2(30);

Помимо переменных также можно объявлять и константы, как показано в следующем примере:

tax_rate constant number := 0.08; 

Еще можно использовать атрибут %TYPE и с его помощью указывать при объявлении переменной, что ее тип данных должен совпадать с типом данных определенного столбца таблицы: 

emp_num employee.emp_id%TYPE;

Посредством атрибута %ROWTYPE можно указывать, что тип данных записи (строки) должен совпадать с типом данных определенной таблицы базы данных. Например, в следующем коде указано, что запись DeptRecord должна содержать все те же столбцы, что и таблица department, а типы данных и длина этих столбцов в ней должны выглядеть абсолютно идентично:

declare
v_DeptRecord department%ROWTYPE;

 

Написание исполняемых операторов PL/SQL

После оператора BEGIN можно начинать вводить все свои желаемые SQL-операторы. Выглядеть эти операторы должны точно так же, как обычные операторы SQL. При использовании операторов SELECT и INSERT в PL/SQL, правда, необходимо помнить об особенностях, о которых более подробно речь пойдет в следующих разделах.

 

Использование оператора SELECT в PL/SQL

При использовании оператора SELECT в PL/SQL нужно сохранять извлекаемые значения в переменных, как показано ниже:


DECLARE
   name VARCHAR2(30);
BEGIN
   SELECT employee_name INTO name FROM employees WHERE emp_id=99999;
END;
/

 

Использование DML-операторов в PL/SQL

Любые операторы INSERT, DELETE или UPDATE работают в PL/SQL точно так же, как в обычном SQL. Однако в PL/SQL после каждого из них можно также применять оператор COMMIT, как показано ниже: 


BEGIN
   DELETE FROM employee WHERE emp_id = 99999;
   COMMIT;
END;
/

 

Обработка ошибок

В PL/SQL любая ошибка или предупреждение называется исключением (exception). В PL/SQL есть кое-какие определенные внутренне ошибки, но также допускается определять и свои собственные. При возникновении любой ошибки инициируется исключение, и управление переходит в отвечающий за обработку исключений раздел программы PL/SQL. В случае определения своих собственных ошибочных ситуаций необходимо обеспечивать инициирование исключений за счет применения специального оператора RAISE.

Ниже приведен пример использования оператора RAISE для обработки исключений: 


DECLARE
   acct_type INTEGER := 7;
BEGIN
   IF acct_type NOT IN (1, 2, 3) THEN
      RAISE INVALID_NUMBER; -- raise predefined exception
   END IF;
   EXCEPTION
      WHEN INVALID_NUMBER THEN
   ROLLBACK;
END;
/

 

Управляющие структуры в PL/SQL

В PL/SQL предлагается несколько видов управляющих структур (control structures), которые позволяют обеспечивать итерацию кода или условное выполнение определенных операторов. Все они кратко описаны в последующих разделах моего блога.

 

Условное управление

Главной разновидностью условной управляющей структуры в PL/SQL является оператор IF, который обеспечивает условное выполнение операторов. Он может применяться в одной из трех следующих форм: IF-THEN, IF-THEN-ELSE и IF-THEN-ELSEIF. Ниже приведен пример простого оператора IF-THEN-ELSEIF


BEGIN
   . . .
   IF total_sales > 100000 THEN
      bonus := 5000;
   ELSEIF total_sales > 35000 THEN
      bonus := 500;
   ELSE
      bonus := 0;
   END IF;
   INSERT INTO new_payroll VALUES (emp_id, bonus . . .);
END;
/

 

Конструкции циклов в PL/SQL

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

 

Простой цикл

Конструкция простого цикла подразумевает помещение набора SQL-операторов между ключевыми словами LOOP и END LOOP. Оператор EXIT завершает цикл. Конструкция простого цикла применяется тогда, когда точно неизвестно, сколько раз должен выполняться цикл. В случае ее применения решение о том, когда цикл должен завершаться, принимается на основании содержащейся между операторами LOOP и END LOOP логики.

В следующем примере цикл будет выполняться до тех пор, пока значение quality_grade не достигнет 6


LOOP
   . . .
   if quality_grade > 5 then
      . . .
      EXIT;
   end if;
END LOOP;

Еще один простой вид цикла позволяет выполнять конструкция LOOP...EXIT...WHEN, в которой длительность цикла регулируется оператором WHEN. Внутри WHEN указывается условие, и когда это условие становится истинным, цикл завершается. Ниже показан простой пример:


DECLARE
   count_num NUMBER(6);
BEGIN
   count_num := 1;
   LOOP
      dbms_output.put_line(' This is the current count '|| count_num);
      count_num := count_num + 1;
      Exit when count_num > 100;
   END LOOP;
END;

 

Цикл WHILE

Цикл WHILE указывает, что определенный оператор должен выполняться до тех пор, пока определенное условие остается истинным. Обратите внимание на то, что условие вычисляется за пределами цикла, и вычисляется оно всякий раз, когда выполняются операторы, указанные между операторами LOOP и END LOOP. Когда условие перестает быть истинным, происходит выход из цикла. Ниже приведен пример цикла WHILE


WHILE total <= 25000
LOOP
   . . .
   SELECT sal INTO salary FROM emp WHERE . . .
   total := total + salary;
END LOOP;

 

Цикл FOR

Цикл FOR применяется тогда, когда требуется, чтобы оператор выполнялся определенное количество раз. Он имитирует классический цикл do, который существует в большинстве языков программирования. Ниже приведен пример цикла FOR


BEGIN
   FOR count_num IN 1..100
      LOOP
         dbms_output.put_line('The current count is : '|| count_num);
      END LOOP;
END;

 

Записи в PL/SQL

Записи (records) в PL/SQL позволяют воспринимать взаимосвязанные данные как одно целое. Они могут содержать поля, каждое из которых может представлять отдельный элемент. Можно использовать атрибут ROW%TYPE и с его помощью объявлять записью столбцы определенной таблицы, что подразумевает применение таблицы в качестве шаблона курсора, а можно создавать и свои собственные записи. Ниже приведен простой пример записи: 


DECLARE
   TYPE MeetingTyp IS RECORD (
      date_held DATE,
      location VARCHAR2(20),
      purpose VARCHAR2(50));

Для ссылки на отдельное поле внутри записи применяется точечное обозначение, как показано ниже: 

MeetingTyp.location

 

Использование курсоров

Курсором (cursor) в Oracle называется указатель на область в памяти, в которой содержится результирующий набор SQL-запроса, позволяющий индивидуально обрабатывать содержащиеся в результирующем наборе строки. Курсоры, которые используются Oracle при выполнении DML-операторов, называются неявными, а курсоры, которые создают и используют разработчики приложений — явными.

 

Неявные курсоры

Неявные курсоры автоматически применяются Oracle всякий раз, когда в коде PL/SQL используется оператор SELECT. Они могут использоваться лишь в тех операторах, которые возвращают одну строку. В случае если SQL-оператор возвращает более одной строки, будет выдаваться сообщение об ошибке.

В приведенном ниже блоке кода PL/SQL оператор SELECT, например, предусматривает применение неявного курсора: 


DECLARE
   emp_name varchar2(40);
   salary float;
BEGIN
   SELECT emp_name, salary FROM employees
   WHERE employee_id=9999;
   dbms_output.put_line('employee_name : '||emp_name||'
      salary :'||salary);
END;
/

 

Явные курсоры

Явные курсоры создаются разработчиком приложения и облегчают операции с набором строк, которые могут обрабатываться друг за другом. Они применяются всегда, когда известно, что SQL-оператор будет возвращать более одной строки. Обратите внимание, что явный курсор необходимо всегда объявлять в начале блока PL/SQL внутри раздела DECLARE, в отличие от неявного курсора, на который никогда не нужно ссылаться в коде.

После объявления явного курсора он будет проходить через следующие этапы обработки.

  1. Конструкция OPEN будет определять строки, которые находятся в курсоре, и делать их доступными для программы PL/SQL.
  2. Команда FETCH будет извлекать данные из курсора в указанную переменную.
  3. По завершении процесса обработки курсор должен всегда закрываться явным образом.

В листинге А.4 показан пример создания курсора и затем его использования внутри цикла.


 


DECLARE
/* Курсор select_emp объявляется явно */
   CURSOR select_emp IS
      select emp_id, city
      from employees
      where city = 'DALLAS';
      v_empno employees.emp_id%TYPE;
      v_empcity employees.city%TYPE;
BEGIN
   /* Курсор select_emp открывается */
   Open select _emp;
   LOOP
   /* Данные курсора select_emp извлекаются в переменную v_empno */
      FETCH select_emp into v_empno;
      EXIT WHEN select_emp%NOTFOUND;
      dbms_output.put_line(v_empno|| ','||v_empcity);
   END LOOP;
   /* Курсор select_emp закрывается */
   Close select_emp;
END;
/

Атрибуты курсоров

В примере, приведенном в листинге А.4, для указания того, когда цикл должен завершаться, используется специальный атрибут курсора %NOTFOUND. Атрибуты курсоров очень полезны при работе с явными курсорами. Наиболее важные из них перечислены ниже.

  • %ISOPEN. Булевский атрибут, который после завершения выполнения SQL-оператора возвращает false. До тех пор, пока курсор остается открытым, он возвращает true.
  • %FOUND. Булевский атрибут, который выполняет проверку на предмет наличия подходящих для SQL-оператора строк, т.е. остались ли у курсора еще какие-то строки для извлечения.
  • %NOTFOUND. Булевский атрибут, который сообщает о том, что не удалось обнаружить ни одной подходящей для SQL-оператора строки, т.е. у курсора больше не осталось никаких строк для извлечения.
  • %ROWCOUNT. Атрибут, который возвращает информацию о том, сколько курсору удалось извлечь строк на текущий момент.

 

Курсорный цикл FOR

Обычно при использовании явных курсоров требуется открывать курсор, извлекать данные и по завершении закрывать курсор. Курсорный цикл FOR позволяет выполнять эти процедуры по открытию, извлечению и закрытию автоматически, чем очень сильно упрощает дело. В листинге А.5 показан пример применения конструкции курсорного цикла FOR.


 


DECLARE
   CURSOR emp_cursor IS
      SELECT emp_id, emp_name, salary
      FROM employees;
      v_emp_info employees%RowType;
Begin
   FOR emp_info IN emp_cursor
   LOOP
      dbms_output.put_line ('Employee id : '||emp_id||'Employee
         name : '|| emp_name||'Employee salary :'||salary);
   END LOOP;
END;
/

 

Курсорные переменные

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

Сначала определяется тип REF CURSOR, как показано ниже: 


DECLARE
   TYPE EmpCurTyp IS REF CURSOR RETURN dept%ROWTYPE;

Затем объявляются сами курсорные переменные типа EmpCurType в анонимном блоке кода PL/SQL либо в процедуре (или функции):


DECLARE
   TYPE EmpRecTyp IS RECORD (
      Emp_id NUMBER(9),
      emp_name VARCHAR2(3O),
      sal NUMBER(7,2));
   TYPE EmpCurTyp IS REF CURSOR RETURN EmpRecTyp;
      emp_cv EmpCurTyp; -- объявление курсорной переменной 

 

Процедуры, функции и пакеты

Процедуры в PL/SQL могут применяться для выполнения различных DML-операций. Ниже приведен пример простой процедуры Oracle:


create or replace procedure new_employee (emp_id number,
   last_name varchar(2), first_name varchar(2))
is
begin
   insert into employees values (emp_id, last_name, first_name);
end new_employee;
/ 

В отличие от процедур, функции в PL/SQL возвращают значение, как показано в следующем примере:


CREATE OR REPLACE FUNCTION sal_ok (salary REAL, title VARCHAR2) RETURN BOOLEAN
IS
   min_sal REAL;
   max_sal REAL;
BEGIN
   SELECT losal, hisal INTO min_sal, max_sal FROM sals
   WHERE job = title;
   RETURN (salary >= min_sal) AND (salary <= max_sal);
END sal_ok; 

Пакеты (packages) в Oracle представляют собой объекты, которые обычно состоят из нескольких взаимосвязанных процедур и функций и, как правило, применяются для выполнения какой-нибудь функции приложения путем вызова всех находящихся внутри пакета взаимосвязанных процедур и функций. Пакеты являются чрезвычайно мощным средством, поскольку могут содержать большие объемы функционального кода и многократно выполняться несколькими пользователями.

Каждый пакет обычно состоит из двух частей: спецификации и тела. В спецификации пакета объявляются все входящие в его состав переменные, курсоры и подпрограммы (процедуры и функции), а в теле пакета содержится фактический код этих курсоров и подпрограмм.

В листинге А.6 приведен пример простого пакета Oracle.


 

/* Сначала идет спецификация пакета /*
create or replace package emp_pkg as
   type list is varray (100) of number (5);
   procedure new_employee (emp_id number, last_name
      varchar2, first_name varchar2);
   procedure salary_raise (emp_id number, raise number);
end emp_pkg;
/
/* Далее следует тело пакета */
create or replace package body emp_pkg as
   procedure new_employee (emp_id number,
      last_name varchar(2), first_name varchar(2) is
   begin
      insert into employees values (emp_id, last_name, first_name);
   end new_employee;
   procedure salary_raise (emp_num number, raise_pct real) is
   begin
      update employees set salary = salary * raise_pct
      where emp_id = emp_num;
   end salary_raise;
end emp_pkg;
/

При желании использовать пакет emp_pkg для награждения какого-то сотрудника надбавкой к зарплате, все, что потребуется сделать — выполнить следующую команду: 


SQL> EXECUTE emp_pkg.salary_raise(99999, 0.15);

 

Вас заинтересует / Intresting for you:

Управление приложениями PL/SQL...
Управление приложениями PL/SQL... 1382 просмотров Rasen Fasenger Mon, 22 Oct 2018, 04:44:08
Встроенные методы коллекций PL...
Встроенные методы коллекций PL... 1944 просмотров sepia Sun, 08 Jul 2018, 07:33:47
Тип данных RAW в PL/SQL
Тип данных RAW в PL/SQL 1544 просмотров Doctor Thu, 12 Jul 2018, 08:41:33
Символьные функции и аргументы...
Символьные функции и аргументы... 2976 просмотров Анатолий Wed, 23 May 2018, 18:54:01


iVoron аватар
iVoron ответил в теме #9137 23 авг 2018 14:58

ildergun пишет: Да, для novice-программеров очень полезный материал! Хорошо изложили основы языка.


Спасибо, друзья!
ildergun аватар
ildergun ответил в теме #9081 09 июнь 2018 11:55
Да, для novice-программеров очень полезный материал! Хорошо изложили основы языка.
Oracle_Admin аватар
Oracle_Admin ответил в теме #9039 13 мая 2018 04:56
Хорошая статья. мое Вам "Да"!
VaaPa аватар
VaaPa ответил в теме #9034 07 мая 2018 14:26
Добротная статья. Будет весьма полезна для начинающих программистов PL/SQL. Да и опытным освежить знания не помешает)))
Условные операторы, циклы, курсоры языка описаны изумительно. Зачетик Вам!

Comments on Основы языка PL/SQL

Будьте первым прокомментировавшим
Пожалуйста, авторизуйтесь, для комментирования