oracle pl/sql显示select语句的结果
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11615574/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Pl/SQL display results of select statement
提问by roymustang86
Set ServerOutput on size 100000;
declare
countTab number := 0;
countCol number := 0;
currDate varchar2(30);
scale number := 0;
Begin
select count(*) into countCol from USER_TAB_COLUMNS where TABLE_NAME = 'EVAPP_INTERFACE' and COLUMN_NAME = 'TARGET_AMNT_LTV_NUM' and DATA_SCALE is null;
IF (countCol <> 0) then
DBMS_OUTPUT.put_line(' EVAPP_INTERFACE.TARGET_AMNT_LTV_NUM values begin');
execute immediate 'select APPSEQNO, TARGET_AMNT_LTV_NUM from evapp_interface where TARGET_AMNT_LTV_NUM > 999999999999';
END IF;
END;
\
I am trying to display the results of the select query. I tried running just the select statements as is, but it gives an exception saying it can't find the columns mentioned. So, I tried putting the table name infront of the columns, and it complained that I needed to use INTO, and I used that as well, but still it did not like the syntax.
我正在尝试显示选择查询的结果。我尝试按原样运行 select 语句，但它给出了一个异常，说它找不到提到的列。所以，我尝试将表名放在列的INTO前面，它抱怨我需要使用，我也使用了它，但它仍然不喜欢语法。
采纳答案by Justin Cave
Assuming you are using SQL*Plus, the simplest option is probably to do something like
假设您使用的是 SQL*Plus，最简单的选择可能是执行类似的操作
Set ServerOutput on size 100000;
variable rc refcursor;
declare
countTab number := 0;
countCol number := 0;
currDate varchar2(30);
scale number := 0;
Begin
select count(*)
into countCol
from USER_TAB_COLUMNS
where TABLE_NAME = 'EVAPP_INTERFACE'
and COLUMN_NAME = 'TARGET_AMNT_LTV_NUM'
and DATA_SCALE is null;
IF (countCol <> 0) then
DBMS_OUTPUT.put_line(' EVAPP_INTERFACE.TARGET_AMNT_LTV_NUM values begin');
open :rc
FOR 'select APPSEQNO, TARGET_AMNT_LTV_NUM ' ||
' from evapp_interface ' ||
' where TARGET_AMNT_LTV_NUM > 999999999999';
END IF;
END;
/
PRINT rc;
If you want to display the result from PL/SQL, you'd need to open the cursor, fetch the results into local variables, and then do something with the local variables such as writing them to DBMS_OUTPUT.
如果要显示来自 PL/SQL 的结果，则需要打开游标，将结果提取到局部变量中，然后对局部变量执行一些操作，例如将它们写入DBMS_OUTPUT.
