Oracle - consultas dinamicas

1. necesito una definicion formal de que es una consulta dinamica
2. consulta dinamica es lo mismo que consulta parametrizada?diferencias?
3. maneras de interactuar con SMBD
gracias

1. necesito una definicion formal de que es una consulta dinamica
2. consulta dinamica es lo mismo que consulta parametrizada?diferencias?
3. maneras de interactuar con SMBD

1. necesito una definicion formal de que es una consulta dinamica

What Are Cursor Variables?

Cursor variables are like C or Pascal pointers, which hold the memory location (address) of some item instead of the item itself. So, declaring a cursor variable creates a pointer, not an item. In PL/SQL, a pointer has datatype REF X, where REF is short for REFERENCE and X stands for a class of objects. Therefore, a cursor variable has datatype REF CURSOR.

To execute a multi-row query, Oracle opens an unnamed work area that stores processing information. To access the information, you can use an explicit cursor, which names the work area. Or, you can use a cursor variable, which points to the work area.

Whereas a cursor always refers to the same query work area, a cursor variable can refer to different work areas. So, cursors and cursor variables are not interoperable; that is, you cannot use one where the other is expected.

Why Use Cursor Variables?

Mainly, you use cursor variables to pass query result sets between PL/SQL stored subprograms and various clients. Neither PL/SQL nor any of its clients owns a result set; they simply share a pointer to the query work area in which the result set is stored. For example, an OCI client, Oracle Forms application, and Oracle server can all refer to the same work area.

A query work area remains accessible as long as any cursor variable points to it. Therefore, you can pass the value of a cursor variable freely from one scope to another. For example, if you pass a host cursor variable to a PL/SQL block embedded in a Pro*C program, the work area to which the cursor variable points remains accessible after the block completes.

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, then continue to fetch from it back on the client side.

Also, you can reduce network traffic by having a PL/SQL block open (or close) several host cursor variables in a single round trip.

2. consulta dinamica es lo mismo que consulta parametrizada?diferencias?
NO

Un ejemplo ... por algun motivo tienes separados los empleados en EMP1 y EMP2 ...

CREATE OR REPLACE PROCEDURE PROCESA ( PTABLA VARCHAR2) IS
TYPE tipo_cursor IS REF CURSOR;
v_cursor tipo_cursor;
v_query VARCHAR2(15000);
vid number;
vname varchar2(40);
tabla varchar2(40);
BEGIN
v_query := 'SELECT EMPID, ENAME FROM ' || PTABLA ;
OPEN v_cursor FOR v_query;
LOOP
FETCH v_cursor INTO vid, vname;
EXIT WHEN v_cursor%NOTFOUND;
-- PROCESAR INFORMACION ...
END LOOP;
END;
/

EXEC PROCESA('EMP1')

EXEC PROCESA('EMP2')