PL/SQL-->DBMS_DDL包的使用

PL/SQL-->DBMS_DDL包的使用

      为了便于建立性能良好的PL/SQL程序，Oracle提供了大量的系统包供使用。Oracle提供的这些包扩展并增强了数据库的一些功能，以及突破了PL/SQL的一些限制。
      本文讲述了Oracle提供的包DBMS_DDL，以及其使用方法。


一、      使用DBMS_DDL包可以对包，包体，存储过程，函数，触发器等等进行编译，以及为数据库对象提供一些统计信息。

      下面列出几个常用的过程

      1.ALTER_COMPILE    --编译对象

              PROCEDURE DBMS_DDL.ALTER_COMPILE
                    (type IN VARCHAR2        --PROCEDURE, FUNCTION, PACKAGE, PACKAGE BODY or TRIGGER.
                    ,schema IN VARCHAR2
                    ,name IN VARCHAR2);

              与之相等的操作：ALTER PROCEDURE | FUNCTION | PACKAGE [.] COMPILE [BODY]

              --下面创建一个过程来对数据库中特定用户的无效对象进行重新编译

                      CREATE OR REPLACE PROCEDURE recompile
                            (status_in IN VARCHAR2 := 'INVALID',
                              name_in IN VARCHAR2 := '%',
                              type_in IN VARCHAR2 := '%',
                              schema_in IN VARCHAR2 := USER)
                      IS
                            v_objtype VARCHAR2(100);
                            err_status NUMERIC;
                            CURSOR obj_cur IS   
                                  SELECT owner, object_name, object_type
                                      FROM ALL_OBJECTS
                                    WHERE status LIKE UPPER (status_in)
                                        AND object_name LIKE UPPER (name_in)
                                        AND object_type LIKE UPPER (type_in)
                                        AND owner LIKE UPPER (schema_in)
                                    ORDER BY
                                        DECODE (object_type,
                                              'PACKAGE', 1,
                                              'FUNCTION', 2,
                                              'PROCEDURE', 3,
                                              'PACKAGE BODY', 4);
                      BEGIN
                            FOR rec IN obj_cur
                            LOOP
                                  IF rec.object_type = 'PACKAGE'
                                  THEN
                                        v_objtype := 'PACKAGE SPECIFICATION';
                                  ELSE
                                        v_objtype := rec.object_type;
                                  END IF;  

                                  DBMS_DDL.ALTER_COMPILE (v_objtype, rec.owner, rec.object_name);

                                  DBMS_OUTPUT.PUT_LINE
                                        ('Compiled ' || v_objtype || ' of ' ||
                                          rec.owner || '.' || rec.object_name);  
                            END LOOP;

                      EXCEPTION
                            WHEN OTHERS THEN
                            BEGIN
                                      err_status := SQLCODE;
                                      DBMS_OUTPUT.PUT_LINE(' Recompilation failed : ' || SQLERRM(err_status));
                                      IF ( obj_cur%ISOPEN) THEN
                                            CLOSE obj_cur;
                                      END IF;
                            END;
                      END;

                      scott@ORCL> exec recompile(schema_in=>'SCOTT');
                      Compiled FUNCTION of SCOTT.F_NEGATIVE
                      Compiled PROCEDURE of SCOTT.COMPUTE
                      Compiled TRIGGER of SCOTT.E_D

                      PL/SQL procedure successfully completed.

                 
      2.ANALYZE_OBJECT    --收集表，索引，簇等的统计信息

              PROCEDURE DBMS_DDL.ANALYZE_OBJECT
                    (type IN VARCHAR2                    --TABLE, CLUSTER or INDEX
                    ,schema IN VARCHAR2
                    ,name IN VARCHAR2
                    ,method IN VARCHAR2                --ESTIMATE, COMPUTE or DELETE
                    ,estimate_rows IN NUMBER DEFAULT NULL
                    ,estimate_percent IN NUMBER DEFAULT NULL
                    ,method_opt IN VARCHAR2 DEFAULT NULL) --[FOR TABLE ][ FOR ALL [INDEXED] COLUMNS] [SIZE n][ FOR ALL INDEXES ]
                    ,partname    IN VARCHAR2 DEFAULT NULL);   

              与之相等的操作：ANALYZE TABLE|CLUSTER|INDEX [.] [] STATISTICS [SAMPLE [ROWS|PERCENT]]

scott@ORCL> exec dbms_ddl.analyze_object('TABLE','SCOTT','EMP','ESTIMATE');
                      PL/SQL procedure successfully completed.           

      3.DBMS_DDL.WRAP --使用wrap函数可以加密子程序

              该函数使用了3个重载函数，即可以使用3种不同的方式来对子程序进行动态加密

                      DBMS_DDL.WRAP(                  --方式一
                            ddl          VARCHAR2)    --接收VARCHAR2类型的输入
                          RETURN VARCHAR2;

                      DBMS_DDL.WRAP(                  --方式二
                            ddl          DBMS_SQL.VARCHAR2S,        --允许大的DDL语句的输入,dbms_sql.varchar2s限制为每行256字节
                            lb            PLS_INTEGER,
                            ub            PLS_INTEGER)
                          RETURN DBMS_SQL.VARCHAR2S;             

                      DBMS_DDL.WRAP(                --方式三
                            ddl          DBMS_SQL.VARCHAR2A,        --允许大的DDL语句的输入,dbms_sql.varchar2a为每行32767字节
                            lb            PLS_INTEGER,
                            ub            PLS_INTEGER)
                          RETURN DBMS_SQL.VARCHAR2A;                             

                      ddl:输入参数ddl要求语法为”create or replace…”的字符串，用以创建包、包体、类型、类型体、函数和过程的程序单元的DDL语句 。如果输入参数ddl所定义的程序单元不能被加密，或存在语法错误，则将抛出“MALFORMED_WRAP_INPUT”异常。
                      lb:为加密集合的最低元素
                      ub:为加密集合的最高元素
                      返回值:为加密后的代码。可以将它写入一个文件中，或者存储在表中。

              --使用简单方式实现加密，使用方式一

                      SET SERVEROUTPUT ON SIZE UNLIMITED

                      DECLARE
                          l_source  VARCHAR2(32767);
                          l_wrap      VARCHAR2(32767);
                      BEGIN
                          l_source := 'CREATE OR REPLACE FUNCTION get_date_string RETURN VARCHAR2 AS' ||
                                                  'BEGIN ' ||
                                                  'RETURN TO_CHAR(SYSDATE, ''DD-MON-YYYY''); ' ||
                                                  'END get_date_string;';

                          l_wrap := SYS.DBMS_DDL.WRAP(ddl => l_source);
                          DBMS_OUTPUT.put_line(l_wrap);
                      END;

                      CREATE OR REPLACE FUNCTION get_date_string wrapped
                      a000000
                      1f
                      abcd
                      abcd
                      abcd
                      abcd
                      abcd
                      abcd
                      abcd
                      abcd
                      abcd
                      abcd
                      abcd
                      abcd
                      abcd
                      abcd
                      abcd
                      8
                      6e 96
                      Mm0XeMkyhwPRoFPms2i maxm XAwg0xff8upynSmEPJ4IfeVj Sbm6TkMCRi9trW1AmVTMIZs
                      v4ABZD6CoiUcaSYfjdvzRqCe avAGromyS4qOtqqHxyw/0TtfJ0S2rO1lBTPgb1vb7rX1 6x0m
                      LRwU

                      对于使用DBMS_DDL.WRAP输出的密文，可以将其复制到文本文件或表中，然后将其部署到需要的地方，从一定程度上保证了代码
              的安全性。对于方式一而言，VARCHAR2(32767字节)长度限制了能够使用的PL/SQL代码长度,因此使用WRAP的另外两个重载函数可以解
              决长度缺陷问题。

      4.使用重载过程CREATE_WRAPPED加密子程序  

              Oracle 除了提供重载函数WRAP实现加密之外，同时也提供了重载过程来实现对子程序加密，有关参数描述请参考前面。

                      DBMS_DDL.CREATE_WRAPPED (
                            ddl        VARCHAR2);

                      DBMS_DDL.CREATE_WRAPPED(
                            ddl        DBMS_SQL.VARCHAR2A,
                            lb          PLS_INTEGER,
                            ub          PLS_INTEGER);

                      DBMS_DDL.CREATE_WRAPPED(
                            ddl        DBMS_SQL.VARCHAR2S,
                            lb          PLS_INTEGER,
                            ub          PLS_INTEGER);

              与函数wrap不同，过程create_wrapped不但加密源代码，而且还会在数据库中执行加密后的密文。

              --下面使用CREATE_WRAPPED来加密子程序

                      SET SERVEROUTPUT ON SIZE UNLIMITED
                      DECLARE
                          l_source  DBMS_SQL.VARCHAR2A;
                          l_wrap      DBMS_SQL.VARCHAR2A;
                      BEGIN
                          l_source(1) := 'CREATE OR REPLACE FUNCTION get_date_string RETURN VARCHAR2 AS ';
                          l_source(2) := 'BEGIN ';
                          l_source(3) := 'RETURN TO_CHAR(SYSDATE, ''DD-MON-YYYY''); ';
                          l_source(4) := 'END get_date_string;';

                          SYS.DBMS_DDL.CREATE_WRAPPED(ddl => l_source,
                                                                                  lb  => 1,
                                                                                  ub  => l_source.count);
                      END;                     

                      scott@ORCL> SET PAGESIZE 100

                      scott@ORCL> SELECT text        --查看加密后的密文
                          2  FROM    user_source
                          3  WHERE  name = 'GET_DATE_STRING'
                          4  AND      type = 'FUNCTION';

                      TEXT
                      --------------------------------------------------------------------------------------
                      FUNCTION get_date_string wrapped
                      a000000
                      1f
                      abcd
                      abcd
                      abcd
                      abcd
                      abcd
                      abcd
                      abcd
                      abcd
                      abcd
                      abcd
                      abcd
                      abcd
                      abcd
                      abcd
                      abcd
                      8
                      6f 96
                      i5ktuqFIjCuYLEse2bjmcBG7 ZWgwg0xff8upynSmEPJ4IfeV jSbm6TkMCRi9trW1AmVTMB5D
                      uU2KKgFAWoMdxYFR8VesyCs4 U8zk9ML7b7Q G/lGiyLbcaOIMZ4bNhIBYc2VVs vjaqr86Fu8
                      VByi                     

                      scott@ORCL> select  --使用get_ddl获得加密后的密文
                          2  dbms_metadata.get_ddl('FUNCTION','GET_DATE_STRING')
                          3  from dual;

                      DBMS_METADATA.GET_DDL('FUNCTION','GET_DATE_STRING')
                      --------------------------------------------------------------------------------
                          CREATE OR REPLACE FUNCTION "SCOTT"."GET_DATE_STRING" wrapped
                      a000000
                      1f
                      abcd
                      abcd
                      abcd
                      abcd
                      abcd
                      abcd
                      abcd
                      abcd
                      abcd
                      abcd
                      abcd
                      abcd
                      abcd
                      abcd
                      abcd
                      8
                      6f 96
                      i5ktuqFIjCuYLEse2bjmcBG7 ZWgwg0xff8upynSmEPJ4IfeV jSbm6TkMCRi9trW1AmVTMB5D
                      uU2KKgFAWoMdxYFR8VesyCs4 U8zk9ML7b7Q G/lGiyLbcaOIMZ4bNhIBYc2VVs vjaqr86Fu8
                      VByi             
       

      5.DBMS_DDL.IS_TRIGGER_FIRE_ONCE 用于判断特定的触发器是否被触发过   
     
              DBMS_DDL.IS_TRIGGER_FIRE_ONCE(
                      trig_owner  IN VARCHAR2,
                      trig_name    IN VARCHAR2)
                      RETURN BOOLEAN;

              BEGIN
                  IF dbms_ddl.is_trigger_fire_once('SCOTT', 'tr_tb_a') THEN
                      dbms_output.put_line('TRUE');
                  ELSE
                      dbms_output.put_line('FALSE');
                  END IF;
              END;

              TRUE

转自：http://blog.csdn.net/robinson_0612/article/details/6098248