需要把select 的结果保存到变量 select * into .. from bcs.BCS_BANK_TRANSFER_DETIAL
where(TransferDateTime>=starttime and CancelFlag > '0' and CancelFlag < '9');
else
select * into .. from bcs.BCS_BANK_TRANSFER_DETIAL
where (TransferDateTime<=(sysdate-(1/24)*(1/6)) and TransferDateTime>starttime and CancelFlag>'0' and CancelFlag<'5');
where(TransferDateTime>=starttime and CancelFlag > '0' and CancelFlag < '9');
else
select * into .. from bcs.BCS_BANK_TRANSFER_DETIAL
where (TransferDateTime<=(sysdate-(1/24)*(1/6)) and TransferDateTime>starttime and CancelFlag>'0' and CancelFlag<'5');
在存储过程在,需要用游标
CURSOR C_CONS IS
select * from bcs.BCS_BANK_TRANSFER_DETIAL
建议不用采用select *,只需要将你需要的字段取出来并赋予变量值,
selct a,b,c into @a,@b,@c from ……
declare
t_1 bcs.BCS_BANK_TRANSFER_DETIAL%rowtype;
begin
select * into t_1 from bcs.BCS_BANK_TRANSFER_DETIAL
where(TransferDateTime>=starttime and CancelFlag > '0' and CancelFlag < '9');使用该变量时
用t1.colname的形式使用
declare
t_1 bcs.BCS_BANK_TRANSFER_DETIAL%rowtype;
begin
select * into t_1 from bcs.BCS_BANK_TRANSFER_DETIAL
where(TransferDateTime>=starttime and CancelFlag > '0' and CancelFlag < '9');使用该变量时
用t1.colname的形式使用
谢谢这位大侠!可在调试时总出错(在sql*plus里)如下:
declare
*
ERROR 位于第 1 行:
ORA-01403: 未找到数据
ORA-06512: 在line 9
不知是何意?
谢谢大家!!!
AS
TYPE myrctype IS REF CURSOR;
END pkg_test;
/
create procedure pro(starttime in date,p_rc out pkg_test.myrctype)
is
str varchar2(100);
begin
if (TO_NUMBER(TO_CHAR(sysdate,'HH24'))>=21) then
str:='select * from bcs.BCS_BANK_TRANSFER_DETIAL
where(TransferDateTime>=starttime and CancelFlag > ''0'' and CancelFlag < ''9'')';
else
str:='select * from bcs.BCS_BANK_TRANSFER_DETIAL
where (TransferDateTime<=(sysdate-(1/24)*(1/6)) and TransferDateTime>starttime and CancelFlag>''0'' and CancelFlag<''5'')';
end if;
open p_rc for str;
end;
/
你好!你的代码我编译了,结果却报错了,愿闻其详!!
主要看这些字符串是否正确
'select * from bcs.BCS_BANK_TRANSFER_DETIAL
where(TransferDateTime>=starttime and CancelFlag > ''0'' and CancelFlag < ''9'')'