ORACLE 10 (付首昕)学习笔记 第2节 命令。

ORACLE 10 学习笔记-第2节-命令。
1. inner join / left join/ right join / full join
select a.dname, b.ename from dept a, emp b where a.deptno=b.deptno and a.deptno=10;
select a.dname, b.ename from dept a inner join emp b
on a.deptno=b.deptno and a.deptno=10;
select dname,ename from dept natural join emp;
select a.dname,b.ename from dept a left join emp b
on a.deptno=b.deptno and a.deptno=10;
select a.dname, b.ename from dept a right join emp b
on a.deptno=b.deptno and a.deptno=10;
select a.dname, b.ename from dept a, emp b where a.deptno=b.deptno(+) and b.deptno(+)=10;
2. 单行子查询
select ename,sal,deptno from emp where deptno=
(select deptno from emp where ename='scott');
select ename,job,sal,deptno from emp where job in
(select distinct job from emp where deptno=10);
select ename,sal,deptno from emp where sal>all
(select sal from emp where deptno=30);
3.建立触发器
create [or replace] trigger grigger_name
timing event1 [or event2 or event3]
on table_name
pl/sql block;
4.休息日不能修改雇员信息
create or replace trigger tr_sec_emp
before insert or update or delete on emp
begin
  if to_char(sysdate,'DY','nls_date_language=AMERICAN')
     IN ('SAT','SUN') THEN
    case
      when inserting then
        raise_application_error(-20001,'不能在休息日增加雇员');
      when updateing then
        raise_application_error(-20001,'不能在休息日更新雇员');
      when deleteing then
        raise_application_error(-20001,'不能在休息日解雇雇员');
     end case;
   end if;
end;
/
5.
create table audit_table(
name varchar2(20),ins int,upd int,del int,
starttime date,endtime date);
6.
create or replace trigger tr_audit_emp
after insert or update or delete on emp
declare
  v_temp in