1.创建SCOTT/TIGER模式:
SQL> @C:/Oracle/Ora92/SQLPlus/Demo/DemoBld.SQL;
DemoBld.SQL会创建5个表并填入数据。执行结束后,它会自动退出SQL*Plus,所以运行完这个脚本后SQL*Plus窗口将消失,对此不要感到奇怪,这是正常的。
注意,此时自动创建的5个标准演示表上没有定义任何引用完整性,建议在运行完DemoBld.SQL后,再执行以下语句添加引用完整性:
SQL> ALTER TABLE Emp ADD CONSTRAINT Emp_PK PRIMARY KEY(EmpNo);
表已更改。
SQL> ALTER TABLE Dept ADD CONSTRAINT Dept_PK PRIMARY KEY(DeptNo);
表已更改。
SQL> ALTER TABLE Emp ADD CONSTRAINT Emp_FK_Dept FOREIGN KEY(DeptNo) REFERENCES Dept;
表已更改。
SQL> ALTER TABLE Emp ADD CONSTRAINT Emp_FK_Emp FOREIGN KEY(Mgr) REFERENCES Emp;
表已更改。
或者把这些语句放到一个脚本中,每次创建SCOTT演示表时,执行一下。
如果无法访问DemoBld.SQL,执行以下脚本即可:
CREATE TABLE Emp
(
EmpNo NUMBER(4) NOT NULL,
EName VARCHAR2(10),
Job VARCHAR2(9),
Mgr NUMBER(4),
HireDate DATE,
Sal NUMBER(7, 2),
Comm NUMBER(7, 2),
DeptNo NUMBER(2)
);
INSERT INTO Emp
VALUES (7369,
'SMITH',
'CLERK',
7902,
TO_DATE('17-DEC-1980', 'DD-MON-YYYY'),
800,
NULL,
20);
INSERT INTO Emp
VALUES (7499,
'ALLEN',
'SALESMAN',
7698,
TO_DATE('20-FEB-1981', 'DD-MON-YYYY'),
1600,
300,
30);
INSERT INTO Emp
VALUES (7521,
'WARD',
'SALESMAN',
7698,
TO_DATE('22-FEB-1981', 'DD-MON-YYYY'),
1250,
500,
30);
INSERT INTO Emp
VALUES (7566,
'JONES',
'MANAGER',
7839,
TO_DATE('2-APR-1981', 'DD-MON-YYYY'),
2975,
NULL,
20);
INSERT INTO Emp
VALUES (7654,
'MARTIN',
'SALESMAN',
7698,
TO_DATE('28-SEP-1981', 'DD-MON-YYYY'),
1250,
1400,
30);
INSERT INTO Emp
VALUES (7698,
'BLAKE',
'MANAGER',
7839,
TO_DATE('1-MAY-1981', 'DD-MON-YYYY'),
2850,
NULL,
30);
INSERT INTO Emp
VALUES (7782,
'CLARK',
'MANAGER',
7839,
TO_DATE('9-JUN-1981', 'DD-MON-YYYY'),
2450,
NULL,
10);
INSERT INTO Emp
VALUES (7788,
'SCOTT',
'ANALYST',
7566,
TO_DATE('09-DEC-1982', 'DD-MON-YYYY'),
3000,
NULL,
20);
INSERT INTO Emp
VALUES (7839,
'KING',
'PRESIDENT',
NULL,
TO_DATE('17-NOV-1981', 'DD-MON-YYYY'),
5000,
NULL,
10);
INSERT INTO Emp
VALUES (7844,
'TURNER',
'SALESMAN',
7698,
TO_DATE('8-SEP-1981', 'DD-MON-YYYY'),
1500,
0,
30);
INSERT INTO Emp
VALUES (7876,
'ADAMS',
'CLERK',
7788,
TO_DATE('12-JAN-1983', 'DD-MON-YYYY'),
1100,
NULL,
20);
INSERT INTO Emp
VALUES (7900,
'JAMES',
'CLERK',
7698,
TO_DATE('3-DEC-1981', 'DD-MON-YYYY'),
950,
NULL,
30);
INSERT INTO Emp
VALUES (7902,
'FORD',
'ANALYST',
7566,
TO_DATE('3-DEC-1981', 'DD-MON-YYYY'),
3000,
NULL,
20);
INSERT INTO Emp
VALUES (7934,
'MILLER',
'CLERK',
7782,
TO_DATE('23-JAN-1982', 'DD-MON-YYYY'),
1300,
NULL,
10);
CREATE TABLE Dept
(
DeptNo NUMBER(2),
DName VARCHAR2(14),
Loc VARCHAR2(13)
);
INSERT INTO Dept
VALUES (10,
'ACCOUNTING',
'NEW YORK');
INSERT INTO Dept
VALUES (20,
'RESEARCH',
'DALLAS');
INSERT INTO Dept
VALUES (30,
'SALES',
'CHICAGO');
INSERT INTO Dept
VALUES (40,
'OPERATIONS',
'BOSTON');
ALTER TABLE Emp ADD CONSTRAINT Emp_PK PRIMARY KEY(EmpNo);
ALTER TABLE Dept ADD CONSTRAINT Dept_PK PRIMARY KEY(DeptNo);
ALTER TABLE Emp ADD CONSTRAINT Emp_FK_Dept FOREIGN KEY(DeptNo) REFERENCES Dept;
ALTER TABLE Emp ADD CONSTRAINT Emp_FK_Emp FOREIGN KEY(Mgr) REFERENCES Emp;
2.删除SCOTT/TIGER模式:
SQL> @C:/Oracle/Ora92/SQLPlus/Demo/DemoDrop.SQL;
删除时也会自动退出SQL*Plus。
软件环境:Windows XP SP3+Oracle 9i R2,[ORACLE_HOME]=C:/Oracle/Ora92