Эффективное программирование на PL/SQL - Взаимозаменяемость динамического курсора и REF-курсора
ОГЛАВЛЕНИЕ
Взаимозаменяемость динамического курсора и REF-курсора
Вы знаете, каким полезным может быть Native Dynamic Cursor, особенно, когда до вызова не знаешь точно, что будет запрашиваться. Динамический PL/SQL можно также использовать через DBMS_SQL. Оба метода имеют свои преимущества. Но что будет, если вы начали разрабатывать программу, в которой используется сначала один метод, а затем необходимо переключиться на другой?
В Oracle Database 11 g этот процесс необыкновенно прост. Поддерживаемый пакет DBMS_SQL имеет новую функцию, TO_REFCURSOR, которая конвертирует динамический курсор DBMS_SQL в ref-курсор. Вот пример такой конвертации:
1 create or replace procedure list_trans_by_store
2 (
3 p_store_id number
4 )
5 is
6 type num_tab is table of number index by binary_integer;
7 type type_refcur is ref cursor;
8 c_ref_trans_cur type_refcur;
9 c_trans_cur number;
10 trans_id num_tab;
11 trans_amt num_tab;
12 ret integer;
13 l_stmt clob;
14 begin
15 c_trans_cur := dbms_sql.open_cursor;
16 l_stmt :=
17 'select trans_id, trans_amt from trans where store_id = :store_id';
18 dbms_sql.parse(c_trans_cur, l_stmt, dbms_sql.native);
19 dbms_sql.bind_variable(c_trans_cur, 'store_id', p_store_id);
20 ret := dbms_sql.execute(c_trans_cur);
21 c_ref_trans_cur := dbms_sql.to_refcursor(c_trans_cur);
22 fetch c_ref_trans_cur bulk collect into trans_id, trans_amt;
23 for ctr in 1.. trans_id.count loop
24 dbms_output.put_line(trans_id(ctr) // ' ' // trans_amt(ctr));
25 end loop;
26 close c_ref_trans_cur;
27* end;
Предположим, нужно написать общую процедуру, которая не знает списка столбцов в select-выражении во время компиляции. Это тот случай, когда native dynamic SQL становится необходимым. Можно описать для него ref-курсор. Теперь, чтобы стало интереснее, предположим, что вы не знаете всех bind-переменных, для этого случая больше всего подходит dbms_sql. Как выполнить это сложное требование, написав минимум кода? Просто: начните с dbms_sql для bind-переменных, а затем конвертируйте в ref-курсор.
Аналогично, чтобы конвертировать Native Dynamic SQL в REF-курсор, необходимо вызвать другую функцию, TO_CURSOR_NUMBER:
cur_handle := dbms_sql.to_cursor_number (c_ref_cur);
Ref-курсор, определённый в переменной c_ref_cur, должен быть открыт раньше этого вызова. После этого вызова жизнь ref-курсора закончена; манипулировать можно только dbms_sql-курсором.
Предположим, что вы знаете bind-переменные во время компиляции, но не знаете списка select; вы можете начать с native dynamic sql, с ref-курсора, а потом заменить его на dbms_sql, чтобы описать и извлечь столбцы из курсора.
Заключение
Как видите, Oracle Database 11 g содержит несколько улучшений, которые помогают писать код на PL/SQL более эффективно.