今天接着来学习DNS语句（原生动态SQL），学习有关绑定变量或绑定参数的使用规则。


参数模式

绑定参数可以有3种模式：

IN            只读值（默认模式）

OUT        只允许写

IN OUT   可以读取输入的值，也可以把值传递出去

当我们执行动态查询时，所有绑定参数必须是IN模式，除非我们使用的是RETURNING子句，如下所示：

PROCEDURE wrong_incentive(
        IN INTEGER,
        new_layoffs IN NUMBER
         )
IS
          sql_String VARCHAR2(32767);
          sal_after_layoffs NUMBER;
BEGIN
           sql_string := ‘UPDATE ceo_compensation SET salary = salary + 10 * :layoffs
                                    WHERE company_id = :company
                                    RETURNING salary INTO :newsal’;
           EXECUTE IMMEDIATE sql_string
                         USING new_layoffs, company_in, sal_after_layoffs;
            DBMS_OUTPUT.PUT_LINE(
‘CEO compensation after latest round of layoffs $’ ||sal_after_layoffs);
 END;

除了和RETURNING子句一起使用，out和in out模式的绑定参数在执行动态PL/SQL时也发挥了很大作用。

在动态PL/SQL中，绑定参数的模式必须与PL/SQL程序的参数模式以及动态PL/SQL块中变量的使用相匹配。

下面是执行动态PL/SQL的USING子句的使用指导：

1、可以给一个IN模式的绑定变量提供任何类型正确的表达式：直接量、命名常量、变量或者复杂表达式。这些表达式被求值后再传递给动态PL/sql块。

2、必须提供一个变量来接收OUT或IN OUT模式绑定变量的输出值。

3、只能绑定动态PL/SQL块中有SQL类型的变量。例如，如果过程有布尔参数，我们不能使用USING子句设置（或提取）布尔值。

例子：

PROCEDUREanalyze_new_technology(
        Tech_nameIN VARCHAR2,
        Analysis_yearIN INTEGER,
        Number_of_adherents IN OUT NUMBER,
        Projected_revenue OUT NUMBER
        )
--一个动态执行该过程的块
DECLARE
       devoted_followers NUMBER;
       est_revenue NUMBER;
BEGIN
       EXECUTE IMMEDIATE
                       ‘BEGIN
                              Analyze_new_technology (:p1, :p2, :p3, :p4);
                         END;’
END;

重复的占位符

在动态构造和执行的SQL字符串中，NDS将占位符通过位置而非名称与USING子句绑定参数关联。

然而，多个拥有相同名称的占位符的处理需要根据我们使用的是动态SQL还是动态PL/SQL来确定。

需要遵循以下原则：

1、我们执行动态SQL字符串时，我们必须为每个占位符提供一个参数，即使这些占位符有重复。

2、当我们执行动态PL/sql块时，必须为每个唯一占位符提供一个参数。

/*一个有重复占位符的动态SQL的示例。注意val_in参数的重复使用*/
PROCEDURE updnumval_SQL(
        col_in IN Varchar2,
        start_in IN DATE,
        end_in IN DATE,
        val_in IN NUMBER
        )
IS
        dml_str VARCHAR2(32767);
BEGIN
        dml_str := 'UPDATE emp SET ' || col_in || ' = :val
              WHERE hiredate BETWEEN :lodate AND :hidate
              AND :val IS NOT NULL';
         EXECUTE IMMEDIATE dml_str
               USING val_in, start_in,end_in,val_in;
END;                         
/*一个有重复占位符的动态PL/SQL块的示例。注意val_in只提供一次就可以*/

PROCEDURE updnumval_PLSQL(
        col_in IN Varchar2,
        start_in IN DATE,
        end_in IN DATE,
        val_in IN NUMBER
        )

IS
        dml_str VARCHAR2(32767);
BEGIN
        dml_str :=
               'BEGIN
                     UPDATE emp SET ' || col_in || '= :val
                          WHERE hiredate BETWEEN :lodateAND :hidate
                          AND :val IS NOT NULL;
                END;';
         EXECUTE IMMEDIATE dml_str
               USING val_in,start_in,end_in;
END;

 

传递NULL值

有时会遇到将NULL值作为参数传递的情况，像下面这样：

EXECUTE  IMMEDIATE ‘UPDATE emp SET salary = :newsal

                                             WHERE hiredate IS NULL’

                        USING NULL;

然而，会出现以下错误：

                PLS-00457：in USING clause,expressions have to be of SQL types

意思是在USING子句的表达式必须是SQL类型，而NULL没有数据类型。

那么该怎么传递NULL值？有以下两种做法：

1、将NULL值隐藏在一个变量的后面，如果用一个未初始化的变量会更容易些：

DECLARE
       --默认初始值为NULL
       No_salary_when_firednumber;
BEGIN
       EXECUTE IMMEDIATE ‘UPDATE emp SET salary = :newsal
                                                   WHERE hiredate IS NULL’
             USING no_salary_when_fired;
END;
2、使用转换函数将NULL转换为一个有类型的值：
EXECUTE IMMEDIATE ‘UPDATE emp SET salary = :newsal
                                             WHERE hiredate IS NULL’
      USING to_number(NULL);