章彬.txt

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

1, 把咱们练习（6月8号）里面写到的过程和函数放到包里进行管理，然后调用测试

   create or replace package my_pkg
   is
   pragma serially_reusable;
   v_n number;
   v_m number;
   zhensu_error exception;
   function zhensu_function(n out int)
              return int;
       function input_function(v_n int)
             return int;
       procedure updataemp( v_n int,v_m int);
   end my_pkg;



2，把昨天最后一道题写出来

   create view temp_view_id 
  as
  select a.name,a.age from temp a ;
  create or replace trigger add_seq
  instead of insert 
  on tmp_view for each row
 declare 
     v int;
     maxid int;
 begin
     select max(id) into maxid from a;
     select count(*) into v from temp_view where name =:new.name;
       if v=0 then
         insert into a values(:new.name,:new.age,maxid+1);
       end if;
 end;


3，把刚才的例子敲一遍
    create or replace package body my_pkg
is
pragma serially_reusable;
v_flag number;
cursor mycursor is
select deptno,dname from dept;
function check_dept(v_deptno number)
return number
is
begin
select count(*) into v_flag from dept
where deptno = v_deptno;
if v_flag > 0 then
v_flag := 1;
end if;
return v_flag;
end check_dept;
function add_dept(v_deptno number,v_deptname varchar2,v_deptloc varchar2)
return number
is
begin
if
check_dept(v_deptno) = 0 then
insert into dept
values (v_deptno,v_deptname,v_deptloc);
return 1;
else
 return 0;
 end if;
 exception
 when others then
 v_sqlcode := sqlcode;
 v_sqlerrm := sqlerrm;
 return -1;
 end add_dept;
 function remove_dept(v_deptno number)
 return number
 is
 begin
 if check_dept(v_deptno) = 1 then
 delete from dept where deptno = v_deptno;
 return 1;
 else
 return 0;
 end if;
 exception
 when others then
 v_sqlcode := sqlcode;
 v_sqlerrm := sqlerrm;
 return -1;
 end remove_dept;
 procedure query_dept(v_deptno number)
 is
 begin
 if check_dept(v_deptno) = 1 then
 select * into v_deptrec from dept
 where deptno = v_deptno;
 end if;
 exception
 when others then
 v_sqlcode := sqlcode;
 v_sqlerrm := sqlerrm;
 end query_dept;
 procedure read_dept
 is
 v_deptno number;
 v_dname varchar2(14);
 begin
 for c_mycursor in mycursor loop
 v_deptno := c_mycursor.deptno;
 v_dname := c_mycursor.dname;
 dbms_output.put_line(v_deptno||' '||v_dname);
 end loop;
 end read_dept;
 begin
 v_sqlcode := null;
 v_sqlerrm := '|初始化消息文本';
 end my_pkg;

4，自己写一个应用的例子，这个例子里面用到游标，过程（把写的游标放到过程里），触发器（把过程	放到触发器里），题目不限，自己想象
     create view com_view as  
 select a.deptno,a.ename,a.sal,a.comm from emp11 a;
  
  create or replace trigger com_tri
  instead of insert 
  on com_view for each row
  declare
     count1 int;
     procedure addcomm(no int)
     is 
        cursor c_update(i int)
        is  
        select * from emp11 for update;
        tmp c_update%rowtype;
     begin
       open c_update(no);
          loop
             fetch c_update into tmp;
                exit when c_update%notfound ;
                    if tmp.deptno=no then
                        update emp11 set comm = 555 where current of c_update;
                    end if;
          end loop ;         
             close c_update;
     end addcomm;
   
     
  begin
    select count(*) into count1 from comm_view where deptno=:new.deptno;
        if count1=0 then
           insert into emp11(deptno,ename,sal,comm) values             
           (:new.deptno,:new.ename,:new.sal,:new.comm);
       end if;
    addcomm(:new.deptno);
  end;




  
5，每人对6，7章学的内容做一个总结（包含讲了那些内容，自己掌握了多少等等）