Automatic Sql Script Sample

--1. 先用管理者登入
sqlplus / as sysdba << EOF

--2. 如果有需要建立 TABLESPACE 就執行以下SCRIPT 
-- "NEWONE" 可以改成題目要求的.ex. "NEWTWO"   (雙引號要保留喔)
-- /opt/oracle/oradata/orcl/NewOne.dbf 路徑以及檔明也是試題目而變動的
CREATE SMALLFILE TABLESPACE "NEWONE" DATAFILE '/opt/oracle/oradata/orcl/NewOne.dbf' 
SIZE 100M AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED LOGGING 
EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO 

--3. 先DROP SCHEMA 擁有者 (避免已經存在不能新增)
-- elmasri 可以改成題目要求的 SCHEMA 名稱
drop user elmasri cascade;

--4. 新增新的 SCHEMA
-- elmasri 可以改成題目要求的 SCHEMA 名稱
-- users 可以改成題目要求的 TABLESPACE(步驟2)
create user elmasri identified by elmasri
default tablespace users;

--5. 允許 連線跟使用 resource 的權限 給剛剛建立的 SCHEMA
-- elmasri 可以改成題目要求的 SCHEMA 名稱
grant connect, resource to elmasri;

--6. 利用剛剛建立的 SCHEMA 做連線
-- elmasri/elmasri 看成 帳號/密碼
connect elmasri/elmasri;

--7. 依照題目建立 TABLE
DROP TABLE employee CASCADE CONSTRAINTS;
CREATE TABLE employee (
  fname    varchar2(15) not null, -- 欄位名稱 	欄位型別(欄位型別大小)	是否允許 NULL
  minit    varchar2(1),
  lname    varchar2(15) not null,
  ssn      char(9),
  bdate    date,
  address  varchar2(30),
  sex      char,
  salary   number(10,2),
  superssn char(9),
  dno      number(4),
  primary key (ssn),-- 主 KEY
  foreign key (superssn) references employee(ssn)
--  foreign key (dno) references department(dnumber) 此行不能執行因為 department 表格還沒被建立 所以等到建立完表格 DEPARTMENT 再去 直行 ALTER TABLE 動作
);


DROP TABLE department CASCADE CONSTRAINTS;
CREATE TABLE department (
  dname        varchar2(15) not null,
  dnumber      number(4),
  mgrssn       char(9) not null, 
  mgrstartdate date,
  primary key (dnumber), -- 主 KEY
  unique (dname), -- 唯一值
  foreign key (mgrssn) references employee(ssn) --參考外部健 foreign key (表內某欄位) references 外部表格(外部表格的某欄位)
);

alter table employee add (
  foreign key (dno) references department(dnumber)
);
...

--8. 以下是填肉的部份拉
DELETE FROM employee;
INSERT INTO employee VALUES ('James', 'E', 'Borg',
        '888665555', '10-NOV-27', 'Houston,TX', 'M', 55000, null, null);
...

DELETE FROM department;
INSERT INTO department VALUES ('Research', 5, '333445555', '22-MAY-78');
...

UPDATE employee SET DNO = 5 WHERE ssn = '333445555';
...

INSERT INTO employee VALUES ('John', 'B', 'Smith',
        '123456789', '09-Jan-55', 'Houston,TX', 'M', 30000, '333445555', 5);
...
INSERT INTO employee VALUES ('Ahmad', 'V', 'Jabbar',
        '987987987', '29-MAR-59', 'Houston,TX', 'M', 25000, '987654321', 4);

DELETE FROM project;
INSERT INTO project VALUES ('ProductX', 1, 'Bellaire',  5);
...

DELETE FROM dept_locations;
INSERT INTO dept_locations VALUES (1, 'Houston');
...

DELETE from dependent;
INSERT INTO dependent VALUES ('333445555','Alice','F','05-APR-76','Daughter');
...

DELETE FROM works_on;
INSERT INTO works_on VALUES ('123456789', 1,  32.5);
...

exit;
EOF

Hopes this all will help you guys!!

[References]

elmasri.sh Download