Oracle实战练习(续四)

#1.PL/SQL
 #PL/SQL是Oracle内部使用的编程语言, Procedure Language过程语言,其语言格式比较固定
 begin
   dbms_output.put_line('HelloWorld');
 end; 
 /   #/表示执行
 set serveroutput on;   #执行输出命令,默认是off(关闭)

 declare  #声明变量
   v_name varchar2(20);
   begin
     v_name := 'myname';    #给变量赋值
     dbms_output.putline(v_name);
   end;
   / 
  #打印:myname, 输出myname
  #PL/SQL过程已成功完成  
 
 declare
  v_name number:= 0;
 begin
  v_name := 2/v_name;
  dbms_output.put_line(v_name);
  exception
   when others then
    dbms_output.put_line('error');
 end;
 /
 #去掉exception语句块
 declare
  v_name number:= 0;
 begin
  v_name := 2/v_name;
  dbms_output.put_line(v_name);
 end;
 /
 #则会出现如下错误:除数为 0 在 line 4
 
 #2.变量声明
     #变量名不能使用保留字,如from, select等
     #第一个字符必须是字母
     #变量名最多包含30个字符
     #不要与数据库的表或者列同名
     #每一行只能声明一个变量
 #3.常用变量类型
     #binary_integer: 整数,主要用来计数而不是用来表示字段类型  
     #number : 数字类型
     #char: 定长字符串
     #varchar2: 变长字符串
     #date: 日期
     #long: 长字符串, 最长2GB
     #boolean :布尔类型, 可以取值为true, false和null值.
    
 declare
   v_temp number(1);
   v_count binary_integer := 0;   --binary_integer 是用来记数的,效率比较高一点.
   v_sal number(7,2) := 4000.00;
   v_date date := sysdate;
   v_pi constant number(3,2) := 3.14;
   v_valid boolean := false;
   v_name varchar2(20) not null := 'MyName';
  begin
   dbms_output.put_line('v_temp value: ' || v_date);
  end;
 
 #4.变量声明,使用%type属性
  declare
    v_empno number(4);
    v_empno2 emp.empno%type;  --表示emp表中empno的类型
    v_empno3 v_empno2%type;
   begin
     dbms_output.put_line('Test');
   end; 
 
  --Table 变量类型
  declare
    type type_table_emp_empno is table of emp.empno%type index by binary_integer;
    v_empnos type_table_emp_empno;
   begin
     v_empnos(0):=7369;
     v_empnos(2):=7839;
     v_empnos(-1):= 9999;             
     dbms_output.put_line(v_empnos(-1));
   end; 
  
  --Record变量类型 (相当于java中的类(class))
   declare
     type type_record_dept is record
      (
       deptno dept.deptno%type,
       dname dept.dname%type,
       loc dept.loc%type
      ) ;
     v_temp type_record_dept;  --声明变量是type_record_dept类型
    begin
      v_temp.deptno :=50;   --引用类型的属性
      v_temp.dname := 'aaaa';   
       v_temp.loc := 'bj';
       dbms_output.put_line(v_temp.deptno||' '|| v_temp.dname);
    end;
    /
 --record类型是指定义了一个类,然后引用类中的属性.
 
 --使用%rowtype声明record变量
 declare
   v_temp dept%rowtype;
 begin
   v_temp.deptno := 50;
   v_temp.dname := 'aaa';
   v_temp.loc := 'bj';
   dbms_output.put_line(v_temp.deptno ||'  ' ||v_temp.dname);
 end;
 /    
 --可以保留原来的类型
 
 --PL/SQL语句,必须返回一条记录,只能返回一条记录.
 
 --SQL语句的运用
 declare
   v_ename emp.ename%type;
   v_sal emp.sal%type;
 begin
   select ename, sal into v_ename, v_sal from emp where empno = 7369; --把ename中的记录赋值给v_ename,sal赋值给v_sal.
   dbms_output.put_line(v_ename ||' '||v_sal);
 end;
 / 
 --结果: SMITH 800
 declare
   v_ename emp.ename%type;
   v_sal emp.sal%type;
 begin
   select ename, sal into v_ename, v_sal from emp where empno = 9999;
   dbms_output.put_line(v_ename ||' '||v_sal);
 end;
 / 
   --如果未找到数据
  
 declare
   v_sal emp.sal%type;
 begin
   select sal into v_sal from emp
      where empno = 7369;
      if(v_sal<1200) then
         dbms_output.put_line('low');
      elsif(v_sal<2000) then
         dbms_output.put_line('middle');
      else
         dbms_output.put_line('high');
      end if;
  end;              
 
  set serveroutput on;
  
  declare
    v_sal2 emp.sal%type;
  begin
     select sal into v_sal2 from emp where empno = 7839;  
      if(v_sal2>2500) then
          v_sal2 := v_sal2 / 2; 
      elsif(v_sal2<2500) then
          v_sal2 := v_sal2 * 2;
      else
          v_sal2 := 2500;  
      end if;
  end;             
  
  --循环
  --loop循环
  declare
    i binary_integer:=1;
  begin
    loop
     dbms_output.put_line(i);
      i := i+1;
      exit when(i>=11);
    end loop;
  end;    
  
  --while循环
 declare
   j binary_integer :=1;
 begin
   while (j<11) loop
    dbms_output.put_line(j);
     j := j +1;
   end loop;
 end; 
 
 --for 循环
 begin
   for k in 1..10 loop
    dbms_output.put_line(k);
   end loop;
   for k in reverse 1..10 loop
    dbms_output.put_line(k);
   end loop;
 end;     
     
 --错误处理
 declare
  v_temp number(4);
 begin
  select empno into v_temp from emp where deptno = 10;
 exception
   when too_many_rows then
     dbms_output.put_line('太多记录');
   when others then
     dbms_output.put_line('error');
 end;  
 
 declare
  v_temp number(4);
 begin
   select empno into v_temp from emp where empno = 2222;
 exception
    when no_data_found then
     dbms_output.put_line('没数据');
  end;     
     
 --记录错误的做法
  create table errorlog
  (
    id number primary key,
    errcode number,
    errmsg varchar2(1024),
    errdate date
  );  
 
  create sequence seq_errorlog_id start with 1 increment by 1;        
   declare
     v_deptno dept.deptno%type := 10;
     v_errcode number;
     v_errmsg varchar2(1024);
  begin
    delete from dept where deptno = v_deptno;
    commit;
   exception
     when others then
      rollback;
      v_errcode := SQLCODE;
      v_errmsg := SQLERRM;
       insert into errorlog values(seq_errorlog_id.nextval, v_errcode, v_errmsg, sysdate);
     commit;
  end;
            
 select to_char(errdate, 'YYYY_MM_DD HH24:MI:SS') from errorlog;  
 
 --循环定义一个游标
 --游标是系统为用户开设的一个数据缓冲区,存放SQL语句的执行结果.每个游标区都有一个名字.
 --用户可以用SQL语句逐一从游标中获取记录，并赋给主变量，交由主语言进一步处理。主语言是面向记录的，一组主变量一次只能存放一条记录。
 declare
   cursor c is
    select * from emp;
    v_emp c%rowtype;
 begin
    open c;
     loop
        fetch c into v_emp;
        exit when(c%notfound);
        dbms_output.put_line(v_emp.ename);
    end loop;
  close c;
 end; 
 
 declare
   cursor c is
    select * from emp;
    v_emp emp%rowtype;
 begin
   open c;
    fetch c into v_emp;
     while(c%found) loop
     dbms_output.put_line(v_emp.ename);
      fetch c into v_emp;
     end loop;
   close c;
  end;    
    
 --带参数的游标
 declare
  cursor c (v_deptno emp.deptno%type, v_job emp.job%type) is
   select ename, sal from emp where deptno=v_deptno and job= v_job;
   --v_temp(%rowtype)
 begin
   for v_temp in c(30,'CLEAK') loop
   dbms_output.put_line(v_temp.ename);
  end loop;
end;    
         
 --可更新的游标
 declare
    cursor c is
       select * from emp2 for update;
  begin
    for v_temp in c loop
     if(v_temp.sal<2000)then
      update emp2 set sal= sal*2 where current of c; --更新当前游标,游标到哪条记录,更新哪条.
     elsif(v_temp.sal = 5000) then
      delete from emp2 where current of c;
     end if;
    end loop;
  commit;
 end;    
 
 --存储过程
 --(带有名字的PL/SQL的程序块)
 create or replace procedure p 
   is
     cursor c is
      select * from emp2 for update;
  begin
    for v_emp in c loop
      if(v_emp.deptno = 10) then
        update emp2 set sal= sal+10 where current of c;
      elsif(v_emp.deptno =20) then
        update emp2 set sal=sal+20 where current of c;
      else 
        update emp2 set sal=sal+30 where current of c ;
      end if;
    end loop;
  end;  
 --执行存储过程
 exec p;
 --查看结果集
 select sal from emp2;
 --或者
 begin p;
 end;
 --再执行了一次
 --查看结果集
 select sal from emp2;
 
 --带参数的存储过程
 create or replace procedure p1
  (v_a in number, v_b number, v_ret out number, v_temp in out number)
   is
    begin
      if(v_a>v_b) then
        v_ret :=v_a;
      else
        v_ret :=v_b;
      end if;
      v_temp:=v_temp+1;
    end;
 --调用过程
 declare
   v_a number :=3;
   v_b number :=4;
   v_ret number;
   v_temp number:=5;
 begin
   p(v_a, v_b, v_ret, v_temp);
   dbms_output.put_line(v_ret);
   dbms_output.put_line(v_temp);
 end; 
 --结果
 4
 6
 
 --删除存储过程
  drop procedure p;
 show error;   --显示错误.
 
 --函数(function)
 create or replace function sal_tax
   (v_sal number) return number
    is
    begin
      if(v_sal<2000)then
        return 0.10;
      elsif(v_sal<2750) then
        return 0.15;
      else
       return 0.20;
      end if;
    end;         
  -- 运用函数
  select lower(ename), sal_tax(sal) from emp;
 
  --触发器
  create table emp2_log
    (
      aname varchar2(20),
      action varchar2(10),
      atime date
     );
    
  create or replace trigger trig
    after insert or delete or update on emp2 for each row
   begin
     if inserting then
      insert into emp2_log values(USER,'insert',sysdate);
     elsif updating then
       insert into emp2_log values(USER,'update',sysdate);
     elsif deleting then
       insert into emp2_log values(USER,'delete',sysdate);
    end if;
  end;           
   
 update emp2 set sal= sal*2 where deptno=30;
 select * from emp2_log;
 update dept set deptno = 99 where deptno=10;#违反完整约束条件 (DYS.FK_DEPTNO),因为有外键参考.
 
 --删除触发器
 drop trigger trig;
 
 create or replace trigger trig
   after update on dept
     for each row
  begin
     update emp set deptno=:NEW.deptno where deptno=:OLD.deptno;
 end;    
 
  update dept set deptno=99 where deptno=10;
  select deptno from dept;
 
--树状结构的存储与展示
  drop table article;
  create table article
   (
     id number primary key,
     cont varchar2(4000),
     pid number,
     isleaf number(1),--0代表非叶子节点,1代表叶子节点.
     alevel number(2)
   ); 
  
  insert into article values(1,'蚂蚁大战大象',0,0,0);
  insert into article values(2,'大象被打趴下了',1,0,0);
  insert into article values(3,'蚂蚁也不好过',2,1,2);
  insert into article values(4,'瞎说',2,0,2);
  insert into article values(5,'没有瞎说',4,1,3);
  insert into article values(6,'怎么可能',1,0,1);
  insert into article values(7,'怎么没有可能',6,1,2);
  insert into article values(8,'可能性是很大的',6,1,2);
  insert into article values(9,'大象进医院了',2,0,2);
  insert into article values(10,'护士是蚂蚁',9,1,3);
 
  create or replace procedure p1(v_pid article.pid%type, v_level binary_integer)  is
    cursor c is select * from article where pid=v_pid;
   begin
     for v_article in c loop
       dbms_output.put_line(v_article.cont);