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