PLSQL_Oracle Object所生数据库对象类型汇总和简解(概念)

2014-06-14 Created By
BaoXinjian

一、摘要


开global项目时,会要求详细文件管控,对代码同样如此,所以针对Oracle
Object所有的目标还设产生命名要求和代码规范

以下对Oracle的目标与文书举行一个简短介绍

每当DBA对程式举行instance搬迁时,一个可观的代码管控机制,会压缩过多未必要之麻烦

境内项目,可能针对代码文件之管控不是绝复杂,主要是由于具体developer进行控制,所以每个developer对应的代码风格,就见面潜移默化所有项目

故在类型开始,对相同多重之代码标准,文件命名方式进行控制,对品种成果有死酷之助

特别是当一个品类人员的流动率超过一定幅度时,代码说明与文档记录非常主要,不至于一个developer的相距对项目之快有不行酷的震慑

 

仲、文件类型汇总


文件后缀名

类型

 

agr

 Grants

 

cgr

 Grants

 

con

 Constraints

 

dbl

 Database Links

 

dml

 Seed Data

 

fun

 Functions

 

ind

 Index

 

plb

 Package Body

 

pls

 Package Specific

 

prc

 Procedure

 

seq

 Sequence

 

sgr

 Grants 

 

sna

 Snapshots

 

sql

 SQL Scripts

 

syn

 Synonyms

 

tab

 Table

 

trg

 Trigger

 

vew

 View

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

a

b

 

其三、具体分析


1.agr

–>Grants

GRANT EXECUTE ON
fnd_request TO xxgl;

 

2. cgr

–>Grants

GRANT SELECT,INSERT,DELETE ON xxgl.xxgl_test_journal TO apps

 

3. sgr

–>Grants

GRANT EXECUTE ON
xxgl.xxgl_test_journal_nm_seq
TO apps;

 

4. con

–>Constraints

ALTER TABLE xxgl.xxgl_test_journal ADD CONSTRAINT xxgl_con1 UNIQUE(je_id);

 

5. dbl

–>Database Links

CREATE DATABASE LINK xxgl_test_dblinks

CONNECT TO erpkadex IDENTIFIED BY kl863

USING ‘(DESCRIPTION =

                (ADDRESS_LIST =

                    (ADDRESS = (PROTOCOL = TCP)(HOST =
10.142.202.12)(PORT = 1521))

                 )

                 (CONNECT_DATA =

                     (SERVICE_NAME = ERPKADEX)

                  )

             )’;

 

6. dml

–>Seed Data

INSERT INTO xxgl.xxgl_test_journal VALUES (‘52002’,
‘CMC-4902’);

/

 

7. fun

–>Functions

CREATE OR REPLACE
FUNCTION xxgl.xxgl_test_journal_fun (

    refbuff OUT VARCHAR2,

    retcode OUT VARCHAR2

)

    RETURN BOOLEAN

IS

BEGIN

    RETURN TRUE;

END;

 

8. ind

–>Indexs

CREATE OR REPLACE
UNIQUE INDEX xxgl.xxgl_test_journal_u1

    ON xxgl.xxgl_test_journal(wip_entity_id) LOGGING
TABLESPACE xxgl_indx

 

9. plb

–>package body

CREATE OR REPLACE
PACKAGE BODY xxgl_test_journal_pkg IS

PROCEDURE test_procedure(p_retcode OUT NUMBER,
p_errbuf OUT VARCHAR2)
IS

 BEGIN

    NULL;

END test_procedure;

FUNCTION test_function(p_paremater_in IN NUMBER,

                                       p_paremater_out
OUT NUMBER)
RETURN BOOLEAN IS

BEGIN

    RETURN TRUE;

END test_function;

END xxgl_test_journal_pkg;

 

10. pls

–>package specific

CREATE OR REPLACE
PACKAGE xxgl_test_journal_pkg IS

    PROCEDURE
test_procedure(p_retcode OUT NUMBER,
p_errbuf OUT VARCHAR2);

    FUNCTION test_function(p_paremater_in IN NUMBER,

                                         
 p_paremater_out OUT NUMBER)
RETURN BOOLEAN;

END xxgl_test_journal_pkg;

 

11. prc

–>Procedures

CREATE OR REPLACE
PROCEDURE xxgl.xxgl_test_journal_prc (

    retbuf OUT VARCHAR2,

    retcode OUT VARCHAR

)

IS

BEGIN

    NULL;

END xxgl_test_journal_prc;

 

12. seq

–>Sequences

CREATE SEQUENCE xxgl.xxgl_test_journal_nm_seq

INCREMENT BY 1

START WITH 1

MAXVALUE 9999

NOCACHE

CYCLE

/

 

13. sna

–>Snapshots

CREATE MATERIALIZED VIEW xxgl.xxgl_test_journal_mv

    REFRESH COMPLETE ON DEMAND

AS

    SELECT wip_entity_id, wip_entity_name FROM wip_entities

     WHERE wip_entity_id=52002

/

 

14. sql

–>SQL Scripts

SPOOL xxwip4409.lst

    CONNECT xxwip

    PROMPT Grants to custom objects:

    START xxwip4409.cgr

    PROMPT Executing DML:

    START xxwip4409.dml

SPOOL off

QUIT

 

15. syn

–>Synonyms

CREATE OR REPLACE
PUBLIC SYNONYM xxgl_test_journal_pkg FOR xxgl.xxgl_test_journal_pkg;

 

16. tab

–>Table

CREATE GLOBAL TEMPORARY TABLE xxgl.xxgl_test_journal(

    je_id NUMBER,

    je_name VARCHAR(100
BYTE)

)ON COMMIT DELETE ROWS;

 

17. trg

–>Trigger

分成前置和后置: before/after

由insert,update,delete触发: insert/update/delete

CREATE OR REPLACE
TRIGGER xxgl.xxgl_test_journal_trg

    BEFORE INSERT ON
xxgl.xxgl_test_journal

        FOR EACH ROW

DECLARE

    p_parameter_test VARCHAR(10);

BEGIN

    DBMS_OUTPUT.put_line(:new.je_name);

END;

 

18. vew

–>View

CREATE OR REPLACE
VIEW xxgl.xxgl_test_journal_v

    (wip_entity_id,

    wip_entity_name)

AS

    SELECT wip_entity_id, wip_entity_name

       FROM wip_entities

     WHERE wip_entity_id = 52002

        WITH READ ONLY

/

 

Thanks and Regards

相关文章