李建国.txt

关于oracle和sql的书籍和ppt教程,非常好,本人珍藏品

一,创建四个游标
    1，从dept表打印出所有的部门名字
    2，定义一个返回类型的游标，该游标返回dept表所有的数据 (用两种方式)
declare 
  type l_dept is record(l_deptno dept.deptno%type,l_dname dept.dname%type,l_loc dept.loc%type);
  li_dept l_dept;
  dname dept.dname%type;
cursor l1
is
  select dname from dept; 
cursor l2 
is 
  select deptno,dname,loc from dept;
  return l_dept;
cursor l3
is
  select deptno,dname,loc from dept;
  l3_dept l3%rowtype;
cursor l4
is 
  select dname from dept;
begin 
  dbms_output.put_line('游标l1-------------------------');
  for l_dname in l1 loop
    dbms_output.put_line(l_dname.dname);
  end loop;
  
  open l2;
  dbms_output.put_line('游标l2-------------------------');
  loop
    fetch l2 into li_dept;
    exit when l2%notfound;
    dbms_output.put_line(li_dept.l_deptno||'  '||li_dept.l_dname||'  '||li_dept.l_loc);
    
  end loop;

  open l3;
  dbms_output.put_line('游标l3-------------------------');
  loop
    fetch l3 into l3_dept;
    exit when l3%notfound;
    dbms_output.put_line(l3_dept.deptno||' '||l3_dept.dname||' '||l3_dept.loc);
  end loop;
  
  open l4;
  dbms_output.put_line('游标l4-------------------------');
  loop
    fetch l4 into dname;
    if l4%found then 
      dbms_output.put_line(dname);
    else null;
    exit;
    end if;
  end loop;
  close l2;
  close l3;
  close l4;
end;




二，建一个触发器，完成一个约束：emp表的薪水sal 不能大于20000

create or replace trigger emp_l
before update of sal or insert
on emp_l
for each row
begin 
  case 
    when updating('sal') then 
      if (:new.sal>20000) then
        RAISE_APPLICATION_ERROR(-20000,'工资不能高于20000');
      else 
        null;
      end if;
    when inserting then 
      if :new.sal>20000 then 
        RAISE_APPLICATION_ERROR(-20001,'工资不能高于20000');
      else 
        null;
      end if;
  end case;
end;  


三，以上午写的student,teacher 表为例子，写一个触发器，当删除teacher表的一行数据库时，在student表删除相应的数据（即：删除老师所教的学生）
create or replace trigger l2
after delete 
on teacher
for each row
begin 
  delete from student where lid=:old.tid;
end;


四，写一个触发器，来完成自增长列值的增加。如表temp（name varchar2(10),age int,id int）,当执行insert into temp(name,age) values('andy',24)时 会自动的插入id 的值，id 是递增的列



create or replace trigger l5
before insert 
on temp_l
for each row
declare
i int;
begin
  select count(*) into i from temp_l; 

  update temp_l set sid=i where sid is null;
end; 

create or replace trigger l5
after insert 
on temp_l
for each row
declare
i int;
begin
  select count(*) into i from temp_l; 

  update temp_l set sid=i+1 where sid is null;
end;