vacio - rowcount oracle cursor

Agrega dinámicamente las cláusulas where a un cursor en el oráculo

Question

Tengo un procedimiento plsql que acepta ciertos parámetros, por ejemplo, v_name, v_country, v_type.

Deseo tener un cursor con una instrucción select como esta:

select column from table1 t1, table2 t2
where t1.name = v_name
and t1.country = v_country
and t1.id = t2.id
and t2.type = v_type

Si ciertos parámetros están vacíos, ¿puedo agregar únicamente las cláusulas where relevantes al cursor? ¿O hay una mejor manera de lograr esto?

Answer 1

La mejor forma de usar esto es con DBMS_SQL.

Usted crea una cadena que representa su declaración de SQL. Aún usas variables de vinculación. Es doloroso.

Es algo como esto (no lo he compilado, pero debería estar cerca): -

CREATE OR REPLACE FUNCTION find_country( v_name t1.country%TYPE,
v_type t2.type%TYPE) /* Hmm, column called type? */
DECLARE
v_SQL varchar2(2000);
v_select INTEGER; /* "Pointer" to a DBMS_SQL select statement */
v_execute INTEGER;
BEGIN
v_SQL := ''select column from table1 t1, table2 t2 ||
''where t1.id = t2.id'';
IF v_name IS NOT NULL THEN
v_SQL := v_SQL || '' AND t1.country = :v_name''
END IF;
IF v_type IS NOT NULL THEN
v_SQL := v_SQL || '' AND t2.type = :v_type'';
END IF;
/* Setup Cursor */
v_select := dbms_sql.open_cursor;
dbms_sql.parse( v_select, v_SQL, DBMS_SQL.native);
IF v_name IS NOT NULL THEN
dbms_sql.bind_variable( v_select, '':v_name'', v_name );
END IF;
IF v_type IS NOT NULL THEN
dbms_sql.bind_variable( v_select, '':v_type'', v_type );
END IF;
DBMS_SQL.DEFINE_COLUMN(v_select, 1, v_column); /* This is what we have selected */
/* Return value from EXECUTE is undefined for a SELECT */
v_execute := DBMS_SQL.EXECUTE( v_select );
IF DBMS_SQL.FETCH_ROWS( v_select ) > 0 THEN
/* A row was found
DBMS_SQL.COLUMN_VALUE( v_select, 1, v_column);
/* Tidy Up */
DBMS_SQL.CLOSE_CURSOR(v_select);
RETURN v_ID_address;
ELSE
DBMS_SQL.CLOSE_CURSOR(v_select);
/* No row */
RETURN NULL;
END IF;
EXCEPTION
WHEN OTHERS THEN
IF DBMS_SQL.IS_open(v_select) THEN
DBMS_SQL.CLOSE_CURSOR(v_select);
END IF;
RAISE;
END;

Este enfoque es tan doloroso en comparación con simplemente escribir el SQL en línea que, a menos que tenga montones de columnas, a veces es más fácil escribir un par de versiones diferentes con esta sintaxis:

FOR r IN (SELECT blah FROM blah WHERE t1 = v_t1) LOOP
func( r.blah );
END LOOP;

Answer 2

No es directamente lo que estás preguntando, pero puede ser una solución aceptable:

select column from table1 t1, table2 t2
where
(v_name is null or t1.name = v_name)
and (v_country is null or t1.country = v_country)
and t1.id = t2.id
and (v_type is null or t2.type = v_type)

Answer 3

Una forma sería construir su consulta como una cadena y luego ejecutar ejecutar

Answer 4

La mejor manera de hacerlo sería utilizar la característica Contexto de la aplicación de Oracle, que se define mejor como el mejor rendimiento y seguridad.

La manera más rápida sería lo que sugirió hamishmcn, usando EXECUTE INMEDIATA. Elegiría eso a través de la sugerencia de DB de DBMS_SQL cada vez .

Otra forma más rápida de escribir pero que no funcionará bien sería algo como esto:

select column from table1 t1, table2 t2
where t1.name = nvl(v_name, t1.name)
and t1.country = nvl(v_country, t1.country)
and t1.id = t2.id
and t2.type = nvl(v_type, t2.type)

Answer 5

No necesita usar dbms_sql para resolver este problema y aún puede usar el cursor normal usando un cursor de ref.

Muestra:

DECLARE
TYPE cursor_ref IS REF CURSOR;
c1 cursor_ref;
r1 table1.column%type;
BEGIN
l_sql := ''select t1.column from table1 t1, table2 t2 where t1.id = t2.id '';
if v_name is not null then
l_sql := l_sql||'' and t1.name = ''||v_name ;
end if;
if v_country is not null then
l_sql := l_sql||'' and t1.country = ''||v_country'';
end if;
if v_type is not null then
l_sql := l_sql||'' and t2.type = ''||v_type'';
end if;
open c1 for l_sql;
loop
fetch c1 into r1;
exit when c1%notfound;
-- do something
end loop;
close c1;
end;
/

Puede mejorar esto al vincular las variables con el comando ''usar'' de esta manera:

open c1 for l_sql using v_name, v_country;