oracle的应用

来源：互联网发布：linux ftp配置文件丢失编辑：程序博客网时间：2024/04/30 13:59

_{--Create table

create table REPORT_INFORMATION (
   report_id number(10,0) not null,
   report_title varchar2(500) not null,
   report_description varchar2(500)
);

-- Create/Recreate primary, unique and foreign key constraints

alter table REPORT_INFORMATION
add primary key (report_id);

-- Add comments to the columns

comment on column REPORT_INFORMATION.report_id
is '年报的标识ID';
comment on column REPORT_INFORMATION.report_title
is '年报的标题';
comment on column REPORT_INFORMATION.report_description
is '年报的简要描述';

--Create table

create table REPORT_CATALOG_INFORMATION (
   catalog_id number(19,0) not null,
   report_id number(10,0) not null,
   catalog_name varchar2(500) not null,
   page_from number(10,0) not null,
   page_to number(10,0) not null
);

-- Create/Recreate primary, unique and foreign key constraints

alter table REPORT_CATALOG_INFORMATION
   add primary key (catalog_id);

alter table REPORT_CATALOG_INFORMATION
   add constraint FK_REPORT_ID foreign key (report_id) references REPORT_INFORMATION(report_id);

-- Add comments to the columns

comment on column REPORT_CATALOG_INFORMATION.catalog_id
is '年报目录ID';

comment on column REPORT_CATALOG_INFORMATION.report_id
is '年报标识ID';

comment on column REPORT_CATALOG_INFORMATION.catalog_name
is '年报目录名称';

comment on column REPORT_CATALOG_INFORMATION.page_from
is '年报目录的起始页';

comment on column REPORT_CATALOG_INFORMATION.page_to
is '年报目录的结束页';

-- Create table

create table REPORT_PAGE_INFORMATION (
   page_number number(10,0) not null,
   catalog_id number(19,0) not null,
   page_content clob
);

-- Create/Recreate primary, unique and foreign key constraints

alter table REPORT_PAGE_INFORMATION
   add primary key (page_number,catalog_id);

alter table REPORT_PAGE_INFORMATION
   add constraint FK_CATALOG_ID foreign key (catalog_id) references REPORT_CATALOG_INFORMATION(catalog_id);

--Add comments to the columns

comment on column REPORT_PAGE_INFORMATION.page_number
is '年报的页码';

comment on column REPORT_PAGE_INFORMATION.catalog_id
is '年报的目录ID';

comment on column REPORT_PAGE_INFORMATION.page_content
is '年报的页内容';

--Create sequence

create sequence REPORT_CATALOG_INFO_SEQ
minvalue 1
maxvalue 100000
start with 1
increment by 1
order;

--------------------------------------------------------------------------------------------------------------------------------------------------------

创建同名

--drop public synonym REPORT_INFORMATION;

create public synonym REPORT_INFORMATION for irrsdata.REPORT_INFORMATION;

create public synonym REPORT_PAGE_INFORMATION for irrsdata.REPORT_PAGE_INFORMATION;

create public synonym REPORT_CATALOG_INFORMATION for irrsdata.REPORT_CATALOG_INFORMATION;

create public synonym REPORT_CATALOG_INFO_SEQ for irrsdata.REPORT_CATALOG_INFO_SEQ;

－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－

授权访问

-- Grant/Revoke object privileges
-- 授权用户irrscde，授权角色r_irrsdata_dml和r_irrsdata_qry

grant select,delete,update,insert on REPORT_INFORMATION to irrscde;
grant select,delete,update,insert on REPORT_INFORMATION to r_irrsdata_dml;
grant select on REPORT_INFORMATION to r_irrsdata_qry;

-----------------------------------------------------------
grant select,delete,update,insert on REPORT_PAGE_INFORMATION to irrscde;
grant select,delete,update,insert on REPORT_PAGE_INFORMATION to r_irrsdata_dml;
grant select on REPORT_PAGE_INFORMATION to r_irrsdata_qry;

-------------------------------------------

grant select, insert, update, delete on REPORT_CATALOG_INFORMATION to irrscde;
grant select, insert, update, delete on REPORT_CATALOG_INFORMATION to r_irrsdata_dml;
grant select on REPORT_CATALOG_INFORMATION to r_irrsdata_qry;

-------------------------------------------

grant select on REPORT_CATALOG_INFO_SEQ to irrscde;
grant select on REPORT_CATALOG_INFO_SEQ to r_irrsdata_qry,r_irrsdata_dml;}