位置：文档库 > 数据库 > Oracle数据库增加样例数据 scott用户与相关的表

Oracle数据库增加样例数据 scott用户与相关的表

CSS_Gridlock 上传于 2020-04-02 18:24

《Oracle数据库增加样例数据：scott用户与相关的表》

在Oracle数据库的学习与实践中，样例数据是验证SQL语句、测试功能以及理解表关系的重要工具。scott用户作为Oracle自带的经典示例用户，其关联的部门表（DEPT）、员工表（EMP）、工资历史表（SALGRADE）和奖金表（BONUS）构成了典型的数据库教学场景。本文将详细介绍如何为scott用户下的表结构添加样例数据，涵盖数据插入方法、数据一致性维护以及常见问题解决方案。

一、scott用户及其表结构概述

scott用户是Oracle数据库预装的示例账户，默认密码为tiger。其包含的四个核心表结构如下：

1. DEPT（部门表）

CREATE TABLE DEPT (
  DEPTNO NUMBER(2) PRIMARY KEY,
  DNAME VARCHAR2(14),
  LOC VARCHAR2(13)
);

2. EMP（员工表）

CREATE TABLE EMP (
  EMPNO NUMBER(4) PRIMARY KEY,
  ENAME VARCHAR2(10),
  JOB VARCHAR2(9),
  MGR NUMBER(4),
  HIREDATE DATE,
  SAL NUMBER(7,2),
  COMM NUMBER(7,2),
  DEPTNO NUMBER(2) REFERENCES DEPT(DEPTNO)
);

3. SALGRADE（工资等级表）

CREATE TABLE SALGRADE (
  GRADE NUMBER PRIMARY KEY,
  LOSAL NUMBER,
  HISAL NUMBER
);

4. BONUS（奖金表）

CREATE TABLE BONUS (
  ENAME VARCHAR2(10) REFERENCES EMP(ENAME),
  JOB VARCHAR2(9),
  SAL NUMBER,
  COMM NUMBER
);

二、数据插入前的准备工作

1. 解锁scott用户

ALTER USER scott ACCOUNT UNLOCK;
ALTER USER scott IDENTIFIED BY tiger;

2. 检查表是否存在

SELECT table_name FROM user_tables WHERE table_name IN ('DEPT','EMP','SALGRADE','BONUS');

若表不存在，需先执行表结构创建脚本（如前文所示）。

三、分步插入样例数据

1. 插入部门数据（DEPT）

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');
COMMIT;

验证数据：

SELECT * FROM DEPT ORDER BY DEPTNO;

2. 插入员工数据（EMP）

需注意MGR字段的外键约束（指向EMPNO）和DEPTNO字段的外键约束（指向DEPT.DEPTNO）：

-- 总裁级员工（无上级）
INSERT INTO EMP VALUES (7839, 'KING', 'PRESIDENT', NULL, TO_DATE('17-11-1981','DD-MM-YYYY'), 5000, NULL, 10);

-- 部门经理（上级为KING）
INSERT INTO EMP VALUES (7566, 'JONES', 'MANAGER', 7839, TO_DATE('02-04-1981','DD-MM-YYYY'), 2975, NULL, 20);
INSERT INTO EMP VALUES (7698, 'BLAKE', 'MANAGER', 7839, TO_DATE('01-05-1981','DD-MM-YYYY'), 2850, NULL, 30);

-- 普通员工（上级为部门经理）
INSERT INTO EMP VALUES (7782, 'CLARK', 'MANAGER', 7839, TO_DATE('09-06-1981','DD-MM-YYYY'), 2450, NULL, 10);
INSERT INTO EMP VALUES (7788, 'SCOTT', 'ANALYST', 7566, TO_DATE('13-07-1987','DD-MM-YYYY'), 3000, NULL, 20);
INSERT INTO EMP VALUES (7902, 'FORD', 'ANALYST', 7566, TO_DATE('03-12-1981','DD-MM-YYYY'), 3000, NULL, 20);

-- 销售代表（上级为BLAKE）
INSERT INTO EMP VALUES (7499, 'ALLEN', 'SALESMAN', 7698, TO_DATE('20-02-1981','DD-MM-YYYY'), 1600, 300, 30);
INSERT INTO EMP VALUES (7521, 'WARD', 'SALESMAN', 7698, TO_DATE('22-02-1981','DD-MM-YYYY'), 1250, 500, 30);

COMMIT;

验证数据：

SELECT EMPNO, ENAME, JOB, MGR, DEPTNO FROM EMP ORDER BY DEPTNO, EMPNO;

3. 插入工资等级数据（SALGRADE）

INSERT INTO SALGRADE VALUES (1, 700, 1200);
INSERT INTO SALGRADE VALUES (2, 1201, 1400);
INSERT INTO SALGRADE VALUES (3, 1401, 2000);
INSERT INTO SALGRADE VALUES (4, 2001, 3000);
INSERT INTO SALGRADE VALUES (5, 3001, 9999);
COMMIT;

验证数据：

SELECT * FROM SALGRADE ORDER BY GRADE;

4. 插入奖金数据（BONUS）

此表通常为空，但可添加测试数据：

INSERT INTO BONUS VALUES ('JONES', 'MANAGER', 2975, NULL);
INSERT INTO BONUS VALUES ('BLAKE', 'MANAGER', 2850, NULL);
COMMIT;

四、数据一致性维护技巧

1. 外键约束处理

若插入顺序不当可能导致ORA-02291错误（违反完整约束）。解决方法：

先插入无外键依赖的表（如DEPT）
后插入有外键依赖的表（如EMP）
临时禁用约束（不推荐生产环境使用）：

ALTER TABLE EMP DISABLE CONSTRAINT SYS_C00XXXX;

2. 日期格式处理

Oracle默认日期格式为DD-MON-YY，建议显式指定格式：

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MM-YYYY';

3. 批量插入优化

使用INSERT ALL语法提高效率：

INSERT ALL
  INTO DEPT VALUES (50, 'IT', 'SAN FRANCISCO')
  INTO DEPT VALUES (60, 'HR', 'SEATTLE')
SELECT * FROM dual;

五、常见问题解决方案

1. 权限不足错误（ORA-01031）

解决方案：

GRANT CREATE SESSION TO scott;
GRANT UNLIMITED TABLESPACE TO scott;

2. 表空间不足错误（ORA-01653）

解决方案：

ALTER TABLESPACE USERS ADD DATAFILE '/path/to/users02.dbf' SIZE 100M AUTOEXTEND ON;

3. 数据冲突错误（ORA-00001）

解决方案：

-- 检查重复主键
SELECT EMPNO FROM EMP WHERE EMPNO = 7839;

-- 使用MERGE语句更新重复数据
MERGE INTO EMP e
USING (SELECT 7839 EMPNO, 'KING JR' ENAME FROM dual) s
ON (e.EMPNO = s.EMPNO)
WHEN MATCHED THEN UPDATE SET e.ENAME = s.ENAME
WHEN NOT MATCHED THEN INSERT VALUES (s.EMPNO, s.ENAME, 'PRESIDENT', NULL, SYSDATE, 5000, NULL, 10);

六、高级数据生成方法

1. 使用序列生成自增ID

CREATE SEQUENCE emp_seq START WITH 8000 INCREMENT BY 1;

INSERT INTO EMP VALUES (
  emp_seq.NEXTVAL, 
  'SMITH', 
  'CLERK', 
  7902, 
  TO_DATE('12-01-1983','DD-MM-YYYY'), 
  800, 
  NULL, 
  20
);

2. 使用PL/SQL批量生成数据

DECLARE
  v_deptno NUMBER := 50;
BEGIN
  FOR i IN 1..10 LOOP
    INSERT INTO EMP VALUES (
      emp_seq.NEXTVAL,
      'EMP' || TO_CHAR(i),
      CASE MOD(i,3) WHEN 0 THEN 'CLERK' WHEN 1 THEN 'ANALYST' ELSE 'SALESMAN' END,
      CASE WHEN i

3. 使用外部表导入数据

-- 创建目录对象
CREATE OR REPLACE DIRECTORY data_dir AS '/tmp/oracle_data';

-- 创建外部表
CREATE TABLE emp_ext (
  empno NUMBER,
  ename VARCHAR2(10),
  job VARCHAR2(9),
  mgr NUMBER,
  hiredate DATE,
  sal NUMBER,
  comm NUMBER,
  deptno NUMBER
)
ORGANIZATION EXTERNAL (
  TYPE ORACLE_LOADER
  DEFAULT DIRECTORY data_dir
  ACCESS PARAMETERS (
    RECORDS DELIMITED BY NEWLINE
    FIELDS TERMINATED BY ','
    MISSING FIELD VALUES ARE NULL
  )
  LOCATION ('emp_data.csv')
)
REJECT LIMIT UNLIMITED;

-- 从外部表导入
INSERT INTO EMP SELECT * FROM emp_ext;

七、数据验证与查询示例

1. 部门员工统计

SELECT d.dname, COUNT(e.empno) emp_count, AVG(e.sal) avg_sal
FROM dept d LEFT JOIN emp e ON d.deptno = e.deptno
GROUP BY d.dname
ORDER BY emp_count DESC;

2. 工资等级分布

SELECT e.ename, e.sal, s.grade
FROM emp e JOIN salgrade s ON e.sal BETWEEN s.losal AND s.hisal
ORDER BY s.grade, e.sal DESC;

3. 经理下属关系

SELECT e.ename employee, m.ename manager
FROM emp e LEFT JOIN emp m ON e.mgr = m.empno
WHERE e.mgr IS NOT NULL
ORDER BY m.ename, e.ename;

八、数据清理与重置

1. 删除所有数据

BEGIN
  FOR rec IN (SELECT table_name FROM user_tables) LOOP
    EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || rec.table_name;
  END LOOP;
END;
/

2. 重置序列

DROP SEQUENCE emp_seq;
CREATE SEQUENCE emp_seq START WITH 7369 INCREMENT BY 1;

3. 恢复初始数据

可通过执行Oracle自带的脚本文件$ORACLE_HOME/rdbms/admin/utlsampl.sql重新创建scott用户的完整示例数据。

九、最佳实践建议

1. 事务控制：使用COMMIT/ROLLBACK管理数据变更

2. 备份策略：在执行大规模数据操作前进行表空间备份

3. 性能优化：对频繁查询的列（如DEPTNO、EMPNO）创建索引

CREATE INDEX idx_emp_deptno ON emp(deptno);
CREATE INDEX idx_emp_sal ON emp(sal);

4. 安全考虑：限制scott用户的权限范围，避免用于生产环境

关键词：Oracle数据库、scott用户、样例数据、DEPT表、EMP表、SALGRADE表、BONUS表、数据插入、外键约束、PL/SQL批量生成、数据验证

简介：本文详细介绍了Oracle数据库中scott用户及其关联表的结构，系统讲解了如何为DEPT、EMP、SALGRADE和BONUS表插入样例数据，涵盖了数据插入顺序、外键约束处理、批量生成方法及常见错误解决方案，并提供了数据验证查询和清理重置的完整流程。

立即下载

数据库相关