今天接着来学习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;’
USING‘JAVA’, 2002, IN OUT devoted_followers, OUT est_revenue;
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);