Oracle中绑定变量的使用

Oracle中绑定变量的使用

1.SqlPlus中的用法

SQL> var x number;      
SQL> exec :x := 7369;

PL/SQL procedure successfully completed.

SQL> select * from scott.emp where empno=:x;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20

2.PL SQL中的用法

/* Formatted on 2021-09-24 上午 10:55:17 (QP5 v5.163.1008.3004) */
--查询语句

DECLARE
   V_ENAME   VARCHAR2 (10);
BEGIN
   EXECUTE IMMEDIATE 'SELECT ENAME FROM SCOTT.EMP WHERE EMPNO = :1'
      INTO V_ENAME
      USING 7369;

   DBMS_OUTPUT.PUT_LINE (V_ENAME);
END;

--DML语句

DECLARE
   V_SQL01     VARCHAR2 (4000);
   V_SQL02     VARCHAR2 (4000);
   V_COUNT01   NUMBER;
   V_COUNT02   NUMBER;
BEGIN
   V_SQL01 := 'INSERT INTO MONKEY.TEST02 VALUES (:1,:2,:3)';

   EXECUTE IMMEDIATE V_SQL01 USING 3, SYSDATE, 'CC';

   V_COUNT01 := SQL%ROWCOUNT;

   V_SQL01 := 'INSERT INTO MONKEY.TEST02 VALUES (:1,:1,:1)';

   EXECUTE IMMEDIATE V_SQL01 USING 4, SYSDATE, 'DD';

   V_COUNT02 := SQL%ROWCOUNT;
   DBMS_OUTPUT.PUT_LINE (V_COUNT01 + V_COUNT02);
   COMMIT;
END;

--动态SQL(SQL文本不固定，由变量决定)

DECLARE
   V_SQL     VARCHAR2 (4000);
   V_COL     VARCHAR2 (50);
   V_ENAME   VARCHAR2 (50);
BEGIN
   V_COL := 'EMPNO';
   V_SQL :=
         'DELETE FROM SCOTT.EMP WHERE '
      || V_COL
      || '=:1 RETURNING ENAME INTO :2';

   EXECUTE IMMEDIATE V_SQL USING 7369 RETURNING INTO V_ENAME;

   DBMS_OUTPUT.PUT_LINE (V_ENAME);
END;