Перевод пользовательской функции с T-SQL на PL/SQL

← →
Ольга (2005-07-28 08:31) [0]

Уважаемые мастера-ораклисты, помогите перевести функцию. У меня нет Oracle, негде потренироваться.
На T-SQL это выглядит так:

CREATE FUNCTION dbo.GetRDG (@nplan int, @beg datetime, @end datetime)
RETURNS @GetData TABLE (nplan int, DT datetime, fvalue float)
AS
BEGIN
DECLARE @data datetime, @np int, @h01 float, @h02 float

DECLARE RAB CURSOR SCROLL FOR
SELECT data,nplan,h01,h02
FROM DG_PH
WHERE NPLAN=@NPLAN AND
DATA>=@beg AND DATA<=@end
FOR READ ONLY

OPEN Rab
FETCH NEXT FROM Rab INTO @data, @np, @h01, @h02
WHILE (@@FETCH_STATUS = 0)
BEGIN
INSERT INTO @GetData VALUES (@np, @data, @h01)
INSERT INTO @GetData VALUES (@np, @data, @h02)

FETCH NEXT FROM Rab INTO @data, @np, @h01, @h02
END
DEALLOCATE Rab
RETURN
END

Может ли вообще функция возвращать таблицу(курсор) в Oracle?

← →
ANB © (2005-07-28 09:27) [1]

Может. Но это надо в доку лезть. Могу примерчик поискать с курсором. Набор данных в Oracle похитрее возвращается, я так ни разу не делал.
Совет : Oracle то поставь. Он на любую NT нормально становится.

← →
Ольга (2005-07-28 09:41) [2]

> Oracle то поставь. Он на любую NT нормально становится.

Мои коллеги пробовали - убили кучу времени, но так и не поставили. Да и не хочется, чесно говоря, времени жалко. Мне ведь кроме вот этой "фитюльки" больше от Oracle ничего не надо.

> Могу примерчик поискать с курсором

Безмерно была бы благодарна.

← →
Ольга (2005-07-28 10:25) [3]

Эй, на шхуне! Живые есть?...
Может хоть чтиво какое подскажите на тему создания функций и курсоров в Oracle.

ANB, вы про меня забыли или примерчик не нашелся?

← →
ANB © (2005-07-28 10:31) [4]

Это, отвлекся я. Вот самый короткий примерчик :
PROCEDURE getds_gsl( DATASET OUT system_types.TCursor) IS BEGIN OPEN DATASET FOR SELECT * FROM v_p5_gsl v ORDER BY v.NAME; END getds_gsl;
На 8i работает.
ЗЫ. Ораклу ставить просто. Если 2000 или XP, то вообще без проблем. Там же универсальный инсталлятор. Только лишнее все выкинуть надо, а то по умолчанию еще и HTTP сервер ставится.

← →
evvcom © (2005-07-28 10:44) [5]

Курсор и таблица - разные вещи. [4] возвращает курсор.

← →
Ольга (2005-07-28 11:25) [6]

Процесс пошел. Функцию почти написала.
Нужен аналог для PL/SQL функции DATEADD(minute, 30, @data)
(извините за наглость, у меня кроме Internet ничего нет)

← →
ANB © (2005-07-28 11:29) [7]

> evvcom © (28.07.05 10:44) [5]

> Может ли вообще функция возвращать таблицу(курсор) в Oracle?

> Ольга (28.07.05 11:25) [6]
- ща поищу, но чего то для минут не помню я . . .
Отвечу минут через 20 - 30. Ща оракла у меня занята.

← →
Sergey13 © (2005-07-28 11:40) [8]

2[6] Ольга (28.07.05 11:25)
А зачем функция?
select sysdate+30/1440 from dual
например добавляет полчаса к системной дате. 1440=24*60

← →
ANB © (2005-07-28 11:48) [9]

Точно, не нужна тут функция. В оракле - дата - число. 1 - один день. 1/24/60 - минута. Есть add_months - чтобы прибавить месяцы. Ее достаточно. А время - просто числом добавляй и не мучайся.

← →
Reindeer Moss Eater © (2005-07-28 11:51) [10]

> Может ли вообще функция возвращать таблицу(курсор) в Oracle?

Может конечно.
Надо только определиться куда возвращать результат. На клиента или он будет использоваться исключительно на сервере.
Может и не надо никакого курсора возвращать.

← →
Ольга (2005-07-28 12:18) [11]

Ну вот сотворила НЕЧТО. Трудно программировать на малознакомом языке вслепую. Прежде, чем отправить клиенту функцию на пробу, хочу услышать критику мастеров:

CREATE FUNCTION GetRDG(nplan numeric(6,0),tbeg date,tend date)
RETURNS SETOF users_type AS "
DECLARE r users_type%ROWTYPE; (объявление типа см. ниже)
BEGIN
DECLARE i NUMBER(2,0)
i=1;
FOR Row IN
SELECT datetime,objectid,hour0_30
FROM ZVK_DBA.dsgrplan_archiv
WHERE objectid=NPLAN AND
datetime>=tbeg AND datetime<=tend
LOOP
r.nplan=Row.objectid;
r.DT=TRUNC(Row.datetime)+i*30/1440 ;
r.fvalue=Row.hour0_30;
i=i+1;
RETURN NEXT r;

r.nplan=Row.objectid;
r.DT=TRUNC(Row.datetime)+i*30/1440 ;
r.fvalue=Row.hour1_00;
i=i+1;
RETURN NEXT r;
END LOOP;

RETURN;

END;

Вызываем функцию из клиентского приложения:

Создаем свой тип данных:
Query.SQL.Text:="CREATE TYPE users_type AS (nplan numeric(6,0), DT date, fvalue numeric(10,0));";
Query.ExecSQL;

Считываем данные:
Query.SQL.Text:="SELECT * FROM GetRDG(:np, :d1, :d2)";
Query.Open;

← →
Reindeer Moss Eater © (2005-07-28 12:22) [12]

Ну для начала заменить все "=" на ":="

← →
Reindeer Moss Eater © (2005-07-28 12:27) [13]

Параметры указываются безразмерными.
Вместо numeric надо number.
Вместо returns надо return.
После AS убрать кавычку.

В общем все неправильно.

← →
Reindeer Moss Eater © (2005-07-28 12:31) [14]

А вообще эта функция заменяется одной вьюхой.

← →
Sergey13 © (2005-07-28 12:32) [15]

2[11] Ольга (28.07.05 12:18)
Я бы наверное завернул все это в пакет. Там и тип можно объявить.

[2] Ольга (28.07.05 09:41)
>> Oracle то поставь. Он на любую NT нормально становится.
>Мои коллеги пробовали - убили кучу времени, но так и не поставили.
Не 8-ку на 4 пенек ставили случаем?

← →
Ольга (2005-07-28 12:54) [16]

> В общем все неправильно

Синтаксис я поправлю, конечно. А по сути какие замечания? Или все это бред сивой кобылы в лунную ночь?

> функция заменяется одной вьюхой

Заманчиво, но... Таблица, из которой делается запрос, содержит не 3-4 поля, а 50 (ID, дата и 48 значений за каждые 30 мин. суток). А мне надо получить получасы в записях, а не в полях. Как создать такую вьюху - 48 раз UNION? Это накладно, таблица офигенная.

> Не 8-ку на 4 пенек (???) ставили случаем?

8.1 на Win2000
Если вслепую не удастся, то придется еще попробовать на ХР.

← →
Reindeer Moss Eater © (2005-07-28 12:56) [17]

create or replace view myview as
select objectid,
trunc(datetime) + rownum * 30 / 14440,
decode(mod(rownum,2),0,hour1_00,1,hour0_30)
from ....

← →
Ольга (2005-07-28 12:57) [18]

> Я бы наверное завернул все это в пакет

Я бы тоже, но пока не разобралась с этими пакетами.

← →
Sergey13 © (2005-07-28 12:59) [19]

2[16] Ольга (28.07.05 12:54)
> 8.1 на Win2000
ОСь в данном случае по барабану. Важен процик. Если 4 пенек, то там надо в дистрибутиве пошаманить немного - это известная проблема.

← →
Ольга (2005-07-28 13:07) [20]

Sergey13 © [19]
О, дошло, 4 пенек - это пентиум 4 (я, знаете, в прогр. сленге слаба). Буду иметь в виду, если придется шаманить, поможете?

Reindeer Moss Eater © [17]
Так, так, так... А вот с этого места можно поподробнее:
decode(mod(rownum,2),0,hour1_00,1,hour0_30)
Ничего не поняла.

← →
Sergey13 © (2005-07-28 13:13) [21]

2 [20] Ольга (28.07.05 13:07)
> Буду иметь в виду, если придется шаманить, поможете?
Поможем. Не вопрос.

← →
Reindeer Moss Eater © (2005-07-28 13:53) [22]

Reindeer Moss Eater © [17]
Так, так, так... А вот с этого места можно поподробнее:
decode(mod(rownum,2),0,hour1_00,1,hour0_30)
Ничего не поняла.

Если строка четная, в выборку попадает поле hour1_00, иначе hour0_30

Твоя функция ничего полезного не сделает. Ибо ты возвращаешь и не курсор и не набор данных, а пытаешься вернуть строку.
Select из такой функции и из функции, возвращающей курсор в оракле не проканает. Пиши лучше вьюху.

> Sergey13 © (28.07.05 12:32) [15]
- кстати, у меня тоже была проблема с установкой 8i на П4 (Хрюша). Еле с нашим админом смогли запихать. Обрубив HTTP сервер. И теперь бага есть - если один из сидов хоть раз упал, то больше он в автомате не стартует. Это не оттуда же у проблемы ноги растут ?

2[24] ANB © (28.07.05 15:47)
Вторая часть твоей проблемы - не знаю, разбираться надо (возможно проканает просто пересоздать сервис или отредактировать его ORADIM-ом). А первая - там в дистрибутиве надо убить (вроде в 2 местах) symcjit.dll. В простейшем случае этого хватает. Иногда надо открыть файл oraparam.ini и изменить jre_memory_options на jre_memory_options = -nojit-ms16m-mx32m. Есть еще несколько советов. Но указанного мне хватало в большинстве случаев.

> Sergey13 © (28.07.05 15:55) [25]
- а мы этого не делали. Оракла 8.2.
oradim ом уже все правил, в реестре все включил. Посмотрели лог - там при автостарте - ошибка нехватки Share Memory. Но кода ручками стартуешь базу - все нормально.

Да, еще, пересоздание сервиса помогает, но у меня там уже штук 15 немаленьких схем и неохота их все выгружать - загружать. Я раз промучился, а когда снова упала, уже не стал.

Тьфу, 8.1.7.4.1

← →
Ольга (2005-08-01 13:25) [30]

Возвращаюсь к теме.
И так, я сделала, как мне и советовали, VIEW а не функцию. Теперь возникла другая заморочка:
1. Прилинковала к серверу MSSQL сервер Oracle8.1 Линк с правами пользователя Оракла "reader", вижу оракловские таблицы и View через MS Enterprise Manager.
2. В Оракле "reader" имеет права на чтение одной таблицы ZVK_DBA.DSGRPLAN и на создание VIEW. Создала VIEW
reader.myview. В SQL Plus все прекрасно выбирается.
3. В MS Query Analyzer запускаю 2 запроса к прилинкованным объектам -
напрямую к таблице:
select * from [bl.cdo.ups.ru]..ZVK_DBA.DSGRPLAN - работает
к View:
select * from [bl.cdo.ups.ru]..rdgreader.myview - не работает
Ошибка:
OLE DB provider "bl.cdo.ups.ru" does not contain table ""rdgreader"."getrdg"". The table either does not exist or the current user does not have permissions on that table.
Каких прав не хватает? Или к View надо обращаться как то по особенному?

← →
Ольга (2005-08-01 13:43) [31]

Исправляюсь:
OLE DB provider "bl.cdo.ups.ru" does not contain table ""reader"."myview"". The table either does not exist or the current user does not have permissions on that table.

Перевод пользовательской функции с T-SQL на PL/SQL Найти похожие ветки