create or replace package body test is TYPE item_cd_array IS TABLE OF char(8) INDEX BY BINARY_INTEGER;
procedure testproc2 is
itemcds item_cd_array;
v_sql varchar2(500);
n_sta integer;
n_cid integer;
begin
itemcds(1):='00000001';
itemcds(2):='00000002';
for i in 1..itemcds.count loop
dbms_output.put_line('test'|| itemcds(i));
end loop;
v_sql :='select name from item01 where cd in (:b_sqlin)';
n_cid := dbms_sql.open_cursor();
dbms_sql.parse(n_cid,v_sql,dbms_sql.native);
--下边报错了,应该怎么做呢?
dbms_sql.bind_array(n_cid,'b_sqlin',itemcds,1,itemcds.count); end;
end test;
procedure testproc2 is
itemcds item_cd_array;
v_sql varchar2(500);
n_sta integer;
n_cid integer;
begin
itemcds(1):='00000001';
itemcds(2):='00000002';
for i in 1..itemcds.count loop
dbms_output.put_line('test'|| itemcds(i));
end loop;
v_sql :='select name from item01 where cd in (:b_sqlin)';
n_cid := dbms_sql.open_cursor();
dbms_sql.parse(n_cid,v_sql,dbms_sql.native);
--下边报错了,应该怎么做呢?
dbms_sql.bind_array(n_cid,'b_sqlin',itemcds,1,itemcds.count); end;
end test;
procedure testproc2 is
itemcds item_cd_array;
v_sql varchar2(500);
n_sta integer;
n_cid integer;
begin
itemcds(1):='00000001';
itemcds(2):='00000002';
for i in 1..itemcds.count loop
dbms_output.put_line('test'|| itemcds(i));
end loop;
v_sql :='select name from item01 where cd in (:b_sqlin)';
n_cid := dbms_sql.open_cursor();
dbms_sql.parse(n_cid,v_sql,dbms_sql.native);
--下边报错了,应该怎么做呢?
dbms_sql.bind_array(n_cid,'b_sqlin',itemcds,1,itemcds.count); end;
end test;
能讲讲dbms_sql.bind_array 的作用吗?
SQL> SELECT * FROM ITEM01;CD NAME
---------- --------------------------------------------------
00000001 ITEM01-test-test-test
00000002 ITEM05
00000003 ITEM03-test-test
00000004 ITEM06-test1
00000005 ITEM08-test-ZZSQL>
SQL> CREATE OR REPLACE PROCEDURE TEST_DBSQL_ARRAY(ARRAY_POSITION NUMBER) IS
2 V_CURSOR NUMBER;
3 V_SQL VARCHAR2(100);
4 V_NAME ITEM01.NAME%TYPE;
5 V_STAT NUMBER;
6 CDS_ARRAY DBMS_SQL.VARCHAR2_TABLE;
7
8 BEGIN
9 CDS_ARRAY(1) := '00000001'; CDS_ARRAY(2) := '00000002';
10
11 V_SQL:= 'SELECT NAME FROM ITEM01 WHERE CD = :CDS_VALUES';
12
13 V_CURSOR := DBMS_SQL.OPEN_CURSOR;
14 DBMS_SQL.PARSE(V_CURSOR,V_SQL,DBMS_SQL.NATIVE);
15
16 DBMS_SQL.BIND_ARRAY(V_CURSOR,':CDS_VALUES',CDS_ARRAY,1,ARRAY_POSITION);
17 DBMS_SQL.DEFINE_COLUMN(V_CURSOR,1,V_NAME,128);
18 V_STAT := DBMS_SQL.EXECUTE(V_CURSOR);
19
20 LOOP
21 EXIT WHEN DBMS_SQL.FETCH_ROWS(V_CURSOR)<=0;
22 DBMS_SQL.COLUMN_VALUE(V_CURSOR,1,V_NAME);
23 DBMS_OUTPUT.PUT_LINE('SELECTED NAME: '||V_NAME);
24 END LOOP;
25
26 DBMS_SQL.CLOSE_CURSOR(V_CURSOR);
27 END TEST_DBSQL_ARRAY;
28 /Procedure createdSQL> EXEC TEST_DBSQL_ARRAY(1);SELECTED NAME: ITEM01-test-test-testPL/SQL procedure successfully completedSQL> EXEC TEST_DBSQL_ARRAY(2);SELECTED NAME: ITEM05PL/SQL procedure successfully completedSQL>
FYI:
http://topic.csdn.net/t/20030421/12/1687889.html
这不像传进去了一个数组,而是每次只能传数组里的一个元素?