procedure testproc2 is
v_sql varchar2(500);
n_ret integer;
n_cid integer;
v_sql_in varchar2(100);
temp_name varchar2(50);
begin
v_sql_in :='''00000001'',''00000002''';
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_variable(n_cid,'b_sqlin',v_sql_in,100);
dbms_sql.define_column(n_cid,1,temp_name,100);
n_ret := DBMS_SQL.EXECUTE(n_cid);
LOOP
EXIT WHEN DBMS_SQL.FETCH_ROWS(n_cid) <= 0;
dbms_sql.COLUMN_VALUE(n_cid,1,temp_name);
dbms_output.put_line('name is '||temp_name);
END LOOP;
end;
-----------------------------------------------------------------------------------
实际数据情况如下
SQL>
SQL> select name from item01
2 where cd in ('00000005','00000001');NAME
--------------------------------------------------
ITEM01-test-test-test
ITEM05SQL>
v_sql varchar2(500);
n_ret integer;
n_cid integer;
v_sql_in varchar2(100);
temp_name varchar2(50);
begin
v_sql_in :='''00000001'',''00000002''';
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_variable(n_cid,'b_sqlin',v_sql_in,100);
dbms_sql.define_column(n_cid,1,temp_name,100);
n_ret := DBMS_SQL.EXECUTE(n_cid);
LOOP
EXIT WHEN DBMS_SQL.FETCH_ROWS(n_cid) <= 0;
dbms_sql.COLUMN_VALUE(n_cid,1,temp_name);
dbms_output.put_line('name is '||temp_name);
END LOOP;
end;
-----------------------------------------------------------------------------------
实际数据情况如下
SQL>
SQL> select name from item01
2 where cd in ('00000005','00000001');NAME
--------------------------------------------------
ITEM01-test-test-test
ITEM05SQL>
试试!
SQL> SELECT * FROM ITEM01;CD NAME
---------- --------------------------------------------------
00000001 ITEM01-test-test-test
00000002 ITEM05
00000003 ITEM03-test-test
00000004 ITEM06-test1
00000005 ITEM08-test-ZZSQL> CREATE OR REPLACE PROCEDURE TEST_PROC(CDS VARCHAR2) IS
2 V_CURSOR NUMBER;
3 V_SQL VARCHAR2(100);
4 V_NAME ITEM01.NAME%TYPE;
5 V_STAT NUMBER;
6 BEGIN
7 V_SQL:= 'SELECT NAME FROM ITEM01 WHERE CD IN ('||CDS||')';
8
9 V_CURSOR := DBMS_SQL.OPEN_CURSOR;
10 DBMS_SQL.PARSE(V_CURSOR,V_SQL,DBMS_SQL.NATIVE);
11 DBMS_SQL.DEFINE_COLUMN(V_CURSOR,1,V_NAME,128);
12 V_STAT := DBMS_SQL.EXECUTE(V_CURSOR);
13
14 LOOP
15 EXIT WHEN DBMS_SQL.FETCH_ROWS(V_CURSOR)<=0;
16 DBMS_SQL.COLUMN_VALUE(V_CURSOR,1,V_NAME);
17 DBMS_OUTPUT.PUT_LINE('SELECTED NAME: '||V_NAME);
18 END LOOP;
19
20 DBMS_SQL.CLOSE_CURSOR(V_CURSOR);
21 END TEST_PROC;
22 /Procedure createdSQL> EXEC TEST_PROC('''00000001'',''00000002''');SELECTED NAME: ITEM01-test-test-test
SELECTED NAME: ITEM05PL/SQL procedure successfully completedSQL>
我希望能使用bind 的方法,可以吗?
如果这一层明白了,这个问题也就解决了。