段湘南.txt

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

1, 把咱们练习（6月8号）里面写到的过程和函数放到包里进行管理，然后调用测试
/*create or replace package my_emp_010
is
pragma serially_reusable;
    v_sqlcode number;
    function sum_010(i number)
       return number;
    function d_01(id number)
       return number;
    procedure d_02(id emp.empno%type,salary emp.sal%type);
end my_emp_010;*/
create or replace package body my_emp_010
is
pragma serially_reusable;
    sum1 number;
    j int := 1;
    i_input_error exception;
    sal_01 number;
    function sum_010(i number)
       return number
    is
    begin
       if i < 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 sum_010;
    function d_01(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 d_01;
    procedure d_02(id emp.empno%type,salary emp.sal%type)
    is
    begin
       update emp set sal = salary where empno = id;
       dbms_output.put_line('修改后的工资：'||salary);
    exception
       when no_data_found then
          dbms_output.put_line('没有该雇员：'||id);
    end d_02;
begin
    v_sqlcode := null;
end my_emp_010;

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

3，把刚才的例子敲一遍
/*create or replace package my_pkg_010
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_010;*/
create or replace package body my_pkg_010
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(20);
   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_010;

4，自己写一个应用的例子，这个例子里面用到游标，过程（把写的游标放到过程里），触发器（把过程	放到触发器里），题目不限，自己想象
create or replace trigger tri_stu_con
after update of score
on student_010
for each row
declare
procedure stu_con
is
  cursor stu_cur
  is
  select * from student_010 for update;
  stu_rec student_010%rowtype;
  s_score_error exception;
begin
  open stu_cur;
  loop
    fetch stu_cur into stu_rec;
    exit when stu_cur%notfound;
    if stu_rec.score > 100 then
       raise s_score_error;
    end if;
    if stu_rec.score < 0 then
       raise s_score_error;
    end if;
    if stu_rec.score between 0 and 60
       then update student_010 set score = score + 10
       where current of stu_cur;
    end if;

  end loop;
exception
  when s_score_error then
    dbms_output.put_line('错误的分数:'||stu_rec.score);
end stu_con; 
begin
case when updating then

    stu_con; 
end case;

end;