谢妮娜.txt

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

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

/*  create or replace package a_pkg
    is
       function zsh(a int)
       return int;
       function xnn_1(myno emp.empno%type)
      return number;
       procedure xnn_2(myno emp.empno%type,mysal emp.sal%type);
 end a_pkg;
 */

create or replace package body  a_pkg
    is
                                ---1，定义一个函数，输入一个大于0的整数n， 返回1到n的整数和

    function zsh(a int)
    return int
    as
       v_1 number;
       v_2 number;
       shuru_error exception;
    begin
         if a<=0 then
              raise shuru_error;
         end if;
         for v_2 in 1..a loop
              v_1:=v_1+v_2;
         end loop;
         return v_1;
       exception
         when shuru_error then
            dbms_output.put_line('请输入正整数');
    end zsh;

                                     --2，定义一个函数，输入emp的empno，返回该行的sal

    function xnn_1(myno emp.empno%type)
    return number
    as
       mysal emp.sal%type;
       
    begin
       select sal into mysal from emp where empno=myno;
      return mysal; 
      exception
          when no_data_found then
              dbms_output.put_line('雇员号不存在，请重新输入');
              
    end xnn_1;
   
           ------ 定义一个更新emp表sal的存储过程，输入参数为empno,sal,根据empno更新相应的sal
     procedure xnn_2(myno emp.empno%type,mysal emp.sal%type)
     is
     
     begin
          update emp set sal=mysal where empno=myno;
          commit;

       exception
           when no_data_found then
               dbms_output.put_line('雇员号不存在，请重新输入');

    end xnn_2;
    
  end a_pkg;  

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

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

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

                       ------------------------建立视图
 /*create view tmp_view 
  as
  select a.name,a.age from x_temp a ;
 */
                       ------------------------建立INSTEAD of触发器
 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 x_temp;
     select count(*) into v from tmp_view where name =:new.name;
       if v=0 then
         insert into x_temp values(:new.name,:new.age,maxid+1);
       end if;
 end;


SQL> insert into tmp_view values('dada',10);

1 row inserted

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

3，把刚才的例子敲一遍


/* create or replace package my_pkg
  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_pkg;
      
*/

 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;
     
     --公有函数定义add_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;
     --公有函数定义remove_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;
      --公有过程定义query_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;
        --公有过程定义 read_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，自己写一个应用的例子，这个例子里面用到游标，过程（把写的游标放到过程里），触发器（把过程	放到触发器里），题目不限，自己想象

-----------实现功能：当向dept表中新插入数据时，触发触发器执行输出部门编号小于新插入的部门编
号的所有部门信息-------------

 create or replace trigger tri22
    before insert 
    on dept
    for each row
    declare 
         maxno dept.deptno%type;
          procedure xnpro(v int)
  
          is
    
          cursor  c1(tno int)
            is
              select dname,deptno from dept where deptno<tno;
              myname c1%rowtype;
          begin
             open c1(v);
                loop
                  fetch c1 into myname;
                     if c1%found then
                       dbms_output.put_line('部门名字:  '||myname.dname||'  部门编号:  '||myname.deptno);
                     else exit;
                     end  if;
               end loop;
             close c1;
          end xnpro;   
         
  
      begin
        case 
           when inserting then
                dbms_output.put_line('*******原部门编号小于新插入编号的部门信息*******');
                    xnpro(:new.deptno);
        end case;
         
      end;
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
法二:
 ----------实现功能:给emp11表插入数据时,不管插入的数据为何值,都将补助(comm)更改为555.

/* create view com_view as  -----创建视图
 select a.deptno,a.ename,a.sal,a.comm from emp11 a;
  */
-------------------------------------------instead of 触发器
  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章学的内容做一个总结（包含讲了那些内容，自己掌握了多少等等）
  我们从6月4日开始对第六章sql语言的学习，这个章节的知识点包含了SQL语句的特点、分类以及具体应用。其中以SQL语句的具体应用最为重要，其包含了数据的查询（SELECT语句）、数据的维护（UPDATE、INSERT、DELETE等语句）、事务的控制（ COMMIT、ROLLBACK、SAVEPOINT等语句）以及SQL函数。
   在对SQL语言的学习过程中，我觉得数据查询这一小节知识点比较多，涵盖的内容也很丰富，实现的方式多种多样，比较能够考验思维能力。当查询条件的限制比较复杂时，操作起来比较容易顾此失彼，我感觉还应该加强自己处理多种限制的能力。
 ........