Oracle cursor с параметрами
Summary: in this tutorial, you will learn how to use the PL/SQL cursor with parameters to fetch data based on parameters.
An explicit cursor may accept a list of parameters. Each time you open the cursor, you can pass different arguments to the cursor, which results in different result sets.
The following shows the syntax of a declaring a cursor with parameters:
In the cursor query, each parameter in the parameter list can be used anywhere which a constant is used. The cursor parameters cannot be referenced outside of the cursor query.
To open a cursor with parameters, you use the following syntax:
In this syntax, you passed arguments corresponding to the parameters of the cursor.
Cursors with parameters are also known as parameterized cursors.
Типичные операции с запросами и курсорами
Независимо от типа курсора процесс выполнения команд SQL всегда состоит из одних и тех же действий. В одних случаях PL/SQL производит их автоматически, а в других, как, например, при использовании явного курсора, они явно организуются программистом.
- Разбор. Первым шагом при обработке команды SQL должен быть ее разбор (синтаксический анализ), то есть проверка ее корректности и формирование плана выполнения (с применением оптимизации по синтаксису или по стоимости в зависимости от того, какое значение параметра 0PTIMIZER_M0DE задал администратор базы данных).
- Привязка. Привязкой называется установление соответствия между значениями программы и параметрами команды SQL. Для статического SQL привязка производится ядром PL/SQL . Привязка параметров в динамическом SQL выполняется явно с использованием переменных привязки.
- Открытие. При открытии курсора определяется результирующий набор строк команд SQL, для чего используются переменные привязки. Указатель активной или текущей строки указывает на первую строку результирующего набора. Иногда явное открытие курсора не требуется; ядро PL/SQL выполняет эту операцию автоматически (так происходит в случае применения неявных курсоров и встроенного динамического SQL ).
- Выполнение. На этой стадии команда выполняется ядром SQL .
- Выборка. Выборка очередной строки из результирующего набора строк курсора осуществляется командой FETCH . После каждой выборки PL/SQL перемещает указатель на одну строку вперед. Работая с явными курсорами, помните, что и после завершения перебора всех строк можно снова и снова выполнять команду FETCH , но PL/SQL ничего не будет делать (и не станет инициировать исключение) — для выявления этого условия следует использовать атрибуты курсора.
- Закрытие. Операция закрывает курсор и освобождает используемую им память. Закрытый курсор уже не содержит результирующий набор строк. Иногда явное закрытие курсора не требуется, последовательность PL/SQL делает это автоматически (для неявных курсоров и встроенного динамического SQL ).
На рис. 1 показано, как некоторые из этих операций используются для выборки информации из базы данных в программу PL/SQL .
Рис. 1. Упрощенная схема выборки данных с использованием курсора
To execute a multi-row query, Oracle opens an unnamed work area that stores processing information. A cursor lets you name the work area, access the information, and process the rows individually. For more information, see "Querying Data with PL/SQL".
cursor parameter declaration ::=
Keyword and Parameter Description
An explicit cursor previously declared within the current scope.
A type specifier. For the syntax of datatype , see "Constant and Variable Declaration".
A database table or view that must be accessible when the declaration is elaborated.
A combination of variables, constants, literals, operators, and function calls. The simplest expression consists of a single variable. When the declaration is elaborated, the value of expression is assigned to the parameter. The value and the parameter must have compatible datatypes.
A variable declared as the formal parameter of a cursor. A cursor parameter can appear in a query wherever a constant can appear. The formal parameters of a cursor must be IN parameters. The query can also reference other PL/SQL variables within its scope.
A user-defined record previously declared within the current scope.
A user-defined record type that was defined using the datatype specifier RECORD .
Specifies the datatype of a cursor return value. You can use the %ROWTYPE attribute in the RETURN clause to provide a record type that represents a row in a database table or a row returned by a previously declared cursor. Also, you can use the %TYPE attribute to provide the datatype of a previously declared record.
A cursor body must have a SELECT statement and the same RETURN clause as its corresponding cursor spec. Also, the number, order, and datatypes of select items in the SELECT clause must match the RETURN clause.
A record type that represents a row in a database table or a row fetched from a previously declared cursor or cursor variable. Fields in the record and corresponding columns in the row have the same names and datatypes.
A query that returns a result set of rows. Its syntax is like that of select_into_statement without the INTO clause. See "SELECT INTO Statement". If the cursor declaration declares parameters, each parameter must be used in the query.
Provides the datatype of a previously declared user-defined record.
You must declare a cursor before referencing it in an OPEN , FETCH , or CLOSE statement. You must declare a variable before referencing it in a cursor declaration. The word SQL is reserved by PL/SQL as the default name for implicit cursors, and cannot be used in a cursor declaration.
You cannot assign values to a cursor name or use it in an expression. However, cursors and variables follow the same scoping rules. For more information, see "Scope and Visibility of PL/SQL Identifiers".
You retrieve data from a cursor by opening it, then fetching from it. Because the FETCH statement specifies the target variables, using an INTO clause in the SELECT statement of a cursor_declaration is redundant and invalid.
The scope of cursor parameters is local to the cursor, meaning that they can be referenced only within the query used in the cursor declaration. The values of cursor parameters are used by the associated query when the cursor is opened. The query can also reference other PL/SQL variables within its scope.
The datatype of a cursor parameter must be specified without constraints, that is, without precision and scale for numbers, and without length for strings.
Курсор это средство извлечения данных из БД.
Курсоры содержат определения столбцов и объектов.
Курсоры используются для получения строк, возвращаемых запросом.
Для передачи строк в курсор используется запрос, после чего вы можете
выбирать строки из курсора по одной за один раз.
create table t1(id, type, text)
as
select object_id, object_type, object_name
from all_objects;
create table t1
as
select object_id id, object_type type, object_name text
from all_objects;
select id, type, text from t1
where SCHEDULE FILE_WATCHER_SCHEDULE
select id, type, text from t1
where type = 'SCHEDULE';
17364 SCHEDULE DAILY_PURGE_SCHEDULE
17367 SCHEDULE FILE_WATCHER_SCHEDULE
17372 SCHEDULE PMO_DEFERRED_GIDX_MAINT_SCHED
18172 SCHEDULE BSLN_MAINTAIN_STATS_SCHED
Неявные курсоры определяются в момент выполнения:
DECLARE
v_text t1.text%TYPE;
BEGIN
SELECT text INTO v_text
FROM t1
WHERE /> DBMS_OUTPUT.PUT_LINE( 'text = ' || v_text );
END;
/
В ходе выполнения кода создается курсор для выборки значения text.
Явный курсор определяется до начала выполнения:
DECLARE
CURSOR c_get_text
IS
SELECT text
FROM t1
WHERE v_text t1.text%TYPE;
BEGIN
OPEN c_get_text;
FETCH c_get_text INTO v_text;
DBMS_OUTPUT.PUT_LINE( 'text = ' || v_text );
CLOSE c_get_text;
END;
/
Преимущество явного курсора заключается в наличии у него атрибутов,
облегчающих применение условных операторов.
CREATE OR REPLACE PROCEDURE proc1
AS
CURSOR c_get_text
IS
SELECT text
FROM t1
WHERE v_text t1.text%TYPE;
BEGIN
OPEN c_get_text;
FETCH c_get_text INTO v_text;
IF c_get_text%NOTFOUND THEN
DBMS_OUTPUT.PUT_LINE( 'Данные не найдены. ' );
ELSE
DBMS_OUTPUT.PUT_LINE( 'text = ' || v_text );
END IF;
CLOSE c_get_text;
END;
/
А как подобное сделать с неявным курсором:
CREATE OR REPLACE PROCEDURE proc2
AS
v_text t1.text%TYPE;
v_bool BOOLEAN := TRUE;
BEGIN
BEGIN
SELECT text INTO v_text
FROM t1
WHERE EXCEPTION
WHEN no_data_found THEN
v_bool := FALSE;
WHEN others THEN
RAISE;
END;
IF NOT v_bool THEN
DBMS_OUTPUT.PUT_LINE( 'Данные не найдены. ' );
ELSE
DBMS_OUTPUT.PUT_LINE( 'text = ' || v_text );
END IF;
END;
/
Пришлось заниматься перехватом исключений, чтобы определить, была ли найдена запись.
Параметризация курсоров помогает повысить степень их повторного использования.
курсор с параметром:
DECLARE
CURSOR c_get_text(par1 NUMBER)
IS
SELECT text
FROM t1
WHERE v_text t1.text%TYPE;
BEGIN
OPEN c_get_text(17367);
FETCH c_get_text INTO v_text;
DBMS_OUTPUT.PUT_LINE( 'text = ' || v_text );
CLOSE c_get_text;
END;
/
Переменные типа REF CURSOR могут ссылаться на любые реальные курсоры.
Программа, использующая тип REF CURSOR, может работать с курсорами,
не заботясь о том, какие конкретно данные будут извлечены ими во время выполнения.
CREATE OR REPLACE PROCEDURE proc_ref
AS
v_curs SYS_REFCURSOR;
v_text t1.text%TYPE;
BEGIN
OPEN v_curs
FOR
'SELECT text '
|| 'FROM t1 '
|| 'WHERE FETCH v_curs INTO v_text;
DBMS_OUTPUT.PUT_LINE( 'text = ' || v_text );
Во время компиляции Oracle не знает, каким будет тексе запроса, - он видит строковую переменную.
Но наличие типа REF CURSOR говорит ему о том, что надо будет обеспечить некую работу с курсором.
Например я могу создать функцию, которая принимает некий входной параметр, создает курсор и возвращает тип REF CURSOR :
CREATE OR REPLACE FUNCTION func1(par1 NUMBER)
RETURN SYS_REFCURSOR
IS
v_curs SYS_REFCURSOR;
BEGIN
OPEN v_curs
FOR
'SELECT text '
|| 'FROM t1 '
|| 'WHERE /> || par1;
Другой пользователь может воспользоваться этой функцией так:
v_curs SYS_REFCURSOR;
v_text t1.text%TYPE;
BEGIN
v_curs := func1(17367);
FETCH v_curs INTO v_text;
IF v_curs%NOTFOUND THEN
DBMS_OUTPUT.PUT_LINE( 'Данные не найдены. ' );
ELSE
DBMS_OUTPUT.PUT_LINE( 'text = ' || v_text );
END IF;
Для пользователя, вызывающего функцию func1(), она для него представляет черный ящик, возвращающий курсор.
Сильнотипизированный и слаботипизированный REF CURSOR.
TYPE имя_типа_курсора IS REF CURSOR [ RETURN возвращаемый_тип ];
TYPE refcursor IS REF CURSOR RETURN table1%ROWTYPE;
TYPE refcursor IS REF CURSOR;
Первая форма REF CURSOR называется сильно типизированной, поскольку тип структуры,
возвращаемый курсорной переменной, задается в момент объявления
(непосредственно или путем привязки к типу строки таблицы).
Вторая форма (без предложения RETURN) называется слаботипизированной.
Тип возвращаемой структуры данных для нее не задается.
Такая курсорная переменная обладает большей гибкостью, поскольку для нее можно задавать любые запросы
с любой структурой возвращаемых данных.
В Oracle 9i появился предопределенный слабый тип REF CURSOR с именем SYS_REFCURSOR,
теперь можно не определять собственный слабый тип, достаточно использовать стандартный тип Oracle:
DECLARE
my_cursor SYS_REFCURSOR;
Пример сильнотипизированного курсора:
TYPE my_type_rec IS RECORD (text t1.text%TYPE);
TYPE my_type_cur IS REF CURSOR RETURN my_type_rec;
v_curs my_type_cur;
v_text t1.text%TYPE;
BEGIN
OPEN v_curs
FOR
SELECT text
FROM t1
WHERE FETCH v_curs INTO v_text;
DBMS_OUTPUT.PUT_LINE( 'text = ' || v_text );
TYPE my_type_cur IS REF CURSOR RETURN t1%ROWTYPE;
v_curs my_type_cur;
v_var t1%ROWTYPE;
BEGIN
OPEN v_curs
FOR
SELECT *
FROM t1
WHERE FETCH v_curs INTO v_var;
DBMS_OUTPUT.PUT_LINE( 'id = ' || v_var.id || ', type = ' || v_var.type || ', text = ' || v_var.text );
id = 17367, type = SCHEDULE, text = FILE_WATCHER_SCHEDULE
Пример слаботипизированного курсора:
TYPE my_type_cur IS REF CURSOR;
v_curs my_type_cur;
v_text t1.text%TYPE;
BEGIN
OPEN v_curs
FOR
SELECT text
FROM t1
WHERE FETCH v_curs INTO v_text;
DBMS_OUTPUT.PUT_LINE( 'text = ' || v_text );
v_curs SYS_REFCURSOR;
v_text t1.text%TYPE;
BEGIN
OPEN v_curs
FOR
SELECT text
FROM t1
WHERE FETCH v_curs INTO v_text;
DBMS_OUTPUT.PUT_LINE( 'text = ' || v_text );
Курсор можно передавать в качестве параметра:
1. Функция принимающая курсор
CREATE OR REPLACE FUNCTION get_cursor(p_curs SYS_REFCURSOR)
RETURN VARCHAR2
IS
v_text t1.text%TYPE;
FETCH p_curs INTO v_text;
IF p_curs%NOTFOUND THEN
DBMS_OUTPUT.PUT_LINE( 'Данные не найдены. ' );
ELSE
DBMS_OUTPUT.PUT_LINE( 'Данные найдены. ' );
END IF;
2. Процедура принимающая текст SQL
CREATE OR REPLACE PROCEDURE get_sql (p_sql VARCHAR2)
IS
v_curs SYS_REFCURSOR;
v_res VARCHAR2(50);
BEGIN
IF v_curs%ISOPEN THEN
CLOSE v_curs;
END IF;
BEGIN
OPEN v_curs FOR p_sql;
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20000, 'Unable to open cursor');
END;
v_res := get_cursor(v_curs);
CLOSE v_curs;
DBMS_OUTPUT.PUT_LINE(v_res);
END;
/
BEGIN
get_sql( 'SELECT text FROM t1 WHERE );
END;
/
Данные найдены.
FILE_WATCHER_SCHEDULE
Ещё примеры:
SET SERVEROUTPUT ON
var1 tab.col1%TYPE;
var2 tab.col2%TYPE;
var3 tab.col3%TYPE;
CURSOR cur IS
SELECT col1, col1, col3
FROM tab
ORDER BY col1;
BEGIN
-- Открываем курсор
LOOP
-- Выбираем из курсора строки
FETCH cur
INTO var1, var2, var3;
EXIT WHEN cur%NOTFOUND;
-- Выводим значения переменных
DBMS_OUTPUT.PUT_LINE( 'col1 = ' || var1 || ', col2 = ' || var2 || ', col3 = ' || var3 );
END LOOP;
-- Закрываем курсор
CLOSE cur;
END;
/
Курсоры и цикл FOR
Для получения доступа к строкам из курсора можно использовать цикл FOR.
При использовании цикла FOR не нужно явно открывать курсор - цикл FOR сделает это автоматически.
SET SERVEROUTPUT ON
CURSOR cur IS
SELECT col1, col1, col3
FROM tab
ORDER BY col1;
BEGIN
FOR var IN cur LOOP
DBMS_OUTPUT.PUT_LINE( 'col1 = ' || var.col1 || ', col2 = ' || var.col2 || ', col3 = ' || var.col3 );
END LOOP;
END;
/
Выражение OPEN - FOR
С курсором можно использовать выражение OPEN - FOR, которое добавляет еще больше гибкости при обработке курсоров,
поскольку вы можете назначить курсор для другого запроса.
Запрос может быть любым корректным выражением SELECT.
Это означает что вы можете повторно использовать курсор и назначить курсору позже в коде другой запрос.
SET SERVEROUTPUT ON
-- Определим тип REF CURSOR
TYPE t_cur IS
REF CURSOR RETURN tab%ROWTYPE;
-- Определим объект типа t_cur
cur t_cur;
-- Определим объект для хранения столбцов из таблицы tab
var tab%ROWTYPE;
BEGIN
-- назначим запрос для объекта cur и откроем его
OPEN cur FOR
SELECT * FROM tab WHERE col1 < 5;
-- Выбираем строки из cur в var
LOOP
FETCH cur INTO var;
EXIT WHEN cur%NOTFOUND;
DBMS_OUTPUT.PUT_LINE( 'col1 = ' || var.col1 || ', col2 = ' || var.col2 || ', col3 = ' || var.col3 );
END LOOP;
-- Закрываем объект cur
CLOSE cur;
END;
/
Все ранее рассмотренные курсоры имели конкретный возвращаемый тип, который должен совпадать
со столбцами в запросе исполняемом курсором.
Можно определить курсор, который не имеет возвращаемого типа и может исполнять любой запрос.
SET SERVEROUTPUT ON
DECLARE
-- Определим тип REF CURSOR
TYPE t_cur IS REF CURSOR;
-- Определим объект типа t_cur
cur t_cur;
-- Определим объект для хранения столбцов из таблицы tab1
var1 tab1%ROWTYPE;
-- Определим объект для хранения столбцов из таблицы tab2
var2 tab2%ROWTYPE;
BEGIN
-- назначим запрос для объекта cur и откроем его
OPEN cur FOR
SELECT * FROM tab1 WHERE col1 < 5;
-- Выбираем строки из cur в var1
LOOP
FETCH cur INTO var1;
EXIT WHEN cur%NOTFOUND;
DBMS_OUTPUT.PUT_LINE( 'col1 = ' || var1.col1 || ', col2 = ' || var1.col2 || ', col3 = ' || var1.col3 );
END LOOP;
-- назначим новый запрос для объекта cur и откроем его
OPEN cur FOR
SELECT * FROM tab2 WHERE col1 < 3;
-- Выбираем строки из cur в var2
LOOP
FETCH cur INTO var2;
EXIT WHEN cur%NOTFOUND;
DBMS_OUTPUT.PUT_LINE( 'col1 = ' || var2.col1 || ', col2 = ' || var2.col2 || ', col3 = ' || var2.col3 );
END LOOP;
"Explicit Cursors" for more information about explicit cursors
Oracle Database Development Guide for advantages of cursor variables
Oracle Database Development Guide for disadvantages of cursor variables
6.4.1 Creating Cursor Variables
To create a cursor variable, either declare a variable of the predefined type SYS_REFCURSOR or define a REF CURSOR type and then declare a variable of that type.
Informally, a cursor variable is sometimes called a REF CURSOR ).
The basic syntax of a REF CURSOR type definition is:
For the complete syntax and semantics, see "Cursor Variable Declaration" .
If you specify return_type , then the REF CURSOR type and cursor variables of that type are strong ; if not, they are weak . SYS_REFCURSOR and cursor variables of that type are weak.
With a strong cursor variable, you can associate only queries that return the specified type. With a weak cursor variable, you can associate any query.
Weak cursor variables are more error-prone than strong ones, but they are also more flexible. Weak REF CURSOR types are interchangeable with each other and with the predefined type SYS_REFCURSOR . You can assign the value of a weak cursor variable to any other weak cursor variable.
You can assign the value of a strong cursor variable to another strong cursor variable only if both cursor variables have the same type (not merely the same return type).
You can partition weak cursor variable arguments to table functions only with the PARTITION BY ANY clause, not with PARTITION BY RANGE or PARTITION BY HASH .
For syntax and semantics, see "PARALLEL_ENABLE Clause" .
Example 6-24 Cursor Variable Declarations
This example defines strong and weak REF CURSOR types, variables of those types, and a variable of the predefined type SYS_REFCURSOR .
Example 6-25 Cursor Variable with User-Defined Return Type
In this example, EmpRecTyp is a user-defined RECORD type.
6.4.2 Opening and Closing Cursor Variables
After declaring a cursor variable, you can open it with the OPEN FOR statement, which does the following:
Associates the cursor variable with a query (typically, the query returns multiple rows)
The query can include placeholders for bind variables, whose values you specify in the USING clause of the OPEN FOR statement.
Allocates database resources to process the query
Processes the query; that is:
Identifies the result set
If the query references variables, their values affect the result set. For details, see "Variables in Cursor Variable Queries" .
If the query has a FOR UPDATE clause, locks the rows of the result set
Positions the cursor before the first row of the result set
You need not close a cursor variable before reopening it (that is, using it in another OPEN FOR statement). After you reopen a cursor variable, the query previously associated with it is lost.
When you no longer need a cursor variable, close it with the CLOSE statement, thereby allowing its resources to be reused. After closing a cursor variable, you cannot fetch records from its result set or reference its attributes. If you try, PL/SQL raises the predefined exception INVALID_CURSOR .
You can reopen a closed cursor variable.
"OPEN FOR Statement" for its syntax and semantics
"CLOSE Statement" for its syntax and semantics
6.4.3 Fetching Data with Cursor Variables
After opening a cursor variable, you can fetch the rows of the query result set with the FETCH statement.
The return type of the cursor variable must be compatible with the into_clause of the FETCH statement. If the cursor variable is strong, PL/SQL catches incompatibility at compile time. If the cursor variable is weak, PL/SQL catches incompatibility at run time, raising the predefined exception ROWTYPE_MISMATCH before the first fetch.
"FETCH Statement" for its complete syntax and semantics
"FETCH Statement with BULK COLLECT Clause" for information about FETCH statements that return more than one row at a time
Example 6-26 Fetching Data with Cursor Variables
This example uses one cursor variable to do what Example 6-6 does with two explicit cursors. The first OPEN FOR statement includes the query itself. The second OPEN FOR statement references a variable whose value is a query.
Example 6-27 Fetching from Cursor Variable into Collections
This example fetches from a cursor variable into two collections (nested tables), using the BULK COLLECT clause of the FETCH statement.
6.4.4 Assigning Values to Cursor Variables
You can assign to a PL/SQL cursor variable the value of another PL/SQL cursor variable or host cursor variable.
If source_cursor_variable is open, then after the assignment, target_cursor_variable is also open. The two cursor variables point to the same SQL work area.
If source_cursor_variable is not open, opening target_cursor_variable after the assignment does not open source_cursor_variable .
6.4.5 Variables in Cursor Variable Queries
The query associated with a cursor variable can reference any variable in its scope.
When you open a cursor variable with the OPEN FOR statement, PL/SQL evaluates any variables in the query and uses those values when identifying the result set. Changing the values of the variables later does not change the result set.
To change the result set, you must change the value of the variable and then open the cursor variable again for the same query, as in Example 6-29.
Example 6-28 Variable in Cursor Variable Query—No Result Set Change
This example opens a cursor variable for a query that references the variable factor , which has the value 2. Therefore, sal_multiple is always 2 times sal , despite that factor is incremented after every fetch.
Example 6-29 Variable in Cursor Variable Query—Result Set Change
6.4.6 Querying a Collection
You can query a collection if all of the following are true:
The data type of the collection was either created at schema level or declared in a package specification.
The data type of the collection element is either a scalar data type, a user-defined type, or a record type.
In the query FROM clause, the collection appears in table_collection_expression as the argument of the TABLE operator.
In SQL contexts, you cannot use a function whose return type was declared in a package specification.
Oracle Database SQL Language Reference for information about the table_collection_expression
"CREATE PACKAGE Statement" for information about the CREATE PACKAGE statement
"PL/SQL Collections and Records" for information about collection types and collection variables
Example 7-9, "Querying a Collection with Native Dynamic SQL"
Example 6-30 Querying a Collection with Static SQL
In this example, the cursor variable is associated with a query on an associative array of records. The nested table type, mytab , is declared in a package specification.
6.4.7 Cursor Variable Attributes
A cursor variable has the same attributes as an explicit cursor (see Explicit Cursor Attributes.). The syntax for the value of a cursor variable attribute is cursor_variable_name immediately followed by attribute (for example, cv%ISOPEN ). If a cursor variable is not open, referencing any attribute except %ISOPEN raises the predefined exception INVALID_CURSOR .
6.4.8 Cursor Variables as Subprogram Parameters
You can use a cursor variable as a subprogram parameter, which makes it useful for passing query results between subprograms.
You can open a cursor variable in one subprogram and process it in a different subprogram.
In a multilanguage application, a PL/SQL subprogram can use a cursor variable to return a result set to a subprogram written in a different language.
The invoking and invoked subprograms must be in the same database instance. You cannot pass or return cursor variables to subprograms invoked through database links.
Because cursor variables are pointers, using them as subprogram parameters increases the likelihood of subprogram parameter aliasing, which can have unintended results. For more information, see "Subprogram Parameter Aliasing with Cursor Variable Parameters" .
When declaring a cursor variable as the formal parameter of a subprogram:
If the subprogram opens or assigns a value to the cursor variable, then the parameter mode must be IN OUT .
If the subprogram only fetches from, or closes, the cursor variable, then the parameter mode can be either IN or IN OUT .
Corresponding formal and actual cursor variable parameters must have compatible return types. Otherwise, PL/SQL raises the predefined exception ROWTYPE_MISMATCH .
To pass a cursor variable parameter between subprograms in different PL/SQL units, define the REF CURSOR type of the parameter in a package. When the type is in a package, multiple subprograms can use it. One subprogram can declare a formal parameter of that type, and other subprograms can declare variables of that type and pass them to the first subprogram.
"Subprogram Parameters" for more information about subprogram parameters
"CURSOR Expressions" for information about CURSOR expressions, which can be actual parameters for formal cursor variable parameters
PL/SQL Packages, for more information about packages
Example 6-31 Procedure to Open Cursor Variable for One Query
This example defines, in a package, a REF CURSOR type and a procedure that opens a cursor variable parameter of that type.
Example 6-32 Opening Cursor Variable for Chosen Query (Same Return Type)
In this example ,the stored procedure opens its cursor variable parameter for a chosen query. The queries have the same return type.
Example 6-33 Opening Cursor Variable for Chosen Query (Different Return Types)
In this example,the stored procedure opens its cursor variable parameter for a chosen query. The queries have the different return types.
6.4.9 Cursor Variables as Host Variables
You can use a cursor variable as a host variable, which makes it useful for passing query results between PL/SQL stored subprograms and their clients.
When a cursor variable is a host variable, PL/SQL and the client (the host environment) share a pointer to the SQL work area that stores the result set.
To use a cursor variable as a host variable, declare the cursor variable in the host environment and then pass it as an input host variable (bind variable) to PL/SQL. Host cursor variables are compatible with any query return type (like weak PL/SQL cursor variables).
A SQL work area remains accessible while any cursor variable points to it, even if you pass the value of a cursor variable from one scope to another. For example, in Example 6-34, the Pro*C program passes a host cursor variable to an embedded PL/SQL anonymous block. After the block runs, the cursor variable still points to the SQL work area.
If you have a PL/SQL engine on the client side, calls from client to server impose no restrictions. For example, you can declare a cursor variable on the client side, open and fetch from it on the server side, and continue to fetch from it on the client side. You can also reduce network traffic with a PL/SQL anonymous block that opens or closes several host cursor variables in a single round trip. For example:
Because the cursor variables still point to the SQL work areas after the PL/SQL anonymous block runs, the client program can use them. When the client program no longer needs the cursors, it can use a PL/SQL anonymous block to close them. For example:
This technique is useful for populating a multiblock form, as in Oracle Forms. For example, you can open several SQL work areas in a single round trip, like this:
If you bind a host cursor variable into PL/SQL from an Oracle Call Interface (OCI) client, then you cannot fetch from it on the server side unless you also open it there on the same server call.
Example 6-34 Cursor Variable as Host Variable in Pro*C Client Program
In this example, a Pro*C client program declares a cursor variable and a selector and passes them as host variables to a PL/SQL anonymous block, which opens the cursor variable for the selected query.
PL/SQL parameterized cursor with default values
A parameterized cursor can have default values for its parameters as shown below:
If you open the parameterized cursor without passing any argument, the cursor will use the default values for its parameters.
The following example shows how to use a parameterized cursor with default values.
In this example, we declared a parameterized cursor with default values. When we opened the cursor, we did not pass any arguments; therefore, the cursor used the default values, 2017 for in_year and 1 for in_customer_id .
Now, you should know how to use a PL/SQL cursor with parameters to fetch data from the database tables.
Одной из важнейших характеристик PL/SQL является тесная интеграция с базой данных Oracle в отношении как изменения данных в таблицах, так и выборки данных из таблиц. В этом блоге рассматриваются элементы PL/SQL , связанные с выборкой информации из базы данных и ее обработкой в программах PL/SQL .
При выполнении команды SQL из PL/SQL РСУБД Oracle назначает ей приватную рабочую область, а некоторые данные записывает в системную глобальную область (SGA, System Global Area). В приватной рабочей области содержится информация о команде SQL и набор данных, возвращаемых или обрабатываемых этой командой. PL/SQL предоставляет программистам несколько механизмов доступа к этой рабочей области и содержащейся в ней информации; все они так или иначе связаны с определением курсоров и выполнением операций с ними.
- Неявные курсоры. Команда SELECT .. . INTO считывает одну строку данных и присваивает ее в качестве значения локальной переменной программы. Это простейший (и зачастую наиболее эффективный) способ доступа к данным, но он часто ведет к написанию сходных и даже одинаковых SQL -команд SELECT во многих местах программы.
- Явные курсоры. Запрос можно явно объявить как курсор в разделе объявлений локального блока или пакета. После этого такой курсор можно будет открывать и выбирать из него данные в одной или нескольких программах, причем возможности управления явным курсором шире, чем у неявного.
- Курсорные переменные. Курсорные переменные (в объявлении которых задается тип REF CURSOR ) позволяют передавать из программы в программу указатель на результирующий набор строк запроса. Любая программа, для которой доступна такая переменная, может открыть курсор, извлечь из него необходимые данные и закрыть его.
- Курсорные выражения. Ключевое слово CURSOR превращает команду SELECT в набор REF CURSOR, который может использоваться совместно с табличными функциями для повышения производительности приложения.
- Динамические SQL -запросы. Oracle позволяет динамически конструировать и выполнять запросы с использованием либо встроенного динамического SQL либо программ пакета DMBS_SQL . Этот встроенный пакет описывается в документации Oracle, а также в книге Oracle Built-in Packages (O’Reilly).
6.6 Transaction Processing and Control
Transaction processing is an Oracle Database feature that lets multiple users work on the database concurrently, and ensures that each user sees a consistent version of data and that all changes are applied in the right order.
A transaction is a sequence of one or more SQL statements that Oracle Database treats as a unit: either all of the statements are performed, or none of them are.
Different users can write to the same data structures without harming each other's data or coordinating with each other, because Oracle Database locks data structures automatically. To maximize data availability, Oracle Database locks the minimum amount of data for the minimum amount of time.
You rarely must write extra code to prevent problems with multiple users accessing data concurrently. However, if you do need this level of control, you can manually override the Oracle Database default locking mechanisms.
PL/SQL cursor with parameters example
The following example illustrates how to use a cursor with parameters:
In this example:
- First, declare a cursor that accepts two parameters low price and high price. The cursor retrieves products whose prices are between the low and high prices.
- Second, open the cursor and pass the low and high prices as 50 and 100 respectively. Then fetch each row in the cursor and show the product’s information, and close the cursor.
- Third, open the cursor for the second time but with different arguments, 800 for the low price and 100 for the high price. Then the rest is fetching data, printing out product’s information, and closing the cursor.
Основные принципы работы с курсорами
Курсор проще всего представить себе как указатель на таблицу в базе данных. Например, следующее объявление связывает всю таблицу employee с курсором employee_cur :
Объявленный курсор можно открыть:
Далее из него можно выбирать строки:
Завершив работу с курсором, его следует закрыть:
В этом случае каждая выбранная из курсора запись представляет строку таблицы employee. Однако с курсором можно связать любую допустимую команду SELECT . В следующем примере в объявлении курсора объединяются три таблицы:
В данном случае курсор действует не как указатель на конкретную таблицу базы данных — он указывает на виртуальную таблицу или неявное представление, определяемое командой SELECT . (Такая таблица называется виртуальной, потому что команда SELECT генерирует данные с табличной структурой, но эта таблица существует только временно, пока программа работает с возвращенными командой данными.) Если тройное объединение возвращает таблицу из 20 строк и 3 столбцов, то курсор действует как указатель на эти 20 строк.
Терминология
В PL/SQL имеется множество возможностей выполнения команд SQL , и все они реализованы в программах как курсоры того или иного типа. Прежде чем приступить к их освоению, необходимо познакомиться с методами выборки данных и используемой при этом терминологией.
- Статический SQL . Команда SQL называется статической, если она полностью определяется во время компиляции программы.
- Динамический SQL . Команда SQL называется динамической, если она строится и выполняется на стадии выполнения программы, так что в программном коде нет ее фиксированного объявления. Для динамического выполнения команд SQL могут использоваться программы встроенного пакета DBMS_SQL (имеющегося во всех версиях Oracle) или встроенный динамический SQL .
- Результирующий набор строк. Набор строк с результирующими данными, удовлетворяющими критериям, определяемым командой SQL. Результирующий набор кэшируется в системной глобальной области с целью ускорения чтения и модификации его данных.
- Неявный курсор. При каждом выполнении команды DML ( INSERT, UPDATE, MERGE или delete) или команды select into, возвращающей строку из базы данных прямо в структуру данных программы, PL/SQL создает неявный курсор. Курсор этого типа называется неявным, поскольку Oracle автоматически выполняет многие связанные с ним операции, такие как открытие, выборка данных и даже закрытие.
- Явный курсор. Команда SELECT , явно определенная в программе как курсор. Все операции с явным курсором (открытие, выборка данных, закрытие и т. д.) в программе должны выполняться явно. Как правило, явные курсоры используются для выборки из базы данных набора строк с использованием статического SQL.
- Курсорная переменная. Объявленная программистом переменная, указывающая на объект курсора в базе данных. Ее значение (то есть указатель на курсор или результирующий набор строк) во время выполнения программы может меняться, как у всех остальных переменных. В разные моменты времени курсорная переменная может указывать на разные объекты курсора. Курсорную переменную можно передать в качестве параметра процедуре или функции. Такие переменные очень полезны для передачи результирующих наборов из программ PL/SQL в другие среды (например, Java или Visual Basic).
- Атрибут курсора. Атрибут курсора имеет форму %имя_атрибута и добавляется к имени курсора или курсорной переменной. Это что-то вроде внутренней переменной Oracle, возвращающей информацию о состоянии курсора — например о том, открыт ли курсор, или сколько строк из курсора вернул запрос. У явных и неявных курсоров и в динамическом SQL в атрибутах курсоров существуют некоторые различия, которые рассматриваются в этой статье.
- SELECT FOR UPDATE. Разновидность обычной команды SELECT , устанавливающая блокировку на каждую возвращаемую запросом строку данных. Пользоваться ею следует только в тех случаях, когда нужно «зарезервировать» запрошенные данные, чтобы никто другой не мог изменить их, пока с ними работаете вы.
- Пакетная обработка. В Oracle8i и выше PL/SQL поддерживает запросы с секцией BULK COLLECT , позволяющей за один раз выбрать из базы данных более одной строки.
6.5 CURSOR Expressions
A CURSOR expression returns a nested cursor.
It has this syntax:
You can use a CURSOR expression in a SELECT statement that is not a subquery (as in Example 6-35) or pass it to a function that accepts a cursor variable parameter (see "Passing CURSOR Expressions to Pipelined Table Functions" ). You cannot use a cursor expression with an implicit cursor.
Oracle Database SQL Language Reference for more information about CURSOR expressions, including restrictions
Example 6-35 CURSOR Expression
This example declares and defines an explicit cursor for a query that includes a cursor expression. For each department in the departments table, the nested cursor returns the last name of each employee in that department (which it retrieves from the employees table).
Читайте также: