oracle 11g PL/SQL Programming学习九

最新推荐文章于 2024-07-22 15:12:37 发布

iteye_14608

最新推荐文章于 2024-07-22 15:12:37 发布

阅读量203

点赞数

文章标签：数据库

----------------------------------------------------------------------------
-----------------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

iteye_14608

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
oracle 11g PL/SQL Programming学习九

---------------------------------------------------------------------------------------------PL/SQL学习笔记系列 By Cryking-----------------------------------------转载请注明出处,谢谢!------------------------...
复制链接

扫一扫