PLSQL 之SQL分类和动态SQL

1、SQL 语句分类

1.1、分类方法及类型

在 Oracle 的官方手册《Oracle Database SQL Reference: Types of SQL Statements》中将 SQL 语句分为 6 类，分别是：数据定义语言语句、数据操纵语言语句、事务控制语句、会话控制语句、系统控制语句、嵌入式 SQL 语句。

按照官方的分类方法，数据定义语言的语句是非常丰富的。本人曾在一些非官方资料中看到有人把数据定义语言做了进一步的细分。例如，把 GRANT 和 REVOKE 称之为数据控制语言，即 DCL(Data Control Language)；把 AUDIT、NOAUDIT 称之为审计控制语言。

1.2、数据定义语言

数据定义语言(DDL,Data Definition Language)语句主要有以下用途：

• 1、创建、修改和删除模式对象。

• 2、授予或回收用户权限或角色。

• 3、分析表、索引或集群的信息。

• 4、打开或关闭审计选项。

• 5、向数据字典中添加注释。

DDL 语句主要有：

• CREATE，所有以 CREATE 开头的语句都是。

• ALTER，所有以 ALTER 开头的语句都是。

• DROP，所有以 DROP 开头的语句都是。

• FLASHBACK，所有以 FLASHBACK 开头的语句都是。

• TRUNCATE

• COMMENT

• RENAME

• PURGE

• GRANT

• REVOKE

• AUDIT

• NOAUDIT

• ANALYZE

• ASSOCIATE STATISTICS

• DISASSOCIATE STATISTICS

CREATE、ALTER 及 DROP 命令需要对指定对象的独占访问。例如，如果某个用户在指定的表上具有打开的事务，则其它用户发出的 ALTER TABLE 语句将失败。

GRANT、REVOKE、ANALYZE、AUDIT 及 COMMENT 命令不需要对指定对象的独占访问。例如，你可以在其他用户更新表时分析表。

Oracle 数据库在每个 DDL 语句之前和之后都会隐式地提交当前事务。有些 DDL 语句可能会导致 Oracle 数据库重新编译或重新授权模式对象，如更改对象定义的语句。

1.3、数据操纵语言

数据操作语言(DML,Data Manipulation Language)语句用于访问或操纵现有模式对象中的数据。这些语句不会隐式的提交当前事务。数据操作语言语句主要有：

• INSERT

• DELETE

• UPDATE

• SELECT

• MERGE

• LOCK TABLE

• EXPLAIN PLAN

• CALL

SELECT 语句只能访问数据库中的数据，但不能处理数据库中的数据，尽管它可以在返回查询结果之前对访问的数据进行操作。

只有在动态执行时，PL/SQL 才支持 CALL 和 EXPLAIN PLAN 语句。PL/SQL 中完全支持所有其它 DML 语句。

1.4、其它语句

事务控制语句：事务控制语句用于管理 DML 语句所做的更改。事务控制语句主要有：COMMIT、ROLLBACK、SAVEPOINT、SET TRANSACTION 共 4 个。

会话控制语句：会话控制语句用于动态管理用户会话的属性。会话控制语句是 ALTER SESSION 和 SET ROLE，这些语句不会隐式提交当前事务。PL/SQL 不支持会话控制语句。

系统控制语句：单一系统控制语句 ALTER SYSTEM 动态管理 Oracle 数据库实例的属性。 此语句不会隐式提交当前事务，并且在 PL/SQL 中不受支持。

嵌入式 SQL 语句：嵌入式 SQL 语句将 DDL、DML 和事务控制语句嵌入到 SQL 程序中，个人感觉这个概念挺模糊的。嵌入式 SQL 语句由 Oracle 预编译器支持。

2、动态 SQL 理论

在 PL/SQL 程序块中的 SQL 分为两种：静态 SQL 和动态 SQL。静态 SQL 是早期绑定(early binding)的，是指 SQL 语句在编译时是明确的，执行的是确定对象，大多数的编译情况属于这种类型。动态 SQL 是后期绑定(late binding)的，是指 SQL 语句在编译时是不确定的，编译程序对动态语句部分不进行处理，只是在程序运行时动态地创建语句、对语句进行语法分析并执行该语句。

2.1、动态 SQL 的用途

1、执行 DDL、DCL 语句：在 PL/SQL 程序中执行 DDL 语句、DCL 语句和会话控制语句必须使用动态 SQL 语句。因为这几类语句都不能在 PL/SQL 程序中直接运行的。

2、构造通用 SQL 处理：为了开发灵活和通用的程序，往往需要利用数据字典来生成相关的脚本。由于动态 SQL 特有的灵活性，我们很容易按照某种共性去构造通用和重用功能的代码，例如基于某个表的动态字段查询。

3、执行复杂业务逻辑查询：动态 SQL 语句的另外一个重要应用场景是复杂业务逻辑查询和报表开发环境。在这两种情况下，查询参数是由用户的输入开关和输入项而定，甚至查询出的字段也根据查询参数有所不同，不用动态 SQL 很难实现。

尽管动态 SQL 如此灵活和强大，但凡事总有利弊。首先，动态 SQL 语句无法在编译前期检查 SQL 是否正确，必须等到运行期才会发现问题；其次，静态 SQL 是一次解析，多次执行，虽然动态 SQL 也可以使用绑定变量的方式，但也可能会带来一些意想不到的性能问题。例如，绑定变量在 SQL 要访问的表存在数据倾斜时会提供错误的执行计划；最后，动态 SQL 语句可读性较差，不易维护。

除非是上述三类情况，否则不应该使用动态 SQL 语句。我曾接手过一个大量使用动态 SQL 语句的数据库，可以看的出来，这些存储过程是从网上或其它库中拷贝过来的，而且那些动态 SQL 语句大多也不够灵活和通用，又长期没怎么维护，很多一调用就报错，基于这个数据库的项目的可维护性可想而知！

2.2、动态 SQL 的语法

理论说了这么多，那到底何为动态 SQL 语句呢？简单的说就是存储在字符串变量中的 SQL 命令。如下例中的v_sql即为动态 SQL 语句：

DECLARE
  v_sql VARCHAR2(200):='SELECT t.course_name, t.course_desc FROM demo.t_course t';

早期 Oracle 提供了DBMS_SQL包来执行动态 SQL 语句，后来又提供了本地动态 SQL(Native Dynamic SQL)，用来代替DBMS_SQL执行动态 SQL 语句。使用本地动态 SQL 比使用DBMS_SQL更简单，且大部分情况比DBMS_SQL更高效。但 Oracle 依然支持DBMS_SQL，所以动态 SQL 既可以通过本地动态 SQL 来执行，也可以通过DBMS_SQL包来执行。

通过本地动态 SQL 执行动态 SQL 语句的语法是：

EXECUTE IMMEDIATE dynamic_string
  {INTO {define_variable1 [,define_variable2 ...] | record_name}}
  [USING [IN|OUT|IN OUT] bind_argument1 [,[IN|OUT|IN OUT] bind_argument2] ...]
  [returning_clause];

EXECUTE IMMEDIATE 解析并马上执行动态的 SQL 语句或非运行时创建的 PL/SQL 块。语法中的 INTO 子句用于将查询返回的结果值传递给已定义的标量类型变量或记录变量，但这里只能接收单行查询的返回结果。

使用 EXECUTE IMMEDIATE 方式比使用DBMS_SQL方式写法更简洁，运行数度也更快。但DBMS_SQL比 EXECUTE IMMEDIATE 更强大，某些极端复杂的情况只有DBMS_SQL能实现，不过话又说回来了，如今过于复杂的功能基本也没人会依赖于数据库的，所以我个人觉得没必要掌握DBMS_SQL。但如果不幸需要维护使用了DBMS_SQL的老代码时，可以参考：《Oracle Database PL/SQL Packages and Types Reference: DBMS_SQL》。

2.3、绑定变量

绑定变量也就是动态 SQL 语句中的一个占位符，它告诉 Oracle 现在需要生成一个执行计划，随后会为这个占位符提供一个值。绑定变量的占位符是在动态 SQL 语句中指定的，可以是 :1、:2、:3 之类的，也可以是 :a、:b、:c 这样的定义。

绑定变量在动态 SQL 中应用，主要是为了实现一次解析，多次执行，从而提高效率的目的。绑定变量的优势是可以在库缓存中共享游标，这样可以避免硬解析及与之相关的额外开销。因此，绑定变量是一种减少应用程序在分析查询时使用栓锁数目的有效方法，其次，使用绑定变量可以避免注入***。

本地动态 SQL 语法中的 USING 子句用于在 DML 需要传入的变量中、共享库缓存游标、避免硬解析。USING 后的是绑定变量的实际值，按照动态 SQL 中绑定变量的顺序赋值，不管名字是否重复，全部都要赋值。而 RETURNING INTO 子句主要用于 DML 语句中所返回被影响的列值。

使用绑定变量的几个限制：

• 1、绑定变量不能直接传入 NULL 值。

• 2、USING 后的绑定变量必须是 SQL 类型，例如：字符型、×××、浮点型等，而 PL/SQL 的 BOOLEAN 等类型是不支持的。

• 3、绑定变量不能用表名或列别名来替换。

• 4、绑定变量只能代替 SQL 或 PL/SQL 中的字面量、变量和简单表达式。

• 5、动态 DLL 语句中不允许出现绑定变量。因为在运行期间，引擎要解析动态语句中的对象，确保 SQL 语法正确，而绑定变量此时还是变量，这样 Oracle 会认为对象不存在。此时可以通过拼接字符串的方式来实现。

3、动态 SQL 实战

3.1、封装执行 DML 的通用存储过程

示例 1（证明动态 DLL 语句中不允许出现绑定变量）：

DECLARE
  v_sql VARCHAR2(100);
  v_table_name VARCHAR2(30):='demo.t2';BEGIN
  v_sql := 'DROP TABLE :1';  EXECUTE IMMEDIATE v_sql USING v_table_name;
  
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('异常：'||SQLCODE||': '||SUBSTR(SQLERRM,1,64));END;

上例的输出结果是：“异常：-903: ORA-00903: 表名无效”。这也就证明了 DROP TABLE 语句中是不允许使用绑定变量的，其它 DDL 也一样，这里就不一一证明了。上例的正确写法是（将绑定变量换成拼接字符串）：

DECLARE
  v_sql VARCHAR2(100);
  v_table_name VARCHAR2(30):='demo.t2';BEGIN
  v_sql := 'DROP TABLE '||v_table_name;  EXECUTE IMMEDIATE v_sql;END;

示例 2（编写一个简单的动态查询，遍历输出所有课程信息）：

DECLARE
  v_sql VARCHAR2(500);
  v_table_name VARCHAR2(30):='T_COURSE';
  v_field_list VARCHAR2(300);
  v_cursor SYS_REFCURSOR;
  v_course demo.t_course%ROWTYPE;
BEGIN
  v_sql := 'SELECT WM_CONCAT(t.column_name) FROM USER_TAB_COLUMNS t WHERE t.table_name=:1';
  EXECUTE IMMEDIATE v_sql INTO v_field_list USING v_table_name;
  
  v_sql := 'SELECT '||v_field_list||' FROM '||v_table_name;
  OPEN v_cursor FOR v_sql;  LOOP
    FETCH v_cursor INTO v_course;    EXIT WHEN v_cursor%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(v_course.course_id||','||v_course.course_name||','||v_course.course_desc);
  END LOOP;
  CLOSE v_cursor;END;

示例 3（封装一个根据某个字段值删除表中数据的通用存储过程）：

CREATE OR REPLACE PROCEDURE sp_delete_data(
  p_table IN VARCHAR2,       -- 删除表名称
  p_field IN VARCHAR2 := '', -- 删除条件字段名
  p_value IN VARCHAR2 := ''  -- 删除条件字段值)/**************************************************
功能：删除 p_table 表中 p_field 字段值为 p_value 的数据

修订记录：
版本号    修订时间      修订人    描述
1.0.0     2017-05-17    hanzz     1.创建此存储过程
**************************************************/AS
  v_sql VARCHAR2(500);BEGIN
  IF p_field IS NULL THEN -- 未指定字段时，删除整个表的数据
    v_sql := 'DELETE FROM '||p_table;
  ELSE
    v_sql := 'DELETE FROM '||p_table||' t WHERE t.'||p_field||'='''||p_value||'''';
  END IF;
  
  EXECUTE IMMEDIATE v_sql; -- 执行删除语句
  COMMIT; -- 提交删除
END;

调用：

BEGIN
  sp_delete_data('demo.t_course','course_id','1'); -- 删除课程表中 ID 为 1 的课程
  sp_delete_data(p_table => 'demo.t_course'); -- 删除课程表中的所有数据END;

示例 4（封装一个根据某个字段值修改另一个字段值的通用存储过程）：

CREATE OR REPLACE PROCEDURE sp_update_data(
  p_table_name IN VARCHAR2,        -- 修改表名称
  p_update_field IN VARCHAR2,      -- 被修改字段名
  p_update_value IN VARCHAR2,      -- 被修改字段值
  p_where_field IN VARCHAR2 := '', -- 修改条件字段名
  p_where_value IN VARCHAR2 := ''  -- 修改条件字段值)/**************************************************
功能：将 p_table_name 表中 p_where_field 字段值为 p_where_value 的行中 p_update_field 字段的值修改为 p_update_value

修订记录：
版本号    修订时间      修订人    描述
1.0.0     2017-05-17    hanzz     1.创建此存储过程
**************************************************/AS
  v_sql VARCHAR2(500);BEGIN
  IF p_where_field IS NULL THEN -- 未指定字段时，修改整个表的数据
    v_sql := 'UPDATE '||p_table_name||' SET '||p_update_field||'=:1';    EXECUTE IMMEDIATE v_sql USING p_update_value;
  ELSE
    v_sql := 'UPDATE '||p_table_name||' SET '||p_update_field||'=:1'||' WHERE '||p_where_field||'=:2';
    EXECUTE IMMEDIATE v_sql USING p_update_value,p_where_value; -- 执行修改语句
  END IF;

  COMMIT; -- 提交修改
END;

调用：

BEGIN
  sp_update_data('t_course','course_desc','汉语','course_id','1'); -- 将 ID 为 1 的课程备注改为汉语
  sp_update_data('t_course','course_desc','计算机'); -- 将表中所有课程备注改为计算机END;

3.2、批量编译数据库对象

视图、函数、存储过程、包、触发器等模式对象依赖于基表，当基表被修改后，可能会导致 Oracle 数据库重新编译这些模式对象，如果编译未通过就会直接挂掉（在 PL/SQL Developer 里看的话，对象名左上角会有一个红色的星号）。而且好像一旦编译未通过就不再重新编译了，这样会导致即使正确的修改，只要中间过程有检测到编译未通过最终也会挂掉。所以有时候不得不在所有修改完成后对那些挂掉的对象进行编译。

编译对象的语法：

ALTER PROCEDURE|FUNCTION|PACKAGE [.] COMPILE [BODY];

编译示例可参考：《.Net程序员学用Oracle系列(7)：视图、函数、存储过程、包》。但有时候挂掉的对象太多，如果为每个挂掉的对象都写条编译语句来编译显然效率太低，这时候可以用下面这段 PL/SQL 实现批量编译数据库中所有已挂掉的模式对象（包括：视图、函数、存储过程、包、触发器）。

DECLARE
  v_sql VARCHAR2(200);BEGIN
  FOR obj IN(    SELECT t.object_type,t.object_name FROM USER_OBJECTS t
    WHERE t.object_type IN('VIEW','FUNCTION','PROCEDURE','PACKAGE','TRIGGER') AND t.status='INVALID'
    ORDER BY t.object_type
  )  LOOP
    v_sql:='ALTER '||obj.object_type||' '||USER||'.'||obj.object_name||' COMPILE';
    DBMS_OUTPUT.PUT_LINE(v_sql||';');    EXECUTE IMMEDIATE v_sql;  END LOOP;END;

3.3、统计数据库的总行数

示例 1（统计当前数据库的总行数）：

DECLARE
  v_cursor SYS_REFCURSOR;
  v_sql VARCHAR2(200);    -- 存放统计行数的 SQL 语句
  v_rows INT := 0;        -- 单表行数
  v_total_rows INT := 0;  -- 总行数
  v_total_tables INT :=0; -- 总表数BEGIN
  OPEN v_cursor FOR
    SELECT 'SELECT COUNT(1) FROM '||LOWER(USER)||'.'||LOWER(t.table_name) 
    FROM USER_TABLES t;
  LOOP
    FETCH v_cursor INTO v_sql;
    EXIT WHEN v_cursor%NOTFOUND;    EXECUTE IMMEDIATE v_sql INTO v_rows; -- 执行统计的 SQL 语句
    v_total_rows := v_total_rows + v_rows; -- 逐表累加行数
    v_total_tables := v_total_tables + 1;  -- 累加表数
  END LOOP;
  CLOSE v_cursor;
  DBMS_OUTPUT.PUT_LINE(USER||' 的总表数是：'||v_total_tables);
  DBMS_OUTPUT.PUT_LINE(USER||' 的总行数是：'||v_total_rows);END;

示例 2（统计指定用户所拥有表的总行数）：

DECLARE
  v_cursor SYS_REFCURSOR;
  v_owner VARCHAR2(30):='JIRA';
  v_sql VARCHAR2(200);    -- 存放统计行数的 SQL 语句
  v_rows INT := 0;        -- 单表行数
  v_total_rows INT := 0;  -- 总行数
  v_total_tables INT :=0; -- 总表数BEGIN
  OPEN v_cursor FOR
    SELECT 'SELECT COUNT(1) FROM '||LOWER(v_owner)||'.'||LOWER(t.table_name) 
    FROM DBA_TABLES t WHERE t.owner='JIRA';
  LOOP
    FETCH v_cursor INTO v_sql;
    EXIT WHEN v_cursor%NOTFOUND;    EXECUTE IMMEDIATE v_sql INTO v_rows; -- 执行统计的 SQL 语句
    v_total_rows := v_total_rows + v_rows; -- 逐表累加行数
    v_total_tables := v_total_tables + 1;  -- 累加表数
  END LOOP;
  CLOSE v_cursor;
  DBMS_OUTPUT.PUT_LINE(v_owner||' 的总表数是：'||v_total_tables);
  DBMS_OUTPUT.PUT_LINE(v_owner||' 的总行数是：'||v_total_rows);END;

转载于:https://blog.51cto.com/12902932/1928063