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

Откуда:
Сообщений: 29
Прошу помощи.
Задача: есть таблица с полем статуса, необходимо сделать выборку из этой таблицы по этому полю, затем заблокировать через SELECT FOR UPDATE и вернуть в набором данных клиенту. Сразу скажу, по одной записи возвращать не подходит.
27 июн 08, 09:32    [5856221]     Ответить | Цитировать Сообщить модератору
 Re: Функция, возвращающая набор данных  [new]
Jannny
Member

Откуда: Спб
Сообщений: 6424
поищите по слову pipelined
27 июн 08, 10:15    [5856464]     Ответить | Цитировать Сообщить модератору
 Re: Функция, возвращающая набор данных  [new]
FOX75
Member

Откуда:
Сообщений: 29
Спасибо, ща попробую.
Пока на ум приходит конструкция, через временную таблицу, т.е.
CREATE FUNCTION F RETURN SYS_REFCURSOR IS
pR SYS_REFCURSOR;
Scan_Locked EXCEPTION;
PRAGMA EXCEPTION_INIT (Scan_Locked, -54);--ORA-00054: resource busy
begin
CREATE TEMPORARY TABLE TMP_TABLE (N number(9));
for N IN (SELECT * FROM TABLE_NAME WHERE FieldName = FieldData) loop
begin
INSERT INTO TMP_TABLE
SELECT Fld FROM TABLE_NAME WHERE Field_ID = N.Field_ID FOR UPDATE NOWAIT;
exception
when Scan_Locked then
NULL;
END;
OPEN pR FOR SELECT * FROM TMP_TABLE;
end;
DROP TABLE TMP_TABLE;
RETURN pR
end;

Поправте, что не так
27 июн 08, 10:40    [5856641]     Ответить | Цитировать Сообщить модератору
 Re: Функция, возвращающая набор данных  [new]
Jannny
Member

Откуда: Спб
Сообщений: 6424
FOX75
Поправте, что не так
Да все, если честно, начиная с подхода (использовать неОракловый подход) и кончая синтаксическим ошибками.
27 июн 08, 10:41    [5856652]     Ответить | Цитировать Сообщить модератору
 Re: Функция, возвращающая набор данных  [new]
FOX75
Member

Откуда:
Сообщений: 29
систетику я не беру в счет, т.к. я писал на скору руку, главное подход
27 июн 08, 10:49    [5856706]     Ответить | Цитировать Сообщить модератору
 Re: Функция, возвращающая набор данных  [new]
Andrey.L
Member

Откуда: Харьков
Сообщений: 1546
FOX75
Прошу помощи.
Задача: есть таблица с полем статуса, необходимо сделать выборку из этой таблицы по этому полю, затем заблокировать через SELECT FOR UPDATE и вернуть в набором данных клиенту. Сразу скажу, по одной записи возвращать не подходит.
Зачем блокировать?
27 июн 08, 10:50    [5856717]     Ответить | Цитировать Сообщить модератору
 Re: Функция, возвращающая набор данных  [new]
Andrey.L
Member

Откуда: Харьков
Сообщений: 1546
FOX75
Спасибо, ща попробую.
Пока на ум приходит конструкция, через временную таблицу, т.е.
CREATE FUNCTION F RETURN SYS_REFCURSOR IS
  pR SYS_REFCURSOR;  
  Scan_Locked EXCEPTION; 
  PRAGMA EXCEPTION_INIT (Scan_Locked, -54);--ORA-00054: resource busy 
begin
 CREATE TEMPORARY TABLE TMP_TABLE (N number(9));
 for N IN (SELECT * FROM TABLE_NAME WHERE FieldName = FieldData) loop
   begin
     INSERT INTO TMP_TABLE
       SELECT Fld FROM TABLE_NAME WHERE Field_ID = N.Field_ID FOR UPDATE NOWAIT;
   exception 
     when Scan_Locked then 
       NULL;
   END;
   OPEN pR FOR SELECT * FROM TMP_TABLE;  
 end;
 DROP TABLE TMP_TABLE;
 RETURN pR
end;

Поправте, что не так
С MSSQL перелазишь? Выделенные строки работать не будут. Почитай про GLOBAL TEMPORARY TABLE (это совсем другое, чем #).
27 июн 08, 10:53    [5856728]     Ответить | Цитировать Сообщить модератору
 Re: Функция, возвращающая набор данных  [new]
медведка_креведка
Member

Откуда: Из лесоморя
Сообщений: 77
FOX75
Спасибо, ща попробую.
Пока на ум приходит конструкция, через временную таблицу, т.е.
CREATE FUNCTION F RETURN SYS_REFCURSOR IS
pR SYS_REFCURSOR;
Scan_Locked EXCEPTION;
PRAGMA EXCEPTION_INIT (Scan_Locked, -54);--ORA-00054: resource busy
begin
CREATE TEMPORARY TABLE TMP_TABLE (N number(9));
for N IN (SELECT * FROM TABLE_NAME WHERE FieldName = FieldData) loop
begin
INSERT INTO TMP_TABLE
SELECT Fld FROM TABLE_NAME WHERE Field_ID = N.Field_ID FOR UPDATE NOWAIT;
exception
when Scan_Locked then
NULL;
END;
OPEN pR FOR SELECT * FROM TMP_TABLE;
end;
DROP TABLE TMP_TABLE;
RETURN pR
end;

Поправте, что не так


Не стоит повторно изобретать велосипед :)


Writing a Pipelined Table Function
You declare a pipelined table function by specifying the PIPELINED keyword. Pipelined functions can be defined at the schema level with CREATE FUNCTION or in a package. The PIPELINED keyword indicates that the function returns rows iteratively. The return type of the pipelined table function must be a supported collection type, such as a nested table or a varray. This collection type can be declared at the schema level or inside a package. Inside the function, you return individual elements of the collection type. The elements of the collection type must be supported SQL datatypes, such as NUMBER and VARCHAR2. PL/SQL datatypes, such as PLS_INTEGER and BOOLEAN, are not supported as collection elements in a pipelined function.

Example shows how to assign the result of a pipelined table function to a PL/SQL collection variable and use the function in a SELECT statement.



CREATE PACKAGE pkg1 AS
  TYPE numset_t IS TABLE OF NUMBER;
  FUNCTION f1(x NUMBER) RETURN numset_t PIPELINED;
END pkg1;
/

CREATE PACKAGE BODY pkg1 AS
-- FUNCTION f1 returns a collection of elements (1,2,3,... x)
FUNCTION f1(x NUMBER) RETURN numset_t PIPELINED IS
  BEGIN
    FOR i IN 1..x LOOP
      PIPE ROW(i);
    END LOOP;
    RETURN;
  END;
END pkg1;
/

-- pipelined function is used in FROM clause of SELECT statement
SELECT * FROM TABLE(pkg1.f1(5));
27 июн 08, 10:54    [5856733]     Ответить | Цитировать Сообщить модератору
 Re: Функция, возвращающая набор данных  [new]
FOX75
Member

Откуда:
Сообщений: 29
автор
Andrey.L
Да с него родимого. Блокирую, чтоб на время сначала нужно быстрая блокировка записи, а потом выгрузка CLOB данных, дабы несколько конкурирующих приложений не захватили данные с CLOB поля (Ввод анкет со сканов).

автор
медведка_креведка
Спасибо, на этом сейчас и думаю. Оригинальное решение, которое я упустил.

Спасибо всем участникам!!!!
27 июн 08, 11:01    [5856771]     Ответить | Цитировать Сообщить модератору
 Re: Функция, возвращающая набор данных  [new]
stax..
Guest
FOX75

Поправте, что не так

советовали Вам другое
но не важно
SQL> var c refcursor
SQL> 
SQL> CREATE or replace FUNCTION F (p_dname in varchar2)
  2  RETURN SYS_REFCURSOR IS
  3   pR SYS_REFCURSOR;
  4   Scan_Locked EXCEPTION;
  5   PRAGMA EXCEPTION_INIT (Scan_Locked, -54);--ORA-00054: resource busy
  6  begin
  7  OPEN pR FOR
  8   SELECT *
  9   FROM emp e
 10   WHERE deptno in (select d.deptno from dept d where d.dname=p_dname)
 11   FOR UPDATE NOWAIT;
 12  RETURN pR;
 13  exception
 14   when Scan_Locked then
 15  --
 16  -- не знаю что надо возвращать
 17  --
 18  OPEN pR FOR
 19   SELECT *
 20   FROM emp e
 21   where 1=2;
 22   RETURN pR;
 23  end;
 24  /

Function created.

SQL> exec :c:=f('SALES');

PL/SQL procedure successfully completed.

SQL> print :c;

    EMPNO ENAME      JOB             MGR HIREDATE       SAL      COMM    DEPTNO
--------- ---------- --------- --------- -------- --------- --------- ---------
     7499 ALLEN      SALESMAN       7698 20.02.81      1600       300        30
     7521 WARD       SALESMAN       7698 22.02.81      1250       500        30
     7654 MARTIN     SALESMAN       7698 28.09.81      1250      1400        30
     7698 BLAKE      MANAGER        7839 01.05.81      2850                  30
     7844 TURNER     SALESMAN       7698 08.09.81      1500                  30
     7900 JAMES      CLERK          7698 03.12.81       950                  30

6 rows selected.

в другой сессии

SQL> var c refcursor
SQL> exec :c:=f('SALES');

PL/SQL procedure successfully completed.

SQL> print :c;

no rows selected

SQL> 

ps
работа с тмп табличками в оракля имеет свои фичи
.....
stax
27 июн 08, 11:04    [5856785]     Ответить | Цитировать Сообщить модератору
 Re: Функция, возвращающая набор данных  [new]
Andrey.L
Member

Откуда: Харьков
Сообщений: 1546
FOX75
Блокирую, чтоб на время сначала нужно быстрая блокировка записи, а потом выгрузка CLOB данных, дабы несколько конкурирующих приложений не захватили данные с CLOB поля
Описание вообще не понял.
FOX75
Ввод анкет со сканов.
Одну и ту же анкету могут одновременно с разных сканов загнать?
27 июн 08, 11:17    [5856853]     Ответить | Цитировать Сообщить модератору
 Re: Функция, возвращающая набор данных  [new]
FOX75
Member

Откуда:
Сообщений: 29
8 SELECT *
9 FROM emp e
10 WHERE deptno in (select d.deptno from dept d where d.dname=p_dname)
11 FOR UPDATE NOWAIT;

Есть одно но, я не смогу получить данные пока не завершиться транзакция, единственный способ только построчная проверка на блокировку.
27 июн 08, 11:24    [5856917]     Ответить | Цитировать Сообщить модератору
 Re: Функция, возвращающая набор данных  [new]
Jannny
Member

Откуда: Спб
Сообщений: 6424
FOX75
8 SELECT *
9 FROM emp e
10 WHERE deptno in (select d.deptno from dept d where d.dname=p_dname)
11 FOR UPDATE NOWAIT;
Есть одно но, я не смогу получить данные пока не завершиться транзакция, единственный способ только построчная проверка на блокировку.
Можно перевести выделенное?
Имеется ввиду, что Вам нужно аля for update skip locked?
27 июн 08, 11:28    [5856948]     Ответить | Цитировать Сообщить модератору
 Re: Функция, возвращающая набор данных  [new]
FOX75
Member

Откуда:
Сообщений: 29
автор
Andrey.L
Извини за недостаток информации, только у одного оператора определенный скан может отображон на экране оператора.
27 июн 08, 11:28    [5856949]     Ответить | Цитировать Сообщить модератору
 Re: Функция, возвращающая набор данных  [new]
FOX75
Member

Откуда:
Сообщений: 29
автор
Jannny
Совершенно верно, при чем еще есть одно условие, что оператор может захватить не более определенного кол-ва сканов и вводить данные удаленно, Offline режиме, а потом выложить данные.
27 июн 08, 11:33    [5856984]     Ответить | Цитировать Сообщить модератору
 Re: Функция, возвращающая набор данных  [new]
stax..
Guest
FOX75
8 SELECT *
9 FROM emp e
10 WHERE deptno in (select d.deptno from dept d where d.dname=p_dname)
11 FOR UPDATE NOWAIT;

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

есть недокументированный в старых версиях параметр
SKIP LOCKED
первая
  1  SELECT *
  2  FROM emp e
  3  WHERE deptno <20
  4  FOR UPDATE NOWAIT
  5* skip locked
SQL> /

    EMPNO ENAME      JOB             MGR HIREDATE       SAL      COMM    DEPTNO
--------- ---------- --------- --------- -------- --------- --------- ---------
     7782 CLARK      MANAGER        7839 09.06.81      2450                  10
     7839 KING       PRESIDENT           17.11.81      5000                  10
     7934 MILLER     CLERK          7782 23.01.82      1300                  10

вторая
  1  SELECT *
  2  FROM emp e
  3  WHERE deptno <30
  4  FOR UPDATE NOWAIT
  5* skip locked
  6  /

    EMPNO ENAME      JOB             MGR HIREDATE       SAL      COMM    DEPTNO
--------- ---------- --------- --------- -------- --------- --------- ---------
     7369 SMITH      CLERK          7902 17.12.80       800                  20
     7566 JONES      MANAGER        7839 02.04.81      2975                  20
     7788 SCOTT      ANALYST        7566 09.12.82      3000                  20
     7876 ADAMS      CLERK          7788 12.01.83      1100                  20
     7902 FORD       ANALYST        7566 03.12.81      3000                  20


если версия оракля старая,
я Вас ничего не говорил
.....
stax
27 июн 08, 11:37    [5857007]     Ответить | Цитировать Сообщить модератору
 Re: Функция, возвращающая набор данных  [new]
FOX75
Member

Откуда:
Сообщений: 29
автор
есть недокументированный в старых версиях параметр
SKIP LOCKED
\
А вот за это огромное спасибо!!!!
27 июн 08, 11:41    [5857034]     Ответить | Цитировать Сообщить модератору
 Re: Функция, возвращающая набор данных  [new]
FOX75
Member

Откуда:
Сообщений: 29
Хотя
автор
stax..
, недокументированные операторы, это дырка в проекте, особенно при переходе на др. версию сервера.
27 июн 08, 11:45    [5857063]     Ответить | Цитировать Сообщить модератору
 Re: Функция, возвращающая набор данных  [new]
Jannny
Member

Откуда: Спб
Сообщений: 6424
stax..
есть недокументированный в старых версиях параметр SKIP LOCKED
Да?
27 июн 08, 11:49    [5857086]     Ответить | Цитировать Сообщить модератору
 Re: Функция, возвращающая набор данных  [new]
stax..
Guest
FOX75
Хотя
автор
stax..
, недокументированные операторы, это дырка в проекте, особенно при переходе на др. версию сервера.

http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/statements_10002.htm#SQLRF01702
.....
stax
27 июн 08, 11:50    [5857091]     Ответить | Цитировать Сообщить модератору
 Re: Функция, возвращающая набор данных  [new]
stax..
Guest
Jannny
stax..
есть недокументированный в старых версиях параметр SKIP LOCKED
Да?

да
зы
напр когда-то bitand была недокументирована,
но "некоторые" ее активно пользовали
решать не мне
....
stax
27 июн 08, 11:53    [5857127]     Ответить | Цитировать Сообщить модератору
 Re: Функция, возвращающая набор данных  [new]
FOX75
Member

Откуда:
Сообщений: 29
Н да... и куда я глядел, когда читал хелпы
27 июн 08, 11:58    [5857167]     Ответить | Цитировать Сообщить модератору
 Re: Функция, возвращающая набор данных  [new]
Andrey.L
Member

Откуда: Харьков
Сообщений: 1546
stax..
есть недокументированный в старых версиях параметр
SKIP LOCKED
Пните, пожалуйста в документацию. Не нашел.
27 июн 08, 12:28    [5857417]     Ответить | Цитировать Сообщить модератору
 Re: Функция, возвращающая набор данных  [new]
Jannny
Member

Откуда: Спб
Сообщений: 6424
Andrey.L
stax..
есть недокументированный в старых версиях параметр
SKIP LOCKED
Пните, пожалуйста в документацию. Не нашел.
3-мя постами выше. Старыми были названы все, кроме 11ой :)
27 июн 08, 12:31    [5857440]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить