谢妮娜.txt

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

一,创建四个游标

    1，从dept表打印出所有的部门名字

 declare
   cursor c1 is
   select dname from a_dept;
   tname dept.dname%type;
 begin
   open c1;
   loop
   fetch c1 into tname;
   exit when c1%notfound;
   dbms_output.put_line (tname);
   end loop;
 end;

SQL> /

aaaaaaaa
ACCOUNTING
RESEARCH
SALES
OPERATIONS
pingxin

PL/SQL procedure successfully completed


    2，定义一个返回类型的游标，该游标返回dept表所有的数据（两种方法）

  法一：declare 
       cursor c2 return dept%rowtype
       is 
       select * from dept;
       tdept dept%rowtype;
   begin
      open c2;
      loop
      fetch c2 into tdept;
      exit when c2%notfound;
      dbms_output.put_line(tdept.deptno||' 名称为：'||tdept.dname||' 地点为：'||tdept.loc);
      end loop;
    end;


 法二： declare 
      type deptrecord is record (deptno dept.deptno%type,dname dept.dname%type,loc       dept.loc%type);
       cursor c2 return deptrecord
       is 
       select deptno,dname,loc from dept;
       tdept c2%rowtype;
  begin
      open c2;
      loop
      fetch c2 into tdept;
      exit when c2%notfound;
      dbms_output.put_line(tdept.deptno||' 名称为：'||tdept.dname||' 地点为：'||tdept.loc);
      end loop;
  end;

SQL> 
 14  /

50 名称为：aaaaaaaa 地点为：beijing
10 名称为：ACCOUNTING 地点为：NEW YORK
20 名称为：RESEARCH 地点为：DALLAS
30 名称为：SALES 地点为：CHICAGO
40 名称为：OPERATIONS 地点为：BOSTON
70 名称为：pingxin 地点为：china

PL/SQL procedure successfully completed

==================================================================

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

create or replace trigger sal_limit 
      before
      update of sal or insert 
      on emp for each row
    begin
      case
        when updating('sal') then
          if :new.sal > 20000 then
          raise_application_error(-20020,'修改数据的薪水不能大于20000');
          end if;
        when inserting then
          if :new.sal > 20000 then
          raise_application_error(-20021,'插入数据的薪水不能大于20000');
          end if;
       end case;
     end;

19  /

Trigger created
-----------------------------------------------------------插入SAL>20000的数据
SQL> insert into emp (empno,sal) values(3344,30000);

insert into emp (empno,sal) values(3344,30000)

ORA-20021: 插入数据的薪水不能大于20000
ORA-06512: 在 "SCOTT.SAL_LIMIT", line 9
ORA-04088: 触发器 'SCOTT.SAL_LIMIT' 执行过程中出错
------------------------------------------------------------修改SAL>20000的数据
SQL> update emp set sal=45000 where empno=7654;

update emp set sal=45000 where empno=7654

ORA-20020: 修改数据的薪水不能大于20000
ORA-06512: 在 "SCOTT.SAL_LIMIT", line 5
ORA-04088: 触发器 'SCOTT.SAL_LIMIT' 执行过程中出错

===================================================================================

三，以上午写的student,teacher 表为例子，写一个触发器，当删除teacher表的一行数据库时，在student表删除相应的数据（即：删除老师所教的学生）

create or replace trigger dele_stu24
      after
      delete 
      on teacher for each row
      begin
      case
      when deleting  then
      delete from student where tid =:old.id;
      end case;
      end;
=================================================================================
四，写一个触发器，来完成自增长列值的增加。如表temp（name varchar2(10),age int,id int）,当执行insert into temp(name,age) values('andy',24)时 会自动的插入id 的值，id 是递增的列

  create or replace trigger add_seq
     before
     insert 
     on x_temp for each row
 declare 
     maxid int;
 begin
     case
       when inserting then
       select max(id) into maxid from x_temp;
       update x_temp set id = (maxid+1) where id =null;
       end case;
 end;
  -----------------------------------------------------------------

     create or replace trigger add_seq
     before
     insert 
     on x_temp for each row
 declare 
    maxid int;
 begin
     case
        when inserting then
        select max(id) into maxid from x_temp;
            if maxid = null then
            insert into x_temp (id,name,age) values(seq_id.nextval,:new.name,:new.age);
            end if;
     end case;
 end;