SQL PL/SQL 动态变量绑定

debugcn 发表于 Dev

AAD

我想运行以下查询：

SELECT 
    * 
FROM 
    TABLE1
WHERE 
    COL1 = :DynamicValue1
    AND  COL2 = :DynamicValue2
USING 
    USERENTEREDVALUE1, USERENTEREDVALUE2;

我不想使用EXECUTE IMMEDIATE.

如何using在选择查询中使用关键字？当我运行它时，我会弹出来输入值，但它给出了错误 Ora-00933

考西克·纳亚克

您可以使用替换变量

DEFINE lname = 'Rogers'
DEFINE mgrid = 122

SELECT *
FROM employees
WHERE last_name = '&lname'
AND manager_id  = '&mgrid';

当您在 SQL developer 或 SQL* Plus 中运行它时，您会得到

old:SELECT *
FROM employees
WHERE last_name = '&lname'
AND manager_id  = &mgrid
new:SELECT *
FROM employees
WHERE last_name = 'Rogers'
AND manager_id  = 122

EMPLOYEE_ID FIRST_NAME           LAST_NAME                 EMAIL                     PHONE_NUMBER         HIRE_DAT JOB_ID         SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
----------- -------------------- ------------------------- ------------------------- -------------------- -------- ---------- ---------- -------------- ---------- -------------
        134 Michael              Rogers                    MROGERS                   650.127.1834         26-08-06 ST_CLERK         2900                       122            50

或者使用绑定变量

VARIABLE   lname VARCHAR2(40) 
VARIABLE  mgrid NUMBER 
EXEC :lname := 'Rogers'
EXEC :mgrid := 122
VARIABLE x REFCURSOR


BEGIN
     OPEN :x FOR SELECT *
                 FROM employees
                 WHERE last_name =:lname 
                 AND manager_id =:mgrid;
END;
/

    PRINT x

结果

PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.



EMPLOYEE_ID FIRST_NAME           LAST_NAME                 EMAIL                     PHONE_NUMBER         HIRE_DAT JOB_ID         SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
----------- -------------------- ------------------------- ------------------------- -------------------- -------- ---------- ---------- -------------- ---------- -------------
        134 Michael              Rogers                    MROGERS                   650.127.1834         26-08-06 ST_CLERK         2900                       122            50

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。