oracle 11g PL/SQL Programming学习九

lltgmt9171

----------------------------------------------------------------------------

-----------------PL/SQL学习笔记系列 By Cryking-----------------

------------------------转载请注明出处,谢谢!------------------------
　　

第三部分 PL/SQL 高级编程

第11章 动态SQL(Dynamic SQL)

NDS(native Dynamic SQL)在9i中出现,10g和11g中得到提高.它提供了一个可替代DBMS_SQL的方法.

NDS和DBMS_SQL包让你可以在运行时创建和执行动态sql.

本章分为三大部分:

动态SQL结构

NDS

DBMS_SQL包


动态sql语句是非常有用的技术,它让你在PL/SQL程序运行时可以创建和执行查询.

在使用动态sql语句中,NDS是首先选择的.

大部分时候都可以使用NDS来解决,只有当动态语句的输出值的数量和数据类型未知时,你才必须使用DBMS_SQL包来解决.

动态SQL结构(Dynamic SQL Architecture)

动态SQL可以灵活的解决很多问题.它可以让你写匿名函数.

匿名函数的声明和正常函数一样,但是它们有未知的参数列表和返回类型.

当你有NDS和DBMS_SQL两种方法时,你应该先考虑NDS,因为dbms_sql只是为了向下兼容而存在的.

动态sql的结构中,你可以使用字符串连接或者使用占位符.使用字符串连接容易受到SQL注入攻击.

而使用占位符可以避免这种SQL注入攻击.这些占位符就是绑定变量.它们就像是动态SQL中的形式参数.

编译时,将不会对动态语句中的对象进行验证.

运行动态语句的过程包含四个步骤:

1.在运行时的语句被解析

2.将有占位符的语句映射实际参数到形式参数

3.执行语句

4.返回值给调用语句



本地动态SQL(Native Dynamic SQL (NDS))

NDS是一个有用并且简单的工具,它非常容易使用和部署.



动态语句

使用动态DDL语句,可以在编译时避免失败,因为编译时不检查动态语句中的对象及语法.

示例1:
--删除某个对象,如使用静态语句,当此对象不存在时,会报错
--使用动态SQL则避免了抛出错误
00:34:43 SCOTT@orcl> BEGIN
00:34:45   2    -- Use a loop to check whether to drop a sequence.
00:34:45   3    FOR i IN (SELECT null
00:34:45   4                FROM user_objects
00:34:45   5               WHERE object_name = 'SAMPLE_SEQUENCE') LOOP
00:34:45   6      EXECUTE IMMEDIATE 'DROP SEQUENCE sample_sequence';
00:34:45   7      dbms_output.put_line('Dropped [sample_sequence].');
00:34:45   8    END LOOP;
00:34:45   9  END;
00:34:46  10  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
00:34:49 SCOTT@orcl> create SEQUENCE sample_sequence;

Sequence created.

Elapsed: 00:00:00.03
00:35:08 SCOTT@orcl> BEGIN
00:35:15   2    -- Use a loop to check whether to drop a sequence.
00:35:15   3    FOR i IN (SELECT null
00:35:15   4                FROM user_objects
00:35:15   5               WHERE object_name = 'SAMPLE_SEQUENCE') LOOP
00:35:15   6      EXECUTE IMMEDIATE 'DROP SEQUENCE sample_sequence';
00:35:15   7      dbms_output.put_line('Dropped [sample_sequence].');
00:35:15   8    END LOOP;
00:35:15   9  END;
00:35:17  10  /
Dropped [sample_sequence].

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.09


DML动态语句

DML动态语句经常是在运行时进行拼接.字符串拼接带来的问题就是sql注入.

使用DBMS_ASSERT包可以对输入参数进行验证以防SQL注入攻击.

示例:
　　

21:20:34 SCOTT@orcl> create table emp_bak as select * from emp;
Table created.
Elapsed: 00:00:01.16
21:21:38 SCOTT@orcl> CREATE OR REPLACE PROCEDURE insert_item(table_name VARCHAR2,
21:21:40   2                                          pename     varchar2) IS
21:21:40   3    stmt VARCHAR2(2000);
21:21:40   4  BEGIN
21:21:40   5    --动态DML语句拼接,使用dbms_assert防止sql注入
21:21:40   6    stmt := 'INSERT INTO ' || dbms_assert.simple_sql_name(table_name) ||
21:21:40   7            '(EMPNO,ENAME) SELECT MAX(EMPNO)+1,' ||
21:21:40   8            dbms_assert.enquote_literal(pename) || ' FROM ' ||
21:21:40   9            dbms_assert.simple_sql_name(table_name);
21:21:40  10    dbms_output.put_line(stmt);
21:21:40  11    EXECUTE IMMEDIATE stmt;
21:21:40  12  END insert_item;
21:21:40  13  /

Procedure created.

Elapsed: 00:00:00.18
21:21:41 SCOTT@orcl> BEGIN
21:21:50   2    insert_item(table_name => 'EMP', pename => 'CryTest');
21:21:50   3  END;
21:21:51   4  /
INSERT INTO EMP(EMPNO,ENAME) SELECT MAX(EMPNO)+1,'CryTest' FROM EMP

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.04
21:21:51 SCOTT@orcl> select empno,ename from emp where ename='CryTest';

EMPNO ENAME
---------- ----------
7935 CryTest

1 row selected.

Elapsed: 00:00:00.01

　　

--dbms_assert.simple_sql_name简单的检查名称是否符合sql命名,它不检查长度
21:22:40 SCOTT@orcl> BEGIN insert_item(table_name => '00sss', pename => 'CryTest
_ERR'); END;
21:23:11   2  /
BEGIN insert_item(table_name => '00sss', pename => 'CryTest_ERR'); END;
*
ERROR at line 1:
ORA-44003: invalid SQL name
ORA-06512: at "SYS.DBMS_ASSERT", line 146
ORA-06512: at "SCOTT.INSERT_ITEM", line 6
ORA-06512: at line 1


Elapsed: 00:00:00.04



其中dbms_assert.enquote_literal给字符串首尾自动加上单引号,并对字符串里的单引号进行验证.(验证单引号必须成对出现)


关于SQL注入攻击

SQL注入攻击就是尝试以不成对的引号字符串传入到SQL语句中.

动态SQL就是一个黑客可以试图利用你的代码的地方.

oracle现在使用DBMS_ASSERT包来帮助你来防止SQL注入攻击.

它有以下的函数:

1.ENQUOTE_LITERAL

它接受一个字符串输入,给字符串首尾加上单引号后返回.

2.ENQUOTE_NAME

它将一个字符串首尾加上双引号之后返回.它有一个可选的参数控制是否需要将输入的字符串转换为大写.

3.NOOP

不进行任何验证,输入是什么,返回就是什么
21:43:47 SCOTT@orcl> select dbms_assert.noop('input Test') a from dual;

A
-------------------------
input Test

1 row selected.

Elapsed: 00:00:00.01


4.QUALIFIED_SQL_NAME

它验证输入的字符串是否是一个有效的模式对象名称.
21:44:00 SCOTT@orcl> select dbms_assert.QUALIFIED_SQL_NAME('emp01') col from dual;

COL
-----------------
emp01

1 row selected.

Elapsed: 00:00:00.01
21:46:39 SCOTT@orcl> select dbms_assert.QUALIFIED_SQL_NAME('01emp') col from dual;
select dbms_assert.QUALIFIED_SQL_NAME('01emp') col from dual
*
ERROR at line 1:
ORA-44004: invalid qualified SQL name
ORA-06512: at "SYS.DBMS_ASSERT", line 188


Elapsed: 00:00:00.01





5.SCHEMA_NAME

它验证输入字符串是否是一个有效的模式名称.
21:48:25 SCOTT@orcl> select dbms_assert.SCHEMA_NAME('emp') col from dual;
select dbms_assert.SCHEMA_NAME('emp') col from dual
*
ERROR at line 1:
ORA-44001: invalid schema
ORA-06512: at "SYS.DBMS_ASSERT", line 243


Elapsed: 00:00:00.01
21:48:34 SCOTT@orcl> select dbms_assert.SCHEMA_NAME('SCOTT') A from dual;

A
----------
SCOTT

1 row selected.

Elapsed: 00:00:00.01



6.SIMPLE_SQL_NAME

它简单验证输入的字符串是否是一个有效的模式对象名称.

7.SQL_OBJECT_NAME

它验证输入的字符串是否是一个有效的模式对象名.
21:53:31 SCOTT@orcl> select dbms_assert.SQL_OBJECT_NAME('empEMP01') col from dual;
select dbms_assert.SQL_OBJECT_NAME('empEMP01') col from dual
*
ERROR at line 1:
ORA-44002: invalid object name
ORA-06512: at "SYS.DBMS_ASSERT", line 293


Elapsed: 00:00:00.02
21:53:55 SCOTT@orcl> select dbms_assert.SIMPLE_SQL_NAME('empEMP01') col from dual;

COL
------------
empEMP01

1 row selected.

Elapsed: 00:00:00.00
21:54:31 SCOTT@orcl> select dbms_assert.SQL_OBJECT_NAME('emp') A from dual;

A
----------
emp

1 row selected.

Elapsed: 00:00:00.01

当使用绑定变量的时候,不需要使用DBMS_ASSERT包来防止SQL注入攻击.


带占位符的动态语句

占位符就像是形式参数,它们零散的分布在动态sql语句里面.你使用USING子句来向它们传递实际参数值.

USING子句可带以逗号隔开的参数列表.它们缺省情况下是IN模式(传值模式).

你可以手动指定IN OUT或OUT模式.

oracle 11g文档对占位符变量有几点建议:

1.如果动态的SELECT语句返回的结果最多一行,你应该通过INTO子句来返回值.

这可能需要你以引用游标的方式来打开这个语句,或者将SQL语句放到一个带括号的匿名块中.

(OPEN FOR 游标 或者 OPEN FOR (动态SQL))

如果返回的值后面要用到,你不能在USING子句中使用IN OUT或OUT模式的参数.

2.如果动态的SELECT语句返回的结构多于一行,你应当通过BULK COLLECT INTO子句来返回值.

3.如果是动态的DML语句,并且只有输入的占位符,你应该将它们放到USING子句中.

4.如果是动态的DML语句,并且使用了RETURING INTO子句,你应该将输入的值放到USING子句中,将输出的值放到RETURNING INTO子句中.

5.如果是动态的PL/SQL匿名块或CALL语句,你应该在USING子句中使用输入和输出参数.

此时你应该根据需要将USING子句中的参数设置为IN OUT模式或OUT模式或IN模式.

示例:
23:40:52 SCOTT@orcl> CREATE OR REPLACE PROCEDURE insert_item(pempno number, pename varchar2) IS
23:40:53   2    stmt VARCHAR2(2000);
23:40:53   3    --使用占位符
23:40:53   4  BEGIN
23:40:53   5    stmt := 'INSERT INTO emp_bak '
23:40:53   6    || '(EMPNO,ENAME) '
23:40:53   7    || 'SELECT :EMPNO,:ENAME'
23:40:53   8    || ' FROM DUAL';
23:40:53   9    dbms_output.put_line(stmt);
23:40:53  10    EXECUTE IMMEDIATE stmt
23:40:53  11      USING pempno, pename;
23:40:53  12  END insert_item;
23:40:55  13  /

Procedure created.

Elapsed: 00:00:00.10
23:40:56 SCOTT@orcl> BEGIN insert_item(pempno => 9999, pename => 'CryTest'); END
;
23:43:42   2  /
INSERT INTO emp_bak (EMPNO,ENAME) SELECT :EMPNO,:ENAME FROM DUAL

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.02



与上面的使用字符串拼接的方法有所不同的是,这里使用了绑定变量.

USING中的实际参数个数应当要与动态SQL中的占位符个数一致.
应当注意表名等不能使用绑定变量.


　　一般情况下,我们应当首选绑定变量的这种方式来执行动态SQL.
　　带输入和输出的动态语句

使用NDS可以非常方便绑定输入和获得输出.而使用DBMS_SQL方法将会是很冗长的.

示例:


23:54:52 SCOTT@orcl> DECLARE
23:55:08   2    -- Define explicit record structure.
23:55:08   3    TYPE emp_record IS RECORD(
23:55:08   4      empno number,
23:55:08   5      ename VARCHAR2(60));
23:55:08   6    -- Define dynamic variables.
23:55:08   7    emp_cursor SYS_REFCURSOR;
23:55:08   8    emp_row    emp_record;
23:55:08   9    stmt       VARCHAR2(2000);
23:55:08  10  BEGIN
23:55:08  11    -- Set statement.
23:55:08  12    stmt := 'SELECT empno, ename FROM emp_bak ' ||
23:55:08  13            'WHERE ename = :input';
23:55:08  14    -- Open and read dynamic cursor, then close it.
23:55:08  15    OPEN emp_cursor FOR stmt
23:55:08  16      USING 'SCOTT';
23:55:08  17    LOOP
23:55:08  18      FETCH emp_cursor
23:55:08  19        INTO emp_row;
23:55:08  20      EXIT WHEN emp_cursor%NOTFOUND;
23:55:08  21      dbms_output.put_line('[' || emp_row.empno || '][' || emp_row.ename || ']');
23:55:08  22    END LOOP;
23:55:08  23    CLOSE emp_cursor;
23:55:08  24  END;
23:55:09  25  /
[7788][SCOTT]

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01



--使用BULK COLLECT INTO处理返回多行的动态SELECT语句
00:02:47 SCOTT@orcl> DECLARE
00:03:17   2    -- Define explicit record structure.
00:03:17   3    TYPE emp_record IS RECORD(
00:03:17   4      empno number,
00:03:17   5      ename VARCHAR2(60));
00:03:17   6    TYPE emp_collection IS TABLE OF emp_record;
00:03:17   7    -- Define dynamic variables.
00:03:17   8    emp_cursor SYS_REFCURSOR;
00:03:17   9    emp_rows   emp_collection;
00:03:17  10    stmt       VARCHAR2(2000);
00:03:17  11  BEGIN
00:03:17  12    -- Set statement.
00:03:17  13    stmt := 'SELECT empno, ename ' || 'FROM emp_bak ' ||
00:03:17  14            'WHERE SUBSTR(ename,0,1) = :input';
00:03:17  15    -- Open and read dynamic cursor, then close it.
00:03:17  16    OPEN emp_cursor FOR stmt USING 'S';
00:03:17  17    FETCH emp_cursor BULK COLLECT INTO emp_rows;
00:03:17  18    for i in 1 .. emp_rows.count loop
00:03:17  19      dbms_output.put_line('[' || emp_rows(i).empno || '][' || emp_rows(i).ename || ']');
00:03:17  20    END LOOP;
00:03:17  21    CLOSE emp_cursor;
00:03:17  22  END;
00:03:18  23  /
[7369][SMITH]
[7788][SCOTT]

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.11





--使用CLOB的动态SQL语句

--注意这种更新CLOB字段的方法
00:12:10 SCOTT@orcl> CREATE TABLE ITEM AS
00:12:31   2  SELECT 1 ITEM_ID
00:12:31   3  ,'TEST_TITLE' item_title
00:12:31   4  ,TO_CLOB('Just for Test') item_desc FROM DUAL;

Table created.

Elapsed: 00:00:00.08
00:15:43 SCOTT@orcl> DECLARE
00:15:44   2    -- Define explicit record structure.
00:15:44   3    target CLOB;
00:15:44   4    source VARCHAR2(2000) := 'FOR APPEND TEST!';
00:15:44   5    movie  VARCHAR2(60) := 'TEST_TITLE';
00:15:44   6    stmt   VARCHAR2(2000);
00:15:44   7  BEGIN
00:15:44   8    -- Set statement.
00:15:44   9    stmt := 'UPDATE item ' || 'SET item_desc = empty_clob() ' ||
00:15:44  10            ' WHERE item_title = :input ' ||
00:15:44  11            'RETURNING item_desc INTO :descriptor';
00:15:44  12    EXECUTE IMMEDIATE stmt
00:15:44  13      USING movie
00:15:44  14      RETURNING INTO target;--因为empty_clob(),所以target返回是空的
--下面才把要更新的值放到target里,达到真正的更新字段的目的
00:15:44  15    dbms_lob.writeappend(target, LENGTH(source), source);
00:15:44  16  END;
00:15:45  17  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.14
00:16:26 SCOTT@orcl> col item_desc format a18
00:16:32 SCOTT@orcl> select *  from item;

ITEM_ID ITEM_TITLE ITEM_DESC
---------- ---------- ------------------
1 TEST_TITLE FOR APPEND TEST!

1 row selected.

Elapsed: 00:00:00.01