TYPE FlowNoArr IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
..
out_resultset out CUR_WATER_RATE
..
x_arr FlowNoArr;
..
x_arr(x_count) := x_flowno;
..
open out_resultset for
select max(decode(a.pmd_info,'03001',a.pmd_value)) as pd03001,
max(decode(a.pmd_info,'03002',a.pmd_value)) as pd03002,
max(decode(a.pmd_info,'03003',a.pmd_value)) as pd03003,
(select b.pmt_tranamt from pb_paymentflow b where b.pmt_flowno=a.pmd_flowno),
max(decode(a.pmd_info,'03004',a.pmd_value)) as pd03004,
max(decode(a.pmd_info,'03005',a.pmd_value)) as pd03005,
max(decode(a.pmd_info,'03006',a.pmd_value)) as pd03006,
max(decode(a.pmd_info,'03007',a.pmd_value)) as pd03007,
max(a.pmd_flowno)
from pb_payment_detail a where a.pmd_flowno in x_arr(1) group by a.pmd_flowno;
其中x_arr为FlowNoArr类型
在某个过程中,往x_arr里面填入了一些数据
然后我想找出所有a.pmd_flowno存在于x_arr里面的数据,并返回一个动态游标
现在问题是,in x_arr这样好像不行,select xxx from x_arr 也不行
in x_arr(1)这样就只能取出一个,怎么才能全部取出来呢?
谢谢
..
out_resultset out CUR_WATER_RATE
..
x_arr FlowNoArr;
..
x_arr(x_count) := x_flowno;
..
open out_resultset for
select max(decode(a.pmd_info,'03001',a.pmd_value)) as pd03001,
max(decode(a.pmd_info,'03002',a.pmd_value)) as pd03002,
max(decode(a.pmd_info,'03003',a.pmd_value)) as pd03003,
(select b.pmt_tranamt from pb_paymentflow b where b.pmt_flowno=a.pmd_flowno),
max(decode(a.pmd_info,'03004',a.pmd_value)) as pd03004,
max(decode(a.pmd_info,'03005',a.pmd_value)) as pd03005,
max(decode(a.pmd_info,'03006',a.pmd_value)) as pd03006,
max(decode(a.pmd_info,'03007',a.pmd_value)) as pd03007,
max(a.pmd_flowno)
from pb_payment_detail a where a.pmd_flowno in x_arr(1) group by a.pmd_flowno;
其中x_arr为FlowNoArr类型
在某个过程中,往x_arr里面填入了一些数据
然后我想找出所有a.pmd_flowno存在于x_arr里面的数据,并返回一个动态游标
现在问题是,in x_arr这样好像不行,select xxx from x_arr 也不行
in x_arr(1)这样就只能取出一个,怎么才能全部取出来呢?
谢谢
我原以为数组就可以当作TABLE或者COLUMN来用了,没想到原来不可以的,有什么解决办法么?
我只要筛选出我记录过的RECORD就可以,用不用数组没所谓...
你把你最初的需求再说说。没必要搞这么多type
这个是pl/sql的类型,要改成sql类型
sql>CREATE OR REPLACE TYPE FlowNoArr IS TABLE OF VARCHAR2(30);--后面的去掉了 INDEX BY BINARY_INTEGER;
..
out_resultset out CUR_WATER_RATE
..
x_arr FlowNoArr := FlowNoArr(); --这里哦,注意啦
..
--注意洛,赋值前先extend;
x_arr.extend;
--
x_arr(x_count) := x_flowno;
..
open out_resultset for
select max(decode(a.pmd_info,'03001',a.pmd_value)) as pd03001,
max(decode(a.pmd_info,'03002',a.pmd_value)) as pd03002,
max(decode(a.pmd_info,'03003',a.pmd_value)) as pd03003,
(select b.pmt_tranamt from pb_paymentflow b where b.pmt_flowno=a.pmd_flowno),
max(decode(a.pmd_info,'03004',a.pmd_value)) as pd03004,
max(decode(a.pmd_info,'03005',a.pmd_value)) as pd03005,
max(decode(a.pmd_info,'03006',a.pmd_value)) as pd03006,
max(decode(a.pmd_info,'03007',a.pmd_value)) as pd03007,
max(a.pmd_flowno)
from pb_payment_detail a
where a.pmd_flowno in (select column_value from table(x_arr) t) group by a.pmd_flowno;
V_SQL := 'SELECT ... IN(:X) ...';OPEN OUT_RESULTSET FOR V_SQL USING V_STR;
Error: PLS-00455: 游标 'OUT_RESULTSET' 不能在动态 SQL OPEN 语句中使用再次感谢各位的帮忙...
把传入的x_arr的数据insert into到临时表中,然后修改你的代码如下:a.pmd_flowno in(select col from tempTable1);
剩下的不就很简单了.
SQL> SELECT * FROM TEST_NUM; NUM1 NUM2 QTY
---------- ---------- ----------
1 1 1
1 2 1
1 3 1
2 4 1
2 5 1
2 6 1
3 7 1
3 8 1
4 9 1
4 10 1
5 1 211 rows selectedSQL>
SQL> CREATE OR REPLACE PROCEDURE SP_TEST_ARRAY/*(CUR_RETURN OUT SYS_REFCURSOR)*/ IS
2 TYPE FLOWNOARR IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
3
4 CUR_RETURN SYS_REFCURSOR;
5
6 X_ARR FLOWNOARR;
7
8 V_STR VARCHAR2(100) := '';
9 V_SQL VARCHAR2(100);
10
11 ROW_NUM TEST_NUM%ROWTYPE;
12 BEGIN
13 X_ARR(1) := '1';
14 X_ARR(2) := '2';
15
16 FOR I IN 1..X_ARR.COUNT LOOP
17 V_STR := V_STR||','||X_ARR(I);
18 END LOOP;
19
20 V_SQL := 'SELECT * FROM TEST_NUM WHERE NUM1 IN ('||LTRIM(V_STR,',')||')';
21
22 OPEN CUR_RETURN FOR V_SQL;
23
24 LOOP
25 EXIT WHEN CUR_RETURN%NOTFOUND;
26 FETCH CUR_RETURN INTO ROW_NUM;
27 DBMS_OUTPUT.PUT_LINE('NUM1: '||ROW_NUM.NUM1 || ' NUM2: '||ROW_NUM.NUM2||' QTY: '||ROW_NUM.QTY);
28 END LOOP;
29
30 IF CUR_RETURN%ISOPEN THEN
31 CLOSE CUR_RETURN;
32 END IF;
33
34 EXCEPTION WHEN OTHERS THEN
35 DBMS_OUTPUT.PUT_LINE(SQLCODE||' : '||SQLERRM);
36 END;
37 /Procedure createdSQL> EXEC SP_TEST_ARRAY;NUM1: 1 NUM2: 1 QTY: 1
NUM1: 1 NUM2: 2 QTY: 1
NUM1: 1 NUM2: 3 QTY: 1
NUM1: 2 NUM2: 4 QTY: 1
NUM1: 2 NUM2: 5 QTY: 1
NUM1: 2 NUM2: 6 QTY: 1
NUM1: 2 NUM2: 6 QTY: 1PL/SQL procedure successfully completed
...
out_resultset out CUR_WATER_RATE
...
open out_resultset for 'select 1 from dual'; --会报错...
CUR_RETURN SYS_REFCURSOR;
...
open CUR_RETURN for 'select 1 from dual'; --会报错
我的动态游标:
TYPE WATER_RATE IS RECORD(
cstNo varchar(30), --用户编号
cstName varchar(100), --用户姓名
beginDate varchar(8), --抄表日期
amount DECIMAL(18,2), --合计金额
water DECIMAL(18,2), --水费
sewage DECIMAL(18,2), --污水费
lateFees DECIMAL(18,2), --滞纳金
storage varchar(30), --水量
notifyNo varchar(30) --用户编号
);
TYPE CUR_WATER_RATE IS REF CURSOR RETURN WATER_RATE;可以解释一下这是为什么吗?谢谢
...
CUR_RETURN SYS_REFCURSOR;
...
open CUR_RETURN for 'select 1 from dual'; --不会报错