Oracle 模式下查看 PL 定义代码的方法

obclient [SYS]> select dbms_metadata.get_ddl('PROCEDURE','INSERT_T1','SYS') from dual;

+-----------------------------------------------------------------------------------------------------------------------------------------------+
| DBMS_METADATA.GET_DDL('PROCEDURE','INSERT_T1','SYS')                                                                                          |
+-----------------------------------------------------------------------------------------------------------------------------------------------+
| CREATE OR REPLACE PROCEDURE "SYS"."INSERT_T1"
IS
BEGIN
INSERT INTO t1 VALUES (ceil(dbms_random.value1000),ceil(dbms_random.value100));
END |
+-----------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.442 sec)

obclient [SYS]> select dbms_metadata.get_ddl('FUNCTION','GET_T1_COUNT','SYS') from dual;

+--------------------------------------------------------------------------------------------------------------------------------------------+
| DBMS_METADATA.GET_DDL('FUNCTION','GET_T1_COUNT','SYS')                                                                                     |
+--------------------------------------------------------------------------------------------------------------------------------------------+
| CREATE OR REPLACE FUNCTION "SYS"."GET_T1_COUNT"
RETURN int IS
v_cnt int;
begin
select count(*) into v_cnt from t1 ;
return v_cnt;
end |
+--------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.109 sec)

obclient [SYS]> select dbms_metadata.get_ddl('TRIGGER','TR_ADD_T1','SYS') from dual;

+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| DBMS_METADATA.GET_DDL('TRIGGER','TR_ADD_T1','SYS')                                                                                                                       |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| CREATE OR REPLACE TRIGGER "SYS"."TR_ADD_T1" AFTER INSERT ON t1 FOR EACH ROW
BEGIN
INSERT INTO t2 VALUES (:new.id,:new.r1);
END;

ALTER TRIGGER "SYS"."TR_ADD_T1" ENABLE |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.019 sec)

obclient [SYS]> select dbms_metadata.get_ddl('PACKAGE','PACKAGE_SAMPLE','SYS') from dual; 

+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | DBMS_METADATA.GET_DDL('PACKAGE','PACKAGE_SAMPLE','SYS') | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | create or replace PACKAGE package_sample as function get_t1_count2(v_id int :=1) RETURN int; END package_sample\n create or replace PACKAGE BODY package_sample as
function get_t1_count2(v_id int :=1)
return int
as
v_cnt int :=0;
BEGIN
select count(*) into v_cnt from t1 where id = v_id;
return v_cnt;
END  get_t1_count2;
END package_sample |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.312 sec)