Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 Функция с курсором  [new]
volkmenrus
Member

Откуда:
Сообщений: 4
Имеется вот такая функция:

create or replace FUNCTION find_object_param1(name_object in varchar2)

RETURN SYS_REFCURSOR

IS
st sys_refcursor;
sql0 VARCHAR2(2000);

BEGIN

sql0:='SELECT attr.NAME, attr.DETAILS,
DECODE(p.ATTRIBUTE_ID,3,TO_CHAR(p.NUMBER_VALUE),p.TEXT_VALUE) AS PARAMS
FROM PARAMS p,
ATTRIBUTES attr,
OBJECTS o,
OBJECT_TYPES ot
WHERE p.ATTRIBUTE_ID = attr.ATTRIBUTE_ID
AND o.OBJECT_TYPE_ID = ot.OBJECT_TYPE_ID
AND p.object_id = o.object_id
AND ot.NAME ='||name_object;

IF LOWER(name_object) = 'shop' THEN
OPEN st FOR sql0;
END IF;
RETURN st;
CLOSE st;
END find_object_param1;

Вызываю ее:
select find_object_param1('shop') from dual;

И получаю:
ORA-00904: "SHOP": invalid identifier
ORA-06512: at "SHOP.FIND_OBJECT_PARAM1", line 23
00904. 00000 - "%s: invalid identifier"
*Cause:
*Action:

Ни как не пойму, что не так подскажите пожалуйста.
19 май 16, 15:04    [19194013]     Ответить | Цитировать Сообщить модератору
 Re: Функция с курсором  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 18373
Апострофа нет завершающего в строчном литерале:
AND ot.NAME ='||name_object;


Но вообще не комильфо так писать.
19 май 16, 15:09    [19194041]     Ответить | Цитировать Сообщить модератору
 Re: Функция с курсором  [new]
Egoр
Member

Откуда:
Сообщений: 833
volkmenrus,
Может быть так задумывалось?
create or replace FUNCTION find_object_param1(name_object in varchar2)

RETURN SYS_REFCURSOR

IS
st sys_refcursor;
sql0 VARCHAR2(2000);

BEGIN

sql0:='SELECT attr.NAME, attr.DETAILS,
DECODE(p.ATTRIBUTE_ID,3,TO_CHAR(p.NUMBER_VALUE),p.TEXT_VALUE) AS PARAMS
FROM PARAMS p,
ATTRIBUTES attr,
OBJECTS o,
OBJECT_TYPES ot
WHERE p.ATTRIBUTE_ID = attr.ATTRIBUTE_ID
AND o.OBJECT_TYPE_ID = ot.OBJECT_TYPE_ID
AND p.object_id = o.object_id
AND ot.NAME = :1';

IF LOWER(name_object) = 'shop' THEN
OPEN st FOR sql0 using in name_object;
END IF;
RETURN st;
CLOSE st;
END find_object_param1;
19 май 16, 15:10    [19194046]     Ответить | Цитировать Сообщить модератору
 Re: Функция с курсором  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 18373
Перепишите хоть так:
sql0:='SELECT attr.NAME, attr.DETAILS, 
...
AND ot.NAME = :1';

IF LOWER(name_object) = 'shop' THEN
OPEN st FOR sql0 
using name_object;
END IF;
19 май 16, 15:14    [19194071]     Ответить | Цитировать Сообщить модератору
 Re: Функция с курсором  [new]
123йй
Member

Откуда:
Сообщений: 1638
andrey_anonymous
Апострофа нет завершающего в строчном литерале

это и есть завершающий. а вот открывающий
sql0:='SELECT attr.NAME, attr.DETAILS,
19 май 16, 15:20    [19194108]     Ответить | Цитировать Сообщить модератору
 Re: Функция с курсором  [new]
123йй
Member

Откуда:
Сообщений: 1638
volkmenrus,
найди что пропустил
AND ot.NAME =shop
19 май 16, 15:26    [19194153]     Ответить | Цитировать Сообщить модератору
 Re: Функция с курсором  [new]
volkmenrus
Member

Откуда:
Сообщений: 4
Egoр, Ок. Спасибо большое.
А если у меня три параметра:

'SELECT attr.NAME, attr.DETAILS,
DECODE(p.ATTRIBUTE_ID,3,TO_CHAR(p.NUMBER_VALUE),p.TEXT_VALUE) AS PARAMS
FROM PARAMS p,
ATTRIBUTES attr,
OBJECTS o,
OBJECT_TYPES ot
WHERE p.ATTRIBUTE_ID = attr.ATTRIBUTE_ID
AND o.OBJECT_TYPE_ID = ot.OBJECT_TYPE_ID
AND ot.NAME =:1
AND p.object_id = o.object_id
AND o.OBJECT_ID IN (SELECT OBJECT_ID FROM PARAMS WHERE (TEXT_VALUE = LOWER(:2) OR TO_CHAR(NUMBER_VALUE) = (:2)) AND ATTRIBUTE_ID=:3)'

Как их перечислить OPEN st FOR sql0 using in name_object, а дальше чет я не пойму ... подскажите пожалуйста.
19 май 16, 15:30    [19194179]     Ответить | Цитировать Сообщить модератору
 Re: Функция с курсором  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 18373
volkmenrus
Как их перечислить OPEN st FOR sql0 using in

Через запятую
19 май 16, 15:32    [19194197]     Ответить | Цитировать Сообщить модератору
 Re: Функция с курсором  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 18373
123йй
andrey_anonymous
Апострофа нет завершающего в строчном литерале

это и есть завершающий. а вот открывающий

Мсье не понял намека.
Как еще разжевать?
AND ot.NAME ='''||name_object||'''';
19 май 16, 15:33    [19194217]     Ответить | Цитировать Сообщить модератору
 Re: Функция с курсором  [new]
Melkomyagkii_newbi
Member

Откуда: из прошлого
Сообщений: 1863
volkmenrus,

просто using 1st_variable, 2nd, etc

Учись пользоваться гугелем
https://docs.oracle.com/cd/B10501_01/appdev.920/a96590/adg09dyn.htm
Sample DML Operation Using Native Dynamic SQL

The following native dynamic SQL procedure gives a raise to all employees with a particular job title:

CREATE OR REPLACE PROCEDURE salary_raise (raise_percent NUMBER, job VARCHAR2) IS
    TYPE loc_array_type IS TABLE OF VARCHAR2(40)
        INDEX BY binary_integer;
    dml_str VARCHAR2        (200);
    loc_array    loc_array_type;
BEGIN
    -- bulk fetch the list of office locations
    SELECT location BULK COLLECT INTO loc_array
        FROM offices;
    -- for each location, give a raise to employees with the given 'job' 
    FOR i IN loc_array.first..loc_array.last LOOP
        dml_str := 'UPDATE emp_' || loc_array(i) 
        || ' SET sal = sal * (1+(:raise_percent/100))'
        || ' WHERE job = :job_title';
    EXECUTE IMMEDIATE dml_str USING raise_percent, job;
    END LOOP;
END;
/
SHOW ERRORS;
19 май 16, 15:34    [19194218]     Ответить | Цитировать Сообщить модератору
 Re: Функция с курсором  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 18373
Ну и в данном случае переменная sql0 не особо нужна, самовыражайтесь дословно:
IF LOWER(name_object) = 'shop' THEN
OPEN st FOR
SELECT attr.NAME, attr.DETAILS, 
DECODE(p.ATTRIBUTE_ID,3,TO_CHAR(p.NUMBER_VALUE),p.TEXT_VALUE) AS PARAMS 
FROM PARAMS p,
ATTRIBUTES attr,
OBJECTS o,
OBJECT_TYPES ot
WHERE p.ATTRIBUTE_ID = attr.ATTRIBUTE_ID
AND o.OBJECT_TYPE_ID = ot.OBJECT_TYPE_ID 
AND p.object_id = o.object_id
AND ot.NAME = name_object;
END IF;
19 май 16, 15:37    [19194252]     Ответить | Цитировать Сообщить модератору
 Re: Функция с курсором  [new]
Egoр
Member

Откуда:
Сообщений: 833
volkmenrus
А если у меня три параметра:
...
AND ot.NAME =:1
...
AND o.OBJECT_ID IN (SELECT OBJECT_ID FROM PARAMS WHERE (TEXT_VALUE = LOWER(:2) OR TO_CHAR(NUMBER_VALUE) = (:2)) AND ATTRIBUTE_ID=:3)'.
перечислять через запятую, как вам уже сказали.
Но, если я ничего не путаю, у вас ЧЕТЫРЕ параметра.
В случае open...for using параметры передаются не по именам, а по позиции.
19 май 16, 15:53    [19194388]     Ответить | Цитировать Сообщить модератору
 Re: Функция с курсором  [new]
Egoр
Member

Откуда:
Сообщений: 833
volkmenrus,
andrey_anonymous
Ну и в данном случае переменная sql0 не особо нужна ...
imho, в вашем конкретном случае вариант andrey_anonymous будет лучшим.
19 май 16, 15:57    [19194412]     Ответить | Цитировать Сообщить модератору
 Re: Функция с курсором  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 18373
Egoр
В случае open...for using параметры передаются не по именам, а по позиции.

http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/dynamic.htm#LNPLS631

Egoр
imho, в вашем конкретном случае вариант andrey_anonymous будет лучшим.

Ну я бы не торопился.
Огромный вопрос - для зачем ТС вообще sys_refcursor использует.
Подозреваю, что ему не только динамика не нужна, но и сам ref cursor - скорее ненужное усложнение очень простых вещей :)
19 май 16, 16:04    [19194451]     Ответить | Цитировать Сообщить модератору
 Re: Функция с курсором  [new]
volkmenrus
Member

Откуда:
Сообщений: 4
Да вы правы может и не нужны, но как лучше сделать я не знаю... Спасибо всем за помощь, очень помогли 3 день уже бился над этой функцией, мучением конец)
19 май 16, 16:24    [19194598]     Ответить | Цитировать Сообщить модератору
 Re: Функция с курсором  [new]
Egoр
Member

Откуда:
Сообщений: 833
andrey_anonymous
http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/dynamic.htm#LNPLS631
Ну, т.е. все-таки четыре параметра.
andrey_anonymous
Огромный вопрос - для зачем ТС вообще sys_refcursor использует.
Согласен, это я поспешил :))
19 май 16, 16:26    [19194625]     Ответить | Цитировать Сообщить модератору
 Re: Функция с курсором  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 18373
volkmenrus
Да вы правы может и не нужны, но как лучше сделать я не знаю..

А что сделать-то хотели?
Конкретнее - как будете использовать свою функцию?
19 май 16, 16:27    [19194632]     Ответить | Цитировать Сообщить модератору
 Re: Функция с курсором  [new]
ora601
Member

Откуда:
Сообщений: 750
volkmenrus,
кстати

RETURN st;
CLOSE st;


вкурсе, что курсор у тебя не закрывается, так что эта функция кандидат на утечку курсоров, если её использовать в SELECT list, i.e.

SELECT find_object_param1('shop') from dual connect by level<301
19 май 16, 16:34    [19194685]     Ответить | Цитировать Сообщить модератору
 Re: Функция с курсором  [new]
volkmenrus
Member

Откуда:
Сообщений: 4
Вообщем эта функция просто ищет с входными параметрами совпадения в табличках и выводит результат ...
19 май 16, 16:37    [19194703]     Ответить | Цитировать Сообщить модератору
 Re: Функция с курсором  [new]
Melkomyagkii_newbi
Member

Откуда: из прошлого
Сообщений: 1863
volkmenrus,

тебе про то что надо на клиенте курсор закрывать ибо после return ничего не выполняется в функции
19 май 16, 16:46    [19194778]     Ответить | Цитировать Сообщить модератору
 Re: Функция с курсором  [new]
ora601
Member

Откуда:
Сообщений: 750
andrey_anonymous
Ну и в данном случае переменная sql0 не особо нужна, самовыражайтесь дословно:
IF LOWER(name_object) = 'shop' THEN
OPEN st FOR
SELECT attr.NAME, attr.DETAILS, 
DECODE(p.ATTRIBUTE_ID,3,TO_CHAR(p.NUMBER_VALUE),p.TEXT_VALUE) AS PARAMS 
FROM PARAMS p,
ATTRIBUTES attr,
OBJECTS o,
OBJECT_TYPES ot
WHERE p.ATTRIBUTE_ID = attr.ATTRIBUTE_ID
AND o.OBJECT_TYPE_ID = ot.OBJECT_TYPE_ID 
AND p.object_id = o.object_id
AND ot.NAME = name_object;
END IF;


Если уже на то пошло, то даже и PL/SQL не нужен:
+

SELECT CURSOR (SELECT attr.NAME, attr.DETAILS, 
DECODE(p.ATTRIBUTE_ID,3,TO_CHAR(p.NUMBER_VALUE),p.TEXT_VALUE) AS PARAMS 
FROM PARAMS p,
ATTRIBUTES attr,
OBJECTS o,
OBJECT_TYPES ot
WHERE p.ATTRIBUTE_ID = attr.ATTRIBUTE_ID
AND o.OBJECT_TYPE_ID = ot.OBJECT_TYPE_ID 
AND p.object_id = o.object_id
AND ot.NAME = :name_object AND lower(:name_object)='shop') FROM dual

20 май 16, 00:04    [19196264]     Ответить | Цитировать Сообщить модератору
 Re: Функция с курсором  [new]
Shop'ее не напишешь
Guest
ora601
Если уже на то пошло, то даже и PL/SQL не нужен
Не эквивалентно. Оригинальная процедура не только открывает курсор, но и закрывает... сразу после return.
И если не shop, то все равно закрывает после возврата неоткрытого курсора.
20 май 16, 00:35    [19196307]     Ответить | Цитировать Сообщить модератору
 Re: Функция с курсором  [new]
ora601
Member

Откуда:
Сообщений: 750
Shop'ее не напишешь
но и закрывает... сразу после return.


А еще после RETURN можно писать бизнес логику. Ну да, щас.
20 май 16, 01:02    [19196328]     Ответить | Цитировать Сообщить модератору
 Re: Функция с курсором  [new]
--Eugene--
Member

Откуда: Боярышник
Сообщений: 2170
Shop'ее не напишешь
ora601
Если уже на то пошло, то даже и PL/SQL не нужен
Не эквивалентно.
1+
20 май 16, 07:23    [19196443]     Ответить | Цитировать Сообщить модератору
 Re: Функция с курсором  [new]
--Eugene--
Member

Откуда: Боярышник
Сообщений: 2170
а
[...] ot.NAME = :name_object AND lower(:name_object)='shop' [...]
- вообще шедевр
20 май 16, 07:27    [19196445]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Oracle Ответить