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
技术交流，技术讨论，欢迎加入
Technology Blog Created By Oracle ERP - 鲍新建
