Функции в Oracle, возвращающие таблицу

[Kathy]

И это не последнее различие, с которым сталкиваются при переходе из одной СУБД в другую. Главное в этом случае — грамотно использовать возможности конкретной СУБД.

[Stas911]

Капитан, рад что вы с нами! :)

[evmatyunina]

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

Статья — отличная. Все очень прозрачно, понятно и по существу!

[ncix]

сложновато. в Firebird как то понятнее всё:

CREATE PROCEDURE GET_EMPLOYEES(
DEP_ID INTEGER = NULL;
)
RETURNS( --список возвращаемых полей
EMPLOYEE VARCHAR(50);
DEPARTMENT VARCHAR(50);
) AS
BEGIN
FOR SELECT E.empinfo, D.depinfo
FROM T_DEPARTMENTS D
JOIN T_EMPLOYESS E ON E.id_department = D.id
WHERE :DEP_ID IS NULL OR D.ID = :DEP_ID INTO :EMPLOYEE, :DEPARTMENT DO
SUSPEND; -- Это волшебное слово - аналог pipe row
END

SELECT * FROM GET_EMPLOYEES()



[aspanin]

В MSSQL аналогично

[ncix]

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

[aspanin]

Я усматриваю в этом преимущества:
1) укрепляются мышцы пальцев;
2) программисту подконтрольны те механизмы, которые в том же MSSQL являются черным ящиком, например sequences.

[ncix]

кстати в MSSQL так и не запилили sequences? Очень нужны. И UPDATE… RETURNING и INSERT… RETURNING

Привык ко всем этим ништякам на Firebird

[aspanin]

В 2008 не увидел. Не представляю, как без этого можно строить многопользовательские системы, использующие поля с автоинкрементом. Приходится использовать GUID.

[ncix]

там вроде есть какой-то костыль с возвратом id вставленной строки. Впрочем этот костыль почему-то во многих СУБД встречается и считается нормальным решением.

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

[aspanin]

А если между insert и select @@SCOPE_IDENTITY проскочит транзакция с insert от другого пользователя?

[ncix]

я полагаю это в контексте транзакции должно работать. Иначе бессмысленно

[ncix]

это только в ХП доступно? или в обычном запросе можно?

[ncix]

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

[Albertum]

1. Последовательности, наконец, появились. В SQL Server 2008 R2 Service Pack 1 ( CTP) уже точно. sqlserver-training.com/how-to-create-and-use-sequence-in-sql-server/-
2. Очень давно есть, OUTPUT Clause называется

[Duke_Raven]

pipelined имеет смысл использовать когда весь результирующий набор вы сами формируете. А если необходимо вернуть результат select, то проще использовать курсор.

[dmgorsky]

А ещё при работе с конвейерными (pipelined) функциями нужно иногда помнить про исключение NO_DATA_NEEDED :)

[pihel]

Возвратить таблицу можно еще так:
CREATE OR REPLACE
FUNCTION admin.virtual_date_table(p_num_rows in INTEGER, p_start_date IN DATE, p_end_date IN DATE)
RETURN virtual_date_table_type
IS
l_data virtual_date_table_type := virtual_date_table_type();
l_step NUMBER := 1;
BEGIN
l_step := (p_end_date - p_start_date) / p_num_rows;
FOR i IN 1 .. p_num_rows
LOOP
l_data.extend;
l_data(l_data.count) := p_start_date + i * l_step;
END LOOP;

RETURN l_data;
END;
/


select * from table(virtual_date_table(5, sysdate-5, sysdate))

[dmgorsky]

pipelined экономнее по памяти имхо.

[aspanin]

Нет. А что Вас смутило?

[aspanin]

У нас речь идет именно о получении набора данных, а не о программном интерфейсе

[aspanin]

Вы однозначно не уловили изюминки. Описанный шаблон позволяет вызывать функции выборок однотипно, меняя только название и параметры. Отсюда и логичная модель, когда методы работы с данными инкапсулируются в одном объекте. Или, по Вашему, в клиенте лучше наклепать кучу TADOQuery и TADOStoredProc'ов с жестко прописанными параметрами?

[aspanin]

Всё верно, на то он и сервер, чтобы выдавать данные по запросу с параметрами. Выборка данных — задача сервера, и sql код должен быть на сервере. Мне кажется это идеологически правильным.

[dmgorsky]

Кстати, а зачем было в самой функции разделение на два курсора делать?

Ведь можно написать условие в курсоре
where (dep.id = prm_depID or prm_depID is null)

ну, или, развлечения ради,
where nvl(to_char(dep.id), '') LIKE nvl(to_char(prm_depID), '%')

[aspanin]

Засчитано

[workless]

where dep.id = NVL(prm_depID, dep.id)

Без лайков и to_char