tipos - ¿Es posible emitir un estado SELECT desde un bloque PL/SQL?
procedimientos y funciones oracle pl/sql (10)
¿Cómo puedo obtener un bloque PL / SQL para generar los resultados de una instrucción SELECT la misma manera que si hubiera hecho un SELECT simple?
Por ejemplo, cómo hacer un SELECT como:
SELECT foo, bar FROM foobar;
Insinuación :
BEGIN
SELECT foo, bar FROM foobar;
END;
no funciona
Crea una función en un paquete y devuelve un SYS_REFCURSOR:
FUNCTION Function1 return SYS_REFCURSOR IS
l_cursor SYS_REFCURSOR;
BEGIN
open l_cursor for SELECT foo,bar FROM foobar;
return l_cursor;
END Function1;
Depende de para qué necesitas el resultado.
Si está seguro de que va a haber solo 1 fila, use el cursor implícito:
DECLARE
v_foo foobar.foo%TYPE;
v_bar foobar.bar%TYPE;
BEGIN
SELECT foo,bar FROM foobar INTO v_foo, v_bar;
-- Print the foo and bar values
dbms_output.put_line(''foo='' || v_foo || '', bar='' || v_bar);
EXCEPTION
WHEN NO_DATA_FOUND THEN
-- No rows selected, insert your exception handler here
WHEN TOO_MANY_ROWS THEN
-- More than 1 row seleced, insert your exception handler here
END;
Si desea seleccionar más de 1 fila, puede usar un cursor explícito:
DECLARE
CURSOR cur_foobar IS
SELECT foo, bar FROM foobar;
v_foo foobar.foo%TYPE;
v_bar foobar.bar%TYPE;
BEGIN
-- Open the cursor and loop through the records
OPEN cur_foobar;
LOOP
FETCH cur_foobar INTO v_foo, v_bar;
EXIT WHEN cur_foobar%NOTFOUND;
-- Print the foo and bar values
dbms_output.put_line(''foo='' || v_foo || '', bar='' || v_bar);
END LOOP;
CLOSE cur_foobar;
END;
o usa otro tipo de cursor:
BEGIN
-- Open the cursor and loop through the records
FOR v_rec IN (SELECT foo, bar FROM foobar) LOOP
-- Print the foo and bar values
dbms_output.put_line(''foo='' || v_rec.foo || '', bar='' || v_rec.bar);
END LOOP;
END;
Desde un bloque anónimo? Me gustaría ahora más sobre la situación en la que piensas que es necesario, porque con las cláusulas de factorización de subconsulta y vistas en línea es bastante raro que necesites recurrir a PL / SQL para cualquier cosa que no sea la situación más compleja.
Si puede usar un procedimiento con nombre, entonces use funciones segmentadas. Aquí hay un ejemplo extraído de la documentación:
CREATE PACKAGE pkg1 AS
TYPE numset_t IS TABLE OF NUMBER;
FUNCTION f1(x NUMBER) RETURN numset_t PIPELINED;
END pkg1;
/
CREATE PACKAGE BODY pkg1 AS
-- FUNCTION f1 returns a collection of elements (1,2,3,... x)
FUNCTION f1(x NUMBER) RETURN numset_t PIPELINED IS
BEGIN
FOR i IN 1..x LOOP
PIPE ROW(i);
END LOOP;
RETURN;
END;
END pkg1;
/
-- pipelined function is used in FROM clause of SELECT statement
SELECT * FROM TABLE(pkg1.f1(5));
El clásico bloque "Hello World!" Contiene una sección ejecutable que llama al procedimiento DBMS_OUTPUT.PUT_LINE para mostrar texto en la pantalla:
BEGIN
DBMS_OUTPUT.put_line (''Hello World!'');
END;
Puede verificarlo aquí: http://www.oracle.com/technetwork/issue-archive/2011/11-mar/o21plsql-242570.html
Incluso si la pregunta es antigua, pero compartiré la solución que responde perfectamente a la pregunta:
SET SERVEROUTPUT ON;
DECLARE
RC SYS_REFCURSOR;
Result1 varchar2(25);
Result2 varchar2(25);
BEGIN
OPEN RC FOR SELECT foo, bar into Result1, Result2 FROM foobar;
DBMS_SQL.RETURN_RESULT(RC);
END;
Necesita usar SQL dinámico nativo. Además, no necesita BEGIN-END para ejecutar el comando SQL:
declare
l_tabname VARCHAR2(100) := ''dual'';
l_val1 VARCHAR2(100):= ''''''foo'''''';
l_val2 VARCHAR2(100):= ''''''bar'''''';
l_sql VARCHAR2(1000);
begin
l_sql:= ''SELECT ''||l_val1||'',''||l_val2||'' FROM ''||l_tabname;
execute immediate l_sql;
dbms_output.put_line(l_sql);
end;
/
Output:
SELECT ''foo'',''bar'' FROM dual
Para versiones inferiores a 12c, la respuesta simple es NO , al menos no en la forma en que se está haciendo es SQL Server.
Puede imprimir los resultados, puede insertar los resultados en tablas, puede devolver los resultados como cursores dentro de la función / procedimiento o devolver un conjunto de filas desde la función -
pero no puede ejecutar la instrucción SELECT, sin hacer algo con los resultados.
servidor SQL
begin
select 1+1
select 2+2
select 3+3
end
/ * 3 conjuntos de resultados devueltos * /
Oráculo
SQL> begin
2 select 1+1 from dual;
3 end;
4 /
select * from dual;
*
ERROR at line 2:
ORA-06550: line 2, column 1:
PLS-00428: an INTO clause is expected in this SELECT statement
Puede hacer esto en Oracle 12.1 o superior:
declare
rc sys_refcursor;
begin
open rc for select * from dual;
dbms_sql.return_result(rc);
end;
No tengo una base de datos 12.x o DBVisualizer para probar, pero ese debería ser probablemente su punto de partida.
Para obtener más detalles, consulte Conjuntos de resultados implícitos en la Guía de nuevas características de Oracle 12.1 , Blog de Tom Kyte , Base de Oracle, etc.
Para versiones anteriores, dependiendo de la herramienta, es posible que pueda usar las variables de vinculación del cursor de ref. Como este ejemplo de SQL * Plus:
set autoprint on
var rc refcursor
begin
open :rc for select count(*) from dual;
end;
/
PL/SQL procedure successfully completed.
COUNT(*)
----------
1
1 row selected.
si desea ver la salida de selección de la consulta en pl / sql, necesita usar un cursor explícito. Que contendrá el conjunto de datos activo y, al buscar cada fila a la vez, mostrará todo el registro del conjunto de datos activo, siempre que recupere el registro del conjunto de datos al iterar en bucle. Esta información no se generará en formato tabular, este resultado será en formato de texto plano. Esperamos que esto sea útil. Para cualquier otra consulta, puede preguntar ....
set serveroutput on;
declare
cursor c1 is
select foo, bar from foobar;
begin
for i in c1 loop
dbms_output.put_line(i.foo || '' '' || i.bar);
end loop;
end;
utilizar ejecutar declaración inmediata
me gusta:
declare
var1 integer;
var2 varchar2(200)
begin
execute immediate ''select emp_id,emp_name from emp''
into var1,var2;
dbms_output.put_line(var1 || var2);
end;