cursor CUR_QASSET is
select p.PROD_NAME,
cts.STATUS_CD,
bucket_id,
a.price,
a.serial_num
from RM_ASSET a,
RM_BUCKET B,
RM_INVENTORY i,
RM_PRODUCT p,
RM_CLASS_STAT cts,
where a.bucket_id = b.row_id
and b.inv_id = i.row_id
and p.row_id = b.prod_id
and cts.row_id = b.class_stat_id and rownum<=qty;
这是我放在存储过程里的一个游标之前where后面没有加rownum<=qty (qty是我传入的一个参数),可以正确读出信息这条select语句,我放在外面单独执行也是没有问题的,但是为什么放在游标里就出现了这个问题呢?
select p.PROD_NAME,
cts.STATUS_CD,
bucket_id,
a.price,
a.serial_num
from RM_ASSET a,
RM_BUCKET B,
RM_INVENTORY i,
RM_PRODUCT p,
RM_CLASS_STAT cts,
where a.bucket_id = b.row_id
and b.inv_id = i.row_id
and p.row_id = b.prod_id
and cts.row_id = b.class_stat_id and rownum<=qty;
这是我放在存储过程里的一个游标之前where后面没有加rownum<=qty (qty是我传入的一个参数),可以正确读出信息这条select语句,我放在外面单独执行也是没有问题的,但是为什么放在游标里就出现了这个问题呢?
select * from (select .... from order by ....) where rownum<10;
cursor CUR_QASSET is
select * from (select p.PROD_NAME,
cts.STATUS_CD,
bucket_id,
a.price,
a.serial_num
from RM_ASSET a,
RM_BUCKET B,
RM_INVENTORY i,
RM_PRODUCT p,
RM_CLASS_STAT cts,
where a.bucket_id = b.row_id
and b.inv_id = i.row_id
and p.row_id = b.prod_id
and cts.row_id = b.class_stat_id)
where rownum<=qty;
我单独执行select 语句,得到的条数和rownum那边控制的条数是一样的但是我把select放在游标里,然后游标在一个存储过程里,我用外边的java程序调用这个存储过程,结果出来了很多的结果,,,好像那个rownum<=qty 没有其作用似地就是想问一下,为什么会这样呢,哪里出问题了
cur sys_ref_cursor;open cur for
select p.PROD_NAME,
cts.STATUS_CD,
bucket_id,
a.price,
a.serial_num
from RM_ASSET a,
RM_BUCKET B,
RM_INVENTORY i,
RM_PRODUCT p,
RM_CLASS_STAT cts,
where a.bucket_id = b.row_id
and b.inv_id = i.row_id
and p.row_id = b.prod_id
and cts.row_id = b.class_stat_id and rownum<=qty;
cursor CUR_QASSET(参数,类型) is
cursor CUR_QASSET is
select p.PROD_NAME,
cts.STATUS_CD,
....
where ....
and rownum<=参数;