--=============================
-- 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 基礎--> ROLLUP 與CUBE 運算符實現數據匯總
SQL 基礎--> 層次化查詢(START BY ... CONNECT BY PRIOR)
?
??? 有關 PL/SQL 請參考
??????? PL/SQL --> 語言基礎
???????
?
更多文章、技術交流、商務合作、聯系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號聯系： 360901061
您的支持是博主寫作最大的動力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點擊下面給點支持吧，站長非常感激您！手機微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對您有幫助就好】元
