В общем, не претендую на "совет от мастера".. ибо родилось в бою.. :)
Но, сам искал решение, и в общем то так и не нашел внятного ответа не тут не там..
Суть: Часто есть необходимость программировать на стороне сервера "умные" запросы возвращающие наборы данных, иными словами, параметроризованные view но с логикой условия несколько сложней чем простое WHERE..
На этот случай, в Oracle есть по сути два подхода - процедуры и функции. Преимущества того или иного не буду обсуждать, скажу только то, что в процедуру мы должны передать указатель (ссылку) на курсор а функция этого не требует ибо просто возвращает набор данных.
В любом случае, мне показалось использование функции более удобным. Замечу, разговор идет не о вызове на стороне сервера а о работе из приложения, в данном случае, написанного на C#. Но по сути, организация такого подхода может с успехом пользоваться и на стороне сервера.
Ничего нового нет, гуру могут пить кофе.. :)
И так, первое что надо сделать, объявить тип ссылочного курсора, по сути, это будет аналогичным типу DataSet.
Объявить можно пример так:
1.
2.
3.
4.
CREATE OR REPLACE PACKAGE L2.SQLGET --где L2 это имя схемы (пользователя под которым создаете/работаете)
AS
TYPE TCURSOR IS REF CURSOR;
END;
далее, что бы не париться со схемами (есть подозрение, что правильно строить БД и размещать объекты (таблицы и т.д.) таки не в схеме sys... :) ) при обращении к этому типу, просто создадим публичный синоним
create or replace public synonym SQLGET for L2.SQLGET;
таким образом, сможем без указания владельца обращаться к этому типу.
А далее все намного проще чем казалось:
Например, нам надо отобразить набор данных в зависимости от условия или из одного источника или из другого, причем эти источники (например, таблицы) совершенно разные и по составу и по содержанию.
Делаем функцию:
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
CREATE OR REPLACE FUNCTION L3.GET_CHEM_ANL( D1 DATE, D2 DATE, WORKER INTEGER) RETURN SQLGET.TCURSOR
IS
CUR SQLGET.TCURSOR;
BEGIN
IF(WORKER != 0 ) THEN -- ЕСЛИ НАС ИНТЕРЕСУЕТ ЧТО-ТО ИНОЕ ЧЕМ "ПО УМОЛЧАНИЮ"
OPEN CUR FOR
SELECT * FROM L2.PLANTS A
WHERE A.DATE_WORK BETWEEN D1 AND D2 -- ПОКАЖЕМ ДАННЫЕ ЗА ТРЕБУЕМЫЙ ДИАПАЗОН
ORDER BY A.RECTIME DESC;
ELSE -- ИНАЧЕ, НАС ИНТЕРЕСУЕТ КОНКРЕТНЫЙ УЧАСТОК
OPEN CUR FOR
SELECT P.FIO, P.TAB_NUM, D.* FROM L2.DEP D
LEFT OUTER JOIN L2.PERS P ON (D.DEP_ID = P.DEP_ID)
WHERE D.DEP_NUM = WORKER
ORDER BY P.TAB_NUM DESC;
END IF;
RETURN CUR;
END;
Где L3 - это другая схема, но это для примера, возможно вообще не указывать схему, если Вы работаете под своими именем и понятия не имеете о каких то там схемах - все будет создано в вашей схеме по умолчанию.
Таблица DEP - подразделения - имеет например 15 полей
Таблица PLANTS - производственные участки имеет например 25 полей
Причем, в случае, если передан параметр WORKER отличный от 0 то возвращаем по сути VIEW основанный на 2-х таблицах.
В общем то на стороне сервера всё..
Вот, мы создали приложение на C# и как же достучаться до этой функции???
попробуем так:
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Oracle.DataAccess.Client;
using System.Data;
namespace ChemHeat
{
class Class1
{
OracleConnection con = new OracleConnection( "User Id=L3;Password=password;Data Source=ORA;" );
OracleCommand cmd;
public Class1()
{
cmd = con.CreateCommand();
}
~Class1()
{
if ( con.State == ConnectionState.Open )
con.Close();
}
public void GetData(DateTime d1, DateTime d2, int id_worker)
{
cmd.Parameters.Clear();
cmd.CommandText = "L3.GET_WORK_PLANT";
cmd.CommandType = CommandType.StoredProcedure;
// важно! добавляем параметр сурсора (CUR) первым, иначе, Oracle будет долго ругаться
cmd.Parameters.Add( "CUR", OracleDbType.RefCursor, ParameterDirection.ReturnValue );
cmd.Parameters.Add( "D1", d1);
cmd.Parameters.Add( "D2", d2 );
cmd.Parameters.Add( "WORKER", Convert.ToInt32(id_worker) );
try
{
if ( con.State != ConnectionState.Open )
con.Open();
OracleDataReader rd = cmd.ExecuteReader();
while ( rd.Read() )
{
Console.WriteLine(String.Format("#\t{0}\t{1}\t{2}",
rd[0].ToString(), rd[1].ToString(), rd[2].ToString()));
}
rd.Close();
}
finally
{
con.Close();
}
}
}
}
Честно говоря, если Вы будите использовать визуальный конструктор датасета, то это не пригодится, всё сделают за вас. Но, часто все же надо ручками - и этот пример, возможно не совсем полноценный, т.к. лень было делать отдельно приложение и вывод у меня идет не на консоль а в ListView - но в данном случае это думаю не критично, достаточно и наглядно.. :)
Так что нет никаких сложностей, хотел добавить ответ на вопрос, как сделать так, что бы например в ComboBox`е одно а получались значения другие, т.е. как организовать например это
1.
SELECT FIO, TAB_NUM FROM PERS
что бы пользователю отображалось Ф.И.О. но при выборе, можно было получить табельный номер - это просто, но тут думаю, это будет уже лишним.. :)
***************************************
