Oracle SQL 学习笔记25 - 动态SQL

执行动态SQL语句的两种方法

SQL语句执行阶段

SQL语句的执行主要分为Parse、Bind、Execute和Fetch阶段。有些阶段仅针对特定语句——例如，fetch仅针对select。

对于嵌入plsql的SQL（SELECT，DML，COMMIT和ROLLBACK），parse和bind发生于编译阶段，对于动态SQL，所有阶段都发生于运行时。

动态SQL定义

动态SQL指在运行时结构可变的SQL。在程序中表现为字符串。动态SQL可以绑定变量，可以是DDL语句，可以通过dbms_sql调用，可以通过execute immediate调用，而其本身是在运行时才确定。

本地动态SQL

本地动态SQL可以在pl/sql中直接执行，支持如下调用方法：

• EXECUTE IMMEDIATE
• OPEN-FOR
• FETCH
• CLOSE

使用 EXECUTE IMMEDIATE

可以使用 EXECUTE IMMEDIATE 在本地运行动态SQL和匿名块：

EXECUTE  IMMEDIATE  dynamic_string
  [INTO  {diefine_variable
    [,  define_variable]  ...  | record}]
  [USING  [IN|OUT|IN  OUT]  bind_argument
  [,  [IN|OUT|IN  OUT]  bind_argument]..];
  [USING  []]

其中，INTO用于处理单行查询语句的返回值。USING处理绑定变量，默认为输入。

动态SQL执行DDL语句

CREATE  PROCEDURE  create_table(
  table_name  VARCHAR2,  col_specs  VARCHAR2)  IS
BEGIN
  EXECUTE  IMMEDIATE  'CREATE  TBALE  '  ||  table_name  ||  '  ('  ||  col_space  ||  ')';
END;
/

BEGIN
  create_table('EMPLOYEE_NAMES',  'id  NUMBER(4)  PRIMARY  KEY,  name  VARCHAR2(40)');
END;
/

动态执行DML语句

CREATE  FUNCTION  del_rows(table_name  VARCHAR2)
RETURN  NUMBER  IS
BEGIN
  EXECUTE  IMMEDIATE  'DELETE  FROM  '  ||  table_name;
  RETURN  SQL%ROWCOUNT;
END;

BEGIN  DBMS_OUTPUT.PUT_LINE(
  del_rows('EMPLOYEE_NAMES')  ||  '  rows  deleted.');
END;

CREATE  PROCEDURE  add_row(table_name  VARCHAR2,  id  NUMBER,  name  VARCHAR2)  IS
BEGIN
  EXECUTE  IMMEDIATE  'INSERT  INTO  '  ||  table_name  ||  '  VALUES(:1,  :2);  USING  id,  name;
END;

动态SQL执行单行返回的查询语句

CREATE  FUNCTION  get_emp(emp_id  NUMBER)
RETURN  employees%ROWTYPE  IS
  stmt  VARCHAR2(200);
  emprec  employees%ROWTYPE;
BEGIN
  stmt  :=  'SELECT  * FROM  employees  '  ||  
             'WHERE  employee_id  =  :id';
  EXECUTE  IMMEDIATE  stmt  INTO  emprec  USING  emp_id;
  RETURN  emprec;
END;
/

DECLARE
  emprec  employees%ROWTYPE  :=  get_emp(10);
BEGIN
  DBMS_OUTPUT.PUT_LINE('Emp:  '  ||  emprec.last_name);
END;
/

动态SQL执行多行返回的查询

使用OPEN-FOR，FETCH和CLOSE处理

CREATE  PROCEDURE  list_employees(deptid  NUMBER)  IS
  TYPE  emp_refcsr  IS  REF  CURSOR;
  emp_cv  emp_refcsr;
  emprec  employees%ROWTYPE;
  stmt  VARCHAR2(200)  :=  'SELECT  *  FROM  employees';
BEGIN
  IF  deptid  IS  NULL  THEN  OPEN  emp_cv  FOR  stmt;
  ELSE
    stmt  "=  stmt  ||  '  WHERE  department_id  =  :id';
    OPEN  emp_cv  FOR  stmt  USING  deptid;
  END  IF;
  LOOP
    FETCH  WHEN  emp_cv  INTO  emprec;
    EXIT  WHEN  emp_cv%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(emprec.department_id  ||  ' '  ||  emprec.last_name);
  END  LOOP;
  CLOSE  emp_cv;
END;
/

动态执行PL/SQL Block

CREATE  FUNCTION  annual_sal(emp_id  NUMBER)
RETURN  NUMBER  IS
  plsql  varchar2(200)  :=  
    'DECLARE  '  ||
    '  emprec  employees%ROWTYPE;  '  ||
    'BEGIN  '  ||
    '  emprec  :=  get_emp(:empid);  '  ||
    '  :res  :=  emprec.salary  * 12;  '  ||
    'END;';
  result  NUMBER;
BEGIN
  EXECUTE  IMMEDIATE  plsql
    USING  IN  emp_id,  OUT  result;
      RETURN  result;
END;
/

EXECUTE  DBMS_OUTPUT.PUT_LINE(annual_sal(100))

使用本地动态SQL来编译PL/SQL代码

• ALTER PROCEDURE name COMPILE
• ALTER FUNCTION name COMPILE
• ALTER PACKAGE name COMPILE SPECIFICATION
• ALTER PACKAGE name COMPILE

CREATE  PROCEDURE  compile_plsql(name  VARCHAR2,  plsql_type VARCHAR2,  options  VARCHAR2  :=  NULL)  IS
  stmt  varchar2(200)  :=  'ALTER  '  ||  plsql_type  ||  ' '
    ||  name  ||  '  COMPILE';
BEGIN
  IF  options  IS  NOT  NULL  THEN
    stmt  :=  stmt  ||  '  '  ||  options;
  EXECUTE  IMMEDIATE  stmt;
END;
/

使用DBMS_SQL package 编译动态SQL

DBMS_SQL用于编译和执行动态SQL，包括

• OPEN_CURSOR
• PARSE
• BIND_VARIABLE
• EXECUTE
• FETCH_ROWS
• CLOSE_CURSOR

使用DBMS_SQL执行DML语句

删除行的例子

CREATE  OR  REPLACE  FUNCTION  delete_all_rows(table_name  VARCHAR2)  RETURN  NUMBER  IS
  csr_id  INTEGER;
  rows_del  NUMBER;
BEGIN
  csr_id  :=  DBMS_SQL.OPEN_CURSOR;
  DBMS_SQL.PARSE(csr_id,  
    'DELETE  FROM  '  ||  table_name,  DBMS_SQL.NATIVE);
  rows_del  :=  DBMS_SQL.EXECUTE(csr_id);
  DBMS_SQL.CLOSE_CURSOR(csr_id);
  RETURN  rows_del;
END;
/

CREATE  table  temp_emp AS SELECT  *  FROM  employees;
BEGIN
  DBMS_OUTPUT.PUT_LINE('Rows  Deleted:  '  ||
  delete_all_rows('temp_emp'));
END;
/

本地动态SQL和DBMS_SQL比较

本地动态SQL比DBMS_SQL更加易用，代码量更小，且性能好，而且支持所有pl/sql数据类型，包括用户自定义类型。而且，本地动态SQL可以将数据取得并存入pl/sql record中。

使用DBMS_METADATA 查看和管理元数据

元数据（metadata）最常见的定义为“有关数据的结构数据”，或者再简单一点就是“关于数据的信息”，例如：

1. 某个数据库中的表和视图的个数以及名称；
2. 某个表或者视图中列的个数以及每一列的名称、数据类型、长度、精度、描述等；
3. 表上定义的约束；
4. 表上定义的索引以及主键/外键信息。

DBMS_METADATA提供了元数据的提取、管理和重新提交的功能。

元数据API

Oracle提供了丰富的元数据API：

1. 将对象元数据提取保存为XML
2. 将XML文件转化为各种格式（包括转化为SQL DDL）
3. 提交XML重建对象

DBMS_METADAE 子程序

名称	子程序
OPEN	指定了将要被获取的对象的类型，其元数据的版本，以及对象模型。返回的是一个对象集的opaque上下文句柄
SET_FILTER	对将要获得的对象指定限制条件，例如：对象名称或者schema
SET_COUNT	指定一个FETCH_XXX调用能返回的对象的最大数量
GET_QUERY	返回FETCH_XXX将要用到的query的文本
SET_PARSE_ITEM	Enables output parsing，指定将要被parse和返回的对象属性
ADD_TRANSFORM	指定transform，该transform将被FETCH_XXX用来将取得的对象转为XML。
SET_TRANSFORM_PARAM, SET_REMAP_PARAM	为XSLT stylesheet（identified by transform_handle）指定参数
FETCH_XXX	返回满足条件的元数据，这些条件由OPEN和SET_FILTER决定
CLOSE	使OPEN返回的句柄无效，并清空相关状态

FETCH_XXX 子程序

名称	描述
FETCH_XML	这个函数将以XMLType的形式，返回一个对象的XML元数据
FETCH_DDL	这个函数为一个定义好的嵌入表（predefined nested table）返回DDL（用于创建或者删除对象）
FETCH_CLOB	这个函数将对象（transformed or not）作为一个CLOB返回
FETCH_XML_CLOB	在IN OUT NOCOPY中，会有CLOB类型的对象，这个存储过程返回这种对象的XML元数据，以避免昂贵的LOB拷贝。

SET_FILTER存储过程

• 语法

PROCEDURE  set_filter
(
  handle  IN  NUMBER,
  name  IN  VARCHAR2,
  value  IN  VARCHAR2|BOOLEAN|NUMBER,
  object_type_path  VARCHAR2
);

...
DBMS_METADATA.SET_FILTER(handle,  'NAME',  'HR');

Filter 过滤器

以对象类型分类的过滤机有70多个

• Named Objects
• Tables
• Objects dependent on tables
• Index
• Dependent objects
• Granted objects
• Table data
• Index statistics
• Constraints
• All object types
• Database export

设置过滤器的例子

DBMS_METADATA.SET_FILTER(handle,  'SCHEMA_EXPR',  'IN  (''  PAYROLL'',  ''HR'')');

DBMS_METADATA.SET_FILTER(handle,  'EXCLUDE_PATH_EXPR',  '=''FUNCTION'' ');

DBMS_METADATA.SET_FILTER(handle,  'EXCLUDE_PATH_EXPR',  '=''PROCEDURE'' ');

DBMS_METADATA.SET_FILTER(handle,  'EXCLUDE_PATH_EXPR',  '=''PACKAGE'' ');

DBMS_METADATA.SET_FILTER(handle,  'EXCLUDE_NAME_EXPR',  'LIKE  ''  PAYROLL%'' ',  'VIEW');

在编程中的应用

CREATE  PROCEDURE  example_one  IS
  h  NUMBER:
  th1  NUMBER;
  th2  NUMBER;
  doc  sys.ku$_ddls;
BEGIN
  h  :=  DBMS_METADATA.OPEN('SCHEAM_EXPORT');
  DBMS_METADATA.SET_FILTER(h,  'SCHEMA',  'HR');
  th1  :=  DBMS_METADATA.ADD_TRANSFORM(h,  'MODIFY',  'NULL',  'TABLE');
DBMS_METADATA.SET_REMAP_PARAM(th1,  'REMAP_TABLESPACE',  'SYSTEM',  'TBS1');
  th2  :=  DBMS_METADATA.ADD_TRANSFORM(h,  'DDL');
DBMS_METADATA.SET_REMAP_PARAM(th2,  'SQLTERMINATOR',  TRUE);
DBMS_METADATA.SET_REMAP_PARAM(th2,  'REF_CONSTRAINTS',  FALSE,  'TABLE');
LOOP
  doc  :=  DBMS_METADATE.FETCH_DDL(h);
  EXIT  WHEN  doc  IS  NULL;
END  LOOP;
DBMS_METADATA.CLOSE(h);
END:

查看metadata APIs

名称	描述
GET_XXX	返回单个对象的元数据
GET_DETPENDENT_XXX	返回依赖对象的元数据
GET_GRANTED_XXX	返回通过grant获得的对象的元数据

其中，XXX可以是DDL或者XML。

• 实例

1. 获得HR.EMPLOYEE的XML

SELECT  DBMS_METADATA.GET_XML('TABLE',  'EMPLOYEE',  'HR')
FROM  dual;

2. 获得所有“grant”给HR.EMPLOYEES的对象的DDL

SELECT  DBMS_METADATA.GET_DEPENDENT_DDL('OBJECT_GRANT',  'EMPLOYEES',  'HR')
FROM  dual;

3. 获得所有“system grant”给HR.EMPLOYEES的对象的DDL

SELECT  DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT',  'EMPLOYEES',  'HR')
FROM  dual;

-- remove storage
EXECUTE
 DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,  'STORAGE',  false);

-- remove tablespace
EXECUTE
 DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,  'TABLESPACE',  false);

-- remove SEGMENT_ATTRIBUTES
EXECUTE
 DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,  'SEGMENT_ATTRIBUTES',  false);

-- table struts
SELECT  DBMS_METADATA.GET_DDL('TABLE',  'EMPLOYEES')  FROM  dual;