有一些错误，真的。

• 局部变量应该是VARCHAR2，而不是NVARCHAR2（因为你会得到“expression is of wrong type”错误）
• 参数应连接到where条件中
• 小心SQL注入（如果有任何问题）
• 请注意作为参数传递的字符串和日期（提示：将它们括在单引号中）
• 别忘了在语句的各个部分之间加上空格
• 在实际运行最终语句之前，最好显示它以验证它是否正确
• 您发布的代码表明一次只能有一个参数;如果不是，你将不得不修改where条件（连接以前的值，包括and关键字）

我没有您的表，因此我将使用Scott的示例模式来说明它。

SQL> CREATE OR REPLACE PROCEDURE p_Fetch (p_deptno    IN     NUMBER,
  2                                       p_loc       IN     VARCHAR2,
  3                                       p_hiredate  IN     DATE,
  4                                       tblout         OUT SYS_REFCURSOR)
  5  AS
  6     v_query_string     VARCHAR2 (5000);
  7     v_where_condition  VARCHAR2 (5000);
  8  BEGIN
  9     v_query_string :=
 10           'select d.dname, e.ename, e.job '
 11        || ' from emp e join dept d on d.deptno = e.deptno ';
 12
 13     IF p_deptno IS NOT NULL
 14     THEN
 15        v_where_condition := ' where d.deptno = ' || p_deptno;
 16     ELSIF p_loc IS NOT NULL
 17     THEN
 18        v_where_condition := ' where d.loc = ' || CHR (39) || p_loc || CHR (39);
 19     ELSIF p_hiredate IS NOT NULL
 20     THEN
 21        v_where_condition :=
 22           ' where e.hiredate <= date ' || CHR (39) || p_hiredate || CHR (39);
 23     END IF;
 24
 25     OPEN tblout FOR v_query_string || ' ' || v_where_condition;
 26  END;
 27  /

Procedure created.

我们试试看

SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'yyyy-mm-dd';

Session altered.

SQL> SET SERVEROUTPUT ON
SQL> DECLARE
  2     rc       SYS_REFCURSOR;
  3     --
  4     l_dname  dept.dname%TYPE;
  5     l_ename  emp.ename%TYPE;
  6     l_job    emp.job%TYPE;
  7  BEGIN
  8     p_fetch (p_deptno    => NULL,
  9              p_loc       => NULL,
 10              p_hiredate  => DATE '1981-05-01',
 11              tblout      => rc);
 12
 13     LOOP
 14        FETCH rc INTO l_dname, l_ename, l_job;
 15
 16        EXIT WHEN rc%NOTFOUND;
 17        DBMS_OUTPUT.put_line (l_dname || ' ' || l_ename || ' ' || l_job);
 18     END LOOP;
 19  END;
 20  /
RESEARCH JONES MANAGER
RESEARCH SMITH CLERK
SALES BLAKE MANAGER
SALES ALLEN SALESMAN
SALES WARD SALESMAN

PL/SQL procedure successfully completed.

另一个测试：

SQL> DECLARE
  2     rc       SYS_REFCURSOR;
  3     --
  4     l_dname  dept.dname%TYPE;
  5     l_ename  emp.ename%TYPE;
  6     l_job    emp.job%TYPE;
  7  BEGIN
  8     p_fetch (p_deptno    => NULL,
  9              p_loc       => 'NEW YORK',
 10              p_hiredate  => NULL,
 11              tblout      => rc);
 12
 13     LOOP
 14        FETCH rc INTO l_dname, l_ename, l_job;
 15
 16        EXIT WHEN rc%NOTFOUND;
 17        DBMS_OUTPUT.put_line (l_dname || ' ' || l_ename || ' ' || l_job);
 18     END LOOP;
 19  END;
 20  /
ACCOUNTING CLARK MANAGER
ACCOUNTING KING PRESIDENT
ACCOUNTING MILLER CLERK

PL/SQL procedure successfully completed.

SQL>