使用原生动态SQL(NDS)时,可以使用EXCUTE IMMEDIATE语句来立即执行指定的SQL语句,语法如下:
EXECUTEIMMEDIATE SQL_string
[[ BULK COLLECT ] INTO {define_variable[, define_variable]… | record} ]
[USING[IN | OUT | IN OUT] bind_argument
[, [IN | OUT | IN OUT] bind_argument] …];
解释:SQL_string是包含SQL语句或PL/SQL块的字符串表达式。
define_variable是接收由查询返回的列值的变量。
record是用户定义类型或者基于%ROWTYPE的记录,接收由查询返回的整个行值。
bind_argument是一个表达式,表达式的值将传递给指定SQL语句或PL/SQL块;它也可以是一个标识符,作为PL/SQL块中调用的函数或过程的输入和 / 或输出变量。
INTO子句:用于单列查询。对于每个查询返回的列值,我们必须在兼容类型记录中提供单个变量或字段。如果我们以BULK COLLECT作为INTO的开始,则可以在一个或多个集合内提取多行。
USING子句:这个子句允许我们为SQL字符串提供绑定参数,可用于动态SQL和PL/SQL,因此我们需要制定一个参数模式。该模式只与PL/SQL和RETURNING子句有关。绑定变量的默认模式为IN,是SQL唯一可用的绑定参数。
NDS支持所有SQL数据类型。可以绑定如字符串、日期及数字等标量值,也可以绑定模式级集合、LOB、对象类型实例、XML文件、REF等。
下面是一些使用EXECUTE IMMEDIATE语句的例子:
--创建一个可以执行任何DDL语句的过程:
CREATE PROCEDURE exec_DDL (ddl_string in VARCHAR2) IS
BEGIN
EXECUTE IMMEDIATE ddl_string;
END;
用exec_DDL创建索引:
BEGIN
Exec_DDL(‘CREATE INDEX emp_index ON employees(last_name)’);
END;
--获取任意一个表中符合指定的WHERE条件的记录数量:
CREATE FUNCTION tabcount (table_in INVARCHAR2)
RETURN PLS_INTEGER
IS
l_query VARCHAR2(32767);
l_return PLS_INTEGER;
BEGIN
l_query := 'SELECT count(*) FROM ' || table_in;
EXECUTE IMMEDIATE l_query INTO l_return;
RETURN l_return;
END;
--所以,无论是在SQL*plus还是在PL/SQL程序中,我们现在再也不需要编写SELECT count(*)了,可以执行类似以下的操作:
BEGIN
IF tabcount ('emp') >10
THEN
DBMS_OUTPUT.PUT_LINE('We are growing fast!');
END IF;
END;
--更新数字列的值,并返回更新的行数:
FUNCTION updNVal(col IN VARCHAR2,val INNUMBER,start_in IN DATE,end_in IN DATE)
RETURN PLS_INTEGER
IS
BEGIN
EXECUTE IMMEDIATE 'update emp set' || col || ' = :the_value
where hire_date BETWEEN :lo AND :hi'
USING val, start_in, end_in;
RETURN SQL%ROWCOUNT;
END;
解释:该例子介绍了绑定子句(USING):PL/SQL引擎将USING子句中的数值传递给SQL引擎。解析该语句后,SQL引擎使用所提供数值代替占位符(:the_value、:lo和:hi)。
我们还使用SQL%ROWCOUNT游标属性来确定由动态SQL语句修改的行数,对静态也适用。