SQL/PSM

Материал из Национальной библиотеки им. Н. Э. Баумана
Последнее изменение этой страницы: 15:05, 30 января 2019.
SQL/PSM
Парадигма Мультипарадигмальный язык программирования
Первый   появившийся 1996
Стабильная версия SQL:2016
OS многоплатформенное
Главная реализация
MySQL
IBM's SQL PL
Mimer SQL
Под влиянием
Ada

SQL/PSM(Persistent Stored Modules) — стандарт, разработанный Американским национальным институтом стандартов (ANSI) в качестве расширения SQL. Впервые был принят в 1996. Стандарт главным образом определяет расширение с процедурным языком: разрешает объявление переменных, управление логикой исполнения, циклы и условия[Источник 1].

Расширение SQL/PSM закреплено стандартом ISO/IEC 9075-4:2003.

История

Уже в начале девяностых годов XX века было очевидно, что в стандарте ANSI SQL не хватает средств для создания хранимых процедур. Для этой цели коммерческие базы данных начали внедрять свои собственные фирменные языки[Источник 2]. Наиболее популярными из них были PL / SQL (Oracle 1992), T-SQL (Sybase и Microsoft, 1995) и SPL (Informix, 1996).

С 1990 года группа разработчиков во главе с Джимом Мелтоном, начала работать над этой проблемой. В 1998 году проект нового стандарта стал частью SQL3 – под названием SQL/PSM. Однако, в то время у большинства крупных компаний были свои языки, несовместимые со стандартом, и они не смогли отказаться от них в пользу PSM. Стандарт был реализован только в тех РСУБД, в которых не было поддержки хранимых процедур до 1998 года.

После 2005 года стандарт SQL / PSM стал более популярным, когда он был реализован в Advantage Database Server (Sybase iAnywhere, 2005), MySQL (2005) и PostgreSQL (2007).

Основные возможности SQL/PSM

PSM позволяет нам хранить процедуры как элементы схемы базы данных. PSM-смесь обычных операторов (if, while и т. д.) и SQL. Это позволяет нам делать то, что мы не можем сделать только в SQL.[Источник 3]

Процедура и функция

Создание процедуры:

CREATE PROCEDURE <name> (
          <parameter list> )
<optional local declarations>
<body>;

Альтернатива функции:

CREATE FUNCTION <name> (
       <parameter list> ) RETURNS <type>

В отличие от обычных пар имен типа в таких языках, как C, PSM использует тройку режим-имя-тип, где может быть режим:

  • IN = процедура использует значение, не изменяет значение.
  • OUT = изменение процедуры, не используется.
  • INOUT = оба.

Пример:

CREATE PROCEDURE JoeMenu (
        IN b CHAR(20),
        IN p REAL
)
INSERT INTO Sells
VALUES(Joe’’s Bar, b, p);

Процедуры вызова

Используется оператор SQL / PSM CALL с именем нужной процедуры и аргументов.

CALL JoeMenu ('Moosedrool', 5,00);

Функции используются в выражениях SQL, где значение возвращаемого типа является соответствующим.

Типы операторов PSM

Стандарт поддерживает следующие типы операторов:

RETURN <выражение>
устанавливает возвращаемое значение функции. (В отличие от C и т. д., RETURN не прекращает выполнение функции).

DECLARE <имя> <тип>, используемый для объявления локальных переменных. BEGIN , , END> для групп операторов. Операторы присваивания: значение <переменной> = <выражение>

SET b = Bud;

Операторы меток.

Оператор IF

Простейшая форма:

 IF <условие> THEN <оператор (ы)> END IF;

Добавление ELSE <оператор (ы)> при желании, как IF...THEN...ELSE...END IF; Дополнительный случай ELSEIF <оператор (ы)>: IF...THEN...ELSEIF...ELSEIF...ELSE...END IF; Пример:

CREATE FUNCTION Rate (IN b CHAR(20) )
      RETURNS CHAR(10)
      DECLARE cust INTEGER;
BEGIN
      SET cust = (SELECT COUNT(*) FROM Frequents
                     WHERE bar = b);
       IF cust < 100 THEN RETURN unpopular
       ELSEIF cust < 200 THEN RETURN average
       ELSE RETURN popular
       END IF;
END;

Оператор цикла LOOP

Основная форма: LOOP <операторы> END LOOP; Выход из цикла: LEAVE <имя цикла>; <имя цикла> связано с циклом, добавляя имя и двоеточие к ключевому слову LOOP. Пример:

loop1: LOOP
    ...
    LEAVE loop1;
    ...
END LOOP;

Другие операторы цикла

WHILE <условие>                         
   DO <оператор>                     
END WHILE;
  
FOR <индекс> IN [REVERSE] .. DO <операторы> END FOR
REPEAT <оператор>                        
   UNTIL <условие>                    
END REPEAT;

Запросы

Общие запросы SELECT-FROM-WHERE в PSM не разрешены. Существует три способа получить результат запроса:

1.Запросы, производящее одно значение, может быть выражением в присваивании.

SET p = (SELECT price FROM Sells
  WHERE bar = Joe’’s Bar AND
         beer = Bud)

2.Одна строка SELECT ...INTO.

SELECT price INTO p FROM Sells
WHERE bar = Joe’’s Bar AND
       beer = Bud;

3.Cursors.

Cursors

Курсор - это, по существу, кортеж-переменная, которая варьируется по всем кортежам в результате некоторого запроса. Объявить курсор c с помощью:

DECLARE c CURSOR FOR <query>;

Чтобы использовать курсор c, мы должны выдать команду:

OPEN c;

Когда закончите с c, выполните команду:

CLOSE c;

Извлечение кортежей из Cursor

Чтобы получить следующий кортеж из курсора c, введите команду:

FETCH FROM c INTO x1,x2,...,xn;

x1,x2,...,xn - это список переменных, по одному для каждого компонента кортежей, на который указывает c. c автоматически перемещается в следующий кортеж.

Breaking Cursor Loops

Обычный способ использования курсора - создать цикл с помощью инструкции FETCH и сделать что-то с каждым выбранным кортежем. Трудность в том, как мы выходим из цикла, когда курсор не имеет больше кортежей. Каждая операция SQL возвращает статус, который представляет собой 5-значное число. 00000 = «Все в порядке» и 02000 = «Не удалось найти кортеж».

В PSM мы можем получить значение статуса в переменной, называемой SQLSTATE. Мы можем объявить условие, которое является логической переменной, которая истинна тогда и только тогда, когда SQLSTATE имеет определенное значение. Например, Мы можем объявить условие: NotFound для представления 02000:

DECLARE NotFound CONDITION FOR SQLSTATE '02000';

Структура Cursor Loops:

cursorLoop: LOOP
   ...
   FETCH c INTO ... ;
   IF NotFound THEN LEAVE cursorLoop;
   END IF;
   ...
END LOOP;

Пример

Давайте напишем процедуру, которая исследует Sells (bar, beer, price), и повысит на 1 доллар цену на все пиво в баре Джо, которые составляют менее 3 долларов США:

CREATE PROCEDURE JoeGouge( )
 DECLARE theBeer CHAR(20);
 DECLARE thePrice REAL;
 DECLARE NotFound CONDITION FOR
     SQLSTATE 02000;
 DECLARE c CURSOR FOR
    (SELECT beer, price FROM Sells
     WHERE bar = Joe’’s Bar);
BEGIN
  OPEN c;
  menuLoop: LOOP
     FETCH c INTO theBeer, thePrice;
     IF NotFound THEN LEAVE menuLoop END IF;
     IF thePrice < 3.00 THEN
        UPDATE Sells SET price = thePrice + 1.00
        WHERE bar = Joe’’s Bar AND beer = theBeer;
     END IF;
  END LOOP;
  CLOSE c;
END;

Поддерживаемые платформы

SQL/PSM поддерживается на следующих платформах:

См. также

Открытый исходный код:

Частный:


Источники

  1. Введение в SQL/PSM — объявление переменных, операторы циклов // Образовательный блог — всё для учебы [2009-2019]. URL: https://all4study.ru/sql/vvedenie-v-sqlpsm-obyavlenie-peremennyx-operatory-ciklov-i-t-d.html (дата обращения 30.01.2019)
  2. SQL/PSM Manual // Чешская Postgres Wiki [2017-2019]. URL: https://postgres.cz/wiki/SQL/PSM_Manual (дата обращения 04.01.2018)
  3. Основы SQL/PSM // Uppsala universitet. [2017-2019]. URL:http://user.it.uu.se/~torer/kurser/dbt/psm.pdf (дата обращения 04.01.2018)

Ссылки