SQL — Урок 6. Динамический SQL (DSQL)

Базы данных SQL — Урок 6. Динамический SQL (DSQL)

Помечено: ,

  • В этой теме 0 ответов, 1 участник, последнее обновление 1 год, 8 месяцев назад сделано Васильев Владимир Сергеевич.
Просмотр 0 веток ответов
  • Автор
    Сообщения
    • #5196
      @admin

      Динамический SQL это расширение, позволяющее писать подпрограммы на процедурном языке с использованием команд SQL. PostgreSQL поддерживает несколько процедурных языков как PL/Tcl и PL/Perl. Здесь же рассматривается PL/pgSQL, который очень близок по синтаксису и своим возможностям с Oracle PL/SQL. Процедурный язык в MySQL более ограничен и находится на стадии разработки.

      Для использования языка PL/pgSQL, его надо установить в БД командой
      CREATE LANGUAGE plpgsql;

      Процедурные операторы

      блоковая структура кода

      Процедурный язык имеет блоковую структуру.
      Группировка команд производится операторами BEGIN END.
      Вложенные блоки разрешаются. Oracle позволяет независимый блок, у остальных внешний блок
      должен быть в составе функции
      или т.п. Оператор DECLARE позволяет объявить локальные переменные блока.
      По умолчанию переменные инициализируются в null.
      Для удобства в Oracle и PostgreSQL можно использовать два псевдотипа type и rowtype.
      Первый определяет тип переменной такой же как у столбца указанной таблицы.
      Второй определяет тип переменной как структуру соответствующую записи указанной таблицы.

      -- Oracle, PostgreSQL
      DECLARE
        i integer:=3;
        t tblname.id%type;
        trow tblname%rowtype;
      BEGIN
        ...
      END;
      
      -- MySQL
      BEGIN
          -- локальная переменная
          DECLARE o integer DEFAULT 3;
          DECLARE i double precision DEFAULT 3.14;    
          ...
      END;
      

      присвоение

      Для присвоения значения переменной используется операция :=.
      В MySQL присвоение должно происходить в операторе SET (по стандарту).

      -- Oracle, PostgreSQL
      i:=23;
      
      -- MySQL
      SET i:=23;
      SET i=23;
      

      условный оператор

      Условный оператор IF позволяет выполнить ряд команд, если указанное условие истинно.
      Внутри него может быть сколько угодно не обязательных алтернативных условий ELSE IF,
      и одного необязательного слова ELSE,
      определяющего что делать, если ни одно условие не выполнено.

      -- Oracle
      IF i >3 THEN
        delete from tblname where id>40;
      ELSIF i=-4 then -- альтернативное условие
        delete from tblname where id=4;
      ELSE
        delete from tblname ;
      END if;
      
      -- MySQL
      IF i >3 THEN
        delete from tblname where id>40;
      ELSEIF i=-4 then -- альтернативное условие
        delete from tblname where id=4;
      ELSE
        delete from tblname ;
      END IF;
      
      -- в PostgreSQL ELSEIF синоним ELSIF,
      -- так что оба приведенных выше примера верны
      

      оператор выбора

      В первом синтаксисе оператор выбора CASE позволяет выполнить тот или иной код
      в зависимости от значения указанного выражения. Во втором синтаксисе конструкция CASE аналогична условному оператору. Обратите внимание, что в отличие от CASE, используемого в обычных командах SQL, этот оператор завершается словами END CASE, а не просто END. Данный оператор не поддерживается в PostgreSQL.

      -- Oracle
      CASE i
      WHEN 2 THEN i:=4;
      WHEN 5 THEN i:=0;
      ELSE i:=5;
      END CASE;
      
      -- MySQL
      CASE i
      WHEN 2 THEN
        set i:=4;
      WHEN 5 THEN
        set i:=0;
      ELSE
        set i:=5;
      END CASE;
      

      метки

      Метки полезны, когда есть вложенные циклы и нужно выйти из внешнего внутри вложенного. Фактически они именует блок или цикл. А в MySQL они обязательны для операторов выхода из цикла и продолжения итерации.

      -- Oracle, PostgreSQL
      <<l1>>
      
      -- MySQL 
      l1: 
      

      безусловный цикл

      Безусловный цикл определяется оператором LOOP. Другими словами условие выхода указывается явно внутри тела цикла.

      -- Oracle, PostgreSQL
      -- оператор выхода EXIT
      -- без метки
      LOOP
        i:=i+1;
        EXIT WHEN i>11;
      END LOOP;
      
      -- Oracle, PostgreSQL
      -- с меткой
      <<l1>>LOOP
        i:=i+1;
        EXIT l1 WHEN i>11;
      END LOOP l1;
      
      -- MySQL
      -- оператор выхода LEAVE
      l1: LOOP
        set i:=i+1;
        if i>11 then
          LEAVE l1;
        end if;
      END LOOP l1;
      

      цикл с предусловием

      Цикл с предусловием определяется оператором WHILE.

      -- Oracle, PostgreSQL
      WHILE i < 10 LOOP
        i:=i+1;
      END LOOP;
      
      -- MySQL
      WHILE i < 10 DO
        set i:=i+1;
      END WHILE;
      

      цикл по счетчику

      MySQL не поддерживает цикл по счетчику. Счетчик в виде переменной объявлять не надо. По умолчанию счетчик изменяется на 1, если присутствует ключевое слово REVERSE на -1.

      -- Oracle, PostgreSQL
      FOR j IN 1..10 LOOP
         i:=i+1;
      END LOOP;
      
      -- Oracle
      FOR j IN REVERSE 1..10 LOOP
         i:=i+1;
      END LOOP;
      
      -- PostgreSQL
      FOR j IN REVERSE 10..1 LOOP
         i:=i+1;
      END LOOP;
      

      цикл по элементам

      Для обработки записей запроса добавлен специальный цикл. В PostgreSQL переменная, в которой будет храниться текущая запись, должна быть объявлена явно с типом RECORD. Также вместо одной переменной можно указать список переменных, соответствующих столбцам запроса.

      -- Oracle
      FOR cur IN
        (select * from tblname )
      LOOP
        i:=i+cur.id;
      END LOOP;
      
      -- PostgreSQL 
      declare
         i integer := 1;
         cur RECORD;
      begin
      
      FOR cur IN
        (select * from tblname )
      LOOP
        i:=i+cur.id;
      END LOOP;
      ...
      

      операторы выхода/продолжения итерации

      Для выхода из цикла и перехода на следующую итерацию служат операторы EXIT и CONTINUE в Oracle и PostgreSQL. В MySQL это операторы LEAVE и ITERATE.

      -- Oracle, PostgreSQL
      <<l1>>for m in 1..20 loop
        for n in 1..30 loop
      
           if n=15 then 
             CONTINUE; 
           end if;
           
           i:=n+m;
           ...
      
           -- выход из обоих циклов 
           EXIT l1 WHEN i>50; 
        end loop;
      end loop l1;  
      
      -- MySQL
      l1: loop
        set i:=i+1;
      
        if i=5 then
          ITERATE l1;
        end if;
         ...
       
        if i>11 then
          LEAVE l1;
        end if;
      end loop l1;
      

      Выборка в переменные

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

      -- Oracle, PostgreSQL
      -- переменная r типа tblname%rowtype;
      SELECT * INTO r FROM tblname WHERE id=1;
      
      -- PostgreSQL
      -- переменная r типа record
      SELECT * INTO r FROM tblname WHERE id=1;
      
      -- Oracle, PostgreSQL, MySQL
      -- типы и колличество переменных v соответствует
      -- типам и колличеству столбцов в выборке
      SELECT * INTO v1, v2,... FROM tblname WHERE id=1;
      

      Хранимые процедуры

      Процедуры и функции основные места, где разрешено использования процедурного расширения, что позволяет решать более сложные задачи. Процедуры не возвращают значений как это делают функции, однако результат их работы можно сохранить в аргументах. Для этого им надо задать атрибут OUT, по умолчанию же аргументы обычно имеют атрибут IN.

      Некоторые СУБД позволяют перегрузку, т.е. создание нескольких процедур или функций с одним именем, но с разным числом или типами аргументов.

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

      Для примера создадим процедуру вставки пяти записей в следующую таблицу:

      create table tblname (
        id integer, 
        i integer,
      
        constraint pk_tblname primary key(id) 
      );
      

      Oracle

      В Oracle можно задать аргументам значения по умолчанию.
      Конструкция OR REPLACE позволяет пересоздать процедуру, если она существует без вывода сообщения об ошибке. Для уничтожения процедуры служит команда DROP PROCEDURE. Перегрузка разрешена только внутри пакетов.

      -- создание процедуры
      CREATE OR REPLACE PROCEDURE insert5(n IN OUT integer) 
      AS
         o integer :=n; -- локальная переменная
      BEGIN
         delete from tblname where id < 6;
         insert into tblname values(1,o);
         insert into tblname values(2,o);
         insert into tblname values(3,o);
         insert into tblname values(4,o);
         insert into tblname values(5,o);
         commit;
         n:=5;
      END insert5;
      
      -- уничтожение процедуры
      drop procedure insert5;
      

      PostgreSQL

      Для создания процедур и функций в PostgreSQL используется одна конструкция. В первом случае мы просто не указываем тип возвращаемого значения. Более того, это запрещено, если выходных параметров более одного. Если выходных аргументов нет, то в качестве возвращаемого типа надо указать void. Также нельзя задавать аргументам значение по умолчанию, но это компенсируется механизмом перегрузки.

      -- создание процедуры
      CREATE OR REPLACE FUNCTION insert5(n INOUT integer) 
      AS $$
      DECLARE 
         o integer :=5; -- локальная переменная
      BEGIN
         delete from tblname where id < 6;
         insert into tblname values(1,o);
         insert into tblname values(2,o);
         insert into tblname values(3,o);
         insert into tblname values(4,o);
         insert into tblname values(5,o);
         commit;
         n:=5;  
      END
      $$ 
      LANGUAGE plpgsql;
      
      -- нет выходных аргументов
      CREATE OR REPLACE FUNCTION pdemo() 
      returns void
      AS $$
      DECLARE 
      r integer;   
      BEGIN
         r:=3;
      END
      $$ 
      LANGUAGE plpgsql;
      
      -- уничтожение процедуры
      DROP FUNCTION insert5(n INOUT integer); 
      

      MySQL

      Ниже приведен пример процедуры для MySQL.

      -- создание процедуры
      DELIMITER $$
      
      DROP PROCEDURE IF EXISTS insert5 $$
      CREATE PROCEDURE insert5 (INOUT n integer)
      BEGIN
         declare o integer default 1;
         set o:=n;
         delete from tblname where id < 6;
         insert into tblname values(1,o);
         insert into tblname values(2,o);
         insert into tblname values(3,o);
         insert into tblname values(4,o);
         insert into tblname values(5,o);
         commit;
         set n:=5;
      END $$
      
      DELIMITER ;
      
      -- уничтожение процедуры
      DROP PROCEDURE insert5;
      

      вызов процедур

      Хотя по стандарту вызов процедур делается командой CALL, в Oracle она разрешена только вне процедурного блока. А внутри блока вызывается обычным образом.
      PostgreSQL вообще не поддерживает эту команду и для вызова процедуры используется команда SELECT.

      CALL procname();
      
      -- Oracle, внутри процедурного блока
      procname();
      
      -- PostgreSQL
      select pdemo();
      

      Хранимые функции

      Практически все сказанное о процедурах относится и к функциям. Для примера создадим функцию inc. Она будет принимать один целочисленный аргумент n и возвращать значение большее n на единицу. А для хранения единицы воспользуемся локальной переменной o.

      Oracle

      CREATE OR REPLACE FUNCTION inc(n integer ) 
      RETURN integer -- тип возвращаемого значения
      AS    
          o integer :=1; 
      BEGIN      
          return one+n;
      END inc;
      
      -- 20 значение по умолчанию
      CREATE OR REPLACE FUNCTION inc(n integer:=20 ) 
      ...
      
      -- пример использования функции
      select inc(24) from dual;
      
      -- уничтожение функции
      DROP FUNCTION inc;
      

      PostgreSQL

      В PostgreSQL если функции не нужно процедурное расширение, то в качестве языка можно использовать SQL. В этом случае возвращаемое значение определяется последней командой select, а к параметрам можно обратится только по их номерам.

      -- функция на plpgsql 
      CREATE OR REPLACE FUNCTION inc(n integer) 
      RETURNS integer 
      AS $$
      DECLARE
          o integer := 1;
      BEGIN
          return o;
      END;
      $$ 
      LANGUAGE plpgsql;
      
      -- функция на SQL
      CREATE OR REPLACE FUNCTION inc2(integer) 
      RETURNS integer 
      AS $$
         
         -- перед этим может быть
         -- любое число SQL команд
         select $1+1;
      $$ 
      LANGUAGE SQL;
      
      -- пример использования функции
      select inc2(24);
      
      -- уничтожение функции
      DROP FUNCTION inc(n integer);
      

      MySQL

      В MySQL для функций есть дополнительные ограничения:

      • аргументы не могут быть выходными;
      • не могут использоваться некоторые команды SQL, например commit;
      • запрещена рекурсия.

      DELIMITER $$
      -- уничтожаем функцию, если она существует
      DROP FUNCTION IF EXISTS inc $$
      
      -- создаем функцию
      CREATE FUNCTION inc (n integer)
      RETURNS integer -- тип возвращаемого значения
      BEGIN      
          declare o integer default 1;
          return n+1; -- результат
      END $$
      
      DELIMITER ;
      
      -- пример использования функции
      select inc(24);
      
      -- уничтожение функции
      DROP FUNCTION inc;
      

      Исключения

      Рассматриваемые здесь СУБД позволяют по-своему обработать исключения — ситуации
      возникновения ошибок. В Oracle и Postgre это достигается добавлением в блок секции EXCEPTION, в котором и происходит обработка исключения.

      Для конкретности рассмотрим исключения, возникающие во время SELECT INTO для следующей таблицы с данными.

      create table tblname (
        id integer, 
        constraint pk_tblname primary key(id) 
      );
      
      insert into tblname values(1);
      insert into tblname values(2);
      insert into tblname values(3);
      insert into tblname values(4);
      insert into tblname values(5);
      commit;
      

      Oracle

      create or replace function F(n in integer) 
      return integer
      as
         o integer :=5; 
      begin
        
        BEGIN
          select id into o 
          from tblname 
          where id=n or id=2*n;
        
        EXCEPTION
          WHEN NO_DATA_FOUND THEN 
            o:=0; 
          WHEN TOO_MANY_ROWS THEN
            o:=999;
        END;
        
      return o;  
      end F;
      

      PostgreSQL

      Ключевое слово strict позволяет генерировать исключения.

      create or replace function F(n in integer) 
      returns integer
      as
      $$
      DECLARE
         o integer :=5; 
      begin
        
        BEGIN    
          select id into STRICT o 
          from tblname 
          where id=n or id=2*n;
        
        EXCEPTION
          WHEN NO_DATA_FOUND THEN 
            o:= 0; 
          WHEN TOO_MANY_ROWS THEN
            o:= 999;    
        END;
        
      return o;  
      end;
      $$
      LANGUAGE 'plpgsql';
      

      MySQL

      В MySQL обработчик исключения объявляется вначале блока при этом указывается продолжать ли выполение после обработки (CONTINUE) или выйти из блока (EXIT). Сама обработка должна занимать только один оператор, обычно это SET или BEGIN END.

      DELIMITER $$
      
      DROP FUNCTION IF EXISTS f $$
      CREATE FUNCTION f (n integer ) RETURNS integer
      BEGIN
      declare o integer default 0;
      
      begin
      DECLARE CONTINUE HANDLER FOR 1329 set o:=0;
      DECLARE CONTINUE HANDLER FOR 1172
          begin
             set o:=999;
             ...
          end;
      
        select id into o
        from tblname
        where id=n or id=2*n;
      end;
      
      return o;
      END $$
      
      DELIMITER ;
      

      Курсоры

      Курсор — объект, позволяющий работать с областью памяти, в которой сохраняются записи
      указанного запроса данных. В стандарте определены три команды управления курсором:

      • OPEN — открывает курсор;
      • FETCH — берет текущую запись из результирующего набора записей. После каждого
        применения FETCH следующая запись становится активной. Структурная переменная или
        список переменных указанных во фрагменте INTO должны соответствовать столбцам записи;
      • CLOSE — закрывает курсор, в большинстве СУБД закрытие курсора делается автоматически в конце блока, в котором курсор объявлен.

      Oracle

      В Oracle для определения успешного выбора записи используются два атрибута курсора:

      • FOUND — истина, если запись найдена;
      • NOTFOUND — истина, если запись не найдена.

      Для сохранения данных всех записей курсора в массивы используется конструкция
      BULK COLLECT. Ниже приведен пример функции подсчитывающий число записей в таблице tblname с четным идентификатором с помощью курсоров.

      -- создание функции
      CREATE OR REPLACE FUNCTION curdemo 
      RETURN integer 
      IS
        -- курсор
        cursor cur is select * from tblname;
        -- можно указать какого типа будет запись курсора
        cursor cur2 return tblname%rowtype 
            is select * from tblname;
        val tblname%rowtype;
        r integer:=0;
      BEGIN
        OPEN cur;
        loop
          FETCH cur INTO val; 
          
          -- если записи нет
          EXIT WHEN cur%NOTFOUND; 
       
          if mod(val.id,2)=0 then
            r:=r+1;
          end if;
        end loop;
        CLOSE cur;
        return(r);
      END curdemo;
      
      -- проверка
      select curdemo() from dual;
      

      PostgreSQL

      В PostgreSQL для определения успешного выбора записи курсора используется специальная
      переменная FOUND. Кроме стандартных возможностей позволяется перемещаться по курсору командой MOVE без выбора данных. Во-вторых, выбор записи и перемещение можно делать не последовательно, а перепрыгивая через записи и в любом направлении. Ниже приведен аналогичный пример для PostgreSQL.

      -- создание функции
      CREATE OR REPLACE FUNCTION curdemo() 
      RETURNS integer 
      AS $$
      DECLARE
          val tblname%rowtype;
          -- курсор
          cur CURSOR IS select * from tblname;
          r integer:=0;
      BEGIN
        OPEN cur;
        loop
           FETCH cur INTO val; 
      
           -- если записи нет
           EXIT WHEN not FOUND ;
      
           if mod(val.id,2)=0 then
             r:=r+1;
           end if;
         end loop;
         CLOSE cur;
         return r;
      END;
      $$ 
      LANGUAGE plpgsql;
      
      -- проверка
      select curdemo();
      

      MySQL

      Ниже приведен аналогичный пример для MySQL.

      -- создание функции
      DELIMITER $$
      DROP FUNCTION IF EXISTS curdemo
      CREATE FUNCTION  curdemo()
      RETURNS integer
      BEGIN
      declare r integer default 0;
      declare val integer default 0;
      declare nodata boolean default false;
      -- курсор
      declare cur CURSOR FOR SELECT id FROM tblname;
      -- обработчик отсутствия следующей записи в курсоре
      declare continue handler for not found set nodata=true;
      
      OPEN cur; -- открываем курсор
      l1:loop
      	FETCH cur INTO val;
      
      	-- если записи нет
      	if nodata then
      	  close cur; -- закрываем курсор
      	  leave l1;  -- выходим из цикла
      	end if;
      	
      	if mod(val,2)=0 then
      	  set r:=r+1;
      	end if;
      end loop l1;
      
      return r;
      END;
      $$
      DELIMITER;
      
      -- проверка
      select curdemo(); 
      

      Триггеры

      Триггер это процедура обработки события связанного с таблицей, которая вызывается автоматически. Ниже приведен список стандартных событий:

      • удаление записи из таблицы;
      • вставка записи в таблицу;
      • изменение записи в таблице.

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

      -- создание таблицы
      create table tblname (
      id integer, 
      str varchar(45),  
      dt date,
      
      constraint pk_tblname primary key (id)
      );
      

      Назначение нашего тригера в подмене null значений поля dt текущей датой перед вставкой новой записи.

      Oracle

      -- создаем тригер
      create or replace trigger tgins_tblname
        before insert on tblname  
        for each row
      declare
      -- место для локальных переменных
      r integer; 
      begin
      
      if :old.dt is null then
          :new.dt := current_date;
      end if;  
        
      end tgins_tblname;
      
      -- проверка
      insert into tblname 
          values(1,'text info',null);
      commit;
      select * from tblname;
      
      -- отключить тригер
      ALTER TRIGER test_trig DISABLE;
      
      -- включить тригер
      ALTER TRIGER test_trig ENABLE;
      
      -- удаление тригера
      DROP TRIGER test_trig;
      

      PostgreSQL

      В PostgreSQL триггер не содержит кода, а только указывает на функцию, которую нужно
      запускать. Причем не любая функция, а возвращающая тип trigger.
      Кроме переменных new и old в триггерных процедурах доступны дополнительные переменные,
      которые тоже могут пригодится.

      -- триггерная функция
      -- в ней доступны переменные new и old
      CREATE FUNCTION tgpins_tblname() RETURNS trigger 
      AS $$
      BEGIN
      
      if new.dt is null then
          new.dt:=current_date;
      end if;
              
      RETURN NEW;
      END;
      $$ 
      LANGUAGE plpgsql;
      
      -- а теперь создаем сам тригер
      CREATE TRIGGER tgins_tblname 
          BEFORE INSERT ON tblname
          FOR EACH ROW 
          EXECUTE PROCEDURE tgpins_tblname();
          
      -- проверка
      insert into tblname 
          values(1,'text info',null);
      commit;
      select * from tblname;   
      
      -- отключить тригер
      ALTER TABLE DISABLE TRIGGER tgins_tblname;
      
      -- включить тригер
      ALTER TABLE ENABLE TRIGGER tgins_tblname;
      
      -- удаление тригера 
      DROP TRIGGER IF EXISTS tgins_tblname ON tblname;
      

      MySQL

      -- создаем тригер
      DELIMITER $
      CREATE TRIGGER tgins_tblname 
        BEFORE INSERT ON tblname
        FOR EACH ROW
      BEGIN
      
      if new.dt is null then
          set new.dt:=current_date();
      end if;
      
      END$
      DELIMITER;
      
      -- проверка
      insert into tblname
          values(1,'text info',null);
      commit;
      select * from tblname;
      
      -- удаление тригера
      DROP TRIGGER IF EXISTS tgins_tblname;
      

      Числа прописью

      В практической жизни для печати различных документов полезна функция преобразования числа в строку, где число указано словами. Ниже приведен пример решения этой задачи на Oracle для чисел меньших миллиона.
      При желании диапазон чисел легко расширить до необходимого. Переписать код для другой СУБД также не представляет никакой сложности.Возможно можно и проще, но привожу, что первое пришло в голову.

      -- таблица сопоставления чисел и строк
      create table numbers
      (
      n integer,
      gender integer, 
      str varchar(20),
      
      CONSTRAINT pk_numbers PRIMARY KEY (n,gender)
      );
      
      /*
      0 - без разницы какой род
      1 - мужской род
      2 - женский род
      3 - средний род
      */
      
      insert into numbers values(0, 0,'ноль');
      insert into numbers values(1, 1,'один');
      insert into numbers values(1, 2,'одна');
      insert into numbers values(1, 3,'одно');
      insert into numbers values(2, 1,'два');
      insert into numbers values(2, 2,'две');
      insert into numbers values(3, 0,'три');
      insert into numbers values(4, 0,'четыре');
      insert into numbers values(5, 0,'пять');
      insert into numbers values(6, 0,'шесть');
      insert into numbers values(7, 0,'семь');
      ...
      insert into numbers values(19, 0,'девятьнадцать');
      
      insert into numbers values(20, 0,'двадцать');
      insert into numbers values(30, 0,'тридцать');
      ...
      insert into numbers values(90, 0,'девяносто');
      
      insert into numbers values(100, 0,'сто');
      insert into numbers values(200, 0,'двести');
      ...
      insert into numbers values(900, 0,'девятьсот');
      
      insert into numbers values(1000, 0,'тысяча');
      insert into numbers values(4000, 0,'тысячи');
      insert into numbers values(5000, 0,'тысяч');
      commit;
      
      -- сохраняет в r число до 100 прописью 
      create or replace procedure n100_inwords
      (
      an integer, 
      ag in integer,
      r out varchar
      ) 
      is
        tmp integer:=0;
        st varchar(255);
      begin
        
        -- числа меньшие 20
        if an < 20 then
      
            select t.str into r
            from numbers t
            where t.n=an and
            (t.gender=ag or
            t.gender=0
            );
                 
        else
      
            tmp:=mod(an,10);
        
            if tmp !=0 then
                -- единицы
                select t.str into st
                from numbers t 
                where t.n=tmp and (t.gender=ag or t.gender=0);
            end if;
        
            -- десятки
            tmp:= an-tmp;
            select t.str into r
            from numbers t 
            where t.n=tmp and (t.gender=ag or t.gender=0);
       
            r:=r||' '||st; 
        end if;  
      end n100_inwords;
      
      -- сохраняет в r число до 1000 прописью 
      create or replace procedure n1000_inwords
      (
      an integer, 
      ag in integer, 
      r out varchar
      ) 
      is
        tmp integer;
        st varchar(255);
      begin
        tmp:= mod(an,100);
        
        if tmp!=0 or (tmp=0 and an=0)   then
          n100_inwords(tmp,ag,st);
        end if;
        
        -- сотни
        tmp:=an-tmp;
        
        if tmp>0 then 
            select t.str into r
            from numbers t 
            where t.n=tmp and 
                (t.gender=ag or t.gender=0);  
           
            r:=r||' '||st;
        else
            r:=st;           
        end if;  
      end n1000_inwords;
      
      
      -- сохраняет в r число до 1000000 прописью 
      create or replace procedure n1000000_inwords
      (
      an in integer, 
      ag in integer, 
      r out varchar
      ) 
      is
        tmp integer;
        tmp1 integer;
        st varchar(255);
      begin
        
        tmp:= mod(an,1000);
        
        if tmp>0 or (tmp=0 and an/1000=0) then
           n1000_inwords(tmp,ag,r);
        else
           r:='';
        end if;
        
        -- тысячи
        tmp:=an-tmp; 
        
        if tmp>0 then 
           tmp1:= mod(tmp,10000);
      
           select t.str into st
           from numbers t
           where
             (t.n=1000 and tmp1=1000) or
             (t.n>1000 and t.n < =4000 
             and tmp1>1000 and tmp1 < =4000) or
             (t.n>=5000 and (tmp1>=5000 or tmp1=0));
        
           r:=st||' '||r;
           tmp:=tmp / 1000;
           n1000_inwords(tmp,2,st);
           r:=st||' '||r;
        end if;   
      end n1000000_inwords;
      
      -- возвращает строку - число до 1000000 прописью 
      create or replace function int_inwords
      (
      n in integer, 
      g in integer
      ) 
      return varchar2 
      is
        r varchar2(255);
      begin
        n1000000_inwords(n,g,r);  
        return r;
      end int_inwords;
      
      -- проверка
      select int_inwords(815334,1)||' руб. '||
             int_inwords(32,2)||' коп.'
             as summa 
      from dual
      

Просмотр 0 веток ответов
  • Для ответа в этой теме необходимо авторизоваться.
×