Информация

Последние записи

Теги


Блоги


Записи из всех блогов с тегом: return


Sum по disitnct полю , в другой колонке

Блог: Oracle SQL
Учебный тренинг. SQL от новичка до профессионала.
Бесплатные авторские видеокурсы SQL, PLSQL, JAVA
-- итак требуется найти сумму по distinct полю на основе идентификатора, который находится в другой колонке
-- есть доп условие нельзя использовать подзапрос, то есть необходимо выполнить задачу за одно обращение


-- подготовим данные для тестового примера
drop table t; -- если надо
create table t as
WITH r AS (
select /*+ materialize */ * from ALL_OBJECTS WHERE ROWNUM < 100 )
select object_id as id,length(r.object_name) as fid,
 (select count(1) from r a where length(r.object_name) = length(a.object_name))  as price   
from r


-- есть вторичный ключ fid от которого зависит значение в поле price
-- необходимо найти сумму по полю price в distinct поля fid одним запросом, то есть без вложенного select

select sum(price) sm from t 
-- это неправильное решение

select sum(distinct price) sm from t 
-- и это неправильное решение price может быть одинаковый для разных fid
-- надо как то так
select sum(price distinct fid) sm from t
-- но так написать нельзя

-- первое решение,
-- работает быстро, но решение не идеально, ключ может быть и не числовой
-- сумма Id + price - сумма id
-- , во избежание неприятностей использовали to_number(rpad(fid, 20)
select sum(distinct to_number(rpad(fid,10,'0')) +price) -  sum(distinct to_number(rpad(fid,10,'0'))) as sm  from t


-- решение второе - используем model
  select r from  t 
   model
   return updated rows   
   dimension by(id)
  measures(0 r, nullif(price, lag(price)over(partition by fid order by id)) lg)
  rules upsert(r[0] = sum(lg)[any])
автор: Myp3_u_K добавлено: 23 май 18 просмотры: 873, комментарии: 0



Конструкция With и Function

Блог: Oracle SQL
Учебный тренинг. SQL от новичка до профессионала.
Бесплатные авторские видеокурсы SQL, PLSQL, JAVA

В SQL диалекте Oracle 12C есть возможность определить функцию или процедуру на языке PL/SQL с помощью оператора WITH, используя обычный SQL


Синаксис
WITH
 PROCEDURE <NAME_PROCEDURE> 
 BEGIN
 ... 
 END;

 FUNCTION <NAME_FUNCTION>
 BEGIN
   ...
 END;
SELECT <NAME_FUNCTION>
FROM <TABLE>;


Примеры
Вывести на экран тип объекта Перевернуть слова означающие типы объектов в ALL_OBJECTS, ограничить выборку 100 строками
WITH
  FUNCTION reversive_fnc(p_name VARCHAR2) RETURN VARCHAR2
  is i NUMBER; v VARCHAR2(50);
  begin
    FOR i IN 1..LENGTH(p_name) LOOP
      v := v || SUBSTR(p_name, LENGTH(p_name)-i+1, 1);
    END LOOP; 
    return v; 
  end;
SELECT DISTINCT reversive_fnc(object_type) as rname, object_type FROM all_objects WHERE rownum < 101;


Добавить к идентификатору объекта заданное кол нулей , преобразовать к числу, ограничить выборку 100 строками
WITH
  FUNCTION incid_fnc(p_id NUMBER, p_count NUMBER) RETURN NUMBER
  is
  begin     
    return TO_NUMBER(rpad(p_id , p_count, '0')); 
  end;
SELECT object_id, incid_fnc(object_id, 10) fn FROM all_objects WHERE rownum < 101
автор: Myp3_u_K добавлено: 02 май 18 просмотры: 1271, комментарии: 1



Динамический SQL в Oracle - легко и понятно (часть третья - BULK, операции с множествами)

Блог: Oracle SQL
Учебный тренинг. SQL от новичка до профессионала.
Бесплатные авторские видеокурсы SQL, PLSQL, JAVA

Использование множеств при работе с динамическим SQL

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

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

Синтаксис
BULK FETCH statement
BULK EXECUTE IMMEDIATE statement
FORALL statement
COLLECT INTO clause
RETURNING INTO clause
%BULK_ROWCOUNT cursor attribute

Синтаксис для динамического связывания с использованием инструкции BULK

Инструкция BULK позволяет Oracle работать переменной в SQL , так , чтобы обрабатывать множества значений. Тип множества может быть любой допустимы в PL / SQL (индекс-таблицы, вложенные таблицы, и массивы переменного размера). Однако элементы должны иметь строго заданный тип данных SQL, CHAR , DATE или NUMBER . Динамические коллекции могут применяться в командах: EXECUTE IMMEDIATE , FETCH и FORALL .

EXECUTE IMMEDIATE и BULK

Эти операторы позволяет вам позволяет связывать заданные переменные или OUT аргументы и передавать их, как параметры, для динамического оператора SQL. Синтаксис имеет следующий вид:
EXECUTE IMMEDIATE dynamic_string
   [[BULK COLLECT] INTO define_variable[, define_variable ...]]
   [USING bind_argument[, bind_argument ...]]
   [{RETURNING | RETURN} 
   BULK COLLECT INTO bind_argument[, bind_argument ...]];

Благодаря инструкции BULK можно возвращать данные в коллекции из динамического SQL.
читать дальше...
автор: Myp3_u_K добавлено: 08 сен 13 просмотры: 12146, комментарии: 0



Использование динамического SQL - легко и понятно (часть первая EXECUTE IMMEDIATE)

Блог: Oracle SQL
Учебный тренинг. SQL от новичка до профессионала.
Бесплатные авторские видеокурсы SQL, PLSQL, JAVA


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

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

Так же динамический SQL незаменим в случаях ,когда требуется назначить или изменить для определённого пользователя права или привилегии, или роли, или для изменения параметров сессии

Ключевое отличие динамического sql от статического , в том, что команда динамического SQL строится непосредственно во время выполнения процедуры или функции PL/SQL.

Таким образом, команда динамического SQL - это , построенная во время выполнения программы строка SQL запроса , так же такая строка может быть использована во время выполнения анонимного PLSQL блока.
читать дальше...
автор: Myp3_u_K добавлено: 06 сен 13 просмотры: 79336, комментарии: 3