create table emp2 ( empno number(4),
ename varchar2(10),
job varchar2(9),
mgr number(4),
hiredate date,
sal number(7,2),
comm number(7,2),
deptno number(2)
);
-----------------------------------------------------------------------------------------------------------
insert into emp2 (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7369, 'SMITH', 'CLERK', 7902, to_date('17-12-1980', 'dd-mm-yyyy'), 800.00, null, 20);
insert into emp2 (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7499, 'ALLEN', 'SALESMAN', 7698, to_date('20-02-1981', 'dd-mm-yyyy'), 1600.00, 300.00, 30);
insert into emp2 (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7521, 'WARD', 'SALESMAN', 7698, to_date('22-02-1981', 'dd-mm-yyyy'), 1250.00, 500.00, 30);
insert into emp2 (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7566, 'JONES', 'MANAGER', 7839, to_date('02-04-1981', 'dd-mm-yyyy'), 2975.00, null, 20);
insert into emp2 (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7654, 'MARTIN', 'SALESMAN', 7698, to_date('28-09-1981', 'dd-mm-yyyy'), 1250.00, 1400.00, 30);
insert into emp2 (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7698, 'BLAKE', 'MANAGER', 7839, to_date('01-05-1981', 'dd-mm-yyyy'), 2850.00, null, 30);
insert into emp2 (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7782, 'CLARK', 'MANAGER', 7839, to_date('09-06-1981', 'dd-mm-yyyy'), 2450.00, null, 10);
insert into emp2 (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7788, 'SCOTT', 'ANALYST', 7566, to_date('19-04-1987', 'dd-mm-yyyy'), 3000.00, null, 20);
insert into emp2 (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7839, 'KING', 'PRESIDENT', null, to_date('17-11-1981', 'dd-mm-yyyy'), 5000.00, null, 10);
insert into emp2 (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7844, 'TURNER', 'SALESMAN', 7698, to_date('08-09-1981', 'dd-mm-yyyy'), 1500.00, 0.00, 30);
insert into emp2 (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7876, 'ADAMS', 'CLERK', 7788, to_date('23-05-1987', 'dd-mm-yyyy'), 1100.00, null, 20);
insert into emp2 (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7900, 'JAMES', 'CLERK', 7698, to_date('03-12-1981', 'dd-mm-yyyy'), 950.00, null, 30);
insert into emp2 (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7902, 'FORD', 'ANALYST', 7566, to_date('03-12-1981', 'dd-mm-yyyy'), 3000.00, null, 20);
insert into emp2 (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7934, 'MILLER', 'CLERK', 7782, to_date('23-01-1982', 'dd-mm-yyyy'), 1300.00, null, 10);
=============================================================================================================================
create table dept2 ( deptno number(2),
dname varchar2(14),
loc varchar2(13)
);
----------------------------------------------------
insert into dept2 (DEPTNO, DNAME, LOC)
values (10, 'ACCOUNTING', 'NEW YORK');
insert into dept2 (DEPTNO, DNAME, LOC)
values (20, 'RESEARCH', 'DALLAS');
insert into dept2 (DEPTNO, DNAME, LOC)
values (30, 'SALES', 'CHICAGO');
insert into dept2 (DEPTNO, DNAME, LOC)
values (40, 'OPERATIONS', 'BOSTON');
============================================================
create table salgrade2 ( grade number,
losal number,
hisal number
);
--------------------------------------------------
insert into salgrade2 (GRADE, LOSAL, HISAL)
values (1, 700, 1200);
insert into salgrade2 (GRADE, LOSAL, HISAL)
values (2, 1201, 1400);
insert into salgrade2 (GRADE, LOSAL, HISAL)
values (3, 1401, 2000);
insert into salgrade2 (GRADE, LOSAL, HISAL)
values (4, 2001, 3000);
insert into salgrade2 (GRADE, LOSAL, HISAL)
values (5, 3001, 9999);
========================================================
select * from emp2;
select * from dept2;
select * from salgrade2;
=====================================
select * from emp2; --employee2 员工表
select * from dept2; --department2 部门表
select * from salgrade2; -- salary grade2 工资等级表
-----------------------------------------------------
emp2
empno 员工编号
ename 员工姓名
job 工作/工种
mgr manager上级编号
hiredate 入职日期
sal salary 工资
comm 奖金/津贴
deptno 部门编号
---------------------
dept2
deptno 部门号
dname 部门名称
loc 所在地
---------------------------
salgrade2
grade 等级
losal lowest salary 最低工资
hisal high salary 最高工资
=============================================
触发器：
销售表 销售数量 +100
库存表 库存 -100
create table spb( --商品表
spbm integer, --商品编码
spmc varchar2(30), --商品名称
shangbi varchar2(20), --商标
chandi varchar2(12), --产地
guige varchar2(20), --规格
danwei varchar2(4), --单位
danjia number(10,2), --单价
kucun number(10,2) --库存数量
);
create table stock( --采购表
djh integer, --单据号
spbm integer, --商品编码
cgy varchar2(8), --采购员
cgd varchar2(30), --采购地
cgsl number(10,2), --采购数量
cgpc varchar2(16), --采购批次
cgrq date); --采购日期
创建第一个触发器：
一旦采购表中被插入采购商品的记录，就更新商品表的数据，使相应的商品的库存增加：
create or replace trigger stock_ins
after insert on stock
for each row
begin
update spb set kucun=kucun + :NEW.cgsl where spbm=:NEW.spbm;
end;
