Oracle Final Exam Notes
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