田粟.txt

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

1, 把咱们练习（6月8号）里面写到的过程和函数放到包里进行管理，然后调用测试
/*create or replace package oracle588
is
pragma serially_reusable;
   note number;
    function sum0(num number)
       return number;
    function d1(id number)
       return number;
    procedure d2(id emp.empno%type,salary emp.sal%type);
end oracle588;*/

create or replace package body oracle
is
pragma serially_reusable;
    sum1 number;
    j int := 1;
    i_input_error exception;
    sal_01 number;
    function sum0(num number)
       return number
    is
    begin
       if num< 1 then
          raise i_input_error;
       end if;
       j := j + 1;
       for j in 1..i loop
          sum1 := sum1 + j;
       end loop;
       return sum1;
    exception
       when i_input_error then
          return 0;
    end sum0;
    function d1(id number)
       return number
    is
    begin
       select sal into sal_01 from emp where empno = id;
       return sal_01;
    exception
       when no_data_found then
          return 0;
    end d1;
    procedure d2(id emp.empno%type,salary emp.sal%type)
    is
    begin
       update emp set sal = salary where empno = id;
       dbms_output.put_line('sal：'||salary);
    exception
       when no_data_found then
          dbms_output.put_line('not have ：'||id);
    end d2;
begin
    note := null;
end oracle588;


2，把昨天最后一道题写出来
   
create or replace view temp_view 
as
select temp.name,temp.age,temp.id from temp
---------------------------------------------------------------------
create or replace trigger tri_temp
instead of insert
on temp_view
for each row
declare
   max_id number;
begin
   select max(id) into max_id from temp;
   if max_id is null then
     insert into temp values(:new.name,:new.age,1);
   else
     insert into temp values(:new.name,:new.age,max_id + 1);
   end if;
end;

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，自己写一个应用的例子，这个例子里面用到游标，过程（把写的游标放到过程里），触发器（把过程	放到触发器里），题目不限，自己想象
5，每人对6，7章学的内容做一个总结（包含讲了那些内容，自己掌握了多少等等）