章猛.txt

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

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

create or replace package p_zm
is
 function sum_n(n number)
      return number;
 function no_re_sal(f_empno number)
      return number;
 procedure up_emp(up_no number,up_sal number);
end p_zm;

1.2:
create or replace package body p_zm
is
  function sum_n(n number)
        return number
  is
    renn number :=1;
     n_sum number :=0;
     n_error exception;
 begin
    if n<=0 then
      raise n_error;
    end if;
    while renn <= n loop
       n_sum:=renn+n_sum;
       renn:=renn+1;
    end loop;
    return n_sum;
 exception
    when n_error then
      dbms_output.put_line('输入错误');
      return null;
 end sum_n;
    -----------------------------------------
 function no_re_sal(f_empno number)
     return number
 is
     f_sal number(8,2);
 begin
   select sal into f_sal from zmemp where empno=f_empno;
   return f_sal;
 exception
   when no_data_found then
      dbms_output.put_line('该员工不存在');
      return null;
 end no_re_sal;
 -------------------------------------------------
 procedure up_emp(up_no number,up_sal number)
 is
 begin
  update zmemp set sal = up_sal where empno = up_no;
  commit;
end up_emp;
begin
null;
end;


2:(把昨天最后一道题写出来)


create or replace trigger u_ztemp
instead of insert
on v_ztemp
for each row
declare
   m_id number;
begin
   select max(id) into m_id from ztemp;
   dbms_output.put_line(m_id);
  
if m_id is null then
     insert into ztemp values(:new.name,:new.age,1);
   else
      insert into ztemp values(:new.name,:new.age,m_id+1);
   end if;
  
end;


4:自己写一个应用的例子，这个例子里面用到游标，过程（把写的游标放到过程里），触发器（把过程	放到触发器里），题目不限，自己想象

当delete某位老师的编号id时，将该老师领导的学生的LID改为空，同时打印出这些学生的信息。
create or replace trigger zmm
after delete on teacher
for each row
  declare
    procedure mmz(cc number)
    is
      cursor c1 is select * from student where lid=cc;
    begin
      dbms_output.put_line('老师'||cc||'离职,其学生');
      dbms_output.put_line('******************************************');
      for c_c in c1 loop
         dbms_output.put_line(c_c.id||'    '||c_c.name);
      end loop;
       dbms_output.put_line('******************************************');
      dbms_output.put_line('现在没老师带');
    end;
begin 
    mmz(:old.tid);
    update student set lid=null where lid=:old.tid;

   
end;










3:把刚才的例子敲一遍
create or replace package my_pkgz
 is 
 pragma serially_reusable;
   v_deptrec dept%rowtype;
   v_sqlcode number;
   v_sqlerrm varchar2(2048);
   function add_dept(v_deptno number,
                     v_deptname varchar2,
                     v_deptloc varchar2)
             return number;
    function remove_dept(v_deptno number)
             return number;
    procedure query_dept(v_deptno number);
    procedure read_dept; 
 end my_pkgz;

create or replace package body my_pkgz
 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_pkgz;


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

 第六章主要是SQL语言基础，包括基本查询，分组查询，连接查询和事物的控制以及SQL函数，其中连接查询是比较复杂而且容易出错的地方。