在Oracle数据库创建脚本中如何初始化某些表

最新推荐文章于 2023-02-05 12:16:15 发布

lisa06

最新推荐文章于 2023-02-05 12:16:15 发布

阅读量4.1k

点赞数

分类专栏：数据库文章标签：脚本数据库 oracle table encoding insert

本文链接：https://blog.csdn.net/lisa06/article/details/5269080

版权

数据库专栏收录该内容

3 篇文章 0 订阅

订阅专栏

Sample

表空间：IMPTEMP

表：Roles 、Users

通过PL/SQL导出的数据库脚本

-----------------------------------------------------
-- Export file for user IMPTEMP                    --
-- Created by Administrator on 2010-1-29, 14:14:25 --
-----------------------------------------------------

spool sample.log

prompt
prompt Creating table ROLES
prompt ====================
prompt
create table IMPTEMP.ROLES
(
ROLENAME NVARCHAR2(50) not null,
ROLEXML NCLOB
)
tablespace IMPTEMP
pctfree 10
initrans 1
maxtrans 255
storage
(
    initial 64K
    minextents 1
    maxextents unlimited
);
alter table IMPTEMP.ROLES
add constraint ROLES_PRIMARY_KEY primary key (ROLENAME)
using index
tablespace IMPTEMP
pctfree 10
initrans 2
maxtrans 255
storage
(
    initial 64K
    minextents 1
    maxextents unlimited
);

prompt
prompt Creating table USERS
prompt ====================
prompt
create table IMPTEMP.USERS
(
USERID   NVARCHAR2(50) not null,
USERNAME NVARCHAR2(50),
ROLENAME NVARCHAR2(50),
PASSWORD NVARCHAR2(50),
USERXML NCLOB,
USERDN   NVARCHAR2(100),
ISLOCAL NUMBER(1)
)
tablespace IMPTEMP
pctfree 10
initrans 1
maxtrans 255
storage
(
    initial 64K
    minextents 1
    maxextents unlimited
);
comment on column IMPTEMP.USERS.ISLOCAL
is '0-ISNOTLOCAL : 1-ISLOCAL';
alter table IMPTEMP.USERS
add constraint USERS_PRIMARY_KEY primary key (USERID)
using index
tablespace IMPTEMP
pctfree 10
initrans 2
maxtrans 255
storage
(
    initial 64K
    minextents 1
    maxextents unlimited
);

spool off

现在要为Users表初始化一条userid为“admin”的人员，为Roles表初始化一条rolename为‘超级管理员’的角色，修改脚本如下：

insert into IMPTEMP.ROLES t values('超级管理员','<?xml version="1.0" encoding="GBK"?><RoleInfo><RoleName>超级管理员</RoleName><RightList><Right>显示任务</Right><Right>查看配置</Right><Right>查看资源</Right><Right>节目流程管理</Right><Right>任务操作</Right><Right>Interplay资源发送</Right><Right>Interplay资源删除</Right><Right>创建ISIS Workspace</Right><Right>删除ISIS Workspace</Right><Right>基本配置</Right><Right>用户与角色</Right><Right>模板定义</Right><Right>设备工位</Right><Right>系统服务</Right><Right>多点登录</Right><Right>送审送播</Right></RightList></RoleInfo>');

prompt
prompt Creating table USERS
prompt ====================
prompt
create table IMPTEMP.USERS
(
USERID   NVARCHAR2(50) not null,
USERNAME NVARCHAR2(50),
ROLENAME NVARCHAR2(50),
PASSWORD NVARCHAR2(50),
USERXML NCLOB,
USERDN   NVARCHAR2(100),
ISLOCAL NUMBER(1)
)
tablespace IMPTEMP
pctfree 10
initrans 1
maxtrans 255
storage
(
    initial 64K
    minextents 1
    maxextents unlimited
);
comment on column IMPTEMP.USERS.ISLOCAL
is '0-ISNOTLOCAL : 1-ISLOCAL';
alter table IMPTEMP.USERS
add constraint USERS_PRIMARY_KEY primary key (USERID)
using index
tablespace IMPTEMP
pctfree 10
initrans 2
maxtrans 255
storage
(
    initial 64K
    minextents 1
    maxextents unlimited
);

insert into IMPTEMP.USERS t values('admin','admin','超级管理员','admin','<?xml version="1.0" encoding="GBK"?><UserInfo><UserID>admin</UserID><ExtraInfo></ExtraInfo></UserInfo>','','0');
spool off