Процедура на сервере с параметрами

← →
BorisUK (2003-01-17 07:49) [0]

Есть желание перенести формирование запроса на сервер, в процедуру. (а не формировать его на клиенте в зависимости от многих условий)
Проблема в том что эти условия никто не отменял.
вот Пример
PROCEDURE GetDoc(MyCursor IN OUT MyProc, dtCreateDateBegin in varchar2, dtCreateDateEnd in varchar2, DocState in dt.Reference, lNomerDoc in varchar2, lUserID in varchar2)
AS ....

Далее нужен запрос если параметры lUserID lNomerDoc и DocState - переданы, то включить их в запрос, а если не переданы, то такое условие не ставить.

Если делать чтото типа
If lUserID <> "" then
select * from table where fieldlUserID = lUserID;
else
select * from table;
end if;

Но понятно, что при даже трех параметрах - так делать уже неудобно (параметров 12).
Есть ли другой способ - подскажите где копать :) ?????
Очень надеюсь , что способ есть - ИМХО должен быть!!
Заранее спасибо.

← →
Alexandr (2003-01-17 08:01) [1]

не совсем понял. Как-то ты запутанно очень объяснил
Но смею предложить вариант

select * from table where (fieldlUserID = lUserID or IuserID="");

примерно так.

← →
BorisUK (2003-01-17 08:06) [2]

Ну так получится что когда параметр lUserID="" (тоесть непередан) то будет
select * from table where (fieldlUserID = "" or IuserID="");
Да хоть бы и так, но селектнутся записи где это поле пустое !!!
Но надо то, чтоб при отсутствии параметра считать что его важе не надо - тоесть все...
select * from table

!

← →
Alexandr (2003-01-17 08:11) [3]

ты вообще знаешь как условие or работает?
Или сегодня просто неудачный день?
так вот, если у тебя iuserid будет равно "" т.е. результат true,
то будет пофигу чего там в fieldlUserID все-равно все записи выберуться. т.е. условие в скобках будет всегдла true.

← →
BorisUK (2003-01-17 08:28) [4]

Слушай
> Alexandr ©

или я не так чтото делаю или ты меня по ложному пути направил
Ибо делаю
select * from table where (fieldlUserID = "" or IuserID="");
и пока на входе в параметре не будет чтото что в поле есть (например USER1) записи не выбераются
Тоесть как я и предположил конструкция ищет в поле либо значение либо его отсутствие (тоесть пусто чтоб было В ПОЛЕ) но никак не ВСЕ ПОЛЯ.
Если у тебя пример полностью работает, то может листинг поши чтоли... у меня однозначно такая конструкция не работает.
Кстати пробовал и ничего не передавать и передавать в параметр :="". В хранимой процедуре пробовал и
select * from table where (fieldlUserID = lUserID or IuserID="");
и так
select * from table where (fieldlUserID = lUserID or IuserID=null);

????

← →
Alexandr (2003-01-17 08:34) [5]

1) у тебя сервер БД какой?
2) Попробуй в скобки заключить where((условие1) or (условие2))
3) видимо, не =null надо, а is null
4) Я таким способом постоянно пользуюсь в ХП на Interbase

← →
BorisUK (2003-01-17 08:45) [6]

Спасибо заработало
Но это ваще нивкакие варота...
Сдалал проверку на пробел.. тоесть
select * from table where (fieldlUserID = lUserID or lUserID =" ");

И если параметр юзером не задан то передаю в процедуру этот злосчастный пробел!!!!

Но пачему условие не выполнялось когда передавал :=""
пробовал ваще ничего не передавать
Проверки в процедере делал и
select * from table where (fieldlUserID = lUserID or lUserID ="");
и
select * from table where (fieldlUserID = lUserID or lUserID is null);

Пачемуто не прокатывало блин... Вот такая петрушка!

← →
BorisUK (2003-01-17 08:46) [7]

в скобки тоже брал - не помогло
> Alexandr ©

← →
Alexandr (2003-01-17 08:55) [8]

1) Какой сервер БД
2) я вообще-то часто is Null в таких случаях использую.
3) Для null: смотря как ты его на клиенте передал.

← →
BorisUK (2003-01-17 09:57) [9]

> Alexandr ©

Сервер ORACL.
PL/SQL
Передаю с клиента либо :="" либо ваще ничего
Параметры процедуры все asString

Есть несколько вопросов касательно дальнейшего развития данной темы.

Вот не могу придумать как обыграть ситуацию...
Таже процедура но еще есть параметр CanLike : boolean и lNomer
как сделать чтобы работало примерно так (это явно нерабочий вариант но смысл понятен)
Select * from table where
(if СanLike then FieldNomer like "%"||lNomer||"%" else FieldNomer =lNomer)
Тоесть либо тебе Like либо строгое равенство

Далее

Есть определенные типы документов которые нужны не все а к примеру так FieldDocType in (12345, 12346, 12347)... и иногда нужен какойнибудь один.
Как сделать чтобы если параметр явно передали например DocType=12345 Тогда ....
where FieldDocType=DocType
а если не передали то
where FieldDocType in (12345, 12346, 12347)

Неужели у PL SQL нет какогонить инструмента позволяющего такое реализовать? Если есть подскажите где искать?

← →
Sergey13 (2003-01-17 10:21) [10]

2BorisUK © (17.01.03 09:57)
>Передаю с клиента либо :="" либо ваще ничего
Это две большие разницы, как говорят в Одессе

>Но понятно, что при даже трех параметрах - так делать уже неудобно (параметров 12).
А кому щас легко? Никто и не утверждал нигде что процедуры всегда короткие.
>Есть ли другой способ - подскажите где копать :) ?????
Другого нет, ИМХО. И копать надо в сторону динамического SQL.

Если динамический SQL не нравится, можно попробовать в качестве первого параметра передавать номер варианта запроса (их же конечное количество?) и в зависимости от него в процедуре сразу идти на соответствующую ветку и подставлять нужные параметры в заранее подготовленый запрос.
Например
вариант 1 параметры 5,6,9
вариант 2 параметры 3,4,12
А можно (раз все параметры стринговые) принять, что для первого варианта второй параметр значит одно, а для второго другое. Тогда и число параметров можно сократить.

В общем варианты реализации есть. PL/SQL достаточно мощный процедурный язак.

← →
stone (2003-01-17 10:46) [11]

см. про динамическое составление запросов в PL/SQL

← →
BorisUK (2003-01-17 13:06) [12]

Покопал динамический SQL - очень дельная вещь.
Можно нашпиговать запрос как душе угодно...
Непонятно всего одна деталь - помогите плиз додуматься немогу.
Мне надо чтоб этот запрос возвратил результат в параметр IN OUT моей процедуры который описан как
MyCursor IN OUT MyProc ,где MYProc IS REF CURSOR RETURN <table1>%rowtype;

Пока запрос статический (с параметрами) все работает
делаем так
OPEN MyCursor FOR SELECT * From Table1 where Field1=Param1 ....
И получаем набор данных в курсоре

при динамическом запросе его вначале формируем...
к примеру в переменную V_MySQL - я его сформировал как мне надо
затем делаем

v_cursor:=Dbms_Sql.Open_Cursor;
Dbms_Sql.Parse(v_cursor, V_MySQL, DBMS_SQL.v7);

// V_MySQL - содержит простой запрос типа "Select * from table1 where Field1=Par1 and Field2=Par2", но он соответствует некоторым условиям, задачи были описаны выше. Тоесть он уже какой мне надо - нужно только добится его вывода...

// переменная v_cursor Integer; идентификационный номер курсора, используемый для обозначения контекстной области

Вот я не могу додуматься как сделать чтоб результат этого запроса получился на выходе процедуры - тоесть в моем MyCursor
??????
Не мог бы ктонибудь направить меня на путь истинный.
Задача на самом деле очень полезная, в дальнейшем может всем пригодиться :)

← →
Dmitry Rogov (2003-01-17 13:26) [13]

Ну, например, так:

create or replace function FN_NEXTRECORD (TableName varchar2) return number is TYPE cur IS REF CURSOR; cr cur; Result number; vcSql varchar2 (1000); begin vcSQL := "select sq_"||TableName||"_id.NextVal from dual"; open cr for vcsql; fetch cr into Result; close cr; Return(Result); exception when OTHERS then return(0); end;

← →
Sergey13 (2003-01-17 13:42) [14]

2BorisUK © (17.01.03 13:06)
Ты определись, что тебе надо и в чем проблема. То ты спрашивал как запрос сформировать, то перешел на возврат курсора в датасет. С простым запросом ты курсор возвратил? Тогда какие проблемы со сложным? Или у тебя не получается курор возвратить. Тогда забудь пока про динамический сиквел и работай над этим с простым курсором. А то все сразу... 8-)

← →
BorisUK (2003-01-17 13:46) [15]

> open cr for vcsql;

Странно у меня это не работает...
Ну я по аналогии в своей процедуре подставил
OPEN MyCursor For V_MySQL
она матерится - говорит "Нельзя использовать в комманде OPEN Dynamic SQL"
А что разве можно так???
Что еще попробовать можно?

← →
BorisUK (2003-01-17 14:25) [16]

> Sergey13 © (17.01.03 13:42)

С простым статическим запросом я курсор возвращяю.
Я сразу это написал.

...
TYPE MyView IS REF CURSOR RETURN View_SELDOC%rowtype;
...
PROCEDURE GetDoc(MyCursor IN OUT MyView)
AS
BEGIN
OPEN MyCursor FOR SELECT * from Alb_Seldoc where rownum<15;
END GetDoc;

Теперь одна проблема как сделать чтобы выполнилось чтото типа
как Dmitry Rogov предложил.
Например так уже не работает... а именно так бы и хотелось!
PROCEDURE GetDoc(MyCursor IN OUT MyView, lDocState in dt.Reference,)
AS
v_SQLString varchar2(100);
BEGIN
v_SQLString:="SELECT * from Alb_Seldoc where rownum<15";
if lDocState<>"" then
v_SQLString:=v_SQLString||"and DocState="||lDocState;
else v_SQLString:=v_SQLString||"and DocState in (1, 2, 3)";
end if;

OPEN MyCursor FOR v_SQLString;
END GetDoc;

говорит "Нельзя использовать в комманде OPEN Dynamic SQL"
Извините если это элементарно. Но хоть скажите примерно где косяк?
Заранее спасибо.

← →
Sergey13 (2003-01-17 14:33) [17]

2BorisUK © (17.01.03 14:25)
>Извините если это элементарно.
Вот этого я не говорил.
>говорит "Нельзя использовать в комманде OPEN Dynamic SQL"
Ну раз говорит, значит нельзя. Тогда попробуй как я писал Sergey13 © (17.01.03 10:21) про варианты. Может так прокатит.

← →
BorisUK (2003-01-17 14:52) [18]

Всю документацию, что нашел - перерыл по этому Динамическому SQL ать его.
Но нигде нет именно простого примера как его заставить выдаваться в курсор.
Еслиб это небыло так извратно с 12 параметрами делать вариант как писал Sergey13 © (17.01.03 10:21) , то давно бы сделал..
Блин казалось бы чего проще..
if то делай то... и все.
я в 20 строчек напишу как этому SQL сформироваться в зависимости от входящих параметров.
КАК потом запустить это на выполнение и в курсор вернуть блин найти немогу :(

Не ужто все так плохо и никто никогда не пытался такое замутить???
Аж абыдно да...

← →
Dmitry Rogov (2003-01-17 16:46) [19]

А Oracle какой?
У меня на 8.1.7 замечательно работает. Это NATIVE DYNAMIC SQL.
Если пользоваться DBMS_SQL тогда что-то типа:

CREATE OR REPLACE FUNCTION fn_nextrecord(vcNameTable IN VARCHAR2) RETURN INTEGER IS iNextRecord INTEGER; vcExec VARCHAR2(256); curExec INTEGER; iCount INTEGER; BEGIN iNextRecord := 0; vcExec := "SELECT sq_"||vcNameTable||"_ID"||".nextval FROM dual"; curExec := DBMS_SQL.Open_Cursor; DBMS_SQL.Parse(curExec,vcExec,DBMS_SQL.native); DBMS_SQL.Define_Column(curExec,1,1); iCount := DBMS_SQL.Execute(curExec); iCount := DBMS_SQL.Fetch_Rows(curExec); DBMS_SQL.Column_Value(curExec,1,iNextRecord); DBMS_SQL.Close_Cursor(curExec); END IF; RETURN iNextRecord; EXCEPTION WHEN OTHERS THEN IF DBMS_SQL.Is_Open(curExec) THEN DBMS_SQL.Close_Cursor(curExec); END IF; RETURN iNextRecord; END; /

← →
BorisUK (2003-01-20 08:27) [20]

> Dmitry Rogov (17.01.03 16:46)

Пожалусто помогите если это в ваших силах.
Ваш пример наводит меня на мысль, что то что я задумал можно сделать, но никак не пойму как. Вот ход моих запутавшихся мыслей...
1. Мне нужно чтоб функция возвращяла набор данных - я его потом отображаю в датасет
2. Функция принимает некоторое количество параметров и я ПРЕДВОРИТЕЛЬНО формирую запрос в зависимости от их количества и сочетания.

Вот так я определяю свою функцию и все работает как надо если не использовать Динамич SQL/
МОЯ ф-я:

Function GetDocF(dtCreateDateBegin in varchar2, dtCreateDateEnd in varchar2, lDocState in dt.Reference, lNomerDoc in varchar2, lUserID in varchar2)
return MyView is
cr MyView;

BEGIN
OPEN cr FOR SELECT * FROM doctree dt WHERE
/* and dt.initdate > To_Date(dtCreateDateBegin,"DD.MM.YYYY HH24:MI")
and dt.initdate < To_Date(dtCreateDateEnd,"DD.MM.YYYY HH24:MI")*/
((dt.authorid=LUserID) or (LUserID is null))
and dt.doctype in (1025174756, 1020599430)
and (dt.label like "%"||lNomerDoc||"%")
and dt.DocState in (1000000034, 1000000035, 1000000036, 1000000037, 1000000039, 1019713784, 1019713785, 1016722168)
and rownum<100
order by dt.initdate desc;
Return(cr);
END GetDocF;

← →
BorisUK (2003-01-20 08:52) [21]

Сорри не дописал, случайно добавил...
Ну так вот это все работает тип MyView
описан как
TYPE MyView IS REF CURSOR RETURN DocTree%rowtype;

Переделываю это как казалось бы в динамически формируемы запрос
тоесть конкатинирую строки рапроса в зависимости от входящих параметров в переменную
v_SQLString:="SELECT * ";
if lDocState<>"" then
v_SQLString:=v_SQLString||"and dt.DocState="||lDocState;
else v_SQLString:=v_SQLString||"and dt.DocState in (1, 2, 3)";
end if;
И подтавляю на место прошлого запроса в комманду OPEN
...
OPEN cr FOR v_SQLString;
Судя по вашему примеру логично должно работать, но не работает!
Версия OPACLR 8.1.5 - точно ли это будет работать на 8.1.7
Именно с возвращением курсора а не параметра типа Integer как в вашем примере...???
И еще есть один вопрос - как можно применить DBMS_SQL чтобы вернуть именно в курсор а не в Integer?

Что я не так понимаю?

Вот если исходить из моего примера то

curExec := DBMS_SQL.Open_Cursor;
- Открываем курсов и в переменной curExec будет идентификационный номер курсора, используемый для обозначения контекстной области
DBMS_SQL.Parse(curExec,v_SQLString,DBMS_SQL.native);
- Здесь у меня все парсится, но ведь это простой разбор синтаксиса - синтаксис запроса верен!!!

DBMS_SQL.Define_Column(curExec,1,1); - Вот здесь не понял 1,1 - это позиция и номер колонки, а как тип задать и можно ли для всей таблици сразу? Из за этого я наверное и не могу потом согласовать типы...

iCount := DBMS_SQL.Execute(curExec); - Эта функция исполняет запрос и возвращяет в iCount всеголишь количество обработанных строк.

iCount := DBMS_SQL.Fetch_Rows(curExec);
- Выбираем результат в локальный буфер

DBMS_SQL.Column_Value(curExec,1,iNextRecord);
- возможно именно сдесь и можно произвести запись результата в переменную, но до этого у меня еще не доходит - типы не соответствуют.

DBMS_SQL.Close_Cursor(curExec);

← →
BorisUK (2003-01-20 09:00) [22]

Блин... прочитал все что написал и сам не понял что спросил :)
В общем не могу понять как сделать чтоб динамически сформированный запрос типа "Select чтото From оттудато Where де тото" - возвращяющий набор данных. Чтоб работал (в том примере где я его статически подставил ведь работает) и возвращял эти данные в result моей функции - тип REF CURSOR.
????

← →
BorisUK (2003-01-20 13:04) [23]

Все - работает
Прошу прощения что вводил в заблуждение.
Обратите внимание

> ,где MyView.
TYPE MyView IS REF CURSOR RETURN DocTree%rowtype;

Сам не пойму зачем привязывался к конкретной таблице.
Именно поэтому работала статика. Т к типы проверялись и все было ок еще при компиляции.
А при динамике ему негде было типы сверять.. вводило в заблуждение что ошибка была не про типы а какапопало :)

В общем пишем просто
MyView IS REF CURSOR и все работает :)
Даже без всякого DBMS...
просто OPEN cr FOR "траляля"||"траляляля"||"траляля что хотим"
как раз то что и было надо.
Всем спасибо особенно

> Dmitry Rogov

За то что убедил что так МОЖНО, что сподвигло найти эту чертову ошибку :)

← →
Dmitry Rogov (2003-01-20 15:21) [24]

Да не за что. Успехов!

Процедура на сервере с параметрами Найти похожие ветки