in列表是不行的,如果in列表是参数传入, 请使用动态SQL语句create procedure ptest(pi_temp,o out sys_refcursor) begin --- open cur for 'select * from 表A where cdsc_id in('''||pi_temp||''')'; --- end;
-- 不可以! -- 所以,当用select column_name into variable_name from table_name语句赋值时, -- 这个select 语句必须只会返回一条记录,否则将出错!-- 但一个变量可以多次赋值,每次赋值时,会用新值覆盖原来的值!
-- 怎么就不可以呢?set serveroutput on;DECLARE v_number NUMBER(18,0); BEGIN v_number := 123; dbms_output.put_line('v_number the first value is: '||v_number); v_number := 456; dbms_output.put_line('v_number the second value is: '||v_number); END; /
SQL> CREATE OR REPLACE PROCEDURE p_test1(i_namelist VARCHAR2, 2 o OUT SYS_REFCURSOR) IS 3 BEGIN 4 OPEN o FOR 'select empno,ename,sal from emp where ename in (' || i_namelist || ')'; 5 END p_test1; 6 /过程已创建。SQL> select empno,ename,sal from emp; EMPNO ENAME SAL ---------- ---------- ---------- 7369 SMITH 1100 7499 ALLEN 1800 7521 WARD 1450 7566 JONES 3275 7654 MARTIN 1450 7698 BLAKE 3050 7782 CLARK 2750 7788 SCOTT 3300 7839 KING 5300 7844 TURNER 1700 7876 ADAMS 1400已选择11行。SQL> var o refcursor SQL> exec p_test1('''BLAKE'',''SCOTT''',:o);PL/SQL 过程已成功完成。SQL> print o EMPNO ENAME SAL ---------- ---------- ---------- 7698 BLAKE 3050 7788 SCOTT 3300SQL>
请使用动态SQL语句create procedure ptest(pi_temp,o out sys_refcursor)
begin
---
open cur for 'select * from 表A where cdsc_id in('''||pi_temp||''')';
---
end;
-- 不可以!
-- 所以,当用select column_name into variable_name from table_name语句赋值时,
-- 这个select 语句必须只会返回一条记录,否则将出错!-- 但一个变量可以多次赋值,每次赋值时,会用新值覆盖原来的值!
v_number NUMBER(18,0);
BEGIN
v_number := 123;
dbms_output.put_line('v_number the first value is: '||v_number);
v_number := 456;
dbms_output.put_line('v_number the second value is: '||v_number);
END;
/
2 o OUT SYS_REFCURSOR) IS
3 BEGIN
4 OPEN o FOR 'select empno,ename,sal from emp where ename in (' || i_namelist || ')';
5 END p_test1;
6 /过程已创建。SQL> select empno,ename,sal from emp; EMPNO ENAME SAL
---------- ---------- ----------
7369 SMITH 1100
7499 ALLEN 1800
7521 WARD 1450
7566 JONES 3275
7654 MARTIN 1450
7698 BLAKE 3050
7782 CLARK 2750
7788 SCOTT 3300
7839 KING 5300
7844 TURNER 1700
7876 ADAMS 1400已选择11行。SQL> var o refcursor
SQL> exec p_test1('''BLAKE'',''SCOTT''',:o);PL/SQL 过程已成功完成。SQL> print o EMPNO ENAME SAL
---------- ---------- ----------
7698 BLAKE 3050
7788 SCOTT 3300SQL>