批处理执行数据库初始化脚本

65 阅读 0 评论 43 点赞

我是靠谱客的博主友好咖啡豆，最近开发中收集的这篇文章主要介绍批处理执行数据库初始化脚本，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

批处理命令

@echo *************************************************************************
@echo  在oracle 中建立数据库
@echo *************************************************************************

@echo off
set /p p_sys_pwd="请输入sys的密码:"
set /p p_sid="请输入数据库连接串(TNS名)(如orcl):"
set /p p_schema="请输入新的用户名称(如FMServer):"
set /p p_schema_PWD="请输入新用户密码:"

sqlplus sys/%p_sys_pwd%@%p_sid% as sysdba @"CreateUser.sql" '%p_schema%' '%p_schema_PWD%'
@echo  创建用户%p_schema% 完成。请继续建立系统表
pause;

sqlplus %p_schema%/%p_schema_PWD%@%p_sid%  @"CreateTable.sql"
@echo  建立系统表完成。请继续初始化系统
pause;

sqlplus %p_schema%/%p_schema_PWD%@%p_sid%  @"InitData.sql"
@echo  系统初始化系完成。
pause;

SQL语句：

创建用户

set echo off
set feedback off
set newpage none
set pagesize 5000
set linesize 5000
set verify off
set heading off
set termout off
set trimspool  on
set long 100000
define dbUser='&1'
define dbPwd='&2'

CREATE USER &dbUser IDENTIFIED BY "&dbPwd" DEFAULT TABLESPACE "USERS" QUOTA UNLIMITED ON "USERS";

GRANT ALTER ANY INDEX TO &dbUser;

GRANT ALTER ANY PROCEDURE TO &dbUser;

GRANT ALTER ANY SEQUENCE TO &dbUser;

GRANT ALTER ANY TABLE TO &dbUser;

GRANT ALTER ANY TRIGGER TO &dbUser;

GRANT COMMENT ANY TABLE TO &dbUser;

GRANT CREATE ANY CONTEXT TO &dbUser;

GRANT CREATE ANY INDEX TO &dbUser;

GRANT CREATE ANY JOB TO &dbUser;

GRANT CREATE ANY SEQUENCE TO &dbUser;

GRANT CREATE ANY TABLE TO &dbUser;

GRANT CREATE ANY TRIGGER TO &dbUser;

GRANT CREATE ANY TYPE TO &dbUser;

GRANT CREATE ANY VIEW TO &dbUser;

GRANT CREATE SEQUENCE TO &dbUser;

GRANT CREATE SESSION TO &dbUser;

GRANT CREATE TABLE TO &dbUser;

GRANT CREATE TRIGGER TO &dbUser;

GRANT CREATE VIEW TO &dbUser;

GRANT DELETE ANY TABLE TO &dbUser;

GRANT DROP ANY INDEX TO &dbUser;

GRANT DROP ANY PROCEDURE TO &dbUser;

GRANT DROP ANY TABLE TO &dbUser;

GRANT DROP ANY TRIGGER TO &dbUser;

GRANT EXECUTE ANY CLASS TO &dbUser;

GRANT INSERT ANY TABLE TO &dbUser;

GRANT LOCK ANY TABLE TO &dbUser;

GRANT ON COMMIT REFRESH TO &dbUser;

GRANT RESTRICTED SESSION TO &dbUser;

GRANT SELECT ANY DICTIONARY TO &dbUser;

GRANT SELECT ANY SEQUENCE TO &dbUser;

GRANT SELECT ANY TABLE TO &dbUser;

GRANT SELECT ANY TRANSACTION TO &dbUser;

GRANT UPDATE ANY TABLE TO &dbUser;

exit

创建表

CREATE SEQUENCE seq_BoxAuId
MINVALUE 1
MAXVALUE 999999999999999999
START WITH 1
INCREMENT BY 1
NOCACHE
;

CREATE TABLE T_BoxAuthor
(
    BoxAuId INTEGER NOT NULL CONSTRAINT PK_T_BOXAUTHOR PRIMARY KEY,
	BoxSN   nvarchar2(20) NOT NULL,
    UserASN nvarchar2(20) NULL,
    UserBSN nvarchar2(20) NULL,
    AStatus INTEGER          NULL
)
;


CREATE OR REPLACE TRIGGER tri_BoxAuId
BEFORE INSERT ON T_BoxAuthor
for each row when(new.BoxAuId is null)
begin  
select seq_BoxAuId.nextval into:new.BoxAuId from sys.dual;
end;
/

exit

初始化数据：

INSERT INTO T_BoxState ( BoxStateID, BoxStateName ) VALUES (0,'xxx');
INSERT INTO T_BoxType ( BoxTypeID, BoxTypeName ) VALUES ( 2, 'rr' );


commit;
exit