Вернуть выборку из анонимного PLSQL блока в C#

122

28 августа 2019, 11:00

Получилось вернуть выборку из запроса SELECT, но вот хотелось бы вернуть отдельную таблицу, с которой в теле блока делали много разного. Что-то удаляли, например, после сложной обработки (какие-то строки). Сейчас код такой:

using System.Data.OracleClient;
....
private void btn_execute_Click(object sender, EventArgs e)
        {
            string oracleDbConnection = @"Data Source= (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = " + tb_ip.Text + ")(PORT = " + tb_port.Text + ")))(CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = " + tb_servname.Text + ")));"
                + @"User Id=" + tb_login.Text + ";Password=" + tb_pass.Text + ";";
            try
            {
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = rtb_Query.Text;
                cmd.Parameters.Add("return_cursor", OracleType.Cursor);
                cmd.Parameters["return_cursor"].Direction = ParameterDirection.Output;
                using (cmd.Connection = new OracleConnection(oracleDbConnection))
                {
                    cmd.Connection.Open();
                    Console.WriteLine("Connected!");
                    var reader = cmd.ExecuteReader();
                    OracleDataAdapter da = new OracleDataAdapter(cmd);
                    DataTable dt = new DataTable();
                    this.dgv_results.DataSource = dt;
                    da.Fill(dt);
                    this.dgv_results.DataSource = dt;
                    foreach (DataGridViewRow row in dgv_results.Rows)
                    {
                        row.HeaderCell.Value = String.Format("{0}", row.Index + 1);
                    }
                    Console.WriteLine("Loading is ended");
                }
            }
            catch (OracleException ex)
            {
                Console.WriteLine(ex.Message);
            }
        }

А сам запрос:

declare
begin
    open :return_cursor for
        SELECT * FROM CITIES WHERE NOT REGEXP_LIKE (to_char(CIT_ID), '[2,4,6,8,0]') AND NOT REGEXP_LIKE (to_char(CIT_ID), '[\D]');
end;

То есть, я вижу как в курсор подать select чего-нибудь, но не представляю как вернуть что-нибудь вроде:

FOR Cur_row in SELECT * FROM CITIES LOOP
    <преобразования>
    other_table.add (Cur_row.CIT_ID); -- ну да, тут дичь
END LOOP;
open :return_cursor for SELECT * FROM other_table;

При этом other_table нигде в базе не создавать как таблицу (пусть и с последующим удалением).

Answer 1

Если требуемое в цикле преобразование возможно сделать внутри простого селекта - лучше сделать именно внутри селекта. А если нет, то вам может помочь pipelined функция. Она работает практически так, как вы просите, только вместо строки

other_table.add (Cur_row.CIT_ID); -- ну да, тут дичь

Нужно будет написать pipe row и добавить кое-что еще.

Теперь по шагам:

Создаете тип, который будет задавать строку запроса:
```
create type city_row as object (
  city_id number,
  city_name varchar2(100));
```
На его основе создаете табличный тип:
```
create type t_city_table as table of city_row;
```

Теперь создаете функцию, которая возвращает табличный тип:

create or replace function get_cities return t_city_table pipelined as 
  cur_row city_row := city_row(null, null);
begin
  FOR i in (SELECT * FROM CITIES) LOOP
      cur_row.city_id := i.city_id;
      cur_row.city_name := i.city_name; 
      --<преобразования>
      pipe row (cur_row); -- ваша "не дичь"
  END LOOP;
end;
/

Обращаетесь с ней, как с таблицей (почти):
```
SELECT * FROM TABLE(get_cities);
```

Answer 2

Используйте коллекцию.

CREATE OR REPLACE TYPE other_row AS OBJECT( CIT_ID NUMBER );
/
CREATE OR REPLACE TYPE other_table AS TABLE OF other_row ;
/

и тогда ваш код скорее всего отработает:

FOR Cur_row in SELECT * FROM CITIES LOOP
    <преобразования>
    other_table.add (Cur_row.CIT_ID); -- ну да, тут дичь
END LOOP;
open :return_cursor for SELECT * FROM TABLE(other_table);