VPF::хранимая процедура - Форум программистов

Wanderer2019

Дата 23.7.2009, 23:33 (ссылка)

(нет голосов)

Загрузка ...

Опытный

Профиль
Группа: Участник
Сообщений: 937
Регистрация: 3.12.2004
Где: Санкт-Петербург/П рага

Репутация: нет
Всего: 17

Всем привет,

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

Насколько я понял в оракле нельзя просто написать SELECT в хранимой процедуре и вернуть rowset. Можно бегать курсором или описывать табличные типы данных? правильно?

Может есть у кого простенький пример подобной процедуры - типа SELECT * FROM MyTable. Google выдает всего очень много а по существу все очень закручено, так что быстро не разобраться. smile

defmzk

Дата 24.7.2009, 04:01 (ссылка)

(нет голосов)

Загрузка ...

Новичок

Профиль
Группа: Участник
Сообщений: 22
Регистрация: 16.7.2009

Репутация: нет
Всего: нет

я как понял типа вот этого?

Код


select trunc(sysdate) from DUAL

Gudinya

Дата 24.7.2009, 08:43 (ссылка)

(нет голосов)

Загрузка ...

Новичок

Профиль
Группа: Участник
Сообщений: 46
Регистрация: 2.2.2008

Репутация: 5
Всего: 8

самый простой вариант

Код


create or replace function GetTableColumns(aTableName varchar2) return sys_refcursor
as
  Res sys_refcursor;
begin

  Open Res for
    Select uts.column_name
    From user_tab_columns uts
    Where uts.table_name = Upper(aTableName);
  
  Return Res;
end;

azesmcar

Дата 24.7.2009, 08:58 (ссылка)

(нет голосов)

Загрузка ...

uploading...

Профиль
Группа: Участник Клуба
Сообщений: 6291
Регистрация: 12.11.2004
Где: Армения

Репутация: 3
Всего: 211

Wanderer2019

Посмотри rowtype и курсоры.

Код


create or replace
function foo(l_id integer) return config%rowtype as
  rt config%rowtype;
begin
  select * into rt from config where config_id = l_id;
  return rt;
end;

что-то вроде этого. Если нужна процедура - используй out аргументы.

Это сообщение отредактировал(а) azesmcar - 24.7.2009, 12:06

DimW

Дата 24.7.2009, 09:42 (ссылка)

(нет голосов)

Загрузка ...

Эксперт

Профиль
Группа: Завсегдатай
Сообщений: 1330
Регистрация: 24.2.2005
Где: Орёл

Репутация: 23
Всего: 44

Цитата(azesmcar @ 24.7.2009, 08:58

)

что-то вроде этого.

azesmcar, ты показал как вернуть одну строку, автор просил набор.
вариант Gudinya правельный, но не единственный.

azesmcar

Дата 24.7.2009, 10:23 (ссылка)

(нет голосов)

Загрузка ...

uploading...

Профиль
Группа: Участник Клуба
Сообщений: 6291
Регистрация: 12.11.2004
Где: Армения

Репутация: 3
Всего: 211

Цитата(DimW @ 24.7.2009, 09:42

)

azesmcar, ты показал как вернуть одну строку, автор просил набор.

Да, мне показалось что набор данных - имеется ввиду структура а не набор строк.

Zloxa

Дата 24.7.2009, 10:29 (ссылка)

(нет голосов)

Загрузка ...

Чо?

Профиль
Группа: Завсегдатай
Сообщений: 3473
Регистрация: 12.9.2008

Репутация: 37
Всего: 161

Объясните зачем Вам нужно "SELECT * FROM MyTable" засовывать в процедуру. Не удовлетворит ли Вас просто вьюха?

--------------------

Достоверно известно, что 89% людей доверяют статистике взятой с потолка smile

Wanderer2019

Дата 24.7.2009, 11:11 (ссылка)

(нет голосов)

Загрузка ...

Опытный

Профиль
Группа: Участник
Сообщений: 937
Регистрация: 3.12.2004
Где: Санкт-Петербург/П рага

Репутация: нет
Всего: 17

Всем спасибо!
Буду пробовать.
Zloxa, мне ж из кода нужно возвращать большой набор данных. Писать запрос в теле фунции (.NET) я не хочу.

Добавлено через 14 минут и 12 секунд
Gudinya,
А как в запросе Oracle вызвать эту функцию? и чем функция предпочтительней к хранимой процедуре?

Gudinya

Дата 24.7.2009, 12:01 (ссылка)

(нет голосов)

Загрузка ...

Новичок

Профиль
Группа: Участник
Сообщений: 46
Регистрация: 2.2.2008

Репутация: 5
Всего: 8

ну эту функцию я как пример предложил
если вы хотите ее использовать для возвращения набора данных типа

Код


Select * From table(функция)

то использование курсора не самый удачный пример (лучше возвращать набор данных на основе типа)
если вы хотите использовать функцию ввиде

Код


Select функция From AnyTable

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

DimW

Дата 24.7.2009, 12:01 (ссылка)

(нет голосов)

Загрузка ...

Эксперт

Профиль
Группа: Завсегдатай
Сообщений: 1330
Регистрация: 24.2.2005
Где: Орёл

Репутация: 23
Всего: 44

Цитата(Wanderer2019 @ 24.7.2009, 11:11

)

А как в запросе Oracle вызвать эту функцию?

Код


begin
  :result := gettablecolumns(atablename => :atablename);
end;

в результе получите набор данных.

Цитата(Wanderer2019 @ 24.7.2009, 11:11

)

и чем функция предпочтительней к хранимой процедуре?

предпочтительней тогда когда требуется вернуть несколько наборов данных, т.е. обьявив несколько out параметров вы можете в них передать несколько наборов:

Код


create or replace procedure GetTableColumnsP(aTableName varchar2
                                            ,rs1 out sys_refcursor
                                            ,rs2 out sys_refcursor)
as
 Res1 sys_refcursor;
 Res2 sys_refcursor;
begin
  Open Res1 for
    Select uts.column_name
    From user_tab_columns uts
    Where uts.table_name = Upper(aTableName);
  rs1 := Res1;
  
  Open Res2 for
    Select uts.column_name
    From user_tab_columns uts
    Where uts.table_name = Upper(aTableName);
  rs2 := Res2;
end;

вызывать так:

Код


begin
  gettablecolumnsp(atablename => :atablename,
                   rs1 => :rs1,
                   rs2 => :rs2);
end;

Добавлено через 4 минуты и 23 секунды
и всетаки поясните почему вам требуется именно проседура, чем вьюха не устраивает?
ибо вьюха вам даст тот же функционал что вы и хотели.

Цитата(Wanderer2019 @ 23.7.2009, 23:33

)

Насколько я понял в оракле нельзя просто написать SELECT в хранимой процедуре

отличаться будут только вызовы.

Wanderer2019

Дата 24.7.2009, 12:29 (ссылка)

(нет голосов)

Загрузка ...

Опытный

Профиль
Группа: Участник
Сообщений: 937
Регистрация: 3.12.2004
Где: Санкт-Петербург/П рага

Репутация: нет
Всего: 17

Цитата(Gudinya @ 24.7.2009, 10:01

)

лучше возвращать набор данных на основе типа

это типа этого:

Код


create type eDelivery_doctype_obj is object (ID number, NAME varchar2(50), FILEMASK varchar2(150), GROUPING number,SPLITTING number, ZIPPING number, TIMERID number);

create type eDelivery_doctypes_tab is table of eDelivery_doctype_obj;

create or replace function all_docs return EDELIVERY_DOCTYPES_TAB
is
l_doctypes_tab eDelivery_doctypes_tab := eDelivery_doctypes_tab();
n integer := 0;
begin
for r in (select ID, NAME, FILEMASK, GROUPING, SPLITTING,ZIPPING,TIMERID from eDelivery_DocumentTypes)
loop
l_doctypes_tab.extend;
 n := n + 1;
l_doctypes_tab(n) := eDelivery_doctype_obj(r.ID, r.NAME,r.FILEMASK,r.GROUPING,r.SPLITTING,r.ZIPPING,r.TIMERID);
 end loop;
 return l_doctypes_tab;
end;

но, чтобы например дописать какой нибудь JOIN. Что мне делать? Руками склеивать каждую строчку таблицы A со строкой таблицы B????

Добавлено через 6 минут и 1 секунду

Цитата(DimW @ 24.7.2009, 10:01

)

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

Ведь чтобы вернуть значения из View - необходимо делать запрос. Запрос из кода типа SELECT * FROM myView будет работать быстрее чем вызов хранимой процедуры/функции с возвратом набора строк?

Zloxa

Дата 24.7.2009, 12:41 (ссылка)

(нет голосов)

Загрузка ...

Чо?

Профиль
Группа: Завсегдатай
Сообщений: 3473
Регистрация: 12.9.2008

Репутация: 37
Всего: 161

Цитата(Wanderer2019 @ 24.7.2009, 12:29

)

Запрос из кода типа SELECT * FROM myView будет работать быстрее чем вызов хранимой процедуры/функции с возвратом набора строк?

Даже если быстрее, то не значительно.
С вьюхой можно джойниться

ЗЫ
Вы видимо привыкли использовать MS SQL.
Многие бест практис для MS SQL в Оракле - пур практис. И наоборот.

Это сообщение отредактировал(а) Zloxa - 24.7.2009, 12:42

--------------------

Достоверно известно, что 89% людей доверяют статистике взятой с потолка smile

DimW

Дата 24.7.2009, 12:56 (ссылка)

(нет голосов)

Загрузка ...

Эксперт

Профиль
Группа: Завсегдатай
Сообщений: 1330
Регистрация: 24.2.2005
Где: Орёл

Репутация: 23
Всего: 44

Цитата(Wanderer2019 @ 24.7.2009, 12:29

)

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

если всетаки Zloxa прав и вы пытаетесь разрабатовоть в оракле так как вы привыкли это делать в другой СУБД, то саветую пересмотреть подход. это касается не только оракла, это проблема "переезда" с любой СУБД на любую.

Zloxa

Дата 24.7.2009, 12:56 (ссылка)

(нет голосов)

Загрузка ...

Чо?

Профиль
Группа: Завсегдатай
Сообщений: 3473
Регистрация: 12.9.2008

Репутация: 37
Всего: 161

Цитата(Wanderer2019 @ 24.7.2009, 11:11

)

Писать запрос в теле фунции (.NET) я не хочу.

Я не могу понять этого.
ПОЧЕМУ?
Если язык запросов для того и разрабатывался, чтобы его использовать из приложения.

--------------------

Достоверно известно, что 89% людей доверяют статистике взятой с потолка smile

Gudinya

Дата 24.7.2009, 12:59 (ссылка)

(нет голосов)

Загрузка ...

Новичок

Профиль
Группа: Участник
Сообщений: 46
Регистрация: 2.2.2008

Репутация: 5
Всего: 8

Что то типа...
опять же самый простой вариант

Код


create or replace type MyType is table of varchar2(250)

Код


create or replace function GetTableColumns(aTableName varchar2) return MyType
as
  Res MyType;
begin
    Select *
    Bulk collect into Res
    From user_tab_columns uts
    Where uts.table_name = Upper(aTableName);
  
  Return Res;
end;

Код


select * From table(cast(GetTableColumns('MyTableName') as MyType))

DimW

Дата 24.7.2009, 13:08 (ссылка)

(нет голосов)

Загрузка ...

Эксперт

Профиль
Группа: Завсегдатай
Сообщений: 1330
Регистрация: 24.2.2005
Где: Орёл

Репутация: 23
Всего: 44

Цитата(Gudinya @ 24.7.2009, 12:59

)

опять же самый простой вариант

Gudinya, вестию сервера уточните плиз, а то не феншуй:

Код


Connected to Oracle9i Enterprise Edition Release 9.2.0.8.0 

 
SQL> select * From table(cast(GetTableColumns('MyTableName') as MyType))
  2  /
 
select * From table(cast(GetTableColumns('MyTableName') as MyType))
 
ORA-06575: Package or function GETTABLECOLUMNS is in an invalid state
 
SQL>

Gudinya

Дата 24.7.2009, 13:29 (ссылка)

(нет голосов)

Загрузка ...

Новичок

Профиль
Группа: Участник
Сообщений: 46
Регистрация: 2.2.2008

Репутация: 5
Всего: 8

Код


Create table MyTable
(a number, b number);

create or replace type MyType is table of varchar2(250);
/
create or replace function GetTableColumns(aTableName varchar2) return MyType
as
  Res MyType;
begin
    Select uts.COLUMN_NAME
    Bulk collect into Res
    From user_tab_columns uts
    Where uts.table_name = Upper(aTableName);

  Return Res;
end;
/
select * From table(cast(GetTableColumns('MyTable') as MyType));

Код


Connected to Oracle9i Enterprise Edition Release 9.2.0.6.0 
Connected as user
 
SQL> 
 
Table created
 
Type created
 
Function created
 
COLUMN_VALUE
--------------------------------------------------------------------------------
A
B

Это сообщение отредактировал(а) Gudinya - 24.7.2009, 13:38

DimW

Дата 24.7.2009, 13:46 (ссылка)

(нет голосов)

Загрузка ...

Эксперт

Профиль
Группа: Завсегдатай
Сообщений: 1330
Регистрация: 24.2.2005
Где: Орёл

Репутация: 23
Всего: 44

Gudinya, прошу прощенья, это я накосячил.

Zloxa

Дата 24.7.2009, 14:03 (ссылка)

(нет голосов)

Загрузка ...

Чо?

Профиль
Группа: Завсегдатай
Сообщений: 3473
Регистрация: 12.9.2008

Репутация: 37
Всего: 161

DimW, Дыкть эта.. .а у тебя гранты то есть на референс словаря?
Gudinya, тыбы нубу ограничивал область применения своих советов.. у него ведь того:

Цитата(Wanderer2019 @ 24.7.2009, 11:11

)

нужно возвращать большой набор данных

--------------------

Достоверно известно, что 89% людей доверяют статистике взятой с потолка smile

Gudinya

Дата 24.7.2009, 14:52 (ссылка)

(нет голосов)

Загрузка ...

Новичок

Профиль
Группа: Участник
Сообщений: 46
Регистрация: 2.2.2008

Репутация: 5
Всего: 8

Zloxa

Цитата

нужно возвращать большой набор данных

Код


create or replace function GetTableColumns(aTableName varchar2) return MyType pipelined
as
begin
   for c in (
    Select uts.COLUMN_NAME
    From user_tab_columns uts
    Where uts.table_name = Upper(aTableName)) loop
    pipe row(c.column_name);
  end loop;
  Return;
end;

Zloxa

Дата 24.7.2009, 14:57 (ссылка)

(нет голосов)

Загрузка ...

Чо?

Профиль
Группа: Завсегдатай
Сообщений: 3473
Регистрация: 12.9.2008

Репутация: 37
Всего: 161

Gudinya, smile

А теперь можешь объяснить, чем это лучше вьюхи(я уже молчу про просто селект), не забывая учесть то, что ТС ничего не говорил о необходимости передачи в процедуру параметров. smile

upd.
Это из той же оперы, что то что я могу насрать посреди офиса, вовсе означает что мне это делать следует.

Это сообщение отредактировал(а) Zloxa - 24.7.2009, 15:00

--------------------

Достоверно известно, что 89% людей доверяют статистике взятой с потолка smile

Gudinya

Дата 24.7.2009, 15:09 (ссылка)

(нет голосов)

Загрузка ...

Новичок

Профиль
Группа: Участник
Сообщений: 46
Регистрация: 2.2.2008

Репутация: 5
Всего: 8

Цитата(Zloxa @ 24.7.2009, 14:57

)

А теперь можешь объяснить, чем это лучше вьюхи

В том то и дело что это ни каким образом не лучше вьюхи (не считая случаев когда тип заполняется сложным набором данных на основе нескольких селектов) даже не безызвестный Кайт пишет что старается использовать селект везде где это только возможно. Хоть я так и не понял зачем нужна была эта функция, я ее написал smile

Цитата(Zloxa @ 24.7.2009, 14:57

)

ТС ничего не говорил о необходимости передачи в процедуру параметров.

виноват, увлекся smile

Это сообщение отредактировал(а) Gudinya - 24.7.2009, 15:14

1 Пользователей читают эту тему (1 Гостей и 0 Скрытых Пользователей)
0 Пользователей:
« Предыдущая тема \| Oracle \| Следующая тема »