Oracle创建表，注释，视图，序列，同义词，触发器，存储过程举例

DDL相关操作

--创建员工信息表create table empinfo(empno number(4) constraint pk_emp primary key,ename varchar2(10),job varchar2(10),mgr varchar2(10),hiredate date,sal number(7,2),deptno number(2) constraint fk_deptno references deptinfo);--创建员工信息表注释comment on table empinfo is '员工信息表';comment on column empinfo.empno is '员工号';comment on column empinfo.ename is '员工姓名';comment on column empinfo.job is '工作';comment on column empinfo.mgr is '上司';comment on column empinfo.hiredate is '雇佣日期';comment on column empinfo.sal is '薪水';comment on column empinfo.deptno is '所属部门号';--创建部门信息表create table deptinfo(deptno number(2) constraint pk_dept primary key,dname varchar2(20),loc varchar2(20));--创建部门信息表注释comment on table deptinfo is '部门信息表';comment on column deptinfo.deptno is '部门号';comment on column deptinfo.dname is '部门名';comment on column deptinfo.loc is '地址';--创建工资等级表create table salarygrade(grade number;losal number;hisal number);--创建工资等级表注释comment on table salarygrade is '工资等级表';comment on column salarygrade is '等级';comment on column salarygrade is '等级中最低的薪水';comment on column salarygrade is '等级中最高的薪水';

视图view

--为empinfo表的empno,ename,sal和deptinfo表的dname以及salarygrade表的grade创建一个视图create view empinfo_detpinfo_salarygradeas select e.empno,e.ename,e.sal,d.dname,s.grade from empinfo e inner join deptinfo d using (deptno)inner join salarygrade s on e.sal between s.losal and s.hisal;--通过视图查询select * from empinfo_deptinfo_salarygrade;

序列sequence

--为员工表的empno创建一个序列create sequence empinfo_empno_seqstart with 1001increment by 1nomaxvaluenocyclecache 10;--查询序列当前值select empinfo_empno_seq.currval from dual;--查询序列下一个值select empinfo_empno_seq.nextval from dual;

同义词synonym

--为视图empinfo_depptinfo_salarygrade创建同义词create synonym eds for empinfo_deptinfo_salarygrade;--通过视图同义词来查询视图中的数据select * from eds;

触发器trigger

--为员工表的empno创建一个自动插入的触发器create or replace trigger empinfo_empno_tribefore insert on empinfofor each rowbegin  :new.empno:=empinfo_empno_seq.nextval;end;

存储过程

--创建一个可以控制行数的乘法表的过程create or replace procedure nine_nine(nine_line in number)as begin  for i in 1..nine_line loop    for j in 1..i loop      dbms_output.put(j||'*'||i||'='||j*i||'  ');    end loop;      dbms_output.put_line('');  end loop;end;--调用这个存储过程set serveroutput on;execute nine_nine(9);