李建国.txt

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

1, 把咱们练习（6月8号）里面写到的过程和函数放到包里进行管理，然后调用测试
/*create or replace package package_emp_l 
is
pragma serially_reusable;
  procedure procedure_emp_1_l;
  
end package_emp_l;*/
create or replace package body package_emp_l
is
pragma serially_reusable;
  ---------------定义私有游标，用来取empno
  cursor cursor_empno is
    select empno from emp_lijian;
  ----------------定义私有函数用来查询sal
  function function_emp_1_l(e_empno number)
    return number
    is 
      e_sal number;
    begin
      select sal into e_sal from emp_lijian where empno=e_empno;
      dbms_output.put_line(e_sal);
      return e_sal;
    end function_emp_1_l;
  ------------------定义一个私有过程用来完成修改sal
  procedure procedure_emp_2_l(e_empno emp_lijian.empno%type,e_sal emp_lijian.sal%type)
  is
  begin
    update emp_lijian set sal=e_sal where empno=e_empno;
  end procedure_emp_2_l;
  ------------------定义公有过程用来完成操作
  procedure procedure_emp_1_l
  is
    type rowtable_emp_l is table of emp_lijian%rowtype index by binary_integer;
    rowtable_emp1_l rowtable_emp_l;--创建数组用来存放emp中的值
  begin
    for e_empno in cursor_empno loop
      --function_emp_1_l(e_empno.empno);
      --exit when currsor_empno%notfound;
      if function_emp_1_l(e_empno.empno)<2500 then 
        procedure_emp_2_l(e_empno.empno,3600);
        dbms_output.put_line(e_empno.empno);
      end if;
     
    end loop;
    --close cursor_empno;
  end procedure_emp_1_l;
end package_emp_l;
  
2，写一个触发器，来完成自增长列值的增加。如表temp（name varchar2(10),age int,id int）,当执行insert into temp(name,age) values('andy',24)时 会自动的插入id 的值，id 是递增的列

create or replace trigger l5
instead of insert 
on view_temp_l
for each row
declare
  maxid int;
  i int;
begin
  select max(sid) into maxid from temp_l; 
  if maxid is null then   
    insert into temp_l(sid,sname) values(1,:new.sname);
  else
    insert into temp_l(sid,sname) values(maxid+1,:new.sname);
  end if;
end; 

  

3，把刚才的例子敲一遍
create or replace package my_pkg
is
pragma serially_reusable;
  v_deptrex dept%rowtype;
  v_sqlcode number;
  v_sqlerrm varxchar2(2048);
  function add_dept(v_deptno number,v_deptname varchar2,v_deptloc varchar2)
    return number;
  function remove_dept(v_deptno number)
    return number;
  procedure qery_dept(deptno number);
  procedure read_put;
end my_pkg;

create or replaxe package body my_plg 
is 
pragma serially_reusable;
  v_flag number;
  cursor mycursor is
    select deptno ,dname from dept;
  function chek_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_falg:=1;
    end if;
    return v_flag;
  end chek_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 
    wehrn 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 vdeptred from dpt
        where deptno=v_deptno;
      end if;
    exception others then 
      v_sqlcode:=sqlcode;
      v_sqlerrm:=sqlerrm;
    end query_dept;  
    procedure read_dept
    is
      v_deptno number;
      v_name varchar2(14);
      begin 
        for cmycursor in mycursor loop
          v_deptno:=c_mycursor.deptno;
          v_dname:=c_myursor.dnaem;
          dbms_output.putline(v_deptno||' '||v_dname);
        end loop;
      end read_dept;
    begin 
      v_sqlcode:=null;
      v_sqlerrm:='初始化消息文本';
    end my_pkg;