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 int;
begin
select count(*) into v_temp from audit_table
  where name='emp';
if v_temp=0 then
   insert into audit_table values
   ('emp',0,0,0,sysdate,null);
end if;
case
  when inserting then
    update audit_table set ins=ins+1,endtime=sysdate
      where name='emp';
  when updating then
    update audit_table set upd=upd+1,endtime=sysdate
      where name='emp';
  when deleting then
    update audit_table set del=del+1,endtime=sysdate
      where name='emp';
end case;
end;
/
7.
update emp set sal=2000 where empno=7788;
update emp set sal=2000 where empno=7369;
select upd,starttime,endtime from audit_table
where name='emp';

8.
create or replace trigger tr_emp_time
before insert or update or delete on emp
begin
if to_char(sysdate,'hh24') not between
'9' and '17' then
raise_application_error(-20101,'非工作时间');
end if ;
end ;
/

9.视图
create or replace view dept_emp as
select a.deptno,a.dname,b.empno,b.ename
from dept a, emp b where a.deptno=b.deptno;