本文介绍 Oracle 模式下如何获取存储过程、函数、触发器、包、包体的定义代码。
适用版本
OceanBase 数据库 V2.x 和 V3.x 版本。
获取存储过程的定义代码
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)
