Oracle 5.0 Reference Manual Download

Page: 3003 / 3232

Restrictions on Server-Side Cursors

2983

The behavior that variables take precedence over table columns is nonstandard.

Use of stored routines can cause replication problems. This issue is discussed further in

Section 18.6,

“Binary Logging of Stored Programs”

INFORMATION_SCHEMA

does not have a

PARAMETERS

table until MySQL 5.5, so applications that

need to acquire routine parameter information at runtime must use workarounds such as parsing
the output of

SHOW CREATE

statements or the

param_list

column of the

mysql.proc

table.

param_list

contents can be processed from within a stored routine, unlike the output from

SHOW

The

--replicate-wild-do-table=db_name.tbl_name

[1459]

option applies to tables, views,

and triggers. It does not apply to stored procedures and functions, or events. To filter statements
operating on the latter objects, use one or more of the

--replicate-*-db

options.

There are no stored routine debugging facilities.

Before MySQL 5.0.17,

CALL

statements cannot be prepared. This true both for server-side prepared

statements and for SQL prepared statements.

MySQL does not support

UNDO

handlers.

MySQL does not support

FOR

loops.

To prevent problems of interaction between server threads, when a client issues a statement, the
server uses a snapshot of routines and triggers available for execution of the statement. That is, the
server calculates a list of procedures, functions, and triggers that may be used during execution of
the statement, loads them, and then proceeds to execute the statement. This means that while the
statement executes, it will not see changes to routines performed by other threads.

For triggers, the following additional restrictions apply:

• Triggers currently are not activated by foreign key actions.

• The

RETURN

statement is not permitted in triggers, which cannot return a value. To exit a trigger

immediately, use the

LEAVE

statement.

• Triggers are not permitted on tables in the

mysql

database.

• The trigger cache does not detect when metadata of the underlying objects has changed. If a trigger

uses a table and the table has changed since the trigger was loaded into the cache, the trigger
operates using the outdated metadata.

E.2. Restrictions on Server-Side Cursors

Server-side cursors are implemented beginning with the C API in MySQL 5.0.2 using the

mysql_stmt_attr_set()

function. A server-side cursor enables a result set to be generated on the

server side, but not transferred to the client except for those rows that the client requests. For example,
if a client executes a query but is only interested in the first row, the remaining rows are not transferred.

In MySQL, a server-side cursor is materialized into an internal temporary table. Initially, this is a

MEMORY

table, but is converted to a

MyISAM

table when its size exceeds the minimum value of the

max_heap_table_size

[468]

and

tmp_table_size

[501]

system variables. Note that the same

restrictions apply to internal temporary tables created to hold the result set for a cursor as for other
uses of internal temporary tables. See

Section 8.8.5, “How MySQL Uses Internal Temporary Tables”

(Beginning with MySQL 5.0.14, the same temporary-table implementation also is used for cursors in
stored routines.) One limitation of the implementation is that for a large result set, retrieving its rows
through a cursor might be slow.

Oracle 5.0, Reference Manual

Search results

Summary of Contents for 5.0

Reviews:

Related manuals for 5.0

Brands by name

Popular brands