Как написать процедуру на PL/SQL, возвращающую набор данных (recordset)?

makondo
Дата: 29.09.2004 19:39:20
1 Вот пример на MSSQL

CREATE PROCEDURE dbo.SSearch
@ScreenData1 int,
@ScreenData2 int
AS
SELECT cID, Structure
FROM C
WHERE ((Screens1 & @ScreenData1) = @ScreenData1) and ((Screens2 & @ScreenData2) = @ScreenData2)
ORDER BY cID

Как написать точно такое же на PL/SQL ?

2. Можно ли в Oracle написать так:
SELECT * FROM SSearch(111, 222)

?
UDAVchik
Дата: 29.09.2004 19:56:44
makondo
SELECT * FROM SSearch(111, 222)

Для такого нужно, чтобы SSearch возвращал PL/SQL Collection и делать
SELECT * FROM Table(SSearch(111, 222))
makondo
Дата: 29.09.2004 19:59:37
А как саму процедуру переписать?
Stax
Дата: 29.09.2004 20:11:16
UDAVchik
Дата: 29.09.2004 20:18:09
makondo
А как саму процедуру переписать?

Гм... Почитай про Table Functions в PL/SQL Subprograms в доке по Oracle PL/SQL.

В двух словах - создаётся тип-коллекция, функция описывается как возвращающая его, в ней - переенная.
В эту переменную делается BULK COLLECT, после чего её функция и возвращает...
UDAVchik
Дата: 29.09.2004 20:20:32
Stax
Запрос из функции


Нда - какое-то представление даёт, но для означенной проблемы -
SELECT ... BULK COLLECT INTO ResultTAble
Подойдёт больше :)
makondo
Дата: 29.09.2004 23:22:38
Хм..
Потренировался сейчас дома на Скоте Тайгере - работает...
Завтра попробую.

Теперь такой вопрос - допустим я буду селектить из таблицы, в которой пару миллионов (ну или миллион) записей.
Такая штука будет работать пока не закончит или по мере надобности?
makondo
Дата: 29.09.2004 23:51:46
Некоторые проблемы..
тренируемся на табличке emp
С возвратом таблицы с одним полем все ок проходит.
Пытаемся вернуть 2 поля, как описано в той ссылке:

SQL> desc emp;
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
EMPNO                                     NOT NULL NUMBER(4)
ENAME                                              VARCHAR2(10)
JOB                                                VARCHAR2(9)
MGR                                                NUMBER(4)
HIREDATE                                           DATE
SAL                                                NUMBER(7,2)
COMM                                               NUMBER(7,2)
DEPTNO                                             NUMBER(2)

Создаем тип.

SQL> create or replace type t_test as object (
  2  empno number(4),
  3  ename varchar2(10)
  4  )
  5  /

Type created.

SQL> create or replace type tbl_test as table of t_test
  2  /

Type created.

Теперь функцию:

SQL>   create or replace function empno_and_mgr2(
  2    screen1 int,
  3    screen2 int)
  4    return tbl_test as
  5    v_result tbl_test := tbl_test();
  6   begin
  7    SELECT empno, ename BULK COLLECT INTO v_result
  8   FROM emp
  9   WHERE empno>screen1 and empno<screen2;
 10    return v_result;
 11    end;
 12    /

Warning: Function created with compilation errors.

SQL> show errors;
Errors for FUNCTION EMPNO_AND_MGR2:

LINE/COL ERROR
-------- -----------------------------------------------------------------
7/3      PL/SQL: SQL Statement ignored
8/2      PL/SQL: ORA-00947: not enough values

Что тут неверно?

Кстати, по ссылке
http://www.oracle-base.com/articles/9i/BulkBindsAndRecordProcessing9i.php
у мужика вполне получался %ROWTYPE
Но у Скота не выходит...
sergeyyy71
Дата: 30.09.2004 08:49:28
Нужно сделать приведение

SELECT t_test(empno, ename) BULK COLLECT INTO v_result
UDAVchik
Дата: 30.09.2004 10:39:01
И вот этого -
5    v_result tbl_test := tbl_test();
лучше не делать.

Поскольку не факт, что авто-extend пройдёт