oracle plsql 将变量连接到字符串

Question

下面是在 oracle plsql 中编写的程序，当该程序通过将 P_LOC 值指定为“DALLAS”来运行时 它给出错误 -904 ORA-00904:“DALLAS”: 无效标识符，请提供解决方案

create or replace
PROCEDURE PR_EMP_LST1 
( P_LOC IN VARCHAR2 
, P_MIN_SAL IN NUMBER
, P_MAX_SAL IN NUMBER 
, P_REF_CUR OUT  SYS_REFCURSOR
) AS

  TYPE TY_LST_REFCURSOR IS REF CURSOR ; 
  cur_emp_lst  TY_LST_REFCURSOR;
  v_inv_query  VARCHAR2(2000);
  V_USG_CL    VARCHAR2(200);


BEGIN

  IF P_LOC IS NULL AND p_min_sal IS  NULL AND p_max_sal IS NULL  THEN  
      v_inv_query :='SELECT ENAME 
                     FROM EMP ';
  ELSE               
      v_inv_query :='SELECT ENAME 
                     FROM EMP WHERE ';


    IF P_LOC IS NULL AND p_min_sal IS NOT NULL AND p_max_sal IS  NOT NULL  THEN                

      v_inv_query :=v_inv_query ||' SAL BETWEEN '
                                  ||p_min_sal 
                                  ||' AND ' 
                                  || p_max_sal; 
       --  v_inv_query :=v_inv_query ||' SAL BETWEEN :1 AND :2 ';

           dbms_output.put_line('2');
     --  V_USG_CL:=  ' USING '||p_min_sal||' , '|| p_max_sal;   
    ELSIF P_LOC IS NOT NULL AND p_min_sal IS NOT NULL AND p_max_sal IS  NOT NULL THEN
     dbms_output.put_line('1');
      v_inv_query :=v_inv_query||' SAL BETWEEN '
                                  ||p_min_sal || ' AND '||  p_max_sal
                                  || ' AND DEPTNO IN (SELECT DEPTNO 
                                                 FROM DEPT
                                                 WHERE LOC= '
                                                 || p_loc  
                                                 ||' )'; 
           dbms_output.put_line('2');                                       
    ELSIF P_LOC IS NOT NULL AND p_min_sal IS NULL AND p_max_sal IS  NOT NULL THEN    
       v_inv_query :=v_inv_query||' SAL <= '
                                   ||p_max_sal
                                   ||' AND DEPTNO IN (SELECT DEPTNO 
                                                 FROM DEPT
                                                 WHERE LOC= '
                                                  || p_loc 
                                                  ||' )'; 
      ELSIF P_LOC IS NOT NULL AND p_min_sal IS NOT NULL AND p_max_sal IS NULL THEN  
       v_inv_query :=v_inv_query||' SAL >= '
                                    ||p_min_sal
                                    ||' AND DEPTNO IN (SELECT DEPTNO 
                                                  FROM DEPT
                                                  WHERE LOC= '
                                                  ||p_loc
                                                  ||' )'; 
     ELSIF P_LOC IS  NULL AND p_min_sal IS  NULL AND p_max_sal IS NOT NULL THEN 
       v_inv_query :=v_inv_query||'  SAL <= '
                                              ||p_max_sal;

     ELSIF P_LOC IS  NULL AND p_min_sal IS NOT NULL AND p_max_sal IS NULL THEN 
       v_inv_query :=v_inv_query||' SAL >= '
                                     || p_min_sal;  

     ELSIF P_LOC IS NOT NULL AND p_min_sal IS  NULL AND p_max_sal IS NULL THEN 
       v_inv_query :=v_inv_query||' DEPTNO IN (SELECT DEPTNO 
                                                  FROM DEPT
                                                  WHERE LOC= '
                                                  ||p_loc
                                                  ||' )';                               
     END IF;
  END IF;  
  dbms_output.put_line('3');   
   dbms_output.put_line(v_inv_query );
  OPEN cur_emp_lst  FOR v_inv_query ;

  dbms_output.put_line('4');   
  P_REF_CUR:=cur_emp_lst;                               

END PR_EMP_LST1;

Answer 1

'从部门中选择部门号，其中 LOC= ''' || p_loc ||''' )';

由于 Dallas 是字符串，因此您需要将其处理为字符串： 'DALLAS' 而不是 DALLAS。字符串内部必须使用 ''

Answer 2

P_LOC参数为字符串类型，参数值需用单引号括起来。
在现有的动态 SQL 条件中，不存在单引号。

请将以下条件替换为新条件。

现有状况：
|| ' AND DEPTNO IN (SELECT DEPTNO FROM DEPT WHERE LOC= ' || p_loc ||' )';

新状况：
|| q'[ AND DEPTNO IN (SELECT DEPTNO FROM DEPT WHERE LOC= ']' || p_loc || q'[' )]';

谢谢...