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)

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

1f

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

8

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

1f

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

8

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

1f

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

abcd

8

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 基础--> 子查询

SQL 基础-->多表查询

SQL基础-->分组与分组函数

SQL 基础-->常用函数

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

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

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏杨建荣的学习笔记

Oracle分区数据问题的分析和修复

今天根据同事的反馈,处理了一个分区表的问题,也让我对Oracle的分区表功能有了进一步的理解。 首先根据开发同事的反馈,他们在程序批量插入一部分数据的时候...

31640
来自专栏乐沙弥的世界

dbms_lock.relase 无法释放自定义的锁?

      最近开发人员说使用dbms_lock.allocate_unique自定义的锁在使用dbms_lock.relase无法释放,下面来个演示的例子来看...

23820
来自专栏北京马哥教育

SQL优化之踩过的坑

正看资料看的过瘾,突然收到报警,说服务器负载太高,好吧,登录服务器看看,我擦嘞,还能不能愉快的玩耍了?下面是当时的负载情况 ? 看见mysql使用cpu已经到了...

43360
来自专栏杨建荣的学习笔记

关于reset sequence(r3笔记第85天)

sequence在工作中使用比较频繁,对于Insert中插入的值,如果只需要它能够自动递增,这个时候sequence就派上用场了。 但是既然sequence的值...

31980
来自专栏数据和云

循序渐进:Oracle 12.2的Sharding基础概念解读

张大朋(Lunar)Oracle 工程师 Lunar 拥有超过十年的 ORACLE SUPPORT 从业经验,曾经服务于ORACLE ACS部门,现就职于 O...

32240
来自专栏Jerry的SAP技术分享

使用ABAP代码返回S/4HANA Material上维护的Attachment明细

17230
来自专栏乐沙弥的世界

Oracle大表清理truncate .. reuse storage

a、通过上述测试,当使用reuse storage与普通方式并无明显差异 b、truncate table 是ddl操作,无法回滚 c、尽管无明显性能差...

15440
来自专栏calvin

mssql 优化之索引部分

具有较高的 index_advantage 的索引那些 SQL 服务器认为会产生最大的积极影响,减少工作量,基于查询的成本和预期他们会使用索引的次数减少。

28820
来自专栏乐沙弥的世界

PL/SQL --> DML 触发器

何谓触发器?简言之,是一段命名的PL/SQL代码块,只不过该代码块在特定的条件下被触发并且执行。对于这样的代码我们称之为触发器

11230
来自专栏Jerry的SAP技术分享

使用BAPISDORDER_GETDETAILEDLIST创建S/4HANA的Outbound Delivery

要在S/4HANA里创建Outbound Delivery,首先要具有一个销售订单,ID为376,通过事务码VA03查看。

12610

扫码关注云+社区

领取腾讯云代金券