Эффективное программирование на 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 более эффективно.