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

2014-06-14 Created By
BaoXinjian

Oracle 1一、摘要


做global项目时,会须要详细文件管控,对代码同样如此,所以对Oracle
Object所有的对象都要有命名需求和代码规范

以下对Oracle的目标和文书做一个简单易行介绍

在DBA对程式做instance搬迁时,一个一石两鸟的代码管控机制,会缩短过多不需要的难为

境内项目,可能对代码文件的管控不是太复杂,首如果由现实developer举行控制,所以每个developer对应的代码风格,就会影响总体项目

由此在类型起初,对一多级的代码标准,文件命名格局开展支配,对项目成果有很大的提携

专门是当一个档次人士的流动率当先一定幅度时,代码说明和文档记录卓殊首要,不至于一个developer的离开对项目标快慢有更加大的震慑

 

Oracle 2二、文件类型汇总


文件后缀名

类型

 

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

 

 

 

 

 

 

 

 

 

 

 

 

Oracle, 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

a

b

 

Oracle 3三、具体分析


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

Oracle 4

相关文章