PL/SQL -- 动态SQL

最新推荐文章于 2021-11-20 13:27:45 发布

这个就不用说了吧

最新推荐文章于 2021-11-20 13:27:45 发布

阅读量156

点赞数

本文链接：https://blog.csdn.net/fsfsdfsdw/article/details/86649691

版权

--====================

-- PL/SQL --> 动态SQL

--====================

使用动态SQL是在编写PL/SQL过程时经常使用的方法之一。很多情况下，比如根据业务的需要，如果输入不同查询条件，则生成不同的执行

SQL查询语句，对于这种情况需要使用动态SQL来完成。再比如，对于分页的情况，对于不同的表，必定存在不同的字段，因此使用静态SQL则只

能针对某几个特定的表来形成分页。而使用动态的SQL，则可以对不同的表，不同的字段进行不同的分页。这些情况的处理通常都是用动态SQL来

完成。本文讲述了动态SQL的日常用法。

一、动态SQL和静态SQL

1.静态SQL

静态SQL通常用于完成可以确定的任务。比如传递部门号调用存储过程，返回该部门的所有雇员及薪水信息，则该语句为

SELECT ename，sal INTO lv_ename,lv_sal FROM scott.emp WHERE deptno=&dno;

对于上述类似的DML语句在第一次运行时进行编译，而后续再次调用，则不再编译该过程。即一次编译，多次调用，使用的相同的执行

计划。此种方式被称之为使用的是静态的SQL。

2.动态SQL

动态SQL通常是用来根据不同的需求完成不同的任务。比如分页查询，对于表emp分页，需要使用字段雇员姓名，薪水，雇用日期，且按

薪水降序生成报表，每页显示行数据。而对于表sales，需要使用字段雇员名称，客户名称，销售数量，销售日期，且按销售日期升序

排列。以上两种情况，可以创建存储过程来对其进行分页，通过定义变量，根据输入不同的表名，字段名，排序方法来生成不同的SQL

语句。对于输入不同的参数，SQL在每次运行时需要事先对其编译。即多次调用则需要多次编译，此称之为动态SQL。

动态SQL语句通常存放在字符串变量中，且SQL语句可以包含占位符(使用冒号开头)。

也可以直接将动态SQL紧跟在EXECUTE IMMEDIATE语句之后，如EXECUTE IMMEDIATE 'alter table emp enable row movement'

3.两者的异同

静态SQL为直接嵌入到PL/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子句来实现。

d.使用系统提供的PL/SQL包DBMS_SQL来实现动态SQL，关于该方式请参考后续博文。

二、动态SQL的语法

下面是动态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, ...]

1.语法描述

dynamic_SQL_string：存放指定的SQL语句或PL/SQL块的字符串变量

defined_variable1：用于存放单行查询结果，使用时必须使用INTO关键字，类似于使用SELECT ename INTO v_name FROM scott.emp；

只不过在动态SQL时，将INTO defined_variable1移出到dynamic_SQL_string语句之外。

bind_argument1：用于给动态SQL语句传入或传出参数，使用时必须使用USING关键字，IN表示传入的参数，OUT表示传出的参数，

IN OUT则既可以传入，也可传出。

RETURNING | RETURN 子句也是存放SQL动态返回值的变量。

2.使用要点

a.EXECUTE IMMEDIATE执行DML时，不会提交该DML事务，需要使用显示提交(COMMIT)或作为EXECUTE IMMEDIATE自身的一部分。

b.EXECUTE IMMEDIATE执行DDL,DCL时会自动提交其执行的事务。

c.对于多行结果集的查询，需要使用游标变量或批量动态SQL，或者使用临时表来实现。

d.当执行SQL时，其尾部不需要使用分号，当执行PL/SQL 代码时，其尾部需要使用分号。

f.动态SQL中的占位符以冒号开头，紧跟任意字母或数字表示。

三、动态SQL的使用(DDL，DCL，DML以及单行结果集)

1.使用EXECUTE IMMEDIATE处理DDL操作

下面是一个简单的DDL操作，将其封装在存储过程之中，通过传入表名来进行调用。

CREATE OR REPLACE PROCEDURE trunc_table(table_name VARCHAR2) --创建存储过程trunc_table

sql_statement VARCHAR2(100);

BEGIN

sql_statement := 'TRUNCATE TABLE ' || table_name; --为变量进行赋值，用于生成动态SQL语句

EXECUTE IMMEDIATE sql_statement; --使用EXECUTE IMMEDIATE执行动态SQL语句

END;

flasher@ORCL> create table tb2 --从scott.emp生产表tb2

2 as select empno,ename,sal,deptno from scott.emp;

flasher@ORCL> select count(1) from tb2;

COUNT(1)

----------

flasher@ORCL> exec trunc_table('tb2'); --调用存储过程来对表tb2进行truncate

flasher@ORCL> select count(1) from tb2; --表tb2被清空

COUNT(1)

----------

flasher@ORCL> insert into tb2 --重新为表tb2生成记录

2 select empno,ename,sal,deptno from scott.emp;

flasher@ORCL> commit;

2.使用EXECUTE IMMEDIATE处理DCL操作

下面使用sys帐户创建存储过程grant_sys_priv用于给用户授予权限

sys@ORCL> conn sys/redhat@orcl as sysdba

CREATE OR REPLACE PROCEDURE grant_sys_priv(priv VARCHAR2, username VARCHAR2)

sql_stat VARCHAR2(100);

BEGIN

sql_stat := 'GRANT ' || priv || ' TO ' || username;

EXECUTE IMMEDIATE sql_stat;

END;

sys@ORCL> exec grant_sys_priv('connect','usr1');

3.使用EXECUTE IMMEDIATE处理DML操作

在使用EXECUTE IMMEDIATE处理DML操作时，分为几种情况，即不带输入参数，带输入参数，既有输入也有输出参数或返回参数等不同情

这个就不用说了吧

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
PL/SQL -- 动态SQL

PL/SQL -- 动态SQL
复制链接

扫一扫