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;
\
我试图显示选择查询的结果.我尝试按原样运行select语句,但它给出了一个例外,说它无法找到所提到的列.所以,我尝试将表名放在列的前面,并且它抱怨我需要使用INTO,我也使用它,但它仍然不喜欢语法.
解决方法
假设您正在使用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;
如果要显示PL / sql的结果,则需要打开游标,将结果提取到局部变量中,然后对局部变量执行某些操作,例如将它们写入DBMS_OUTPUT.
