Oracle 常用的十大 DDL 对象

table:(表)
　　创建表

create table test3 (tid number,tname varchar2(20),hiredate date default sysdate);
create table emp20 as select * from emp where deptno=20;
create table empinfo as select e.empno,e.ename,e.sal,e.sal*12 annsal,d.dname
　　　　　　from emp e, dept d where e.deptno=d.deptno

 

　　修改表

alter table test3 add photo blob;
alter table test3 modify tname varchar2(40);
alter table test3 drop column photo;
alter table test3 rename column tname to username;
rename test3 to test5;

 

　　删除表

drop table test5;(没有正真的删除,只是放入了oracle的回收站中)
　　　　show recyclebin(查看回收站)
　　　　purge recyclebin;(清空回收站)
　　　　　　　　　　　　(管理员没有回收站)
　　　　flashback table TESTSAVEPOINT to before drop;(闪回回收站中的表)

drop table TESTSAVEPOINT purge;

　　表/列约束:

　　　　　check : 检查约束 gender varchar2(2) check (gender in ('男','女')),
        create table student
            (
              sid number constraint student_pk primary key,  //主键(索引)
              sname varchar2(20) constraint student_name_notnull not null, //非空
              gender varchar2(2) constraint student_gender check (gender in ('男','女')), //检查
              email varchar2(40) constraint student_email_unique unique   //唯一
                                 constraint student_email_notnull not null, //非空
              deptno number constraint student_fk references dept(deptno) on delete set null //外键(级联置空)
            );  (on delete cascade :级联删除)

 

view(视图) 虚表
　　创建视图(要有视图权限):

        create view empinfoview
        as
        select e.empno,e.ename,e.sal,e.sal*12 annsal,d.dname
        from emp e, dept d
        where e.deptno=d.deptno;  //可以进行DML操作,但是不建议该操作.
        
        create or replace view empinfoview
        as
        select e.empno,e.ename,e.sal,e.sal*12 annsal,d.dname
        from emp e, dept d
        where e.deptno=d.deptno
        with read only;   //屏蔽了 DML操作.

 

　　删除视图:

drop view empinfoview;

 

sequence(序列) :维护表的主键,但是回滚等操作可能会出现裂缝.
　　创建序列:

        create sequence myseq; //创建序列
        select myseq.nextval from dual; //下移指针,返回序列值
        select myseq.currval from dual; //返回当前指针序列值.
        
        create table testseq (tid number,tname varchar2(20));
        insert into testseq values(myseq.nextval,'aaa'); //使用序列维护主键(类似自动增长列).

 

　　删除序列

drop sequence myseq;

 

index(索引) :类似目录,增加查询效率
　　自动创建:
　　　　当定义了  PRIMARY   KEY   和   UNIQUE   之后,自动在相应的列上创建唯一性索引.

　　创建索引:

        create index myindex on emp(deptno); //B树索引
        create bitmap index myindex on emp(deptno); //位图索引索引(适合查询)

　　删除索引:

drop index myindex;

 

synonym(同义词) :表的别名 (为了安全)
　　创建同义词:

        create synonym myemp for emp; //私有同义词
        create public synonym myemp for emp; //公有同义词

 

procedure(存储过程)
　　创建存储过程
　　(不带参数的)

        create or replace procedure sayHelloWorld  //不带参数
        as
          --说明部分
        begin
           dbms_output.put_line('Hello World');

        end;
        /

 

　　(带参数) 

        --给指定的员工涨100，(带参数)    
        create or replace procedure raisesalary(eno in number) //带一个输入参数参数的
        as
           --定义变量保存涨前的薪水
           psal emp.sal%type;
        begin
           --得到涨前的薪水
           select sal into psal from emp where empno=eno;

           --涨100
           update emp set sal=sal+100 where empno=eno;

            --要不要commit(不要,调用者提交回滚)

           dbms_output.put_line('涨前:'||psal||'   涨后:'||(psal+100));

        end;
        / 

 

　　//带有out参数的存储过程(可以有返回值(OUT))

        create or replace procedure queryempinfo(eno in number,
                                            pename out varchar2,
                                            psal   out number,
                                            pjob   out varchar2)
        as
        begin
          select ename,sal,empjob into pename,psal,pjob from emp where empno=eno;
        end;
        /

　　调用存储过程(sql中):

        1. exec sayHelloWorld();
        2. begin
             sayHelloWorld();
             sayHelloWorld();
           end;
           /

 

function(存储函数)
　　创建一个存储函数

        create or replace function queryempincome(eno in number)
        return number
        as
           --定义变量保存月薪和奖金
           psal emp.sal%type;
           pcomm emp.comm%type;
        begin
           select sal,comm into psal,pcomm from emp where empno=eno;

           --返回年收入
           return psal*12+nvl(pcomm,0);
        end;
        /    

 //原则,只有一个返回值使用存储函数,否则使用存储过程.

package(程序包)
　　包头

        CREATE OR REPLACE PACKAGE MYPAKCAGE AS 

          type empcursor is ref cursor;
          procedure queryEmpList(dno in number, empList out empcursor);

        END MYPAKCAGE;

 

　　包体

        CREATE OR REPLACE PACKAGE BODY MYPAKCAGE AS

          procedure queryEmpList(dno in number, empList out empcursor) AS
          BEGIN

            open empList for select * from emp where deptno=dno;

          END queryEmpList;

        END MYPAKCAGE;

 

trigger(触发器): 类似对insert,update,delete的监听器
　　创建触发器:
　　　　语句级触发器(每条语句操作一次,无论改变多少行)

        create trigger abcd       
        after/before  insert/delete/update[of 列名]
        on emp
        declare
        begin
           dbms_output.put_line('成功操作了表格');
        end;
        /

 

　　　　/*
　　　　触发器应用一：实施复杂的安全性检查
　　　　禁止在非工作时间插入新员工

　　　　周末:to_char(sysdate,'day') in ('星期六','星期日')
　　　　上班前 下班后：to_number(to_char(sysdate,'hh24')) not between 9 and 17
　　　　*/

        create or replace trigger securityemp
        before insert
        on emp
        begin
          if to_char(sysdate,'day') in ('星期六','星期日','星期三') or
             to_number(to_char(sysdate,'hh24')) not between 9 and 17 then
             --禁止insert
             raise_application_error(-20001,'禁止在非工作时间插入新员工');     
          end if;

        end;
        /
        

　　　　行级触发器(每改变一行操作一次)

        create or replace trigger checksalary
        before update
        on emp
        for each row              //有此行就是行级触发器
        begin
          --if 涨后的薪水  < 涨前的薪水 then
          if :new.sal < :old.sal then
            raise_application_error(-20002,'涨后的工资不能少于涨前的工资。
                                    涨前:'||:old.sal||'  涨后:'||:new.sal);
          end if;
        end;
        /