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 [<schema>.] < name > 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 )

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 ;

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 [<schema>.] < name > [<method>] STATISTICS [SAMPLE <n> [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

abcd

6e 96

Mm0XeMkyhwPRoFPms2i + maxm + XAwg0xff8upynSmEPJ4IfeVjSbm6TkMCRi9trW1AmVTMIZs

v4ABZD6CoiUcaSYfjdvzRqCeavAGromyS4qOtqqHxyw / 0TtfJ0S2rO1lBTPgb1vb7rX16x0m

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

abcd

6f 96

i5ktuqFIjCuYLEse2bjmcBG7ZWgwg0xff8upynSmEPJ4IfeVjSbm6TkMCRi9trW1AmVTMB5D

uU2KKgFAWoMdxYFR8VesyCs4U8zk9ML7b7Q + G / lGiyLbcaOIMZ4bNhIBYc2VVsvjaqr86Fu8

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

abcd

6f 96

i5ktuqFIjCuYLEse2bjmcBG7ZWgwg0xff8upynSmEPJ4IfeVjSbm6TkMCRi9trW1AmVTMB5D

uU2KKgFAWoMdxYFR8VesyCs4U8zk9ML7b7Q + G / lGiyLbcaOIMZ4bNhIBYc2VVsvjaqr86Fu8

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

二、更多参考

有关 SQL 请参考

SQL 基础--> ROLLUP 与CUBE 运算符实现数据汇总

SQL 基础--> 层次化查询(START BY ... CONNECT BY PRIOR)

有关 PL/SQL 请参考

PL/SQL --> 隐式游标(SQL%FOUND)

PL/SQL --> 异常处理(Exception)

PL/SQL --> PL/SQL 记录

PL/SQL --> 包的创建与管理

PL/SQL --> 包重载、初始化

PL/SQL --> DBMS_DDL 包的使用

PL/SQL --> DML 触发器

PL/SQL --> INSTEAD OF 触发器

PL/SQL --> DBMS_DDL包的使用

更多文章、技术交流、商务合作、联系博主

微信扫码或搜索：z360901061

微信扫一扫加我为好友

QQ号联系： 360901061

您的支持是博主写作最大的动力，如果您喜欢我的文章，感觉我的文章对您有帮助，请用微信扫描下面二维码支持博主2元、5元、10元、20元等您想捐的金额吧，狠狠点击下面给点支持吧，站长非常感激您！手机微信长按不能支付解决办法：请将微信支付二维码保存到相册，切换到微信，然后点击微信右上角扫一扫功能，选择支付二维码完成支付。

【本文对您有帮助就好】元

2元

5元

10元

20元

自定义