章彬.txt

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

 一,创建四个游标
    1，从dept表打印出所有的部门名字
    2，定义一个返回类型的游标，该游标返回dept表所有的数据 (用两种方式)
declare
 type deptrecord is record
 (deptno dept.deptno%type,
 dname dept.dname%type,
 loc dept.loc%type);
 v_dept_name dept.dname%type;
 v_dept_loc dept.loc%type;
 v_deptrecord deptrecord;
 cursor c1
  is
  select dname,loc from dept where deptno <= 10;
  cursor c2(v_dept_no number)
  is
   select dname,loc from dept;
   cursor c3 (v_dept_no number)
  return deptrecord
  is
  select deptno,dname,loc from dept where deptno <= v_dept_no;
  cursor c4(v_dept_no number)
  is 
  select deptno,dname,loc from dept where deptno <= v_dept_no;
  v_dept_rec c4%rowtype;
 begin
 -------------------------------------------------------------------
  open c1;
  loop
   fetch c1 into v_dept_name;
   dbms_output.put_line('所有部门的名字为'||v_dept_name);
   exit when c1%notfound;
   end loop;
   -------------------------------------------------------------
   open c2(20)
   loop
    fetch c2 into v_dept_name,v_dept_loc;
    exit when c2%notfound;
    dbms_output.put_line('c2: '||v_dept_name||' '||v_dept_loc);
    end loop;
    ------------------------------------------------------------------------
    open c3(v_dept_no =>30);
    loop
      fetch c3 into v_deptrecord;
      exit when c3%notfound;
        dbms_output.put_line('c3: '||v_deptrecord.deptno||' '||v_deptrecord.dname);
        end loop;
   -----------------------------------------------
        open c4(40);
        loop
         fetch c4 into v_dept_rec;
         exit when c4%notfound;
         dbms_output.put_line('c4: 'v_dept_rec.deptno||' '||v_dept_rec.dname);
         end loop;
   -------------------------------------
         close c1;
         close c2;
         close c3;
         close c4;
    end;

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

   create or replace trigger tri_emp_sal
before 
update of sal
or insert
on emp
for each row
when (old.sal='20000')
begin
 case
    when UPDATING ('sal')  then
    if :new.sal> :old.sal then
    RAISE_APPLICATION_ERROR(-20001,'薪水不能多于20000');
    end if;
    when inserting then 
    if :new.sal> :old.sal then
    RAISE_APPLICATION_ERROR(-20002,'薪水不能多于20000');
    end if;
    END CASE;
    
end;
    
三，以上午写的student,teacher 表为例子，写一个触发器，当删除teacher表的一行数据库时，在student表删除相应的数据（即：删除老师所教的学生）
create or replace trigger tr_stu_tea
  2  after delete of teacher
  3  on student
  4  for each row
  5  begin
  6   delete student lid= :delete teacher tid
  7   where tid= :lid;
  8   end;
  9  /


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