动态SQL.ppt

動態SQL 網通資訊部:沈艷嬌 2016.09.09 * 動態SQL和靜態SQL 使用动态SQL是在编写PL/SQL过程时经常使用的方法之一。很多情况下，比如根据业务的需要，如果输入不同查询条件，则生成不同的执行SQL查询语句，对于这种情况需要使用动态SQL来完成。再比如，对于分页的情况，对于不同的表，必定存在不同的字段，因此使用静态SQL则只能针对某几个特定的表来形成分页。而使用动态的SQL，则可以对不同的表，不同的字段进行不同的分页。这些情况的处理通常都是用动态SQL来完成。 動態SQL和靜態SQL 1.静态SQL ???????静态SQL通常用于完成可以确定的任务。比如传递部门号调用存储过程，返回该部门的所有雇员及薪水信息，则该语句为 SELECT?column1, column2?INTO?col1,col2? FROM?table?WHERE?id=vid; ???????对于上述类似的DML语句在第一次运行时进行编译，而后续再次调用，则不再编译该过程。即一次编译，多次调用，使用的相同的执行计划。此种方式被称之为使用的是静态的SQL。 動態SQL和靜態SQL 2.动态SQL ???????动态SQL通常是用来根据不同的需求完成不同的任务。比如分页查询，对于表emp分页，需要使用字段雇员姓名，薪水，雇用日期，且按薪水降序生成报表，每页显示行数据。而对于表sales，需要使用字段雇员名称，客户名称，销售数量，销售日期，且按销售日期升序排列。以上两种情况，可以创建存储过程来对其进行分页，通过定义变量，根据输入不同的表名，字段名，排序方法来生成不同的SQL语句。对于输入不同的参数，SQL在每次运行时需要事先对其编译。即多次调用则需要多次编译，此称之为动态SQL。 ???????动态SQL语句通常存放在字符串变量中，且SQL语句可以包含占位符(使用冒号开头)。 ???????也可以直接将动态SQL紧跟在EXECUTE IMMEDIATE语句之后，如EXECUTE?IMMEDIATE?alter table emp enable row movement 動態SQL和靜態SQL 3.两者的异同 ???????静态SQL为直接嵌入到PL/SQL中的代码，而动态SQL在运行时，根据不同的情况产生不同的SQL语句。 ???????静态SQL为在执行前编译，一次编译，多次运行。动态SQL同样在执行前编译，但每次执行需要重新编译。 ???????静态SQL可以使用相同的执行计划，对于确定的任务而言，静态SQL更具有高效性。但缺乏灵活性 ???????动态SQL使用了不同的执行计划，效率不如静态SQL，但能够解决复杂的问题。 ???????动态SQL容易产生SQL注入，为数据库安全带来隐患。 動態SQL和靜態SQL 4.动态SQL语句的几种方法 ???????a.使用EXECUTE IMMEDIATE语句 ???????包括DDL语句，DCL语句，DML语句以及单行的SELECT?语句。该方法不能用于处理多行查询语句。 ???????b.使用OPEN-FOR，FETCH和CLOSE语句 ???????对于处理动态多行的查询操作，可以使用OPEN-FOR语句打开游标，使用FETCH语句循环提取数据，最终使用CLOSE语句关闭游标。 ???????c.使用批量动态SQL ???????即在动态SQL中使用BULK子句，或使用游标变量时在fetch中使用BULK?，或在FORALL语句中使用BULK子句来实现。 二、動態SQL的語法 a. 动态SQL常用的语法之一??? ???????EXECUTE IMMEDIATE dynamic_SQL_string ???????[INTO defined_variable1, defined_variable2, ...] ???????[USING [IN | OUT | IN OUT] bind_argument1,?bind_argument2, ???????...][{RETURNING | RETURN} field1, field2, ... INTO bind_argument1, ???????bind_argument2, ...] 二、動態SQL的語法 1.语法描述 ???????dynamic_SQL_string：存放指定的SQL语句或PL/SQL块的字符串变量 ???????defined_variable1：用于存放单行查询结果，使用时必须使用INTO关键字，类似于使用SELECT?ename?INTO?v_name?FROM?scott.emp； ???????只不过在动态SQL时，将INTO defined_variable1移出到dynamic_SQL_str