ReSQL.ru
     
powered by simpleCommunicator - 2.0.30     © 2024 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Map
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Oracle [игнор отключен] [закрыт для гостей] / запрос возвращающий таблицу с произвольным количеством столбцов
11 сообщений из 36, страница 2 из 2
  2  все
запрос возвращающий таблицу с произвольным количеством столбцов
    #40105409
Фотография Sayan Malakshinov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Sayan Malakshinov
Модератор форума
andrey_anonymous
классических ref cursor на динамическом sql
имхо проще и правильнее было бы использовать Implicit Statement Results (DBMS_SQL.RETURN_RESULT)
...
Рейтинг: 0 / 0
19.10.2021, 14:33
| Ответить | Цитировать | Написать  
запрос возвращающий таблицу с произвольным количеством столбцов
    #40105410
Фотография Кобанчег
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Кобанчег
Участник
SY
PTT это чисто SQL решение.
А exec кто делает?
...
Рейтинг: 0 / 0
19.10.2021, 14:34
| Ответить | Цитировать | Написать  
запрос возвращающий таблицу с произвольным количеством столбцов
    #40105433
Фотография andrey_anonymous
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
andrey_anonymous
Участник
Sayan Malakshinov
Implicit Statement Results (DBMS_SQL.RETURN_RESULT)

Уж больно оно новомодное.
Есть подозрение, что не каждый клиент справится - там апишка на клиенте вроде как своя (get_next_resultset).
...
Рейтинг: 0 / 0
19.10.2021, 15:52
| Ответить | Цитировать | Написать  
запрос возвращающий таблицу с произвольным количеством столбцов
    #40105472
Фотография SY
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SY
Участник
Кобанчег
SY
PTT это чисто SQL решение.
А exec кто делает?


SQL решение в смысле никаких сторонних tools типа Java, C++, .NET... и самое главное мы получаем результат через SELECT.

SY.
...
Рейтинг: 0 / 0
19.10.2021, 20:24
| Ответить | Цитировать | Написать  
запрос возвращающий таблицу с произвольным количеством столбцов
    #40105477
Фотография SY
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SY
Участник
Забыл про SQL макро, правда придется форсировать hard parse:

Код: plsql
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.
drop table test_tbl purge
/
drop sequence test_tbl_seq
/
create table test_tbl as (
                          select 'blue' val,1 rn,1 col from dual union all
                          select 'pink',1,2 from dual union all
                          select 'azure',1,3 from dual union all
                          select 'orange',2,1 from dual union all
                          select 'green',2,2 from dual
                         )
/
create or replace
  function pivot_test_tbl_macro
    return varchar2
    sql_macro
    is
        v_stmt clob := 'SELECT ';
        v_cnt  number;
    begin
        select  max(col)
          into  v_cnt
          from  test_tbl;
        for v_i in 1..v_cnt loop
          v_stmt := v_stmt || 'MAX(CASE COL WHEN ' || v_i || ' THEN VAL END) COL' || v_i || ',';
        end loop;
        v_stmt := rtrim(v_stmt,',') || ' FROM TEST_TBL GROUP BY RN ORDER BY RN';
        execute immediate v_stmt;
        return v_stmt;
end;
/
create sequence test_tbl_seq -- для форсирования hard parse
/



Теперь:

Код: plsql
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.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
SQL> set verify off
SQL> column test_tbl_seq new_value test_tbl_seq noprint
SQL> select  test_tbl_seq.nextval test_tbl_seq
  2    from  dual
  3  /

SQL> select  *
  2    from  table(pivot_test_tbl_macro) -- execution &test_tbl_seq (форсируем hard parse чтобы SQL_MACRO вызывалось при каждом обращении)
  3  /

COL1   COL2   COL3
------ ------ ------
blue   pink   azure
orange green

SQL> insert
  2    into  test_tbl
  3    values(
  4           'yellow',
  5           1,
  6           7
  7          )
  8  /

1 row created.

SQL> select  test_tbl_seq.nextval test_tbl_seq
  2    from  dual
  3  /

SQL> select  *
  2    from  table(pivot_test_tbl_macro) -- execution &test_tbl_seq (форсируем hard parse чтобы SQL_MACRO вызывалось при каждом обращении)
  3  /

COL1   COL2   COL3   COL4   COL5   COL6   COL7
------ ------ ------ ------ ------ ------ ------
blue   pink   azure                       yellow
orange green

SQL> insert
  2    into  test_tbl
  3    values(
  4           'teal',
  5           3,
  6           4
  7          )
  8  /

1 row created.

SQL> select  test_tbl_seq.nextval test_tbl_seq
  2    from  dual
  3  /

SQL> select  *
  2    from  table(pivot_test_tbl_macro) -- execution &test_tbl_seq (форсируем hard parse чтобы SQL_MACRO вызывалось при каждом обращении)
  3  /

COL1   COL2   COL3   COL4   COL5   COL6   COL7
------ ------ ------ ------ ------ ------ ------
blue   pink   azure                       yellow
orange green
                     teal

SQL>



SY.
...
Рейтинг: 0 / 0
19.10.2021, 22:30
| Ответить | Цитировать | Написать  
запрос возвращающий таблицу с произвольным количеством столбцов
    #40105504
Фотография Stax
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Stax
Участник
SY
Забыл про SQL макро, правда придется форсировать hard parse:
SY.


test_tbl_seq.nextval форсирует hard parse?

.....
stax
...
Рейтинг: 0 / 0
20.10.2021, 08:41
| Ответить | Цитировать | Написать  
запрос возвращающий таблицу с произвольным количеством столбцов
    #40105563
Фотография SY
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SY
Участник
Stax

test_tbl_seq.nextval форсирует hard parse?
stax


Не сам по себе а:

Код: plsql
1.
2.
3.
select  *
  from  table(pivot_test_tbl_macro) -- execution &test_tbl_seq (форсируем hard parse чтобы SQL_MACRO вызывалось при каждом обращении)
/



Посему каждый раз имеем новый SQL текст которого нет в shared pool так-что hard parse. Из минусов - пухнущий shared pool. Можно избежать:

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
create or replace
  function pivot_test_tbl_macro
    return varchar2
    sql_macro
    is
        v_stmt clob := 'SELECT ';
        v_cnt  number;
    begin
        select  max(col)
          into  v_cnt
          from  test_tbl;
        for v_i in 1..v_cnt loop
          v_stmt := v_stmt || 'MAX(CASE COL WHEN ' || v_i || ' THEN VAL END) COL' || v_i || ',';
        end loop;
        v_stmt := rtrim(v_stmt,',') || ' FROM TEST_TBL GROUP BY RN ORDER BY RN';
        execute immediate v_stmt;
        return v_stmt;
end;
/



Теперь:

Код: plsql
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.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
SQL> set verify off
SQL> column column_count new_value column_count noprint
SQL> select  max(col) column_count
  2    from  test_tbl
  3  /

SQL> select  *
  2    from  table(pivot_test_tbl_macro) -- column count is &column_count форсируем hard parse чтобы SQL_MACRO вызывалось при каждом новом числе полей
  3  /

COL1   COL2   COL3
------ ------ ------
blue   pink   azure
orange green

SQL> insert
  2    into  test_tbl
  3    values(
  4           'yellow',
  5           1,
  6           7
  7          )
  8  /

1 row created.

SQL> select  max(col) column_count
  2    from  test_tbl
  3  /

SQL> select  *
  2    from  table(pivot_test_tbl_macro) -- column count is &column_count форсируем hard parse чтобы SQL_MACRO вызывалось при каждом новом числе полей
  3  /

COL1   COL2   COL3   COL4   COL5   COL6   COL7
------ ------ ------ ------ ------ ------ ------
blue   pink   azure                       yellow
orange green

SQL> insert
  2    into  test_tbl
  3    values(
  4           'teal',
  5           3,
  6           4
  7          )
  8  /

1 row created.

SQL> select  max(col) column_count
  2    from  test_tbl
  3  /

SQL> select  *
  2    from  table(pivot_test_tbl_macro) -- column count is &column_count форсируем hard parse чтобы SQL_MACRO вызывалось при каждом новом числе полей
  3  /

COL1   COL2   COL3   COL4   COL5   COL6   COL7
------ ------ ------ ------ ------ ------ ------
blue   pink   azure                       yellow
orange green
                     teal

SQL> delete test_tbl
  2   where col = 7
  3  /

1 row deleted.

SQL> select  max(col) column_count
  2    from  test_tbl
  3  /

SQL> select  *
  2    from  table(pivot_test_tbl_macro) -- column count is &column_count форсируем hard parse чтобы SQL_MACRO вызывалось при каждом новом числе полей
  3  /

COL1   COL2   COL3   COL4
------ ------ ------ ------
blue   pink   azure
orange green
                     teal

SQL>



SY.
...
Рейтинг: 0 / 0
20.10.2021, 12:52
| Ответить | Цитировать | Написать  
запрос возвращающий таблицу с произвольным количеством столбцов
    #40105566
kmskmskms
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
kmskmskms
Гость
а как объявить функцию в пакете которая возвращает таблицу с заранее неизвестным количеством столбцов?
количество столбцов будет известно только внутри функции исходя из содержимого таблицы
...
Рейтинг: 0 / 0
20.10.2021, 13:03
| Ответить | Цитировать | Написать  
запрос возвращающий таблицу с произвольным количеством столбцов
    #40105569
Фотография Stax
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Stax
Участник
SY


Не сам по себе а:



понял, я проморгал амперсанд

....
stax
...
Рейтинг: 0 / 0
20.10.2021, 13:09
| Ответить | Цитировать | Написать  
запрос возвращающий таблицу с произвольным количеством столбцов
    #40105597
Фотография SY
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SY
Участник
kmskmskms
а как объявить функцию в пакете которая возвращает таблицу с заранее неизвестным количеством столбцов?
количество столбцов будет известно только внутри функции исходя из содержимого таблицы


Число столбцов определяется только при hard parse посему что бы не использовалось (ODCI interface, SQL macro, polymorphic table function...) мы будем вынуждены форсировать hard parse для каждого нового числа столбцов что я и показал.

Код: plsql
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.
SQL> select  *
  2    from  table(pivot_test_tbl_macro)
  3  /

COL1   COL2   COL3   COL4
------ ------ ------ ------
blue   pink   azure
orange green
                     teal

SQL> insert
  2    into  test_tbl
  3    values(
  4           'yellow',
  5           1,
  6           7
  7          )
  8  /

1 row created.

SQL>  -- не форсируем hard parse
SQL>  select  *
  2    from  table(pivot_test_tbl_macro)
  3  /

COL1   COL2   COL3   COL4
------ ------ ------ ------
blue   pink   azure
orange green
                     teal

SQL> select  *
  2    from  table(pivot_test_tbl_macro) -- column count 7 форсируем hard parse
  3  /

COL1   COL2   COL3   COL4   COL5   COL6   COL7
------ ------ ------ ------ ------ ------ ------
blue   pink   azure                       yellow
orange green
                     teal

SQL>



Первый SELECT в shared pool Oracle не находит и выполняет hard parse вызывая SQL macro который конструирует SELECT с четырьмя полями. Затем мы вставляем запись с COL=7 т.е. теперь надо вывести 7 полей. Однако при последующем выполнении идентичного SELECTа (абсолютно идентичный текст) Oracle находит SQL текст в shared pool и использует его. Посему SQL macro (ODCI interface, polymorphic table - любой метод переменного числа полей) не вызывается и последующие выполнения идентичного SELECTа выдадут 4 поля независимо от MAX(COL). А как только мы изменили текст SELECTа и Oracle не находит новый текст в shared pool то выполняется hard parse, вызывается SQL macro который конструирует SELECT с семью полями.

SY.
...
Рейтинг: 0 / 0
20.10.2021, 14:19
| Ответить | Цитировать | Написать  
запрос возвращающий таблицу с произвольным количеством столбцов
    #40105604
Фотография Stax
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Stax
Участник
SY

вызывается SQL macro который конструирует SELECT с семью полями.
SY.

имхо

то что с макро не парсится, ето скорее косяк, чем достоинство

добавили б хинт (прагму) парсить в любом случае (независимо от наличия в шаред пуле)

.....
stax
...
Рейтинг: 0 / 0
20.10.2021, 14:28
| Ответить | Цитировать | Написать  
11 сообщений из 36, страница 2 из 2
  2  все
Форумы / Oracle [игнор отключен] [закрыт для гостей] / запрос возвращающий таблицу с произвольным количеством столбцов
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru, Чат Кролика       Пашэ: Рисунки, Чат       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=52&msg=40105563&tid=1879808]:
 0ms
get settings:
 8ms
get forum list:
 5ms
check forum access:
 1ms
check topic access:
 1ms
track hit:
 42ms
get topic data:
 2ms
get forum data:
 0ms
get page messages:
 196ms
get tp. blocked users:
 0ms
others: 290ms
total:  545ms
созд. / загр.: 545ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]