A Simple PLSQL Source Control Repository in Oracle

最新推荐文章于 2022-03-28 20:50:02 发布

最新推荐文章于 2022-03-28 20:50:02 发布 · 164 阅读

文章标签：

#Oracle #CVS #SQL #Cache

本文介绍如何在Oracle数据库中创建一个简单的PL/SQL源代码版本控制系统。通过建立表格、序列及触发器来记录每次过程创建时的源代码、版本号和其他元数据。

Create A Simple PL/SQL Source Control Repository in Oracle Using Trigger

1 Create a table as repository of pl/sql source code

create table CVS
(
  P_VER   NUMBER,
  P_TEXT  LONG,
  P_NAME  VARCHAR2(100),
  P_OWNER VARCHAR2(30),
  P_TYPE  VARCHAR2(30),
  P_DATE  DATE,
  P_USER  VARCHAR2(30)
)

2 Create a sequence as version number generator

create sequence VER_SEQ
minvalue 1
maxvalue 999999999999999999999999999
start with 1
increment by 1
cache 20;

3 Create a trigger to store procedure code in CVS table after created

create or replace trigger CVS_PROCEDURE
  after create on schema 
declare
  P_TEXT VARCHAR2(10000);
begin
  
  IF(ora_dict_obj_type = 'PROCEDURE') THEN
  
    P_TEXT := 'CREATE OR REPLACE ';
    FOR RA IN (SELECT TEXT FROM USER_SOURCE 
                WHERE NAME=ora_dict_obj_name ORDER BY LINE)
    LOOP
        P_TEXT := P_TEXT || RA.TEXT;
    END LOOP;
    
    INSERT INTO CVS(P_OWNER,P_NAME,P_TYPE,P_TEXT,
                    P_VER,P_USER,P_DATE)
    SELECT ora_dict_obj_owner,
           ora_dict_obj_name, 
           ora_dict_obj_type,
           P_TEXT,
           VER_SEQ.NEXTVAL, USER, SYSDATE
    FROM dual;    
  
  END IF;       
end CVS_PROCEDURE;

done ^_^

Every compilation of a procedure will result in a new record in CVS table.