张杨.txt

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

1, 把咱们练习（6月8号）里面写到的过程和函数放到包里进行管理，然后调用测试
2，把昨天最后一道题写出来
/*create or replace view view_temp_010
as
  select name,age from temp_010;*/
  
create or replace trigger view_temptri
instead of insert 
on view_temp_010
for each row
declare 
  v_maxid number;
begin 
  select max(id) into v_maxid from temp_010;
        if v_maxid is null then 
    insert into temp_20 values(:new.name,:new.age,1);  
  else  
          insert into temp_010 values(:new.name,:new.age,v_maxid+1);
   end if;
end;
3，把刚才的例子敲一遍
/*create or  replace package my_pkg123
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_pkg123;*/


create or replace package body my_pkg123
as 
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;
    --公有函数定义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;
     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循环处理处理结果的每一行         iiiiiiiiii
      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_pkg123;

create or replace procedure puery
--(v_no in emp.empno%type)
is 
  v_no  emp.empno%type
  v_comm emp.comm%type;
begin 
   v_no:= dbms_output.put_line('enter:'||&no);
   select  comm into v_comm from emp where empno=v_no;
   if v_comm is null or v_comm=0 then
    raise_application_error(-20001,'wu comm');
   end if;
  exception 
     when no_data_found then
       dbms_output.put_line('wuren');
end;